Updating a few fields within an MS ACCESS table.

Hi everyone!

I am trying to update a few fields with data. In an MS ACCESS db.

I'm sure I have the syntax of the UPDATE command wrong. Can someone PLEASE tell me what I am doing wrong?
Here's the code.

Code:

---

Public Sub SaveDB()
        ' This Function Saves the config into the database
        Dim databaseName As String = Application.StartupPath & "\CONFIG.DAT"

        Dim ServerName, NetID As String
        ServerName = frmConfig.txtServerName.Text
        NetID = frmConfig.txtNetID.Text
        Dim constring As String = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source =" & databaseName
        Using myconnection As New OleDbConnection(constring)
            myconnection.Open()
            Dim sqlQry As String = "UPDATE SystemData SET ServerName = '" & frmConfig.txtServerName.Text & "', ServerID = '" & frmConfig.txtNetID.Text & '" & WHERE configid=1;'"

          Using cmd As New OleDbCommand(sqlQry, myconnection)
                cmd.Parameters.AddWithValue("@ServerName", ServerName)
                cmd.Parameters.AddWithValue("@NetID", NetID)
                cmd.ExecuteNonQuery()
            End Using
        End Using
End Sub

---

You're adding parameters to your command but you have no parameters in your SQL code. Where you're inserting the values into your SQL code, you need to use the names of your parameters, i.e.

Code:

---

UPDATE SystemData SET ServerName = @ServerName, ServerID = @NetID WHERE configid=1

---

Note that there's no string concatenation and no spurious single quotes, semicolons or ampersands. Also, if your column is named ServerID then I'd suggest that you use @ServerID for the parameter. It's not essential but I always match the parameters to the columns if possible.

By the way, I'd suggest that you change this:

Code:

---

Dim databaseName As String = Application.StartupPath & "\CONFIG.DAT"

'...

Dim constring As String = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source =" & databaseName

---

to this:

Code:

---

Dim constring As String = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\CONFIG.DAT"

---

In fact, I'd suggest that you store that connection string in the config file too.

this string is not right

Quote:

---

Dim sqlQry As String = "UPDATE SystemData SET ServerName = '" & frmConfig.txtServerName.Text & "', ServerID = '" & frmConfig.txtNetID.Text & '" & WHERE configid=1;'"

---

it should be used with parameters as JM says but for it to work as is then

Code:

---

Dim sqlQry As String = "UPDATE SystemData SET ServerName = '" & frmConfig.txtServerName.Text & "', ServerID = '" & frmConfig.txtNetID.Text & "' WHERE configid=1"

---

Thanks so much for the help!! It worked like a charm! Thank again!