Please Help Access/SQL newbie with Stored Procedure

[Andrew Holmes]

I converted (upsized) my Access dB to SQL2000
I am trying to fix all the macros and queries that broke in the process.
Can someone tell me how to pass a Text box value to a Stored Procedure?

For instance I am trying to fill a List Box control with the last names that
match a text box...
so I have a Stored Procedure defined that pulls the Fname Lname ID with the
criteria being the Lname= @Lname
I get a prompt but I would rather pass the value of the text box field..

thanks

 

[Ron Weiner]

Something like:

Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
strSql = "Exec YourProc @Lname = " & LnameID
Set cn = New ADODB.Connection
cn.ConnectionString = strYourValidConnectionString
cn.Open
Set rs = cn.Execute(strSql)
...

Should work for you if you only need to pass one or two args

Ron W

 

[Newbie]

To create a recordset . . . Something like:
Dim cmdProduction As ADODB.Command
Dim params As ADODB.Parameters
Dim param As ADODB.Parameter
Dim rsData As ADODB.Recordset

Set cmdProduction = New ADODB.Command
Set rsData = New ADODB.Recordset
With cmdProduction
Set .ActiveConnection = cn
.CommandText = strReport
.CommandType = adCmdStoredProc
Set params = .Parameters
End With

' Define stored procedure params and append to command.
params.Append cmdProduction.CreateParameter("@RETURN_VALUE", adInteger,
adParamReturnValue, 0)
params.Append cmdProduction.CreateParameter("@DateFrom", adDBTimeStamp,
adParamInput, 0)
params.Append cmdProduction.CreateParameter("@DateTo", adDBTimeStamp,
adParamInput, 0)

' Specify input parameter values
params("@DateFrom") = mDateFrom
params("@DateTo") = mDateTo

' Execute the command
Set rsData = cmdProduction.Execute

HTH

Ali

 

[Newbie]

Oops forgot the connection string:

You also need to open the connection before the With statement
Dim cn As ADODB.Connection

Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User
ID=xxx;Password=xxxx;Initial Catalog=MIS;Data Source=CPW2K1"
cn.CursorLocation = adUseClient
cn.Open

HTH
Ali

 

[Andrew Holmes]

Thanks so much for takling the time to help me! Ill begin trying it... !