• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 268
  • Last Modified:

ASP.NET Access Update Error

When I run the below code I get a syntax error message about the update command. Can anyone tell me what I am doing wrong?
Dim con As New System.Data.OleDb.OleDbConnection
        Dim myPath As String
 
        myPath = Server.MapPath("App_Data/Visions_DB.mdb")
        con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data source=" & myPath & ";"
 
        Dim updCmd As New System.Data.OleDb.OleDbCommand
 
        updCmd.CommandText = "Update SET Data Present_Method=@Present_Method,Proposed_Method=@Proposed_Method,Status=@Status,Facilitator=@Facilitator,Validator=@Validator,Notes=@Notes,ValDate=@ValDate WHERE Vis_Num=@Vis_Num"
 
        updCmd.Parameters.AddWithValue("@Present_Method", txtPresent.Text)
        updCmd.Parameters.AddWithValue("@Proposed_Method", txtProposed.Text)
        updCmd.Parameters.AddWithValue("@Status", ddstatus.SelectedValue)
        updCmd.Parameters.AddWithValue("@Facilitator", ddFacilitator.SelectedValue)
        updCmd.Parameters.AddWithValue("@Validator", txtImplement.Text)
        updCmd.Parameters.AddWithValue("@Notes", txtNotes.Text)
        updCmd.Parameters.AddWithValue("ValDate", StartDatetxt.Text)
        updCmd.Parameters.AddWithValue("@Vis_Num", Request.QueryString("Vis_Num"))
 
        updCmd.Connection = con
        con.Open()
        updCmd.ExecuteNonQuery()
        con.Close()

Open in new window

0
ITHelper80
Asked:
ITHelper80
  • 8
  • 8
  • 6
  • +2
2 Solutions
 
rockiroadsCommented:
In your list of updCmd.Parameters.AddWithValue, all bar ValDate are prefixed with @

Does it need to be prefixed with @
?
0
 
rockiroadsCommented:
Also, with these parameter queries, can you not get away with specifying ? instead of @name?

something like this?

updCmd.CommandText = "Update SET Data Present_Method=?,Proposed_Method=?,Status=tus,Facilitator=?,Validator=?,Notes=?,ValDate=? WHERE Vis_Num=?"
 
        updCmd.Parameters.AddWithValue("Present_Method", txtPresent.Text)
        updCmd.Parameters.AddWithValue("Proposed_Method", txtProposed.Text)
        updCmd.Parameters.AddWithValue("Status", ddstatus.SelectedValue)
        updCmd.Parameters.AddWithValue("Facilitator", ddFacilitator.SelectedValue)
        updCmd.Parameters.AddWithValue("Validator", txtImplement.Text)
        updCmd.Parameters.AddWithValue("Notes", txtNotes.Text)
        updCmd.Parameters.AddWithValue("ValDate", StartDatetxt.Text)
        updCmd.Parameters.AddWithValue("Vis_Num", Request.QueryString("Vis_Num"))


0
 
ITHelper80Author Commented:
Thanks rockiroads,

Ill try your code.

Im new to this and still learning so can you explain the benefit of using ? over @
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
ITHelper80Author Commented:
I still recieve a syntax error for the Update command.

Does the update command care if its overwriting some of the fields with the same values? ie the user didnt change the field but its in the update command
0
 
jjamstrongCommented:
leave update command as is

just remove the @ in the following section

updCmd.Parameters.AddWithValue("Present_Method", txtPresent.Text)
updCmd.Parameters.AddWithValue("Proposed_Method", txtProposed.Text)
updCmd.Parameters.AddWithValue("Status", ddstatus.SelectedValue)
updCmd.Parameters.AddWithValue("Facilitator", ddFacilitator.SelectedValue)
updCmd.Parameters.AddWithValue("Validator", txtImplement.Text)
updCmd.Parameters.AddWithValue("Notes", txtNotes.Text)
updCmd.Parameters.AddWithValue("ValDate", StartDatetxt.Text)
updCmd.Parameters.AddWithValue("Vis_Num", Request.QueryString("Vis_Num"))
0
 
ITHelper80Author Commented:
Thanks for the suggestion.

Still get Syntax error in UPDATE statement.
0
 
ALaRivaCommented:
Here is the line that you are failing at:
updCmd.CommandText = "Update SET Data Present_Method=@Present_Method,Proposed_Method=@Proposed_Method,Status=@Status,Facilitator=@Facilitator,Validator=@Validator,Notes=@Notes,ValDate=@ValDate WHERE Vis_Num=@Vis_Num"

The update statement should follow this format
UPDATE [tablename] SET [field]=[value],[field]=[value]

My assumption is that your table is called "Data".  See attached code for modified statement.

- Anthony
updCmd.CommandText = "Update Data SET Present_Method=@Present_Method,Proposed_Method=@Proposed_Method,Status=@Status,Facilitator=@Facilitator,Validator=@Validator,Notes=@Notes,ValDate=@ValDate WHERE Vis_Num=@Vis_Num"

Open in new window

0
 
rockiroadsCommented:
Sorry for the delay. Went to grab some food.

The fields that you are using, do they all have a value in there?
0
 
MaxOvrdrv2Commented:
man... copy paste the code attached to my post and try that...

here i've assumed that "Data" is your table name... and that all of your fields in the DB are strings/varchar... if they're not varchar, you will need to typecast the parameter... example:

AddProfileCom.Parameters.Add(New System.Data.OleDb.OleDbParameter("contacts", System.Data.OleDb.OleDbType.Boolean, 2, "contacts"))

M
Dim con As New System.Data.OleDb.OleDbConnection
        Dim myPath As String
 
        myPath = Server.MapPath("App_Data/Visions_DB.mdb")
        con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data source=" & myPath & ";"
 
        Dim updCmd As New System.Data.OleDb.OleDbCommand
 
        updCmd.CommandText = "Update Data SET Present_Method=?,Proposed_Method=?,Status=?,Facilitator=?,Validator=?,Notes=?,ValDate=? WHERE Vis_Num=@Vis_Num"
 
        updCmd.Parameters.Add("Present_Method", txtPresent.Text)
        updCmd.Parameters.Add("Proposed_Method", txtProposed.Text)
        updCmd.Parameters.Add("Status", ddstatus.SelectedValue)
        updCmd.Parameters.Add("Facilitator", ddFacilitator.SelectedValue)
        updCmd.Parameters.Add("Validator", txtImplement.Text)
        updCmd.Parameters.Add("@Notes", txtNotes.Text)
        updCmd.Parameters.Add("ValDate", StartDatetxt.Text)
        updCmd.Parameters.Add("Vis_Num", Request.QueryString("Vis_Num"))
 
        updCmd.Connection = con
        con.Open()
        updCmd.ExecuteNonQuery()
        con.Close()
 

Open in new window

0
 
MaxOvrdrv2Commented:
crap... forgot a small piece on the query statement:

WHERE Vis_Num=?

please change it in the code i've provided.

thanks.
0
 
jjamstrongCommented:
Your Missing the table name in the update command...
0
 
rockiroadsCommented:
good spot jjamstrong, looks like you cracked it.

other thing though, does it work with @ or would you need to put ?. I had always thought you had to put ?
0
 
ITHelper80Author Commented:
The table name is Data.

Some of the fields that are being updated ie Notes, Validator, ValDate are empty int he database...is that a problem?

Max I tried you code and had to add and got an error about having the wrong datatype. Everything should be text though.
0
 
rockiroadsCommented:
syntax for update is not

updCmd.CommandText = "Update SET Data Present

but

updCmd.CommandText = "Update Data SET Present
0
 
rockiroadsCommented:
Looking at the earlier posts, you can see ALaRiva spotted this first
0
 
ITHelper80Author Commented:
Max

Ive tried your code but VSD 08 keeps making me add OleDbType..... and no matter which one I select the debug says I am trying to convert text to an integer which of course errors the whole thing out
0
 
rockiroadsCommented:
go back to your original code, ensure you have the sql in the right way
i.e. updat data set instead of update set data

see if it works or you get a different error. Also might be worth using ? as I know that is one way to use parameter queries.
0
 
jjamstrongCommented:
Sorry...
rockiroads your right it is ?.
@ is for SQL.
0
 
jjamstrongCommented:
But You can also use the @ in this case to specifie the name of the parameter that you are passing and the following should work. Just remember to make the necesary convertions because all that you are passing here is text, and I see that you even have a date field so you need to make the conversion.

updCmd.Parameters.AddWithValue("Present_Method", txtPresent.Text)
updCmd.Parameters.AddWithValue("Proposed_Method", txtProposed.Text)
updCmd.Parameters.AddWithValue("Status", ddstatus.SelectedValue)
updCmd.Parameters.AddWithValue("Facilitator", ddFacilitator.SelectedValue)
updCmd.Parameters.AddWithValue("Validator", txtImplement.Text)
updCmd.Parameters.AddWithValue("Notes", txtNotes.Text)
updCmd.Parameters.AddWithValue("ValDate", StartDatetxt.Text)
updCmd.Parameters.AddWithValue("Vis_Num", Request.QueryString("Vis_Num"))
0
 
ITHelper80Author Commented:
Ok now I am getting this error. Parameter ?_8 has no default value.



Dim con As New System.Data.OleDb.OleDbConnection
Dim myPath As String
 
myPath = Server.MapPath("App_Data/Visions_DB.mdb")
        con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data  source=" & myPath & ";"
 
        Dim updCmd As New System.Data.OleDb.OleDbCommand
 
        updCmd.CommandText = "Update Data SET Present_Method=?,Proposed_Method=?,Status=?,Facilitator=?,Validator=?,Notes=?,ValDate=? WHERE Vis_Num=?"
 
        updCmd.Parameters.AddWithValue("Present_Method", txtPresent.Text)
        updCmd.Parameters.AddWithValue("Proposed_Method", txtProposed.Text)
        updCmd.Parameters.AddWithValue("Status", ddstatus.SelectedValue)
        updCmd.Parameters.AddWithValue("Facilitator", ddFacilitator.SelectedValue)
        updCmd.Parameters.AddWithValue("Validator", txtImplement.Text)
        updCmd.Parameters.AddWithValue("Notes", txtNotes.Text)
        updCmd.Parameters.AddWithValue("ValDate", StartDatetxt.Text)
        updCmd.Parameters.AddWithValue("Vis_Num", Request.QueryString("Vis_Num"))
 
        updCmd.Connection = con
        con.Open()
        updCmd.ExecuteNonQuery()
        con.Close()

Open in new window

0
 
jjamstrongCommented:
I just tried and example here... I it gived me the error when I didn't convert the text in the controls to the respective data type of the table in the database... hope this helps
0
 
ITHelper80Author Commented:
All of the field types in in the Database are text minus the AutoID field
0
 
rockiroadsCommented:
Perhaps its worth validating the vis_num

Dim sVisNum As String = Request.QueryString("Vis_Num")

then check x has a value or not, maybe using len or something

if len(sVisNum) = 0 then
    msgbox "No vis num"
end if

0
 
jjamstrongCommented:
like what rockiroads said... Validate if it is getting the queryString... and after that I also recommend to convert it to Integer before asigning it to the parameter.
0
 
ITHelper80Author Commented:
Ok I got it with with help for you all. The last few posts made me realize I didnt need the request query string since that was already valid. I ran a query earlier in the app that populates the Vis_Num field.

jjamstrong and rockiroads,

I think its only to split the points between you both since each contributed to my solution. Thanks for all the effort.
        Dim con As New System.Data.OleDb.OleDbConnection
        Dim myPath As String
 
        myPath = Server.MapPath("App_Data/Visions_DB.mdb")
        con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data source=" & myPath & ";"
 
        Dim updCmd As New System.Data.OleDb.OleDbCommand
 
        updCmd.CommandText = "Update Data SET Present_Method=?,Proposed_Method=?,Status=?,Facilitator=?,Validator=?,Notes=?,ValDate=? WHERE Vis_Num=?"
 
 
        updCmd.Parameters.AddWithValue("Present_Method", txtPresent.Text)
        updCmd.Parameters.AddWithValue("Proposed_Method", txtProposed.Text)
        updCmd.Parameters.AddWithValue("Status", ddstatus.SelectedValue)
        updCmd.Parameters.AddWithValue("Facilitator", ddFacilitator.SelectedValue)
        updCmd.Parameters.AddWithValue("Validator", txtImplement.Text)
        updCmd.Parameters.AddWithValue("Notes", txtNotes.Text)
        updCmd.Parameters.AddWithValue("ValDate", StartDatetxt.Text)
        updCmd.Parameters.AddWithValue("Vis_Num", txtVisNum.Text)
 
        updCmd.Connection = con
        con.Open()
        updCmd.ExecuteNonQuery()
        con.Close()

Open in new window

0
 
MaxOvrdrv2Commented:
i'm sorry but i'm going to have to contest this... your code is an EXACT match to mine above... yet you completely ignored my post and continued to try to work it out with the other 2...
0
 
MaxOvrdrv2Commented:
nevermind... missed rockis post above mine.

Cheers!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 8
  • 8
  • 6
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now