Executing stored procedures

[chobo321321]

Hi, I am trying to execute a stored procedure that take in 1 (datetime) parameter. Everytime I try and execute it I get the following error, which makes no sense to me.

"Procedure 'Shipping' expects parameter '@ShipDate, which was not supplied.

Any help is appreciated, thanks.

Here is the code for the form:

Private Sub cmdShippers_Click()
Dim rst As New ADODB.Recordset
Dim par As New ADODB.Parameter
Dim cmd As New ADODB.Command

Dim datShipDate As Date

cmd.ActiveConnection = CurrentProject.Connection

datShipDate = txtShipDate

With cmd
    .CommandText = "Shipping"
    .CommandType = adCmdStoredProc
    Set par = .CreateParameter("@ShipDate", adDBTimeStamp, adParamInput, datShipDate)
    .Parameters.Append par
End With

cmd.Execute

'ShowRecordset rst

End Sub

I'll also post the stored procedure code. The procedure byitself works fine.

create Procedure Shipping
(
	@ShipDate Datetime
)
as


SELECT datepart(weekday, o.shipdate) as 'Week Day'
FROM  tblOrder o
Where o.shipdate is not null and 
o.shipdate = CONVERT(DATETIME, @ShipDate, 101) 

exec Shipping '03/22/2004'

 

[RoyVidar]

Try the following and see if that works

With cmd
    .CommandText = "Shipping"
    .CommandType = adCmdStoredProc
    Set par = .CreateParameter("@ShipDate", adDBTimeStamp, adParamInput)
    .Parameters.Append par
    par.value = datShipDate
End With

 

[chobo321321]

I still get an "arguement has too many parameters" error. At the moment I'm downloading some vb service pack 5, not sure if will help, but I've run out of ideas.

 

[RoyVidar]

Sorry, I don't know. It works on my setup (both in an ADP using currentproject.connection, and establishing a connection to MSDE from an mdb)

Does the parameter contain a valid date prior to executing?

Debug.Print cmd.parameters(0)

Your initial parameter is passed as the size arguement of the createparameter thingie, it would need to look like this:

Set par = .CreateParameter("@ShipDate", adDBTimeStamp, adParamInput,, datShipDate)

(additional comma after the parameter direction arguement)

I don't think it would hurt to close (recordset if opened) and release the object variables either, but I can't say whether they might be the culprit or not, just throwing out some ideas...

 

[chobo321321]

I appreciate the help. I think there something wrong with my computer (setup). Even my combo box objects don't work properly, (at least I think they don't). Awhile back I tried doing something with bookmarks, and it never worked. I bring it to school and it worked fine...

Here's a pic of one
http://img140.exs.cx/img140/2681/combo1dq.gif

I did add the extra comma, and tried various other things, but nothing worked.

I also added the debug code in there, and the parameter is empty, even though the variable for datShipDate contain a date in the createparameters string. I even setup a string variable to retrieve the textbox value, and the converted the string to date, just to make sure the right datatype was in there.

I'm using the following references for Access:
- Visual Basic for applications
- Microsoft Access 10.0 Object Library
- OLE Automation
- Microsoft Calendar Control 10.0
- Microsoft ActiveX Data Objects 2.6 Library
- Microsoft Active Data Objects Recordset 2.7 Library

 

[RoyVidar]

Strange - I have no answers - but what happens if you create a new adp, and just run the SP like this?

To evaluate the result (if it get's that far), assign the command object to a recordset, and debug.print it

BTW - I'm not a big fan of implicit instantiation (dim something as NEW object), in some cases it can lead to unneeded challenges, especially when automating other Office applications, but... I prefer explicit declaration/instantiation - also try with a connection object variable - sometimes assigning currentproject.connection directly to a command object causes some anomalities

dim cmd as adodb.command
dim rs as adodb.recordset
dim par as adodb.parameter
dim cn as adodb.connection

set cmd = new adodb.command
set cn=currentproject.connection
with cmd
    .activeconnection = cn
    .CommandText = "Shipping"
    .CommandType = adCmdStoredProc
    Set par = .CreateParameter("@ShipDate", adDBTimeStamp, adParamInput)
    .Parameters.Append par
    par.value = datShipDate
    set rst = .execute
end with
if ((not rst.bof) and (not rst.eof)) then
    debug.print rst.getstring
end if

If it could be some corruption, I think the /decompile option should work on adp's too, see the following link for a step by step instruction
http://www.granite.ab.ca/access/decompile.htm

 

[chobo321321]

I tried that decomplile/recompile, and it had no problems with it. I didn't notice any changes to the file. I also cut and paste the code you posted, and I get the following error message.

Run-time error '-2147217904 (80040e10)':

Procedure 'Shipping' expects parameter '@Shipping', which was not supplied.

I checked the "par" variable using ? par, and it did contain a parameter with the date value (02/07/2004). I also installed access xp on another computer, and got the same error number different message. I'm completely lost on this one. The parameter datatype is a datetime, and I am sending it a date dataype... Am I missing a reference? Thanks for the help I really appreicate it.

 

[RoyVidar]

I'm completely out of ideas, except getting the latest service packs and MDAC.

 

[chobo321321]

Would you know how to supply the parameter manually to the execute statement? I have been trying, but I'm not sure if I am using the right syntax. Thanks for all your help. I must be missing some file, or setting, because I installed Access XP on a computer that was recently formated and I get the same weird problems and error.

 

[RoyVidar]

Is something like this what you're after?

dim cn as adodb.connection
dim rs as adodb.recordset
dim strsql as string

strsql = "exec shipping '" & dtStartdate & "'"
set cn=currentproject.connection
set rs=new adodb.recordset
rs.open strsql, cn, adopenkeyset, adlockoptimistic, adcmdtext

- and of course, this also works on my setup

 

[chobo321321]

Ya, that was what I was looking for. When you run that does it return anything (records?), or would I need to add some code to display what was returned. When I run it, it does nothing, so I'm not sure if it works or not. Thanks.

 

[pbaldy]

It opens a recordset, so you wouldn't "see" anything. Add this line to the end to verify you're returning data:

msgbox rs![Week Day]

 

[chobo321321]

I put the code in, but no msgbox displays when I click the button. This isn't even an issue about code anymore, I trust you when you say it works on your end. There is something screwed up on my end. I have tested access 2003, and xp on different computers (one that had a fresh install of windows 2000, the other has win XP pro, which hasn't been formated in years, and they both have problems.

I'm going to bring this to school, and test it out on their computers (always seems to work there), and check what references they are using. I'm going to also look for updates, and service packs, maybe one of them has a fix.

I just wanted to say thanks for sticking it out, on this problem, and providing code examples, not many people are that helpful anymore, thank you.