I am in the process of creating an excel macro that calls a SQL Server stored procedure and populates a column in a spreadsheet.
There are three columns in the spreadsheet: "Student Name", "Course Title" and "Enrollment Status". I want to be able to use "Student Name" and "Course Title" cell values as parameters for the stored procedure.
The stored procedure is going to evaluate every row in the spreadsheet for that "Student Name" and "Course Title" (row combination) and populate the enrollment status in the "Enrollment Status" column.
This is a simple Stored Procedure that is querying a small data set so I am not worried about performance.
Note: This is conceptually identical to what I am trying to accomplish however I do not want to display business specific subject matter.
Example: A1 = Janet Smith, B1 = Physics. A1 and B1 are parameters for the stored procedure which then returns C1 that indicates enrollment status. I.E. "Janet Smith", "Physics", "Not
A2 = Samuel Adams, B2 = Biology. A2 and B2 are parameters for the stored procedure which then returns C2 that indicates enrollment status. I.E. "Samuel Adams", "Biology",
This is what I came up with to take a single cell as a parameter. My questions is how can I do this for a range of cells (A1:A100, B1:100, C1:100).
Thanks in advance for your taking the time to look at this. Much appreciated.
Private Sub CommandButton1_Click()
Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim strConn As String
Set cn = New ADODB.Connection
strConn = "PROVIDER=SQLOLEDB;"
strConn = strConn & "SERVER=SQLABCD;INITIAL CATALOG=DBXYZ;"
strConn = strConn & " INTEGRATED SECURITY=sspi;"
Set cmd = New ADODB.Command
cmd.ActiveConnection = cn
cmd.CommandText = "usp_SampleProc"
cmd.CommandType = adCmdStoredProc
'only have single set of parameters so far. Need to execute stored procedure for all rows
cmd.Parameters(1).Value = Range("A1")
cmd.Parameters(1).Value = Range("B1")
Set rs = cmd.Execute()
If Not rs.EOF Then
MsgBox "No data."