HOW TO UPDATE SQL DATABASE FROM VB USING STORED PROCEDURE
Posted on 2006-04-27
I need help from you experts especially those with strong VB and SQL backgounds on how I can update an SQL database table using stored procedure, with data residing in another table in the same SQL database.
There is a date field in the SQL database that requires to be updated, but VB doesn't like it. The stored procedure bit is not working. I know there should be more code before it but I need your assistance there.
May one of the experts assist, I'd really appreciate it.
Here is my code.
Dim newCnn As ADODB.Connection
dim date as Date
Dim Part_no, Lcation, Qty, Cost, Flight_code, Results, Notes
Private Sub cmdStart_Click()
Set rstPLT = New ADODB.Recordset
Set rstPLT = adoPLT.Recordset
Do Until .EOF
Date = rstPLT!Doc_date
Part_no = rstPLT!Part_no
Location = rstPLT!Cart_no
Qty = rstPLT!Sale_qty
Cost = rstPLT!Sale_price
Flight_code = rstPLT!Aircraft_type
Results = rstPLT!status
Notes = rstPLT!flight_no
' *** invoke the sql stored procedure
Set newCnn = New ADODB.Connection
newCnn.Open ConnectionString:="File Name=" & "C:\PLTComp.UDL"
newCnn.Execute "ANG_Issues_Create_sp @date, @Part_no, @Location, @Qty, @Cost," & _
"@Flight_code, @notes, @result OUT"
Cntr = Cntr + 1
MsgBox ("System FILEs UPLOADED WITH " & Cntr & " RECORDS"), vbInformation
'*** Display the datagrid again with the updated status records
Set .DataSource = adoPLT
.Visible = True
.Caption = "Listing of Exported and Updated Inventory Sales Records"
Set rstPLT = Nothing