Mysql query datagridview c#

Question

The query does not return value when I call it from ASP C# however when I connect the MySQL server and type the same query it returns right values. I couldn't discover the problem.

Here is the code segment:

try
{
    personquery = "select b.* from booking b, makes m  "+
                  "where m.personid="+ 
                  DataDeneme1.login.personid.ToString() +
                  "and m.bookingno=b.bookingno";
    con = new MySqlConnection(System.Configuration.ConfigurationManager.AppSettings.Get("connectionString"));
    cmd.CommandText = personquery;
    con.Open();
    cmd.Connection = con;
    adap = new MySqlDataAdapter(personquery, con);
    adap.Fill(ds);
    // CheckBoxList1.DataSource = ds;
    // CheckBoxList1.DataBind();
    GridView1.DataSource = ds;
    GridView1.DataBind();
    con.Close();
}
catch (Exception ex)
{
    Response.Write(ex.Message);
    Response.Write(ex.StackTrace);
}

The input and the output from mysql server:

mysql> select b.* from booking b, makes m  where m.personid=1 and m.bookingno=b.bookingno;
+-----------+-----------------+--------------+-------------+------------+-------------+  
| bookingno | reservationdate | dropoffplace | pickupplace | pickupdate | dropoffdate |  
+-----------+-----------------+--------------+-------------+------------+-------------+  
|         8 | 2011-05-09      | Ankara       | Ankara      | 2011-05-10 | 2011-05-15  |   
|         9 | 2011-05-09      | Ankara       | Ankara      | 2011-05-20 | 2011-05-25  |   
+-----------+-----------------+--------------+-------------+------------+-------------+  
2 rows in set (0.00 sec)

and Exception message....

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'm.bookingno=b.bookingno' at line 1 at MySql.Data.MySqlClient.MySqlStream.ReadPacket() at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int32& insertedId) at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int32& insertedId) at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId) at MySql.Data.MySqlClient.MySqlDataReader.NextResult() at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior) at MySql.Data.MySqlClient.MySqlCommand.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 DataDeneme1.customerview.loadList() in E:\VisualStudioProjects\DataDeneme1\DataDeneme1\customerview.aspx.cs:line 38

he is getting a SQL error, so the dataset fill is not going to even happen. — Dustin Laine

Dustin Laine Dustin Laine · Accepted Answer · 2011-05-09T22:56:15

I would bet it has to do with the dynamic value DataDeneme1.login.personid.ToString(), since the exception states the next piece of text. Ensure your value is what you are expecting, a blank/whitespace value would cause this error.

UPDATE
Based on you comment I saw what I believe to be the issue. If the value is 1 then the result of this:

personquery = "select b.* from booking b, makes m  "+
              "where m.personid="+ 
              DataDeneme1.login.personid.ToString() +
              "and m.bookingno=b.bookingno";

Would be:

select b.* from booking b, makes m
where m.personid=1and m.bookingno=b.bookingno

No space, so add that to the initial query creation:

personquery = "select b.* from booking b, makes m  "+
              "where m.personid= "+ 
              DataDeneme1.login.personid.ToString() +
              " and m.bookingno=b.bookingno";

Which would result in:

select b.* from booking b, makes m
where m.personid= 1 and m.bookingno=b.bookingno

Mysql query datagridview c#

2 Answers