britttd
asked on
Creating DataAdapter UpdateCommand for FoxPro table returns error.
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("@
prmUpdateSO = cmdSoMag.Parameters.Add("@
prmUpdateSO = cmdSoMag.Parameters.Add("@
prmUpdateSO = cmdSoMag.Parameters.Add("@
prmUpdateSO = cmdSoMag.Parameters.Add("@
prmUpdateSO.SourceVersion = DataRowVersion.Original
daUpdateSo.UpdateCommand = cmdUpdateSO
Is what I'm trying to do even possible? If so, what am I doing wrong?
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("@
prmUpdateSO = cmdSoMag.Parameters.Add("@
prmUpdateSO = cmdSoMag.Parameters.Add("@
prmUpdateSO = cmdSoMag.Parameters.Add("@
prmUpdateSO = cmdSoMag.Parameters.Add("@
prmUpdateSO.SourceVersion = DataRowVersion.Original
daUpdateSo.UpdateCommand = cmdUpdateSO
Is what I'm trying to do even possible? If so, what am I doing wrong?
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
"@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
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
ASKER
Still getting a 'System.Data.OleDb.OleDbEx
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
try
'yourcode
catch ex as exception
msgbox(ex.message)
end try
ASKER
I get "Syntax Error".
another try
Dim cmdUpdateSO As New OleDbCommand("Update SoMagazine SET status = ?, " & _
"rollstatus = ?, cpono = ?, qty = ? where " & _
"caddrno = ? and 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
Dim cmdUpdateSO As New OleDbCommand("Update SoMagazine SET status = ?, " & _
"rollstatus = ?, cpono = ?, qty = ? where " & _
"caddrno = ? and 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
ASKER
Think it's close now. I get "SQL Column 'Q0P5' is not found" as an error.
is column Q0P5 part of your table ?
ASKER
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?
do you know what this Q0P5 is then ?
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Now I am getting a "Concurrency violation. updatecommand affected 0 records" even show the dataset has changed. Any ideas?
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 ;-))
is that possible ? (although I can guess your answer ;-))
ASKER
No it's not possible for this. I figured it out though last night. Thanks for your help.
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
cmdupdateSO.Parameters.Add
cmdupdateSO.Parameters.Add
cmdupdateSO.Parameters.Add
cmdupdateSO.Parameters.Add
daUpdateSo.UpdateCommand = cmdUpdateSO