Inserting new records with dates problem

[themaj]

I am trying to insert new records into an Access DB and getting some weird stuff.
I get the data from a DBF table and load that into a dataset table and DataGridView with no problems.
Then I loop through the ds.table and insert it into the DB with this query.

cmd.CommandText = "INSERT INTO GRINDITEMS (MODE, PERIOD, lngHOUR, lngMINUTE, DOB, SYSDATE)" & _
                                    " VALUES (" & _
                                    ds.Tables(0).Rows(iRows).Item(1) & "," & _
                                    ds.Tables(0).Rows(iRows).Item(2) & "," & _
                                    ds.Tables(0).Rows(iRows).Item(3) & "," & _
                                    ds.Tables(0).Rows(iRows).Item(4) & "," & _
                                    ds.Tables(0).Rows(iRows).Item(5) & "," & _
                                    ds.Tables(0).Rows(iRows).Item(6) & ")"

                cmd.ExecuteNonQuery()

All the columns are fine except the date columns (DOB and SYSDATE); they take a date like '8/01/2012' from the DBF and it reads in the DataSet Table as '8/01/2012' (as evidenced in a DataGridView) but it ends up being written to the Access table as '12-30-1899'. Note those columns in the DB are set as Date/Time with ShortDate format.
If I change the DB column DataType to 'text' is write as '3.97614314115308E-03'!!!

I've tried a bunch of formatting tricks and that hasn't worked either. Need some help here and hoping someone can tell me where/when (best practice) I should make any Format changes; in the first read from the DBF or when a INSERT to Access DB.

Thanks in advance,

[Pgmer]

Check before cmd.executeNonQuery on

ds.Tables(0).Rows(iRows).Item(5) & "," &                                     
ds.Tables(0).Rows(iRows).Item(6) & "

What ur getting at these items...

[nhbs01]

I get the data from a DBF table and load that into a dataset table and DataGridView with no problems.

[Reverend Jim]

Try putting "#" around the date fields. Access prefers dates in the form

INSERT INTO mytable (SYSDATE) VALUES(#2012-10-31#)

That would make the last two lines of your query

"#" & ds.Tables(0).Rows(iRows).Item(5) & "#," & _
"#" & ds.Tables(0).Rows(iRows).Item(6) & "#)"

If MODE is a text field then you'll also have to put single quotes around it. If this doesn't work then please print out the value of cmd.CommandText and post it here.

/EDIT - I corrected the typo ($ to #) as pointed out in the next post.

[themaj]

That was a quick response from you guys and Jim hit the winning point with the # sign, although I think you had typo with the $ sign on Line 1.

The following works perfectly.

    "#" & ds.Tables(0).Rows(iRows).Item(5) & "#," & _
    "#" & ds.Tables(0).Rows(iRows).Item(6) & "#)"

I'll also note that when I intentionally changed one of the datatypes of these fields to 'Text' while leaving the other at 'DateTime' and they both work fine.

That leaves me with the question of the benefits of either datatype in the Access table. Would it be field size or is there an inherent property of the DateTime datatype that ensures that only legitimate dates can be accepted? Eventually this db will have almost 1 million records so size is important as well as any aspects of data validation. These 2 fields will also be significant in future queries.

I'd appreciate comments on that before I close this case.

Thanks again Jim

[Reverend Jim]

Righto about the typo. I corrected it. If you store dates as strings instead of native date values then you can't use the SQL date functions in queries. Also, as you point out, dates can be stored n less space than the equivalent strings. Comparisons will not work depending on how you format the dates if stored as strings. For example

SELECT * FROM myTable WHERE thedate > GETDATE()

GETDATE() returns the current date. The query doesn't work if "thedate" is not a DATE value. If you really insist on storing dates as strings then comparing two date fields will be meaningless if your format isn't YYYY-MM-DD or YYYY/MM/DD or any format where year is first, followed by month, then day and month and day are always two digits.

[themaj]

Jim, glad you brought that up because there's no reason or preference for me to store the dates as text values; I'd prefer storing as DateTime. I'm just trying to figure the how and when to convert those values.

The date (DOB) matches the folder name as in '20120901' and I already convert that for a variable as a LongInt in several Subs. I could use a Function to convert to DateTime but again it's the when/where to do it.
Should I do it initially right after I do the DataAdapter.Fill statement, which is followed by a DataGridView.DataSource?
Should I do it as I write to the Access table?

I'm assuming (dangerously) that I can use a Function in my INSERT statement. At least we could in VB6.

As I mentioned to you in other posts, I'm all for learning Best Practices....fire away my friend :)

[Reverend Jim]

Should I do it initially right after I do the DataAdapter.Fill statement, which is followed by a DataGridView.DataSource?

I'm afraid I can't help you with that part. I have no experience using DataAdapters. All my experience is with ADO.net. I also used dates as folder (or file) names or prefices, however I used the format YYYY-MM-DD so the translation to date (because our system date format was also YYYY-MM-DD) was trivial.

[themaj]

Now I'm back to a similar problem when trying to INSERT records that have several non-alphanumeric characters.

cmd.CommandText = "INSERT INTO STOREITEMS (ITEMID, STOREID ,LONGNAME, ROUTING) " & _
                  "VALUES (" & _
                   ds.Tables(0).Rows(iRows).Item(0) & "," & _
                   ds.Tables(0).Rows(iRows).Item(1) & "," & _
                   "'" & Trim(ds.Tables(0).Rows(iRows).Item(4)) & "' ," & _
                    ds.Tables(0).Rows(iRows).Item(5) & ") "

This code give me the error:
Syntax error (missing operator) in query expression ''Shepard's Pie' ,1)'

The first 2 fields above are integers; no problem. The 3rd field is text and it works fine until it hits a text string that contains some non-alphanumeric character like an apostrophe, or a greater/less than <>, or others. Is there another operator or some similar mark like the '#' that I should be using for strings?

I find it hard to believe that I can't save strings like O'Neil, O'Connor or even Momma's Lasagna because of the apostrophe.

Please help

[Reverend Jim]

Before you do the insert you have to replace all single quotes with two single quotes. In other words, instead of

INSERT INTO mytable (name) VALUES('O'Neal')

you have to use

INSERT INTO mytable (name) VALUES('O''Neal')

Just like if you wanted to have a string with a " in it, you have to double up

Dim s As String = "then I said, ""too bad"""

to get the string

then I said, "too bad"

so use

Replace(ds.Tables(0).Rows(iRows).Item(0),"'","''")

It's ugly but it works.

[themaj]

Thanks Jim, all makes sense...kinda....all the O'Neils will be happy:)

But how the heck do I handle items that have other non-alphanumeric characters like
' <<LUNCH>> '

Unfortunately I'm seeing a bunch of these in the DBF database.

[themaj]

It seems that I need to invest some time in Parametrized queries. Apparently that can avoid all these problems. Any thoughts or experience on this?

[Reverend Jim]

I know enough about them to recommend that they be used but I've never really used them. Between db apps that I've written for my own use (for which the added security isn't required) and db apps that didn't need a user interface, I've never had the need to use parameterized entry. There are several examples on this forum.