?
Solved

Creating DataAdapter UpdateCommand for FoxPro table returns error.

Posted on 2004-09-21
15
Medium Priority
?
310 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
Technology Partners: 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 2000 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

Technology Partners: 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 …
Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
There may be issues when you are trying to access Outlook or send & receive emails or due to Outlook crash which leads to corrupt or damaged PST file. To eliminate the corruption from your PST file, you need to repair the corrupt Outlook PST file. U…
Suggested Courses
Course of the Month9 days, 3 hours left to enroll

621 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