PARAMETER TO Stored Procedure

[gurvar]

hI,
i'M TRTYING TO PASS PARAMETER TO A SP.BUT GETTING "OUT OF RANGE ERROR ERROR
FOLLOWING IS THE CODE:
Dim conDG As SqlClient.SqlCo nnection
Dim cmdDG As SqlClient.SqlCo mmand
Dim drDG As SqlClient.SqlDa taReader
Dim dtDueYear As String
Dim sqldataAdapter1 As SqlClient.SqlDa taAdapter
dtDueYear = Trim(txtYear.Te xt)
' If (IsDate(dtDueYe ar) = True) Then
conDG = New
SqlClient.SqlCo nnection("Serve r=SQLDB;UID=PWD ;pwd=;Database= SampleDB")
cmdDG = New SqlClient.SqlCo mmand("USE testdb EXEC
sp_CrossTabTask
'Task_T','Initi al_CH','Initial s','Convert(cha r(3),Due_Date_D T)','DATEPART(Y YYY,Due_Date_DT )'", conDG).Paramete rs(@dDate).Valu e ' = trim(dtDueYear. text)

where dDate is the parameter I have declared int the Stored procedure.

Thanks in advance..

[Brendan Green]

1. Specify the database that you want to operate against in the connection
string, rather than in the command.
2. In the command, specify only the stored proc name.
3. Set the command to be of CommandType = StoredProc.
4. Set each parameter for the command.
5. Execute the command.

Your current command string is executing the stored proc directly, and
passes the parameters to it.

Apart from that, your parameter @dDate is not in the parameters collection,
thus the out of range error.

"gurvar" <gu****@discuss ions.microsoft. com> wrote in message
news:5D******** *************** ***********@mic rosoft.com...

hI,
i'M TRTYING TO PASS PARAMETER TO A SP.BUT GETTING "OUT OF RANGE ERROR
ERROR
FOLLOWING IS THE CODE:
Dim conDG As SqlClient.SqlCo nnection
Dim cmdDG As SqlClient.SqlCo mmand
Dim drDG As SqlClient.SqlDa taReader
Dim dtDueYear As String
Dim sqldataAdapter1 As SqlClient.SqlDa taAdapter
dtDueYear = Trim(txtYear.Te xt)
' If (IsDate(dtDueYe ar) = True) Then
conDG = New
SqlClient.SqlCo nnection("Serve r=SQLDB;UID=PWD ;pwd=;Database= SampleDB")
cmdDG = New SqlClient.SqlCo mmand("USE testdb EXEC
sp_CrossTabTask
'Task_T','Initi al_CH','Initial s','Convert(cha r(3),Due_Date_D T)','DATEPART(Y YYY,Due_Date_DT )'",
conDG).Paramete rs(@dDate).Valu e ' = trim(dtDueYear. text)

where dDate is the parameter I have declared int the Stored procedure.

Thanks in advance..

[Cor Ligthert [MVP]]

Gurvar,

Have a look at this sample on our website. That you use a Stored Procedure
is completely inrelevant for programming with AdoNet. It only tells that you
have the command on the server.

http://www.vb-tips.com/default.aspx?...6-7139b8970071

I hope this helps,

Cor

[gurvar]

Thanks for a very informative link. I was able to do the con/cmd.
but I'm stuck at the Add parameter part.
Following is the error I'm getting:

The SqlParameterCol lection only accepts non-null SqlParameter type objects,
not String objects.
-------------------------
cmdDG = New
SqlClient.SqlCo mmand("sp_Cross TabTask('Task_T ','month(Due_Da te_DT)','MonthI nitials','Initi al_CH','CONVERT (char(3),Due_Da te_DT)'

cmdDG.CommandTy pe = CommandType.Sto redProcedure
cmdDG.Parameter s.Add("@table", sysname) ' There is no sysname for dbType
cmdDG.Parameter s.Add("@onrows" , "month(Due_Date _DT)") '
cmdDG.Parameter s.Add("@onrowsa lias", "MonthsInitials ")
cmdDG.Parameter s.Add("@oncols" , Trim(txtYear.Te xt).tostring)
sp_CrossTabTask
'Task_T','month (Due_Date_DT)', 'MonthsInitials ','Convert(char 93),Due_Date)DT =
'2003') --works on SQL server

@table AS sysname, --Table to crosstab
@onrows AS nvarchar(128), --Grouping key values(on rows)
@onrowsalias AS sysname = NULL, --Alias for grouping column
@oncols AS nvarchar(128), --Destination columns (on columns)
@Date AS nvarchar(128),
@sumcol AS sysname = NULL --Data cells

Thanks,
Sangeetha
"Cor Ligthert [MVP]" wrote:

Gurvar,

Have a look at this sample on our website. That you use a Stored Procedure
is completely inrelevant for programming with AdoNet. It only tells that you
have the command on the server.

http://www.vb-tips.com/default.aspx?...6-7139b8970071

I hope this helps,

Cor

[Brendan Green]

Try something like this:

cmdDG = New SqlClient.SqlCo mmand("sp_Cross TabTask")
cmdDG.CommandTy pe = CommandType.Sto redProcedure

cmdDG.Parameter s.Add("@table", value_of_table)
cmdDG.Parameter s.Add("@onrows" , whatever_here)
cmdDG.Parameter s.Add("@onrowsa lias", whatever_here)
cmdDG.Parameter s.Add("@oncols" , whatever_here)

"gurvar" <gu****@discuss ions.microsoft. com> wrote in message
news:EC******** *************** ***********@mic rosoft.com...

Thanks for a very informative link. I was able to do the con/cmd.
but I'm stuck at the Add parameter part.
Following is the error I'm getting:

The SqlParameterCol lection only accepts non-null SqlParameter type
objects,
not String objects.
-------------------------
cmdDG = New
SqlClient.SqlCo mmand("sp_Cross TabTask('Task_T ','month(Due_Da te_DT)','MonthI nitials','Initi al_CH','CONVERT (char(3),Due_Da te_DT)'

cmdDG.CommandTy pe = CommandType.Sto redProcedure
cmdDG.Parameter s.Add("@table", sysname) ' There is no sysname for dbType
cmdDG.Parameter s.Add("@onrows" , "month(Due_Date _DT)") '
cmdDG.Parameter s.Add("@onrowsa lias", "MonthsInitials ")
cmdDG.Parameter s.Add("@oncols" , Trim(txtYear.Te xt).tostring)
sp_CrossTabTask
'Task_T','month (Due_Date_DT)', 'MonthsInitials ','Convert(char 93),Due_Date)DT
=
'2003') --works on SQL server

@table AS sysname, --Table to crosstab
@onrows AS nvarchar(128), --Grouping key values(on rows)
@onrowsalias AS sysname = NULL, --Alias for grouping column
@oncols AS nvarchar(128), --Destination columns (on columns)
@Date AS nvarchar(128),
@sumcol AS sysname = NULL --Data cells

Thanks,
Sangeetha
"Cor Ligthert [MVP]" wrote:

Gurvar,

Have a look at this sample on our website. That you use a Stored
Procedure
is completely inrelevant for programming with AdoNet. It only tells that
you
have the command on the server.

http://www.vb-tips.com/default.aspx?...6-7139b8970071

I hope this helps,

Cor

[gurvar]

Hi,
Thanks for all the help.I have come really close.
But having problem with sending the last input parameter which goes in the
WHERE clause as month(Due_Date_ DT) = @ddate

I'm getting error at "GROUP". Is it because the date charecter is declared
as @ddate nvarchar(128) but assigned to a Date funtion. But sent as a string
from UI?

cmdDG.Parameter s.Add("@ddate", trim(txtvalue.t ext))

Thanks in advance again...

"Brendan Green" wrote:

Try something like this:

cmdDG = New SqlClient.SqlCo mmand("sp_Cross TabTask")
cmdDG.CommandTy pe = CommandType.Sto redProcedure

cmdDG.Parameter s.Add("@table", value_of_table)
cmdDG.Parameter s.Add("@onrows" , whatever_here)
cmdDG.Parameter s.Add("@onrowsa lias", whatever_here)
cmdDG.Parameter s.Add("@oncols" , whatever_here)

"gurvar" <gu****@discuss ions.microsoft. com> wrote in message
news:EC******** *************** ***********@mic rosoft.com...

Thanks for a very informative link. I was able to do the con/cmd.
but I'm stuck at the Add parameter part.
Following is the error I'm getting:

The SqlParameterCol lection only accepts non-null SqlParameter type
objects,
not String objects.
-------------------------
cmdDG = New
SqlClient.SqlCo mmand("sp_Cross TabTask('Task_T ','month(Due_Da te_DT)','MonthI nitials','Initi al_CH','CONVERT (char(3),Due_Da te_DT)'

cmdDG.CommandTy pe = CommandType.Sto redProcedure
cmdDG.Parameter s.Add("@table", sysname) ' There is no sysname for dbType
cmdDG.Parameter s.Add("@onrows" , "month(Due_Date _DT)") '
cmdDG.Parameter s.Add("@onrowsa lias", "MonthsInitials ")
cmdDG.Parameter s.Add("@oncols" , Trim(txtYear.Te xt).tostring)
sp_CrossTabTask
'Task_T','month (Due_Date_DT)', 'MonthsInitials ','Convert(char 93),Due_Date)DT
=
'2003') --works on SQL server

@table AS sysname, --Table to crosstab
@onrows AS nvarchar(128), --Grouping key values(on rows)
@onrowsalias AS sysname = NULL, --Alias for grouping column
@oncols AS nvarchar(128), --Destination columns (on columns)
@Date AS nvarchar(128),
@sumcol AS sysname = NULL --Data cells

Thanks,
Sangeetha
"Cor Ligthert [MVP]" wrote:

Gurvar,

Have a look at this sample on our website. That you use a Stored
Procedure
is completely inrelevant for programming with AdoNet. It only tells that
you
have the command on the server.

http://www.vb-tips.com/default.aspx?...6-7139b8970071

I hope this helps,

Cor

[Brendan Green]

Can you post the exact error message, along with the code for the stored
proc?

"gurvar" <gu****@discuss ions.microsoft. com> wrote in message
news:7B******** *************** ***********@mic rosoft.com...

Hi,
Thanks for all the help.I have come really close.
But having problem with sending the last input parameter which goes in the
WHERE clause as month(Due_Date_ DT) = @ddate

I'm getting error at "GROUP". Is it because the date charecter is declared
as @ddate nvarchar(128) but assigned to a Date funtion. But sent as a
string
from UI?

cmdDG.Parameter s.Add("@ddate", trim(txtvalue.t ext))

Thanks in advance again...

"Brendan Green" wrote:

Try something like this:

cmdDG = New SqlClient.SqlCo mmand("sp_Cross TabTask")
cmdDG.CommandTy pe = CommandType.Sto redProcedure

cmdDG.Parameter s.Add("@table", value_of_table)
cmdDG.Parameter s.Add("@onrows" , whatever_here)
cmdDG.Parameter s.Add("@onrowsa lias", whatever_here)
cmdDG.Parameter s.Add("@oncols" , whatever_here)

"gurvar" <gu****@discuss ions.microsoft. com> wrote in message
news:EC******** *************** ***********@mic rosoft.com...

> Thanks for a very informative link. I was able to do the con/cmd.
> but I'm stuck at the Add parameter part.
> Following is the error I'm getting:
>
> The SqlParameterCol lection only accepts non-null SqlParameter type
> objects,
> not String objects.
> -------------------------
> cmdDG = New
> SqlClient.SqlCo mmand("sp_Cross TabTask('Task_T ','month(Due_Da te_DT)','MonthI nitials','Initi al_CH','CONVERT (char(3),Due_Da te_DT)'
>
> cmdDG.CommandTy pe = CommandType.Sto redProcedure
> cmdDG.Parameter s.Add("@table", sysname) ' There is no sysname for
> dbType
> cmdDG.Parameter s.Add("@onrows" , "month(Due_Date _DT)") '
> cmdDG.Parameter s.Add("@onrowsa lias", "MonthsInitials ")
> cmdDG.Parameter s.Add("@oncols" , Trim(txtYear.Te xt).tostring)
>
>
> sp_CrossTabTask
> 'Task_T','month (Due_Date_DT)', 'MonthsInitials ','Convert(char 93),Due_Date)DT
> =
> '2003') --works on SQL server
>
> @table AS sysname, --Table to crosstab
> @onrows AS nvarchar(128), --Grouping key values(on rows)
> @onrowsalias AS sysname = NULL, --Alias for grouping column
> @oncols AS nvarchar(128), --Destination columns (on columns)
> @Date AS nvarchar(128),
> @sumcol AS sysname = NULL --Data cells
>
> Thanks,
> Sangeetha
> "Cor Ligthert [MVP]" wrote:
>
>> Gurvar,
>>
>> Have a look at this sample on our website. That you use a Stored
>> Procedure
>> is completely inrelevant for programming with AdoNet. It only tells
>> that
>> you
>> have the command on the server.
>>
>> http://www.vb-tips.com/default.aspx?...6-7139b8970071
>>
>> I hope this helps,
>>
>> Cor
>>
>>
>>