[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 507
  • Last Modified:

how to run a stored procedure and pass values from excel?

Can someone help me with this?
I'm trying to run a stored procedure from excel so I can pass the results to the spreadsheet. But the problem I'm having is the procedure requires a @beginDate and a @endDate.
My questions is how can I ask send this values from the excel when I run the procedure?

thanks a bunch!
here is the code I have in excel:
Public Sub Daily_results()
 
  Dim cnn As Object
  Dim CRecordset3 As Object  
  
   ' Create a connection object
  Set cnn = CreateObject("ADODB.Connection")
 
   ' Connect to the database
  cnn.Provider = "SQLOLEDB"
  cnn.Open "Server=Vaio;Database=Product;User Id=admin;Password=password"
  If cnn.State <> 1 Then
    MsgBox "Could not connect to the database"
     Exit Sub
  End If
 
 
   ' Open recordset BeginNextRecordset
 
  Set CRecordset3 = CreateObject("ADODB.Recordset")
  CRecordset3.Open "EXEC spGetDailyCC", cnn, adOpenDynamic, adLockPessimistic
 
  Sheets("sheet1").[B4].CopyFromRecordset CRecordset3
    
 
  cnn.Close
  Set cnn = Nothing
 
End Sub

Open in new window

0
Romacali
Asked:
Romacali
  • 18
  • 9
  • 3
  • +1
1 Solution
 
nutschCommented:

How to Pass Parameters to a Stored Procedure

<script type="text/javascript">                loadTOCNode(2, 'moreinformation');            </script>     To pass parameters, include them after the name of the stored procedure   in a string. For example:  

Open in new window

      ' String specifying SQL.
      command.SQLx = "My_StorProc parm1, parm2, parm3"
      ...
      ' For stored procedure that doesn't return records.
      i = MyDB.ExecuteSQL(SQLx)
      ...
      'For stored procedure that returns records.
      set Ds = MyDB.OpenRecordset(SQLx, dbOpenDynaset, dbSQLPassThrough)How to Pass Parameters to a Stored Procedure       To pass parameters, include them after the name of the stored procedure    in a string. For example:         ' String specifying SQL.
      command.SQLx = "My_StorProc parm1, parm2, parm3"
      ...
      ' For stored procedure that doesn't return records.
      i = MyDB.ExecuteSQL(SQLx)
      ...
      'For stored procedure that returns records.
      set Ds = MyDB.OpenRecordset(SQLx, dbOpenDynaset, dbSQLPassThrough)

						    The object variable (Ds) contains the first set of results from the    stored procedure (My_StorProc).   


						

Open in new window

  The object variable (Ds) contains the first set of results from the   stored procedure (My_StorProc).  
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please try this:

for the reference, the adodb constants:
http://www.flb.fo/infolit/ADOVBS.INC
  Set CCommand3 = CreateObject("ADODB.Command")
  Set CCommand3.ActiveConnection = cnn
  CCommand3.CommandText = "spGetDailyCC"
  CCommand3.CommandType = 4 'for adCmdStoredProc
  CCommand3.Parameters.Append Command3.CreateParameter("@beginDate", 133, adParamInput,,,<the start date>)
  CCommand3.Parameters.Append Command3.CreateParameter("@endDate", 133, adParamInput,,,<the end date>) 
  Set CRecordset3 = CreateObject("ADODB.Recordset")
  CRecordset3.Open CCommand3

Open in new window

0
 
RomacaliAuthor Commented:
hi Angell,

Im trying your codes but these lines are red:
  CCommand3.Parameters.Append Command3.CreateParameter("@beginDate", 133, adParamInput,,,<the start date>)
  CCommand3.Parameters.Append Command3.CreateParameter("@endDate", 133, adParamInput,,,<the end date>)

what do I have to do?

I declared on the top this:
 Dim cnn As Object
  Dim CRecordset3 As Object  
  Dim begindate As Date
  Dim endDate As Date
  Dim CCommand3 As Object

what else do I have to do?
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
nutschCommented:
Try replacing <the start date> by your start date.
0
 
RomacaliAuthor Commented:
it is not working I'm getting the a syntax error.. not sure what to do.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please change "adParamInput" by the value as defined in the link I provided
0
 
RomacaliAuthor Commented:
I tried thi:
CCommand3.Parameters.Append Command3.CreateParameter("@beginDate", 133, adParamInput, "04/01/2008")
  CCommand3.Parameters.Append Command3.CreateParameter("@endDate", 133, adParamInput, "04/31/2008")

and it gives me this error:
Object variable or with block variable not set
0
 
RomacaliAuthor Commented:
here is my codes... still not working :(
Public Sub Daily_results()
 
  Dim cnn As Object
  Dim CRecordset3 As Object
  Dim begindate as date
  Dim enddate as date  
  
   ' Create a connection object
  Set cnn = CreateObject("ADODB.Connection")
 
   ' Connect to the database
  cnn.Provider = "SQLOLEDB"
  cnn.Open "Server=Vaio;Database=Product;User Id=admin;Password=password"
  If cnn.State <> 1 Then
    MsgBox "Could not connect to the database"
     Exit Sub
  End If
 
 
   ' Open recordset BeginNextRecordset
 
  Set CRecordset3 = CreateObject("ADODB.Recordset")
  CRecordset3.Open "EXEC spGetDailyCC", cnn, adOpenDynamic, adLockPessimistic
  
  CRecordset.Parameters.Append Commend3.CreateParameter("@beginDate", 133, adParamInput,"04/01/2008")
  CRecordset.Parameters.Append Commend3.CreateParameter("@endDate", 133, adParamInput,"04/31/2008")
 
  Sheets("sheet1").[B4].CopyFromRecordset CRecordset3
 
    
 
  cnn.Close
  Set cnn = Nothing
 
End Sub

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sorry, it should have been CCommand3 and not CCommand3
  Set CCommand3 = CreateObject("ADODB.Command")
  Set CCommand3.ActiveConnection = cnn
  CCommand3.CommandText = "spGetDailyCC"
  CCommand3.CommandType = 4 'for adCmdStoredProc
  CCommand3.Parameters.Append CCommand3.CreateParameter("@beginDate", 133, adParamInput,,,<the start date>)
  CCommand3.Parameters.Append CCommand3.CreateParameter("@endDate", 133, adParamInput,,,<the end date>) 
  Set CRecordset3 = CreateObject("ADODB.Recordset")
  CRecordset3.Open CCommand3

Open in new window

0
 
RomacaliAuthor Commented:
it is still giving me an error :(
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
on which line?
0
 
RomacaliAuthor Commented:
CCommand3.Parameters.Append CCommand3.CreateParameter("@beginDate", 133, adParamInput, "03/10/2008")

it says type mismatch
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the ,,, in my code was not a typo...
CCommand3.Parameters.Append CCommand3.CreateParameter("@beginDate", 133, adParamInput,,, cdate("03/10/2008"))

Open in new window

0
 
RomacaliAuthor Commented:
I tried this one and now I got:

wrong number of arguments or invalid property assigment
0
 
RomacaliAuthor Commented:
here is my code.
Public Sub GetPrivateDailyCensusTEST2()
 
  Dim cnn As Object
  Dim CRecordset3 As Object   'Private Avg
  Dim CCommand3 As Object
  
  
   ' Create a connection object
  Set cnn = CreateObject("ADODB.Connection")
 
   ' Connect to the database
  cnn.Provider = "SQLOLEDB"
  cnn.Open "Server=vaio;Database=database;User Id=admin;Password=password"
  If cnn.State <> 1 Then
    MsgBox "Could not connect to the database"
     Exit Sub
  End If
 
  Set CCommand3 = CreateObject("ADODB.Command")
  Set CCommand3.ActiveConnection = cnn
  CCommand3.CommandText = "spGetPrivateDailyCensusNew"
  CCommand3.CommandType = 4 'for adCmdStoredProc
  CCommand3.Parameters.Append CCommand3.CreateParameter("@beginDate", 133, adParamInput, , , CDate("03/10/2008"))
  CCommand3.Parameters.Append CCommand3.CreateParameter("@endDate", 133, adParamInput, , , CDate("03/10/2008"))
  
  Set CRecordset3 = CreateObject("ADODB.Recordset")
  CRecordset3.Open CCommand3
 
   ' Open recordset BeginNextRecordset
 
'  Set CRecordset3 = CreateObject("ADODB.Recordset")
 ' CRecordset3.Open "EXEC spGetPrivateDailyCensusNew", cnn
  
  Sheets("WeeklyCensus").[D4].CopyFromRecordset CRecordset3
    
 
  cnn.Close
  Set cnn = Nothing
 
End Sub

Open in new window

0
 
RomacaliAuthor Commented:
I tried this:
ad Now I get an error:

Procedure 'sp_' expects paramater @beginDate, which was not supplied. pointing to line:
 CRecordset3.Open CCommand3

I'm attaching my stored procedure
Public Sub GetPrivateDailyCensusTEST2()
 
  Dim cnn As Object
  Dim CRecordset3 As Object   'Private Avg
  Dim CCommand3 As Object
  
  
   ' Create a connection object
  Set cnn = CreateObject("ADODB.Connection")
 
   ' Connect to the database
  cnn.Provider = "SQLOLEDB"
  cnn.Open "Server=vaio;Database=Database;User Id=user;Password=password"
  If cnn.State <> 1 Then
    MsgBox "Could not connect to the database"
     Exit Sub
  End If
 
Dim begindate As Date
Dim enddate As Date
 
 'Set current sheet
 Worksheets("WeeklyCensus").Activate
 Range("B1").Activate
 begindate = Range("B1").Value 'Change to the correct cell
 enddate = Range("B2").Value
 
 
  Set CCommand3 = CreateObject("ADODB.Command")
  Set CCommand3.ActiveConnection = cnn
  CCommand3.CommandText = "spGetPrivateDailyCensusNew"
  CCommand3.CommandType = 4 'for adCmdStoredProc
  
  CCommand3.Parameters.Append CCommand3.CreateParameter("@beginDate", 133, adParamInput, CDate("03/10/2008"))
  CCommand3.Parameters.Append CCommand3.CreateParameter("@endDate", 133, adParamInput, enddate)
  
  Set CRecordset3 = CreateObject("ADODB.Recordset")
  CRecordset3.Open CCommand3
  
   ' Open recordset BeginNextRecordset
 
 'Set CRecordset3 = CreateObject("ADODB.Recordset")
 ' CRecordset3.Open "EXEC spGetPrivateDailyCensusNew", cnn
  
  Sheets("WeeklyCensus").[D4].CopyFromRecordset CRecordset3
    
 
  cnn.Close
  Set cnn = Nothing
 
End Sub

Open in new window

procedure.txt
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
any  better like this:
  CCommand3.Parameters.Append CCommand3.CreateParameter("@beginDate", 133, 1,  , CDate("03/10/2008"))
  CCommand3.Parameters.Append CCommand3.CreateParameter("@endDate", 133, 1, ,  CDate("03/10/2008"))

Open in new window

0
 
RomacaliAuthor Commented:
nope :( the same  error:

Procedure 'sp_' expects paramater @beginDate, which was not supplied. pointing to line:
 CRecordset3.Open CCommand3
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
although I don't have my hands on a vb6+sql server environment right now, that should work, though...

you might want to try this:
  CCommand3.Parameters.Append CCommand3.CreateParameter("beginDate", 133, 1,  , CDate("03/10/2008"))
  CCommand3.Parameters.Append CCommand3.CreateParameter("endDate", 133, 1, ,  CDate("03/10/2008"))

Open in new window

0
 
RomacaliAuthor Commented:
Hi Angell, Thanks for you help...
it worked, but what i need is to get this date from the top of my spreadsheet. example:
A                                          B                
enter the begin date:      3/1/2008
enter the en date:      4/1/2009

I want to send to the stored procedure the value as beginDate=b1 and enddate=b2

Is there a way? Should I open a new question?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
great!

so, next step:
  CCommand3.Parameters.Append CCommand3.CreateParameter("beginDate", 133, 1,  , CDate(begindate ))
  CCommand3.Parameters.Append CCommand3.CreateParameter("endDate", 133, 1, ,  CDate(enddate))

Open in new window

0
 
Anthony PerkinsCommented:
By the way, it should be 135 (adDBTimeStamp) and not 133 (adDBDate) See here for a complete list:
Data Type Mapping
http://www.carlprothman.net/Technology/DataTypeMapping/tabid/97/Default.aspx
0
 
RomacaliAuthor Commented:
hi angell,

I tried what you said and I got this error:
Variable not defined pointing to CDate(begindate )
0
 
RomacaliAuthor Commented:
I declared them as :
Dim beginDate As Date
Dim endDate As Date

but nothing happened... how can I pointing these variable to read what I have on B1 and B2 of my sheet?

thanks for helping me
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you no longer have this code?

 begindate = cdate(Range("B1").Value)
 enddate = cdate(Range("B2").Value)
 
 
and as begindate and endate are declare "as date":
 
 CCommand3.Parameters.Append CCommand3.CreateParameter("beginDate", 133, 1,  , begindate)
  CCommand3.Parameters.Append CCommand3.CreateParameter("endDate", 133, 1, ,  enddate)

Open in new window

0
 
nutschCommented:
hey Angel, there's another question on the same topic. You're more qualified to handle it:
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_24318069.html?cid=1066#a24139648
0
 
RomacaliAuthor Commented:
I tried this:
 Dim beginDate As Date
  Dim endDate As Date
 
 beginDate = CDate(Range("B1").Value)
 endDate = CDate(Range("B2").Value)
 
 

 
 CCommand3.Parameters.Append CCommand3.CreateParameter("beginDate", 133, 1, , beginDate)
  CCommand3.Parameters.Append CCommand3.CreateParameter("endDate", 133, 1, , endDate)
 
'  CCommand3.Parameters.Append CCommand3.CreateParameter("beginDate", 135, 1, , CDate(Range("B1")))
 ' CCommand3.Parameters.Append CCommand3.CreateParameter("endDate", 135, 1, , CDate(Range("B2")))
  Set CRecordset3 = CreateObject("ADODB.Recordset")
  CRecordset3.Open CCommand3

  ' Record the result from store procedure to the sheet

  Sheets("WeeklyCensus").[A7].CopyFromRecordset CRecordset3

but didn't work

if I have only this: it works

CCommand3.Parameters.Append CCommand3.CreateParameter("beginDate", 135, 1, , CDate(Range("B1")))
 'CCommand3.Parameters.Append CCommand3.CreateParameter("endDate", 135, 1, , CDate(Range("B2")))
  Set CRecordset3 = CreateObject("ADODB.Recordset")
  CRecordset3.Open CCommand3

  ' Record the result from store procedure to the sheet

  Sheets("WeeklyCensus").[A7].CopyFromRecordset CRecordset3

can I just leave like this one? or it is better to declare the way you did?
   
0
 
RomacaliAuthor Commented:
actually now it is not working either way :(
0
 
RomacaliAuthor Commented:
it is very odd i tried early this morning and it worked!! Now all I get are these dates:

I set the begin date = 3/1/2008 and end date = 8/30/2008
###############################################################################################################################################################################################################################################################      0.00
1/1/1900      0.00
1/8/1900      0.00
1/15/1900      0.00
1/22/1900      0.00
1/29/1900      0.00
2/5/1900      0.00
2/12/1900      0.00
2/19/1900      0.00
2/26/1900      0.00
3/4/1900      0.00
3/11/1900      0.00
3/18/1900      0.00
3/25/1900      0.00
4/1/1900      0.00
4/8/1900      0.00
4/15/1900      0.00


Please help

Public Sub GetCountyDailyCensus()
 
  Dim cnn As Object
  Dim CRecordset3 As Object   'Private Avg
  Dim CCommand3 As Object
  
  
   ' Create a connection object
  Set cnn = CreateObject("ADODB.Connection")
 
   ' Connect to the database
  cnn.Provider = "SQLOLEDB"
  cnn.Open "Server=Vaio;Database=database;User Id=admin;Password=password"
  If cnn.State <> 1 Then
    MsgBox "Could not connect to the database"
     Exit Sub
  End If
 
  ' Open recordset
  
  Set CCommand3 = CreateObject("ADODB.Command")
  Set CCommand3.ActiveConnection = cnn
  CCommand3.CommandText = "spGetCensusNew"
  CCommand3.CommandType = 4 'for adCmdStoredProc
  
  Dim begindate As Date
  Dim enddate As Date
  begindate = CDate(Range("B1").Value)
  enddate = CDate(Range("B2").Value)
  
 
  
  ' Gather/Send the beginDate and endDate using values from the sheet col B1 and B2
  
  CCommand3.Parameters.Append CCommand3.CreateParameter("beginDate", 133, 1, , begindate)
  CCommand3.Parameters.Append CCommand3.CreateParameter("endDate", 133, 1, , enddate)
  Set CRecordset3 = CreateObject("ADODB.Recordset")
  CRecordset3.Open CCommand3
 
  ' Record the result from store procedure to the sheet
 
  Sheets("WeeklyCensus").[A7].CopyFromRecordset CRecordset3
    
 
  cnn.Close
  Set cnn = Nothing
 
End Sub

Open in new window

0
 
RomacaliAuthor Commented:
problem resolved
the  begindate = CDate(Range("B1").Value)
  enddate = CDate(Range("B2").Value)

should be:

 begindate = CDate(Range("B2").Value)
  enddate = CDate(Range("B3").Value)
 

thanks Angell!!
 
0
 
RomacaliAuthor Commented:
Angell rocks. Thanks it was great having you helping me!

thanks you so very much!
0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

  • 18
  • 9
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now