1

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Andreas Oxinos
  • 11
  • 1
  • What is your connection string? Seems like you are in a transaction that hasn't been finalized. – btlog Feb 24 '12 at 01:21
  • Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\dPasswords.mdf;Integrated Security=True;User Instance=True" – Andreas Oxinos Feb 24 '12 at 01:51
  • Please don't prefix your titles with "C#, " and such. That's what the tags are for. – John Saunders Feb 24 '12 at 02:01
  • Can you **attach** your database file (.mdf) to a SQL Server instance, using SQL Server Mgmt Studio (Express) ?? Give it a logical name, and then use the connection string **to the attached database** instead of fiddling around with the database file. This whole `AttachDbFilename=` stuff is broken and a mess - don't use it! It just causes grief (and tons of messages here on SO.....) – marc_s Feb 24 '12 at 05:46
  • I'm pretty sure your stored procedure is executed just fine - but you're looking at the wrong .MDF file for the results and thus you don't see them...... – marc_s Feb 24 '12 at 05:46
  • See my response [to this other SO question about the exact same topic](http://stackoverflow.com/questions/9382756/data-is-not-inserting-into-table/9382877#9382877) from just a few days ago..... – marc_s Feb 24 '12 at 05:48

1 Answers1

1

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.

Muad'Dib
  • 28,542
  • 5
  • 55
  • 68
  • Indeed @ID is my primary key, i fixed this problem now, but the main problem as i decribed above is that i cant add any data in my database from my application. – Andreas Oxinos Feb 24 '12 at 01:12
  • When i run the StoreProcedure from the server explorer it correctly add datam but from my program no. – Andreas Oxinos Feb 24 '12 at 01:13
  • Just refresh the table and check whether the data is inserted. I'm telling this because even I had breaked my head initially. Worrying that the data is not inserted even when it actually had. Fix the primary key problem as Muad'Dib suggested. – Manjunath K Mayya Feb 24 '12 at 05:16