Insert DateTime into FoxPro Database

OK Experts...here is a good one for you!
I have a VB.net program that I'm getting the date and time from a log file and want to place this information into a FoxPro database.

I've created the FoxPro database and I'm able to manually enter in a date and time by typing in the date then moving with the arrows on the key board over two times then entering in the date...however when I try to do this in my VB.net program I get the date just fine but the time does NOT Show up correctly I get the default time of 12:00:00.

Here is my insert statement:
MyFoxProAdapter.Insert(sDate & "  " & sTime, "0510", "smithmrk", "123", "456")

I've tried to just put sDate & sTime but that doesn't work either...I've tried ctype(sdate & sTime, datetime) but that doesn't work either.

See the attached screen shot of my results.

Thanks,
Mark
ScreenShot.jpg
smithmrkAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Olaf DoschkeSoftware DeveloperCommented:
I don't know MyFoxProAdapter and what parameters it's Insert function has. Nobody here will know that. Typically for VB.NET would be using a OledbCommand and for VB6 or earlier using VFPOLEDB.1 Provider, then you can

INSERT INTO sometable (datetimefield) VALUES (Datetime(Y,M,D,H,M,S))

Datetime takes the parameters in numeric form, so either you build a sql string with the DATETIME call or you parameterize it and pass in int parameters.

Another solution is to use a datetime literal in the form of

INSERT INTO sometable (datetimefield) VALUES ( {^yyyy-mm-dd hh:mm:ss} )

Where the leters each stand for a digit of the year, month, day etc...

General OLEDB sample usages are shown here, eg more details about how to pass in such an INSERT SQL: http://msdn.microsoft.com/en-US/library/3haz2895%28v=vs.80%29.aspx

That said, again: I don't know anything of a MyFoxProAdapter object, you have to tell us where that comes from and what it is and how it works, before we can help you with that.

Bye, Olaf.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
smithmrkAuthor Commented:
OK first of all THANK YOU for your suggestions above...I'll give them a try!
Second, sorry I didn't provide more information on my GUI (wizard) TableAdapter...so I put together some more screen shots to show you how the MyFoxProAdapater is setup.

Thought this might be able to help you tell me why I can't pass in the date and time in the correct format to the table.

See attached PDF...let me know if you need any additional information.

Thanks,
Mark
GUI-TableAdapter.pdf
0
smithmrkAuthor Commented:
I FOUND IT!!!
Believe it or not I found my issue by Google on a post you had on a different Web Site!

http://www.tek-tips.com/viewthread.cfm?qid=1679894

This is what I need to change to get it to work:
MyFoxProAdapter.Insert(Format(Now, "yyyy-MM-ddThh:mm:ss"), "0510", "smithmrk", "123", "456")

I needed that "T" in the middle!
Thanks,
Mark
0
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

smithmrkAuthor Commented:
THANK YOU!!!!

Mark
0
Olaf DoschkeSoftware DeveloperCommented:
OK, I see you have a parameterized insert into query, The VB.NET system.DateTime type should also do then. It much depends on what driver you use.

But good to know this format also works. It's rather an XML or ODBC datetime format than VFP, but then this is perhaps more important than how VFP needs it.

If NOT parameterizing that part of the query, you could instead pass in the VFP literal date type or DATEIMTE() function call, it will then be executed by VFP ODBC or OLEDB. You have to use one or the other from VB.NET to DBF, that's still unclear from your screenshot.

In detail, if you change your query string to INSERT INTO td_audit_logs (chgdate, client_num, username, old_acct, new_acct) VALUES (DateTime(1999,12,31,23,59,59), ? , ? , ?, ?) and remove the datetime parameter from your parameter collection and call, you would also get the datetime over via changing the command string itself.

So in short: while that works for this case, sometimes NOT making a value a parameter and instead use the remote databases syntax or functions can also work. I overcame similar problems with MySQL Bigint or datetime types. Datetime often is a problem, every database brews it's own specific syntax and behaviour with this type.

And since  you want "Now" in that field, it's even shorter:INSERT INTO td_audit_logs (chgdate, client_num, username, old_acct, new_acct) VALUES (DateTime(), ? , ? , ?, ?), without any parameter vlaues DateTime() returns Now. Also you may make that a default value for that DBF field and then shorten your insert to INSERT INTO td_audit_logs (client_num, username, old_acct, new_acct) VALUES (?, ? , ?, ?), skipping the datatime field.

Bye, Olaf.
0
smithmrkAuthor Commented:
Thanks for the update!
My connection string is:

connectionString="Provider=VFPOLEDB.1;

Not sure if that changes anything.

Thanks again,
Mark
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.