Help With SQL DATA Source Control

Can you show us the stored procedure code?

ok...yes...but let me elaborate on some new things I found out...even though Im still not getting anywhere....
I think somehow I have to get the value back as a return value....not as a dataset ....but can figure the code out to make those params work...
My first post is my revised vb.net code so far...and I will repost the sp.  By the way..the sp is excuting correctly....I can verify this by checkign the data on the server..
I just cant seem to capture the return value in my code using this datasource implementation.

Thanks

 Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
 
        dsOrbitCounter.SelectCommandType = SqlDataSourceCommandType.StoredProcedure
        dsOrbitCounter.SelectCommand = "sp_getOrbitCounter"
        dsOrbitCounter.SelectParameters.Add("@CounterNum", TypeCode.Int32, "12")
        dsOrbitCounter.SelectParameters(0).Direction = Data.ParameterDirection.Input
        dsOrbitCounter.SelectParameters.Add(New Parameter("Return value"))
        dsOrbitCounter.SelectParameters(1).Direction = Data.ParameterDirection.ReturnValue
 
        lblCH1RMANumber.Text = 
      
 
    End Sub

Select allOpen in new window

Here is the stored procedure ..which is working fine..but maybe this will help you see what I need to get it in my code behind so I can pass the return value to the label control.

CREATE PROCEDURE sp_getOrbitCounter (@@CounterNum int) AS
begin
	declare @@Current int,
		@CurrentCounter int
	--Get Current Value
	select @@current = currentcounter from midware..OrbitCounter where objectid = @@CounterNum
	--Increment Value
	update midware..orbitcounter set currentcounter = (@@Current + 1) where objectid = @@CounterNum
	--Return Incremented Value
	select @CurrentCounter = currentcounter from midware..OrbitCounter where objectid = @@CounterNum
	return @CurrentCounter
end
 
 
 
GO

Select allOpen in new window

my input is always 12...as that is the row its incrementing in this case...and my output value is always the incremented value in the sp.

I think it is the vb.net code here.  I think you need to convert the output to be integer.  So the syntax might be something like this


----vb code
 
    Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
 
        Dim sqlString As String = "sp_getOrbitCounter"
        dsOrbitCounter.SelectCommand = sqlString
        dsOrbitCounter.SelectCommandType = SqlDataSourceCommandType.StoredProcedure
        dsOrbitCounter.SelectParameters.Add("@CounterNum", "Int32", "12")
        Dim dbReader As SqlDataReader = dsOrbitCounter.Select(DataSourceSelectArguments.Empty)

        Dim tempInteger as Integer
 
        While dbReader.Read()
            tempInteger = Convert.ToInt32(dbReader("objectid").ToString())
            lblCH1RMANumber.Text = tempInteger
        End While
 
 
    End Sub

Place around with the syntax here.  I believe it is the converting part of the coding.  Hope this help!

dbReader contains no value...b/c the stored procedure is not passing a recordset..its passing a return value...
dbreader just ends the while loop

Can you set a default value in the stored procedure with 12
what is the purpose of passing a parameter if the value always the same?  Why not something like this
in your stored procedure.

CREATE PROCEDURE sp_getOrbitCounter
(
)
AS
               declare @@tempInt int
               set @@tempInt = 12
begin
                   declare @@Current int,
            @CurrentCounter int
      --Get Current Value
      select @@current = currentcounter from midware..OrbitCounter where objectid = @@tempInt
      --Increment Value
      update midware..orbitcounter set currentcounter = (@@Current + 1) where objectid = @@tempInt
      --Return Incremented Value
      select @CurrentCounter = currentcounter from midware..OrbitCounter where objectid = @@tempInt
      return @CurrentCounter
end

You need to change your VB code too if you change the stored procedure.  

The stored procedure is working fine.  I pass 12 only in this instance.  This sp is used in other business processes...
Either way..by modifying this stored procedure...this does not solve the question.

I need to get the return value from the SP in vb.net...that is all.  Thanks for your help

I notice something here too
---- stored procedure
CREATE PROCEDURE sp_getOrbitCounter (@@CounterNum int) AS


---VB
dsOrbitCounter.SelectParameters.Add("@CounterNum", "Int32", "12")


typo??  @@CounterNum and @CounterNum  ????

Thanks..but the parameter is not name specific...its placement....it assumes parameter(0) as the input...etc.

I just need to fix this .net code so it will set the return value from the sp to a variable or label ---cheers

I think you are correct about the return..but this solution is dependent on an ado.net solution..not using the sqldatasource/params.  There is not command object in the sqldatasource control....

:(  !!!!!!

ok so im taking this a different approach....lets go the ado.net way...and forget this datasource control..i cannot get it to work..and im on a tough deadline for somethign I thought was simple:)  

Here is my code using ado.net...im still not getting the return value

   Dim connString As String = ConfigurationManager.ConnectionStrings("atsapp1").ToString
        Dim myConnection As New SqlConnection(connString)
        myConnection.Open()
 
        Dim myCommand As New SqlCommand("sp_getOrbitCounter", myConnection)
        myCommand.CommandType = CommandType.StoredProcedure
 
        'Create a SqlParameter object to hold the output parameter value
        Dim incrementParam As New SqlParameter("@CurrentCounter", SqlDbType.Int)
 
        'IMPORTANT - must set Direction as Output
        incrementParam.Direction = ParameterDirection.ReturnValue
 
        'Finally, add the parameter to the Command's Parameters collection
        myCommand.Parameters.Add(incrementParam)
 
        'Call the sproc...
        Dim reader As SqlDataReader = myCommand.ExecuteReader()
 
        'Now you can grab the output parameter's value...
        Dim CH1RMANumber As Int32 = incrementParam.Value
        lblCH1RMANumber.Text = CH1RMANumber.ToString

Select allOpen in new window