Error with Update Querry in VB

[Solved/Closed]
Report
Posts
25
Registration date
Thursday November 1, 2012
Status
Member
Last seen
March 27, 2014
-
Posts
1
Registration date
Saturday January 26, 2013
Status
Member
Last seen
January 26, 2013
-
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 & "')"

9 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
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
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
You have comma before "Where" clause
thank you very much for your kind assistance its ok now
Posts
1
Registration date
Saturday January 26, 2013
Status
Member
Last seen
January 26, 2013

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