Conversion failed when converting the varchar value 'AccNum' to data type int.

[gggram2000]

I need some help, cant figure out the error.

SqlConnection dbConnection = new SqlConnection(xmldb);
dbConnection.Open();
string sql = "SELECT InvStatus FROM SaleReceipts Where AccNum = '" + lblAccNum.Text + "' ";
SqlCommand command = new SqlCommand(sql, dbConnection);
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
         InvoiceStatus = reader[0].ToString();                 
}
reader.Close();            
dbConnection.Close();

I get this error:
Conversion failed when converting the varchar value 'AccNum' to data type int.

I have checked in the database and AccNum is datatype int, and InvStatus datatype varchar, If there's something I'm missing or something else I should check i'd appreciate the help. Thanks

[nateraaaa]

Change the Select statement to look like this

string sql = "SELECT InvStatus FROM SaleReceipts Where AccNum = '" + Convert.ToInt32(lblAccNum.Text) + "' ";

Nathan

[gggram2000]

Change the Select statement to look like this

string sql = "SELECT InvStatus FROM SaleReceipts Where AccNum = '" + Convert.ToInt32(lblAccNum.Text) + "' ";

Nathan

Thanks, but when I try that I get this error:
"Input string was not in a correct format."

[nateraaaa]

Thanks, but when I try that I get this error:
"Input string was not in a correct format."

What is the value of AccNum.Text? Is the value numeric?

If so try this

string sql = "SELECT InvStatus FROM SaleReceipts Where AccNum = " + Convert.ToInt32(lblAccNum.Text);

[Curtis Rutland]

The problem is that you are surrounding the param with single quotes. In SQL, that represents a varchar value, a string. Change your select statement to this:

string sql = "SELECT InvStatus FROM SaleReceipts Where AccNum = " + lblAccNum.Text;

Better yet, learn how to use String.Format"

string sql = String.Format("SELECT InvStatus FROM SaleReceipts Where AccNum = {0}", lblAccNum.Text);

The best way would be to use a parameterized query.

string sql = "SELECT InvStatus FROM SaleReceipts Where AccNum = @AccNum";
SqlCommand command = new SqlCommand(sql, dbConnection);
command.Parameters.AddWithValue("@AccNum", lblAccNum.Text);

This one is the most secure, and the easiest to understand just by looking at it.

@nateraaaa,
You wouldn't have to convert the text value to an int to put it back into a string. The original error was coming from the DB engine.

[gggram2000]

The problem is that you are surrounding the param with single quotes. In SQL, that represents a varchar value, a string. Change your select statement to this:

string sql = "SELECT InvStatus FROM SaleReceipts Where AccNum = " + lblAccNum.Text;

Better yet, learn how to use String.Format"

string sql = String.Format("SELECT InvStatus FROM SaleReceipts Where AccNum = {0}", lblAccNum.Text);

The best way would be to use a parameterized query.

string sql = "SELECT InvStatus FROM SaleReceipts Where AccNum = @AccNum";
SqlCommand command = new SqlCommand(sql, dbConnection);
command.Parameters.AddWithValue("@AccNum", lblAccNum.Text);

This one is the most secure, and the easiest to understand just by looking at it.

@nateraaaa,
You wouldn't have to convert the text value to an int to put it back into a string. The original error was coming from the DB engine.

Yeah it's much easier to understand. It worked perfect, thanks a lot for ur input! Appreciate it!

--George

[Curtis Rutland]

Glad to be of service =D

[nateraaaa]

If the DB expects AccNum to be an int then the value set for the @AccNum must be an int. That is why I suggested using the Convert.ToInt32 on AccNum.Text. There is also built in error handling here because if AccNum.Text is not a valid int the Convert.ToInt32 operator will throw an exception.

Nathan

[Curtis Rutland]

Good point, I was assuming he had already verified that the text was an int somewhere else.

[gggram2000]

Good point, I was assuming he had already verified that the text was an int somewhere else.

Yeah I did, when I said AccNum is dataType int in the database and was therefore viewing it as that on lblAccNum.text. Cheers!