Convert VB.NET Date to String in Access Database-VBForums
Results 1 to 13 of 13

Thread: Convert VB.NET Date to String in Access Database

  1. #1

    Thread Starter
    New Member
    Join Date
    Jul 2010
    Posts
    13

    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

  2. #2
    Fanatic Member ThomasJohnsen's Avatar
    Join Date
    Jul 2010
    Location
    Denmark
    Posts
    528

    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)

  3. #3

    Thread Starter
    New Member
    Join Date
    Jul 2010
    Posts
    13

    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?

  4. #4
    Hyperactive Member
    Join Date
    Dec 2007
    Location
    Somewhere else today
    Posts
    355

    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.

  5. #5

    Thread Starter
    New Member
    Join Date
    Jul 2010
    Posts
    13

    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?

  6. #6
    Hyperactive Member
    Join Date
    Dec 2007
    Location
    Somewhere else today
    Posts
    355

    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.

  7. #7

    Thread Starter
    New Member
    Join Date
    Jul 2010
    Posts
    13

    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.

  8. #8
    PowerPoster
    Join Date
    May 2002
    Posts
    25,155

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  9. #9

    Thread Starter
    New Member
    Join Date
    Jul 2010
    Posts
    13

    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.

  10. #10
    PowerPoster
    Join Date
    May 2002
    Posts
    25,155

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  11. #11

    Thread Starter
    New Member
    Join Date
    Jul 2010
    Posts
    13

    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.

  12. #12
    PowerPoster
    Join Date
    May 2002
    Posts
    25,155

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  13. #13
    Fanatic Member ThomasJohnsen's Avatar
    Join Date
    Jul 2010
    Location
    Denmark
    Posts
    528

    Re: Convert VB.NET Date to String in Access Database

    Quote Originally Posted by dabeastro View Post
    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
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.