unknown column ' column name' in 'fieldlist'

Question

I am trying to select my column no.ofTrips in my tbl_bill_addtrips but when everytime i click the button, OK. the Unknown column 'no.ofTrips' in 'fieldlist' appear. this is my sample code

try
    {
        string MyConSs = "SERVER=localhost;" +
         "DATABASE=prototype_db;" +
         "UID=root;";

        MySqlConnection conns = new MySqlConnection(MyConSs);
        MySqlCommand comms = conns.CreateCommand();
        MySqlDataAdapter da = new MySqlDataAdapter();

        comms.CommandText = "Select no.ofTrips FROM tbl_bill_addtrips WHERE RouteFrom = '" + this.txtBillRouteFrom.Text + "' and RouteTo ='" + txtBillRouteTo.Text + "'";
        MySqlDataReader Readers;
        conns.Open();
        Readers = comms.ExecuteReader();
        while (Readers.Read())
        {
            txtNoOfTrips.Text = Readers.GetValue(0).ToString();
        }
        Readers.Close();
        conns.Close();
    }
    catch (Exception error)
    {
        MessageBox.Show(error.ToString());
    }

please help me thanks in advance

cross check whether you have a column name as named above. And check whether its spelled correct or not — Vivekh, Dec 06 '13 at 11:21
I would follow Rob's advice. To avoid furthe problems, I would also suggest avoiding dots in column names. — Laurent S., Dec 06 '13 at 11:24
Rob's right . or right click on table and select design and copy the name of the column and paste it here. — Vivekh, Dec 06 '13 at 11:29

CodingIntrigue · Answer 1 · 2013-12-06T11:29:09.757

1

If your column name really does contain a . then you need to wrap it in brackets:

Select [no.ofTrips] FROM tbl_bill_addtrips WHERE RouteFrom ...
//     ^          ^

Or a backtick for MySQL:

Select `no.ofTrips` FROM tbl_bill_addtrips WHERE RouteFrom ...
//     ^          ^

Also, this code is susceptible to SQL Injection Attacks. Please have a read about parameterized queries.

edited Dec 06 '13 at 11:29

answered Dec 06 '13 at 11:21

CodingIntrigue

75,930
30
170
176

score 0 · Accepted Answer · edited Dec 06 '13 at 11:28

0

You have table alias "no" but thats not defined in from section.
Try this:

Select no.ofTrips FROM tbl_bill_addtrips no WHERE ...

Or remove alias from column:

Select ofTrips FROM tbl_bill_addtrips WHERE ...

edited Dec 06 '13 at 11:28

Suraj Singh

4,041
1
21
36

answered Dec 06 '13 at 11:21

Panu Oksala

3,245
1
18
28

score 0 · Answer 3 · edited May 23 '17 at 12:21

You cannot have column name no.ofTrips as you are using in query. It will separate in table and column. for example no as Table name which contain ofTrips as a column. Check the column name is correct in the query.

Solution: Correct the no.ofTrips column name with allowed characters in sql.

Query many be as below:

"Select no.ofTrips FROM tbl_bill_addtrips as no WHERE RouteFrom = '"....

here no is made as abbrivation of the tbl_bill_addtrips that may be you are missing..

Check this - What special characters are allowed in T-SQL column name?

Crazy Programmer · Answer 4 · 2013-12-06T11:30:39.187

0

its happening because your column name consists of a '.' try renaming your column to no_of_Trips or no_ofTrips as because of . it takes 'no' as a table and 'ofTrips' as a column

you can also use [] around column name as [no.ofTrips]

edited Dec 06 '13 at 11:30

answered Dec 06 '13 at 11:23

Crazy Programmer

196
1
3
18

unknown column ' column name' in 'fieldlist'

4 Answers4