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.SqlConnection
Dim cmdDG As SqlClient.SqlCommand
Dim drDG As SqlClient.SqlDataReader
Dim dtDueYear As String
Dim sqldataAdapter1 As SqlClient.SqlDataAdapter
dtDueYear = Trim(txtYear.Text)
' If (IsDate(dtDueYear) = True) Then
conDG = New
SqlClient.SqlConnection("Server=SQLDB;UID=PWD;pwd= ;Database=SampleDB")
cmdDG = New SqlClient.SqlCommand("USE testdb EXEC
sp_CrossTabTask
'Task_T','Initial_CH','Initials','Convert(char(3), Due_Date_DT)','DATEPART(YYYY,Due_Date_DT)'", conDG).Parameters(@dDate).Value ' = 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****@discussions.microsoft.com> wrote in message
news:5D**********************************@microsof t.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.SqlConnection
Dim cmdDG As SqlClient.SqlCommand
Dim drDG As SqlClient.SqlDataReader
Dim dtDueYear As String
Dim sqldataAdapter1 As SqlClient.SqlDataAdapter
dtDueYear = Trim(txtYear.Text)
' If (IsDate(dtDueYear) = True) Then
conDG = New
SqlClient.SqlConnection("Server=SQLDB;UID=PWD;pwd= ;Database=SampleDB")
cmdDG = New SqlClient.SqlCommand("USE testdb EXEC
sp_CrossTabTask
'Task_T','Initial_CH','Initials','Convert(char(3), Due_Date_DT)','DATEPART(YYYY,Due_Date_DT)'",
conDG).Parameters(@dDate).Value ' = 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 SqlParameterCollection only accepts non-null SqlParameter type objects,
not String objects.
-------------------------
cmdDG = New
SqlClient.SqlCommand("sp_CrossTabTask('Task_T','mo nth(Due_Date_DT)','MonthInitials','Initial_CH','CO NVERT(char(3),Due_Date_DT)'

cmdDG.CommandType = CommandType.StoredProcedure
cmdDG.Parameters.Add("@table", sysname) ' There is no sysname for dbType
cmdDG.Parameters.Add("@onrows", "month(Due_Date_DT)") '
cmdDG.Parameters.Add("@onrowsalias", "MonthsInitials")
cmdDG.Parameters.Add("@oncols", Trim(txtYear.Text).tostring)
sp_CrossTabTask
'Task_T','month(Due_Date_DT)','MonthsInitials','Co nvert(char93),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.SqlCommand("sp_CrossTabTask")
cmdDG.CommandType = CommandType.StoredProcedure

cmdDG.Parameters.Add("@table", value_of_table)
cmdDG.Parameters.Add("@onrows", whatever_here)
cmdDG.Parameters.Add("@onrowsalias", whatever_here)
cmdDG.Parameters.Add("@oncols", whatever_here)

"gurvar" <gu****@discussions.microsoft.com> wrote in message
news:EC**********************************@microsof t.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 SqlParameterCollection only accepts non-null SqlParameter type
objects,
not String objects.
-------------------------
cmdDG = New
SqlClient.SqlCommand("sp_CrossTabTask('Task_T','mo nth(Due_Date_DT)','MonthInitials','Initial_CH','CO NVERT(char(3),Due_Date_DT)'

cmdDG.CommandType = CommandType.StoredProcedure
cmdDG.Parameters.Add("@table", sysname) ' There is no sysname for dbType
cmdDG.Parameters.Add("@onrows", "month(Due_Date_DT)") '
cmdDG.Parameters.Add("@onrowsalias", "MonthsInitials")
cmdDG.Parameters.Add("@oncols", Trim(txtYear.Text).tostring)
sp_CrossTabTask
'Task_T','month(Due_Date_DT)','MonthsInitials','Co nvert(char93),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.Parameters.Add("@ddate", trim(txtvalue.text))

Thanks in advance again...

"Brendan Green" wrote:

Try something like this:

cmdDG = New SqlClient.SqlCommand("sp_CrossTabTask")
cmdDG.CommandType = CommandType.StoredProcedure

cmdDG.Parameters.Add("@table", value_of_table)
cmdDG.Parameters.Add("@onrows", whatever_here)
cmdDG.Parameters.Add("@onrowsalias", whatever_here)
cmdDG.Parameters.Add("@oncols", whatever_here)

"gurvar" <gu****@discussions.microsoft.com> wrote in message
news:EC**********************************@microsof t.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 SqlParameterCollection only accepts non-null SqlParameter type
objects,
not String objects.
-------------------------
cmdDG = New
SqlClient.SqlCommand("sp_CrossTabTask('Task_T','mo nth(Due_Date_DT)','MonthInitials','Initial_CH','CO NVERT(char(3),Due_Date_DT)'

cmdDG.CommandType = CommandType.StoredProcedure
cmdDG.Parameters.Add("@table", sysname) ' There is no sysname for dbType
cmdDG.Parameters.Add("@onrows", "month(Due_Date_DT)") '
cmdDG.Parameters.Add("@onrowsalias", "MonthsInitials")
cmdDG.Parameters.Add("@oncols", Trim(txtYear.Text).tostring)
sp_CrossTabTask
'Task_T','month(Due_Date_DT)','MonthsInitials','Co nvert(char93),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****@discussions.microsoft.com> wrote in message
news:7B**********************************@microsof t.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.Parameters.Add("@ddate", trim(txtvalue.text))

Thanks in advance again...

"Brendan Green" wrote:

Try something like this:

cmdDG = New SqlClient.SqlCommand("sp_CrossTabTask")
cmdDG.CommandType = CommandType.StoredProcedure

cmdDG.Parameters.Add("@table", value_of_table)
cmdDG.Parameters.Add("@onrows", whatever_here)
cmdDG.Parameters.Add("@onrowsalias", whatever_here)
cmdDG.Parameters.Add("@oncols", whatever_here)

"gurvar" <gu****@discussions.microsoft.com> wrote in message
news:EC**********************************@microsof t.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 SqlParameterCollection only accepts non-null SqlParameter type
> objects,
> not String objects.
> -------------------------
> cmdDG = New
> SqlClient.SqlCommand("sp_CrossTabTask('Task_T','mo nth(Due_Date_DT)','MonthInitials','Initial_CH','CO NVERT(char(3),Due_Date_DT)'
>
> cmdDG.CommandType = CommandType.StoredProcedure
> cmdDG.Parameters.Add("@table", sysname) ' There is no sysname for
> dbType
> cmdDG.Parameters.Add("@onrows", "month(Due_Date_DT)") '
> cmdDG.Parameters.Add("@onrowsalias", "MonthsInitials")
> cmdDG.Parameters.Add("@oncols", Trim(txtYear.Text).tostring)
>
>
> sp_CrossTabTask
> 'Task_T','month(Due_Date_DT)','MonthsInitials','Co nvert(char93),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
>>
>>
>>