Error in begin transaction

Question

I tried to do begin transaction on SQL Server, but it returns an error that I can't figure out what the real problem is. So here is some of my code I tried.

This is the error:

Code:

SqlConnection connection = new SqlConnection("Data Source=LOCALHOST\\SQLEXPRESS;Initial Catalog=tempdb;Integrated Security=SSPI;User ID = xxxx; Password=xxx;");

DateTime dt = dateTimePicker1.Value.Date;
dt = dt.AddDays(60); 

string selectQuery = "BEGIN TRANSACTION UPDATE tester SET 
test_ad=@dateTimePicker1, test_ud=@dt, test_pd=@dt WHERE 
test_name=@textBox1;INSERT INTO records(testr_status, testr_name, testr_ad, 
testr_ud, testr_pd, apte_name)VALUES(@testr_status, testr_name = @comboBox1, 
testr_ad = @dateTimePicker1, testr_ud = @dt, testr_pd = @dt COMMIT";

connection.Open();

SqlCommand command = new SqlCommand(selectQuery, connection);
command.Parameters.AddWithValue("@dateTimePicker1",this.dateTimePicker1.Value.Date);
command.Parameters.AddWithValue("@textBox1", this.textBox1.Text);
command.Parameters.AddWithValue("@comboBox1",this.comboBox1.SelectedItem);
command.Parameters.AddWithValue("@testr_status",SqlDbType.VarChar);
command.Parameters.AddWithValue("@dt", dt);

int iResult = command.ExecuteNonQuery();

if (iResult > 0)
    MessageBox.Show("Successfully saved ", "Error",MessageBoxButtons.OK, MessageBoxIcon.Information);
else
    MessageBox.Show("Record not saved ", "Error",MessageBoxButtons.OK, MessageBoxIcon.Error);

command.ExecuteNonQuery();
connection.Dispose();
command.Dispose();

`testr_name = @comboBox1` makes sense in an `UPDATE`. It **does not** makes sense in an `INSERT`. Remove `testr_name =` etc. — mjwills, Jun 27 '18 at 03:31
For the transaction requirement, see https://stackoverflow.com/questions/19165291/how-to-use-sqltransaction-in-c-sharp . — mjwills, Jun 27 '18 at 03:33
This isn't an answer to your question, but I think you should read this https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/ — DeanOC, Jun 27 '18 at 03:45

score 1 · Accepted Answer · answered Jun 27 '18 at 04:31

Try cleaning up a bit your query or paste it on SSMS and declare your parameters and you will figure out what is wrong.

In your case your INSERT statement has some errors.

This is not valid syntax VALUES (test_name = @combobox1) instead you only pass the parameter VALUES (@combobox1)
There are more columns in the INSERT statement than values specified in the VALUES clause, you are not providing a value for apte_name. In the c# code you will need to add that parameter too.
You are missing the closing parenthesis for the VALUES clause

You should end up with something like this (not tested)

string selectQuery = 
@"BEGIN TRANSACTION 
            UPDATE tester SET 
                test_ad = @dateTimePicker1, 
                test_ud = @dt, 
                test_pd = @dt 
            WHERE test_name = @textBox1; 

            INSERT INTO records
            (
                testr_status, 
                testr_name,
                testr_ad,
                testr_ud,  
                testr_pd, 
                apte_name
            )
            VALUES
            (
                @testr_status, 
                @comboBox1,
                @dateTimePicker1,
                @dt, 
                @dt,
                @apte_name
            );

 COMMIT";

thank you. it is working! but when i checked it in ssms, it insert two records (duplicate records) . may i know why? — aci95, Jun 27 '18 at 05:59

glenebob · Answer 2 · 2018-06-27T05:30:41.133

1

The actual problem is, that is one big, invalid SQL statement. Use the semi-colon to separate statements, like so:

"BEGIN TRANSACTION;
INSERT ...;
UPDATE ...;
ETC ...;
COMMIT;"

That said, don't embed transaction statements in a query string. Do what Oliver suggests in another answer.

edited Jun 27 '18 at 05:30

answered Jun 27 '18 at 04:43

glenebob

1,943
11
11

score 0 · Answer 3 · answered Jun 27 '18 at 03:22

0

You can use SqlTransaction

using (SqlConnection conn = new SqlConnection("Connection String"))
{
    conn.Open();
    SqlTransaction trans;
    trans = conn.BeginTransaction();
    string selectQuery = "your sql query";
    SqlCommand command = new SqlCommand(selectQuery, connection);
    int iResult = command.ExecuteNonQuery();
    if (iResult > 0)
    {
        trans.Commit();
    }else{
        trans.Rollback();
    }
    conn.Close();
}

answered Jun 27 '18 at 03:22

Oliver

43
1
7

You might also wanna rollback in case of an exception being thrown. – Ray Krungkaew Jun 27 '18 at 03:25
Rollback is automatic. Upon exception, the using block will exit, closing the connection, implicitly rolling back any open transaction. – glenebob Jun 27 '18 at 04:39

Saurabh Dash · Answer 4 · 2018-06-27T06:03:02.610

-1

Use a formatted string for your select query by using @ and the syntax in the value block in not accurate.

        string selectQuery = @"
BEGIN TRANSACTION
        UPDATE tester SET test_ad = @dateTimePicker1, test_ud = @dt, test_pd = @dt WHERE test_name = @textBox1;
        INSERT INTO records(testr_status, testr_name, testr_ad, testr_ud, testr_pd, apte_name) VALUES(@testr_status, @comboBox1, @dateTimePicker1, @dt, @dt); 
       COMMIT";

edited Jun 27 '18 at 06:03

answered Jun 27 '18 at 04:24

Saurabh Dash

1
3

The `INSERT` statement is invalid. – mjwills Jun 27 '18 at 04:50

Error in begin transaction

4 Answers4