I’m new to VB and I have an Access database that I’m trying to insert records into using VB.NET. For some reason, I’m getting an error telling me that I have a syntax error in my INSERT INTO statement. Here is the code I’m using:
Dim SQL As String
SQL = "INSERT INTO tblUser (EmpFirstName, EmpLastName, UserName, Password) VALUES ('firstname', 'lastname', 'username', 'password')"
Try
Dim MyConnection As New OleDbConnection(ConnectionString)
Dim MyCommand As New OleDbCommand(SQL, MyConnection)
MyConnection.Open()
MyCommand.ExecuteNonQuery()
MyConnection.Close()
Catch ex As Exception
MsgBox(ex.ToString)
End Try
I have the connection string stored in a variable named ConnectionString (not just a fancy name) in a module, and I know the connection string is good because I use it in other parts of the program and they work flawlessly. Here is a link to the actual error message that pops up. Click Here.
Some of the suggestions that I’ve tried and haven’t worked are:
- Adding a ; after the SQL statement like this: 'password';)"
- Adding a ; after the ) like this: ‘password’);”
- Changing the ‘ to ‘’ (double apostrophe)
Any ideas would be greatly appreciated,
Thanks!
Scott LyslePosted Apr 7, 2007, 5:20 AM
"INSERT INTO [tblUser] ([EmpFirstName], [EmpLastName], [UserName], [Password]) VALUES('" & firstname & "', '" & lastname & "', '" & username & "', '" & password & "')"
or, try something like this:
(within a function that returns a Boolean)
Dim
queryString As String = "INSERT INTO [tblUser] ([EmpFirstName], [EmpLastName], [UserName], [Password]) VALUES (@FirstName, @LastName, @UID, @Pwd)"Dim dbCommand As System.Data.IDbCommand = New System.Data.OleDb.OleDbCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection Dim dbParam_FirstName As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
dbParam_FirstName .ParameterName = "@FirstName"
dbParam_FirstName .Value = firstname
dbParam_FirstName .DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_FirstName ) Dim dbParam_LastName As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
dbParam_LastName .ParameterName = "@LastName"
dbParam_LastName .Value = lastname
dbParam_LastName .DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_LastName ) Dim dbParam_UID As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
dbParam_UID .ParameterName = "@UID"
dbParam_UID .Value = username
dbParam_UID .DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_UID ) Dim dbParam_PWD As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
dbParam_PWD .ParameterName = "@PWD"
dbParam_PWD .Value = password
dbParam_PWD .DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_PWD )
Dim rowsAffected As Integer = 0
dbConnection.Open() Try
rowsAffected = dbCommand.ExecuteNonQuery
Catch
Return False
Finally
dbConnection.Close()
End Try If rowsAffected = 0 Then
Return False
Else
Return True
End If