Results 1 to 4 of 4

Thread: Updating a few fields within an MS ACCESS table.

  1. #1

    Thread Starter
    New Member
    Join Date
    Jul 2008
    Posts
    5

    Question 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
    Last edited by WisconsinMan; Sep 6th, 2014 at 08:29 PM.

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Updating a few fields within an MS ACCESS table.

    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.

  3. #3
    Fanatic Member
    Join Date
    Oct 2011
    Location
    Sydney, Australia
    Posts
    756

    Re: Updating a few fields within an MS ACCESS table.

    this string is not right

    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"
    My CodeBank Submissions
    • Listbox with transparency and picture support - Click Here
    • Check for a true internet connection - Click Here
    • Open Cash drawer connected to receipt printer - Click Here
    • Custom color and size border around form - Click Here
    • Upload file to website without user logins, includes PHP - Click Here
    • List All Removable USB Storage Devices - Click Here
    • Custom On/Off Slide Control - Click Here
    • Insert multiple rows of data into one database table using parameters - Click Here
    • Trigger USB/Serial Cash Drawer - Click Here

  4. #4

    Thread Starter
    New Member
    Join Date
    Jul 2008
    Posts
    5

    Re: Updating a few fields within an MS ACCESS table.

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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width