system.data.sqlclient.sqlException on ExecuteNonQuery()

Question

I am trying to add new patient to my database in my clinic management project using this method:

public void create_Patient()
{
    SqlCommand c = new SqlCommand();
    c.CommandText = "EXECUTE SP_add_patient @ownerID, @ownername, @petname, @animaltype, @animalbirthyear, @owneradress, @ownerphone";
    c.Parameters.AddWithValue("@ownerID", this.ownerID.ToString());
    c.Parameters.AddWithValue("@ownername", this.ownername.ToString());
    c.Parameters.AddWithValue("@petname", this.petname.ToString());
    c.Parameters.AddWithValue("@animaltype", this.animalType.ToString());
    c.Parameters.AddWithValue("@animalbirthyear", this.animalBirthYear.ToString());
    c.Parameters.AddWithValue("@owneradress", this.ownerAddress.ToString());
    c.Parameters.AddWithValue("@ownerphone", this.ownerPhone.ToString());
    c.Parameters.AddWithValue("@owneremail", this.ownerEmail.ToString());

    SQL_CON SC = new SQL_CON();
    SC.execute_non_query(c);
}

I'm using the stored procedure:

CREATE PROCEDURE dbo.SP_add_patient
    @ownerID varchar(100)
    , @ownername varchar(100)
    , @petname varchar(100)
    , @animaltype varchar(100)
    , @animalbirthyear int
    , @owneradress varchar(100)
    , @ownerphone varchar(100)
    , @owneremail varchar(100) AS 
INSERT INTO dbo.patientValues (@ownerID
                              ,@ownername
                              ,@petname
                              ,@animaltype
                              ,@animalbirthyear
                              ,@owneradress
                              ,@ownerphone
                              ,@owneremail);

and in execute_non_query(c) I am using ExecuteNonQuery():

public void execute_non_query(SqlCommand cmd)
{

    try
    {
        // open a connection object
        this.conn.Open();
        cmd.Connection = conn;
        cmd.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
        MessageBox.Show("error", MessageBoxButtons.OK);
    }
    finally
    {
        if (conn != null)
        {
            conn.Close();
        }
    }
}

When I get to ExecuteNonQuery(), I get an exception:

system.data.sqlclient.sqlException exception.

I looked a for a solution and tried to change the identity specification, but the primary key type is varchar, and I can't change it to a numeric type (the primary key must be a name).

How can I solve it?

For what it's worth, your data access strategy is *very old.* Consider using Dapper or Entity Framework Core, both of which contain an `Execute` function that's much easier to use. — Robert Harvey, Jun 22 '20 at 22:28
Add the error details to your question, the value of ex.Message or ex.ToString()). The best practice when calling stored procedures to to specify CommandType.StroredProcedure and specify only the proc name in the CommandText. Also, do not use `sp_` as the proc name prefix as that denotes system stored procedures. — Dan Guzman, Jun 22 '20 at 22:29
You are using the wrong CommandType. See [How to execute a stored procedure within C# program](https://stackoverflow.com/a/1260961/719186) — LarsTech, Jun 22 '20 at 22:53
@LarsTech CommandType.Text works fine if you build out the batch with the parameters as OP did. — David Browne - Microsoft, Jun 22 '20 at 23:00
animalbirthyear is a number but you cast it to a string in your parameters. It is also a very bad smell when you have `toString()` all over the place if you code is written correctly and the data is the right type you should not need to cast to string. — Hogan, Jun 23 '20 at 02:16

score 0 · Answer 1 · answered Jun 22 '20 at 22:47

0

You've got a bug here:

    catch (Exception ex)
    {
        MessageBox.Show("error", MessageBoxButtons.OK);
    }

should be something like:

    catch (Exception ex)
    {
        MessageBox.Show(ex.Message, MessageBoxButtons.OK);
    }

answered Jun 22 '20 at 22:47

David Browne - Microsoft

80,331
6
39
67

1

Who knows... Maybe it's a feature :) – Legacy Code Jun 22 '20 at 23:35

score 0 · Answer 2 · answered Jun 23 '20 at 01:22

You can try it as below,

try
{
    SqlTransaction trx = null;
    connection = new SqlConnection(connectionString);
    connection.Open();
    trx = connection.BeginTransaction();
    
    SqlCommand c = new SqlCommand("[dbo].[SP_add_patient]", connection, trx);
    
    c.CommandType = CommandType.StoredProcedure;
    c.Parameters.AddWithValue(""@ownerID", this.ownerID.ToString());

    c.ExecuteNonQuery();
    trx.Commit();
    connection.Close();

}
catch (SqlException e)
{
                
}

system.data.sqlclient.sqlException on ExecuteNonQuery()

2 Answers2