Problems by inserting values from textboxes

Question

I'm trying to insert the current date to the database and i allways get the message(when i press the button on the form to save to my access database), that the data type is incorect in the conditional expression. the code:

string conString = "Provider=Microsoft.Jet.OLEDB.4.0;"
         + "Data Source=C:\\Users\\Simon\\Desktop\\save.mdb";

            OleDbConnection empConnection = new OleDbConnection(conString);


            string insertStatement = "INSERT INTO obroki_save "
                                 + "([ID_uporabnika],[ID_zivila],[skupaj_kalorij]) "
                                 + "VALUES (@ID_uporabnika,@ID_zivila,@skupaj_kalorij)";

            OleDbCommand insertCommand = new OleDbCommand(insertStatement, empConnection);

            insertCommand.Parameters.Add("@ID_uporabnika", OleDbType.Char).Value = users.iDTextBox.Text;
                            insertCommand.Parameters.Add("@ID_zivila", OleDbType.Char).Value = iDTextBox.Text;
            insertCommand.Parameters.Add("@skupaj_kalorij", OleDbType.Char).Value = textBox1.Text;
            empConnection.Open();

            try
            {
               int count = insertCommand.ExecuteNonQuery();
            }
            catch (OleDbException ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                empConnection.Close();
                textBox1.Clear();
                textBox2.Clear();
                textBox3.Clear();
                textBox4.Clear();
                textBox5.Clear();
            }

I have now cut out the date,( i made access paste the date ), still there is the same problem. Is the first line ok? users.idtextbox.text? Please help !

Answer 1

try Changing

insertCommand.Parameters.Add("@datum", OleDbType.Char).Value = DateTime.Now;

to

 `insertCommand.Parameters.Add("@datum", OleDbType.Char).Value 
      = DateTime.Now.ToString("dd MMM yyyy HH:mm");`

( or some other acceptable date format)

Answer 2

Assuming that your database actually holds dates, and not strings, for the date column - I think you're trying to insert char values into your date column.

Change

insertCommand.Parameters.Add("@datum", OleDbType.Char).Value = DateTime.Now;

to

insertCommand.Parameters.Add("@datum", OleDbType.Date).Value = DateTime.Now;

Answer 3

You should use the type OleDbType.Date in the parameter. That maps to a DateTime value in .NET and an OLE date (internally represented as double) in the database. That's what Access uses for storing dates.

With any of the other date formats in OleDbType, it will be converted to a string representation that Access doesn't recognise as a date.

If you use OleDbType.Char the DateTime value will be converted to a string using the current culture in .NET, which may or may not be understood by Access depending on what the culture happens to be. In your case it seems that it was not understood.

Answer 4

I think you are getting issue due to the date format set in your regional settings and the date format accepted by MS Access.

You can use a specific date format as mentioned in following code.

 DateTimeFormatInfo ukDTFomat = new CultureInfo("en-GB", false).DateTimeFormat;
 DateTime.Now.ToString("yyyy-MM-dd", ukDTFomat);

Answer 5

You can simplify your OleDb usage by adding parameters like this:

OleDbParameter param = new OleDbParameter("@datum", DateTime.Now);
command.Parameters.Add(param);

This uses the override of the OleDbParameter that takes a string (the parameter name) and an object (whatever the value should be for the parameter). This lets OleDb figure out the correct parameter type for you (and saves you a lot of work typing and changing parameter types).

Update: just noticed this is Jet. All bets are off when it comes to Access, so I'm by no means sure the above will work.

Answer 6

Are you sure the problem is the date value? I.e., are the other columns really of type Char? For date values, you should use OleDbType.DBDate but you should also use the appropriate type for the other columns.

EDIT Tested against an Access database with a date column, the following worked (although stripped the time obviously):

cmd.Parameters.Add( "@DateColumn", OleDbType.DBDate ).Value = DateTime.Now;

As well as (which included the time)

cmd.Parameters.Add( "@TestDate", OleDbType.Date ).Value = DateTime.Now;

EDIT As with the date parameters, you should be passing specific types for the other values:

insertCommand.Parameters.Add("@ID_uporabnika", OleDbType.Integer).Value = int.Parse( users.iDTextBox.Text );
insertCommand.Parameters.Add("@ID_zivila", OleDbType.Integer.Value = int.Parse( iDTextBox.Text );
insertCommand.Parameters.Add("@skupaj_kalorij", OleDbType.Integer).Value = int.Parse( textBox1.Text );