locked
Incorrect syntax near 00 in sql server asp.net RRS feed

Answers

  • User281315223 posted

    This could be an issue of how you are building your query, as you should generally never just concatenate values from your text boxes into your queries (this can leave you vulnerable to SQL Injection attacks). Specifically the following section which could cause your input to run into your keywords in your query :

    Table.Number >= " + TextBoxNumber.Text + "AND TableReservation.Time= @Time"

    Notice the "AND" doesn't have a space before it, which could cause something like a Table Number of 100 to yield "100AND" which could explain your error.

    You might try the following refactored code below which uses a bit of parameteriziation to help avoid issues like these :

    using(var connection = new SqlConnection("Your Connection String"))
    {
         // Build your query
         var query = "SELECT TableReservation.TableNumber, TableReservation.Time, TableReservation.Duration FROM TableReservation INNER JOIN Table ON TableReservation.TableNumber=Table.TableNumber WHERE Date = @Date AND Table.Number >= @TableNumber AND TableReservation.Time = @Time";
    
         // Build a command to execute your query
         using(var cmd = new SqlCommand(query, connection))
         {
               // Parse your value (safely using TryParse)
               var tableNumber = 0;
               Int32.TryParse(TextBoxNumber.Text, tableNumber)
    
               // Get the available tables
               var matchedTables = tables.Select(a => a >= tbNumber ? a : 0).ToArray();
               
               // Open your connection
               connection.Open();
               
               // Add your parameters
               cmd.Parameters.AddWithValue("@Date",Calendar.SelectedDate.Date);
               cmd.Parameters.AddWithValue("@TableNumber",tableNumber);
               cmd.Parameters.AddWithValue("@Time",?);  // I'm not sure what you should be passing in here? Do you have some way of specifying the time?
    
               // Use a list to avoid having to manually track indices
               var takenTime = List<string>(); 
    
               var reservations = cmd.ExecuteReader();
               while (reservations.Read())
               {
                    // Add the time for this row
                    TakenTime.Add(reservations.GetString(0));
               }
    
               // Do you need to do something with takenTime here?
               var takenTimeArray = takenTime.ToArray();
         }
    }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, May 16, 2015 9:03 AM
  • User3690988 posted

    You may need single quotes around your date, and I would add a space before the AND in "AND TableReservation.Time= @Time".

    string sqlComand = "SELECT TableReservation.TableNumber, TableReservation.Time, TableReservation.Duration FROM TableReservation INNER JOIN Table ON TableReservation.TableNumber=Table.TableNumber WHERE Date = '" + Calendar.SelectedDate.Date.ToString() + "' AND Table.Number >= " + TextBoxNumber.Text + " AND TableReservation.Time= @Time";
    
    SqlCommand cmd = new SqlCommand("SELECT TableReservation.TableNumber, TableReservation.Time, TableReservation.Duration FROM TableReservation INNER JOIN Table ON TableReservation.TableNumber=Table.TableNumber WHERE Date = '" + Calendar.SelectedDate.Date.ToString() + "' AND Table.Number >= " + TextBoxNumber.Text + " AND TableReservation.Time= @Time", ConnectionString);

    If that does not fix the error, I would create a string that produces your SQL (string sqlComand from above), then:

    • Run in Debug
    • Get the value of the sqlComand string
    • Run the sqlComand string in SQL Server Management Studio to see what error may be produced.
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, May 16, 2015 9:09 AM

All replies

  • User281315223 posted

    This could be an issue of how you are building your query, as you should generally never just concatenate values from your text boxes into your queries (this can leave you vulnerable to SQL Injection attacks). Specifically the following section which could cause your input to run into your keywords in your query :

    Table.Number >= " + TextBoxNumber.Text + "AND TableReservation.Time= @Time"

    Notice the "AND" doesn't have a space before it, which could cause something like a Table Number of 100 to yield "100AND" which could explain your error.

    You might try the following refactored code below which uses a bit of parameteriziation to help avoid issues like these :

    using(var connection = new SqlConnection("Your Connection String"))
    {
         // Build your query
         var query = "SELECT TableReservation.TableNumber, TableReservation.Time, TableReservation.Duration FROM TableReservation INNER JOIN Table ON TableReservation.TableNumber=Table.TableNumber WHERE Date = @Date AND Table.Number >= @TableNumber AND TableReservation.Time = @Time";
    
         // Build a command to execute your query
         using(var cmd = new SqlCommand(query, connection))
         {
               // Parse your value (safely using TryParse)
               var tableNumber = 0;
               Int32.TryParse(TextBoxNumber.Text, tableNumber)
    
               // Get the available tables
               var matchedTables = tables.Select(a => a >= tbNumber ? a : 0).ToArray();
               
               // Open your connection
               connection.Open();
               
               // Add your parameters
               cmd.Parameters.AddWithValue("@Date",Calendar.SelectedDate.Date);
               cmd.Parameters.AddWithValue("@TableNumber",tableNumber);
               cmd.Parameters.AddWithValue("@Time",?);  // I'm not sure what you should be passing in here? Do you have some way of specifying the time?
    
               // Use a list to avoid having to manually track indices
               var takenTime = List<string>(); 
    
               var reservations = cmd.ExecuteReader();
               while (reservations.Read())
               {
                    // Add the time for this row
                    TakenTime.Add(reservations.GetString(0));
               }
    
               // Do you need to do something with takenTime here?
               var takenTimeArray = takenTime.ToArray();
         }
    }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, May 16, 2015 9:03 AM
  • User3690988 posted

    You may need single quotes around your date, and I would add a space before the AND in "AND TableReservation.Time= @Time".

    string sqlComand = "SELECT TableReservation.TableNumber, TableReservation.Time, TableReservation.Duration FROM TableReservation INNER JOIN Table ON TableReservation.TableNumber=Table.TableNumber WHERE Date = '" + Calendar.SelectedDate.Date.ToString() + "' AND Table.Number >= " + TextBoxNumber.Text + " AND TableReservation.Time= @Time";
    
    SqlCommand cmd = new SqlCommand("SELECT TableReservation.TableNumber, TableReservation.Time, TableReservation.Duration FROM TableReservation INNER JOIN Table ON TableReservation.TableNumber=Table.TableNumber WHERE Date = '" + Calendar.SelectedDate.Date.ToString() + "' AND Table.Number >= " + TextBoxNumber.Text + " AND TableReservation.Time= @Time", ConnectionString);

    If that does not fix the error, I would create a string that produces your SQL (string sqlComand from above), then:

    • Run in Debug
    • Get the value of the sqlComand string
    • Run the sqlComand string in SQL Server Management Studio to see what error may be produced.
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Saturday, May 16, 2015 9:09 AM
  • User-931110487 posted

    Thank you so much for the quick answer. I've now got it to work with some minor changes in your code example. have a nice day :-)

    Saturday, May 16, 2015 10:18 AM