Error converting data type NVarchar into int

Question

I'm inserting data in two tables at once...

CREATE PROCEDURE inserttwo
(
     @BookID int,
     @BookName nvarchar(50),
     @DateIssue datetime,
     @ReturnDate datetime,
     @PersonID int
)
AS
    INSERT INTO tblReturn(BookID, BookName, DateIssue, ReturnDate, PersonID)
    VALUES(@BookID, @BookName, @DateIssue, @ReturnDate, @PersonID)

    INSERT INTO tblIssue(BookID, BookName, DateIssue, ReturnDate, PersonID)
    VALUES(@BookID, @BookName, @DateIssue, @ReturnDate, @PersonID)

then I'm updating and deleting these tables by stored procedure...

Delete query:

ALTER PROCEDURE [dbo].[Issuedelete]
(@BookID int)
AS
    DELETE FROM tblIssue
    WHERE BookID = @BookID  

Update query:

ALTER PROCEDURE [dbo].[IssueUpdate]
   (@BookID int,
    @BookName nvarchar(50),
    @DateIssue datetime,
    @ReturnDate datetime,
    @PersonID int)
AS
    UPDATE tblIssue
    SET [BookID]     = @BookID , 
        [BookName]   = @BookName,
        [DateIssue]  = @DateIssue,
        [ReturnDate] = @ReturnDate,
        [PersonID]   = @PersonID
    WHERE BookID = @BookID

C# code to delete from tblIssue:

private void btnDelete_Click(object sender, EventArgs e)
{
    try
    {
        string c = ConfigurationManager.ConnectionStrings["LMS"].ConnectionString;

        SqlConnection con = new SqlConnection(c);
        con.Open();

        SqlCommand cmd = new SqlCommand("Issuedelete", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@BookID", ComBox1BookID.Text);

        cmd.ExecuteNonQuery();
        con.Close();
        storedproc();
    }
    catch(Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

C# code to update tblIssue:

private void btnupdate_Click(object sender, EventArgs e)
{
    try
    {
        string c = ConfigurationManager.ConnectionStrings["LMS"].ConnectionString;

        SqlConnection con = new SqlConnection(c);
        con.Open();

        SqlCommand cmd = new SqlCommand("IssueUpdate", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@BookID", ComBox1BookID.Text);
        cmd.Parameters.AddWithValue("@BookName", ComBox2BName.Text);
        cmd.Parameters.AddWithValue("@DateIssue", IssueDate.Value.ToString());
        cmd.Parameters.AddWithValue("@ReturnDate", ReturnDate.Value.ToString());
        cmd.Parameters.AddWithValue("@PersonID", CBox3PerID.Text);

        cmd.ExecuteNonQuery();

        con.Close();
        storedproc();
    }
    catch (Exception ex)
    {
        Console.WriteLine("SqlError" + ex);
    }
}

After compiling, I get an error

Can't convert datatype into int

I tried other ways by changing the parameters with OleDB and SqlDbType... But it's not deleting and updating records... And also having same problem when I'm updating and deleting into tblReturn... Please, help me??? :(

Answer 1

You are sending text values to integer and datetime parameters, Change your update button click event code to

        private void btnupdate_Click(object sender, EventArgs e)
        {
            try
            {

                string c = ConfigurationManager.ConnectionStrings["LMS"].ConnectionString;
                SqlConnection con = new SqlConnection(c);
                con.Open();
                SqlCommand cmd = new SqlCommand("IssueUpdate", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@BookID", Convert.ToInt32(ComBox1BookID.Text));
                cmd.Parameters.AddWithValue("@BookName", ComBox2BName.Text);
                cmd.Parameters.AddWithValue("@DateIssue", IssueDate.Value);
                cmd.Parameters.AddWithValue("@ReturnDate", ReturnDate.Value);
                cmd.Parameters.AddWithValue("@PersonID",Convert.ToInt32(CBox3PerID.Text));
                cmd.ExecuteNonQuery();
                con.Close();
                storedproc();
            }
            catch (Exception ex)
            {
                Console.WriteLine("SqlError" + ex);

            }
        }

Update Remove this line after set in your update stored procedure

[BookID] =@BookID ,

if its a identity column, it will throw exception on updating it.

Update 2

Try this cmd.Parameters.Add("@BookID", SqlDbType.Int).Value = Convert.ToInt32(ComBox1BookID.Text); if you are adding parameters with add function