Solved

ASP.NET Access Update Error

Posted on 2009-04-02
27
236 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

0
Comment
Question by:ITHelper80
  • 8
  • 8
  • 6
  • +2
27 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 24051091
In your list of updCmd.Parameters.AddWithValue, all bar ValDate are prefixed with @

Does it need to be prefixed with @
?
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 250 total points
ID: 24051143
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
 
LVL 6

Author Comment

by:ITHelper80
ID: 24051170
Thanks rockiroads,

Ill try your code.

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

Author Comment

by:ITHelper80
ID: 24051251
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
 
LVL 2

Expert Comment

by:jjamstrong
ID: 24051354
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
 
LVL 6

Author Comment

by:ITHelper80
ID: 24051413
Thanks for the suggestion.

Still get Syntax error in UPDATE statement.
0
 
LVL 10

Expert Comment

by:ALaRiva
ID: 24051664
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 24051781
Sorry for the delay. Went to grab some food.

The fields that you are using, do they all have a value in there?
0
 
LVL 10

Expert Comment

by:MaxOvrdrv2
ID: 24052121
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
 
LVL 10

Expert Comment

by:MaxOvrdrv2
ID: 24052150
crap... forgot a small piece on the query statement:

WHERE Vis_Num=?

please change it in the code i've provided.

thanks.
0
 
LVL 2

Assisted Solution

by:jjamstrong
jjamstrong earned 250 total points
ID: 24052419
Your Missing the table name in the update command...
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24052521
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
 
LVL 6

Author Comment

by:ITHelper80
ID: 24052561
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 65

Expert Comment

by:rockiroads
ID: 24052579
syntax for update is not

updCmd.CommandText = "Update SET Data Present

but

updCmd.CommandText = "Update Data SET Present
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24052596
Looking at the earlier posts, you can see ALaRiva spotted this first
0
 
LVL 6

Author Comment

by:ITHelper80
ID: 24052660
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 24052705
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
 
LVL 2

Expert Comment

by:jjamstrong
ID: 24052777
Sorry...
rockiroads your right it is ?.
@ is for SQL.
0
 
LVL 2

Expert Comment

by:jjamstrong
ID: 24052899
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
 
LVL 6

Author Comment

by:ITHelper80
ID: 24052902
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
 
LVL 2

Expert Comment

by:jjamstrong
ID: 24052911
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
 
LVL 6

Author Comment

by:ITHelper80
ID: 24052930
All of the field types in in the Database are text minus the AutoID field
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24052999
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
 
LVL 2

Expert Comment

by:jjamstrong
ID: 24053209
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
 
LVL 6

Author Comment

by:ITHelper80
ID: 24053479
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
 
LVL 10

Expert Comment

by:MaxOvrdrv2
ID: 24058667
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
 
LVL 10

Expert Comment

by:MaxOvrdrv2
ID: 24058672
nevermind... missed rockis post above mine.

Cheers!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now