special characher issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

special characher issue

Author

Topic

tombino
Starting Member

26 Posts

Posted - 2008-02-05 : 19:02:14

Hi,

I have a problem doing a query in ASP. This is my query:

Dim vardate
vardate = "#" & latestdate & "#"

sSQL1 = "SELECT tbl_size.sizename, tbl_stylesize.sizeid, tbl_stylesize.rate, tbl_stylesize.changedate " & _
"FROM tbl_stylesize " & _
"INNER JOIN tbl_size " & _
"ON tbl_stylesize.sizeid = tbl_size.sizeid " & _
"WHERE tbl_stylesize.active = -1 " & _
" AND FORMAT (vardate, "mm/dd/yyyy" ) = tbl_stylesize.changedate " & _
" AND tbl_stylesize.styleid = " & arrItem & _
" ORDER BY tbl_size.sizeid"

The problem is the " (ando may be also /)in the FORMAT date. It reconize it like a end of statement. I tryed to write

"" & mm & "/" & dd & "/" & yyyy & """ or
Chr(34) & mm/dd/yyyy & Chr(34)

but anything is working.
Can you please help me?

Best Regards,
Tom

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2008-02-06 : 06:23:55

Can you printout what you are getting in the sSQL1 code....i.e. with (test) data filled in?

tombino
Starting Member

26 Posts

Posted - 2008-02-06 : 17:18:38

Yes, with the first example with "mm/dd/yyyy":

The website cannot display the page

with """ & mm & "/" & dd & "/" & yyyy & """:

Microsoft VBScript runtime error '800a000d'
Type mismatch: '[string: "" & mm & "]'

/catalog2.asp, line 994

with Chr(34) & mm/dd/yyyy & Chr(34):

Microsoft VBScript runtime error '800a000d'
Type mismatch: '[string: "" & mm & "]'

/catalog2.asp, line 994

I tryed also """&mm/dd/yyyy&""" but come ou the same error.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-02-06 : 17:26:18

I *think* you have a script concatination issue (untested):

quote:
Originally posted by tombino

sSQL1 = "SELECT tbl_size.sizename, tbl_stylesize.sizeid, tbl_stylesize.rate, tbl_stylesize.changedate " & _
"FROM tbl_stylesize " & _
"INNER JOIN tbl_size " & _
"ON tbl_stylesize.sizeid = tbl_size.sizeid " & _
"WHERE tbl_stylesize.active = -1 " & _
" AND " & FORMAT (vardate, "yyyy-mm-dd" ) & " = tbl_stylesize.changedate " & _
" AND tbl_stylesize.styleid = " & arrItem & _
" ORDER BY tbl_size.sizeid"

tombino
Starting Member

26 Posts

Posted - 2008-02-06 : 17:30:18

vardate and changedate are the same date. I test the same quary in SQL server 2005 and work, but as soon that I put it in the ASP page doesnt work.

If I try to write the quary in one line with "mm/dd/yyyy":

Microsoft VBScript compilation error '800a0401'

Expected end of statement

/catalog2.asp, line 1004

sSQL1 = "SELECT tbl_size.sizename, tbl_stylesize.sizeid, tbl_stylesize.rate, tbl_stylesize.changedate FROM tbl_stylesize INNER JOIN tbl_size ON tbl_stylesize.sizeid = tbl_size.sizeid WHERE tbl_stylesize.active = -1 AND FORMAT(#05/02/2008#, "mm/dd/yyyy") = tbl_stylesize.changedate AND tbl_stylesize.styleid = 4451 ORDER BY tbl_size.sizeid"
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------^

tombino
Starting Member

26 Posts

Posted - 2008-02-06 : 17:34:22

Hi Lamprey,

I tryed your solution and come out this:

Microsoft VBScript runtime error '800a000d'
Type mismatch: 'FORMAT'

/catalog2.asp, line 1002

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-02-06 : 18:31:24

Well, you should format your date in yyy-mm-dd format, assuming you are comparing to an actual date.

I've not done much with VBScript in a long time, you might want to ask on a VBScript forum as your script is wrong. But, maybe this will help? [url]http://classicasp.aspfaq.com/date-time-routines-manipulation/can-i-make-vbscript-format-dates-for-me.html[/url]

EDIT: Humm, that doesn't seem to be working correctly: http://classicasp.aspfaq.com/date-time-routines-manipulation/can-i-make-vbscript-format-dates-for-me.html

tombino
Starting Member

26 Posts

Posted - 2008-02-06 : 19:06:46

I ask a VBScript forum. When I find a solution I will post it.

Thanks for the help!

Best Regards,
Tom

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-13 : 08:17:36

quote:
Originally posted by Lamprey

Well, you should format your date in yyy-mm-dd format, assuming you are comparing to an actual date.

I've not done much with VBScript in a long time, you might want to ask on a VBScript forum as your script is wrong. But, maybe this will help? [url]http://classicasp.aspfaq.com/date-time-routines-manipulation/can-i-make-vbscript-format-dates-for-me.html[/url]

EDIT: Humm, that doesn't seem to be working correctly: http://classicasp.aspfaq.com/date-time-routines-manipulation/can-i-make-vbscript-format-dates-for-me.html

YYYYMMDD is the true universal format

set dateformat dmy
declare @t datetime
set @t='2008-04-11'
select @t
set @t='20080411'
select @t

and see the difference

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources