Error in writing of date&Time function in VB.NET to Access DataBase

Question

0.00/5 (No votes)

See more:

Hai All,

This is the code i am using to write the data to Access Table An data Mismatch error has been occured in runtime.

Any one plzzzzzzzzz Help me.......................

SQL

cmdInsert.CommandText = "INSERT INTO SMSDATA1DB (Start_Of_Packet, Packet_Identifier,Soft_Version,Site_ID,Date_Time,Alarm_String,Solar_Voltage,Solar_Current,Solar_Avg_PF,Solar_Inst_Energy,Solar_Cum_Energy,Load_Voltage,Load_Current,Load_Avg_PF,Load_Inst_Energy,Load_Cumm_Energy,Grid_Voltage,Grid_Current,Grid_Inst_Energy,Grid_Cum_Energy,Batt_Voltage,Batt_Charg_Current,Batt_Charg_Energy,Batt_Disch_Current,Batt_Disch_Energy,Status_String,End_Of_String) VALUES ('" & STX1 & "', '" & STX2 & "', '" & STX3 & "', '" & STX4 & "', '" & STX5 & "' , '" & result1 & "', '" & STX7 & "', '" & STX8 & "', '" & STX9 & "', '" & STX25 & "', '" & STX10 & "', '" & STX11 & "', '" & STX12 & "', '" & STX13 & "', '" & STX26 & "', '" & STX14 & "', '" & STX15 & "', '" & STX16 & "', '" & STX27 & "', '" & STX17 & "', '" & STX18 & "', '" & STX19 & "', '" & STX20 & "', '" & STX21 & "', '" & STX22 & "', '" & result2 & "', '" & STX24 & "')"
            MsgBox(cmdInsert.CommandText)
            cmdInsert.CommandType = CommandType.Text
            cmdInsert.Connection = cnnOLEDB
            cmdInsert.ExecuteNonQuery()
            cmdInsert.Dispose()

The Date&Time Function is Date_Time and the value of that is stored in STX5 string

Posted 2-Dec-11 19:50pm

Himachandra

Updated 2-Dec-11 19:53pm

Wendelius

v2

Add a Solution

Comments

NikulDarji 3-Dec-11 2:29am

Check the datatype of access database set it "Text"....

2 solutions

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 · 2011-12-02T20:42:00

For goodness sake! There are so many things wrong here, I'll just go for the big ones:

1) Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead
2) Do not use such similar names for your text fields. You said it yourself: "The Date&Time Function is Date_Time and the value of that is stored in STX5 string" - You have to tell us, because the names do not help anyone. If you used STXDateTime or similar, we would not need to be told - and in two weeks time, you wouldn't have to look through your code to work it out either, because you will have forgotten. Purely for maintenance, and readability, and reliability, change to sensible names!

The first one will probably help to solve your problem: the string form of the Date and time is almost certianbly in your local format, rather than ISO which the database is expecting. If you feed it 13/01/2009 14:59 and it expects yyyy-MM-dd hh:mm:ss then it will cause a data mismatch. Keep your dates and times in DateTime objects instead of strings, and pass them (and everything else) through as parameters - your code will be more readable, and your problem will go away.

VB

cmdInsert.CommandText = "INSERT INTO SMSDATA1DB (Start_Of_Packet, Packet_Identifier,Soft_Version,...) VALUES (@SOP, @PI, @SV,...)"
cmdInsert.Parameters.AddWithValue("@SOP", STX1)
cmdInsert.Parameters.AddWithValue("@PI", STX2)
cmdInsert.Parameters.AddWithValue("@SV, STX3)
...

Scubapro · Answer 2 · 2011-12-03T01:29:00

Solution 2

Use the Format fuction in your SQL and set it to your local format:

VB

"SELECT... ,FORMAT(STX5,'DD-MM-YYYY'), ..."

Posted 3-Dec-11 1:29am

Scubapro