locked
Insert using Stored Procedure C# RRS feed

  • Question

  • User31738214 posted
    Stored Procedure
    ALTER PROCEDURE [dbo].[usp_InsertPoint]
    
    @Points int,
    @ID int,
    @num int,
    @PointDate DATETIME,
    @Comment VARCHAR(50),
    @pointID int OUTPUT
    AS
    SET NOCOUNT OFF
    insert into point (totalPoint,ID,studentNum,pointDate,pointComment)
    values (@Points,@ID,@num,@PointDate,@Comment)
    SELECT @pointID = SCOPE_IDENTITY() WHERE @@ROWCOUNT = 1;
    
    C# Code
    
    
      string commandText = "usp_InsertPoint";
                    using (SqlConnection conn = new SqlConnection(connectionString))
                    {
                        string point = ddlPoint.SelectedItem.Value;
                        string comment = txtCommentPoint.Text;
                        string studentID = GridView1.SelectedRow.Cells[2].Text;
                        string Office = GridView1.SelectedRow.Cells[5].Text;
                        SqlCommand cmd = new SqlCommand(commandText, conn);
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add(new SqlParameter("@Points", SqlDbType.Int)).Value = point;
                        cmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int)).Value = Office;
                        cmd.Parameters.Add(new SqlParameter("@num", SqlDbType.Int)).Value = studentID;
                        cmd.Parameters.Add(new SqlParameter("@Comment", SqlDbType.VarChar, 50)).Value = comment;
                        SqlParameter ParamId = cmd.Parameters.Add("@pointID", SqlDbType.Int);
                        ParamId.Direction = ParameterDirection.Output;
                        int pointID = Convert.ToInt32(ParamId.Value);
                        conn.Open();
                        cmd.ExecuteNonQuery();
                    }
    
                }
                catch (Exception ex)
                {
    
                    ex.ToString();
                }
                finally
                {
                    GridView1.DataBind();
                    conn.Close();}

    My intent is for a user to select a droplist item and then populate a gridview. When gridview is selected choose how many points you would like to give the user with a comment as to why. I am able to grab all values that i need and when i was debugging i noticed that my insert code was not generating the next value for pointID when it should be auto incrementing..

    Any suggestions would greatly help.

    Thank-you

    Saturday, March 30, 2013 6:06 PM

All replies

  • User-1360095595 posted

    You need to read the output parameter AFTER you execute the query. Also, I don't see where you're specifying the pointdate parameter. 

    Saturday, March 30, 2013 6:26 PM
  • User-1716253493 posted
    insert into point (totalPoint,studentNum, pointDate,pointComment) values ( @Points,@num ,@ PointDate ,@Comment )
    Saturday, March 30, 2013 6:30 PM
  • User31738214 posted

    Failed to convert parameter value from a String to a Int32. and Input string was not in correct format those are the errors

    try
                {
                    string commandText = "usp_InsertPoint";
                    using (SqlConnection conn = new SqlConnection(connectionString))
                    {
                        string point = ddlPoint.SelectedItem.Value;
                        string comment = txtCommentPoint.Text;
                        string studentID = GridView1.SelectedRow.Cells[2].Text;
                        string Office = GridView1.SelectedRow.Cells[5].Text;
                        SqlCommand cmd = new SqlCommand(commandText, conn);
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add(new SqlParameter("@Points", SqlDbType.Int)).Value = point;
                        cmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int)).Value =Convert.ToInt32(Office);
                        cmd.Parameters.Add(new SqlParameter("@num", SqlDbType.Int)).Value =Convert.ToInt32(studentID);
                        //cmd.Parameters.Add(new SqlParameter("@pointDate", SqlDbType.DateTime)).ToString();
                        cmd.Parameters.Add(new SqlParameter("@Comment", SqlDbType.VarChar, 50)).Value = comment;
                        SqlParameter ParamId = cmd.Parameters.Add("@pointID", SqlDbType.Int);
                        ParamId.Direction = ParameterDirection.Output;
                        int pointID = Convert.ToInt32(ParamId.Value);
                        conn.Open();
                        cmd.ExecuteNonQuery();
                    }
    
                }
                catch (Exception ex)
                {
    
                    throw (ex);
                }
                finally
                {
                    GridView1.DataBind();
                    //conn.Close();
    
                }
            


    im running into now

    Saturday, March 30, 2013 6:48 PM
  • User31738214 posted

    Failed to convert parameter value from a String to a Int32. and Input string was not in correct format those are the errors

    try
                {
                    string commandText = "usp_InsertPoint";
                    using (SqlConnection conn = new SqlConnection(connectionString))
                    {
                        string point = ddlPoint.SelectedItem.Value;
                        string comment = txtCommentPoint.Text;
                        string studentID = GridView1.SelectedRow.Cells[2].Text;
                        string Office = GridView1.SelectedRow.Cells[5].Text;
                        SqlCommand cmd = new SqlCommand(commandText, conn);
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add(new SqlParameter("@Points", SqlDbType.Int)).Value = point;
                        cmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int)).Value =Convert.ToInt32(Office);
                        cmd.Parameters.Add(new SqlParameter("@num", SqlDbType.Int)).Value =Convert.ToInt32(studentID);
                        //cmd.Parameters.Add(new SqlParameter("@pointDate", SqlDbType.DateTime)).ToString();
                        cmd.Parameters.Add(new SqlParameter("@Comment", SqlDbType.VarChar, 50)).Value = comment;
                        SqlParameter ParamId = cmd.Parameters.Add("@pointID", SqlDbType.Int);
                        ParamId.Direction = ParameterDirection.Output;
                        int pointID = Convert.ToInt32(ParamId.Value);
                        conn.Open();
                        cmd.ExecuteNonQuery();
                    }
    
                }
                catch (Exception ex)
                {
    
                    throw (ex);
                }
                finally
                {
                    GridView1.DataBind();
                    //conn.Close();
    
                }
            


    im running into now

    Saturday, March 30, 2013 6:48 PM