1/1/1900

[middletree]

When a form field (input type=text) is left blank, it stores 1/1/1900 into
the field in the SQL Server DB. I'm not sure why.

The field in SQL Server is a date/time field. I could make it varchar or
something similar, but I will need to do math with these dates later in the
project.

I tried converting the thing to date by doing this:

strTempSolution = CDate(Trim(Request.Form("TempSolution")))
or
strTempSolution = DateValue(Trim(Request.Form("TempSolution")))

but I got type mismatch errors pointing to those very lines:
=======================
Type mismatch: 'DateValue'

/CloseTicket.asp, line 124
=======================

What's weird is that the DateValue seems to work fine in another part of the
code that's unrelated to this, but is on the same ASP page. The difference
is that in that field, I prefill the text field with Now, which has both a
date and time. In the above-referenced field, however, the user types the
date in via this format: mm/dd/yyyy.

This works fine when a date has been entered into the web form. It's only
when blank that it inserts 1/1/1900 into the DB. When I say it works fine, I
am talking about without the DateValue or Cdate or any type of attempted
conversion. I'd like it to enter NULL, or not enter anything.

 

[middletree]

I should add that when I do a response.write of the SQL statement, I get:

UPDATE TKT_Ticket SET StatusID = '1',DefectNum = '' ,Files=
'',Contact_CustID = '641' ,Current_Source =
'E',LastModified='12/29/2004',CustTicketID = '333', TempSolution='',
ProbIdentified = '12/02/2004', SolutionIdentified='12/03/2004',
SolutionImplemented='12/04/2004',RootCause = '256' WHERE TKT_TicketID =
'16184'

The key is this part:
TempSolution='',

It demonstrates that it is entering a blank value. So up to that point, it
is working correctly.

 

[middletree]

Yeah, I did that as you wrote it, and it works. However, now I have a
follow-up problem.

Here's the SQL Statement in my ASP code:

Set RS2 = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT
Contact_CustID,Current_Source,LastModified,Files,CustomerCode, "
strSQL = strSQL & "TempSolution, ProbIdentified, SolutionIdentified,
SolutionImplemented "
strSQL = strSQL & "FROM TKT_Ticket "
strSQL = strSQL & "WHERE TKT_TicketID = '"&strTicketID&"'"

RS2.Open strSQL, objConnection

strContactCustID = RS2("Contact_CustID")
strCurrSource = RS2("Current_Source")
strLM = RS2("LastModified")
strExistingFiles = RS2("Files")
strCallerCustomerCode = RS2("CustomerCode")
* strTempSolution = RS2("TempSolution")
strProbIdentified = RS2("ProbIdentified")
strSolutionIdentified = RS2("SolutionIdentified")
strSolutionImplemented = RS2("SolutionImplemented")

Here's the error I get on a page which is supposed to display this data:

Microsoft VBScript runtime error '800a005e'

Invalid use of Null

/AddToTicket.asp, line 39

As you can imagine, line 39 is the one with the asterisk above. Its value is
null in the DB right now. The other three fields right after it as defined
exactly the same way, but they have dates in there.

 

[middletree]

Yes, I got that after I posted that.

Did you see my response and follow-up question in my other post?

 

[middletree]

Well, I went to http://www.aspfaq.com/show.asp?id=2150 and tried that, and
here's my query:

(deleted Select and other fieldnames)
strSQL = strSQL & "ISNULL(TempSolution,' '), "

And I get this error:
===================================
ODB.Recordset error '800a0cc1'

Item cannot be found in the collection corresponding to the requested name
or ordinal.

/AddToTicket.asp, line 39
===================================

where line 39 is strTempSolution = RS2("TempSolution")

 

[Aaron [SQL Server MVP]]

You need to (a) give it an alias if you apply formulae to the column, or
else (b) refer to it by ordinal position instead of name.

For suggestion (a),

strSQL = strSQL & "TempSolution = ISNULL(TempSolution, ' '),"

--
http://www.aspfaq.com/
(Reverse address to reply.)

 

[middletree]

OK, that worked. Thanks.

You need to (a) give it an alias if you apply formulae to the column, or
else (b) refer to it by ordinal position instead of name.

For suggestion (a),

strSQL = strSQL & "TempSolution = ISNULL(TempSolution, ' '),"

--
http://www.aspfaq.com/
(Reverse address to reply.)




back

 

[middletree]

You are correct; Aaron is the man.

Actually, I changed it from Aaron's suggestion:

"TempSolution = ISNULL(TempSolution, ' '),"

to:

"ISNULL(TempSolution, ' ') AS TempSolution,"

Mainly because it it consistent with other stuff I have in my queries.
Seems to work fine.

 

[jean]

to (e-mail address removed) send a message my lov

From http://search.yahoo.com/search?toggle=1&ei=UTF-8&fr=FP-tab-web-t&[email protected]

Posted via DevelopmentNow.com Group
http://www.developmentnow.com