469,265 Members | 1,977 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,265 developers. It's quick & easy.

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

I need some help, cant figure out the error.
Expand|Select|Wrap|Line Numbers
  1. SqlConnection dbConnection = new SqlConnection(xmldb);
  2. dbConnection.Open();
  3. string sql = "SELECT InvStatus FROM SaleReceipts Where AccNum = '" + lblAccNum.Text + "' ";
  4. SqlCommand command = new SqlCommand(sql, dbConnection);
  5. SqlDataReader reader = command.ExecuteReader();
  6. while (reader.Read())
  7. {
  8.          InvoiceStatus = reader[0].ToString();                 
  9. }
  10. reader.Close();            
  11. 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
Oct 22 '08 #1
9 23967
nateraaaa
663 Expert 512MB
Change the Select statement to look like this

Expand|Select|Wrap|Line Numbers
  1. string sql = "SELECT InvStatus FROM SaleReceipts Where AccNum = '" + Convert.ToInt32(lblAccNum.Text) + "' ";
  2.  
Nathan
Oct 22 '08 #2
Change the Select statement to look like this

Expand|Select|Wrap|Line Numbers
  1. string sql = "SELECT InvStatus FROM SaleReceipts Where AccNum = '" + Convert.ToInt32(lblAccNum.Text) + "' ";
  2.  
Nathan
Thanks, but when I try that I get this error:
"Input string was not in a correct format."
Oct 22 '08 #3
nateraaaa
663 Expert 512MB
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
Expand|Select|Wrap|Line Numbers
  1. string sql = "SELECT InvStatus FROM SaleReceipts Where AccNum = " + Convert.ToInt32(lblAccNum.Text);
Oct 22 '08 #4
Curtis Rutland
3,256 Expert 2GB
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:
Expand|Select|Wrap|Line Numbers
  1. string sql = "SELECT InvStatus FROM SaleReceipts Where AccNum = " + lblAccNum.Text;
  2.  
Better yet, learn how to use String.Format"
Expand|Select|Wrap|Line Numbers
  1. string sql = String.Format("SELECT InvStatus FROM SaleReceipts Where AccNum = {0}", lblAccNum.Text);
  2.  
The best way would be to use a parameterized query.
Expand|Select|Wrap|Line Numbers
  1. string sql = "SELECT InvStatus FROM SaleReceipts Where AccNum = @AccNum";
  2. SqlCommand command = new SqlCommand(sql, dbConnection);
  3. command.Parameters.AddWithValue("@AccNum", lblAccNum.Text);
  4.  
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.
Oct 22 '08 #5
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:
Expand|Select|Wrap|Line Numbers
  1. string sql = "SELECT InvStatus FROM SaleReceipts Where AccNum = " + lblAccNum.Text;
  2.  
Better yet, learn how to use String.Format"
Expand|Select|Wrap|Line Numbers
  1. string sql = String.Format("SELECT InvStatus FROM SaleReceipts Where AccNum = {0}", lblAccNum.Text);
  2.  
The best way would be to use a parameterized query.
Expand|Select|Wrap|Line Numbers
  1. string sql = "SELECT InvStatus FROM SaleReceipts Where AccNum = @AccNum";
  2. SqlCommand command = new SqlCommand(sql, dbConnection);
  3. command.Parameters.AddWithValue("@AccNum", lblAccNum.Text);
  4.  
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
Oct 22 '08 #6
Curtis Rutland
3,256 Expert 2GB
Glad to be of service =D
Oct 22 '08 #7
nateraaaa
663 Expert 512MB
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
Oct 22 '08 #8
Curtis Rutland
3,256 Expert 2GB
Good point, I was assuming he had already verified that the text was an int somewhere else.
Oct 22 '08 #9
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!
Oct 22 '08 #10

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

8 posts views Thread by Jimbo | last post: by
1 post views Thread by simon | last post: by
reply views Thread by simon | last post: by
12 posts views Thread by Frederik Vanderhaeghe | last post: by
reply views Thread by =?Utf-8?B?RGVuaXMgU29oZXQ=?= | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.