Incorrect date is storing in ms access db.(something like 01-07-1894 00:00:00 even when I am saving 12-03-2017)

Question

0.00/5 (No votes)

See more:

I am Developing Window Based Application Through vb.net but Each Time i am saving the date in access ms db,it is saving the date like 01-07-1894 00:00:00 type even When I am saving 12-03-2017 from Date Time Picker...
I tried selecting different dates and each time i am getting the date been stored in db like "**-**-1894"
I redesigned form controls,reinstalled software's still i am unlucky..
Please somebody Help Me...!!
Thanks in Advance..........!!

What I have tried:

Try
Dim ds As New DataSet()
cn = New OleDbConnection(str)
cn.Open()
cmd = New OleDbCommand("select * from cus_reg_table", cn)
Dim da As New OleDbDataAdapter(cmd)
cmd = New OleDbCommand("insert into cus_reg_table(cid,cdate,cname,caddr,cmob) values(" + cnumLabel.Text + "," + DateTimePicker1.Text + ",'" + cnameTextBox.Text + "','" + addressTextBox.Text + "'," + cmobnoTextBox.Text + ") ", cn)
cmd.ExecuteNonQuery()
MsgBox("Record added successfully.....")
cn.Close()
Catch ex As Exception
MsgBox(ex.Message.ToString)
End Try

Posted 11-Mar-17 18:15pm

Member 12996616

Updated 11-Mar-17 19:51pm

v3

Add a Solution

Comments

Bryian Tan 12-Mar-17 0:27am

missing a single quote?

",'" + DateTimePicker1.Text + "','"

Member 12996616 12-Mar-17 0:31am

but what if i use a single quote then it will store my date as string no..???
Later i dont want problems while retrieving them and comparing dates...

Member 12996616 12-Mar-17 0:36am

as "cdate" datatype i am using in access db is "Date/Time"..
I think if i use '"+DateTimePicker1.text+"' or '"+DateTimePicker.Value.Date+"' then it will store my date as string/Text and not as a Date itself..but i am not sure with my thinking...

Bryian Tan 12-Mar-17 0:58am

Maybe # instead of '. Think of it this way, If you going to write a query from the Access database to insert the data , how the query will look like?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2017-03-11T19:51:00

Stop doing it like that: never concatenate strings to form an SQL command as it both causes problems like this, and leaves you wide open to SQL Injection, which can damage or destroy your database. Always use Parameterised queries.

Change your code:

VB

cmd = New OleDbCommand("INSERT INTO cus_reg_table (cid,cdate,cname,caddr,cmob) VALUES (@CID, @CDT, @CNM, @CAD, @CMO)"
cmd.Parameters.AddWithValue("CID", cnumLabel.Text)
cmd.Parameters.AddWithValue("CDT", DateTimePicker1.Value)
cmd.Parameters.AddWithValue("CNM", cnameTextBox.Text)
cmd.Parameters.AddWithValue("CAD", addressTextBox.Text)
cmd.Parameters.AddWithValue("CMO", cmobnoTextBox.Text)
cmd.ExecuteNonQuery()

The chances are that will fix your problem at the same time.