Syntax Error in Update

Louisa · March 16th, 2004, 05:53 AM

I have a simple update where I am adding new row into a database, but it errors saying syntax error in the insert into. I have almost identical code and it works fine, what am I doing wrong, it is driving me insane.

Louisa

Code:

StrSave = "Select passwords.id, passwords.name, passwords.login, passwords.password, passwords.addedby from Passwords"
SaveAdapter = New OleDb.OleDbDataAdapter(StrSave, myConnection)

SaveAdapter.Fill(SaveData, "Passwords")

StrMax = "Select max(ID) as MaxID from passwords"

MaxAdapter = New OleDb.OleDbDataAdapter(StrMax, myConnection)

MaxAdapter.Fill(MaxData, "Passwords")

Dim TblSave As DataTable
TblSave = SaveData.Tables("Passwords")

Dim SaveRow As DataRow
SaveRow = TblSave.NewRow()

Dim MaxID As Integer
MaxId = MaxData.Tables(0).Rows(0).Item("MaxId") + 1
SaveRow("id") = MaxID
SaveRow("Name") = TxtName.Text
SaveRow("Login") = TxtLogin.Text
SaveRow("Password") = TxtPassword.Text
SaveRow("Addedby") = "Louisa"

TblSave.Rows.Add(SaveRow)
Dim objCommandBuilder As New OleDb.OleDbCommandBuilder(SaveAdapter)
SaveAdapter.Update(SaveData, "passwords")

jaucourt · March 23rd, 2004, 05:22 AM

The way you're doing it is a bit convoluted. I'd try the following:

Code:

Dim sqlMax As String = "SELECT MAX(id) AS 'MaxID' FROM passwords"
Dim cmdMax As New OleDb.OleDbCommand(sqlMax, myConnection)
Dim maxId As Integer, maxReader As OleDb.OleDbDataReader

' get next id...
maxReader = cmdMax.ExecuteReader
maxReader.Read()
maxId = maxReader("MaxID") + 1
maxReader.Close()
cmdMax.Dispose()

' insert new row
Dim sqlInsert As String = "INSERT INTO Passwords (Id, Name, Login, Password, Addedby) VALUES ("
sqlInsert = sqlInsert & maxId & ", "
sqlInsert = sqlInsert & TxtName.Text & ", "
sqlInsert = sqlInsert & TxtLogin.Text & ", "
sqlInsert = sqlInsert & TxtPassword.Text & ", "
sqlInsert = sqlInsert & "'Louisa')"

Dim cmdInsert As New OleDb.OleDbCommand(sqlInsert, myConnection)
cmdInsert.ExecuteNonQuery()
cmdInsert.Dispose

This could be improved a bit as well. Really, I'd turn getting the next id into a stored procedure on the server that you can just execute - this will be a bit faster. Also, I'd give the insert command a load of parameters instead of building it line by line as I've done - this won't be very fast either. However, if it isn't a process you're going to repeat a lot, it probably won't matter - it could just be more scalable with the things I've suggested. Oh, one other thing - you'll probably want to put some error handling in there as well, paticularly around the database calls (.ExecuteReader and .ExecuteNonQuery) - and account for a null value coming back for maxId (display an error to the user?).

Jaucourt

jaucourt · March 23rd, 2004, 05:27 AM

Oh, but in case you're wondering, what was wrong with your code was in the final two lines;

Code:

Dim objCommandBuilder As New OleDb.OleDbCommandBuilder(SaveAdapter)
SaveAdapter.InsertCommand = objCommandBuilder.GetInsertCommand
SaveAdapter.Update(SaveData, "passwords")

New line is in bold!