Why am I getting insufficient parameters supplied

[Adrian Rowlands]

using (SQLiteCommand cmd = conn.CreateCommand())
{
try
{

     cmd.CommandText = @"SELECT \* FROM.  customer WHERE lastname = @setName";

    cmd.Parameters.AddWithValue("@setName", txt\_name.Text);

    da\_Customer = new SQLiteDataAdapter(cmd.CommandText, conn);
     dt\_Customer = new DataTable();
    da\_Customer.Fill(dt\_Customer);
    dgv\_customer.DataSource = dt\_Customer;
    }
     catch (Exception ex)
   {
    MessageBox.Show(ex.Message);
     }
     }

I get insufficient parameters supplied with this block of code, any ideas why?

[Lost User]

At what line? It says so in the exception, so why can't you?

Bastard Programmer from Hell :suss: "If you just follow the bacon Eddy, wherever it leads you, then you won't have to think about politics." -- Some Bell.

[Dave Kreskowiak]

First, clean up your code indentation. It makes debugging your code easier and reduces the number of bugs in your code. Next, in your SQL statement, get rid of the period you have on the FROM clause. Then get rid of the * and replace it with the fields you want. Trust me, using "SELECT *" is NOT a habit you want to get into.

SELECT _fieldList_, ... FROM customer WHERE lastname=@setName";

After that, why are you creating a SqlCommand object only to throw it out and never use it with the DataAdapter? The SqlDataAdapter will take a SqlCommand object as a parameter, but you just pass in the SQL statement (.Text property) of the command you built, effectively ignoring the parameter object you built. Your code should be this:

using (SQLiteCommand cmd = conn.CreateCommand())
{
try
{
cmd.CommandText = @"SELECT * FROM customer WHERE lastname = @setName";

    cmd.Parameters.AddWithValue("@setName", txt\_name.Text);

    da\_Customer = new SQLiteDataAdapter(cmd);
    dt\_Customer = new DataTable();
    da\_Customer.Fill(dt\_Customer);
    dgv\_customer.DataSource = dt\_Customer;
}
catch (Exception ex)
{
    MessageBox.Show(ex.Message);
}

}

Also, it seems you're using class global data objects, "da_Customer", "dt_Customer", ... This is a REALLY BAD IDEA and will get you into trouble in the future with bugs that are really difficult to find. You should have individual methods that will return data, creating and disposing their own database objects, more like this:

public DataTable GetCustomerTableFromLastName(string lastName)
{
using (SQLiteConnection conn = new SQLiteConnection(CONNECTIONSTRING))
{
SQLiteCommand comm = conn.CreateCommand();

    cmd.CommandText = @"SELECT firstname, lastname, something, somethingElse FROM customer WHERE lastname = @setName";

    cmd.Parameters.AddWithValue("@setName", txt\_name.Text);

    SQLiteDataAdapter adpat = new SQLiteDataAdapter(cmd);
    DataTable tableResult = new DataTable();
    adapt.Fill(tableResult);

    return tableResult;
}

}

But, even though this is an improvement, it still falls way short of production quality code.

Asking questions is a skill CodeProject Forum Gui

[Richard Deeming]

Adrian Rowlands wrote:

da_Customer = new SQLiteDataAdapter(cmd.CommandText, conn);

Because you're passing the command text to the data adapter, not the command. The data adapter will create a new command using that command text, and none of the parameters you've added to cmd will be copied across. Pass in the command object instead:

da_Customer = new SQLiteDataAdapter(cmd);

But pay attention to Dave's advice (above[^]) as well.

---

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

[C0ding_j3ff]

In my opinion, this could probably have a lot to do with the software you are currently using.