CODEWITHSUNDEEP
c#databaseinsertprocedures
Andreas Oxinos
Andreas Oxinos

Reputation: 11

Stored procedure calling, a weird situation i'm facing

Using Visual Studio 2008, C#

For the past 2 days I'm facing a problem so I thought I would ask here. I have this stored procedure in my database called StoredProcedure1:

ALTER PROCEDURE dbo.StoredProcedure1
   @ID int,
   @Username varchar(10),
   @Pass varchar(10),
   @Status varchar(10)
AS
   INSERT INTO tPasswords(ID, fUsername, fPassword, fStatus)
   Values (@ID, @Username, @Pass, @Status)

   RETURN

I am simply adding a row into my table.

When I execute the procedure from the server explorer inside visual studio everything runs smoothly, and when I see the data into my database I see the new row I just added.

The problem is when I'm trying to run the procedure inside my program.

So for test inside an empty form I created a button and : 

using System.Data.Sql;
using System.Data.SqlClient;

private void button1_Click(object sender, EventArgs e)
{
    string a = "Andreas"; 
    string b = "Andreas2"; 
    string c = "Andreas3"; 
    int _id = 10;
     SqlConnection connection = new SqlConnection(
        Properties.Settings.Default.dPasswordsConnectionString);
    SqlCommand cmd = new SqlCommand(
        "StoredProcedure1", connection);
    connection.Open();
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandTimeout = 600;
    cmd.Parameters.Add("@ID", _id);
    cmd.Parameters.Add("@Username", a);
    cmd.Parameters.Add("@Pass", b);
    cmd.Parameters.Add("@Status", c);

    int i = cmd.ExecuteNonQuery();

    connection.Close();
}

When I press this button I don't have any errors or anything, when I press it twice it causes an error about the same primary key, so the procedure is working.

But when I go to my database I don't see the new data been inserted.

That's my problem actually, I can't write to my database from within my program.

Upvotes: 1

Views: 244

Answers (1)

Muad'Dib
Muad'Dib

Reputation: 29216

you are trying to set the primary key in your stored procedure, i am guessing.

you should let SQL set it for you.

I am guessing @ID is your primary key... remove that code, and the code from your proc where you are inserting the value into the row.

you should make sure your column definition is set to auto-generate these for you.

Upvotes: 1

Related Questions