PARAMETER TO Stored Procedure

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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..
 
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.
 
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','month(Due_Date_DT)','MonthInitials','Initial_CH','CONVERT(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','Convert(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
 
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)
 
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...
 
Can you post the exact error message, along with the code for the stored
proc?
 
Back
Top