We help IT Professionals succeed at work.

ASP.NET Access Update Error

286 Views
Last Modified: 2012-06-21
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

Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2006

Commented:
In your list of updCmd.Parameters.AddWithValue, all bar ValDate are prefixed with @

Does it need to be prefixed with @
?
CERTIFIED EXPERT
Top Expert 2006
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks rockiroads,

Ill try your code.

Im new to this and still learning so can you explain the benefit of using ? over @

Author

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
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"))

Author

Commented:
Thanks for the suggestion.

Still get Syntax error in UPDATE statement.

Commented:
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

CERTIFIED EXPERT
Top Expert 2006

Commented:
Sorry for the delay. Went to grab some food.

The fields that you are using, do they all have a value in there?
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

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

WHERE Vis_Num=?

please change it in the code i've provided.

thanks.
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Top Expert 2006

Commented:
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 ?

Author

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.
CERTIFIED EXPERT
Top Expert 2006

Commented:
syntax for update is not

updCmd.CommandText = "Update SET Data Present

but

updCmd.CommandText = "Update Data SET Present
CERTIFIED EXPERT
Top Expert 2006

Commented:
Looking at the earlier posts, you can see ALaRiva spotted this first

Author

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
CERTIFIED EXPERT
Top Expert 2006

Commented:
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.
Sorry...
rockiroads your right it is ?.
@ is for SQL.
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"))

Author

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

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

Author

Commented:
All of the field types in in the Database are text minus the AutoID field
CERTIFIED EXPERT
Top Expert 2006

Commented:
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

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.

Author

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

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...
nevermind... missed rockis post above mine.

Cheers!

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.