Member Avatar for sluck 
//SELECT ID van ingelogde gebruiker
            MySqlConnection cn = cn = new MySqlConnection("server=localhost; user id=bla; password=bla; database=project; Allow User Variables=True;");
            cn.Open();
            string selID = "SELECT LidID FROM gebruikers WHERE Gebruikersnaam=?Gebruikersnaam;";
            MySqlCommand cmd = new MySqlCommand(selID, cn);
            MySqlDataReader dr = default(MySqlDataReader);
            //Add parameters to get the username and password
            cmd.Parameters.Add("?Gebruikersnaam", MySqlDbType.VarChar);
            cmd.Parameters["?Gebruikersnaam"].Value = User.Identity.Name;

            int userID = 0;
            dr = cmd.ExecuteReader();
            dr.Read();
            userID = dr.GetInt32("LidID");
            dr.Close();

            Label1.Text = userID.ToString();

            //SELECT gebruiker aan hand van ID
            MySqlDataAdapter myDataAdapter = default(MySqlDataAdapter);
            DataSet myDataSet = default(DataSet);
            string selUser = "SELECT LidID FROM leden WHERE LidID=?LidID;";
            MySqlCommand cmd2 = new MySqlCommand(selUser, cn);
            cmd2.Parameters.Add("?LidID", MySqlDbType.Int32);
            cmd2.Parameters["?LidID"].Value = userID;

            myDataAdapter = new MySqlDataAdapter(selUser, cn);
            myDataSet = new DataSet();
            myDataAdapter.Fill(myDataSet, "leden");

            DetailsView1.DataSource = myDataSet;
            DetailsView1.DataBind();

This code gives me always an error:"Parameter '? ' must be defined" on "myDataAdapter.Fill(myDataSet, "leden");"

I want to get the ID from a user that is logged in in my table 'gebruikers' and then select with that ID the information of the users that is stored in 'leden'. And return that information in a detailsview.

But if I change selUser to "... WHERE LidID = 1 ...", it works.

  • 2 Contributors
  • 2 Replies
  • 306 Views
  • 22 Hours Discussion Span
  • Latest Post Latest Post by sluck

Dani AI

Generated

Quick diagnosis: the exception happens because the DataAdapter is being created from the raw SQL string, not from the prepared MySqlCommand that already has the parameter. In ’s code a cmd2 with ?LidID is created, but new MySqlDataAdapter(selUser, cn) makes its own internal SelectCommand (which has no parameter), so Fill() complains. The hard-coded WHERE LidID = 1 worked for the same reason—no parameter needed.

Two simple fixes:

  • Construct the adapter from the prepared command: myDataAdapter = new MySqlDataAdapter(cmd2);
  • Or assign the prepared command to the adapter: myDataAdapter.SelectCommand = cmd2;

A slightly cleaner, safer pattern (use a single connection, check for no-result, and keep parameterization) is shown here:

using (var cn = new MySqlConnection(connString))
{
    cn.Open();
    var idCmd = new MySqlCommand("SELECT LidID FROM gebruikers WHERE Gebruikersnaam = @name", cn);
    idCmd.Parameters.AddWithValue("@name", User.Identity.Name);
    var idObj = idCmd.ExecuteScalar();
    if (idObj == null) return; // no match

    int userId = Convert.ToInt32(idObj);
    var detailsCmd = new MySqlCommand("SELECT * FROM leden WHERE LidID = @id", cn);
    detailsCmd.Parameters.AddWithValue("@id", userId);

    var adapter = new MySqlDataAdapter(detailsCmd);
    var ds = new DataSet();
    adapter.Fill(ds, "leden");
    DetailsView1.DataSource = ds;
    DetailsView1.DataBind();
}

Notes and tips: ’s concatenation suggestion will “solve” the immediate error but removes parameter safety—avoid building SQL by string to prevent SQL injection. Also ensure the first reader is closed before reusing the connection (or use ExecuteScalar), and return the columns needed for DetailsView (selecting only LidID will not populate other fields). Use using to dispose objects and prefer typed Parameters.Add(...) when precise types matter.

Recommended Answers

All 2 Replies

Member Avatar for Renukavani

If your store procedure doest have input param specify as

string selUser = "SELECT LidID FROM leden WHERE LidID=" + userID;   //Add input data

then remove line from 23 to 25 and check the status

Member Avatar for sluck

If your store procedure doest have input param specify as

string selUser = "SELECT LidID FROM leden WHERE LidID=" + userID;   //Add input data

then remove line from 23 to 25 and check the status

Thanks, it works now

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.