Mostafa Bouzari Mostafa Bouzari -4 years ago 59
C# Question

My id is not null, why do I receive run time error?

I get this run time error and my

id
is not null I checked it in my code which you will see and I'm using telerik
GridView


this is the run time error


The parameterized query '(@_fName nvarchar(4),@_lName nvarchar(2),@_phone nvarchar(6),@_a' expects the parameter '@_id', which was not supplied.


I'm trying to get the
id
from another form that's why I wrote it like this

here's my code in my first layer

private void btnEdt_Click(object sender, EventArgs e)
{
Ref_View_Model = new View_model._View_Model();
Ref_C = new Customers();

foreach (var RowInfo in Ref_C.radGridView1.SelectedRows)
{
FireCell = RowInfo.Cells[0].Value.ToString();

if (FireCell==null)
{
MessageBox.Show("null");
}
}

//Ref_C.radGridView1.CurrentRow.Delete();
Ref_C.customersTableAdapter.Update(Ref_C.sales_and_Inventory_SystemDataSet);
Ref_C.customersTableAdapter.Fill(Ref_C.sales_and_Inventory_SystemDataSet.Customers);

Ref_View_Model.GetEditCustomers(FireCell, txtFName.Text, txtLName.Text, txtPhn.Text, txtDdrss.Text);
}


here's my code in the last layer

public void EditCustomres( string _id,string _fName, string _lName,string _phone, string _address)
{

Connection_String = @"Data Source=MOSTAFA-PC;Initial Catalog=" + "Sales and Inventory System" + ";Integrated Security=TrueData Source=MOSTAFA-PC;Initial Catalog=" + "Sales and Inventory System" + ";Integrated Security=True;";
Query = "update Customers " +
"set FName=@_fName ,LName=@_lName ,Phone=@_phone ,[Address]=@_address" +" "+
"where Id like @_id";
using (Con=new SqlConnection(Connection_String))
using (Cmd=new SqlCommand(Query,Con))
{
Cmd.Parameters.Add("@_fName", SqlDbType.NVarChar);
Cmd.Parameters.Add("@_lName", SqlDbType.NVarChar);
Cmd.Parameters.Add("@_phone", SqlDbType.NVarChar);
Cmd.Parameters.Add("@_address", SqlDbType.NVarChar);
Cmd.Parameters.Add("@_id", SqlDbType.Int);

Con = Cmd.Connection;
Con.Open();

Cmd.Parameters["@_fName"].Value = _fName;
Cmd.Parameters["@_lName"].Value = _lName;
Cmd.Parameters["@_phone"].Value = _phone;
Cmd.Parameters["@_address"].Value = _address;
Cmd.Parameters["@_id"].Value = _id;

Cmd.ExecuteNonQuery();
Cmd.Dispose();
}
}

Answer Source

You really don't want to use LIKE in this case, unless you have very specific needs for it to update things that look like the ID you're passing in, but are not quite it...

You're passing in @_id as an SqlDbType.Int, but the variable is actually of type string, this will cause a conversion to happen under the hood. In case you're passing in an empty string, the value is converted to null, which will result in the error you mentioned in your post. Check the code that leads into calling EditCustomres to ensure that it actually passes in the correct value. Adding argument checks, like the one I added, will help you track these kinds of issues much earlier in the callstack.

Step one is to get rid of your LIKE statement, so please update the query to use:

where id = @_id

Then update your code, since you expect the _id variable to be in int, you can't just assign a string to it, which is probably why it's getting reset to null. Either update the method to accept an int parameter instead of string id, or parse the int in your method before adding the parameter:

int idValue = -1;
if (!int.TryParse(_id, NumberStyles.Integer, CultureInfo.InvariantCulture, out idValue))
{
    throw new ArgumentException("id", "Id must be a string which contains an integer value, the value of 'id' was: '" + _id + "'"); 
}

Cmd.Parameters.Add("@_id", SqlDbType.Int).Value = idValue;

You may need to add a using statement to your file:

using System.Globalization;

This is where NumberStyles and CultureInfo are defined. Normally Visual Studio will suggest where you can find these items crtl+. will pop-up a screen which will add this statement automatically.


End result would be:

using System.Globalization;

....
....

public void EditCustomers( string _id,string _fName, string _lName,string _phone, string _address)
{
    int idValue = -1;
    if (!int.TryParse(_id, NumberStyles.Integer, CultureInfo.InvariantCulture, out idValue))
    {
       string message = "Id must be a string which contains an integer value, the value of 'id' was: '" + _id + "'";
       throw new ArgumentException("_id", message); 
    }

    Connection_String = @"Data Source=MOSTAFA-PC;Initial Catalog=" + "Sales and Inventory System" + ";Integrated Security=TrueData Source=MOSTAFA-PC;Initial Catalog=" + "Sales and Inventory System" + ";Integrated Security=True;";
    Query = @"update Customers 
                  set FName=@_fName, LName=@_lName, Phone=@_phone,[Address]=@_address
                  where Id = @_id";

    using (Con=new SqlConnection(Connection_String))
    using (Cmd=new SqlCommand(Query, con))
    {
        Cmd.Parameters.AddWithValue("@_fName", _fName);
        Cmd.Parameters.AddWithValue("@_lName", _lName);
        Cmd.Parameters.AddWithValue("@_phone", _phone);
        Cmd.Parameters.AddWithValue("@_address", _address);
        Cmd.Parameters.AddWithValue("@_id", idValue);

        Con.Open();
        Cmd.ExecuteNonQuery();
    }
}   

I simplified the code a bit as well. using a verbatim string for your SQL statement, removing the call to Dispose (which is already covered by your use of using(cmd) and removing the Con=Cmd.Connection, since it really cannot have any other value.

Using the .AddWithValue method is also easier than creating and setting the value individually.


Be really careful with solutions that require you to concatenate your string to form the SQL query on the SQL Server side, this could result in the value injecting unwanted SQL statements that can expose your data to the outside world or allow outsiders to destroy your data.

Also be careful with LIKE statements for matching update values. Unless you have very strong argument validation on the code paths calling into this statement, passing in id="?" or id="%%" or simply id=string.Empty to your function would likely update all records and will thus overwrite all of them to have the same values. %1% will match 1, 10, 21, 31, 11, 100003... Which is highly unlikely the desired behavior.

Using = over like in delete and update statements is almost always the desired solution. If you get errors with = you're likely mixing data types (string where int is expected for example).

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download