This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(233.6, 98%, 32%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ERM%3C/text%3E%3C/svg%3E)
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)
A core feature of Visual Studio that allows developers to inspect, analyze, and troubleshoot code during execution.
A family of Microsoft suites of integrated development tools for building applications for Windows, the web, mobile devices and many other platforms. Miscellaneous topics that do not fit into specific categories.
Additional SQL Server features and topics not covered by specific categories
Answer accepted by question author
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.
Hi Viorel-1!
Thanks for your answer, was very helpful to me, it's solved.
Thanks!
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.
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.
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
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.