-
Sep 6th, 2014, 06:58 PM
#1
Thread Starter
New Member
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.
-
Sep 6th, 2014, 08:41 PM
#2
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.
-
Sep 8th, 2014, 03:22 AM
#3
Fanatic Member
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
-
Sep 8th, 2014, 09:21 PM
#4
Thread Starter
New Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|