We help IT Professionals succeed at work.

incremental update stored procedure return value

shozam
shozam asked
on
hi there; using asp.net (c#, 3.5) i would like to send my stored procedure a value (ie. myPage) and have my stored procedure:

1. select the record that pertains to this value in my hit counter table,
2. update the current stored value by 1
3. return the new value to my .aspx page

here is my existing code:

.aspx.cs:

        string strMyPage= txtMyPage.Text;

        string strConnection = ConfigurationSettings.AppSettings["myDB"];

        SqlConnection myConnection = new SqlConnection(strConnection);

        SqlCommand myCommand = new SqlCommand("CSP_Update_HitCounter", myConnection);
        myCommand.CommandType = CommandType.StoredProcedure;

        myCommand.Parameters.Add("@myPage", SqlDbType.Text).Value = strMyPage;

        myCommand.Connection.Open();
        myCommand.ExecuteNonQuery();
        myCommand.Connection.Close();
        myCommand.Connection.Dispose();

stored procedure:

(
@count int,
@myPage varchar(50)
)
AS
UPDATE dbo.tblCount
SET @count = fldCount + 1
WHERE(fldPage= @myPage)
      /* SET NOCOUNT ON */
      RETURN @count

This is my best crack at it but isn't quite right.  Also not sure how to retrieve the returned value in my asp.cs page.

suggestions?  thanks all.
Comment
Watch Question

Commented:
Here we go here is an example

 Dim cmd As New SqlCommand("forgot_password", con)
            cmd.CommandType = CommandType.StoredProcedure
         
   'Output Parameters'

            cmd.Parameters.Add(New SqlParameter("@email", ""))
            cmd.Parameters(0).SqlDbType = SqlDbType.VarChar
            cmd.Parameters(0).Size = 50
            cmd.Parameters(0).Direction = ParameterDirection.Output
            cmd.Parameters.Add(New SqlParameter("@username", username))
            cmd.ExecuteNonQuery()

Use the output variable however you need  :P
         
   If cmd.Parameters("@email").Value Is DBNull.Value Then
                lbl_error.Text = "Please check the username"
            Else

Commented:
  Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("db").ConnectionString)
        con.Open()

For opening the connection.

Author

Commented:
thanks for the response, abhi376.  

anyone else?

Commented:
Hey shozam ,

you need anything let me know
Commented:
hi, abhi376; i was just looking for a little more complete response and in c# as i'm just getting familiar with c# syntax (coming from a vb background).

also, your response did not address the stored procedure part of my question.

figured it out on though:

.cs:

        int intCount = 0;

        string strConnection = ConfigurationSettings.AppSettings["myDB"];

        SqlConnection myConnection = new SqlConnection(strConnection);

        SqlCommand myCommand = new SqlCommand("CSP_Update_Count", myConnection);
        myCommand.CommandType = CommandType.StoredProcedure;

        myCommand.Parameters.Add("@countName", SqlDbType.VarChar).Value = (strCountName);


        SqlParameter myCount = new SqlParameter("@count", SqlDbType.Int);
        myCount.Direction = ParameterDirection.Output;
        myCommand.Parameters.Add(myCount);


        myCommand.Connection.Open();
        myCommand.ExecuteNonQuery();


        intCount = Convert.ToInt32(myCount.Value);

        Response.Write(intCount);


        myCommand.Connection.Close();
        myCommand.Connection.Dispose();


stored procedure:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[CSP_Update_Count]

@countName varchar(50),

@count int Output

AS

BEGIN


SET NOCOUNT ON;


UPDATE
        dbo.tblCount
SET    
        fldCount = fldCount + 1
WHERE
        fldCountName = @countName


SELECT
        @count = fldCount
FROM
        dbo.tblCount
WHERE
        fldCountName = @countName

RETURN;

END

thanks anyway.