CODEWITHSUNDEEP
c#ms-accessdatetimepicker
Nisarg Dongare
Nisarg Dongare

Reputation: 11

how to insert date(long format) into access database using datetimepicker in c# ? (error is in date part only)

Error image is here
the error is in query line , its shows syntax error

try {

    string zero = "0";
    DateTime dat = this.dateTimePicker1.Value.Date;
    connection1.Open();
    OleDbCommand command = new OleDbCommand();
    command.Connection = connection1;
    command.CommandText = "insert into client_table(CLIENT, DATE,BILL_AMOUNT, PAID_AMOUNT, BALANCE, CONTACT, ADDRESS )VALUES ('" + txt_client.Text + "', #" + dat.ToLongDateString() + "# ,'" + zero + "','" + zero + "','" + zero + "','" + txt_contact.Text + "','" + txt_address.Text + "')";

    command.ExecuteNonQuery();
    connection1.Close();
    MessageBox.Show("New Client Registration done Successfully.");
    connection1.Dispose();
    this.Hide();
    employee_form f1 = new employee_form("");
    f1.ShowDialog();
}

thank you in advance

Upvotes: 1

Views: 3239

Answers (2)

Nisarg Dongare
Nisarg Dongare

Reputation: 11

//code to write date in the access table.

string zero = "0";
DateTime dat = this.dateTimePicker1.Value.Date;
//MessageBox.Show(dat.ToShortDateString());
connection1.Open();
OleDbCommand command = new OleDbCommand();
command.Connection = connection1;
//command.CommandText = "insert into client_table(DATEE) values( '"dat.ToShortDateString()+"')";
command.CommandText = "insert into client_table (CLIENT, DATEE, BILL_AMOUNT, PAID_AMOUNT, BALANCE, CONTACT, ADDRESS )VALUES ('" + txt_client.Text + "', #"+dat.ToShortDateString()+"# ,'" + zero + "','" + zero + "','" + zero + "','" + txt_contact.Text + "','" + txt_address.Text + "')";
command.ExecuteNonQuery();
connection1.Close();
MessageBox.Show("New Client Registration done Successfully.");
connection1.Dispose();

//New code for receiving the date between two range of dates

try
  {
   DateTime dat = this.dateTimePicker1.Value.Date;
   DateTime dat2 = this.dateTimePicker2.Value.Date;
   // MessageBox.Show(dat.ToShortDateString() + "  " + dat2.ToShortDateString());
   connection1.Open();
   OleDbCommand command = new OleDbCommand();
   command.Connection = connection1;
   string query;
   query = "select * from client_table Where DATEE Between #" + dat.ToLongDateString() +"# and #" + dat2.ToLongDateString() + "# ";
   command.CommandText = query;
   OleDbDataAdapter da = new OleDbDataAdapter(command);
   DataTable dt = new DataTable();
   da.Fill(dt);
   dataGridView1.DataSource = dt;
   connection1.Close();
   }
 catch (Exception ex)
   {
     MessageBox.Show("Error" + ex);
   }

Thank you all of you for the support.

Upvotes: 0

Crowcoder
Crowcoder

Reputation: 11514

In Access, dates are delimited by #, not '. Also, Access does not recognize the long date format. But dates are not stored in any format so no worries, change it to:

... + "', #" + dat.ToString() + "# ...etc.

Although if you do not parameterize your query serious damage or data exposure can be done through SQL Injection because someone could type in a SQL statement into one of those textboxes that you are implicitly trusting.

Working example:

class Program
{
    static void Main(string[] args)
    {
        System.Data.OleDb.OleDbConnectionStringBuilder bldr = new System.Data.OleDb.OleDbConnectionStringBuilder();
        bldr.DataSource = @"C:\Users\tekhe\Documents\Database2.mdb";
        bldr.Provider = "Microsoft.Jet.OLEDB.4.0";

        using (System.Data.OleDb.OleDbConnection cnxn = new System.Data.OleDb.OleDbConnection(bldr.ConnectionString))
        {
            cnxn.Open();
            Console.WriteLine("open");

            using (System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand())
            {
                cmd.Connection = cnxn;
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = "INSERT INTO [Table1] ([Dob]) VALUES(#" + DateTime.Now.ToString() + "#)";
                cmd.ExecuteNonQuery();
            }
        }
        Console.ReadKey();
    }
}

enter image description here

Update

However, you want to do something more like this which uses Parameters to protect against SQL Injection which is extremely easy to exploit so do not think that you don't really need to worry about it:

static void Main(string[] args)
{
    OleDbConnectionStringBuilder bldr = new OleDbConnectionStringBuilder();
    bldr.DataSource = @"C:\Users\tekhe\Documents\Database2.mdb";
    bldr.Provider = "Microsoft.Jet.OLEDB.4.0";

    using (System.Data.OleDb.OleDbConnection cnxn = new OleDbConnection(bldr.ConnectionString))
    {
        cnxn.Open();
        Console.WriteLine("open");

        using (System.Data.OleDb.OleDbCommand cmd = new OleDbCommand())
        {
            cmd.Connection = cnxn;
            cmd.CommandType = System.Data.CommandType.Text;
            OleDbParameter dobParam = new OleDbParameter("@dob", OleDbType.Date);
            dobParam.Value = DateTime.Now;
            cmd.Parameters.Add(dobParam);
            cmd.CommandText = "INSERT INTO [Table1] ([Dob]) VALUES(@dob)";
            cmd.ExecuteNonQuery();
        }
    }
    Console.ReadKey();
}

Upvotes: 3

Related Questions