Syntax error in the SQL

Hi folks,

I get a error stating that there is a syntax error in my SQL Statement. I'm
not seeing what it is, but maybe someone else will.

Exact error from the browser page is:

Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error in UPDATE statement.
/pilots/SubmitEvent.asp, line 47


Line 47 is:
objConn.Execute strSQL


strSQL is a simple concatenated string placed into the variable.

I "response.write'ed" it to the page and then pulled it from the "view
source". The date and time fields are blank and the DB is set to allow that
so it shouldn't be a problem. the Body field contains HTML Code. I have a
function that cleans up the HTML (doubling up the quotes and stuff like
that) before passing it to the strSQL variable. The DB type is an Access
MDB file.

Here is is below,..I'll split it up to keep the sloppy line-breaks to a
minimum:


UPDATE tblEvents

SET fldEventTitle = 'Tuesday Flying Day',
fldEventDate = ,
fldEventTime = ,
fldEventBody = '

Flying days with
focus on training those still learing to fly R/C aircraft.

Re: Syntax error in the SQL

Phillip Windell wrote:
> Hi folks,
>
> I get a error stating that there is a syntax error in my SQL
> Statement. I'm not seeing what it is, but maybe someone else will.
>
> Exact error from the browser page is:
>
> Error Type:
> Microsoft JET Database Engine (0x80040E14)
> Syntax error in UPDATE statement.
> /pilots/SubmitEvent.asp, line 47
>
>
> Line 47 is:
> objConn.Execute strSQL
>
>
> strSQL is a simple concatenated string placed into the variable.

Still using dynamic sql?

>
>
> UPDATE tblEvents
>
> SET fldEventTitle = 'Tuesday Flying Day',
> fldEventDate = ,
> fldEventTime = ,
> fldEventBody = '

Flying days
> with focus on training those still learing to fly R/C
> aircraft.

Flying days with
focus on training those still learing to fly R/C aircraft.

Re: Syntax error in the SQL

Hi Bob,
"Bob Barrows [MVP]" wrote in message
news:%23o52HuqlFHA.1204@TK2MSFTNGP12.phx.gbl...
> > strSQL is a simple concatenated string placed into the variable.
>
> Still using dynamic sql?

I remeber you always preached against that (and I agreed),... I created an
Update Query to use instead and was really hoping to go that way, but I
don't know what I am doing with it so I fell back to old habits. I was also
hoping to use some kind of query to create New records also but can't even
come close to figuring that out,...Access doesn't seem to have such a thing
(the Append query doesn't seem to be for that purpose).
Just as when I used to hang out in this group, I'm not a programmer or a
DBA/DBD but ended up doing this because I'm the only guy around that would
even have a chance of getting this done. It is for a site of an R/C Flying
Club that I happen to be a member of.

I'm trying to design the Site so that "computer illiterates" can update the
site with just their web browsers, so that I am not "married" to the thing
the rest of my life updating manually for them.

> > UPDATE tblEvents
> >
> > SET fldEventTitle = 'Tuesday Flying Day',
> > fldEventDate = ,
> > fldEventTime = ,
> > fldEventBody = '

Flying days
> > with focus on training those still learing to fly R/C
> > aircraft.

Re: Syntax error in the SQL

2 pieces of good news.

1. The NULL took care of that part of the problem
2. I got it to work with an Update Access Query (still required the NULL's)
and don't need the dynamic SQL

How do I go about creating New records without dynamic SQL?

--
Phillip Windell [MCP, MVP, CCNA]
www.wandtv.com
-----------------------------------------------------
Understanding the ISA 2004 Access Rule Processing
http://www.isaserver.org/articles/ISA2004_AccessRules.html

Microsoft Internet Security & Acceleration Server: Guidance
http://www.microsoft.com/isaserver/techinfo/Guidance/2004.as p
http://www.microsoft.com/isaserver/techinfo/Guidance/2000.as p

Microsoft Internet Security & Acceleration Server: Partners
http://www.microsoft.com/isaserver/partners/default.asp
-----------------------------------------------------



"Phillip Windell" <@.> wrote in message
news:%23NCPeBrlFHA.3568@TK2MSFTNGP10.phx.gbl...
> Hi Bob,
> "Bob Barrows [MVP]" wrote in message
> news:%23o52HuqlFHA.1204@TK2MSFTNGP12.phx.gbl...
> > > strSQL is a simple concatenated string placed into the variable.
> >
> > Still using dynamic sql?
>
> I remeber you always preached against that (and I agreed),... I created an
> Update Query to use instead and was really hoping to go that way, but I
> don't know what I am doing with it so I fell back to old habits. I was
also
> hoping to use some kind of query to create New records also but can't even
> come close to figuring that out,...Access doesn't seem to have such a
thing
> (the Append query doesn't seem to be for that purpose).
> Just as when I used to hang out in this group, I'm not a programmer or a
> DBA/DBD but ended up doing this because I'm the only guy around that would
> even have a chance of getting this done. It is for a site of an R/C
Flying
> Club that I happen to be a member of.
>
> I'm trying to design the Site so that "computer illiterates" can update
the
> site with just their web browsers, so that I am not "married" to the thing
> the rest of my life updating manually for them.
>
> > > UPDATE tblEvents
> > >
> > > SET fldEventTitle = 'Tuesday Flying Day',
> > > fldEventDate = ,
> > > fldEventTime = ,
> > > fldEventBody = '

Flying days
> > > with focus on training those still learing to fly R/C
> > > aircraft.

Re: Syntax error in the SQL

Spoke too soon. No error is given, so I thought it worked,...but the Table
is not updated.

I'm lost now,..I have no idea how to troubleshoot that without an error to
go by.

--
Phillip Windell [MCP, MVP, CCNA]
www.wandtv.com
-----------------------------------------------------
Understanding the ISA 2004 Access Rule Processing
http://www.isaserver.org/articles/ISA2004_AccessRules.html

Microsoft Internet Security & Acceleration Server: Guidance
http://www.microsoft.com/isaserver/techinfo/Guidance/2004.as p
http://www.microsoft.com/isaserver/techinfo/Guidance/2000.as p

Microsoft Internet Security & Acceleration Server: Partners
http://www.microsoft.com/isaserver/partners/default.asp
-----------------------------------------------------

Re: Syntax error in the SQL

Phillip Windell wrote:
> Spoke too soon. No error is given, so I thought it worked,...but the
> Table is not updated.
>
> I'm lost now,..I have no idea how to troubleshoot that without an
> error to go by.

Does it work when you test it in Access?

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Syntax error in the SQL

Phillip Windell wrote:
> 2 pieces of good news.
>
> 1. The NULL took care of that part of the problem
> 2. I got it to work with an Update Access Query (still required the
> NULL's) and don't need the dynamic SQL
>
> How do I go about creating New records without dynamic SQL?
>

I don't get it - are you saying you don't know how to create an INSERT
statement (aka "Append query" in Access)?

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Syntax error in the SQL

Phillip Windell wrote:
> Hi Bob,
> "Bob Barrows [MVP]" wrote in message
> news:%23o52HuqlFHA.1204@TK2MSFTNGP12.phx.gbl...
>>> strSQL is a simple concatenated string placed into the variable.
>>
>> Still using dynamic sql?
>
> I remeber you always preached against that (and I agreed),... I
> created an Update Query to use instead and was really hoping to go
> that way, but I don't know what I am doing with it

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1& selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1& selm=ukS%246S%247CHA.2464%40TK2MSFTNGP11.phx.gbl

http://www.google.com/groups?selm=eETTdnvFDHA.1660%40TK2MSFT NGP10.phx.gbl&oe=UTF-8&output=gplain

http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&sel m=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl

Using Command object:
http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Syntax error in the SQL

"Bob Barrows [MVP]" wrote in message
news:eSmYxGslFHA.2080@TK2MSFTNGP14.phx.gbl...
> Phillip Windell wrote:
> > 2 pieces of good news.
> >
> > 1. The NULL took care of that part of the problem
> > 2. I got it to work with an Update Access Query (still required the
> > NULL's) and don't need the dynamic SQL
> >
> > How do I go about creating New records without dynamic SQL?
> >
>
> I don't get it - are you saying you don't know how to create an INSERT
> statement (aka "Append query" in Access)?

Appearantly not,...I thought I did,... but couldn't get anywhere with it.
Without going back and attempting it again, I can't give you anything
"exact" as to errors and such. Maybe later.

I have done INSERT's just fine with dynamic SQL.

--
Phillip Windell [MCP, MVP, CCNA]
www.wandtv.com
-----------------------------------------------------
Understanding the ISA 2004 Access Rule Processing
http://www.isaserver.org/articles/ISA2004_AccessRules.html

Microsoft Internet Security & Acceleration Server: Guidance
http://www.microsoft.com/isaserver/techinfo/Guidance/2004.as p
http://www.microsoft.com/isaserver/techinfo/Guidance/2000.as p

Microsoft Internet Security & Acceleration Server: Partners
http://www.microsoft.com/isaserver/partners/default.asp
-----------------------------------------------------

Re: Syntax error in the SQL

"Bob Barrows [MVP]" wrote in message
news:u4okBGslFHA.420@TK2MSFTNGP09.phx.gbl...
> Phillip Windell wrote:
> > Spoke too soon. No error is given, so I thought it worked,...but the
> > Table is not updated.
> >
> > I'm lost now,..I have no idea how to troubleshoot that without an
> > error to go by.
>
> Does it work when you test it in Access?

I pasted the data into the "prompts" when running the Query in Access itself
and it would fail. I didn't have much patients with fighting with it since
the dynamic SQL worked. Maybe I can try again later this afternoon and be
able to give specifics,...I was rudely interupted by my job and had to stop
on it.

Haven't had time to get to those links you posted in your other message yet.
I'll try to get to them. Once I know the right way to create and work with
these other types of queries I'll be fine, but initially I was just taking a
"stab" at the way I thought they should be.

--
Phillip Windell [MCP, MVP, CCNA]
www.wandtv.com
-----------------------------------------------------
Understanding the ISA 2004 Access Rule Processing
http://www.isaserver.org/articles/ISA2004_AccessRules.html

Microsoft Internet Security & Acceleration Server: Guidance
http://www.microsoft.com/isaserver/techinfo/Guidance/2004.as p
http://www.microsoft.com/isaserver/techinfo/Guidance/2000.as p

Microsoft Internet Security & Acceleration Server: Partners
http://www.microsoft.com/isaserver/partners/default.asp
-----------------------------------------------------

Re: Syntax error in the SQL

I get a data Type Mismatch with the Update Query. If I open Access run the
query and paste the data into the prompts the query works perfectly fine.
I intensionally left the strEventTime and strEventDate fields blank when
doing that to prove they will accept empty inputs. But if I run it via the
normal ASP code it fails with a:

Microsoft JET Database Engine (0x80040E07)
Data type mismatch in criteria expression.

I can't think of what it could be other than the one line of code that runs
the query,...this is it here, the variables are feeding it the exact same
data I pasted into it when running it in Access (even the empty inputs). The
variables are in the exact same order that the prompts appear when the Query
is run in Access. The field for Date is the Date/Time data type while all
others are either Text or Memo. All fields are allowed to be blank except
the strEventID

objConn.qryUpdateEvents strEventTitle, strEventDate, strEventTime,
strEventBody, strEventID

--
Phillip Windell [MCP, MVP, CCNA]
www.wandtv.com
-----------------------------------------------------
Understanding the ISA 2004 Access Rule Processing
http://www.isaserver.org/articles/ISA2004_AccessRules.html

Microsoft Internet Security & Acceleration Server: Guidance
http://www.microsoft.com/isaserver/techinfo/Guidance/2004.as p
http://www.microsoft.com/isaserver/techinfo/Guidance/2000.as p

Microsoft Internet Security & Acceleration Server: Partners
http://www.microsoft.com/isaserver/partners/default.asp
-----------------------------------------------------

Re: Syntax error in the SQL

Phillip Windell wrote:
> I get a data Type Mismatch with the Update Query. If I open Access
> run the query and paste the data into the prompts the query works
> perfectly fine.
> I intensionally left the strEventTime and strEventDate fields blank
> when doing that to prove they will accept empty inputs. But if I run
> it via the normal ASP code it fails with a:
>
> Microsoft JET Database Engine (0x80040E07)
> Data type mismatch in criteria expression.
>
> I can't think of what it could be other than the one line of code
> that runs the query,...this is it here, the variables are feeding it
> the exact same data I pasted into it when running it in Access (even
> the empty inputs). The variables are in the exact same order that the
> prompts appear when the Query is run in Access. The field for Date
> is the Date/Time data type while all others are either Text or Memo.
> All fields are allowed to be blank except the strEventID
>
> objConn.qryUpdateEvents strEventTitle, strEventDate, strEventTime,
> strEventBody, strEventID
>

Use CDate to guarantee that you are passing dates, i.e:

if len(strEventDate) = 0 then
strEventDate=Null
Else
strEventDate=CDate(strEventDate)
End if
if len(strEventTime) = 0 then
strEventTime=Null
Else
strEventTime=CDate(strEventTime)
End if
objConn.qryUpdateEvents strEventTitle, strEventDate, _
strEventTime, strEventBody, strEventID


You'll want to add some error-handling just in case the user supplies a
value that cannot be converted into a date.

HTH,
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Syntax error in the SQL

OK, sounds good. Probably be tomorrow before I fool with it some more.

Thanks Bob!


Phil

"Bob Barrows [MVP]" wrote in message
news:uF4BnHtlFHA.2916@TK2MSFTNGP14.phx.gbl...
> Use CDate to guarantee that you are passing dates, i.e:
>
> if len(strEventDate) = 0 then
> strEventDate=Null
> Else
> strEventDate=CDate(strEventDate)
> End if
> if len(strEventTime) = 0 then
> strEventTime=Null
> Else
> strEventTime=CDate(strEventTime)
> End if
> objConn.qryUpdateEvents strEventTitle, strEventDate, _
> strEventTime, strEventBody, strEventID
>
>
> You'll want to add some error-handling just in case the user supplies a
> value that cannot be converted into a date.

Re: Syntax error in the SQL

It didn't do any good. But I don't think it would have helped because,
first,...the field is empty anyway and I not trying to put anything into it,
and second,..the thing works fine with the exact same data when using
dynamic SQL and it works fine when running the Query "by hand" in
Access,...it is only failing the query is run from the page code. Here's
pretty much the entire page's code without the include files. I'm not
including the include file's code, but I have used it extensively and am
confident that the include file's code is not part of the problem. The
Response.Write's are commented out, and I can run either the dynamic SQL or
the Query depending on which "objConn" line I uncomment and run.

Dim strEventID
Dim strEventTitle
Dim strEventDate
Dim strEventTime
Dim strEventBody

strEventID = Request.Form.Item("txtEventID")
strEventTitle = Request.Form.Item("txtEventTitle")
strEventDate = Request.Form.Item("txtEventDate")
strEventTime = Request.Form.Item("txtEventTime")
strEventBody = Request.Form.Item("txtEventBody")

'Create SQL string for Update
strSQL = "UPDATE tblEvents SET "
strSQL = strSQL & "fldEventTitle = '" & strEventTitle & "', "
strSQL = strSQL & "fldEventDate = " & strEventDate & ", "
strSQL = strSQL & "fldEventTime = '" & strEventTime & "', "
strSQL = strSQL & "fldEventBody = '" & strEventBody & "'"
strSQL = strSQL & " WHERE fldEventID = '" & strEventID & "'"
'Response.write "

strEventID = " & strEventID & "

strEventTitle = " & strEventTitle & "

strEventDate = " & strEventDate & "

strEventTime = " & strEventTime & "

strEventBody = " & strEventBody & "

Re: Syntax error in the SQL

I don't understand. It does not look as if you incorporated my suggested use
of CDate ...

Phillip Windell wrote:
> It didn't do any good. But I don't think it would have helped
> because, first,...the field is empty anyway and I not trying to put
> anything into it, and second,..the thing works fine with the exact
> same data when using dynamic SQL and it works fine when running the
> Query "by hand" in Access,...it is only failing the query is run from
> the page code. Here's pretty much the entire page's code without the
> include files. I'm not including the include file's code, but I have
> used it extensively and am confident that the include file's code is
> not part of the problem. The Response.Write's are commented out, and
> I can run either the dynamic SQL or the Query depending on which
> "objConn" line I uncomment and run.
>
> Dim strEventID
> Dim strEventTitle
> Dim strEventDate
> Dim strEventTime
> Dim strEventBody
>
> strEventID = Request.Form.Item("txtEventID")
> strEventTitle = Request.Form.Item("txtEventTitle")
> strEventDate = Request.Form.Item("txtEventDate")
> strEventTime = Request.Form.Item("txtEventTime")
> strEventBody = Request.Form.Item("txtEventBody")
>
> 'Create SQL string for Update
> strSQL = "UPDATE tblEvents SET "
> strSQL = strSQL & "fldEventTitle = '" & strEventTitle & "', "
> strSQL = strSQL & "fldEventDate = " & strEventDate & ", "
> strSQL = strSQL & "fldEventTime = '" & strEventTime & "', "
> strSQL = strSQL & "fldEventBody = '" & strEventBody & "'"
> strSQL = strSQL & " WHERE fldEventID = '" & strEventID & "'"
> 'Response.write "

strEventID = " & strEventID & "

strEventTitle = " & strEventTitle & "

strEventDate = " & strEventDate & "

strEventTime = " & strEventTime &
> "

strEventBody = " & strEventBody
> & "

Re: Syntax error in the SQL

I did, but it didn't work,...I'm showing you the code from before that
attempt.
I tried CDate() at these places (one at a time), they all failed with Type
Mismatch. These are three separate attempts, I didn't do all three at the
same time.

Attempt #1: strEventDate = CDate(Request.Form.Item("txtEventDate"))

Attempt #2: strEventDate = Request.Form.Item("txtEventDate")
strEventDate = CDate(strEventDate)

Attempt #3:
objConn.qryUpdateEvents strEventTitle, CDate(strEventDate), strEventTime,
strEventBody, strEventID


--
Phillip Windell [MCP, MVP, CCNA]
www.wandtv.com
-----------------------------------------------------
Understanding the ISA 2004 Access Rule Processing
http://www.isaserver.org/articles/ISA2004_AccessRules.html

Microsoft Internet Security & Acceleration Server: Guidance
http://www.microsoft.com/isaserver/techinfo/Guidance/2004.as p
http://www.microsoft.com/isaserver/techinfo/Guidance/2000.as p

Microsoft Internet Security & Acceleration Server: Partners
http://www.microsoft.com/isaserver/partners/default.asp
-----------------------------------------------------


"Bob Barrows [MVP]" wrote in message
news:u4NkxrtlFHA.1412@TK2MSFTNGP09.phx.gbl...
> I don't understand. It does not look as if you incorporated my suggested
use
> of CDate ...
>
> Phillip Windell wrote:
> > It didn't do any good. But I don't think it would have helped
> > because, first,...the field is empty anyway and I not trying to put
> > anything into it, and second,..the thing works fine with the exact
> > same data when using dynamic SQL and it works fine when running the
> > Query "by hand" in Access,...it is only failing the query is run from
> > the page code. Here's pretty much the entire page's code without the
> > include files. I'm not including the include file's code, but I have
> > used it extensively and am confident that the include file's code is
> > not part of the problem. The Response.Write's are commented out, and
> > I can run either the dynamic SQL or the Query depending on which
> > "objConn" line I uncomment and run.
> >
> > Dim strEventID
> > Dim strEventTitle
> > Dim strEventDate
> > Dim strEventTime
> > Dim strEventBody
> >
> > strEventID = Request.Form.Item("txtEventID")
> > strEventTitle = Request.Form.Item("txtEventTitle")
> > strEventDate = Request.Form.Item("txtEventDate")
> > strEventTime = Request.Form.Item("txtEventTime")
> > strEventBody = Request.Form.Item("txtEventBody")
> >
> > 'Create SQL string for Update
> > strSQL = "UPDATE tblEvents SET "
> > strSQL = strSQL & "fldEventTitle = '" & strEventTitle & "', "
> > strSQL = strSQL & "fldEventDate = " & strEventDate & ", "
> > strSQL = strSQL & "fldEventTime = '" & strEventTime & "', "
> > strSQL = strSQL & "fldEventBody = '" & strEventBody & "'"
> > strSQL = strSQL & " WHERE fldEventID = '" & strEventID & "'"
> > 'Response.write "

strEventID = " & strEventID & "

strEventTitle = " & strEventTitle & "

strEventDate = " & strEventDate & "

strEventTime = " & strEventTime &
> > "

strEventBody = " & strEventBody
> > & "

Re: Syntax error in the SQL

"Phillip Windell" <@.> wrote in message
news:ecni6%23tlFHA.708@TK2MSFTNGP09.phx.gbl...
>I did, but it didn't work,...I'm showing you the code from before that
> attempt.
> I tried CDate() at these places (one at a time), they all failed with Type
> Mismatch. These are three separate attempts, I didn't do all three at the
> same time.
>
> Attempt #1: strEventDate = CDate(Request.Form.Item("txtEventDate"))
>
> Attempt #2: strEventDate = Request.Form.Item("txtEventDate")
> strEventDate = CDate(strEventDate)
>
> Attempt #3:
> objConn.qryUpdateEvents strEventTitle, CDate(strEventDate), strEventTime,
> strEventBody, strEventID

I think this is what Bob was describing. I've also included parameterized
queries for inserting and deleting. And also one for selecting events based
on a date range.

[ASP PAGES]
<--Begin SubmitEvent.asp-->
<%
OPTION EXPLICIT
Dim event_date, event_time, cn, rs
If IsDate(Request.Form.Item("txtEventDate")) Then
event_date = CDate(Request.Form.Item("txtEventDate"))
Else
event_date = NULL
End If

If IsDate(Request.Form.Item("txtEventTime")) Then
event_time = CDate(Request.Form.Item("txtEventTime"))
Else
event_time = NULL
End If

Set cn = CreateObject("ADODB.Connection")
cn.Open DB '<-- Your DSN-Less OLEDB Connection String Here
cn.Update_Event _
Request.Form.Item("txtEventID"),_
Request.Form.Item("txtEventTitle"),_
event_date,_
event_time,_
Request.Form.Item("txtEventBody")
cn.Close : Set cn = Nothing
%>
<--End Update_Event.asp-->


[PARAMETERIZED QUERIES]
<--Begin Insert_Event-->
PARAMETERS
prm_event_id TEXT,
prm_title TEXT,
prm_date DATETIME,
prm_time DATETIME,
prm_body TEXT
;
INSERT INTO tblEvents (fldEventID, fldEventTitle, fldEventDate,
fldEventTime, fldEventBody)
VALUES (prm_event_id, prm_title, prm_date, prm_time, prm_body)
<--End Insert_Event-->


<--Begin Update_Event-->
PARAMETERS
prm_event_id TEXT,
prm_title TEXT,
prm_date DATETIME,
prm_time DATETIME,
prm_body TEXT
;
UPDATE tblEvents
SET fldEventTitle = prm_title, fldEventDate = prm_date, fldEventTime =
prm_time, fldEventBody = prm_body
WHERE fldEventID = prm_event_id
<--End Update_Event-->


<--Begin Delete_Event-->
PARAMETERS
prm_event_id TEXT
;
DELETE tblEvents
WHERE fldEventID = prm_event_id
<--End Delete_Event-->


<--Begin Select_Events-->
PARAMETERS
prm_start_dt DATETIME,
prm_end_dt DATETIME
;
SELECT fldEventID, fldEventTitle, fldEventDate, fldEventTime, fldEventBody
FROM tblEvents
WHERE fldEventDate >= prm_start_dt AND fldEvent_Date <= prm_end_dt
ORDER BY fldEventDate, fldEventTime, fldEventID
<--End Select_Events-->

Re: Syntax error in the SQL

Phillip Windell wrote:
> I did, but it didn't work,...I'm showing you the code from before that
> attempt.
> I tried CDate() at these places (one at a time), they all failed with
> Type Mismatch. These are three separate attempts, I didn't do all
> three at the same time.
>
> Attempt #1: strEventDate = CDate(Request.Form.Item("txtEventDate"))

Not good. You need to make sure it CAN be converted to a date first ...

>
> Attempt #2: strEventDate = Request.Form.Item("txtEventDate")
Better, but then you blow it
> strEventDate = CDate(strEventDate)

Here is where you should have tested for a zero-length input:

if len(strEventDate) = 0 then
strEventDate=Null
Else
strEventDate=CDate(strEventDate)
End if

Do the same with strEventTime, unless that field is not a date/time field.
Then, simply:
objConn.qryUpdateEvents strEventTitle, strEventDate, _
strEventTime, strEventBody, strEventID

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Syntax error in the SQL

"Chris Hohmann" wrote in message
news:uYGldIulFHA.3288@TK2MSFTNGP09.phx.gbl...
> I think this is what Bob was describing. I've also included parameterized
> queries for inserting and deleting. And also one for selecting events
based
> on a date range.

> OPTION EXPLICIT
> Dim event_date, event_time, cn, rs
> If IsDate(Request.Form.Item("txtEventDate")) Then
> event_date = CDate(Request.Form.Item("txtEventDate"))
> Else
> event_date = NULL
> End If

Ok,..yes the IsDate() makes sense, I hadn't thought of that.

--
Phillip Windell [MCP, MVP, CCNA]
www.wandtv.com
-----------------------------------------------------
Understanding the ISA 2004 Access Rule Processing
http://www.isaserver.org/articles/ISA2004_AccessRules.html

Microsoft Internet Security & Acceleration Server: Guidance
http://www.microsoft.com/isaserver/techinfo/Guidance/2004.as p
http://www.microsoft.com/isaserver/techinfo/Guidance/2000.as p

Microsoft Internet Security & Acceleration Server: Partners
http://www.microsoft.com/isaserver/partners/default.asp
-----------------------------------------------------

Re: Syntax error in the SQL

"Bob Barrows [MVP]" wrote in message
news:%23k0XwVulFHA.1444@TK2MSFTNGP10.phx.gbl...
> Here is where you should have tested for a zero-length input:
>
> if len(strEventDate) = 0 then
> strEventDate=Null
> Else
> strEventDate=CDate(strEventDate)
> End if
>
> Do the same with strEventTime, unless that field is not a date/time field.
> Then, simply:
> objConn.qryUpdateEvents strEventTitle, strEventDate, _
> strEventTime, strEventBody, strEventID

Ok. Thanks guys. I'll work on it some more later this morning.

--
Phillip Windell [MCP, MVP, CCNA]
www.wandtv.com
-----------------------------------------------------
Understanding the ISA 2004 Access Rule Processing
http://www.isaserver.org/articles/ISA2004_AccessRules.html

Microsoft Internet Security & Acceleration Server: Guidance
http://www.microsoft.com/isaserver/techinfo/Guidance/2004.as p
http://www.microsoft.com/isaserver/techinfo/Guidance/2000.as p

Microsoft Internet Security & Acceleration Server: Partners
http://www.microsoft.com/isaserver/partners/default.asp
-----------------------------------------------------

Re: Syntax error in the SQL

"Bob Barrows [MVP]" wrote in message
news:%23k0XwVulFHA.1444@TK2MSFTNGP10.phx.gbl...
> if len(strEventDate) = 0 then
> strEventDate=Null
> Else
> strEventDate=CDate(strEventDate)
> End if


Looks like that did the trick. Just working that section of code into it
took care of it. I still have to finish the "Delete Record" and "New
Record" sections but I doubt they will be much trouble and hopefully I won't
have pester anyone with it.

Do you think I should incorperate Chris's "If IsDate(strSomething)" into it
anywhere or just keep it in mind for later in case I need it?

Thanks guys,


--
Phillip Windell [MCP, MVP, CCNA]
www.wandtv.com

Re: Syntax error in the SQL

Phillip Windell wrote:
> "Bob Barrows [MVP]" wrote in message
> news:%23k0XwVulFHA.1444@TK2MSFTNGP10.phx.gbl...
>> if len(strEventDate) = 0 then
>> strEventDate=Null
>> Else
>> strEventDate=CDate(strEventDate)
>> End if
>
>
> Looks like that did the trick. Just working that section of code into
> it took care of it. I still have to finish the "Delete Record" and
> "New Record" sections but I doubt they will be much trouble and
> hopefully I won't have pester anyone with it.
>
> Do you think I should incorperate Chris's "If IsDate(strSomething)"
> into it anywhere or just keep it in mind for later in case I need it?
>
Yes, that will be helpful if you are relying on users to enter properly
formatted dates. Sometimes they make mistakes ...

if len(strEventDate) = 0 then
strEventDate=Null
Elseif IsDate(strEventDate) then
strEventDate=CDate(strEventDate)
Else
'I don't know - send error message to user?
'Set strEventDate to Null?
'Your choice
End if

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Syntax error in the SQL

"Bob Barrows [MVP]" wrote in message
news:eDu50x2lFHA.3816@tk2msftngp13.phx.gbl...
: Phillip Windell wrote:
: > Do you think I should incorperate Chris's "If IsDate(strSomething)"
: > into it anywhere or just keep it in mind for later in case I need it?
: >
: Yes, that will be helpful if you are relying on users to enter properly
: formatted dates. Sometimes they make mistakes ...

Users make mistakes?

--
Roland Hall
/* This information is distributed in the hope that it will be useful, but
without any warranty; without even the implied warranty of merchantability
or fitness for a particular purpose. */
Technet Script Center - http://www.microsoft.com/technet/scriptcenter/
WSH 5.6 Documentation - http://msdn.microsoft.com/downloads/list/webdev.asp
MSDN Library - http://msdn.microsoft.com/library/default.asp

Re: Syntax error in the SQL

Roland Hall wrote:
> "Bob Barrows [MVP]" wrote in message
> news:eDu50x2lFHA.3816@tk2msftngp13.phx.gbl...
>> Phillip Windell wrote:
>>> Do you think I should incorperate Chris's "If IsDate(strSomething)"
>>> into it anywhere or just keep it in mind for later in case I need
>>> it?
>>>
>> Yes, that will be helpful if you are relying on users to enter
>> properly formatted dates. Sometimes they make mistakes ...
>
> Users make mistakes?
>
Depends on who's telling the story ...

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.