C# and SQL with primary key

Problems adding records from C# to SQL with a primary key

Page 1 of 1

7 Replies - 5633 Views - Last Post: 21 November 2009 - 09:11 AM Rate Topic: -----

#1 erosenko  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 20
  • Joined: 10-May 09

C# and SQL with primary key

Posted 10 November 2009 - 08:14 PM

Hi all...

Have another issue related to an earlier post.

I know this code works on an old SQL database without a primary key. I re-did my SQL d-base and added a primary key (Column Name - id) and changed my code to match the new d-base instance. I created the id column as data type int and set it to Identity Specification = Yes, and (Is Identity) = Yes to create an auto-numbering identity sequence, and now I am not getting my records into the database anymore (not getting any errors either???). The only difference is the primary key, so I know that is the issue. I am not accounting for the primary key in my code, but shouldn't the primary key with the Identity Specification set to Yes, auto populate when the rest of the data is inserted into the d-base record?

What gives?

Thanks in advance.


My code on the click event looks like this:

private void button1_Click(object sender, EventArgs e)
		{

			//add record from text boxes to database

			try
			{

				System.Data.SqlClient.SqlConnection con;

				con = new System.Data.SqlClient.SqlConnection();
				con.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\decontact.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";

				SqlCommand insert = new SqlCommand();
				insert.CommandText = "INSERT INTO decontact (fname, lname, address, city, state, zip, phone1, phone2, email, notes)VALUES ('" + txtFname.Text + "', '" + txtLname.Text + "', '" + txtAddress.Text + "', '" + txtCity.Text + "', '" + txtState.Text + "', '" + txtZip.Text + "', '" + txtPhone1.Text + "', '" + txtPhone2.Text + "','" + txtEmail.Text + "','" + txtNotes.Text + "')";

				con.Open();
				insert.Connection = con;
				insert.ExecuteNonQuery();
				con.Close();

				MessageBox.Show("Record Added");



			}

			catch (System.Exception ex)
			{
				System.Windows.Forms.MessageBox.Show(ex.Message);
			}



		}



Is This A Good Question/Topic? 0
  • +

Replies To: C# and SQL with primary key

#2 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1524
  • View blog
  • Posts: 5,957
  • Joined: 21-March 08

Re: C# and SQL with primary key

Posted 11 November 2009 - 06:25 AM

if your database is a SQL Express database, you should not be "attaching" it.

You "attach" at SQL CE database.
You connect to the instance of SQL Express.

Did you install SQL Express? or did you use the "Local Database" Item in Visual Studio?

Also, in your sql statement, your "Values" keyword has no space before it. So in sql, it looks like ")Values" which will probably cause an issue.
Was This Post Helpful? 0
  • +
  • -

#3 erosenko  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 20
  • Joined: 10-May 09

Re: C# and SQL with primary key

Posted 11 November 2009 - 02:51 PM

Sorry. I am connecting to it, not attaching. It is SQL Express, 2008, and it is installed. I used the option in C# to copy the database to the output for my project. I setup the table and my columns in SQL.

I will try the space in values and see if that works.

Thank you.
Was This Post Helpful? 0
  • +
  • -

#4 Momerath  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 995
  • View blog
  • Posts: 2,386
  • Joined: 04-October 09

Re: C# and SQL with primary key

Posted 11 November 2009 - 05:59 PM

insert.ExecuteNonQuery();

What value are you getting back from this?
Was This Post Helpful? 0
  • +
  • -

#5 erosenko  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 20
  • Joined: 10-May 09

Re: C# and SQL with primary key

Posted 12 November 2009 - 07:58 PM

View PostMomerath, on 11 Nov, 2009 - 04:59 PM, said:

insert.ExecuteNonQuery();

What value are you getting back from this?



I am hate to sound like a total noob, but I think this is what you are asking... I put a text writer line in my code and this is what I get from the insert.CommandText

INSERT INTO decontact (fname, lname, address, city, state, zip, phone1, phone2, email, notes)VALUES ('John', 'Smith', '1234 Main St', 'Jefferson City', 'MO', '65109', '573-123-4567', '573-765-4321','me@me.com','sample note 1')

If I write out the insert.ExecuteNonQuery in the same text writer, all I get is: 1
Was This Post Helpful? 0
  • +
  • -

#6 Momerath  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 995
  • View blog
  • Posts: 2,386
  • Joined: 04-October 09

Re: C# and SQL with primary key

Posted 12 November 2009 - 08:26 PM

View Posterosenko, on 12 Nov, 2009 - 06:58 PM, said:

If I write out the insert.ExecuteNonQuery in the same text writer, all I get is: 1

That's what I was looking for :) It means that the non-query worked (-1 means it failed, and would make life easier if it had).

Now to think more about this issue. Oh, are you running in debug mode from inside visual studio?
Was This Post Helpful? 0
  • +
  • -

#7 erosenko  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 20
  • Joined: 10-May 09

Re: C# and SQL with primary key

Posted 14 November 2009 - 10:30 AM

View PostMomerath, on 12 Nov, 2009 - 07:26 PM, said:

View Posterosenko, on 12 Nov, 2009 - 06:58 PM, said:

If I write out the insert.ExecuteNonQuery in the same text writer, all I get is: 1

That's what I was looking for :) It means that the non-query worked (-1 means it failed, and would make life easier if it had).

Now to think more about this issue. Oh, are you running in debug mode from inside visual studio?



Yes, I am running in debug mode and it does hit my code to display the message that the record was added. There are no errors in the SQL log and no errors reported from C#. I think my problem is that I am not accounting for the primary key field, but I think that should auto-populate from the way I have SQL setup. I have the id column set as Data Type int, do not allow Nulls, and I have Identity Specification and (Is Identity) both set to yes, with an Identity Increment of 1 and Identity Seed of 1.

Really lost on this one. As I stated earlier, the exact same code works fine on Dbase Table setup without the primary key.



View Posterosenko, on 14 Nov, 2009 - 09:26 AM, said:

View PostMomerath, on 12 Nov, 2009 - 07:26 PM, said:

View Posterosenko, on 12 Nov, 2009 - 06:58 PM, said:

If I write out the insert.ExecuteNonQuery in the same text writer, all I get is: 1

That's what I was looking for :) It means that the non-query worked (-1 means it failed, and would make life easier if it had).

Now to think more about this issue. Oh, are you running in debug mode from inside visual studio?



Yes, I am running in debug mode and it does hit my code to display the message that the record was added. There are no errors in the SQL log and no errors reported from C#. I think my problem is that I am not accounting for the primary key field, but I think that should auto-populate from the way I have SQL setup. I have the id column set as Data Type int, do not allow Nulls, and I have Identity Specification and (Is Identity) both set to yes, with an Identity Increment of 1 and Identity Seed of 1.

Really lost on this one. As I stated earlier, the exact same code works fine on Dbase Table setup without the primary key.




And to make this even more interesting, I just manually added a record to the SQL table, without typing in anything for the ID (primary key) and it works just fine. SQL populated the first record automatically with a 1 for the id number.
Was This Post Helpful? 0
  • +
  • -

#8 erosenko  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 20
  • Joined: 10-May 09

Re: C# and SQL with primary key

Posted 21 November 2009 - 09:11 AM

View Posterosenko, on 14 Nov, 2009 - 09:30 AM, said:

View PostMomerath, on 12 Nov, 2009 - 07:26 PM, said:

View Posterosenko, on 12 Nov, 2009 - 06:58 PM, said:

If I write out the insert.ExecuteNonQuery in the same text writer, all I get is: 1

That's what I was looking for :) It means that the non-query worked (-1 means it failed, and would make life easier if it had).

Now to think more about this issue. Oh, are you running in debug mode from inside visual studio?



Yes, I am running in debug mode and it does hit my code to display the message that the record was added. There are no errors in the SQL log and no errors reported from C#. I think my problem is that I am not accounting for the primary key field, but I think that should auto-populate from the way I have SQL setup. I have the id column set as Data Type int, do not allow Nulls, and I have Identity Specification and (Is Identity) both set to yes, with an Identity Increment of 1 and Identity Seed of 1.

Really lost on this one. As I stated earlier, the exact same code works fine on Dbase Table setup without the primary key.



View Posterosenko, on 14 Nov, 2009 - 09:26 AM, said:

View PostMomerath, on 12 Nov, 2009 - 07:26 PM, said:

View Posterosenko, on 12 Nov, 2009 - 06:58 PM, said:

If I write out the insert.ExecuteNonQuery in the same text writer, all I get is: 1

That's what I was looking for :) It means that the non-query worked (-1 means it failed, and would make life easier if it had).

Now to think more about this issue. Oh, are you running in debug mode from inside visual studio?



Yes, I am running in debug mode and it does hit my code to display the message that the record was added. There are no errors in the SQL log and no errors reported from C#. I think my problem is that I am not accounting for the primary key field, but I think that should auto-populate from the way I have SQL setup. I have the id column set as Data Type int, do not allow Nulls, and I have Identity Specification and (Is Identity) both set to yes, with an Identity Increment of 1 and Identity Seed of 1.

Really lost on this one. As I stated earlier, the exact same code works fine on Dbase Table setup without the primary key.




And to make this even more interesting, I just manually added a record to the SQL table, without typing in anything for the ID (primary key) and it works just fine. SQL populated the first record automatically with a 1 for the id number.






------------------------------
Well - Still not getting this to work. I have another "real" project I need to start on, but if anyone has any ideas on my issue here, please let me know. Thanks!
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1