CODEWITHSUNDEEP
c#.netsql
JRB
JRB

Reputation: 247

Where have I gone wrong with this c# simple sql insert

Final Note: After adding the executeNonQuery AND after I changed the connection string from Filename=|DataDirectory|\testconn.mdf to Filename=c:\Documents........\testconn.mdf. My data began to insert into my table. Thanks all for the help.

While working with this sql insert I find that the code runs without any exceptions but after going to database explorer and looking at the show table data, the data was not inserted. Originally I wasn’t using the transaction code but read on this site this may be the problem it also runs without an exception but still does not actually insert into the table. While stepping through I can see where the status changes from open to close following the conn.Open() and connClose() statements. In addition for best practice is there a cleaner/better way to write my SqlConnection string as well as my SqlCommand strings? Thank you My code:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Transactions;

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {

        public Form1()
        {

            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {

        }

        private void button1_Click(object sender, EventArgs e)
        {
            CommittableTransaction MASTER_TRANSACTION = new CommittableTransaction();
            // 1. Instantiate the connection
            SqlConnection conn = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\testconn.mdf;Integrated Security=True;User Instance=True");


                  try
            {
                // 2. Open the connection
                conn.Open();
                conn.EnlistTransaction(MASTER_TRANSACTION);

                // 3. Pass the connection to a command object
               SqlCommand cmd = new SqlCommand("INSERT INTO Client_Master(Client_ID, Client_First, Client_Last) VALUES('2', 'Joe', 'Shmoe')", conn);
                MASTER_TRANSACTION.Commit();

            }
            finally
            {

             conn.Close();

            }
        }

    }
}

Upvotes: 0

Views: 336

Answers (10)

AEMLoviji
AEMLoviji

Reputation: 3257

your cmd is:

INSERT INTO Client_Master(Client_ID, Client_First, Client_Last) 
                   VALUES('2', 'Joe', 'Shmoe').

and i think that problem is here. with Client_ID column. You have set this column as primary key. but you are going to insert '2' in every time. for this reason it gives you 

   System.Data.SqlClient.SqlException was caught Message=Violation of PRIMARY KEY
 constraint 'PK_Client_Master'. Cannot insert duplicate key in object dbo.Client_Master.

you can make Client_ID column as autoincrement

Upvotes: 1

Chris
Chris

Reputation: 1579

Try adding a "catch" block to the "try" and "finally" so you can see if there is an error occurring.

Currently, you are missing the actually execution of the statement. Add the following to fix that:

cmd.ExecuteNonQuery();

In addition, why are you using a Transaction? Transactions are useful when executing multiple statements to ensure an "all or none" execution. You are just inserting one row.

Upvotes: 1

psousa
psousa

Reputation: 6726

You're creating the command but not doing anything with it. You're missing a:

cmd.ExecuteNonQuery();

Also, you should think of encapsulating the connection with a using statement, as it disposes it automatically, like:

using(SqlConnection conn = new SqlConnection(connectionString))
{


}

Upvotes: 1

Richard Schneider
Richard Schneider

Reputation: 35477

You need to execute the SQL command. Try

cmd.ExecuteNonQuery()

Upvotes: 1

Greg Randall
Greg Randall

Reputation: 921

You have to execute your command. i.e.

 // 3. Pass the connection to a command object
           SqlCommand cmd = new SqlCommand("INSERT INTO Client_Master(Client_ID, Client_First, Client_Last) VALUES('2', 'Joe', 'Shmoe')", conn);
           cmd.ExecuteNonQuery();
            MASTER_TRANSACTION.Commit();

Check here for more info. http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery.aspx

Upvotes: 1

JBrooks
JBrooks

Reputation: 10013

Yes, execute the cmd and then right before the line with "finally" add:

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

and it will show other exceptions if any.

And you don't really need the overhead of a transaction for a single insert.... and if you did you should have a rollback in the Exception block.

Upvotes: 1

harryovers
harryovers

Reputation: 3138

Try cmd.ExecuteNonQuery() to execute the SQL command

MSDN Link

Upvotes: 1

Femaref
Femaref

Reputation: 61447

you need to execute the command via cmd.ExecuteNonQuery() as well.

Upvotes: 1

Brian Lyttle
Brian Lyttle

Reputation: 14579

I think you need to call an execute method on the the command object.

Upvotes: 2

Bala R
Bala R

Reputation: 108957

try calling

cmd.ExecuteNonQuery();

before MASTER_TRANSACTION.Commit();

Upvotes: 3

Related Questions