Insert record(s) DB from Form

Question

I have an Access DB connected to my form with that code ( C# ) :

System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection();
conn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data source= Z:\Tempesta\Area Progetto\Area_Progetto_20_02_2014\Area_Progetto_DATA_MAGAZINE\Data_Magazine\Data_Magazine\DB\DataMG.mdb";
try
{
    System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand();
    cmd.CommandType = System.Data.CommandType.Text;
    cmd.CommandText = "INSERT into Prodotti ([Codice],[Descrizione],[Marchio],[Deposito],[Note],[NumeroProdotti],[PrzListinoBase_Aq],[PrzListinoBase_Ve],[Categoria],[Posizione],[Disponibilita],[QtaVenduta],[QtaAcquistata]) VALUES ('" + this.Codice.Text + "','" + this.Descr.Text + "','" + this.Marchio.Text + "','" + this.Deposito.Text + "'," + this.Note.Text + "," + this.NumProd.Text + "," + this.PrzListAcq.Text + "," + this.PrzListVen.Text + ",'" + this.Categ.Text + "','" + this.Posiz.Text + "'," + this.Disp.Text + "," + this.QtaVen.Text + "," + this.QtaAcq.Text + ")";
    cmd.Connection = conn;
    conn.Open();
    cmd.ExecuteNonQuery();
    conn.Close(); 
}
catch(Exception ex)
{
    MessageBox.Show(ex.ToString());
    // MessageBox.Show("Connessione Fallita!");
    conn.Close();
}
finally
{
    conn.Close();
}

The error I get when i click the buttton is this one :

Error

Any ideas?

unable to understand the language in exception could you please try to translate it ?? — Aftab Ahmed, Feb 26 '14 at 21:24
The `INSERT INTO` statement seems to look alright, however if any of the following are empty then this may cause the error: `Note.Text`, `NumProd.Text`, `PrzListAcq.Text`, `PrzListVen.Text`, `Disp.Text`, `QtaVen.Text`, `QtaAcq.Text`. They are not surrounded by single-quotes (which may be fine as they are numbers?) but this would mean that if they were empty the value list would have two commas next to each other. — Martin, Feb 26 '14 at 21:28

score 0 · Answer 1 · answered Feb 26 '14 at 21:27

I don't know italian (is that even the language? :) ) but from the look of it it could very well be a culture settings problem. If, for example, one of your fields is numeric then the database might expect a different decimal separator than the one in use in your UI.

Also your actual design seems very vulnerable to SQL Injection Attacks.

For these reasons, my suggestion is that you use the command's Parameters collection to set your values rather than trying to pass in a concatenated string.

score 0 · Answer 2 · answered Feb 26 '14 at 21:28

I don't read the language you are posting the error from, however, it looks like a syntax error somewhere in your SqlCommand.

First thing I would suggest is wrapping your connection and command in using blocks to make sure they get disposed of correctly.

Then ALWAYS user parametarized SQL Commands to avoid SQL Injection:

using (var conn = new System.Data.OleDb.OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data source= Z:\Tempesta\Area Progetto\Area_Progetto_20_02_2014\Area_Progetto_DATA_MAGAZINE\Data_Magazine\Data_Magazine\DB\DataMG.mdb"))
using (var cmd = new System.Data.OleDb.OleDbCommand())
{
    cmd.CommandText = "INSERT INTO TableName (column1, column2, column3) VALUES (@Value1, @Value2, @Value3)";
    cmd.Parameters.AddWithValue("@Value1", this.TextBox1.Text);
    cmd.Parameters.AddWithValue("@Value2", this.TextBox2.Text);
    cmd.Parameters.AddWithValue("@Value3", this.TextBox3.Text);

    conn.Open();
    cmd.ExecuteNonQuery();
}

Generally speaking, using parameters eliminates syntax errors because it makes the command much easier to read in it's string representation.

After using parameters i get this error http://i.imgur.com/iQELbH3.jpg It means : Types of data mismatch in criteria expression — Kiirito, Feb 27 '14 at 08:51

score 0 · Accepted Answer · edited May 23 '17 at 11:50

0

You are missing single quotations in Insert Statement where you are assigning values to columns. Your code is vulnerable so should avoid this here is a useful link. Are Parameters really enough to prevent Sql injections?

System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection();
conn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data source= Z:\Tempesta\Area    Progetto\Area_Progetto_20_02_2014\Area_Progetto_DATA_MAGAZINE\Data_Magazine\Data_Magazine\DB    \DataMG.mdb";
try
{
     System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand();
     cmd.CommandType = System.Data.CommandType.Text;
     cmd.CommandText = "INSERT into Prodotti (Codice,Descrizione,Marchio,Deposito,Note,NumeroProdotti,PrzListinoBase_Aq,PrzListinoBase_Ve,Categoria,Posizione,Disponibilita,QtaVenduta,QtaAcquistata) VALUES('" + this.Codice.Text + "','" +  this.Descr.Text + "','" + this.Marchio.Text + "','" + this.Deposito.Text + "','" +  this.Note.Text + "','" + this.NumProd.Text + "','" + this.PrzListAcq.Text + "','" +  this.PrzListVen.Text + "','" + this.Categ.Text + "','" + this.Posiz.Text + "','" +  this.Disp.Text + "','" + this.QtaVen.Text + "','" + this.QtaAcq.Text + "')";
     conn.Open();
     cmd.Connection = conn;
     cmd.ExecuteNonQuery();
     conn.Close(); 
}
catch(Exception ex)
{
    MessageBox.Show(ex.ToString());
    // MessageBox.Show("Connessione Fallita!");
    conn.Close();
}
finally
{
   conn.Close();
}

edited May 23 '17 at 11:50

Community

1
1

answered Feb 26 '14 at 21:29

Aftab Ahmed

1,727
11
15

first let me fix the issue then second task is concerned he is missing single quotations in the insert statement. – Aftab Ahmed Feb 26 '14 at 21:32
1

Right... but you didn't supply any actual information in your answer. you just say "try this". You also leave his code wide open to sql injection and do not supply any additional comments even in the code to try to explain what you fixed. – Evan L Feb 26 '14 at 21:34
1

The problem is that I alrady tried to put in the single quotations with no success. Im actually trying with the parameters, seems to be more easy. – Kiirito Feb 27 '14 at 08:22
that would be great as parametrized query will save you from sql injection. – Aftab Ahmed Feb 27 '14 at 08:24
After using parameters i get this error (i.imgur.com/iQELbH3.jpg) It means : Types of data mismatch in criteria expression – Kiirito Feb 27 '14 at 09:42
it's a separate issue look at this link how to save and retrieve image data http://www.codeproject.com/Articles/21208/Store-or-Save-images-in-SQL-Server – Aftab Ahmed Feb 27 '14 at 09:47
I dont have images to insert or save in my project. – Kiirito Feb 27 '14 at 09:48
you need to convert your image to bytearray then save it to database – Aftab Ahmed Feb 27 '14 at 09:49
then what is this (i.imgur.com/iQELbH3.jpg)?? i think you are saving jpg if not then what you are doing with this image ?? – Aftab Ahmed Feb 27 '14 at 09:50
Its the screen of the error i get in Visual Studio. – Kiirito Feb 27 '14 at 09:50
I'll tell you more in 4 hours from now since now im off from school, see you later thanks – Kiirito Feb 27 '14 at 09:51
I dont have images to save in my project, all are just text or dates – Kiirito Feb 27 '14 at 13:03
Types of data mismatch in criteria expression – Kiirito Feb 27 '14 at 13:10
try to look at your database columns type. For integers you need to pass value like "+txtbox.Text+" and for string columns you need to pass value like this '"+txtbox.Text+"' – Aftab Ahmed Feb 27 '14 at 13:14
Im using parameters like that : `cmd.Parameters.AddWithValue("@Codice", this.Codice.Text );` No need to use ' ' – Kiirito Feb 27 '14 at 13:16
still needed to use cmd.Parameters.AddWithValue("@Codice", Convert.ToInt32(this.Codice.Text) ); this.Codice.Text will pass value as text so in database if you have number then it will throw an error so try to parse it or convert it to integer – Aftab Ahmed Feb 27 '14 at 13:18
I use Convert.ToInt32 for converting it to TEXT or to NUMERIC? – Kiirito Feb 27 '14 at 13:21
Convert.ToInt32 will convert textbox.text to integer – Aftab Ahmed Feb 27 '14 at 13:22
I should use it in all numeric field? Codice its "text" in my DB, I need to use it? – Kiirito Feb 27 '14 at 13:24
if you have Codice numeric in database then convert it to integer – Aftab Ahmed Feb 27 '14 at 13:25
Nope, Codice its text in my DB , so i dont convert it – Kiirito Feb 27 '14 at 13:25
use convert.toint for only those columns which have numeric (number) data type in database. for rest of the columns us as it is – Aftab Ahmed Feb 27 '14 at 13:26
OK now i get this error : http://puu.sh/7cl3o.png it means :Format of the input string is incorrect – Kiirito Feb 27 '14 at 13:29
use convert.toint for only those values which are numeric or number in table if you will try to save a number in text column usually this error comes up – Aftab Ahmed Feb 27 '14 at 13:35
seems you are converting an empty value to integer ok try this one if (!string.IsNullOrEmpty(textbox.text)) { codic = Convert.ToInt32(textbox.text); } – Aftab Ahmed Feb 27 '14 at 13:43
Got it ! It was because I wasnt writing in all the textboxes ! Thanks so much now it works ! – Kiirito Feb 27 '14 at 13:47

tdoan · Answer 4 · 2014-02-26T21:36:57.093

0

I think you may be missing single quotes around some of your text qualifiers in your INSERT statement.

"INSERT into Prodotti ([Codice],[Descrizione],[Marchio],[Deposito],[Note],[NumeroProdotti],[PrzListinoBase_Aq],[PrzListinoBase_Ve],[Categoria],[Posizione],[Disponibilita],[QtaVenduta],[QtaAcquistata]) VALUES ('" + this.Codice.Text + "','" + this.Descr.Text + "','" + this.Marchio.Text + "','" + this.Deposito.Text + "'," + this.Note.Text + "," + this.NumProd.Text + "," + this.PrzListAcq.Text + "," + this.PrzListVen.Text + ",'" + this.Categ.Text + "','" + this.Posiz.Text + "'," + this.Disp.Text + "," + this.QtaVen.Text + "," + this.QtaAcq.Text + ")";

Consider using a parameterized query rather than building your query string by hand. Not only is it safer, but it can help to weed out these kinds of errors which can be tedious to debug.

eg.

String StrSQL = "INSERT INTO tblLog ([Part_Number],[Quantity],[Date],[LOC_Warehouse],[LOC_Row],[LOC_Section],[LOC_Level],[LOC_Bin],[Stock_Added],[Stock_Removed],[Quarantine_Set],[Quarantine_Removed])"
              + "VALUES(@Part_Number, @Quantity, @Date, @Warehouse, @Row, @Section, @Level, @Bin, @Stock_Added, @Stock_Removed, @Quarantine_Set, @Quarantine_Removed)";
SqlConnection conn = new SqlConnection(WHITS.Properties.Settings.Default.LocalConnStr);
SqlCommand cmd = new SqlCommand(StrSQL, conn);
cmd.Parameters.AddWithValue("@Part_Number", Part_Number);
cmd.Parameters.AddWithValue("@Quantity", Quantity);
cmd.Parameters.AddWithValue("@Date", DateTime.Now);
//More Parameters... Skipped for brevity.
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();

edited Feb 26 '14 at 21:36

answered Feb 26 '14 at 21:29

tdoan

63
8

`cmd.Parameters.AddWithValue("@Part_Number", Part_Number);` What shall I put in "Part_Number" field? My textbox? (eg. '" + this.Codice.Text + "' ) ? – Kiirito Feb 27 '14 at 08:18
That's just an example of a parameterized query for you. You'd have to modify your SQL string, and create parameters (@Codice, @Descrizione, etc.) in your query. Then you specify the value to assign to those parameters with cmd.Parameters.AddWithValue. So for you the parameters would look like cmd.Parameters.AddWithValue("@Codice", this.Codice.Text); cmd.Parameters.AddWithValue("@Descrizione", this.Descr.Text ); Etc. – tdoan Feb 27 '14 at 12:54
Also note that when you're using parameterized queries you don't have to put single quotes around all of your text like you have in your comment. – tdoan Feb 27 '14 at 12:58
I put the parameters in, I get this error : i.imgur.com/iQELbH3.jpg It means : Types of data mismatch in criteria expression – Kiirito Feb 27 '14 at 13:06
Sounds like you're providing the wrong datatype for one of your parameters. Check the table definition in your database and make sure you're passing the correct type of value for each parameter. Since it looks like you are dealing with text boxes, you may have to cast (convert) your values to the right format. For example, if numProd is supposed to be a numerical value, you should be passing an integer as the parameter, not the text equivalent. – tdoan Feb 27 '14 at 13:40
Im using now Convert.ToInt32 to numeric fields but i get this error now : puu.sh/7cl3o.png it means :Format of the input string is incorrect – Kiirito Feb 27 '14 at 13:43
One of your conversions is not converting cleanly to an integer - and this error is getting thrown as a result. – tdoan Feb 27 '14 at 15:36

John · Answer 5 · 2014-02-26T21:57:51.850

0

Open your connection earlier. Also, use "using". Here's how I would do it:

try
{
    string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data source= Z:\Tempesta\Area Progetto\Area_Progetto_20_02_2014\Area_Progetto_DATA_MAGAZINE\Data_Magazine\Data_Magazine\DB\DataMG.mdb";

    using (System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(connectionString))
    {
        conn.Open();
        string insertQuery = "INSERT into Prodotti ([Codice],[Descrizione],[Marchio],[Deposito],[Note],[NumeroProdotti],[PrzListinoBase_Aq],[PrzListinoBase_Ve],[Categoria],[Posizione],[Disponibilita],[QtaVenduta],[QtaAcquistata]) VALUES ('" + this.Codice.Text + "','" + this.Descr.Text + "','" + this.Marchio.Text + "','" + this.Deposito.Text + "'," + this.Note.Text + "," + this.NumProd.Text + "," + this.PrzListAcq.Text + "," + this.PrzListVen.Text + ",'" + this.Categ.Text + "','" + this.Posiz.Text + "'," + this.Disp.Text + "," + this.QtaVen.Text + "," + this.QtaAcq.Text + ")";
        System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(insertQuery, conn);
        cmd.CommandType = System.Data.CommandType.Text;
        cmd.ExecuteNonQuery();
        conn.Close(); 
    }
}

Edit: My bad... the code I was referencing was filling a DataAdapter, which doesn't require a call to connection.Open(). Regular querying does. My apologies... I have edited my suggestion.

edited Feb 26 '14 at 21:57

answered Feb 26 '14 at 21:31

John

186
5

Your code is wide open to SQL Injection... Also you don't open your connection after you instantiate the code... this will throw an error. – Evan L Feb 26 '14 at 21:32
Yes, the way he is doing his insert is not recommended for cases where SQL injection is an issue... I wasn't addressing that. I was just showing how I would execute the command. The methodology I showed above does not throw any errors... I use it all the time. – John Feb 26 '14 at 21:35
you declare `conn` in the using statment, but you never call `conn.Open()`... it will error at `cmd.ExecuteNonQuery()`. – Evan L Feb 26 '14 at 21:37
conn is opened automatically in the using statement. Also, you don't really need the `conn.Close();`, because the connection is automatically closed and cleaned up when the code exits the using block. I don't need to try it, I run code just like this every day. – John Feb 26 '14 at 21:39
After using parameters i get this error i.imgur.com/iQELbH3.jpg It means : Types of data mismatch in criteria expression – Kiirito Feb 27 '14 at 09:43

Insert record(s) DB from Form

5 Answers5