Need syntax for CreateParameter Object

[prettyangel]

Can someone help me?

I need to create a report that will take parameters supplied through a combobox control. I need to create the parameter and assign combobox's value to it which I did but it's giving me error plus I've forgotten how to use the declared parameter to create the report.

Below is the code snippet.

Code:
Sub GetMonth()
Set spGetMonth = New ADODB.Command
spGetMonth.CommandText = "GetMonth"
spGetMonth.CommandType = adCmdStoredProc
strMonth = Trim(cboFromPayPeriod.Text)

Set prmbyMonth = spGetMonth.CreateParameter("@GetMonth", adVarChar, adParamInput)

spGetMonth.Parameters.Append prmbyMonth
prmbyMonth.Value = strMonth

' Create recordset by executing the command.
Set spGetMonth.ActiveConnection = dbconnect
Set rstGetMonth = spGetMonth.Execute

End Sub

 

[gmmastros]

Maybe this faq's will help. faq222-2067

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[prettyangel]

Thanks for your response gmmastros.

I found the faq really useful, please how will I supply this to crystal report so that the value entered through vb combobox control will be taken by it.

 

[prettyangel]

Thanks for your response gmmastros.

I found the faq really useful, please how will I supply this to crystal report so that the value entered through vb combobox control will be taken by it.


the ffg error occured
"Parameter object was improperly defined. Incomplete or inconsistent information was provided.

I must be missing something but what?

this is my code.

Function GetMonth(strMonth As String) As String
Set spGetMonth = New ADODB.Command
With spGetMonth
Set .ActiveConnection = dbconnect
.CommandText = "GetMonth"
.CommandType = adCmdStoredProc
strMonth = Trim(cboFromPayPeriod.Text)

Set prminput = .CreateParameter("@GetMonth", adVarChar, adParamInput, , strMonth)
Set prmoutput = .CreateParameter("@Month", adVarChar, adParamOutput, 255)
.Parameters.Append prminput
.Parameters.Append prmoutput
.Execute
GetMonth = .Parameters(1).Value 'Zero-based array
End With
Set spGetMonth = Nothing
End Function

 

[gmmastros]

I think your problem is with this line of code.

Set prminput = .CreateParameter("@GetMonth", adVarChar, adParamInput, , strMonth)

You should specify the size of the varchar variable.

The help file shows the following arguments for the CreateParameter function

Set parameter = command.CreateParameter (Name, Type, Direction, Size, Value)

Each parameter is optional. However, if you use a varchar parameter, then you need to specify the size of the parameter. You should look at the text of the stored procedure (GetMonth). You should find 2 parameters for this procedure. Both should be VarChar's but HOPEFULLY, they both have a size specified for them.

The declaration for the procedure should look something like...
Create Procedure GetMonth
@GetMonth VarChar(100),
@Month VarChar(255) Out
As

So, change the line...
Set prminput = .CreateParameter("@GetMonth", adVarChar, adParamInput, , strMonth)
To
Set prminput = .CreateParameter("@GetMonth", adVarChar, adParamInput, [red]100[/red], strMonth)

The value 100 is just an example. You should use whatever value you see in the Create Procedure Parameter.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[BobRodes]

What is seen in the the FAQ as

    Set prmInput = .CreateParameter("@cid", adInteger, adParamInput, , InCID)
    Set prmOutput = .CreateParameter("@cname", adVarWChar, adParamOutput, 255)
    .Parameters.Append prmInput
    .Parameters.Append prmOutput

Will often be seen as

    .Parameters.Append .CreateParameter("@cid", adInteger, adParamInput, , InCID)
    .Parameters.Append .CreateParameter("@cname", adVarWChar, adParamOutput, 255)

Either way is a perfectly acceptable method of creating parameters.

If you're having a tough time getting the parameters right, you can also do this:

spGetMonth.Parameters.Refresh

This will go to the server, find what parameters the sp expects, and populate the Parameters collection correctly. This is generally avoided where possible in production code, as it requires 2 trips to the server where 1 will do. But you can evaluate the parameters collection after calling it to find out what parameters you need to provide, so it's very useful in certain development environments.

Further reading from msdn:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthadorefresh.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdcolparameters.asp

HTH

Bob

 

[prettyangel]

This is to thank all who responded to my post.
Though I ended up not using that method to pass parameters to my crystal report because I was getting confused with how to pass the created parameter to crystal reports, so I ended up using a selection formula instead.

Thank you all.

Though I would highly appreciate it if someone could still help show me how I should have done it using store procedures.

 

[BobRodes]

<Though I would highly appreciate it if someone could still help show me how I should have done it using store procedures.

The FAQ that George provided shows that.

Bob