convert data type varchar to datetime.

[ooiooipig]

Hi everyone, can anyone advise me on how to solve the following problem??

I need to update the database, however, after I write the following codes, there an error saying
--------------------------------------------------------------------------------
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type varchar to datetime.

-------------------------------------------------------------------------------

here's how my update page look:
-------------------------------------------------------------------------------
<%
vreqType = Request("vreqType")
vReqtrEmail = Request("vReqtrEmail")
vReqNo = Request("ReqNo")
vAppnId = Request("vAppnId")
vreqTitle = replace(trim(Request("vreqTitle")),"'","''")
vDesiredDt = month(Request("vDesiredDt")) & "/" & day(Request("vDesiredDt")) & "/" & year(Request("vDesiredDt"))
vReqtr = replace(trim(Request("vReqtr")),"'","''")
vContactNo = Request("vContactNo")
vDept = replace(trim(Request("vDept")),"'","''")
vRC = Request("vRC")
vPriority = Request("vPriority")
vfuncReq = server.URLencode(trim(Request("vfuncReq")))
vFreqVol = server.URLencode(trim(Request("vFreqVol")))
vSavings = Request("vSavings")
vRevenue = Request("vRevenue")
vBenefit = server.URLencode(trim(Request("vBenefit")))
vTester = replace(trim(Request("vTester")),"'","''")
vTesterContact = Request("vTesterContact")
vApprovOfficer = replace(trim(Request("vApprovOfficer")),"'","''")
vApprovEmail = Request("vApprovEmail")
vStatus = Request("vStatus")

'Format day
strDay = right("0" & cstr(day(date())),2)
'Format Month
strMonth = right("0" & cstr(month(date())),2)
'Format Request Date
ReqDt = strMonth & "/" & strDay & "/" & cstr(year(date()))


sql="EXEC UpdateRequest '" & ("vAppnId") & "','" &_
("vreqTitle") & "','" &_
("ReqDt") & "','" &_
("vDesiredDt") & "','" &_
("vReqtr") & "','" &_
("vReqtrEmail") & "','" &_
("vContactNo") & "','" &_
("vDept") & "','" &_
("vRC") & "','" &_
("vPriority") & "','" &_
("vfuncReq") & "','" &_
("vFreqVol") & "','" &_
("vSavings") & "','" &_
("vRevenue") & "','" &_
("vBenefit") & "','" &_
("vTester") & "','" &_
("vTesterContact") & "','" &_
("vApprovOfficer") & "','" &_
("vApprovEmail") & "','" &_
("vStatus") & "'"
MSCS.execute(sql)

%>

-------------------------------------------------------------------------------

I think there's an error in the DesiredDt, however, i really have a problem finding a way to solve. Anyone can help please??

thanks!!

[david7777]

Have you tried the code leaving out just the "("vDesiredDt") & "','" &_" part to see if it is the problem? I woud do it for you, but dont have MS SQL here at work...:confused:

Also - give an example of the data to be passed using the vDesiredDt queryString. (so i can see the format it is sent in) ie: 05.12.2002

[ooiooipig]

hi david, if i take out the desiredDt part, it still show me the error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type varchar to datetime.


I'm not too sure how to solve it but it's only the DesiredDt and ReqDt using the data type "datetime" in the database.

hope you can help. :rolleyes:

[david7777]

As wammy suggested in your "varchar to money conversion" thread, try the following:


sql="EXEC UpdateRequest '" & ("vAppnId") & "','" &_
("vreqTitle") & "','" &_
("ReqDt") & "','" &_
("vDesiredDt") & "','" &_
("vReqtr") & "','" &_
("vReqtrEmail") & "','" &_
("vContactNo") & "','" &_
("vDept") & "','" &_
("vRC") & "','" &_
("vPriority") & "','" &_
("vfuncReq") & "','" &_
("vFreqVol") & "','" &_
("vSavings") & "','" &_
("vRevenue") & "','" &_
("vBenefit") & "','" &_
("vTester") & "','" &_
("vTesterContact") & "','" &_
("vApprovOfficer") & "','" &_
("vApprovEmail") & "','" &_
("vStatus") & "'"
response.write(sql)
response.end

MSCS.execute(sql)


then post the results here so/we can look at it...

[glenngv]

can we look at the code for UpdateRequest?

btw, are you and twocool in the same project? you have similar codes and variables. :D

[ooiooipig]

hi david, the output will look like this:

EXEC UpdateRequest 'vAppnId','vreqTitle','ReqDt','vDesiredDt','vReqtr','vReqtrEmail','vContactNo','vDept','vRC','vPrior ity','vfuncReq','vFreqVol','vSavings','vRevenue','vBenefit','vTester','vTesterContact','vApprovOffic er','vApprovEmail','vStatus'

[ooiooipig]

Hi glenngv, yah twocool and me are colleagues.

you want to see the code in UpdateRequest?? It's in a stored procedure...

here:

CREATE PROCEDURE UpdateRequest
@ReqNo char,
@vAppnId char,
@vreqTitle varchar(100),
@ReqDt datetime,
@vDesiredDt datetime,
@vReqtr varchar(50),
@vReqtrEmail varchar(50),
@vContactNo nchar(10),
@vDept varchar(50),
@vRC varchar(10),
@vPriority varchar(6),
@vfuncReq varchar(1000),
@vFreqVol varchar(1000),
@vSavings money,
@vRevenue money,
@vBenefit varchar(1000),
@vTester varchar(50),
@vTesterContact nchar(10),
@vApprovOfficer varchar(50),
@vApprovEmail varchar(50),
@vStatus char

AS
BEGIN
update Requests set
AppnId=@vAppnId,
reqTitle=@vreqTitle,
ReqDt=@ReqDt,
DesiredDt=@vDesiredDt,
Reqtr=@vReqtr,
ReqtrEmail=@vReqtrEmail,
ContactNo=@vContactNo,
Dept=@vDept,
RC=@vRC,
Priority=@vPriority,
funcReq=@vfuncReq,
FreqVol=@vFreqVol,
Savings=@vSavings,
Revenue=@vRevenue,
Benefit=@vBenefit,
Tester=@vTester,
TesterContact=@vTesterContact,
ApprovOfficer=@vApprovOfficer,
ApprovEmail=@vApprovEmail,
ReqStatus=@vStatus
where ReqNo=@ReqNo
END
GO

[glenngv]

EXEC UpdateRequest 'vAppnId','vreqTitle','ReqDt','vDesiredDt','vReqtr
','vReqtrEmail','vContactNo','vDept','vRC','vPrior
ity','vfuncReq','vFreqVol','vSavings','vRevenue','
vBenefit','vTester','vTesterContact','vApprovOffic
er','vApprovEmail','vStatus'

are they real values? obviously, you are not passing dates in ReqDt and vDesiredDt fields

[david7777]

Originally posted by ooiooipig
hi david, the output will look like this:

EXEC UpdateRequest 'vAppnId','vreqTitle','ReqDt','vDesiredDt','vReqtr','vReqtrEmail','vContactNo','vDept','vRC','vPrior ity','vfuncReq','vFreqVol','vSavings','vRevenue','vBenefit','vTester','vTesterContact','vApprovOffic er','vApprovEmail','vStatus'
Like glenngv said:

Surely you are passing values to the fields? Like 'vDesiredDt' might be 03-05-2003. And all the other fields would have real values?? At the moment, if the sql code is as you stated above, it wont work, because you are not passing a propper value, but merely the actual field name...

Try the following:

sql="EXEC UpdateRequest '" & vAppnId & "','" &_
vreqTitle & "','" &_
ReqDt & "','" &_
vDesiredDt & "','" &_
vReqtr & "','" &_
vReqtrEmail & "','" &_
vContactNo & "','" &_
vDept & "','" &_
vRC & "','" &_
vPriority & "','" &_
vfuncReq & "','" &_
vFreqVol & "','" &_
vSavings & "','" &_
vRevenue & "','" &_
vBenefit & "','" &_
vTester & "','" &_
vTesterContact & "','" &_
vApprovOfficer & "','" &_
vApprovEmail & "','" &_
vStatus & "'"
response.write(sql)
response.end


Then post the results again :)

[ooiooipig]

the values are being passed from the previous page where they do the editing of the form...

I've used a form in the previous page to pass all the values to the action page where action is taken to update the database... and in the action page, i've requested the values to be passed into the database. is it correct?? I think should be right?? I'm not too sure about that.. please advise... thanks!!

[david7777]

Thats sounds right.

Assuming that the user inputs everything in the correct format, and the values are posted to the action page properly, then everything should be fine...

Try what I said in my last post - First change your sql code to what i wrote in my last post, then run the application:

Fill in the form with test data, then click the submit button.
With the changed code in the action page, the sql code should display. Post the results to us, so we can check your sql code with actual values in it - not field names. This will make life a lot easier for us :)

[ooiooipig]

hi david, i've done what you ask me to do... fill in the fields with the word test data and add in the response.write(sql) and response.end

however, the values doesnt seem to have passed over to the action page as it shows me this result in the action page.

EXEC UpdateRequest 'vAppnId','vreqTitle','ReqDt','vDesiredDt','vReqtr','vReqtrEmail','vContactNo','vDept','vRC','vPrior ity','vfuncReq','vFreqVol','vSavings','vRevenue','vBenefit','vTester','vTesterContact','vApprovOffic er','vApprovEmail','vStatus'

[david7777]

It seems that you are still using your old SQL code though...

Copy and paste this code exactly:

sql="EXEC UpdateRequest '" & vAppnId & "','" &_
vreqTitle & "','" &_
ReqDt & "','" &_
vDesiredDt & "','" &_
vReqtr & "','" &_
vReqtrEmail & "','" &_
vContactNo & "','" &_
vDept & "','" &_
vRC & "','" &_
vPriority & "','" &_
vfuncReq & "','" &_
vFreqVol & "','" &_
vSavings & "','" &_
vRevenue & "','" &_
vBenefit & "','" &_
vTester & "','" &_
vTesterContact & "','" &_
vApprovOfficer & "','" &_
vApprovEmail & "','" &_
vStatus & "'"
response.write(sql)
response.end


Note that ("vreqTitle") has changed to just vreqTitle. And the same with all the others...

[ooiooipig]

oh okie!! i see... I've copied your coding and paste inside the file and this time the result is as follow:

EXEC UpdateRequest 'testdata','test data','04/24/2003','4/30/2003','test data','pig@hotmail.com','64235435 ','test data','test data','Low','test+data','','','','','','','test data','test data@hotmail.com','NEW '

however, when i command out the response.write and response.end

the problem of conversion is there again... please advise me on what to do next..
thanks!!

[david7777]

Aha! :D
EXEC UpdateRequest 'testdata','test data','04/24/2003','4/30/2003','test data','pig@hotmail.com','64235435 ','test data','test data','Low','test+data','','','','','','','test data','test data@singtel.com','NEW '
Notice how the first date is a diffirent format to the second...
Im not sure what type of input you are using for the user to enter a date. Like a text box? It is best to use combo boxes instead:

Put the following in your form for better date selection: (Use it for all dates, just changing the names of the elements)


<form action="" name="formName" id="formName">
<select name="vDesiredDtday">
<option value="01" SELECTED>1</option>
<option value="02">2</option>
<option value="03">3</option>
<option value="04">4</option>
<option value="05">5</option>
<option value="06">6</option>
<option value="07">7</option>
<option value="08">8</option>
<option value="09">9</option>
<option value="10">10</option>
<option value="11">11</option>
<option value="12">12</option>
<option value="13">13</option>
<option value="14">14</option>
<option value="15">15</option>
<option value="16">16</option>
<option value="17">17</option>
<option value="18">18</option>
<option value="19">19</option>
<option value="20">20</option>
<option value="21">21</option>
<option value="22">22</option>
<option value="23">23</option>
<option value="24">24</option>
<option value="25">25</option>
<option value="26">26</option>
<option value="27">27</option>
<option value="28">28</option>
<option value="29">29</option>
<option value="30">30</option>
<option value="31">31</option>
</select>
<select name="vDesiredDtmnth">
<option value="01" SELECTED>January</option>
<option value="02">February</option>
<option value="03">March</option>
<option value="04">April</option>
<option value="05">May</option>
<option value="06">June</option>
<option value="07">July</option>
<option value="08">August</option>
<option value="09">September</option>
<option value="10">October</option>
<option value="11">November</option>
<option value="12">December</option>
</select>
<select name="vDesiredDtyr">
<option value="1980" SELECTED>1980</option>
<option value="1981">1981</option>
<option value="1982">1982</option>
<option value="1983">1983</option>
<option value="1984">1984</option>
<option value="1985">1985</option>
<option value="1986">1986</option>
<option value="1987">1987</option>
<option value="1988">1988</option>
<option value="1989">1989</option>
<option value="1990">1990</option>
<option value="1991">1991</option>
<option value="1992">1992</option>
<option value="1993">1993</option>
<option value="1994">1994</option>
<option value="1995">1995</option>
<option value="1996">1996</option>
<option value="1997">1997</option>
<option value="1998">1998</option>
<option value="1999">1999</option>
<option value="2000">2000</option>
<option value="2001">2001</option>
<option value="2002">2002</option>
<option value="2003">2003</option>
<option value="2004">2004</option>
<option value="2005">2005</option>
<option value="2006">2006</option>
<option value="2007">2007</option>
<option value="2008">2008</option>
<option value="2009">2009</option>
<option value="2010">2010</option>
</select>
</form>


Now you will do the following in you asp page:

vDesiredDt = request("vDesiredDtyr") & "/" & request("vDesiredDtmnth") & "/" & request("vDesiredDtday")

This will elliminate formatting problems... It will give the format of yyyy/mm/dd... im not too sure on what the format needs to be for the database - maybe someone else knows??

So do the same for ReqDt to ensure correct format...

Anyway - once that is done, and you know the right format to use for your dates, everything should be ok...
:)

[ooiooipig]

erm... david, I've formatted the date to become mm/dd/yyyy to be inserted into the database...

vDesiredDt = month(Request("vDesiredDt")) & "/" & day(Request("vDesiredDt")) & "/" & year(Request("vDesiredDt"))

I've got a script for the date selection. and i need to use it... so i cant change it to the way you have said.

when inserting the date, i format it the same way as above but now for update, it cant work... :confused:

is there any other way beside using the option select??
please advise...
thanks!!

[david7777]

ok - no problem
when inserting the date, i format it the same way as above but now for update, it cant work...
is there any other way beside using the option select??
Im not sure what you mean??

[ooiooipig]

hm... i mean when i do an insert before this, there isn't any error. However when doing the update now, it prompts me an error saying must convert the data type. i dont know how come when doing insert, there's no problem but when update, there's error... :confused:

[david7777]

That is very strange... Can you post the sql code you are using for the insert, plus a response.write of it to see what is being put in the statement... (Like you did before with the update)

[ooiooipig]

ok!! i've solved the problem (same as the other thread "convert varchar to money)!! thanks for all your help!! you guys have been great!! :thumbsup: