Posted on 2006-04-27
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

    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

    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.
    LVL 9

    Expert Comment

    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

    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

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
    Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
    An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
    In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

    734 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

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now