Passing Parameters in Stored Procedures

[Dman]

I'm trying to pass 3 parameters to a stored procedure but can't figure out how to do it. Here's the gist of the parameters I need to pass. Bear in mind I'm not sure about the syntax.

FillList.CommandText = "prGetOutCustRMA
(lstCompany.list(lstcompany.listindex) ,txtCustDate1.text,txtCustDate2.text)"

I need to pass the values in the list box, and two text boxes. I continue to get errors. Any hints?

[VBWizInTraining]

I hope this helps........
-----------------------------------------------------------------
Dim fillList as ADODB.Command
Dim prGetOutCustRMA as ADODB.Parameter
Dim strCompany as string

Set fillList.ActiveConnection = (your connection object)
fillList.CommandText = "storedprocedurename"
fillList.CommandType = adCmdStoredProc

strCompany = lstCompany.list(lstcompany.listindex)

Set prGetOutCustRMA = fillList.CreateParameter(Company, adWChar, adParamInput, ,strCompany)
fillList.Parameters.Append prGetOutCustRMA

Set prGetOutCustRMA = fillList.CreateParameter(CustDate1, adDBDate, adParamInput, ,cDate(txtCustDate1.text))
fillList.Parameters.Append prGetOutCustRMA

Set prGetOutCustRMA = fillList.CreateParameter(CustDate2, adDBDate, adParamInput, ,cDate(txtCustDate2.text))
fillList.Parameters.Append prGetOutCustRMA

fillList.Execute

[Dman]

Thanks so much! That was a great help!

[Dman]

However...

When running this:

FillList.CommandText = "prGetOutCustRMA"
strCompany = lstCompany.List(lstCompany.ListIndex)
Set prGetOutRMA = FillList.CreateParameter("Company", adVarWChar, adParamInput, 15, _
strCompany)
FillList.Parameters.Append prGetOutRMA

Set prGetOutRMA = FillList.CreateParameter("Date1", adDBDate, adParamInput, , _
CDate(txtCustDate1.Text))
FillList.Parameters.Append prGetOutRMA

Set prGetOutRMA = FillList.CreateParameter("Date2", adDBDate, adParamInput, , _
CDate(txtCustDate2.Text))
FillList.Parameters.Append prGetOutRMA

Set grsReportList = FillList.Execute

I get this error:
"[ODBC SQL Server driver]Optional feature not implemented."

Any clues?

[Dman]

The answer to the ODBC driver error was that SQL Server doesn't accept the adDBdate data type. I changed it to adDBTimeStamp and it worked fine.

[VBWizInTraining]

hey glad to hear it! now, when you get your app up and running and you've successfully packaged it and tested the install on a remote machine, please write back and let me know how you handled the ODBC connectivity. I've been having problems all week packaging my app so that the DSN for my app's ODBC connection is set up automatically.

So far, installation is successful, but I still have to manually set up the ODBC data source through the control panel before my app sees the database. I hope you have better luck with this than I've been having...

thanks...
Sabrina

[Bigley]

You should use an INI file on the client machine that your app reads on startup and registers the appropriate database automatically. In DAO there is a function for this DBEngine.RegisterDatabase but with ADO you have to use an API call.....
Hope this helps

[Gary Juleff]

Hi VBWizInTraining,

Why not use a DSNLess connection ?

[Dman]

Is there a way to programmically create an ODBC DSN? In other words, what is the actual API?