[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now



Posted on 2006-04-27
Medium Priority
Last Modified: 2008-01-09
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
    With rstPLT
        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
    End With
    MsgBox ("System  FILEs UPLOADED WITH " & Cntr & " RECORDS"), vbInformation
    '*** Display the datagrid again with the updated status records
    With dbgridExport
         Set .DataSource = adoPLT
         .Visible = True
         .Caption = "Listing of Exported and Updated Inventory Sales Records"
    End With
    Set rstPLT = Nothing
End Sub
Question by:DVEAPI
LVL 14

Accepted Solution

nayernaguib earned 1500 total points
ID: 16560059
You need to pass parameter values to the stored procedure coupled with parameter names. For example:

  newCnn.Execute "ANG_Issues_Create_sp @date=" & Date & ", @Part_no=" & Part_no & ...


  Nayer Naguib

Expert Comment

ID: 16562107
To avoid lots and lots of confusing errors, always format the date before passing it to the server.

SQLdate = Format(Date, "yyyymmdd hh:mm")
(or whatever format your server expects.)

The localized Date format of the machine asking is not necessary the same as the SQL server reciving the question.
Should not matter, should be handled by the ADO, but know for a fact that it does help, at least when dealing with Oracle servers.

Expert Comment

ID: 16563212
Note that:
    Set rstPLT = New ADODB.Recordset
    Set rstPLT = adoPLT.Recordset
The first line is redundant - you may as well delete it - the first line creates a recordset, the second line deletes it again and assigns adoPlt.Recordset to it!.

You would be better off using a command object otherwise you will not get the OUT parameter!

Dim pCmd as Command
set pCmd = new etc
pCmd.CommandText = "{? = ANG_Issues_Create_sp (?, ?, ?, ?, ?, ?, ?)}"

pCmd.Parameters(1) = rstPLT!Doc_date
result = pCmd.Execute

will then retrieve the return value.


Author Comment

ID: 16574360
Thankyou all for your answers, but the one posted by Nayer is more appropriate as it goes well with a speciall .dll file. Using SQL stored procedure in VB has been a pain from the start of my new project as its a new and more challenging area I am entering.

As for Gabeso, I 've taken note of your comments. First line is gone from my code. Thankyou.

For Paul, I'm now storing the data field in a memory variable called mdate, and not date as speciafied in the sored procedure.

But I really appreciate all the comments that have been posted.

Thankyou all again.

Best regards,

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is – how do I become a web developer?
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

873 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