Connection to SQL Server database

Question

A .NET application, from which a connection is being made and query executed as follows (wrapped in a try-catch block):

using (SqlConnection conn = new SqlConnection(Configuration.connectionString))
{
    SqlCommand cmd = new SqlCommand(createTransactionQuery,conn);
    conn.Open();
    return cmd.ExecuteNonQuery();
}

The query string is:

createTransactionQuery = "BEGIN " +
                         "BEGIN Transaction" +
                         "  BEGIN TRY " +
                         "      --variables" +
                         "      DECLARE @varStaffID int;" +
                         "      DECLARE @varProductID int;" +
                         "      SET @varStaffID = " + transaction.getStaff().getID() + ";" +
                         "      SET @varProductID = " + transaction.getProduct().getProductID() + ";" +
                         " " +
                         "      --New record in Transactions table " +
                         "      INSERT INTO Transactions (Timestamp_, CustomerID, StaffID, ProductID) " +
                         "      VALUES (SYSDATETIME(),NULL,@varStaffID,@varProductID; " +
                         " " +
                         "      --Update inventory (Products table)" +
                         "      --First retrieve the current quantity of this product" +
                         "      DECLARE @varCurrQuantity int; " +
                         "      SET @varCurrQuantity = (SELECT Quantity FROM Products WHERE ProductID=@varProductID); " +
                         "      --and update it" +
                         "      UPDATE Products " +
                         "      SET Quantity = @varQuantity-1 " +
                         "      WHERE ProductID = @varProductID; " +
                         "  END TRY " +
                         "  BEGIN CATCH " +
                         "      ROLLBACK Transaction " +
                         "  END CATCH " +
                         "COMMIT Transaction" +
                         "END";

This code throws an exception:

System.Exception: Incorrect syntax near 'BEGIN'.

I know that the query string could be created in a better way. However, I want to know what the cause of the problem is, as this exact query is working when it is executed within SQL Server Management Studio itself.

I have made sure the connection string is correct, as it is working exactly as it should in a different part of the application.

It seems you have a missing close parenthesis here `VALUES (SYSDATETIME(),NULL,@varStaffID,@varProductID; " ` — Salah Akbari, Jan 26 '20 at 04:59
*"I know that the query string could be created in a better way."* - start with that. The answers point out several mistakes. You would fix all of them if you 1) parameterized the query so you don't have to do any concatenation, and 2) switched to a verbatim string (`@"..."`). — madreflection, Jan 26 '20 at 05:30
Here isa general hint: do not show us how you cosntruct the string. Show us the RESULTING string. And debug that one. i.e. what you ahve in createTransactionQuery in the next line. This is - obviously - a SQL level error, so debugging should happen on the SQL string level. The rest - including the code to get the string - is just noise. And yo ucan copy/paste that into SSMS to get a better debug info on a line by line item. — TomTom, Jan 26 '20 at 11:18

Salah Akbari · Accepted Answer · 2020-01-26T05:21:43.263

2

It seems you have a missing close parenthesis here:

VALUES (SYSDATETIME(),NULL,@varStaffID,@varProductID; "

However this kind of string concatenation are open to SQL Injection attacks. You should always use parameterized queries to avoid SQL Injection and also to get rid of this kind of errors.

To learn how to use parameterized queries, see an example below:

https://stackoverflow.com/a/50597820/2946329

edited Jan 26 '20 at 05:21

answered Jan 26 '20 at 05:14

Salah Akbari

39,330
10
79
109

score 0 · Answer 2 · answered Jan 26 '20 at 04:59

you can try this for a transaction. remove first begin and last end from you code and follow this instruction:

BEGIN TRANSACTION trans
  BEGIN TRY  
     --Do some insert or update
     COMMIT TRANSACTION trans
  END TRY
BEGIN CATCH
  ROLLBACK TRANSACTION trans
END CATCH

score 0 · Answer 3 · edited Jan 26 '20 at 11:05

0

You have missed a space between "COMMIT Transaction" + "END"; use this "COMMIT Transaction" + " END";

Also you have not declared varQuantity and missed a closed bracket after " VALUES (SYSDATETIME(),NULL,@varStaffID,@varProductID; "

edited Jan 26 '20 at 11:05

Salah Akbari

39,330
10
79
109

answered Jan 26 '20 at 05:18

Naveen kumar

1
1
1

Naveen, your first answer addressed the cause of the syntax error in the question. The comment was causing everything after `BEGIN TRY` to be ignored. That was the *real* answer. – madreflection Jan 26 '20 at 05:24

Connection to SQL Server database

3 Answers3