Clear Arguments for Stored Procedure 2

[UncleCake]

Hi, I need to loop through many invoices which have many items on them. The problem that I ran into is that I need to clear out the arguments for the stored procedure prior to adding new arguments. I tried looking for something with cdm.? but I didn't see anything that would allow me to clear the arguments.

I have built this test in the code below. The first SP works fine, but when I run the second one it has too many arguments, therefore I need to clear them before I start my second SP. I hope that is clear???

    Dim cnConn As New ADODB.Connection
    Dim cmd As New ADODB.Command
    Dim prm As New ADODB.Parameter
    
    cnConn.Open "Driver={SQL Server};Server=hhb;Database=HHb;uid=jk;pwd=jk;"
    cmd.ActiveConnection = cnConn

    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "sp_ScannerInsertInvoice"
    cmd.Parameters.Append cmd.CreateParameter("Invoice", adChar, adParamInput, 7, "G10015")
    cmd.Parameters.Append cmd.CreateParameter("TheDate", adDBTimeStamp, adParamInput, 0, "01/01/2005")
    cmd.Parameters.Append cmd.CreateParameter("EmployeeID", adInteger, adParamInput, , 1001)
    cmd.Execute

' I NEED TO CLEAR ARGUMENTS HERE, OR IS THERE A BETTER WAY?
    
    cmd.CommandText = "sp_ScannerInsertInvoiceItem"
    cmd.Parameters.Append cmd.CreateParameter("Invoice", adChar, adParamInput, 7, "G10011")
    cmd.Parameters.Append cmd.CreateParameter("RecNo", adInteger, adParamInput, 9, 555555)
    cmd.Parameters.Append cmd.CreateParameter("ItemNum", adChar, adParamInput, 50, "OG5050")
    cmd.Parameters.Append cmd.CreateParameter("Location", adChar, adParamInput, 50, "A100")
    cmd.Parameters.Append cmd.CreateParameter("Qty", adInteger, adParamInput, 9, 11)
    cmd.Execute
    
    Set cmd = Nothing

 

[HarleyQuinn]

Could you use the cmd.Parameter.Delete... arguement?

HarleyQuinn
---------------------------------
Help us to help you,
read FAQ222-2244 before posting.

 

[fredericofonseca]

The way you have defined your cmd object is fine if you only have one record to add at the time.

If you have many records to add (and you item invoice is such a case), then you should do something like this.


define one cmd object for each SP.
e.g.

Define the above as global variables.
Dim cnConn As ADODB.Connection
Dim cmd_invoice As ADODB.Command
Dim cmd_invoice_item As ADODB.Command
dim bcmd_invoice_created as boolean

sub insert_invoice()

'Do you connection thing if you need. I use global connections

if not bcmd_invoice_created then
bcmd_invoice_created = true
set cmd_invoice = new ADODB.command
cmd_invoice.CommandType = adCmdStoredProc
cmd_invoice.CommandText = "sp_ScannerInsertInvoice"
cmd_invoice.Parameters.Append cmd_invoice.CreateParameter("Invoice", adChar, adParamInput, 7)
cmd_invoice.Parameters.Append cmd_invoice.CreateParameter("TheDate", adDBTimeStamp, adParamInput, 25)
cmd_invoice.Parameters.Append cmd_invoice.CreateParameter("EmployeeID", adInteger, adParamInput, 9)

set cmd_invoice_item = new ADODB.command
cmd_invoice_item.CommandType = adCmdStoredProc
cmd_invoice_item.CommandText = "sp_ScannerInsertInvoiceItem"
cmd_invoice_item.Parameters.Append cmd_invoice_item.CreateParameter("Invoice", adChar, adParamInput, 7)
cmd_invoice_item.Parameters.Append cmd_invoice_item.CreateParameter("RecNo", adInteger, adParamInput, 9)
cmd_invoice_item.Parameters.Append cmd_invoice_item.CreateParameter("ItemNum", adChar, adParamInput, 50)
cmd_invoice_item.Parameters.Append cmd_invoice_item.CreateParameter("Location", adChar, adParamInput, 50)
cmd_invoice_item.Parameters.Append cmd_invoice_item.CreateParameter("Qty", adInteger, adParamInput, 9)

end if
With cmd_invoice.Parameters
.Item("Invoice").Value = my_invoice_variable
.Item("TheDate").Value = my_invoice_date_variable
.Item("EmployeeID").Value = my_employee_variable

.Execute
end with

loop invoice_item (i)
With cmd_invoice_item.Parameters(i)
.Item("Invoice").Value = my_invoice_variable(i)
.Item("RecNo").Value = my_RecNo_date_variable(i)
.Item("ItemNum").Value = my_ItemNum_variable(i)

.Execute
end with
next item

' DO NOT set the command objects to nothing, as they will be reused the next time you insert an invoice.

if you with to use the some command object then you will need to reset it every time, and on your particular sample code you would need to "set cmd = nothing" before setting the next SP, or you would need to loop on the cmd.parameters collection and delete each individual parameter manually.
e.g.
for i = 0 to cmd.parameter.count - 1
cmd.parameter(i).delete
next i

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[HarleyQuinn]

I think I may have misunderstood the OP there.

HarleyQuinn
---------------------------------
Help us to help you,
read FAQ222-2244 before posting.

 

[HarleyQuinn]

Or maybe I didn't, I've confused myself there...

HarleyQuinn
---------------------------------
Help us to help you,
read FAQ222-2244 before posting.

 

[fredericofonseca]

No you didn't. the .delete was a correct reply to what the OP was asking, but I don't think it is the best solution

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[UncleCake]

Harley, you didn't misunderstand it. I can use the following after each execute and it works.

           For i = 0 To cmd.Parameters.Count - 1
                cmd.Parameters.Delete (0)
            Next i

Frederico, is your suggestion a better or more proper way of doing it, or just a different way. I see how it works, but would it be faster?

I have not worked with SP before, so that is why I am asking.

-UncleCake

 

[HarleyQuinn]

Thanks Frederico for clearing that up, it's awful having a nagging doubt when you second guess yourself. I also agree, it doesn't seem the best way to go about it, that's where the second guessing and doubt started to appear...

HarleyQuinn
---------------------------------
Help us to help you,
read FAQ222-2244 before posting.

 

[UncleCake]

Frederico, I also will be looping through many invoices which have many items on them. Therefore, I would have to delete the invoice and item information at the end of every invoice.

This program is used to pull invoices and items from a Btrieve database and put them into a SQL Server database so I have access to them with a barcode scanner with Windows Media 2003 which doesn't have a driver to access the Btrieve database. What a pain!

 

[strongm]

If you really do want to clear all the parameters, you can use a loop like this:

[blue]Do Until cmd.Parameters.Count = 0
    cmd.Parameters.Delete 0
Loop[/blue]

 

[fredericofonseca]

UncleCake,

I did get you needs correctly.
Do as per my example, as creating the objects and parameters for each invoice is a waste of time and resources.

And Yes it will be faster, as the parameters will already have been created for the second and subsequente times you add either an invoice header or invoice line.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[UncleCake]

Frederico,

Thanks, I will do that!

-UncleCake

 

[strongm]

That'll teach me to check back with the thread before posting ...

 

[UncleCake]

Frederico,

This is what I ended up with and it works great. Thank you all for your help!

-UncleCake

Private Sub Command3_Click()
    Dim cnConn As New ADODB.Connection
    
    Dim cmd_invoice As ADODB.Command
    Dim cmd_invoice_item As ADODB.Command
    
    Dim i As Integer
    Dim x As Integer
    
    cnConn.Open "Driver={SQL Server};Server=hhb;Database=HHb;uid=jk;pwd=jk;"
    Set cmd_invoice = New ADODB.Command
    cmd_invoice.ActiveConnection = cnConn
    cmd_invoice.CommandType = adCmdStoredProc
    cmd_invoice.CommandText = "sp_ScannerInsertInvoice"
    cmd_invoice.Parameters.Append cmd_invoice.CreateParameter("Invoice", adVarChar, adParamInput, 6)
    cmd_invoice.Parameters.Append cmd_invoice.CreateParameter("InvDate", adDBTimeStamp, adParamInput, 0)
    cmd_invoice.Parameters.Append cmd_invoice.CreateParameter("EmployeeID", adInteger, adParamInput, 4)
    
    Set cmd_invoice_item = New ADODB.Command
    cmd_invoice_item.ActiveConnection = cnConn
    cmd_invoice_item.CommandType = adCmdStoredProc
    cmd_invoice_item.CommandText = "sp_ScannerInsertInvoiceItem"
    cmd_invoice_item.Parameters.Append cmd_invoice_item.CreateParameter("Invoice", adVarChar, adParamInput, 7)
    cmd_invoice_item.Parameters.Append cmd_invoice_item.CreateParameter("RecNo", adInteger, adParamInput, 9)
    cmd_invoice_item.Parameters.Append cmd_invoice_item.CreateParameter("ItemNum", adVarChar, adParamInput, 50)
    cmd_invoice_item.Parameters.Append cmd_invoice_item.CreateParameter("Location", adVarChar, adParamInput, 50)
    cmd_invoice_item.Parameters.Append cmd_invoice_item.CreateParameter("Qty", adVarChar, adParamInput, 9)

    
    
    For i = 0 To 3
        With cmd_invoice.Parameters
            .Item("Invoice").Value = CStr(100 + i)
            .Item("InvDate").Value = 12345
            .Item("EmployeeID").Value = 1001
        End With
        cmd_invoice.Execute
        
        For x = 1 To 3
          With cmd_invoice_item.Parameters
            .Item("Invoice").Value = CStr(100 + i)
            .Item("RecNo").Value = 200 + x
            .Item("ItemNum").Value = "OG5099"
            .Item("Location").Value = "Here"
            .Item("Qty").Value = "A100"
          End With

          cmd_invoice_item.Execute
        Next x
    Next i

End Sub