HELP: Error converting data type varchar to bigint.

Question

HELP: Error converting data type varchar to bigint.

Ricardo Montoya | DYMSA 21

hi,please help, when i try to insert data on the sql database have this error message appear. "Error converting data type varchar to bigint."

this is the current Code:

oDataadapter1 = New SqlDataAdapter("Insert into dbo.batch values ('" + Trim(txtBatch_ID.Text) + "','" + Trim("Doccas Food - Spain") + "','" + Trim("ReportName") + "','" + Trim("2020-10-26 13:00:00") + "','" + Trim("Ricardo") + "','" + Trim("2020-10-26 13:00:00") + "','" + Trim("2020-10-26 13:00:00") + "','" + Trim("2020-10-26 14:00:00") + "','" + "10" + "','" + "Test" + "','" + "1500.0" + "','" + "1510.0" + "','" + "1" + "','" + "4" + "','" + "200" + "','" + "210" + "','" + "2" + "','" + "3" + "','" + "0" + "," + "')", CC)
Dim oCmd1 As SqlCommandBuilder = New SqlCommandBuilder(oDataadapter1)
odataset1 = New DataSet
oDataadapter1.Fill(odataset1, "batch")

the data that generates the error is the number 19, (marked with bold, in the code there is a "0" to test)

Answer accepted by question author

2 additional answers

Your answer

Answer 1

Viorel 125.6K

Try something like this: "2" + "','" + "3" + "'," + "0" + ")", CC), i.e. remove the incorrect apostrophes.

Clarify if more values are required after this “0”. And consider Parameterised Queries, which are more reliable.

If this is Visual Basic, then use ‘&’ instead of ‘+’.

Show the new statement if still does not work.

Ricardo Montoya | DYMSA 21 Reputation points

2020-11-04T07:18:41.137+00:00

Hi Viorel-1!

Thanks for your answer, was very helpful to me, it's solved.

Thanks!

Answer 2

You should use a parameterised statement. This saves you from this mess of quotes. Here is a quite example:

SqlCommand cmd = new SqlCommandI();
cmd.CommandText = @"INSERT INTO dbo.batch_values VALUES(@par1, @par2, ...)
cmd.Parameters.Add("@par1", SqlDbType.Int).Value = 35;
cmd.Parameters.Add("@par2", SqlDbType.NVarChar, 40).Value = "Ricardo";
...

And then you pass cmd to your DataAdapter.

The above syntax is C#, but it is easily translated to VB .Net

Concatenating strings to an SQL command like you do is an absolute no-no. It is very difficult to get right and it opens for SQL injection.

Answer 3

CathyJi-MSFT 22,401 Microsoft External Staff

Hi @Ricardo Montoya | DYMSA ，

Your issue is related to Visual Basic, VB is currently not supported in the Q&A forums, the supported products are listed over here https://learn.microsoft.com/en-us/answers/products (more to be added later on).

I suggest you ask the experts in the VB forum over here:
https://social.msdn.microsoft.com/Forums/vstudio/en-US/home?forum=vbgeneral

People there will help you more effectively.

Best regards,
Cathy

If the response is helpful, please click "Accept Answer" and upvote it.

Erland Sommarskog 128.7K Reputation points MVP Volunteer Moderator

2020-11-03T22:14:46.133+00:00

While Ricardo accepted Cathy's answer, I disagree with it. The question is related to data access to SQL Server, and Cathy may recall that we had a forum dedicated to data access on the old MSDN forums.

Overall, I don't find the point in Cathy's reply when two of us has already posted answers to the question.
Ricardo Montoya | DYMSA 21 Reputation points

2020-11-04T07:33:39.253+00:00

Hi Erland, first very thanks for you answer.

I'm new here. I accepted the answer because I thought that since there was another forum, I was not asking the question in the right place.

On the other hand, I have tried the viorel solution and it has worked for me.

I am not sure if it is the correct forum, but I will change the accepted answer because although it is not the place to ask the question, the answer was helpful.

again, thanks for your reply

Share via

HELP: Error converting data type varchar to bigint.

2 additional answers

Your answer