Newbie In Programming Newbie In Programming - 3 months ago 10
C# Question

SQL Invalid Column name detection from Query

I've tried running the code and I have no idea what's wrong with the query. Because it keeps saying invalid column name, when I'm trying to retrieve the data from that column instead. The column name matches the one in the DB. It's well connected because it's connected to a login form where it detects the other given password and name. I'm using based on a search textbox.

private void btnSearch_Click(object sender, EventArgs e)
{
SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyDetailConnectionString"].ToString());

try
{
cnn.Open();
SqlCommand command = new SqlCommand();
command.Connection = cnn;
string query = "SELECT *FROM AffiliatedRegister WHERE Username=" + txtUser.Text + "";
command.CommandText = query;
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
---[Converting String from db /Insert to textboxes]---
}
cnn.Close();
}
catch (Exception ex)
{
MessageBox.Show("Error" + ex);
}
}

Answer

Your statement erred because you did not wrap your string in quotes so Sql interpeted it as on object and not a string. That being said there you should use parameters and not string concatenation.

  1. Use parameters
  2. Wrap your SqlConnection in a using block
  3. You should specify the column order in the SELECT statement, do not use *.
  4. Do not swallow an Exception unless you know how to recover from it

See Best Practices - Executing Sql Statements.

Update code

private void btnSearch_Click(object sender, EventArgs e)
{
    // use ConnectionString property
    // wrap in using block
    using (SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyDetailConnectionString"].ConnectionString))
    {
        try
        {
            SqlCommand command = new SqlCommand();
            command.Connection = cnn;
            // use parameters
            // avoid *, specify columns instead
            string query = "SELECT * FROM AffiliatedRegister WHERE Username= @userName";
            command.CommandText = query;
            // use parameters, I assumed the parameter type and length - it should be updated to the type and length specified in your table schema
            command.Parameters.Add(new SqlParameter("@userName", SqlDbType.VarChar, 200) {Value = txtUser.Text });

            // open as late as possible
            cnn.Open();
            SqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                // ---[Converting String from db / Insert to textboxes]-- -
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show("Error" + ex); 

            // do not swallow the exception unless you know how to recover from it
            throw;
        }
    }
}
Comments