C# datagridview data type mismatch problem

[lianpiau]

Hello! any expert can help me. Please.

I'm design a form with click Search button to search date and display data in datagridview. but when I set my access database OTDate column as Date, It will cause data mismatch problem. however, set OTDate column as Text is fine. But it will difficult when make crystal report sorting by month or year.

my date format is short date time. dd/MMM/yy

this is code:

private void btnSearch_Click(object sender, EventArgs e)
        {
                


                string sql= "SELECT * FROM OT where OTDate ='" + dtpDate.Text + "'";
                

                OleDbConnection connection = new OleDbConnection(ConnectionString);
                connection.Open();
                sCommand = new OleDbCommand(sql, connection);
                sAdapter = new OleDbDataAdapter(sCommand);
                sBuilder = new OleDbCommandBuilder(sAdapter);
                sDs = new DataSet();
                sAdapter.Fill(sDs, "OT");
                sTable = sDs.Tables["OT"];
                connection.Close();
                dataGridView1.DataSource = sDs.Tables["OT"];
                dataGridView1.ReadOnly = true;
                save_btn.Enabled = false;
                dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
            
            
        }

[Mitja Bonca]

The question is, do you really need a dataSet? Do you have more then one DataTable?
Becuause DataSet is nothing else then a bunch of dataTables.

Some strange code you have there:

sAdapter.Fill(sDs, "OT"); //OT is the DataTable name right?
sTable = sDs.Tables["OT"];

better try this was:

sDs = new DataSet();
DataTable sDt = new DataTable("myDataTable");
sAdapter.Fill(sdt);
sDs.Tables.Add(sDt);
dataGridView1.DataSource = sDs.Tables["myDataTable"];

Hope it helps,
Mitja

[lianpiau]

Hello! any expert can help me. Please.

I'm design a form with click Search button to search date and display data in datagridview. but when I set my access database OTDate column as Date, It will cause data mismatch problem. however, set OTDate column as Text is fine. But it will difficult when make crystal report sorting by month or year.

my date format is short date time. dd/MMM/yy

this is code:

private void btnSearch_Click(object sender, EventArgs e) {

string sql= "SELECT * FROM OT where OTDate ='" + dtpDate.Text + "'";

OleDbConnection connection = new OleDbConnection(ConnectionString); connection.Open(); sCommand = new OleDbCommand(sql, connection); sAdapter = new OleDbDataAdapter(sCommand); sBuilder = new OleDbCommandBuilder(sAdapter); sDs = new DataSet(); sAdapter.Fill(sDs, "OT"); sTable = sDs.Tables["OT"]; connection.Close(); dataGridView1.DataSource = sDs.Tables["OT"]; dataGridView1.ReadOnly = true; save_btn.Enabled = false; dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect; }

Thank you for u reply my question. But still cannot.
I'm attach my source program with zip file. Hope u can help me solve the problem.

[lianpiau]

The question is, do you really need a dataSet? Do you have more then one DataTable? Becuause DataSet is nothing else then a bunch of dataTables.

Some strange code you have there:

sAdapter.Fill(sDs, "OT"); //OT is the DataTable name right?
sTable = sDs.Tables["OT"];

better try this was:

sDs = new DataSet();
DataTable sDt = new DataTable("myDataTable");
sAdapter.Fill(sdt);
sDs.Tables.Add(sDt);
dataGridView1.DataSource = sDs.Tables["myDataTable"];

Hope it helps, Mitja

thank you reply me. but still can't. I'm attach program file

[__avd]

I've changed hard-coded sql string into parameterized sql.

string sql= "SELECT * FROM OT where OTDate =@odate"

 OleDbConnection connection = new OleDbConnection(ConnectionString);
 
 sCommand = new OleDbCommand(sql, connection);
 sCommand.Parameters.AddWithValue("@odate",dtpDate.Value);

 sAdapter = new OleDbDataAdapter(sCommand);
 sBuilder = new OleDbCommandBuilder(sAdapter);
 sDs = new DataSet();
 sAdapter.Fill(sDs, "OT");
 sTable = sDs.Tables["OT"];
 .....

[lianpiau]

I've changed hard-coded sql string into parameterized sql.

string sql= "SELECT * FROM OT where OTDate =@odate"

 OleDbConnection connection = new OleDbConnection(ConnectionString);
 
 sCommand = new OleDbCommand(sql, connection);
 sCommand.Parameters.AddWithValue("@odate",dtpDate.Value);

 sAdapter = new OleDbDataAdapter(sCommand);
 sBuilder = new OleDbCommandBuilder(sAdapter);
 sDs = new DataSet();
 sAdapter.Fill(sDs, "OT");
 sTable = sDs.Tables["OT"];
 .....

Thanks adatapost, you coding is work.
Thank you very much.

[Esofts]

Hello guys. am having a problem retrieving a record set from a database and saving it to a data set with c#.
I have a function1 that manipulate parametrized Dataset by calculating average marks and Grades then saves this data set to a database. It works fine but when i try to retrieve the saved data an "Data type mismatch in criteria expressions error occur" but when i type into the database manually it works fine with no error please help;
the code that save record to a dataset is:

public DataSet Select_Exams_from_db( string Currform, string Term, string Year)
        {
            if (this.con.State.Equals(System.Data.ConnectionState.Open))
            {
            }
            else
            {
                dbconnect dbconnect = new dbconnect();
                this.con = dbconnect.Set_connection();
            }
            OleDbDataAdapter data = new OleDbDataAdapter();


            sql = "SELECT  [sid], [curr_term],[Current_form],[Year],[stud_name],avg(math),avg(english),avg(kiswahili),avg(chemistry),avg(physics),avg(business),avg(cre), avg(agriculture), avg(history),avg(geography), avg(biology), avg(computer) FROM examinations WHERE curr_term=@Term AND Current_form=@Currform AND Year=@Year GROUP BY [sid] ,[curr_term],[Current_form],[Year],[stud_name]";
           
            
           
            
            DataSet tabledata = new DataSet();
            using (con)
            {
                data.SelectCommand = new OleDbCommand(sql, con);
                data.SelectCommand.Parameters.AddWithValue("@Term ",Term);
                data.SelectCommand.Parameters.AddWithValue("@Currform",Currform);
                data.SelectCommand.Parameters.AddWithValue("@Year",Convert.ToInt16(Year));            

                 
                try
                {
                   
                    data.Fill(tabledata);
                   
                    
                    
                }
                catch (OleDbException err)
                {
                    MessageBox.Show(err.Message);
                    tabledata = null;
                   
                }
   
                data = null;
                
                return tabledata;
            }
         }

I tried to omit the "Where " clause in my sql statement but still it brings the same error.

the function for saving a dataset to database is working fine but upon saving the above(later) function returns an error when i type data into the database manually the fucntion works well:

public void Save_Dataset_2database(DataSet ds)
        {
            //code for saving goes here

            }
        }

what might be the problem ? because it is not working event when the sql statement dont filter data i.e when[CODhttp://images.daniweb.com/vbulletin/editor/color.gifE] sql="SELECT [sid], [curr_term],[Current_form],[Year],[stud_name],avg(math),avg(english),avg(kiswahili),avg(chemistry),avg(physics),avg(business),avg(cre), avg(agriculture), avg(history),avg(geography), avg(biology), avg(computer) FROM examinations GROUP BY [sid] ,[curr_term],[Current_form],[Year],[stud_name]";[/CODE]

[Esofts]

Hello guys. am having a problem retrieving a record set from a database and saving it to a data set with c#.
I have a function1 that manipulate parametrized Dataset by calculating average marks and Grades then saves this data set to a database. It works fine but when i try to retrieve the saved data an "Data type mismatch in criteria expressions error occur" but when i type into the database manually it works fine with no error please help;
the code that save record to a dataset is:

public DataSet Select_Exams_from_db( string Currform, string Term, string Year)
        {
            if (this.con.State.Equals(System.Data.ConnectionState.Open))
            {
            }
            else
            {
                dbconnect dbconnect = new dbconnect();
                this.con = dbconnect.Set_connection();
            }
            OleDbDataAdapter data = new OleDbDataAdapter();


            sql = "SELECT  [sid], [curr_term],[Current_form],[Year],[stud_name],avg(math),avg(english),avg(kiswahili),avg(chemistry),avg(physics),avg(business),avg(cre), avg(agriculture), avg(history),avg(geography), avg(biology), avg(computer) FROM examinations WHERE curr_term=@Term AND Current_form=@Currform AND Year=@Year GROUP BY [sid] ,[curr_term],[Current_form],[Year],[stud_name]";
           
            
           
            
            DataSet tabledata = new DataSet();
            using (con)
            {
                data.SelectCommand = new OleDbCommand(sql, con);
                data.SelectCommand.Parameters.AddWithValue("@Term ",Term);
                data.SelectCommand.Parameters.AddWithValue("@Currform",Currform);
                data.SelectCommand.Parameters.AddWithValue("@Year",Convert.ToInt16(Year));            

                 
                try
                {
                   
                    data.Fill(tabledata);
                   
                    
                    
                }
                catch (OleDbException err)
                {
                    MessageBox.Show(err.Message);
                    tabledata = null;
                   
                }
   
                data = null;
                
                return tabledata;
            }
         }

I tried to omit the "Where " clause in my sql statement but still it brings the same error.

the function for saving a dataset to database is working fine but upon saving the above(later) function returns an error when i type data into the database manually the fucntion works well:

public void Save_Dataset_2database(DataSet ds)
        {
            //code for saving goes here

            }
        }

what might be the problem ? because it is not working event when the sql statement dont filter data i.e when

sql="SELECT  [sid], [curr_term],[Current_form],[Year],[stud_name],avg(math),avg(english),avg(kiswahili),avg(chemistry),avg(physics),avg(business),avg(cre), avg(agriculture), avg(history),avg(geography), avg(biology), avg(computer) FROM examinations  GROUP BY [sid] ,[curr_term],[Current_form],[Year],[stud_name]";

[Momerath]

Appending your question to the end of a 7 month old closed thread is probably not the best idea. Since you have a new question, open a new thread.

That said, what the error is telling you is that the type of one of these

data.SelectCommand.Parameters.AddWithValue("@Term ",Term);
data.SelectCommand.Parameters.AddWithValue("@Currform",Currform);
data.SelectCommand.Parameters.AddWithValue("@Year",Convert.ToInt16(Year));

Is not the same type as on the database.