-1

I'm writing a lab for a C# class to manage an Access database. It's a C# GUI program that uses a DataGridView to view a database and write to it.

The save table class will not work and gives me the same exception: SystemData.OleDb.OleDbException: 'Data type mismatch in criteria expression.'

I understand the code might be vulnerable to SQL injection but this is a one time lab that's on the clock, need to get a solution to the problem at hand. Not worried about parameters unless they'd fix this issue.


 private void button2_Click_1(object sender, EventArgs e)
        {
            string connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:/Users/sdepasqu/Documents/Customer Database.accdb";

            foreach (DataGridViewRow row in dataGridView1.Rows)
            {
                using (OleDbConnection conn = new OleDbConnection(connString))
                {
                    using (OleDbCommand cmd = new OleDbCommand(@"INSERT INTO customer([CUST_ID], [FIRST_NAME], [LAST_NAME], [ADDRESS], [CITY], [STATE], [POSTAL], [EMAIL], [BALANCE], [CREDIT_LIMIT], [REP_ID]) VALUES(@cust_id, @first_name, @last_name, @address, @city, @state, @postal, @email, @balance, @credit_limit, @rep_id)", conn))
                    {
                        conn.Open();
                        cmd.Parameters.AddWithValue("@id", row.Cells["iDDataGridViewTextBoxColumn"].Value);
                        cmd.Parameters.AddWithValue("@cust_id", row.Cells["cUSTIDDataGridViewTextBoxColumn"].Value);
                        cmd.Parameters.AddWithValue("@first_name", row.Cells["fIRSTNAMEDataGridViewTextBoxColumn"].Value);
                        cmd.Parameters.AddWithValue("@last_name", row.Cells["lASTNAMEDataGridViewTextBoxColumn"].Value);
                        cmd.Parameters.AddWithValue("@address", row.Cells["aDDRESSDataGridViewTextBoxColumn"].Value);
                        cmd.Parameters.AddWithValue("@city", row.Cells["cITYDataGridViewTextBoxColumn"].Value);
                        cmd.Parameters.AddWithValue("@state", row.Cells["sTATEDataGridViewTextBoxColumn"].Value);
                        cmd.Parameters.AddWithValue("@postal", row.Cells["pOSTALDataGridViewTextBoxColumn"].Value);
                        cmd.Parameters.AddWithValue("email", row.Cells["eMAILDataGridViewTextBoxColumn"].Value);
                        cmd.Parameters.AddWithValue("@balance", row.Cells["bALANCEDataGridViewTextBoxColumn"].Value);
                        cmd.Parameters.AddWithValue("@credit_limit", row.Cells["cREDITLIMITDataGridViewTextBoxColumn"].Value);
                        cmd.Parameters.AddWithValue("@rep_id", row.Cells["rEPIDDataGridViewTextBoxColumn"].Value);
                        cmd.ExecuteNonQuery();
                    }
                }
            }
        }

The database table in question:

enter image description here

enter image description here

Tried to do a cmd.Parameters.Add and specify the OleDbDataType but it threw up a bunch of errors at me.

Tu deschizi eu inchid
  • 4,117
  • 3
  • 13
  • 24
Salvatore A Depasquale
  • 1
  • 3
    The problem is caused by AddWithValue. This shortcut to define parameters is often a trap because it looks at the datatype of the value to build an oledbcommand parameter datatype. Now if your last three cells are defined to contains text then the AddWithValue builds a parameter of type text that is not valid for the related columns. Use the Add method and convert the text from the cell to a proper decimal value valid for the columns for the InvariantCulture – Steve Nov 30 '22 at 18:53
  • In other words: _"$315.00"_ is a string, the column expects a decimal. – Steve Nov 30 '22 at 18:57
  • According to [OleDbCommand.Parameters](https://learn.microsoft.com/en-us/dotnet/api/system.data.oledb.oledbcommand.parameters?view=dotnet-plat-ext-7.0#remarks): _The OLE DB .NET Provider does not support named parameters for passing parameters to an SQL statement or a stored procedure called by an OleDbCommand when CommandType is set to Text._ The following may be helpful: https://stackoverflow.com/a/69638011/10024425 – Tu deschizi eu inchid Dec 01 '22 at 00:20
  • Remove `cmd.Parameters.AddWithValue("@id", row.Cells["iDDataGridViewTextBoxColumn"].Value);`. – Tu deschizi eu inchid Dec 01 '22 at 00:21
  • If you take the time to better understand the abilities of your DB POrovider tools, you'll find that all that could could be replaced by `myDataAdapter.Update();` no loop, no fishing data out of the UI elements, just one statement. – Ňɏssa Pøngjǣrdenlarp Dec 01 '22 at 02:53
  • Here's some different code that may be helpful: https://stackoverflow.com/a/70484316/10024425. – Tu deschizi eu inchid Dec 01 '22 at 06:08

1 Answers1

0

Here is an example of cmd parameter:

cmd.Parameters.Add("@item_number", SqlDbType.VarChar).Value = lblitemnum.Text;

Go through your table and write down what type each column is , and re-write your code compared to example above. AutoNumber will probably be int, shorttext should be varchar, cust_id should be int also. Currency should be money, an so on.

duerzd696
  • 304
  • 1
  • 8