SqlException was unhandled, C#

Question

What should I do with this error? I used this code in selectall method and everything was fine, but in selectrow I get this error.

Code:

public DataTable SelectRow(string ContactID)
{
    string query = "Select * from ContactsList Where ContactID = @ID";

    SqlConnection connection = new SqlConnection(ConnectionString);

    SqlDataAdapter adapter = new SqlDataAdapter(query, connection);

    DataTable data = new DataTable();
    adapter.Fill(data);

    return data;
}

Error:

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

Additional information:

Must declare the scalar variable "@ID".

Typo: you did not create the sql parameter ... `string ContactID` => `new @ID` ... — , Aug 22 '21 at 21:18
Does this answer your question? [What are good ways to prevent SQL injection?](https://stackoverflow.com/questions/14376473/what-are-good-ways-to-prevent-sql-injection) • [SqlCommand Parameters Add vs. AddWithValue](https://stackoverflow.com/questions/21110001/sqlcommand-parameters-add-vs-addwithvalue) — , Aug 22 '21 at 21:19

score 2 · Answer 1 · answered Aug 23 '21 at 10:06

The following code

Creates a parameter as a parameter should be done and as recommended by @Olivier Rogier.
Does not use a DataAdapter, seeing you have it as a local var indicates this. Use a command object instead.
Note the return type carries the DataTable if no errors while if a runtime exception is raised the Exception is passed back to the caller.
Recommend not using SELECT * (not part of the question but better to ask for that which is needed and no more).

Code

public class DataOperations
{
    private static string connection = "Data Source=.\\sqlexpress;Initial Catalog=NorthWind2020;Integrated Security=True";

    public static (DataTable table, Exception exception) SelectRow(string contactId)
    {
        DataTable table = new DataTable();

        try
        {
            using (var cn = new SqlConnection { ConnectionString = connection })
            {
                using (var cmd = new SqlCommand { Connection = cn })
                {
                    cmd.CommandText = "SELECT FirstName, LastName FROM dbo.Contacts WHERE ContactId = @contactId;";
                    cmd.Parameters.Add("@contactId", SqlDbType.NVarChar).Value = contactId;
                    cn.Open();
                    table.Load(cmd.ExecuteReader());
                    return (table, null);
                }
            }
        }
        catch (Exception exception)
        {
            return (null, exception);
        }

    }
}

Sample code to call method above.

var (dataTable, exception) = DataOperations.SelectRow(contactIdentifier);
if (exception == null)
{
    Debug.WriteLine("Use table");
}
else
{
    Debug.WriteLine(exception.Message);
}

Also, if the contact identifier is pointing to a single record you could simply pass a single DataRow back rather than an entire DataTable or use a single instance of a class e.g.

public class Contact
{
    // seems this would be an int
    public string ContactId { get; set; }

    public string FirstName { get; set; }

    public string LastName { get; set; }

    public override string ToString() => $"{FirstName} {LastName}";

}

Code to get data

public static Contact SelectContact(string contactId)
{

    Contact contact = new Contact() {ContactId = contactId};
    
    using (var cn = new SqlConnection { ConnectionString = connection })
    {
        using (var cmd = new SqlCommand { Connection = cn  })
        {
            cmd.CommandText = "SELECT FirstName, LastName FROM dbo.Contacts WHERE ContactId = @contactId;";
            cmd.Parameters.Add("@contactId", SqlDbType.NVarChar).Value = contactId;
            cn.Open();

            var reader = cmd.ExecuteReader();
            
            if (reader.HasRows)
            {
                reader.Read();
                contact.FirstName = reader.GetString(0);
                contact.LastName = reader.GetString(1);
            }

        }
    }

    return contact;
}

Shakti K · Answer 2 · 2021-08-22T22:27:44.823

Passing Scalar Variable to SQL Raw Query (Must declare the scalar variable)

You have declared the parameter @ID but didn't pass the value for the parameter. I have modified your code as below.

public DataTable SelectRow(string ContactID)
{
    SqlConnection connection = new SqlConnection(ConnectionString);
    string query = "Select * from ContactsList Where ContactID = @ID";
    SqlCommand cmd=new SqlCommand(query,connection);
    cmd.CommandType=CommandType.Text;
    cmd.Parameters.AddWithValue("@ID",ContactID);               
    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
    DataTable data = new DataTable();
    adapter.Fill(data);
    return data;
}

You can pass the SqlCommand object "cmd" to the SqlDataAdapter constructor which will load the data as per the specified query to the "adapter" object. Hoping that the above code will be helpful to you.

Although best practice is to [not use `AddWithValue`](https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/) — Dale K, Aug 22 '21 at 22:48
Best practice is also to dispose connection, command and adapter with `using` — Charlieface, Aug 23 '21 at 10:59

SqlException was unhandled, C#

2 Answers2

Passing Scalar Variable to SQL Raw Query (Must declare the scalar variable)