Error with Update Querry in VB

Solved/Closed
mikeoe2003 Posts 24 Registration date Thursday November 1, 2012 Status Member Last seen March 27, 2014 - Jan 10, 2013 at 09:42 PM
Susan Raj Posts 1 Registration date Saturday January 26, 2013 Status Member Last seen January 26, 2013 - Jan 26, 2013 at 12:33 AM
Hello,
Please find below my codes the 'Insert Query' runs without error but the 'Update Query' gives the error below:

Error: 'No value given to one or more required parameters.Microsoft.Jet database engine'

THE UPDATE QUERY
mySQLString = "UPDATE Student_info set Surname ='" & txtSurname.Text & "', first_name='" & TxtfirstName.Text & "',"
mySQLString = mySQLString + " [level]='" & TxtLevel.Text & "',birth_day= " & txtBirthday.Text & ", sex='" & TxtSex.Text & "',height=" & txtheight.Text & ", "
mySQLString = mySQLString + "state='" & TxtState.Text & "', room_no= '" & txtRoomNo.Text & "',id_no=" & txtIdNo.Text & ",picture='" & TxtPicturePath.Text & "',"
mySQLString = mySQLString + " Home_address='" & TxtHomeadd.Text & "',finger_print='" & txtfingerPrint.Text & "',matric_num =" & txtMatricNum.Text & ""

THE INSERT QUERY IS RUNS WITHOUT ERROR
mySQLString = "INSERT INTO Student_info (Surname, first_name, matric_number, [level], birth_day, sex, height,state, room_no, id_no, picture, Home_Address,finger_print ) "
mySQLString = mySQLString + "VALUES('" & txtSurname.Text & "','" & TxtfirstName.Text & "'," & txtMatricNum.Text & ",'" & TxtLevel.Text & "',"
mySQLString = mySQLString + "" & txtBirthday.Text & ",'" & TxtSex.Text & "'," & txtheight.Text & ",'" & TxtState.Text & "',"
mySQLString = mySQLString + "'" & txtRoomNo.Text & "'," & txtIdNo.Text & ",'" & TxtPicturePath.Text & "','" & TxtHomeadd.Text & "','" & txtfingerPrint.Text & "')"
Related:

9 responses

rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jan 11, 2013 at 05:42 PM
Are you updating all the records ??
Only difference I see in both is that you are not updating column matric_number
thanks for your response rizvisa1, i like to update all the records including column matric_number. i thought my code handles that please you assistance will he higly appreciated
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jan 12, 2013 at 07:43 AM
Why would you update all records to same value ?

And and i said your insert column is inserting into column matric_number and your update column is updating a column matric_num
thanks for helping me spot the correction in with Matric_Number.
for your second question, i want to be able to effect changes on an existing record. Please can i have your suggestion on how you think i should go about this.
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jan 12, 2013 at 08:10 AM
you need add a where clause that can uniquely identifies the record that you want to update.
Dear Risvisa1, thank you for your kind assistance, i want to be able to make update on all except the Matric_number and i have modified the code as you pointed out below but still have the error-

Error: Syntax error in update statement; microsoft.jet database engine

Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click

Dim myConnection As OleDbConnection
Dim myCommand As OleDbCommand
Dim mySQLString As String
myConnection = New OleDbConnection("provider=Microsoft.JET.OLEDB.4.0; " & _
"data source = '" & Application.StartupPath & "\school_record.mdb'")
myConnection.Open()

mySQLString = "UPDATE Student_info set Surname ='" & txtSurname.Text & "', first_name='" & TxtfirstName.Text & "',"
mySQLString = mySQLString + " [level]='" & TxtLevel.Text & "',birth_day= " & txtBirthday.Text & ", sex='" & TxtSex.Text & "',height=" & txtheight.Text & ", "
mySQLString = mySQLString + "state='" & TxtState.Text & "', room_no= '" & txtRoomNo.Text & "',id_no=" & txtIdNo.Text & ",picture='" & TxtPicturePath.Text & "',"
mySQLString = mySQLString + " Home_address='" & TxtHomeadd.Text & "',finger_print='" & txtfingerPrint.Text & "',where matric_number=" & txtMatricNum.Text & " "

myCommand = New OleDbCommand(mySQLString, myConnection)
myCommand.ExecuteNonQuery()
myConnection.Close()

End Sub
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jan 13, 2013 at 07:58 AM
Best thing would be, print out the sql that is being stored in mySQLString . Run that sql on db alone, see what error you get.
i am not that experienced in this, how can i access the sql statement stored in mysql string please
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jan 13, 2013 at 08:07 AM
you can step thru the code

or add statement "myCommand = New OleDbCommand(mySQLString, myConnection) "

Debug.print mySQLString

you will find the statement printed in intermediate window (ALT + F11 and then CTRL + G)
my problem is with the WHERE clause, if i Comment It out of the Codes it runs but when i Uncomment it i get the error SQl synthax Error please assist

Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
Try
Dim myConnection As OleDbConnection
Dim myCommand As OleDbCommand
Dim mySQLString As String
'Is this neccessary?
If txtMatricNum.Text <> "" And txtBirthday.Text <> "" And txtheight.Text <> "" And txtIdNo.Text <> "" Then
myConnection = New OleDbConnection("provider=Microsoft.JET.OLEDB.4.0; " & _
"data source = '" & Application.StartupPath & "\school_record.mdb'")
myConnection.Open()

mySQLString = "UPDATE Student_info SET Surname ='" & txtSurname.Text & "', first_name='" & TxtfirstName.Text & "',"
mySQLString = mySQLString + " [level]='" & TxtLevel.Text & "',birth_day= " & txtBirthday.Text & ", sex='" & TxtSex.Text & "',height=" & txtheight.Text & ", "
mySQLString = mySQLString + "state='" & TxtState.Text & "', room_no= '" & txtRoomNo.Text & "',id_no=" & txtIdNo.Text & ",picture='" & TxtPicturePath.Text & "',"
mySQLString = mySQLString + " Home_address='" & TxtHomeadd.Text & "',finger_print='" & txtfingerPrint.Text & "',"
mySQLString = mySQLString + "WHERE matric_number = " & txtMatricNum.Text & ""

myCommand = New OleDbCommand(mySQLString, myConnection)
Debug.Print(mySQLString)
myCommand.ExecuteNonQuery()
myConnection.Close()
Else
MsgBox("Enter the required values")
End If
Catch ex As Exception
MessageBox.Show(ex.Message & " - " & ex.Source)
End Try

End Sub
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jan 13, 2013 at 03:02 PM
have you tried what i suggested. If you have then please paste the sql that is generated and the result you get when your directly execute the sql
i have run the statement on the database and i get same error message

UPDATE Student_info SET Surname ='uzoma', first_name='beloved', [level]='8',birth_day= 1987, sex='female',height=61, state='Eboyi state', room_no= '132',id_no=1414,picture='C:\Prog3_CADS_Assist\BackUpp\School_Records\School_Records\bin\Debug\fp1 2.jpg', Home_address='Abakililki',finger_print='C:\Prog3_CADS_Assist\BackUpp\School_Records\School_Records\bin\Debug\pi2 - Copy.jpg',WHERE matric_number = 4411
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jan 15, 2013 at 11:41 AM
You have comma before "Where" clause
thank you very much for your kind assistance its ok now
Susan Raj Posts 1 Registration date Saturday January 26, 2013 Status Member Last seen January 26, 2013
Jan 26, 2013 at 12:33 AM
Check out the post http://a4academics.com/tutorials/24-sql-tutorial/19-3-create-update-and-delete-table for more info on DB Update queries