Click here to Skip to main content
15,173,640 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
I have a code that brings out the above error each time i click the update button, i have tried changing the values both in MS ACCESS AND VB
HERE IS THE CODE

Try
con.Open()
cmd = New OleDbCommand("Update [Trainer] Set [TrainerName]='" & TextBox1.Text & "',[Ag]='" & Convert.ToInt64(TextBox2.Text) & "',[Gender]='" & gender.Text & "',[Sallary]='" & Convert.ToInt64(TextBox3.Text) & "',[Mobile]='" & Convert.ToInt64(TextBox4.Text) & "',[Email]='" & TextBox5.Text & "',[JoiningDate]='" & Datess.Text & "' where [ID]='" & Convert.ToInt64(TextBox6.Text) & "'", con)



Dim i As Integer = cmd.ExecuteNonQuery()
If (i > 0) Then




MsgBox("Record Updated successfully ", MsgBoxStyle.Information, MsgBoxStyle.OkOnly)
con.Close()
clr()
TextBox2.Focus()
showdgv()



Else
MsgBox("Record NOT Updated ", MsgBoxStyle.Critical, MsgBoxStyle.OkOnly)

End If
Catch ex As Exception
MsgBox(ex.Message)
clr()
Finally
con.Close()
End Try

What I have tried:

Changing the names and values in both ms-access and visual studio
Posted
Updated 11-Nov-21 18:02pm
Comments
Gerry Schmitz 11-Nov-21 22:14pm
   
Display (Debug.Write) the command string to see what you actually created.
Richard Deeming 12-Nov-21 4:45am
   
Your code is vulnerable to SQL Injection[^]. NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

You were told about that when you posted your previous copy of this question:
What should I do in “data type mismatch in criteria expression”[^]

1 solution

First, you need to dump that abomination using string concatenation to build an SQL statement. That just leads to the problems you're seeing right now, as well as setting yourself up to have your database destroyed by malicious input.

Basically, the error is telling you that you're trying to stuff a value of one type (like a string or numeric value) into a field that doesn't accept that kind of data. For example, you're trying to put a string into a numeric field.

How do you solve these problems? "Parameterized queries".

VB.NET
strSQL = "INSERT INTO Customers (ID,Name,Email,Budget) " & _  
"VALUES (@ID,@Name,@Email,@Budget)"
  
objCmd = New OleDbCommand(strSQL,objConn)  
  
With objCmd  
    .Parameters.Add(New OleDbParameter("@ID", 5))  
    .Parameters.Add(New OleDbParameter("@Name", "Somebody"))  
    .Parameters.Add(New OleDbParameter("@Email", "somebody@somewhere.com"))  
    .Parameters.Add(New OleDbParameter("@Budget", 200000))  
End With  

objCmd.ExecuteNonQuery()
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900