Solved

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

Posted on 2009-04-13
31
490 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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
In this article I will describe the Copy Database Wizard 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.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
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…

747 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

13 Experts available now in Live!

Get 1:1 Help Now