Previous Topic Next Topic

Error with Update Querry in VB

Solved/Closed
mikeoe2003 Posts 25 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

Answer 1 / 9
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
0
Answer 2 / 9
mikeoe2003
Jan 12, 2013 at 05:40 AM
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
0
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
0
Answer 3 / 9
mikeoe2003
Jan 12, 2013 at 08:05 AM
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.
0
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.
0
Answer 4 / 9
mikeoe2003
Jan 13, 2013 at 07:55 AM
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
0
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.
0

Didn't find the answer you are looking for?

Ask a question
Answer 5 / 9
mikeoe2003
Jan 13, 2013 at 08:03 AM
i am not that experienced in this, how can i access the sql statement stored in mysql string please
0
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)
0
Answer 6 / 9
mikeoe2003
Jan 13, 2013 at 10:35 AM
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
0
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
0
Answer 7 / 9
mikeoe
Jan 15, 2013 at 01:51 AM
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
0
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
0
Answer 8 / 9
mikeoe
Jan 15, 2013 at 12:22 PM
thank you very much for your kind assistance its ok now
0
Answer 9 / 9
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
0

Similar discussions

Corel Draw: Error Reading file problem
clinton_fields2 -
clinton_fields2 -

2 responses
error code 230000
Rajvir -
dwebb -

1 response