none
Why do I get an exception here? RRS feed

  • Question

  • I have this piece of code:

    private string saveNewItemInOleDb ( string parentNumeric_id, char childsChar ) { string numeric_id = ""; string dataTableName = String.Empty; string textToInsert = String.Empty; DoServer.getSrvConnOrSwitch ( "ComeAndGetThr" ); using ( SqlConnection conn = new SqlConnection ( Globals.srv.ConnectionContext.ConnectionString ) ) { dataTableName = getAliasFromFirstChar ( childsChar ); StringBuilder sb = new StringBuilder ( "SELECT * FROM " + dataTableName + " WHERE numeric_id = (SELECT MAX (numeric_id) FROM " + dataTableName + ")" ); string comndText = sb.ToString ( ); DateTime dated = this.dateTimePickerDiaries.Value; string cDated = dated.ToString ( "MM/dd/yyyy hh:mm:ss" ); conn.Open ( ); SqlCommand cmdm = new SqlCommand ( ); cmdm.Connection = conn; cmdm.CommandType = CommandType.Text; cmdm.CommandText = comndText; StringBuilder sbTemp = new StringBuilder(); using ( SqlDataReader rdr = cmdm.ExecuteReader ( CommandBehavior.CloseConnection ) ) { if ( rdr.HasRows == true ) { foreach (System.Data.Common.DbDataRecord row in rdr) { if (parentNumeric_id != "Node0") { numeric_id = (string)row[0]; numeric_id = makeNumeric_id(numeric_id); } else { // this is the case when the new item (category) is entered in the very first column rdr.Close(); StringBuilder sb22 = new StringBuilder(); numeric_id = "0000000"; string memo101 = " "; DateTime dated101 = DateTime.Now; sb22.Append("INSERT INTO dbo.categories (numeric_id, named, dated, memo1 ) " + " VALUES ( @numeric_id, @named, @dated101, @memo101"); // <<=== EXCEPTION HERE SqlCommand cmdm101 = new SqlCommand(); cmdm101.Connection = conn; cmdm101.CommandType = CommandType.Text; cmdm101.CommandText = sb22.ToString(); if (conn.State == ConnectionState.Closed) { conn.Open(); } try { cmdm101.ExecuteNonQuery(); } catch (Exception ex) { TSP.TextToSpeech("could not save item because of exception"); Console.WriteLine(" {0} \r\n {1} ", ex.Message, ex.StackTrace); if (conn.State == ConnectionState.Open) { conn.Close(); } return String.Empty; } } } } if ( rdr.IsClosed == false ) { rdr.Close ( ); } }

    The Exception:

    Must declare the scalar variable "@numeric_id".
        at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
       at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
       at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
       at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
       at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
       at ComeAndGet.Form1.saveNewItemInOleDb(String parentNumeric_id, Char childsChar) in C:\VCSharp_Projects\ComeAndGet\ComeAndGet\Form1.cs:line 2026

    It is a part of a very large program which I completed around 8 years ago. Since then I haven't touched it for a few years. Whilst checking the code now I found that @numeric_id parameter is used in many sections of the program and I could not see any definitions anywhere. I wonder what is going on? I tried to define it as a string which is what it is and got compile errors as a result.

    Thanks.


    • Edited by MyCatAlex Friday, June 29, 2018 3:42 PM
    Friday, June 29, 2018 3:40 PM

Answers

  • That is a SQL syntax error. For these types of things it is generally best to drop into your SQL editor and paste in the code.

    In your particular case you are missing a closing paren after @memo1 in your VALUES clause.

    sb22.Append("INSERT INTO dbo.categories (…)
    VALUES ( @numeric_id, …, @memo1 ) ");


    Michael Taylor http://www.michaeltaylorp3.net

    • Marked as answer by MyCatAlex Friday, June 29, 2018 10:52 PM
    Friday, June 29, 2018 8:00 PM
    Moderator
  • In your INSERT into clause you're referencing several parameters. None of these parameters have been defined on the command and therefore SQL is throwing the exception. I don't see that this could have ever worked as SQL won't have had those values before either.

    To fix it you need to ensure the parameters are added to the command.

    cmdm101.Parameters.AddWithValue("@numeric_id", numeric_id);
    cmdm101.Parameters.AddWithValue("@named", "??");
    cmdm101.Parameters.AddWithValue("@dated101", dated101);
    cmdm101.Parameters.AddWithValue("@memo101", memo101); 
    My gut instinct is that the code you have now was being updated to use parameters. Originally it might have started as being completely inline with string concat and someone was trying to fix the SQL injection issues and never completed it.


    Michael Taylor http://www.michaeltaylorp3.net

    • Marked as answer by MyCatAlex Friday, June 29, 2018 10:53 PM
    Friday, June 29, 2018 4:15 PM
    Moderator
  • Michael correctly identified the error, but to help you self-diagnose these things in the future, I want to point out that the error message told you the last thing it was able to parse correctly:

    Incorrect syntax near '@memo1'

    From that, it should be pretty easy to find your error.


    Tim Roberts, Driver MVP Providenza & Boekelheide, Inc.

    • Marked as answer by MyCatAlex Friday, June 29, 2018 11:22 PM
    Friday, June 29, 2018 8:51 PM

All replies

  • In your INSERT into clause you're referencing several parameters. None of these parameters have been defined on the command and therefore SQL is throwing the exception. I don't see that this could have ever worked as SQL won't have had those values before either.

    To fix it you need to ensure the parameters are added to the command.

    cmdm101.Parameters.AddWithValue("@numeric_id", numeric_id);
    cmdm101.Parameters.AddWithValue("@named", "??");
    cmdm101.Parameters.AddWithValue("@dated101", dated101);
    cmdm101.Parameters.AddWithValue("@memo101", memo101); 
    My gut instinct is that the code you have now was being updated to use parameters. Originally it might have started as being completely inline with string concat and someone was trying to fix the SQL injection issues and never completed it.


    Michael Taylor http://www.michaeltaylorp3.net

    • Marked as answer by MyCatAlex Friday, June 29, 2018 10:53 PM
    Friday, June 29, 2018 4:15 PM
    Moderator
  • Michael, thank you. It is entirely my code overall written and debugged years ago, although now I am trying to enhance it by adding functions. Certainly what you suggested is a way to go but I still get exceptions. I made some changes to the code:

    else
                                {  // this is the case when the new item (category) is entered in the very first column                                
                                    rdr.Close();
                                    StringBuilder sb22 = new StringBuilder();
                                    numeric_id = "0000000";
                                    string memo1 = String.Empty;
                                    string named = String.Empty;
                                    DateTime dated1 = DateTime.Now;
                                    sb22.Append("INSERT INTO dbo.categories (numeric_id, named, dated, memo1 ) " +
                                        " VALUES ( @numeric_id, @named, @dated1,  @memo1 ") ;
                                    SqlCommand cmdm101 = new SqlCommand();
                                    cmdm101.Parameters.AddWithValue("@numeric_id", numeric_id);
                                    cmdm101.Parameters.AddWithValue("@named", named);
                                    cmdm101.Parameters.AddWithValue("@dated1", dated);
                                    cmdm101.Parameters.AddWithValue("@memo1", memo1);
                                    cmdm101.Connection = conn;
                                    cmdm101.CommandType = CommandType.Text;
                                    cmdm101.CommandText = sb22.ToString();
                                    if (conn.State == ConnectionState.Closed)
                                    {
                                        conn.Open();
                                    }
                                    try
                                    {
                                        cmdm101.ExecuteNonQuery();
                                    }

    The RUNTIME exception I get is

    Incorrect syntax near '@memo1'.
        at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
       at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
       at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
       at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption)
       at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
       at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
       at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
       at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
       at ComeAndGet.Form1.saveNewItemInOleDb(String parentNumeric_id, Char childsChar) in C:\VCSharp_Projects\ComeAndGet\ComeAndGet\Form1.cs:line 2034
    
    Thank you.

    • Marked as answer by MyCatAlex Friday, June 29, 2018 10:52 PM
    • Unmarked as answer by MyCatAlex Friday, June 29, 2018 10:52 PM
    Friday, June 29, 2018 7:55 PM
  • That is a SQL syntax error. For these types of things it is generally best to drop into your SQL editor and paste in the code.

    In your particular case you are missing a closing paren after @memo1 in your VALUES clause.

    sb22.Append("INSERT INTO dbo.categories (…)
    VALUES ( @numeric_id, …, @memo1 ) ");


    Michael Taylor http://www.michaeltaylorp3.net

    • Marked as answer by MyCatAlex Friday, June 29, 2018 10:52 PM
    Friday, June 29, 2018 8:00 PM
    Moderator
  • Michael correctly identified the error, but to help you self-diagnose these things in the future, I want to point out that the error message told you the last thing it was able to parse correctly:

    Incorrect syntax near '@memo1'

    From that, it should be pretty easy to find your error.


    Tim Roberts, Driver MVP Providenza & Boekelheide, Inc.

    • Marked as answer by MyCatAlex Friday, June 29, 2018 11:22 PM
    Friday, June 29, 2018 8:51 PM
  • Michael hi,

    You are a miracle worker. It now works, almost. Another complication arose, very much down the road. The code now is trying to insert this record in SQL Server. This is what happens. It is a Treeview or rather a collection thereof. The top elements of the treeView's are exposed in an RTF window, they form a column on the left. They all  suppose to have "numeric_id" = "0000000" The other child elements are attached to them and this part has always worked. I had difficulties adding new elements to the first column where all elements must have this numeric_id = "0000000" although I am not sure now, perhaps the rule must be different. I now need to find out how I solved this problem in the past.

    This particular statement just failed because numeric_id is a primary key and there is also one at least numeric_id with this value in SQL Server.

    You helped me to solve the problem I posted. Thank you. That part is now works. I have to do a little more research about cheating the primary key.

    Thank you, - Alex

    Friday, June 29, 2018 11:06 PM
  • OK, everything, absolutely everything works well now. I have a feeling that I've interacted with Mike Taylor years ago. I think he once explained to me "Run as Administrator" when MS first introduced the concept. I think it was either XP or Vista at that time.

    Anyhow, I appreciate your help for sure. Many thanks, - Alex

    Saturday, June 30, 2018 12:21 AM