Trying to update a record

[AccessShell]

I have been able to read records from the DB. I have able to delete records from the DB. Bit I cannot update a record to the DB.

My routine is as follows. The connection to DB has already been made. Forget the commented out code for now (BeginTrans, Commit, and Rollback). I'll worry about them later.

  
Public Sub UpdateClient()
        Dim strUpdateSQL As String
        Dim Msg As String
        Dim cmd As String
        Dim strI As String
        Dim strP As String
        Dim catCMD As OleDbCommand = con.CreateCommand()
 
        Try
            If Me.txtI.Text <> "" Then
                strI = Me.txtI.Text
            End If
            If Me.txtP.Text <> "" Then
                strP = Me.txtP.Text
            End If
 
            'trans = con.BeginTransaction
            strUpdateSQL = "UPDATE tblClient SET "
            strUpdateSQL = strUpdateSQL & "tblClient.I = '" & strI & "' ?
            strUpdateSQL = strUpdateSQL & "AND tblClient.P = '" & strP & "' "
            strUpdateSQL = strUpdateSQL & "WHERE (((tblClient.CatNbr)=" & gintCatSelectedItem & ") "
            strUpdateSQL = strUpdateSQL & "AND ((tblClient.ClientName)='" & gstrClientName & "')) "
 
            catCMD.CommandType = CommandType.Text
            catCMD.CommandText = strUpdateSQL
            catCMD.ExecuteNonQuery()
 
            'trans.Commit()
            catCMD.Dispose()
 
            Me.txtMessage.Text = " **CLIENT UPDATED SUCCESSFULLY**"
            blnRecipeDeleted = True
 
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            'trans.Rollback()
        End Try
 
End Sub

The " **CLIENT UPDATED SUCCESSFULLY**" message appears on my form. The "Catch ex As Exception" does not execute. The returned form show my changes, but the BD is not updated. When I restart the program, the updates do not show.

Can anyone explain what I did wrong?

Thanks

 

[jmcilhinney]

I'm surprised that there isn't an exception there. I'm not sure whether the data is being saved or not but you can determine that for sure by testing the value returned by ExecuteNonQuery. If it's not zero then data was saved. Regardless the way you're building your SQL code is absolutely abominable. You should be using parameters to insert the values and an XML literal to actually store the text. That will make it far more readable, less error-prone and more secure. You could also make your SQL far less verbose by omitting redundant qualifiers and parentheses. Most importantly though, you put commas, not "AND", between fields to set in an UPDATE statement, or at least you do in every flavour of SQL that I've seen.

Dim sql = <sql>
              UPDATE tblClient
              SET I = @I,
                  P = @P
              WHERE CatNbr = @CatNbr
              AND ClientName = @ClientName
          </sql>

catCMD.CommandText = sql.Value

With catCMD.Parameters
    .AddWithValue("@I", strI)
    .AddWithValue("@P", strP)
    .AddWithValue("@CatNbr", gintCatSelectedItem)
    .AddWithValue("@ClientName", gstrClientName)
End With

 

[AccessShell]

I cannot enter lines 1 thru 7. I get several errors.
Let and Set assignments are no longer supported
WHERE is not declared
tblClient is not declared
squiggly blue line under = <sql>
Of course sql.value is not declared

Maybe I'm running an very old VB.NET and old Visual studio
MS Dev Env 2003 Version 7.1.3088
MS .NET Framework 1.1 Version 1.1.4322 SP1

Maybe I need a reference, or an Imports statement?

Obviously I have not gotten to lines 11 thru 16

 

[jmcilhinney]

Ah, I meant to enquire about the .NET 1.1 in your profile but forgot. Is there a particular reason that you're using such an old version? You can get VS 2015 Community Edition for free, so if you're only using an old one because you don't want to pay for an update, don't worry about cost.

If you really are stuck with VB.NET 2003 then there are no XML literals and, if I remember correctly, no AddWithValue method either. In that case, just use a String and the Add method:

Dim sql As String = "UPDATE tblClient SET I = @I, P = @P WHERE CatNbr = @CatNbr AND ClientName = @ClientName"
 
catCMD.CommandText = sql
 
With catCMD.Parameters
    .Add("@I", strI)
    .Add("@P", strP)
    .Add("@CatNbr", gintCatSelectedItem)
    .Add("@ClientName", gstrClientName)
End With

 

[jmcilhinney]

By the way, my use of "@" to prefix parameters assumes that you're using a Microsoft database. I'm guessing that you're using Access but, if it's something else, you may need to adjust that.

 

[AccessShell]

Thanks for the tip on VS 2015. I will look soon.

I am using an old MS Access DB (2003)

I tried the code you suggested and got no error messages, however the DB did not update. I forced the catch to execute and got an error number = 0, and a blank error message. I don't know what to try next.

BTW, In VB6 (I know, you already taught me that the two VBs are not the same), I was able to click on another line in the same procedure and set that line as the next line to execute during a code walkthrough(F5 and F8), how do I do this in VB.NET?

Thanks

 

[jmcilhinney]

I don't know what to try next.

You should try next what I told you to try first:

I'm not sure whether the data is being saved or not but you can determine that for sure by testing the value returned by ExecuteNonQuery. If it's not zero then data was saved.

That will tell you unequivocally whether the data is being saved or not. I strongly suspect that it is, but the fact that you said that deletes were working as expected casts some doubt. Without knowing exactly how you were testing, it's not possible to know for sure. If you do test that value and find that it's not zero then I pretty much know what the issue is and you should follow the first link in my signature below to find out how local data files are managed. To be honest, I can't recall whether there are and different considerations to be made in VB.NET 2003.

BTW, In VB6 (I know, you already taught me that the two VBs are not the same), I was able to click on another line in the same procedure and set that line as the next line to execute during a code walkthrough(F5 and F8), how do I do this in VB.NET?

I'm not sure whether it can be done using the keyboard but you can certainly do it using the mouse. When you hit a breakpoint, a yellow arrow is displayed on the line that is next to execute. You can drag that arrow to another line, either earlier or later, and have that line executed next. You need to be careful that you drag it to somewhere that makes sense, but I imagine that the considerations were similar in VB6. Again, I'm not sure if there is any difference in VB.NET 2003 to more recent versions.