13 Replies - 879 Views - Last Post: 10 April 2013 - 06:54 PM Rate Topic: -----

#1 pinko.billi   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 09-April 13

reading date from combobox

Posted 10 April 2013 - 06:20 AM

hi buddies !

i have to load the dates from database in combo box and search all data of selected date from database and show it in data gridview.

the code i am writing is
private void Form15_Load(object sender, EventArgs e)
        {
            try
            {

                OleDbConnection con = new OleDbConnection(" provider=microsoft.jet.oledb.4.0; data source=Database1.mdb  ");
                con.Open();




                OleDbCommand cmd = new OleDbCommand(" select distinct Date_Reg from member     ", con);

                OleDbDataReader dr = cmd.ExecuteReader();


                while (dr.Read())
                {
                    comboBox1.Items.Add(dr.GetValue(0).ToString());
                }

                dr.Close();


                con.Close();

            }
            catch (Exception e1)
            {
                MessageBox.Show("Error in Db=" + e1.Message);
            }
        }
 


On the search button code is
 private void button1_Click(object sender, EventArgs e)
        {
            try
            {

                OleDbConnection con = new OleDbConnection(" provider=microsoft.jet.oledb.4.0; data source=Database1.mdb  ");
                con.Open();
                string b = comboBox1.SelectedItem.ToString();
                OleDbDataAdapter da = new OleDbDataAdapter(" select * from member  where Date_Reg='" + b + "' ", con);
                DataSet ds = new DataSet();
                da.Fill(ds, "member");

                dataGridView1.DataSource = ds.Tables["member"];


                con.Close();


            }
            catch (Exception e1)
            {
                MessageBox.Show("Error in Db=" + e1.Message);
            }


        }


After debugging and selecting the date from combobox when i press the search button it give error data type mismatch in criteria expression

kindly guide me about the error and solution
regards

This post has been edited by modi123_1: 10 April 2013 - 07:38 AM
Reason for edit:: removed excessive font sizing


Is This A Good Question/Topic? 0
  • +

Replies To: reading date from combobox

#2 Skydiver   User is online

  • Code herder
  • member icon

Reputation: 7093
  • View blog
  • Posts: 24,112
  • Joined: 05-May 12

Re: reading date from combobox

Posted 10 April 2013 - 06:38 AM

This looks more like a SQL question rather than a C# question.

Anyway, use parameterized queries. It will take care of using the correct type.
Was This Post Helpful? 0
  • +
  • -

#3 pinko.billi   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 09-April 13

Re: reading date from combobox

Posted 10 April 2013 - 07:00 AM

View PostSkydiver, on 10 April 2013 - 06:38 AM, said:

This looks more like a SQL question rather than a C# question.

Anyway, use parameterized queries. It will take care of using the correct type.


i am using OLEDB data provider not SQL and don,t have know how about SQL
Was This Post Helpful? 0
  • +
  • -

#4 Skydiver   User is online

  • Code herder
  • member icon

Reputation: 7093
  • View blog
  • Posts: 24,112
  • Joined: 05-May 12

Re: reading date from combobox

Posted 10 April 2013 - 07:07 AM

Then what language do you call this from line 9?
" select * from member  where Date_Reg='" + b + "' "



SQL is the language. MSSQL is a database engine. OleDb is a generic interface to various database engines, most of which support SQL queries.
Was This Post Helpful? 0
  • +
  • -

#5 pinko.billi   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 09-April 13

Re: reading date from combobox

Posted 10 April 2013 - 07:30 AM

View PostSkydiver, on 10 April 2013 - 07:07 AM, said:

Then what language do you call this from line 9?
" select * from member  where Date_Reg='" + b + "' "



SQL is the language. MSSQL is a database engine. OleDb is a generic interface to various database engines, most of which support SQL queries.


i am talking about data provider not about language. i am writing a program first time, and donot know much about it. the problem is not in query, the problem is in method which i used, because it gives data type mismatch error.
Was This Post Helpful? 0
  • +
  • -

#6 Skydiver   User is online

  • Code herder
  • member icon

Reputation: 7093
  • View blog
  • Posts: 24,112
  • Joined: 05-May 12

Re: reading date from combobox

Posted 10 April 2013 - 07:38 AM

On which line?
Was This Post Helpful? 0
  • +
  • -

#7 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15308
  • View blog
  • Posts: 61,389
  • Joined: 12-June 08

Re: reading date from combobox

Posted 10 April 2013 - 07:42 AM

09	               OleDbDataAdapter da = new OleDbDataAdapter(" select * from member  where Date_Reg='" + b + "' ", con);

Agreed.. that and are you sure the data in 'b' is in a proper date/time format? Heck - is 'date_reg' even a datetime?


@skydiver - side note, the connection string is to a ".mdb" file.. so MS Access-ish db.
Was This Post Helpful? 0
  • +
  • -

#8 pinko.billi   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 09-April 13

Re: reading date from combobox

Posted 10 April 2013 - 07:59 AM

View Postmodi123_1, on 10 April 2013 - 07:42 AM, said:

09	               OleDbDataAdapter da = new OleDbDataAdapter(" select * from member  where Date_Reg='" + b + "' ", con);

Agreed.. that and are you sure the data in 'b' is in a proper date/time format? Heck - is 'date_reg' even a datetime?


@skydiver - side note, the connection string is to a ".mdb" file.. so MS Access-ish db.


yes data in 'b' is in date time format. and date_reg is datetime.see the snapshot of window form and database.
i am trying to dealing with date as a character string.
Was This Post Helpful? 0
  • +
  • -

#9 Skydiver   User is online

  • Code herder
  • member icon

Reputation: 7093
  • View blog
  • Posts: 24,112
  • Joined: 05-May 12

Re: reading date from combobox

Posted 10 April 2013 - 08:02 AM

Yup, and ACCESS still supports basic SQL. It just typically doesn't support store procedures and triggers. An I recall, the OLEDB standard allows for the query string to be any type of query string, but the defacto standard seems to be SQL. I think I saw a sample once where the query string was more like an XPath rather than SQL. I maybe confusing this with ODBC.
Was This Post Helpful? 0
  • +
  • -

#10 Curtis Rutland   User is offline

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 5106
  • View blog
  • Posts: 9,283
  • Joined: 08-June 10

Re: reading date from combobox

Posted 10 April 2013 - 08:42 AM

Your problem probably is with your query parameters then. I thought Access used # symbols to denote dates? Maybe, maybe not. Point being: if you use parameterized queries, you don't have to worry whether or not you've got the right quoting or escaping. Also, just because my example used the MSSQL provider doesn't mean that isn't completely applicable to the other providers, like OleDb and ODBC. They're all derived from the same base classes.

BTW, your OleDbDataAdapter has a constructor that takes an OleDbCommand:

http://msdn.microsof...y/0wz82t73.aspx

You can construct a command with a parameter and pass that to your adapter instead of string concatenating a statement.
Was This Post Helpful? 0
  • +
  • -

#11 pinko.billi   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 09-April 13

Re: reading date from combobox

Posted 10 April 2013 - 09:15 AM

thanks friend , i got your point. its working by adding # sign.
correct code is
 private void button1_Click(object sender, EventArgs e)
        {
            try
            {

                OleDbConnection con = new OleDbConnection(" provider=microsoft.jet.oledb.4.0; data source=Database1.mdb  ");
                con.Open();
                string b = comboBox1.SelectedItem.ToString();
                OleDbDataAdapter da = new OleDbDataAdapter(" select * from member  where Date_Reg=#" + b + "# ", con);
                DataSet ds = new DataSet();
                da.Fill(ds, "member");

                dataGridView1.DataSource = ds.Tables["member"];


                con.Close();


            }
            catch (Exception e1)
            {
                MessageBox.Show("Error in Db=" + e1.Message);
            }


Was This Post Helpful? 0
  • +
  • -

#12 Curtis Rutland   User is offline

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 5106
  • View blog
  • Posts: 9,283
  • Joined: 08-June 10

Re: reading date from combobox

Posted 10 April 2013 - 10:44 AM

You took the wrong message from my post :whatsthat:

The point isn't to manually escape your queries, the point is to do it right. Parameterized queries are easier to write, easier to read, and far, far more secure than string building. I suggest learning how to do it the right way from the beginning, instead of teaching yourself bad habits.
Was This Post Helpful? 0
  • +
  • -

#13 Michael26   User is offline

  • Futurama: Insert funny joke here
  • member icon

Reputation: 414
  • View blog
  • Posts: 1,664
  • Joined: 08-April 09

Re: reading date from combobox

Posted 10 April 2013 - 11:10 AM

SQL Injection and why you should care
Was This Post Helpful? 0
  • +
  • -

#14 pinko.billi   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 15
  • Joined: 09-April 13

Re: reading date from combobox

Posted 10 April 2013 - 06:54 PM

View PostCurtis Rutland, on 10 April 2013 - 10:44 AM, said:

You took the wrong message from my post :whatsthat:/>

The point isn't to manually escape your queries, the point is to do it right. Parameterized queries are easier to write, easier to read, and far, far more secure than string building. I suggest learning how to do it the right way from the beginning, instead of teaching yourself bad habits.

:bigsmile: i took the wrong message but it solve my problem. well i try to learn how to do it. thaks :bigsmile:
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1