May 31, 2005 at 5:25 am
I'm running a access 2000 adp front end working from a SQL2000 database. I've got a sproc P_insertsessions i can get this work from a cmd button on a form but i'm having problems setting the paramerters i want it to use controls on the form.
@contractid = contractid
@start_date = startdate
@end_date = enddate
@hours = duration
Can anyone help.
May 31, 2005 at 5:36 am
Please avoid posting the same question multiple times.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
June 1, 2005 at 4:08 am
Michael,
@contractid = me![contractid]
Richard
June 2, 2005 at 8:31 am
I think the problem you are having is that SQL Server cannot access the controls in your form. The way I handle this is I create a command as a string and then execute it in Access. For example:
Dim strCmd as String
strCmd = "storedProcName" & Me![contractID] & " , " & Me![anotherParam] & " , " & Me![yetAnotherParam]
CurrentProject.Connection.Execute(strCmd)
Or, if your procedure returns a recordset:
Dim rs As ADODB.Recordset
Set rs = CurrentProject.Connection.Execute(strCmd)
June 2, 2005 at 5:38 pm
If you are reading from the sproc, trying to populate your form you can do this:
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
'Call the stored procedure, passing it the parameter, returning recordset rs
CurrentProject.Connection.YourSprocHere InputParameter, rs
'Fill in the fields from the returned recordset
If Not rs.BOF And Not rs.EOF Then
Me.txtSN = rs![SN]
Me.txtModel = rs![Model]
Me.txtShipped = rs![ShippedDate]
Else
End If
rs.Close
Set rs = Nothing
if you are trying to insert values from your form into a sproc, you can do it like this:
Dim Cnn As ADODB.Connection
Dim Cmd As ADODB.Command
Dim lRecordsAffected As Long
Set Cnn = New ADODB.Connection
Set Cmd = New ADODB.Command
Set Cnn = CurrentProject.Connection
With Cmd
.CommandType = adCmdStoredProc
.CommandText = "YourSprocNameHere"
.Parameters.Append .CreateParameter("@Mode", adInt, adParamInput, , me.txtMode)
.Parameters.Append .CreateParameter("@CompanyID", adGUID, adParamInput, , Me.txtCompanyID)
Set .ActiveConnection = Cnn
.Execute lRecordsAffected ', , adExecuteNoRecords ' Do not return a recordset
End With
Set Cmd = Nothing
Cnn.Close
Set Cnn = Nothing
HTH
David
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply