selecting from database

[suganya]

Hi


I have to select the values and display in the textboxes named txtcomm and txtdate from comm table. For that I have given the coding as following.

In stored pro I have given query as

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER Procedure [dbo].[sp_CommisionSelect]
	@ID int,	
	@Comm decimal(2,2),
	@EffectiveDate datetime
	
as
Begin

SELECT Comm=@Comm, EffectiveDate=@EffectiveDate FROM comm WHERE ID=@ID

End

In front I have given query as

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        'Request.QueryString["editid"]
        'If Page.IsValid Then
        Me.Label1.Text = Request.QueryString("editid")
        Dim con As SqlConnection
        Dim cmd, cmd1, cmd2 As New SqlCommand
        Dim str As String
        Dim rd As SqlDataReader
        str = "user id=sa;password=cast;database=sjc;server=AURORA-SERVER"
        con = New SqlConnection(str)
        'Try
        con.Open()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "sp_CommisionSelect"
        cmd.Connection = con
      
        Dim ID As New SqlParameter("@ID", SqlDbType.Int)
        'ID.Direction = ParameterDirection.Output
        ID.Value = Label1.Text.ToString()

        'CDID1.Value = CDID;

        Dim Comm As New SqlParameter("@Comm", SqlDbType.Decimal, 2)
        'Comm.Value = Convert.ToDecimal(txtcomm.Text.ToString())
        Comm.Direction = ParameterDirection.Output

        Dim EffectiveDate As New SqlParameter("@EffectiveDate", SqlDbType.DateTime)
        'EffectiveDate.Value = CDate(txtdate.Text.ToString())
        EffectiveDate.Direction = ParameterDirection.Output

        cmd.Parameters.Add(ID)
        cmd.Parameters.Add(Comm)
        cmd.Parameters.Add(EffectiveDate)
        Dim cnt As Int16
        cnt = cmd.ExecuteNonQuery()

        'cnt = cmd.ExecuteScalar()
        'cmd1.CommandType = CommandType.StoredProcedure
        'cmd1.CommandText = "sp_CommisionUpdate"
        'cmd1.Connection = con
        'Dim Comm1 As New SqlParameter("@Comm", SqlDbType.Decimal, 2)
        'Comm1.Value = Convert.ToDecimal(txtcomm.Text.ToString())

        'Dim EffectiveDate1 As New SqlParameter("@EffectiveDate", SqlDbType.DateTime)
        'EffectiveDate1.Value = CDate(txtdate.Text.ToString())

        'cmd1.Parameters.Add(Comm1)
        'cmd1.Parameters.Add(EffectiveDate1)
        'cmd1.ExecuteNonQuery()
        con.Close()

        'End If
    End Sub

Once I run, it is showing error as

The formal parameter "@ID" was not declared as an OUTPUT parameter, but the actual parameter passed in requested output.

[DrBunchman]

Hi suganya,

The error explains itself really - you've defined all of your parameters as output parameters which means the the database is expecting to return the value of the parameter to the calling process.

If you meant to do this then you need to change your stored procedure to define them as output parameters when you declare them, like this:

@ID int OUTPUT,	
@Comm decimal(2,2) OUTPUT,
@EffectiveDate datetime OUTPUT

If you don't actually want the value of the parameters returned (and looking at your stored proc I can't see why you would as you don't change them at all) then remove the lines where you set the direction property for each of your parameters.

Hope this helps,

Dr B

PS Please remember to wrap your code in CODE tags using the # button - this makes your posts much easier to read.

[suganya]

Hi

Again I modified my code as

@ID int,	
	@Comm decimal(2,2) OUTPUT,
	@EffectiveDate datetime OUTPUT

in DB.

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        'Request.QueryString["editid"]
        'If Page.IsValid Then
        Me.Label1.Text = Request.QueryString("editid")
        Dim con As SqlConnection
        Dim cmd, cmd1, cmd2 As New SqlCommand
        Dim str As String
        Dim rd As SqlDataReader
        str = "user id=sa;password=cast;database=sjc;server=AURORA-SERVER"
        con = New SqlConnection(str)
        'Try
        con.Open()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "sp_CommisionSelect"
        cmd.Connection = con



            

        Dim ID As New SqlParameter("@ID", SqlDbType.Int)
        'ID.Direction = ParameterDirection.Output
        'ID.Value = Label1.Text.ToString()
        ID.Value = Label1.Text

        'CDID1.Value = CDID;

        Dim Comm As New SqlParameter("@Comm", SqlDbType.Decimal, 2)
        'Comm.Value = Convert.ToDecimal(txtcomm.Text.ToString())
        Comm.Direction = ParameterDirection.Output


        Dim EffectiveDate As New SqlParameter("@EffectiveDate", SqlDbType.DateTime)
        'EffectiveDate.Value = CDate(txtdate.Text.ToString())
        EffectiveDate.Direction = ParameterDirection.Output



        cmd.Parameters.Add(ID)
        cmd.Parameters.Add(Comm)
        cmd.Parameters.Add(EffectiveDate)
        Dim cnt As Int16
        cnt = cmd.ExecuteNonQuery()
        con.Close()
    End Sub

but I have commented some lines that specify the textboxes.
eg.
'Comm.Value = Convert.ToDecim al(txtcomm.Text .ToString())

I think, I have to specify both the ParameterDirect ion.Output and the textbox name.

[DrBunchman]

Why are you setting the parameters direction to output? If your stored procedure is just returning a record set then this is unnecessary.

Dr B

[suganya]

Hi

My stored procedure have to select and return data (comm, effective date) from comm table based on id. Thats why

[DrBunchman]

You could do this rather than trying to extract the variables as ouputs

SELECT Comm, EffectiveDate FROM comm WHERE ID=@ID

Then retrieve them with a data reader.

Or am I missing the point? :-)

Dr B