Problem inserting record with date field 
Author Message
 Problem inserting record with date field

Hello,

I'm having a problem inserting a record into a table.

I'm using VB6 and Access database.

Here is how I tried to do it:

INSERT INTO STUDENT
    (StudentID, Amount, LogDate, Memo)
VALUES( 3,  18.12, '9/23/2001 8:00:59 PM', 'blah blah blah blah')

This give me a syntax error in the insert into statement.

So, I tried the sql builder to build the sql string and
here is what the sql builder gives me:

INSERT INTO STUDENT
    (StudentID, Amount, LogDate, `Memo`)
VALUES (3, 18.12, { d '2001-09-23' }, 'blah blah blah blah')

If I do a verify sql syntax check, I get an error message:

"ADO error: Malformed GUID. in query expression '{d '2001-09-23'}'.

So, does this mean that sql builder is unable to get it right too?

I'm guessing there is something wrong in the way the date field
is being added, but unable to figure it out.

What is the correct way to insert this record into the table?

Thanks for you help.
Raman.



Fri, 12 Mar 2004 11:30:26 GMT  
 Problem inserting record with date field
On Sun, 23 Sep 2001 20:30:26 -0700, "Ganapathi Raman"

Quote:

>Hello,

>I'm having a problem inserting a record into a table.

>I'm using VB6 and Access database.

>Here is how I tried to do it:

>INSERT INTO STUDENT
>    (StudentID, Amount, LogDate, Memo)
>VALUES( 3,  18.12, '9/23/2001 8:00:59 PM', 'blah blah blah blah')

Date literals must be delimited using hash marks (#):
VALUES( 3,  18.12, #9/23/2001 8:00:59 PM#, 'blah blah blah blah')

HTH,
Bob Barrows
Please reply to the newsgroup. My reply-to address is my "spam trap" and I don't check it very often.



Fri, 12 Mar 2004 20:31:38 GMT  
 Problem inserting record with date field
Hashes are not required. It works fine just the way it is. I copied the code
to an NT 4.0 machine and ran it there and it worked fine. Of course I
converted the mdb file to access 2000.

I still don't know why it did not work on my home machine that is running
Win 2k. I'm going to try a few things tonight and see if it will work.

Thanks for your input.


Quote:
> On Sun, 23 Sep 2001 20:30:26 -0700, "Ganapathi Raman"

> >Hello,

> >I'm having a problem inserting a record into a table.

> >I'm using VB6 and Access database.

> >Here is how I tried to do it:

> >INSERT INTO STUDENT
> >    (StudentID, Amount, LogDate, Memo)
> >VALUES( 3,  18.12, '9/23/2001 8:00:59 PM', 'blah blah blah blah')

> Date literals must be delimited using hash marks (#):
> VALUES( 3,  18.12, #9/23/2001 8:00:59 PM#, 'blah blah blah blah')

> HTH,
> Bob Barrows
> Please reply to the newsgroup. My reply-to address is my "spam trap" and I

don't check it very often.


Sat, 13 Mar 2004 05:55:13 GMT  
 Problem inserting record with date field
If "converted the mdb file to access 2000" means that you now have an
MSDE database, which is really SQL Server, then you're right: hashes
are not required. In SQL Server, you supply dates with
quote-delimiters.

I'm pretty sure though, that Jet databases still require the hashes. I
don't have A2K, so could someone verify what I'm saying here? Did Jet
3.6 eliminate the need to delimit dates with hashes?

Bob Barrows

On Mon, 24 Sep 2001 14:55:13 -0700, "Ganapathi Raman"

Quote:

>Hashes are not required. It works fine just the way it is. I copied the code
>to an NT 4.0 machine and ran it there and it worked fine. Of course I
>converted the mdb file to access 2000.

>I still don't know why it did not work on my home machine that is running
>Win 2k. I'm going to try a few things tonight and see if it will work.

>Thanks for your input.



>> On Sun, 23 Sep 2001 20:30:26 -0700, "Ganapathi Raman"

>> >Hello,

>> >I'm having a problem inserting a record into a table.

>> >I'm using VB6 and Access database.

>> >Here is how I tried to do it:

>> >INSERT INTO STUDENT
>> >    (StudentID, Amount, LogDate, Memo)
>> >VALUES( 3,  18.12, '9/23/2001 8:00:59 PM', 'blah blah blah blah')

>> Date literals must be delimited using hash marks (#):
>> VALUES( 3,  18.12, #9/23/2001 8:00:59 PM#, 'blah blah blah blah')

>> HTH,
>> Bob Barrows
>> Please reply to the newsgroup. My reply-to address is my "spam trap" and I
>don't check it very often.



Sat, 13 Mar 2004 07:32:03 GMT  
 Problem inserting record with date field
In my home machine I don't have MS Access installed. My work machine has
Access 2000 and when I tried to open the database, it prompted me to convert
it and I chose to convert it. After that, the insert statement started to
work. Do I have an MSDE database now? I don't know.

Here is how I connect to the database:

    Set cnAccount = New ADODB.Connection

    With cnAccount
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = "..\TESTDATA.MDB"
        .Open
    End With

I'm going to try a few things tonight and will keep you informed.

Thanks for your help.


Quote:
> If "converted the mdb file to access 2000" means that you now have an
> MSDE database, which is really SQL Server, then you're right: hashes
> are not required. In SQL Server, you supply dates with
> quote-delimiters.

> I'm pretty sure though, that Jet databases still require the hashes. I
> don't have A2K, so could someone verify what I'm saying here? Did Jet
> 3.6 eliminate the need to delimit dates with hashes?

> Bob Barrows

> On Mon, 24 Sep 2001 14:55:13 -0700, "Ganapathi Raman"

> >Hashes are not required. It works fine just the way it is. I copied the
code
> >to an NT 4.0 machine and ran it there and it worked fine. Of course I
> >converted the mdb file to access 2000.

> >I still don't know why it did not work on my home machine that is running
> >Win 2k. I'm going to try a few things tonight and see if it will work.

> >Thanks for your input.



> >> On Sun, 23 Sep 2001 20:30:26 -0700, "Ganapathi Raman"

> >> >Hello,

> >> >I'm having a problem inserting a record into a table.

> >> >I'm using VB6 and Access database.

> >> >Here is how I tried to do it:

> >> >INSERT INTO STUDENT
> >> >    (StudentID, Amount, LogDate, Memo)
> >> >VALUES( 3,  18.12, '9/23/2001 8:00:59 PM', 'blah blah blah blah')

> >> Date literals must be delimited using hash marks (#):
> >> VALUES( 3,  18.12, #9/23/2001 8:00:59 PM#, 'blah blah blah blah')

> >> HTH,
> >> Bob Barrows
> >> Please reply to the newsgroup. My reply-to address is my "spam trap"
and I
> >don't check it very often.



Sat, 13 Mar 2004 07:55:05 GMT  
 Problem inserting record with date field
Now, it is working in my home machine too. The only change is that I copied
the converted MDB file from my office machine to my home machine.

So, I guess with Access 2000 database, date fields don't have to be enclosed
between #hashes#.

Thank you all.


Quote:
> In my home machine I don't have MS Access installed. My work machine has
> Access 2000 and when I tried to open the database, it prompted me to
convert
> it and I chose to convert it. After that, the insert statement started to
> work. Do I have an MSDE database now? I don't know.

> Here is how I connect to the database:

>     Set cnAccount = New ADODB.Connection

>     With cnAccount
>         .Provider = "Microsoft.Jet.OLEDB.4.0"
>         .ConnectionString = "..\TESTDATA.MDB"
>         .Open
>     End With

> I'm going to try a few things tonight and will keep you informed.

> Thanks for your help.



> > If "converted the mdb file to access 2000" means that you now have an
> > MSDE database, which is really SQL Server, then you're right: hashes
> > are not required. In SQL Server, you supply dates with
> > quote-delimiters.

> > I'm pretty sure though, that Jet databases still require the hashes. I
> > don't have A2K, so could someone verify what I'm saying here? Did Jet
> > 3.6 eliminate the need to delimit dates with hashes?

> > Bob Barrows

> > On Mon, 24 Sep 2001 14:55:13 -0700, "Ganapathi Raman"

> > >Hashes are not required. It works fine just the way it is. I copied the
> code
> > >to an NT 4.0 machine and ran it there and it worked fine. Of course I
> > >converted the mdb file to access 2000.

> > >I still don't know why it did not work on my home machine that is
running
> > >Win 2k. I'm going to try a few things tonight and see if it will work.

> > >Thanks for your input.



> > >> On Sun, 23 Sep 2001 20:30:26 -0700, "Ganapathi Raman"

> > >> >Hello,

> > >> >I'm having a problem inserting a record into a table.

> > >> >I'm using VB6 and Access database.

> > >> >Here is how I tried to do it:

> > >> >INSERT INTO STUDENT
> > >> >    (StudentID, Amount, LogDate, Memo)
> > >> >VALUES( 3,  18.12, '9/23/2001 8:00:59 PM', 'blah blah blah blah')

> > >> Date literals must be delimited using hash marks (#):
> > >> VALUES( 3,  18.12, #9/23/2001 8:00:59 PM#, 'blah blah blah blah')

> > >> HTH,
> > >> Bob Barrows
> > >> Please reply to the newsgroup. My reply-to address is my "spam trap"
> and I
> > >don't check it very often.



Sat, 13 Mar 2004 11:02:39 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. Problem inserting record with date field

2. How to Insert a Record with a Date Field into FoxPro

3. Problem accessing record from SQL Data base Date/Time Field

4. Crystal Reports 8.5 date range parameter field - record selection that includes null date range

5. Problem with format on insert autotext, and in inserting elements with form fields

6. Inserting current date into a contact's note field

7. Insert current date into form fields

8. Insert date and time field into MS Access with SQL

9. INSERT INTO and UPDATE date field

10. Inserting a date field into SQL

11. INSERT query fails with date field

12. Insert #NULL# into Date field causes datatype mismatch error

 

 
Powered by phpBB® Forum Software