Solved

Creating DataAdapter UpdateCommand for FoxPro table returns error.

Posted on 2004-09-21
15
305 Views
Last Modified: 2010-04-23
I'm trying to create an UpdateCommand to update my FoxPro table.  The table in FoxPro does not have an ID field but I thought I could build the updateCommand myself.  My code is as follows:

 Dim cmdUpdateSO As New OleDbCommand("Update SoMagazine SET status = @status, " & _
                "rollstatus = @rollstatus, cpono = @cpono, qty = @qty where " & _
                "caddrno = @caddrno and barcode = @barcode", cnVam2)
            Dim prmUpdateSO As New OleDbParameter
            prmUpdateSO = cmdSoMag.Parameters.Add("@status", OleDbType.Char, 2, "status")
            prmUpdateSO = cmdSoMag.Parameters.Add("@rollstatus", OleDbType.Char, 2, "rollstatus")
            prmUpdateSO = cmdSoMag.Parameters.Add("@cpono", OleDbType.Char, 20, "cpono")
            prmUpdateSO = cmdSoMag.Parameters.Add("@caddrno", OleDbType.Char, 10, "caddrno")
            prmUpdateSO = cmdSoMag.Parameters.Add("@barcode", OleDbType.Char, 20, "barcode")
            prmUpdateSO.SourceVersion = DataRowVersion.Original
            daUpdateSo.UpdateCommand = cmdUpdateSO

Is what I'm trying to do even possible?  If so, what am I doing wrong?
0
Comment
Question by:britttd
  • 8
  • 7
15 Comments
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12112155
shouldn't it be like this

Dim cmdUpdateSO As New OleDbCommand("Update SoMagazine SET status = @status, " & _
                "rollstatus = @rollstatus, cpono = @cpono, qty = @qty where " & _
                "caddrno = @caddrno and barcode = @barcode", cnVam2)
            cmdupdateSO.Parameters.Add("@status", OleDbType.Char, 2, "status")
            cmdupdateSO.Parameters.Add("@rollstatus", OleDbType.Char, 2, "rollstatus")
            cmdupdateSO.Parameters.Add("@cpono", OleDbType.Char, 20, "cpono")
            cmdupdateSO.Parameters.Add("@caddrno", OleDbType.Char, 10, "caddrno")
            cmdupdateSO.Parameters.Add("@barcode", OleDbType.Char, 20, "barcode")
            daUpdateSo.UpdateCommand = cmdUpdateSO
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12112191
sorry

Dim cmdUpdateSO As New OleDbCommand("Update SoMagazine SET status = @status, " & _
                "rollstatus = @rollstatus, cpono = @cpono, qty = @qty where " & _
                "caddrno = @caddrno and barcode = @barcode", cnVam2)
            cmdupdateSO.Parameters.Add(New System.Data.OleDb.OleDbParameter(
"@status", OleDbType.Char, 2, "status"))
            cmdupdateSO.Parameters.Add(New System.Data.OleDb.OleDbParameter(
"@rollstatus", OleDbType.Char, 2, "rollstatus"))
            cmdupdateSO.Parameters.Add(New System.Data.OleDb.OleDbParameter(
"@cpono", OleDbType.Char, 20, "cpono"))
            cmdupdateSO.Parameters.Add(New System.Data.OleDb.OleDbParameter(
"@caddrno", OleDbType.Char, 10, "caddrno"))
            cmdupdateSO.Parameters.Add(New System.Data.OleDb.OleDbParameter(
"@barcode", OleDbType.Char, 20, "barcode"))
            daUpdateSo.UpdateCommand = cmdUpdateSO
0
 

Author Comment

by:britttd
ID: 12112583
Still getting a 'System.Data.OleDb.OleDbException' error.
0
Industry Leaders: 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!

 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12113054
If you put a try catch around your code , what is the exact message

try
'yourcode
catch ex as exception
msgbox(ex.message)
end try
0
 

Author Comment

by:britttd
ID: 12113128
I get "Syntax Error".  
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12113241
another try

Dim cmdUpdateSO As New OleDbCommand("Update SoMagazine SET status =  ?, " & _
                "rollstatus = ?, cpono = ?, qty = ? where " & _
                "caddrno = ? and barcode = ?", cnVam2)
            cmdupdateSO.Parameters.Add(New System.Data.OleDb.OleDbParameter(
"status", OleDbType.Char, 2, "status"))
            cmdupdateSO.Parameters.Add(New System.Data.OleDb.OleDbParameter(
"rollstatus", OleDbType.Char, 2, "rollstatus"))
            cmdupdateSO.Parameters.Add(New System.Data.OleDb.OleDbParameter(
"cpono", OleDbType.Char, 20, "cpono"))
            cmdupdateSO.Parameters.Add(New System.Data.OleDb.OleDbParameter(
"caddrno", OleDbType.Char, 10, "caddrno"))
            cmdupdateSO.Parameters.Add(New System.Data.OleDb.OleDbParameter(
"barcode", OleDbType.Char, 20, "barcode"))
            daUpdateSo.UpdateCommand = cmdUpdateSO
0
 

Author Comment

by:britttd
ID: 12113331
Think it's close now.  I get "SQL Column 'Q0P5' is not found" as an error.
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12113354
is column Q0P5 part of your table ?
0
 

Author Comment

by:britttd
ID: 12113379
No.  It is not part of the table.  There are other fields in the table, but they would never change so I didn't create parameters for them.  Do I need to create parameters for each field in the table?
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12113479
do you know what this Q0P5 is then ?
0
 

Author Comment

by:britttd
ID: 12113492
I found this part out.  I left out the parameter "qty".  Once I added it as a numeric I am getting a data type mismatch.  Can't figure this out yet though.  
0
 
LVL 25

Accepted Solution

by:
RonaldBiemans earned 500 total points
ID: 12113576
So what is the error saying now.
0
 

Author Comment

by:britttd
ID: 12113933
Now I am getting a "Concurrency violation.  updatecommand affected 0 records" even show the dataset has changed.  Any ideas?
0
 
LVL 25

Expert Comment

by:RonaldBiemans
ID: 12120638
Hmmm, concurrency violations, usually means that somebody else has changed the data after you loaded your dataset,
is that possible ? (although I can guess your answer ;-))
0
 

Author Comment

by:britttd
ID: 12122487
No it's not possible for this.  I figured it out though last night.  Thanks for your help.
0

Featured Post

Industry Leaders: 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

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

685 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