Problems with SQL Statement.

[H.J.S.]

Hi. I'm trying to enter information from a HTM form and send it to an ASP
page.
Here's the code that I'm using as a reference.

<---begin code--->

mySQL= "INSERT INTO Employees "
mySQL= mySQL & "(FirstName,LastName,Address,City,Region,PostalCod e) "
mySQL= mySQL & "VALUES ('" & Request.Form("FirstName") & "','"
mySQL= mySQL & Request.Form("LastName") & "'"
mySQL= mySQL & ",'" & Request.Form("Address") & "'"
mySQL= mySQL & ",'" & Request.Form("City") & "','"
mySQL= mySQL & Request.Form("Region") & "','"
mySQL= mySQL & Request.Form("PostalCode") & "')"

myConnection.Execute mySQL

<---end code--->

and here it is adapted to my database.

<---begin code--->

Dim myConnString
Dim myConnection
Dim mySQL

myConnString = Application("aspsample_ConnectionString")

Set myConnection = Server.CreateObject("ADODB.Connection")

myConnection.Open myConnString

mySQL= "INSERT INTO Results "
mySQL= mySQL & "(Name,Room,Phone,Date,Description) "
mySQL= mySQL & "VALUES ('" & Request.Form("Name") & "','"
mySQL= mySQL & Request.Form("Room") & "'"
mySQL= mySQL & ",'" & Request.Form("Phone") & "',"
mySQL= mySQL & ",'" & Request.Form("Date") & "','"
mySQL= mySQL & Request.Form("Description") & ')"

myConnection.Execute mySQL

myConnection.Close

<--- end code --->

When users submit on form.htm they get this error on the ASP page
" Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO
statement.

/data/submit.asp, line 30 "

Line 30 is ---> myConnection.Execute mySQL

I don't know how to properly format the mySQL variable correctly. Can
anyone see the error?

Thanks a bunch.

[Ray at]

Change line 30 to say:

RESPONSE.WRITE mySQL
RESPONSE.END

You need to see the SQL statement before you can debug it. After you do
that, what is the value of mySQL?

Ray at work

"H.J.S." <spam@this> wrote in message
news:%23XHcUHPwDHA.2316@TK2MSFTNGP10.phx.gbl...

> Hi. I'm trying to enter information from a HTM form and send it to an ASP
> page.
> Here's the code that I'm using as a reference.
>
> <---begin code--->
>
> mySQL= "INSERT INTO Employees "
> mySQL= mySQL & "(FirstName,LastName,Address,City,Region,PostalCod e) "
> mySQL= mySQL & "VALUES ('" & Request.Form("FirstName") & "','"
> mySQL= mySQL & Request.Form("LastName") & "'"
> mySQL= mySQL & ",'" & Request.Form("Address") & "'"
> mySQL= mySQL & ",'" & Request.Form("City") & "','"
> mySQL= mySQL & Request.Form("Region") & "','"
> mySQL= mySQL & Request.Form("PostalCode") & "')"
>
> myConnection.Execute mySQL
>
> <---end code--->
>
> and here it is adapted to my database.
>
> <---begin code--->
>
> Dim myConnString
> Dim myConnection
> Dim mySQL
>
> myConnString = Application("aspsample_ConnectionString")
>
> Set myConnection = Server.CreateObject("ADODB.Connection")
>
> myConnection.Open myConnString
>
> mySQL= "INSERT INTO Results "
> mySQL= mySQL & "(Name,Room,Phone,Date,Description) "
> mySQL= mySQL & "VALUES ('" & Request.Form("Name") & "','"
> mySQL= mySQL & Request.Form("Room") & "'"
> mySQL= mySQL & ",'" & Request.Form("Phone") & "',"
> mySQL= mySQL & ",'" & Request.Form("Date") & "','"
> mySQL= mySQL & Request.Form("Description") & ')"
>
> myConnection.Execute mySQL
>
> myConnection.Close
>
> <--- end code --->
>
> When users submit on form.htm they get this error on the ASP page
> " Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
>
> [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO
> statement.
>
> /data/submit.asp, line 30 "
>
> Line 30 is ---> myConnection.Execute mySQL
>
> I don't know how to properly format the mySQL variable correctly. Can
> anyone see the error?
>
> Thanks a bunch.
>
>

[H.J.S.]

Ray, thanks so Much. Here's what I changed the asp page to...


mySQL= "INSERT INTO Results "
mySQL= mySQL & "(Name,Room,Phone,Date,Description) "
mySQL= mySQL & "VALUES ('" & Request.Form("Name") & "','"
mySQL= mySQL & Request.Form("Room") & "','"
mySQL= mySQL & Request.Form("Phone") & "',"'
mySQL= mySQL & Request.Form("Date") & "','"
mySQL= mySQL & Request.Form("Description") & "')"

RESPONSE.WRITE mySQL
RESPONSE.END

and here is the messge on the asp page now..... How can I correct the fact
that Date is messed up?
Thanks.

INSERT INTO Results (Name,Room,Phone,Date,Description) VALUES
('asdasdf','333','555',','DESCRIPTION')



"Ray at <%=sLocation%>" <myfirstname at lane34 dot com> wrote in message
news:%233UX1LPwDHA.2304@TK2MSFTNGP12.phx.gbl...

> Change line 30 to say:
>
> RESPONSE.WRITE mySQL
> RESPONSE.END
>
> You need to see the SQL statement before you can debug it. After you do
> that, what is the value of mySQL?
>
> Ray at work
>
> "H.J.S." <spam@this> wrote in message
> news:%23XHcUHPwDHA.2316@TK2MSFTNGP10.phx.gbl...

> > Hi. I'm trying to enter information from a HTM form and send it to an

ASP

> > page.
> > Here's the code that I'm using as a reference.
> >
> > <---begin code--->
> >
> > mySQL= "INSERT INTO Employees "
> > mySQL= mySQL & "(FirstName,LastName,Address,City,Region,PostalCod e) "
> > mySQL= mySQL & "VALUES ('" & Request.Form("FirstName") & "','"
> > mySQL= mySQL & Request.Form("LastName") & "'"
> > mySQL= mySQL & ",'" & Request.Form("Address") & "'"
> > mySQL= mySQL & ",'" & Request.Form("City") & "','"
> > mySQL= mySQL & Request.Form("Region") & "','"
> > mySQL= mySQL & Request.Form("PostalCode") & "')"
> >
> > myConnection.Execute mySQL
> >
> > <---end code--->
> >
> > and here it is adapted to my database.
> >
> > <---begin code--->
> >
> > Dim myConnString
> > Dim myConnection
> > Dim mySQL
> >
> > myConnString = Application("aspsample_ConnectionString")
> >
> > Set myConnection = Server.CreateObject("ADODB.Connection")
> >
> > myConnection.Open myConnString
> >
> > mySQL= "INSERT INTO Results "
> > mySQL= mySQL & "(Name,Room,Phone,Date,Description) "
> > mySQL= mySQL & "VALUES ('" & Request.Form("Name") & "','"
> > mySQL= mySQL & Request.Form("Room") & "'"
> > mySQL= mySQL & ",'" & Request.Form("Phone") & "',"
> > mySQL= mySQL & ",'" & Request.Form("Date") & "','"
> > mySQL= mySQL & Request.Form("Description") & ')"
> >
> > myConnection.Execute mySQL
> >
> > myConnection.Close
> >
> > <--- end code --->
> >
> > When users submit on form.htm they get this error on the ASP page
> > " Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
> >
> > [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO
> > statement.
> >
> > /data/submit.asp, line 30 "
> >
> > Line 30 is ---> myConnection.Execute mySQL
> >
> > I don't know how to properly format the mySQL variable correctly. Can
> > anyone see the error?
> >
> > Thanks a bunch.
> >
> >

>
>

[H.J.S.]

Sorry the previous post has incorrect information: Here is the Current asp
code


mySQL= "INSERT INTO Results "
mySQL= mySQL & "(Name,Room,Phone,Date,Description) "
mySQL= mySQL & "VALUES ('" & Request.Form("Name") & "','"
mySQL= mySQL & Request.Form("Room") & "','"
mySQL= mySQL & Request.Form("Phone") & "',"'"
mySQL= mySQL & Request.Form("Date") & "','"
mySQL= mySQL & Request.Form("Description") & "')"

RESPONSE.WRITE mySQL
RESPONSE.END

and here is the response on the ASP page.

INSERT INTO Results (Name,Room,Phone,Date,Description) VALUES ('person's
Name','333','555',','DESCRIPTION Here')

[Ray at]

> INSERT INTO Results (Name,Room,Phone,Date,Description) VALUES

> ('asdasdf','333','555',','DESCRIPTION')

Take a look at the value that you're trying to insert for Date. It's just a
single '. In your code that builds the SQL statement, you have the '
outside of the ".

> mySQL= mySQL & Request.Form("Phone") & "',"'

Since the ' is a comment character, that won't generate a VBScript error.

Ray at work


"H.J.S." <spam@this> wrote in message
news:ej8vRFQwDHA.3144@tk2msftngp13.phx.gbl...

> Ray, thanks so Much. Here's what I changed the asp page to...
>
>
> mySQL= "INSERT INTO Results "
> mySQL= mySQL & "(Name,Room,Phone,Date,Description) "
> mySQL= mySQL & "VALUES ('" & Request.Form("Name") & "','"
> mySQL= mySQL & Request.Form("Room") & "','"
> mySQL= mySQL & Request.Form("Phone") & "',"'
> mySQL= mySQL & Request.Form("Date") & "','"
> mySQL= mySQL & Request.Form("Description") & "')"
>
> RESPONSE.WRITE mySQL
> RESPONSE.END
>
> and here is the messge on the asp page now..... How can I correct the

fact

> that Date is messed up?
> Thanks.
>
> INSERT INTO Results (Name,Room,Phone,Date,Description) VALUES
> ('asdasdf','333','555',','DESCRIPTION')
>
>
>

[Ray at]

Okay, then look at this line:

mySQL= mySQL & Request.Form("Phone") & "',"'"

Ray at work

"H.J.S." <spam@this> wrote in message
news:e68SFJQwDHA.2540@TK2MSFTNGP10.phx.gbl...

> Sorry the previous post has incorrect information: Here is the Current

asp

> code
>
>
> mySQL= "INSERT INTO Results "
> mySQL= mySQL & "(Name,Room,Phone,Date,Description) "
> mySQL= mySQL & "VALUES ('" & Request.Form("Name") & "','"
> mySQL= mySQL & Request.Form("Room") & "','"
> mySQL= mySQL & Request.Form("Phone") & "',"'"
> mySQL= mySQL & Request.Form("Date") & "','"
> mySQL= mySQL & Request.Form("Description") & "')"
>
> RESPONSE.WRITE mySQL
> RESPONSE.END
>
> and here is the response on the ASP page.
>
> INSERT INTO Results (Name,Room,Phone,Date,Description) VALUES ('person's
> Name','333','555',','DESCRIPTION Here')
>
>

[H.J.S.]

I'm sorry I'm thickheaded on this one. What change should needs to be done?


"Ray at <%=sLocation%>" <myfirstname at lane34 dot com> wrote in message
news:%23%23XGqJQwDHA.3532@TK2MSFTNGP11.phx.gbl...

> > INSERT INTO Results (Name,Room,Phone,Date,Description) VALUES
> > ('asdasdf','333','555',','DESCRIPTION')

>
> Take a look at the value that you're trying to insert for Date. It's just

a

> single '. In your code that builds the SQL statement, you have the '
> outside of the ".
>

> > mySQL= mySQL & Request.Form("Phone") & "',"'

>
> Since the ' is a comment character, that won't generate a VBScript error.
>
> Ray at work
>
>

[Mark Schupp]

What DBMS are you using? Date formats may vary.


--
Mark Schupp
Head of Development
Integrity eLearning
[url]www.ielearning.com[/url]


"H.J.S." <spam@this> wrote in message
news:eh2g6WQwDHA.2508@TK2MSFTNGP12.phx.gbl...

> I'm sorry I'm thickheaded on this one. What change should needs to be

done?

>
>
> "Ray at <%=sLocation%>" <myfirstname at lane34 dot com> wrote in message
> news:%23%23XGqJQwDHA.3532@TK2MSFTNGP11.phx.gbl...

> > > INSERT INTO Results (Name,Room,Phone,Date,Description) VALUES
> > > ('asdasdf','333','555',','DESCRIPTION')

> >
> > Take a look at the value that you're trying to insert for Date. It's

just

> a

> > single '. In your code that builds the SQL statement, you have the '
> > outside of the ".
> >

> > > mySQL= mySQL & Request.Form("Phone") & "',"'

> >
> > Since the ' is a comment character, that won't generate a VBScript

error.

> >
> > Ray at work
> >
> >

>
>

[H.J.S.]

I'm using the Access Database Driver
Application("aspsample_ConnectionString") = "DSN=aspsample;DRIVER={Microsoft
Access Driver (*.mdb)}"

"Mark Schupp" <mschupp@ielearning.com> wrote in message
news:%237Xq65QwDHA.540@tk2msftngp13.phx.gbl...

> What DBMS are you using? Date formats may vary.
>
>
> --
> Mark Schupp
> Head of Development
> Integrity eLearning
> [url]www.ielearning.com[/url]
>
>
> "H.J.S." <spam@this> wrote in message
> news:eh2g6WQwDHA.2508@TK2MSFTNGP12.phx.gbl...

> > I'm sorry I'm thickheaded on this one. What change should needs to be

> done?

> >
> >
> > "Ray at <%=sLocation%>" <myfirstname at lane34 dot com> wrote in message
> > news:%23%23XGqJQwDHA.3532@TK2MSFTNGP11.phx.gbl...

> > > > INSERT INTO Results (Name,Room,Phone,Date,Description) VALUES
> > > > ('asdasdf','333','555',','DESCRIPTION')
> > >
> > > Take a look at the value that you're trying to insert for Date. It's

> just

> > a

> > > single '. In your code that builds the SQL statement, you have the '
> > > outside of the ".
> > >
> > > > mySQL= mySQL & Request.Form("Phone") & "',"'
> > >
> > > Since the ' is a comment character, that won't generate a VBScript

> error.

> > >
> > > Ray at work
> > >
> > >

> >
> >

>
>

[Ray at]

Here is the way I suggest to do it.

<%
Dim sName, sRoom, sPhone, sDate, sDescription

sName = Request.Form("Name")
sRoom = Request.Form("Room")
sPhone = Request.Form("Phone")
sDate = Request.Form("sDate")
sDescription = Request.Form("Description")

mySQL = "INSERT INTO Results ([Name],[Room],[Phone],[Date],[Description])
VALUES ("
mySQL = mySQL & "'" & SafeIn(sName) & "',"
mySQL = mySQL & "'" & SafeIn(sRoom) & "',"
mySQL = mySQL & "'" & SafeIn(sPhone) & "',"
mySQL = mySQL & "#" & sDate & "#,"
mySQL = mySQL & "'" & SafeIn(sDescription) & "'"
mySQL = mySQL & ")"



Function SafeIn(theString)
SafeIn = Replace(theString, "'", "''")
End Function
%>

This is assuming that all columns other than the date column are text (text
or memo). And some may argue about the inefficiency of concatenating like
that or using dynamic SQL, but we'll just go one step at a time here... :]

Ray at home

"H.J.S." <spam@this> wrote in message
news:eh2g6WQwDHA.2508@TK2MSFTNGP12.phx.gbl...

> I'm sorry I'm thickheaded on this one. What change should needs to be

done?

>
>
> "Ray at <%=sLocation%>" <myfirstname at lane34 dot com> wrote in message
> news:%23%23XGqJQwDHA.3532@TK2MSFTNGP11.phx.gbl...

> > > INSERT INTO Results (Name,Room,Phone,Date,Description) VALUES
> > > ('asdasdf','333','555',','DESCRIPTION')

> >
> > Take a look at the value that you're trying to insert for Date. It's

just

> a

> > single '. In your code that builds the SQL statement, you have the '
> > outside of the ".
> >

> > > mySQL= mySQL & Request.Form("Phone") & "',"'

> >
> > Since the ' is a comment character, that won't generate a VBScript

error.

> >
> > Ray at work
> >
> >

>
>

[Aaron Bertrand [MVP]]

VALUES ('person's Name'

There's a problem here. Strings are delimited by single quotes, right? So
you have one on the left, and one on the right, and then there's one in the
middle buggering it up for you.

[url]http://www.aspfaq.com/2035[/url]

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]

[Aaron Bertrand [MVP]]

Ugh, where did you get that connection string?
[url]http://www.aspfaq.com/2126[/url]

In Access, you delimit dates with # not '
[url]http://www.aspfaq.com/2023[/url]

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]




"H.J.S." <spam@this> wrote in message
news:e80RnFRwDHA.3116@tk2msftngp13.phx.gbl...

> I'm using the Access Database Driver
> Application("aspsample_ConnectionString") =

"DSN=aspsample;DRIVER={Microsoft

> Access Driver (*.mdb)}"
>
> "Mark Schupp" <mschupp@ielearning.com> wrote in message
> news:%237Xq65QwDHA.540@tk2msftngp13.phx.gbl...

> > What DBMS are you using? Date formats may vary.
> >
> >
> > --
> > Mark Schupp
> > Head of Development
> > Integrity eLearning
> > [url]www.ielearning.com[/url]
> >
> >
> > "H.J.S." <spam@this> wrote in message
> > news:eh2g6WQwDHA.2508@TK2MSFTNGP12.phx.gbl...

> > > I'm sorry I'm thickheaded on this one. What change should needs to be

> > done?

> > >
> > >
> > > "Ray at <%=sLocation%>" <myfirstname at lane34 dot com> wrote in

message

> > > news:%23%23XGqJQwDHA.3532@TK2MSFTNGP11.phx.gbl...
> > > > > INSERT INTO Results (Name,Room,Phone,Date,Description) VALUES
> > > > > ('asdasdf','333','555',','DESCRIPTION')
> > > >
> > > > Take a look at the value that you're trying to insert for Date.

It's

> > just

> > > a
> > > > single '. In your code that builds the SQL statement, you have the

'

> > > > outside of the ".
> > > >
> > > > > mySQL= mySQL & Request.Form("Phone") & "',"'
> > > >
> > > > Since the ' is a comment character, that won't generate a VBScript

> > error.

> > > >
> > > > Ray at work
> > > >
> > > >
> > >
> > >

> >
> >

>
>

[H.J.S.]

"Aaron Bertrand [MVP]" <aaron@TRASHaspfaq.com> wrote in message
news:u4ByMZSwDHA.1088@tk2msftngp13.phx.gbl...

> Ugh, where did you get that connection string?
> [url]http://www.aspfaq.com/2126[/url]
>
> In Access, you delimit dates with # not '
> [url]http://www.aspfaq.com/2023[/url]
>
> --
> Aaron Bertrand
> SQL Server MVP
> [url]http://www.aspfaq.com/[/url]
>
>

Thank you. That was the exact problem. When "Date" is omitted everything
gets into the database no problem. However, I tried to change the code to
this

mySQL= "INSERT INTO Results "
mySQL= mySQL & "(Name,Room,Phone,Date,Description) "
mySQL= mySQL & "VALUES ('" & Request.Form("Name") & "','"
mySQL= mySQL & Request.Form("Room") & "','"
mySQL= mySQL & Request.Form("Phone") & "','"
mySQL= mySQL & Request.Form("Date") & "','"
mySQL= mySQL & Request.Form("Description") & "')"

RESPONSE.WRITE mySQL
RESPONSE.END

and here is the output.

INSERT INTO Results (Name,Room,Phone,Date,Description) VALUES ('John
Qunicy','234','2377','12/03/2003','The description goes here.')

OK Seems like that should have done the trick but when I use

myConnection.Execute mySQL
instead of the Response.Write mySQL this error occurrs.
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO
statement.

/data/email.asp, line 33

Again line 33 is the myConnection.Execute mySQL (note: it does work fine
when date is totally omitted).

I tried changing all ' with # but that didn't work.

So how can I concatenate the Date delimited with # with all othe other
values. (please.... It's so close to completely working)...

[H.J.S.]

"Ray at <%=sLocation%>" <myFirstNameATlane34dotKOMM> wrote in message
news:OSElXRRwDHA.3744@TK2MSFTNGP11.phx.gbl...

> Here is the way I suggest to do it.
>
> <%
> Dim sName, sRoom, sPhone, sDate, sDescription
>
> sName = Request.Form("Name")
> sRoom = Request.Form("Room")
> sPhone = Request.Form("Phone")
> sDate = Request.Form("sDate")
> sDescription = Request.Form("Description")
>
> mySQL = "INSERT INTO Results ([Name],[Room],[Phone],[Date],[Description])
> VALUES ("
> mySQL = mySQL & "'" & SafeIn(sName) & "',"
> mySQL = mySQL & "'" & SafeIn(sRoom) & "',"
> mySQL = mySQL & "'" & SafeIn(sPhone) & "',"
> mySQL = mySQL & "#" & sDate & "#,"
> mySQL = mySQL & "'" & SafeIn(sDescription) & "'"
> mySQL = mySQL & ")"
>
>
>
> Function SafeIn(theString)
> SafeIn = Replace(theString, "'", "''")
> End Function
> %>
>
> This is assuming that all columns other than the date column are text

(text

> or memo).

Ray thanks so much for your patient help. I tried that and got this error.
Unterminated string constant

/data/email.asp, line 21

mySQL = "INSERT INTO Results ([Name],[Room],[Phone],[Date],[Description])
-------------------------------------------------------------------------^So
I had to edit the basic code to this...

Dim sName, sRoom, sPhone, sDate, sDescription

sName = Request.Form("Name")
sRoom = Request.Form("Room")
sPhone = Request.Form("Phone")
sDate = Request.Form("sDate")
sDescription = Request.Form("Description")

mySQL= "INSERT INTO Results "
mySQL= mySQL & "([Name],[Room],[Phone],[Date],[Description]) "
mySQL= mySQL & "VALUES ('" & SafeIn(sName) & "','"
mySQL= mySQL & SafeIn(sRoom) & "','"
mySQL= mySQL & SafeIn(sPhone) & "',"
mySQL= mySQL & "#" & sDate & "#,'"
mySQL= mySQL & SafeIn(sDescription) & "')"

Function SafeIn(theString)
SafeIn = Replace(theString, "'", "''")

End Function
RESPONSE.WRITE mySQL
RESPONSE.END

and the response is

INSERT INTO Results ([Name],[Room],[Phone],[Date],[Description]) VALUES
('John Qunicy','234','2377',##,'The description goes here.')

Hmm.. looks close but why is it ,##, instead of ,#date#, or the actual date
value that was entered?

If this is run with Execute mySQL this error comes up

Object required: ''
/data/email.asp, line 36

line 36 is the same execute mySQL line as always.

BTW it may help to view this in a command prompt edit screen. It's easier
to see ' from "

[Ray at]

"H.J.S." <spam@this> wrote in message
news:#iNXkZWwDHA.1576@TK2MSFTNGP11.phx.gbl...

>
>
> Ray thanks so much for your patient help. I tried that and got this

error.

> Unterminated string constant

That would be because of line-wrapping in your newsreader...

>
> /data/email.asp, line 21
>
> mySQL = "INSERT INTO Results ([Name],[Room],[Phone],[Date],[Description])
> -------------------------------------------------------------------------^

So

> I had to edit the basic code to this...

> RESPONSE.WRITE mySQL
> RESPONSE.END
>
> and the response is
>
> INSERT INTO Results ([Name],[Room],[Phone],[Date],[Description]) VALUES
> ('John Qunicy','234','2377',##,'The description goes here.')
>
> Hmm.. looks close but why is it ,##, instead of ,#date#, or the actual

date

> value that was entered?

Well, the value of the date comes from request.form("date"). Is there such
a form field? And does it have a value? What does this show you?

response.write request.form("date")
response.end

>
> If this is run with Execute mySQL this error comes up
>
> Object required: ''
> /data/email.asp, line 36
>
> line 36 is the same execute mySQL line as always.

Did you create your "myConnection" object anywhere?

Ray at home

[H.J.S.]

"Ray at <%=sLocation%>" <myFirstNameATlane34dotKOMM> wrote in message
news:Osq43gWwDHA.2316@TK2MSFTNGP10.phx.gbl...

>
> "H.J.S." <spam@this> wrote in message
> news:#iNXkZWwDHA.1576@TK2MSFTNGP11.phx.gbl...

> > Ray thanks so much for your patient help. I tried that and got this
> > error.
> > Unterminated string constant

>
> That would be because of line-wrapping in your newsreader...

Yes, you're right. I editied it and still get the same ,##, instead of the
date.
and still get the expected object # error

You were also right about defining myconnection. I accidentally erased it.
Even after I put it back it still didn't like the ,##,

So here is what Finally worked.

mySQL= "INSERT INTO Results "
mySQL= mySQL & "(Name,Room,Phone,[Date],Description) "
mySQL= mySQL & "VALUES ('" & Request.Form("Name") & "','"
mySQL= mySQL & Request.Form("Room") & "','"
mySQL= mySQL & Request.Form("Phone") & "','"
mySQL= mySQL & Request.Form("Date") & "','"
mySQL= mySQL & Request.Form("Description") & "')"

Notice [Date] was the only change I had to do after I had concatenated the
whole thing together.
Thanks for putting that in.

Again many thanks for the kind folks here that took time to help and
especially you Ray.

Have a great day!

[Mark Schupp]

mySQL= "INSERT INTO Results "
mySQL= mySQL & "(Name,Room,Phone,Date,Description) "
mySQL= mySQL & "VALUES ('" & Request.Form("Name") & "','"
mySQL= mySQL & Request.Form("Room") & "','"
mySQL= mySQL & Request.Form("Phone") & "',"
mySQL= mySQL & SQLDate(Request.Form("Date")) & ",'"
mySQL= mySQL & Request.Form("Description") & "')"


Function SQLDate(dtIn)

If IsDate(dtIn) Then
SQLDate = "#" & Year(dtIn) & "-" & Month(dtIn) & "-" & Day(dtIn) &
" " & _
Hour(dtIn) & ":" & Minute(dtIn) & ":" &
Second(dtIn) & "#"
Else
SQLDate = "NULL"
End

End Function

--
Mark Schupp
Head of Development
Integrity eLearning
[url]www.ielearning.com[/url]


"H.J.S." <spam@this> wrote in message
news:ef1q7iVwDHA.2452@tk2msftngp13.phx.gbl...

>
> "Aaron Bertrand [MVP]" <aaron@TRASHaspfaq.com> wrote in message
> news:u4ByMZSwDHA.1088@tk2msftngp13.phx.gbl...

> > Ugh, where did you get that connection string?
> > [url]http://www.aspfaq.com/2126[/url]
> >
> > In Access, you delimit dates with # not '
> > [url]http://www.aspfaq.com/2023[/url]
> >
> > --
> > Aaron Bertrand
> > SQL Server MVP
> > [url]http://www.aspfaq.com/[/url]
> >
> >

>
> Thank you. That was the exact problem. When "Date" is omitted everything
> gets into the database no problem. However, I tried to change the code to
> this
>
> mySQL= "INSERT INTO Results "
> mySQL= mySQL & "(Name,Room,Phone,Date,Description) "
> mySQL= mySQL & "VALUES ('" & Request.Form("Name") & "','"
> mySQL= mySQL & Request.Form("Room") & "','"
> mySQL= mySQL & Request.Form("Phone") & "','"
> mySQL= mySQL & Request.Form("Date") & "','"
> mySQL= mySQL & Request.Form("Description") & "')"
>
> RESPONSE.WRITE mySQL
> RESPONSE.END
>
> and here is the output.
>
> INSERT INTO Results (Name,Room,Phone,Date,Description) VALUES ('John
> Qunicy','234','2377','12/03/2003','The description goes here.')
>
> OK Seems like that should have done the trick but when I use
>
> myConnection.Execute mySQL
> instead of the Response.Write mySQL this error occurrs.
> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
>
> [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO
> statement.
>
> /data/email.asp, line 33
>
> Again line 33 is the myConnection.Execute mySQL (note: it does work fine
> when date is totally omitted).
>
> I tried changing all ' with # but that didn't work.
>
> So how can I concatenate the Date delimited with # with all othe other
> values. (please.... It's so close to completely working)...
>
>