Solved

ASP.NET Access Update Error

Posted on 2009-04-02
27
250 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

707 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