CODEWITHSUNDEEP
c#sql.netsqlparameter
Computer Scientist
Computer Scientist

Reputation: 75

SQL Parameter in c# not working for some reason

I am using the following code in c# which is trying to filter data by two given parameters. However, it is not working. Would you have any suggestions.

Thanks, Vancho

Here is the code

 public void TestQuery()
    {
        SqlCommand sqlCommand = new SqlCommand(GetQuery());
        //sqlCommand.Parameters.Add("@SKU", SqlDbType.VarChar).Value = txtSKU.Text;
        //sqlCommand.Parameters.Add("@ProductName", SqlDbType.NVarChar).Value = txtProductName.Text;

        sqlCommand.Parameters.AddWithValue("@SKU", txtSKU.Text);
        sqlCommand.Parameters.AddWithValue("@ProWductName", txtProductName.Text);

        //sqlCommand.Parameters.Add("@SKU", System.Data.SqlDbType.NVarChar).Value = txtSKU.Text;
        //sqlCommand.Parameters.Add("@ProductName", System.Data.SqlDbType.NVarChar).Value = txtProductName.Text;

        //sqlCommand.Parameters["@SKU"].Value = txtSKU.Text;
        //sqlCommand.Parameters["@ProductName"].Value = txtProductName.Text;

        //execute query and other stuff

        conn.Open();

        SqlDataAdapter da = new SqlDataAdapter(GetQuery(), conn);
        DataTable dt = new DataTable();
        da.Fill(dt);

        dt.TableName = "Product Setup";
        dataGridView1.DataSource = dt;

        conn.Close();
    }
    private string GetQuery()
    {
        System.Text.StringBuilder sb = 
            new System.Text.StringBuilder(@"SELECT ProductID, BarCode, SKU, ProductName, SecondaryIDNumber, ModelIDNumber, ProductDescription
        FROM Products WHERE ProductId is not null ");



        if (txtSKU.Text != null)
            sb.Append("AND SKU = @SKU ");

        if (txtProductName.Text != null)
            sb.Append("AND ProductName = @ProductName");
        return sb.ToString();
    }

Upvotes: 0

Views: 1513

Answers (1)

Selman Genç
Selman Genç

Reputation: 101681

You are setting your adapters command but, you didn't give value to your parameters:

SqlDataAdapter da = new SqlDataAdapter(GetQuery(), conn);

Instead use this:

SqlCommand sqlCommand = new SqlCommand(GetQuery());
sqlCommand.Parameters.AddWithValue("@SKU", txtSKU.Text);
sqlCommand.Parameters.AddWithValue("@ProductName", txtProductName.Text);
SqlDataAdapter da = new SqlDataAdapter(sqlCommand, conn);

First get your query,give value to your parameters then pass that query to DataAdapter

Also you can check your parameters before using AddWithValue

if(sqlCommand.CommandText.Contains("@SKU"))
       sqlCommand.Parameters.AddWithValue("@SKU", txtSKU.Text);

if(sqlCommand.CommandText.Contains("@ProductName"))
       sqlCommand.Parameters.AddWithValue("@ProductName",txtProductName.Text);

Upvotes: 3

Related Questions