Solved

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

Posted on 2009-04-13
31
491 Views
Last Modified: 2012-05-06
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
Comment
Question by:Romacali
  • 18
  • 9
  • 3
  • +1
31 Comments
 
LVL 39

Expert Comment

by:nutsch
ID: 24130795

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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24130817
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
 

Author Comment

by:Romacali
ID: 24130966
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
 
LVL 39

Expert Comment

by:nutsch
ID: 24131056
Try replacing <the start date> by your start date.
0
 

Author Comment

by:Romacali
ID: 24131124
it is not working I'm getting the a syntax error.. not sure what to do.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24131162
please change "adParamInput" by the value as defined in the link I provided
0
 

Author Comment

by:Romacali
ID: 24131412
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
 

Author Comment

by:Romacali
ID: 24131831
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24133368
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
 

Author Comment

by:Romacali
ID: 24133413
it is still giving me an error :(
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24133465
on which line?
0
 

Author Comment

by:Romacali
ID: 24133529
CCommand3.Parameters.Append CCommand3.CreateParameter("@beginDate", 133, adParamInput, "03/10/2008")

it says type mismatch
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24133598
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
 

Author Comment

by:Romacali
ID: 24133630
I tried this one and now I got:

wrong number of arguments or invalid property assigment
0
 

Author Comment

by:Romacali
ID: 24133644
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

Author Comment

by:Romacali
ID: 24133727
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24133734
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
 

Author Comment

by:Romacali
ID: 24133758
nope :( the same  error:

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

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24133877
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
 

Author Comment

by:Romacali
ID: 24133952
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24133986
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24134409
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
 

Author Comment

by:Romacali
ID: 24139421
hi angell,

I tried what you said and I got this error:
Variable not defined pointing to CDate(begindate )
0
 

Author Comment

by:Romacali
ID: 24139450
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
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 24139779
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
 
LVL 39

Expert Comment

by:nutsch
ID: 24139900
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
 

Author Comment

by:Romacali
ID: 24140093
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
 

Author Comment

by:Romacali
ID: 24140157
actually now it is not working either way :(
0
 

Author Comment

by:Romacali
ID: 24140205
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
 

Author Comment

by:Romacali
ID: 24140237
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
 

Author Closing Comment

by:Romacali
ID: 31569551
Angell rocks. Thanks it was great having you helping me!

thanks you so very much!
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now