First code in c#

Question

I'm very new in C#. I've written a small code. Can you help me with it : Here is my code :

private void button1_Click(object sender, EventArgs e)
{
    string povezava = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Users\\Matic\\Documents\\FERI\\2.LETNIK\\1.SEMESTER\\RPS\\VAJA3\\bazaPodatkov.accdb";
    string zapisi = "update Zaposleni set ID='" + this.textBox1.Text + 
"',Ime='" + this.textBox2.Text + 
"',Priimek='" + this.textBox3.Text + 
"',Uporabnisko_ime='" + this.textBox4.Text + 
"',Geslo='" + this.textBox5.Text + 
"',E_posta='" + this.textBox6.Text + 
"',Ulica='" + this.textBox7.Text + 
"',Hisna_stevilka='" + this.textBox8.Text + 
"',Mesto='" + this.textBox9.Text + 
"',Delovno_mesto='" + this.textBox10.Text + 
"' where ID='" + this.textBox1.Text + "';";

    OleDbConnection baza = new OleDbConnection(povezava);
    OleDbCommand beri = new OleDbCommand(zapisi, baza);
    OleDbDataReader branje;
    try
    {
        baza.Open();
        branje = beri.ExecuteReader();
        MessageBox.Show("Podatki so shranjeni.");
        while (branje.Read())
        {

        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

I'm having a datatype mismatch in criteria expression.

Do you see anything wrong in my code ?

I recommend setting a break point, stepping through the code and then looking at the zapisi string value prior to getting the error. You could also take the update statement from the string value and run directly in you database to see if the actual statement is valid. Good Luck. — Seymour, Jan 11 '14 at 18:08
Are all of your fields type, String? for example is your ID field, String? — Masoud, Jan 11 '14 at 18:52
This is most likely an error produced by the query execution and not an error in the C# code. My guess is that `ID` is numeric. In that case the WHERE-clause should be written without apostrophes: `WHERE ID = 30`. — Olivier Jacot-Descombes, Jan 11 '14 at 21:56

score 3 · Accepted Answer · edited May 23 '17 at 10:25

You code contains some visible errors and probably some invisible errors.

First problem. Never do a string concatenation to build sql queries. Use always a parameterized query.
You put placeholders (? for OleDb/Access) where you want to put the values and then build a collection of parameters where you set the values. It is the framework code that substitutes the placeholders with the parameter's values putting the appropriate quotes around the values and removing the possibility of sql injection attacks (Little Bobby Tables).

Second, you have an update query, then there is no sense in returning an OleDbDataReader.
Just execute the command (by calling ExecuteNonQuery) and eventually check if the query updates any rows.

Third, enclose the OleDbConnection/Command with the using statement to ensure proper closing and cleanup of the connection and the command.

private void button1_Click(object sender, EventArgs e)
{
    string povezava = "........."
    string zapisi = "update Zaposleni set ID=?,Ime=?,Priimek=?,Uporabnisko_ime=?,Geslo=?," + 
                    "E_posta=?,Ulica=?,Hisna_stevilka=?,Mesto=?,Delovno_mesto=? " + 
                    "where ID=?";
    try
    {
        using(OleDbConnection baza = new OleDbConnection(povezava))
        using(OleDbCommand beri = new OleDbCommand(zapisi, baza))
        {
            baza.Open();
            beri.Parameters.AddWithValue("@p1",this.textBox1.Text);
            beri.Parameters.AddWithValue("@p2",this.textBox2.Text);
            beri.Parameters.AddWithValue("@p3",this.textBox3.Text);
            .... and so on for the other parameters
            beri.Parameters.AddWithValue("@p11",this.textBox1.Text);
            int rowsAffected = beri.ExecuteNonQuery();
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

Finally, the invisible errors. You pass all of your values as strings. This is correct only if the database fields that receive the values are text fields. If one or more of your fields is not a string, (for example a date or an integer) then trying to set its value to a string triggers the datatype mismatch in criteria expression error message.

If this is the case then you need to convert the string to the appropriate datatype. For example (assuming that the ID field is a numeric field)

 beri.Parameters.AddWithValue("@p1",Convert.ToInt32(this.textBox1.Text));

score 0 · Answer 2 · answered Jan 11 '14 at 18:16

0

You have one extra semicolon end of your command string (zapisi) delete it and try again.

Also consider using parameterized queries

answered Jan 11 '14 at 18:16

Selman Genç

100,147
13
119
184

First code in c#

2 Answers2