needs help. SQL UPDATE for MSAccess

[DeanSmith]

Hi all, I am just relearning VB and so far it is a long road, many things have changed since v4. All of this .NET is new to me so please bear with me. I have been seraching the web for tips and help but i have hit a stumbling block that I see no reason why it should fail.

I am using VB Express trying to connect to a local Access database to update a field that i specifiy. This is just the begining of the project, in the long run the project will be taking command promt variables to fill in the database names so I am trying to avoid tying the database to the program with Add New DataSource from the menu bar.

Well, here is the code snippet that is giving me problems. Some of the variable are defined elsewhere and no errors pop up when running the program.


cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='C:\database\axx_ss.mdb'"

updateString = "UPDATE " & tableNameString & " SET UploadDateTime = '" & Now & "' WHERE ID = " & dr(0)
cn.Open()
Dim updateCmd As New OleDbCommand(updateString, cn)
updateCmd.ExecuteNonQuery()

' Return a success message.
If rows_affected <= 1 Then
Res.Text = "Ok." & vbCrLf
Else
Res.Text = "Ok. " & rows_affected & " rows affected" & vbCrLf
End If

cn.Close()




updateString = "UPDATE UUT_RESULT SET UploadDateTime = '1/18/2007 12:26:25 PM' WHERE ID = 142"

The catch error I get is : No value given for one or more required parameters.

I just can't see any problems.
Thanks for any help
Dean

 

[jhomminga]

A demo app to look at

I don't see anything wrong. Have you tried debugging, setting breakpoints and what line actually fails?

I have a functional demo you can look at. It uses a dataset to fill and then send updates back to the database.

HTH

 

[DeanSmith]

Yep, tried setting breakpoints and stepping through.

updateCmd.ExecuteNonQuery()

Is the line that generates the error for the Catch. At least it isn't something obviose that someone else didn't see right off the bat.

 

[jhomminga]

Maybe change connection string?

FROM
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='C:\database\axx_ss.mdb'"

TO
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\database\axx_ss.mdb; User id=admin;"

 

[lingsn]

I am guessing this line got problems:

updateString = "UPDATE " & tableNameString & " SET UploadDateTime = '" & Now & "' WHERE ID = " & dr(0)

In MS Access, if you use the string literal to concat the value to a string for sql execution, and if you are tring to concat the date, you have to use the "#" instead of "'". For example:

updateString = "UPDATE " & tableNameString & " SET UploadDateTime = #" & Format(Now,"dd/MM/yyyy H:mm:ss") & "# WHERE ID = " & dr(0)

 

[DeanSmith]

I tried changing both the connection string and adding the # signs for the date concatenation but neither suggestion helped. Still failing at the ExecuteNonQuery().

Thanks for the suggestions though.

 

[DeanSmith]

Ok, everbody ignore me. I was doing something REALLY STUPID. It is working now.