-
Jul 26th, 2010, 04:31 PM
#1
Thread Starter
New Member
Convert VB.NET Date to String in Access Database
So I am attempting to use the DateTimePicker and then add a day to each iteration in a For/Next loop. That seems to work fine and I can verify with msgbox that it is happening. However, I also need to write the date in string format to an Access database using INSERT INTO. However, when the code is ran, I get a "syntax error (missing operator)". Code is below. Can someone help please? I have tried all I know to do. (Please disregard the additional values in INSERT TO, I have verified they are working properly)
Dim dayadd As Date
dayadd = DateTimePicker1.Value
For x = 0 To txtdays
dayadd = dayadd.AddDays(1).Date
Dim dayadd2 As String = dayadd.ToString("D")
sql = "INSERT INTO accessTest (Date1, BaseTreat, BaseWaste, BaseIrr, BaseCharge, BaseWaterCost, BaseWasteCost) VALUES(" & dayadd2 & "," & basetreat & "," & basewaste & "," & baseirr & ",'five','six','seven')"
objCmd = New OleDb.OleDbCommand(sql, Con)
objCmd.ExecuteNonQuery()
Next
-
Jul 26th, 2010, 05:27 PM
#2
Re: Convert VB.NET Date to String in Access Database
Dates need to be enclosed in #s when used in SQL.
INSERT INTO <> VALUES (#date#, 'String', 12)
Tom
In truth, a mature man who uses hair-oil, unless medicinally , that man has probably got a quoggy spot in him somewhere. As a general rule, he can't amount to much in his totality. (Melville: Moby Dick)
-
Jul 26th, 2010, 05:37 PM
#3
Thread Starter
New Member
Re: Convert VB.NET Date to String in Access Database
Thank you for the reply. I change my SQL line to:
sql = "INSERT INTO accessTest (Date1, BaseTreat, BaseWaste, BaseIrr, BaseCharge, BaseWaterCost, BaseWasteCost) VALUES(#dayadd2#," & basetreat & "," & basewaste & "," & baseirr & ",'five','six','seven')"
However, I now get 'Syntax error in date in query expression '#dayadd2#'.' at my execute line. I also tried the date without conversion to string as variable 'dayadd'. Any ideas?
-
Jul 26th, 2010, 06:13 PM
#4
Hyperactive Member
Re: Convert VB.NET Date to String in Access Database
You do not need to put "" or & around the VALUE variables. So try:
Code:
sql = "INSERT INTO accessTest (Date1, BaseTreat, BaseWaste, BaseIrr, BaseCharge, BaseWaterCost, BaseWasteCost) VALUES(dayadd2, basetreat, basewaste, baseirr, 'five','six','seven')"
Computerman
It was much easier in VB6, but I am now liking Vb.Net alot more.
-
Jul 26th, 2010, 06:26 PM
#5
Thread Starter
New Member
Re: Convert VB.NET Date to String in Access Database
Hmm... I tried to remove the &s and 's...but then I get warnings about missing parameters. I think it is correct the way I have it. basetreat, basewaste, etc. are variables themselves and are represented elsewhere in the code by:
Dim basetreat As Double = pop * demand
Dim basewaste As Double = basetreat - (basetreat * consume / 100)
Dim baseirr As Double = pop * irrigate
Perhaps there is a problem with how I have defined my Access table? I am using:
oQuery = "CREATE TABLE accessTest ( ID Counter," & _
"Date1 TEXT(50) NOT NULL, BaseTreat TEXT(50) NOT NULL, BaseWaste TEXT(50) NOT NULL, BaseIrr TEXT(50) NOT NULL, BaseCharge TEXT(50) NOT NULL, BaseWaterCost TEXT(50) NOT NULL, BaseWasteCost TEXT(50) NOT NULL," & _
"PRIMARY KEY(ID) )"
Is TEXT(50) inappropriate for adding a date to the field?
-
Jul 26th, 2010, 06:46 PM
#6
Hyperactive Member
Re: Convert VB.NET Date to String in Access Database
It depends on how you want to store the date. Is it in a short format or long. Adjust the length to suit. Better still why don't you have the date field set to a date format.
Computerman
It was much easier in VB6, but I am now liking Vb.Net alot more.
-
Jul 26th, 2010, 06:50 PM
#7
Thread Starter
New Member
Re: Convert VB.NET Date to String in Access Database
It just needs mm/dd/yyy. I would like to set the field to to a date format. But I really don't know how....I am new to using SQL and Access together and I am just beginning to learn about parameters. Am I headed in the right direction? Could you please point out how to set fields? I thought it was happening in my create table function....but I do not know the proper tags.... Thanks for your help.
-
Jul 26th, 2010, 06:55 PM
#8
Re: Convert VB.NET Date to String in Access Database
ARGH! GERGLE SNORT!
That's my brain blowing a fuse.
1) There is no good reason to store dates in a text field. There is a DATETIME data type FOR A REASON! Use it.
2) Taking the original code, adding in the solution from post #2, should have resulted in this:
sql = "INSERT INTO accessTest (Date1, BaseTreat, BaseWaste, BaseIrr, BaseCharge, BaseWaterCost, BaseWasteCost) VALUES(#" & dayadd2 & "#," & basetreat & "," & basewaste & "," & baseirr & ",'five','six','seven')"
3) But since, for what ever reason you decided that it should be string instead of a data, that won't work either. You'll need to treat it like a string and put ' around it.
sql = "INSERT INTO accessTest (Date1, BaseTreat, BaseWaste, BaseIrr, BaseCharge, BaseWaterCost, BaseWasteCost) VALUES(' " & dayadd2 & "' ," & basetreat & "," & basewaste & "," & baseirr & ",'five','six','seven')"
4) computerman - valiant try, but you mived the VB Variables into the SQL statement. Since SQL doesn't know jack about them, the code doesn't work.
-tg
-
Jul 26th, 2010, 07:08 PM
#9
Thread Starter
New Member
Re: Convert VB.NET Date to String in Access Database
You are my hero today. I never really wanted the date as string (hence dayadd and dayadd2) but I found myself floundering about as I did not understand that the #s needed to be included within the quotations. Number 2 solved my problem. Thank you so much. I have one other question though....I would like to make use of DATETIME data type, but I can not figure out how to define it. Should it be in my Create Table routine? When I replace TEXT there, my table does not create properly and I bug out. I apologize for my ignorance. I am coming from VBA and biting off more than I should for a graduate project. Thanks again everyone.
Code below fails to create table. Is DATETIME wrong?
oQuery = "CREATE TABLE accessTest ( ID Counter," & _
"Date1 DATETIME(50) NOT NULL, BaseTreat TEXT(50) NOT NULL, BaseWaste TEXT(50) NOT NULL, BaseIrr TEXT(50) NOT NULL, BaseCharge TEXT(50) NOT NULL, BaseWaterCost TEXT(50) NOT NULL, BaseWasteCost TEXT(50) NOT NULL," & _
"PRIMARY KEY(ID) )"
Last edited by dabeastro; Jul 26th, 2010 at 07:21 PM.
-
Jul 26th, 2010, 07:38 PM
#10
Re: Convert VB.NET Date to String in Access Database
First, I don't use Access, so I don't know if DATETIME or just DATE is correct. Go with DATETIME, and if that doesn't work, try DATE. However, take the (50) off... you only need to define the length for text fields. Date fields have a specific built in size, so you don't need to specify it.
-tg
-
Jul 26th, 2010, 08:05 PM
#11
Thread Starter
New Member
Re: Convert VB.NET Date to String in Access Database
Out of curiosity, what do you use to in place of Access to store 1000s or records with multiple fields? I am restricted in my project because the end users are unknown, but are expected to be using dated machines which may only be up .NET 2.0. (Likely government....) Thanks for all your help.
-
Jul 26th, 2010, 08:40 PM
#12
Re: Convert VB.NET Date to String in Access Database
I use SQL... but I'm dealing with 100's of thousands if not millions of records with all kinds of fields, and about two dozen databases and 3 servers... for smaller things, I use SQL CE (Compact Edition) or for really small things, files. It's not that I don't like Access. It's great for what it is, and will probably work in your case. I just happen to be in a position where I don't use Access.
-tg
-
Jul 27th, 2010, 04:54 AM
#13
Re: Convert VB.NET Date to String in Access Database
Originally Posted by dabeastro
Out of curiosity, what do you use to in place of Access to store 1000s or records with multiple fields? I am restricted in my project because the end users are unknown, but are expected to be using dated machines which may only be up .NET 2.0. (Likely government....) Thanks for all your help.
MySQL is another viable (and free) solution, with an excellent documentation and easily implementable objects.
Tom
In truth, a mature man who uses hair-oil, unless medicinally , that man has probably got a quoggy spot in him somewhere. As a general rule, he can't amount to much in his totality. (Melville: Moby Dick)
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|