Macro to Populate Excel data from a SQL Server Database

[mkdev2007]

I am trying to get my macro working that loops through a list of rows on my excell spreadsheet that contain Serial Numbers on an Excel spreadsheet and uses the serial number as a parameter to a stored procedure in my SQL Server Database to lookup the other information but when I try to run it its saying that my stored procedure has too many arguments specified. When I comment out the following line
Call cmd.Parameters.Append(param)
then it says that I have not supplied the value for the parameter.

Here is my macro code:

Sub GetSerialData()
' GetSerial Data Macro
' Goes an reads the SQL Server Database to lookup the Part Number, Lot Number, Product Number, Date Received, Qty, and ROHS Status
'
' Create a connection object.

Dim con As New ADODB.Connection 'object used to store connection to db
Dim cmd As New ADODB.Command 'command used to execute stored proc
Dim param As New ADODB.Parameter 'object used to pass parameters tostored proc
Dim rs As New ADODB.Recordset 'recordset

Dim ws2 As Worksheet, i As Integer
Dim PartNum As String
Dim LotNum As String
Dim ProdNum As String
Dim ReceiveDate As Date
Dim Qty As Integer
Dim ROHSStat As String
Dim startRow As Integer
Dim LastRow As Integer
Dim SerialCol As Integer
Dim MySerial As Long
Dim Revision As String

Set ws2 = Sheets("sheet2")

SerialCol = 5
'this sub is used to load serial data (data for labels that have already been printed)

startRow = 1 'Starting Part row on sheet1
' Find the LAST real row
LastRow = ws2.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row

'open your db connection here

con.Open ("PROVIDER=MSDASQL;DSN=PosiStockLabels;DRIVER=SQLServer;SERVER=SRVPII01;DATABASE=PosiStockLabels")

'execute stored proc used to get serial label data
'and store in recordset
With cmd

Set .ActiveConnection = con
.CommandText = "sp_SelectSerialData"
.CommandType = adCmdStoredProc

For i = startRow To LastRow
If (Len(Trim(ws2.Cells(i, SerialCol))) > 0) Then
MySerial = ws2.Cells(i, SerialCol)

Set param = cmd.CreateParameter("@serialid", adInteger, adParamInput, 4, MySerial)
Call cmd.Parameters.Append(param)
Set rs = .Execute

With rs

'if records were returned from stored proc
'fill the data

If (.EOF = False) And (.BOF = False) Then
ws2.Cells(i, 6) = rs.Fields("PartNumber") 'Part
ws2.Cells(i, 7) = rs.Fields("LotNumber") 'Lot
ws2.Cells(i, 8) = rs.Fields("ProductionNumber") 'Prod
ws2.Cells(i, 9) = rs.Fields("DateReceived") 'Date Rec
ws2.Cells(i, 10) = rs.Fields("Quantity") 'Qty
ws2.Cells(i, 11) = rs.Fields("RoHSStatus") 'ROHS Status
ws2.Cells(i, 15) = rs.Fields("Revision") 'Revision
End If
End With

End If
Next
Set ws2 = Nothing

End With

rs.Close
con.Close
Set rs = Nothing
End Sub

 

[mkdev2007]

I am urgent need ogf help with this can someone help me. When I run this for the first row of data it tries to lookup it does so successfully its not until I loop back around for the next row when it says I have too many parameters passed to the Stored procedure which leads me to believe I should not be doing an append in this case.

Please help.