Click here to Skip to main content
14,305,260 members
Rate this:
Please Sign up or sign in to vote.
See more:
Actually im trying to insert as well as update data into datagridview at run time for that i have written one code but while executing it give me
Data type mismatch in criteria expression
exception in cmd
.ExecuteNonQuery();
line
my accdb table structure is like below

Field Datatype
Account-- Memo
AccountNumber--Number
Date--Date/Time
Description--Memo
Post_Ref--Memo
Debit--Number
Credit--Number
Balance--Number

What I have tried:

my code:

private void dataGridView1_CellValueChanged(object sender, DataGridViewCellEventArgs e)
      {
          //try
          //{
              account = dataGridView1.Rows[e.RowIndex].Cells["AccountNumber"].Value.ToString();
              if (account == "")
              {
                  account1 = 0;
              }
              else
              {
                  account1 = Convert.ToInt32(dataGridView1.Rows[e.RowIndex].Cells["AccountNumber"].Value.ToString());
              }
              if (account1 == 0)
              {
                  OleDbCommand cmd = con.CreateCommand();
                  cmd.CommandType = CommandType.Text;
                  cmd.CommandText = "insert into Ledger values('" + dataGridView1.Rows[e.RowIndex].Cells["Account"].Value.ToString() + "','" + dataGridView1.Rows[e.RowIndex].Cells["AccountNumber"].Value.ToString() + "','" + dataGridView1.Rows[e.RowIndex].Cells["Date"].Value.ToString() + "','" + dataGridView1.Rows[e.RowIndex].Cells["Description"].Value.ToString() + "','" + dataGridView1.Rows[e.RowIndex].Cells["Post_Ref"].Value.ToString() + "','" + dataGridView1.Rows[e.RowIndex].Cells["Debit"].Value.ToString() + "','" + dataGridView1.Rows[e.RowIndex].Cells["Credit"].Value.ToString() + "','" + dataGridView1.Rows[e.RowIndex].Cells["Balance"] + "')";
                  cmd.ExecuteNonQuery();--> exception coming this line
                  Load_data();
              }
Posted
Updated 20-Mar-16 6:31am
v2
Comments
F-ES Sitecore 20-Mar-16 12:31pm
   
Convert your code to use parameterised queries (google "c# ado.net parameterized queries") and convert your values to actual types like int, DateTime etc where relevant. If done correctly it'll fix the issue, and if not it will hopefully make it clearer where the problem lies.
Atul Rokade 20-Mar-16 12:37pm
   
thank you for reply sir , but how could i do this ? because its datagridview how can i added parameterized queries for datagridview
Atul Rokade 20-Mar-16 13:51pm
   
still got same error sir i changed into code

string connectionString = null;

connectionString = ConfigurationManager.ConnectionStrings["AccessConnectionString"].ConnectionString;

con.ConnectionString = connectionString;
string cmd1 = "insert into Ledger([Account],[AccountNumber],[Date],[Description],[Post_Ref],[Debit],[Credit],[Balance])values(?,?,?,?,?,?,?,?)";

OleDbCommand cmd = new OleDbCommand(cmd1, con);
con.Open();
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@Account",dataGridView1.Rows[e.RowIndex].Cells["Account"].Value.ToString());
cmd.Parameters.AddWithValue("@AccountNumber", dataGridView1.Rows[e.RowIndex].Cells["AccountNumber"].Value.ToString());
cmd.Parameters.AddWithValue("@Date", dataGridView1.Rows[e.RowIndex].Cells["Date"].Value.ToString());
cmd.Parameters.AddWithValue("@Description", dataGridView1.Rows[e.RowIndex].Cells["Description"].Value.ToString());
cmd.Parameters.AddWithValue("@Post_Ref", dataGridView1.Rows[e.RowIndex].Cells["Post_Ref"].Value.ToString());
cmd.Parameters.AddWithValue("@Debit", dataGridView1.Rows[e.RowIndex].Cells["Debit"].Value.ToString());
cmd.Parameters.AddWithValue("@Credit", dataGridView1.Rows[e.RowIndex].Cells["Credit"].Value.ToString());
cmd.Parameters.AddWithValue("@Balance", dataGridView1.Rows[e.RowIndex].Cells["Balance"].Value.ToString());
int n = cmd.ExecuteNonQuery();
con.Close();

Load_data();
F-ES Sitecore 20-Mar-16 13:53pm
   
You're passing all of your params as a string (via ToString). For params that aren't string you need to parse them to their native formats. So for ints use int.TryParse, for dates use DateTime.TryParse etc.
Atul Rokade 20-Mar-16 14:17pm
   
sir its giving me Error No overload for method 'TryParse' takes '1' arguments
i written code
cmd.Parameters.AddWithValue("@Account",dataGridView1.Rows[e.RowIndex].Cells["Account"].Value.ToString());
cmd.Parameters.AddWithValue("@AccountNumber",Int32.TryParse(dataGridView1.Rows[e.RowIndex].Cells["AccountNumber"].Value.ToString()));
cmd.Parameters.AddWithValue("@Date", DateTime.TryParse(dataGridView1.Rows[e.RowIndex].Cells["Date"].Value.ToString()));
cmd.Parameters.AddWithValue("@Description", dataGridView1.Rows[e.RowIndex].Cells["Description"].Value.ToString());
cmd.Parameters.AddWithValue("@Post_Ref", dataGridView1.Rows[e.RowIndex].Cells["Post_Ref"].Value.ToString());
cmd.Parameters.AddWithValue("@Debit", Int32.TryParse(dataGridView1.Rows[e.RowIndex].Cells["Debit"].Value.ToString()));
cmd.Parameters.AddWithValue("@Credit", Int32.TryParse(dataGridView1.Rows[e.RowIndex].Cells["Credit"].Value.ToString()));
cmd.Parameters.AddWithValue("@Balance", Int32.TryParse(dataGridView1.Rows[e.RowIndex].Cells["Balance"].Value.ToString()));
F-ES Sitecore 20-Mar-16 16:22pm
   

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

Do not do that! Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.
And pass the values in the correct format directly via the parameters. So you pass an int to a INT column, a DateTime to a DATE or DATETIME column, and only use strigns for VARCHAR or NVARCHAR columns.
The chances are, that will cure your problem at the same time.

And while you are making changes, list the column names you are inserting into:
INSERT INTO MyTable (Column1, Column2) VALUES (?,?)
If you don't, then the DB will try to insert the values in the table current order: so if the first column is an IDENTITY field, the update will fail. And any future changes to the DB may break your code.
   
Comments
Atul Rokade 20-Mar-16 12:38pm
   
thank you for reply sir , but how could i do this ? because its datagridview how can i added parameterized queries for datagridview
OriginalGriff 20-Mar-16 12:51pm
   
The same way you would for any parameterised Access query:
cmd.CommandText = "INSERT INTO MyTable (Column1, Column2) VALUES (?,?)";
cmd.Parameters.AddWithValue("@COL1", dataGridView1.Rows[e.RowIndex].Cells["Account"].Value);
...
Atul Rokade 20-Mar-16 13:11pm
   
ok sir i will try this
Atul Rokade 20-Mar-16 13:51pm
   
still got same error sir i changed into code

string connectionString = null;

connectionString = ConfigurationManager.ConnectionStrings["AccessConnectionString"].ConnectionString;

con.ConnectionString = connectionString;
string cmd1 = "insert into Ledger([Account],[AccountNumber],[Date],[Description],[Post_Ref],[Debit],[Credit],[Balance])values(?,?,?,?,?,?,?,?)";

OleDbCommand cmd = new OleDbCommand(cmd1, con);
con.Open();
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@Account",dataGridView1.Rows[e.RowIndex].Cells["Account"].Value.ToString());
cmd.Parameters.AddWithValue("@AccountNumber", dataGridView1.Rows[e.RowIndex].Cells["AccountNumber"].Value.ToString());
cmd.Parameters.AddWithValue("@Date", dataGridView1.Rows[e.RowIndex].Cells["Date"].Value.ToString());
cmd.Parameters.AddWithValue("@Description", dataGridView1.Rows[e.RowIndex].Cells["Description"].Value.ToString());
cmd.Parameters.AddWithValue("@Post_Ref", dataGridView1.Rows[e.RowIndex].Cells["Post_Ref"].Value.ToString());
cmd.Parameters.AddWithValue("@Debit", dataGridView1.Rows[e.RowIndex].Cells["Debit"].Value.ToString());
cmd.Parameters.AddWithValue("@Credit", dataGridView1.Rows[e.RowIndex].Cells["Credit"].Value.ToString());
cmd.Parameters.AddWithValue("@Balance", dataGridView1.Rows[e.RowIndex].Cells["Balance"].Value.ToString());
int n = cmd.ExecuteNonQuery();
con.Close();

Load_data();

OriginalGriff 20-Mar-16 14:34pm
   
:sigh:
Read what I said:
"And pass the values in the correct format directly via the parameters. So you pass an int to a INT column, a DateTime to a DATE or DATETIME column, and only use strings for VARCHAR or NVARCHAR columns."
Atul Rokade 20-Mar-16 14:40pm
   
griff sir i done this
cmd.Parameters.AddWithValue("@Account",dataGridView1.Rows[e.RowIndex].Cells["Account"].Value.ToString());
cmd.Parameters.AddWithValue("@AccountNumber",int.TryParse(dataGridView1.Rows[e.RowIndex].Cells["AccountNumber"].Value));
cmd.Parameters.AddWithValue("@Date", DateTime.TryParse(dataGridView1.Rows[e.RowIndex].Cells["Date"].Value));
cmd.Parameters.AddWithValue("@Description", dataGridView1.Rows[e.RowIndex].Cells["Description"].Value.ToString());
cmd.Parameters.AddWithValue("@Post_Ref", dataGridView1.Rows[e.RowIndex].Cells["Post_Ref"].Value.ToString());
cmd.Parameters.AddWithValue("@Debit", Int32.TryParse(dataGridView1.Rows[e.RowIndex].Cells["Debit"].Value));
cmd.Parameters.AddWithValue("@Credit", Int32.TryParse(dataGridView1.Rows[e.RowIndex].Cells["Credit"].Value));
cmd.Parameters.AddWithValue("@Balance", Int32.TryParse(dataGridView1.Rows[e.RowIndex].Cells["Balance"].Value));

but still it giving me Error No overload for method 'TryParse' takes '1' arguments
Atul Rokade 21-Mar-16 12:14pm
   
sir please i need your help could you help me please
OriginalGriff 21-Mar-16 12:31pm
   
Look at the documentation for TryParse and they are all the same:
xxx.TryParse(stringToConvert, out variableToPutTheResult)
It returns a bool which says "it worked" / "it failed" so you can report a problem user / log the problem / not proceed to add rubbish to your DB... :laugh:

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100