Solved

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

Posted on 2009-04-13
31
499 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 143

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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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 143

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 143

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 143

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 143

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
 

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 143

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 143

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 143

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 143

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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

627 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