C# Code.. NEED URGENT HELP.

[rotten69]

Hi everyone;

I'm working on a c# service that queries the DB and checks if the right info is available for users. It's an Airline tickets reservation system.

I am not sure why my code is breaking after it gets the query and it gives an error about sqlclient..

This is the error message I get.....

 System.Data.SqlClient.SqlException: Incorrect syntax near '.'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) at WebService1.Service1.flightSearch(String depAirport, String arrAirport, String depDate, Int32 numOfSeats)

[WebMethod]
        public string flightSearch(string depAirport, string arrAirport, string depDate,  int numOfSeats)
        {
            
            SqlConnection conn = new SqlConnection(connString); 
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter();
            //int result = 0;
            
            try
            {
                conn.Open();
                string query = "SELECT departure_date, airport_location, seat_capacity,    origin_airport_code, destination_airport_code FROM Flight_Schedule, Airport, Travel_Class_Capacity";

                query += "WHERE airport_location.Airport= '" + arrAirport + "' AND airport_location.Airport='" + depAirport + "' AND departure_date.Flight_Schedule='" + depDate + "' AND ";

                query += "seat_capacity.Travel_Class_Capacity ='"+numOfSeats+"' AND seat_capacity.Travel_Class_Capacity > 0";

                SqlCommand cmd = new SqlCommand(query, conn);
                da.SelectCommand = new SqlCommand(query,conn);
                da.Fill(ds);
               

                if (ds.Tables[0].Rows.Count <=0)
                {
                    return true;
                }

                 conn.Close();
                
            }
            catch (Exception ex)
            {
                conn.Close();
            }
            return false;

the compiler gets up to da.Fill(ds) then jumps to the Catch Exception part. So, the idea is if this flightSearch method == true then I want to pass it to the next method(review) to display the info of the flights

[WebMethod]
        public string review() // pass something to see if flightSearch == true (But not sure how to do this, I can't pass a new instance of the flightSearch method, Can I? )
        {
            SqlConnection conn = new SqlConnection(connString);
            try
            {
                conn.Open();
                string query = "SELECT flight_number, airline_code, departure_date, arrival_date, departure_airport, arrival_airport,type, seat_capacity travel_class_code FROM Aircraft_Type, Travel_Class_Capacity, Flight_Schedule_Seat_Price";

                query += " WHERE travel_class_code='F' AND travel_class_code='B' AND travel_class_code='E' ";

            }
            catch (Exception) { conn.Close(); }

            return someInfo;

Your help is appreciated so much. Hope to hear from someone really soon. THANKS in advance.

[arunkumars]

" seat_capacity travel_class_code" is wrong, as far as ur previous query is concerned, use 'seat_capacity.travel_class_code' on line 9 of the 3rd part code snippet, this will work if the table is 'seat_capacity'

[rotten69]

Thanks, Buddy. But, My flightSearch method is not working. If it was, I'd pass it to the review method and I am not sure how to do this.

[rotten69]

And, I know I'm missing a comma on line 9 of the second method

[darkagn]

There is an error in your SQL query. I would suggest printing out your query string variable to see what is actually being inserted into your where clause with the values you are adding on the fly.

As an aside, a much safer approach to SQL is to use a parameterised query. Here is an example:

using (SqlConnection conn = new SqlConnection(connString))
{
  conn.Open();
  try
  {
    using (SqlCommand cmd = conn.CreateCommand())
    {
      cmd.CommandText = "SELECT * FROM Aircraft WHERE AircraftType = @Type";
      SqlParameter param = cmd.CreateParameter();
      param.ParameterName = "@Type";
      param.Value = aircraftType; // some value
      param.DataType = DbType.String; // some type
      using (SqlReader rdr = cmd.ExecuteReader())
      {
        while (rdr.Read())
        {
            // do something with the data
        }
      }
    }
  }
  catch (Exception e)
  {
     // do some error handling here
  }
  finally
  {
     conn.Close();
  }
}

[arunkumars]

seems like i got it..
the problem is with the query...
the syntax is as follows 'Select column[1], column[2] from table1 where condition'..
but, wat you are doing is the wrong.
'SELECT departure_date, airport_location, seat_capacity, origin_airport_code, destination_airport_code
FROM Flight_Schedule, Airport, Travel_Class_Capacity
WHERE airport_location.Airport= '" + arrAirport + "' AND airport_location.Airport='" + depAirport + "' AND
departure_date.Flight_Schedule='" + depDate + "' AND seat_capacity.Travel_Class_Capacity ='"+numOfSeats+"' AND
seat_capacity.Travel_Class_Capacity > 0

use this query,
"
SELECT departure_date, airport_location, seat_capacity, origin_airport_code, destination_airport_code
FROM Flight_Schedule, Airport, Travel_Class_Capacity
WHERE Airport.airport_location= '" + arrAirport + "' AND Airport.airport_location='" + depAirport + "' AND
Flight_Schedule.departure_date='" + depDate + "' AND Travel_Class_Capacity.seat_capacity ='"+numOfSeats+"' AND
Travel_Class_Capacity.seat_capacity > 0".

kindly mark as solved if u feel this is enough to get over the exception u are getting..

[rotten69]

I'm still getting the same error.. I think the query is right but what's after it isn't. Inside the WHERE clause; wouldn't this right to put (ColumnName.TableName and not the other way around)?

Thanks.

[Mitja Bonca]

The same error: Incorrect syntax near '.'??
Please double check the correctness of table names and fields.

[nakor77]

string query = "SELECT departure_date, airport_location, seat_capacity,    origin_airport_code, destination_airport_code FROM Flight_Schedule, Airport, Travel_Class_Capacity";
 
                query += "WHERE airport_location.Airport= '" + arrAirport + "' AND airport_location.Airport='" + depAirport + "' AND departure_date.Flight_Schedule='" + depDate + "' AND ";

There's no space between "Travel_Class_Capacity" and "Where"

[Mitja Bonca]

There must be a space. Space must be between every single word in a query, otherwise they are meant as one.

[nakor77]

yes, there must be a space, but the code you provided is missing one. The code I pasted was a snippet from the code you provided, if you look you'll notice it's missing a space before the WHERE. I wasn't telling you that there shouldn't be a space, I was telling you that you don't currently have a space between those two words

[arunkumars]

why dont you check with the syntax, as far I am using, the syntax says TableName.ColumnName.
did you try with the query that i gave you..?