How to Add Parameter to excel VBA to execute Sproc

0

I have an excel sheet that connects to a SQL database and runs a stored procedure. I am trying to add one or more parameters to it so it will pull a certain register and group.What is in bold is what i tried for the first parameter. But it doesn't work. I would like a box to popup to enter the RegisterName. Thanks in advance

Sub Importdata()
 On Error GoTo Err: 
 Dim strSQL, sqltest As String
strSQL = "exec RegisterExtractByName WHERE (RegisterName = ?)"
Set param1 = qt.Parameters.Add("RegisterName Parameter", _
 xlParamTypeVarChar)
param1.SetParam xlConstant, "First Aide Kit"
If strSQL = "" Then
Exit Sub
End If
    Dim cn As ADODB.Connection
    Dim lngRecsAff As Long
    Set cn = New ADODB.Connection
    cn.CommandTimeout = 0 'to fix query timeout issue
    cn.Open "Provider=SQLNCLI11;Data Source=" & Range("A1").Value & ";" & _
      "Initial Catalog=" & Range("A2").Value & ";User ID=SA;Password=Colorblue!"
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
With rs
      .ActiveConnection = cn
      .Open strSQL
End With
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = "MMR"
Dim fieldCount
Dim position
For Each fld In rs.Fields
    fieldCount = fieldCount + 1
    Sheets(Sheets.Count).Cells(1, fieldCount).Value = fld.Name
Next
Sheets(Sheets.Count).Range("A2").CopyFromRecordset rs
Sheets(Sheets.Count).Cells.EntireColumn.AutoFit
Sheets(Sheets.Count).Rows("1:1").Font.Bold = True
Exit Sub
Err:
MsgBox (Err.Description)
End Sub
Answer
Discuss

Discussion

Hi. Not sure what the initial lines of your code are meant to do, Can you clarify please?.

Your DIM statement makes doesn't define strSQL (so it becomes a Variant)- what should it be? (It does make sqltest a String but that isn't used in the following code). 

You have the instruction 
Set param1 = qt.Parameters.Add...
but you haven't defined what qt (or param1) is so they're just empty Variants.

It sounds like use of qt should be preceded by 
Dim qt as QueryTable
and that you need to set the object qt BEFORE adding a parameter or doing anything (so that VBA knows what to act on).

On your last point "a box to pop up to enter Register Name", I suggest you try the InputBox method. That's covered in Don's tutorial Get User Submitted Data from a Prompt in Excel using VBA Macros on this site. 
John_Ru (rep: 1002) Nov 4, '20 at 6:19 am
At a guess, you want to replace the ?  
Like 
 Dim RegVar As String
 RegVar = "First Aide Kit"
strSQL = "exec RegisterExtractByName WHERE (RegisterName = " & RegVar & ")"


You just need to do the input form thing
k1w1sm (rep: 197) Nov 4, '20 at 3:33 pm
John_Ru thanks for your reply. I actually remodified my query and figured out how to do what I needed
ahead Nov 4, '20 at 7:53 pm
That's good to hear.
John_Ru (rep: 1002) Nov 5, '20 at 8:40 am
Add to Discussion



Answer the Question

You must create an account to use the forum. Create an Account or Login