fire a stored procedure

[gogetsome]

Hello, I need to be able to pass two varibles to a stored procedure that is used for a crystal report which should also run when a button is pressed. I believe that I have the stored procedure configured correctly to take the two varibles and have bound the crystal report to this procedure.

I'm new to programming and am working from an example that a previous programmer wrote. Does somebody know of a tutorial or example of how to pass varibles to a stored procedure then display a crystal report based on the variables? Any help would be great!

I have attached the code I'm working with below if someone could help explain why it is choking at the with invoice_detail.server=sqlsvr. I suppose that the connection to the sql server is failing? Is the code even workable?

Like I said, I'm a newb and could really use the help.
Private Sub InvPrint_BeforeUserChanged(KeepFocus As Boolean, CancelLogic As Boolean)

Dim userinfo As New RetrieveGlobals.retrieveuserinfo
Dim sqluid As String
Dim sqlpwd As String
Dim SQLdb As String
Dim sqlsvr As String


sqluid = userinfo.retrieve_user()

sqlpwd = userinfo.sql_password()

SQLdb = userinfo.intercompany_id()

sqlsvr = userinfo.sql_datasourcename()


With Invoice_Detail
.server = sqlsvr
.DataBase = test
.User = sa
.password = test
.InvBeg = InvBeg.Value
.InvEnd = InvEnd.Value
.load_view

End With


Dim adCNN As ADODB.Connection
Dim adrst As ADODB.Recordset
Dim strcmd As String
Dim strCnn As String
Dim SQLCmd As String
Dim SQLstr As String

SQLCmd = "EXEC cssp_Maintenance_Invoice " & _
"'" & InvBeg.Value & "'" & "'" & InvEnd.Value & "'"

SQLstr = "driver={SQL Server};server=" & sqlsvr & ";Database=" & SQLdb & ";" & "PWD=" & sqlpwd & ";UID=" & sqluid
Set adCNN = New ADODB.Connection
adCNN.ConnectionString = SQLstr
adCNN.Open
Set adrst = New ADODB.Recordset
adrst.Open SQLCmd, adCNN, adOpenDynamic, adLockOptimistic

ZoomButtons.Value = 1
Costs.Close


Set s = CreateObject("InternetExplorer.application")
s.Visible = True
s.Navigate "

http://exch-server/scrreports/Service/Maintenance/maintenanceInvoice.rpt"

'?prompt0=" & JobStatus.Job & "&prompt1=" & CStr(Now)


End Sub

 

[barbola]

Have you tried hard-coding the User ID and password? I am new to VB and I normally would hardcode the 'sa' as the user.

I'm sorry I don't know Crystal reports, but I've been learning how to call a stored proc in VB, and I know there is some info in the VB forum if you wanna take a look.

 

[gogetsome]

Thank you for responding. I found a book and read the ado section and came up with this; which works:


Private Sub InvPrint_BeforeUserChanged(KeepFocus As Boolean, CancelLogic As Boolean)

Dim objCmd As ADODB.Command
Dim sDataConnect As String
Dim lngErrorNum As Long
Dim objConn As New ADODB.Connection
objConn.Open "Provider=sqloledb;Data Source=localhost;Initial Catalog=test;User Id=test;Password=test"
' Open a database connection

Set objCmd = CreateObject("ADODB.Command")
With objCmd
.ActiveConnection = objConn
.CommandType = adCmdStoredProc
.CommandText = "storedprocedure"
.Parameters.Append .CreateParameter("@start", adVarChar, adParamInput, 30, 1)
.Parameters.Append .CreateParameter("@finished", adVarChar, adParamInput, 30, 1)
.Execute
End With

Set objCmd = Nothing
objConn.Close
Set objConn = Nothing

Set s = CreateObject("InternetExplorer.application")
s.Visible = True
s.Navigate "

http://sql-server/scrreports/Service/Maintenance/maintenanceInvoice.rpt?&prompt0="

& start & "&prompt1=" & finished


End Sub