UPDATE query not working

[juster21]

I am trying to create an update query but it is failing to make any changes.
Below is the code I have...

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        Dim strSqlEditRecord As String
        Dim myCmd As OleDb.OleDbCommand
        myCmd = Nothing
        strSqlEditRecord = "UPDATE tblMain SET Per_Hour_Quota= '" & txtQuota.Text & "' WHERE EnteredRecord= '" & lblRecordNumber.Text & "';"
        myCmd = New OleDb.OleDbCommand(strSqlEditRecord, gMDB)
        myCmd.Parameters.AddWithValue("@Per_Hour_Quota", txtQuota.Text)
        myCmd.CommandType = CommandType.Text
        myCmd.CommandTimeout = 60
        myCmd.ExecuteNonQuery()
        MsgBox("This record has been updated in the database.", MsgBoxStyle.Information, "Saved Record")
        Clear()
    End Sub

I have a form with a button to query the db and return records based on the criteria selected. The idea here is to allow the user to then make changes to that record and save back to the db. Can anyone tell me where the error is? Thanks!!

[nateraaaa]

There is an easy way to troubleshoot database errors. Surround your code with a try catch block. If the code in the try does not successfully execute the code will fall into the catch block giving you details of the problem.

Nathan

[DrBunchman]

Have you tried running your update query in Management Studio first to see whether it works?

Is your code erroring and causing an exception?

Dr B

[juster21]

There is an easy way to troubleshoot database errors. Surround your code with a try catch block. If the code in the try does not successfully execute the code will fall into the catch block giving you details of the problem.

Nathan

I tried...

        Try
            myCmd.ExecuteNonQuery()
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try

...nothing was caught but there was no update made to the database either

[Plater]

I would suspect your WHERE clause.
Is the correct information making it into that statement?
If EnteredRecord is not a string column, you should NOT surround your values with ' (single quote)

I also see you entering a parameter into it which doesn't look like it does anything.

I would agree with whoever it was that said you should test the query out in the SQL management program

[juster21]

I'm pretty sure the SQL statement is right but maybe not so much with the vb code...please advise if you know better. Thanks!!

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        Dim strSqlEditRecord As String
        Dim myCmd As OleDb.OleDbCommand
        myCmd = Nothing
        strSqlEditRecord = "UPDATE tblMain SET tblMain.Per_Hour_Quota= '" & txtQuota.Text & "' WHERE tblMain.EnteredRecord= '" & lblRecordNumber.Text & "';"
        myCmd = New OleDb.OleDbCommand(strSqlEditRecord, gMDB)
        myCmd.Parameters.AddWithValue("@Per_Hour_Quota", txtQuota.Text)
        myCmd.CommandType = CommandType.Text
        myCmd.CommandTimeout = 60
        Try
            myCmd.ExecuteNonQuery()
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try
 
        MsgBox("This record has been updated in the database.", MsgBoxStyle.Information, "Saved Record")
        Clear()
    End Sub

[Shashi Sadasivan]

strSqlEditRecord = "UPDATE tblMain SET tblMain.Per_Hour_Quota= '" & txtQuota.Text & "' WHERE tblMain.EnteredRecord= '" & lblRecordNumber.Text & "';"
myCmd = New OleDb.OleDbCommand(strSqlEditRecord, gMDB)
myCmd.Parameters.AddWithValue("@Per_Hour_Quota", txtQuota.Text)
myCmd.CommandType = CommandType.Text


Is there a @Per_Hour_Quota in your strSqlRecord?

well, the thing is it does not require a parameter to start with.
I think you can delete the following line
myCmd.Parameters.AddWithValue("@Per_Hour_Quota", txtQuota.Text)

You mentioned about an error !
Is there an exception which is being thrown?
if yes, then where and what is the the exception

[juster21]

strSqlEditRecord = "UPDATE tblMain SET tblMain.Per_Hour_Quota= '" & txtQuota.Text & "' WHERE tblMain.EnteredRecord= '" & lblRecordNumber.Text & "';"
myCmd = New OleDb.OleDbCommand(strSqlEditRecord, gMDB)
myCmd.Parameters.AddWithValue("@Per_Hour_Quota", txtQuota.Text)
myCmd.CommandType = CommandType.Text


Is there a @Per_Hour_Quota in your strSqlRecord?

well, the thing is it does not require a parameter to start with.
I think you can delete the following line
myCmd.Parameters.AddWithValue("@Per_Hour_Quota", txtQuota.Text)

You mentioned about an error !
Is there an exception which is being thrown?
if yes, then where and what is the the exception

if I remove myCmd.Parameters.AddWithValue("@Per_Hour_Quota", txtQuota.Text) then I get an error saying 'No value given for one or more required parameters'.
The error is thrown after Try
myCmd.ExecuteNonQuery()

[kunal pawar]

when u used text always used trim function. As u said there is no error and no any records affected mean problem in ur where condition.

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click

Dim strSqlEditRecord As String
Dim myCmd As OleDb.OleDbCommand
4.
myCmd = Nothing
5.
strSqlEditRecord = "UPDATE tblMain SET Per_Hour_Quota= '" & txtQuota.Text & "' WHERE EnteredRecord= '" & trim(lblRecordNumber.Text) & "';"
6.
myCmd = New OleDb.OleDbCommand(strSqlEditRecord, gMDB)
7.
myCmd.Parameters.AddWithValue("@Per_Hour_Quota", txtQuota.Text)
8.
myCmd.CommandType = CommandType.Text
9.
myCmd.CommandTimeout = 60
10.
myCmd.ExecuteNonQuery()
11.
MsgBox("This record has been updated in the database.", MsgBoxStyle.Information, "Saved Record")
12.
Clear()
13.
End Sub

[juster21]

when u used text always used trim function. As u said there is no error and no any records affected mean problem in ur where condition.

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click

Dim strSqlEditRecord As String
Dim myCmd As OleDb.OleDbCommand
4.
myCmd = Nothing
5.
strSqlEditRecord = "UPDATE tblMain SET Per_Hour_Quota= '" & txtQuota.Text & "' WHERE EnteredRecord= '" & trim(lblRecordNumber.Text) & "';"
6.
myCmd = New OleDb.OleDbCommand(strSqlEditRecord, gMDB)
7.
myCmd.Parameters.AddWithValue("@Per_Hour_Quota", txtQuota.Text)
8.
myCmd.CommandType = CommandType.Text
9.
myCmd.CommandTimeout = 60
10.
myCmd.ExecuteNonQuery()
11.
MsgBox("This record has been updated in the database.", MsgBoxStyle.Information, "Saved Record")
12.
Clear()
13.
End Sub

Thanks for the help. I tried adding the trim function but the record did not save and did not error. Any other thoughts?

[juster21]

Thanks for the help. I tried adding the trim function but the record did not save and did not error. Any other thoughts?

as it turns out, the problem was simple. I misspelled a field name in the database and had to remove the single quotes from the SQL. works fine now.