MS SQL ASP Update timestamp

[GTN170777]

HELP!!!!

I'm really pulling my hair out with this one, I have a form, which currently
does an update, built with dreamwaevers update behaviour (I Know i Know) What
I am trying to do is capture the date and time of the update, so my table
contains a column JBCLNewAccountDate, which i hasve set as a timestamp??

my form contains a number of form fields including a hidden field called
NewAccountdate with a value of =NOW(). I'm trying to pass this field to the
JBCLNewAccountDate during the update...

but when i do this, i get the following error -

Microsoft OLE DB Provider for SQL Server error '80040e07'

Operand type clash: text is incompatible with timestamp

/admin/ppupgradeaccountsettings.asp, line 44


where line 44 is -

MM_editCmd.Execute

The update statement reads --


<%
Dim MM_editAction
MM_editAction = CStr(Request.ServerVariables("SCRIPT_NAME"))
If (Request.QueryString <> "") Then
MM_editAction = MM_editAction & "?" & Server.HTMLEncode(Request.QueryString)
End If

' boolean to abort record edit
Dim MM_abortEdit
MM_abortEdit = false
%>
<%
' IIf implementation
Function MM_IIf(condition, ifTrue, ifFalse)
If condition = "" Then
MM_IIf = ifFalse
Else
MM_IIf = ifTrue
End If
End Function
%>
<%
If (CStr(Request("MM_update")) = "form1") Then
If (Not MM_abortEdit) Then
' execute the update
Dim MM_editCmd

Set MM_editCmd = Server.CreateObject ("ADODB.Command")
MM_editCmd.ActiveConnection = MM_recruta2_STRING
MM_editCmd.CommandText = "UPDATE dbo.JBClient SET JBCLAddress = ?,
JBCLPhone = ?, JBCLFax = ?, JBCLEmail = ?, JBCLURL = ?, JBCLBillingContact =
?, JBCLAccountType = ?, JBCLPreviousAccount = ?, JBCLNewAccountDate = ? WHERE
JBCLID = ?"
MM_editCmd.Prepared = true
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param1", 202,
1, 500, Request.Form("address")) ' adVarWChar
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param2", 202,
1, 50, Request.Form("phone")) ' adVarWChar
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param3", 202,
1, 50, Request.Form("fax")) ' adVarWChar
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param4", 202,
1, 225, Request.Form("email")) ' adVarWChar
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param5", 202,
1, 225, Request.Form("website")) ' adVarWChar
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param6", 202,
1, 50, Request.Form("billingcontact")) ' adVarWChar
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param7", 202,
1, 10, Request.Form("upgradeaccount")) ' adVarWChar
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param8", 202,
1, 10, Request.Form("PreviousAccountType")) ' adVarWChar
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param9", 201,
1, -1, Request.Form("NewAccountdate")) ' adLongVarChar
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param10", 5, 1,
-1, MM_IIF(Request.Form("MM_recordId"), Request.Form("MM_recordId"), null)) '
adDouble
MM_editCmd.Execute
MM_editCmd.ActiveConnection.Close

' append the query string to the redirect URL
Dim MM_editRedirectUrl
MM_editRedirectUrl = "AFTERUPGRADEACCOUNT.asp"
If (Request.QueryString <> "") Then
If (InStr(1, MM_editRedirectUrl, "?", vbTextCompare) = 0) Then
MM_editRedirectUrl = MM_editRedirectUrl & "?" & Request.QueryString
Else
MM_editRedirectUrl = MM_editRedirectUrl & "&" & Request.QueryString
End If
End If
Response.Redirect(MM_editRedirectUrl)
End If
End If
%>

The form looks like ,...

<form ACTION="<%=MM_editAction%>" METHOD="POST" id="form1" name="form1">
<table border="0" cellpadding="0" cellspacing="0"
class="tablewidth576">
<tr>
<td><table border="0" cellpadding="0" cellspacing="0"
class="tablewidth576">
<tr>
<td class="postvacancyleft">Company Name:</td>
<td class="postvacancyright2"><label>
<input name="companyname" type="text"
id="companyname" value="<%=(newaccount.Fields.Item("JBCLName").Value)%>"
maxlength="50" readonly="readonly"/>
</label></td>
</tr>
<tr>
<td class="postvacancyleft">Company Type:</td>
<td class="postvacancyright2"><input
name="companytype" type="text" id="companytype"
value="<%=(newaccount.Fields.Item("JBCompanyTypeLabel").Value)%>"
maxlength="50" readonly="readonly"/></td>
</tr>
<tr>
<td class="postvacancyleft">Address:</td>
<td class="postvacancyright2"><label>
<textarea onkeypress="return taLimit()"
onkeyup="return taCount(myCounter1)" name="address" id="address" cols="45"
rows="7"
maxlength="500"><%=(newaccount.Fields.Item("JBCLAddress").Value)%></textarea>
</label></td>
</tr>
<tr>
<td class="postvacancyleft">Phone:</td>
<td class="postvacancyright2"><input name="phone"
type="text" id="phone"
value="<%=(newaccount.Fields.Item("JBCLPhone").Value)%>" maxlength="50"
/></td>
</tr>
<tr>
<td class="postvacancyleft">Fax:</td>
<td class="postvacancyright2"><input name="fax"
type="text" id="fax" value="<%=(newaccount.Fields.Item("JBCLFax").Value)%>"
maxlength="50" /></td>
</tr>
<tr>
<td class="postvacancyleft">Email:</td>
<td class="postvacancyright2"><input name="email"
type="text" id="email"
value="<%=(newaccount.Fields.Item("JBCLEmail").Value)%>" maxlength="225"
/></td>
</tr>
<tr>
<td class="postvacancyleft">Website:</td>
<td class="postvacancyright2"><input name="website"
type="text" id="website"
value="<%=(newaccount.Fields.Item("JBCLURL").Value)%>" maxlength="225" /></td>
</tr>
<tr>
<td class="postvacancyleft">Billing Contact:</td>
<td class="postvacancyright2"><input
name="billingcontact" type="text" id="billingcontact"
value="<%=(newaccount.Fields.Item("JBCLBillingContact").Value)%>"
maxlength="50" /></td>
</tr>
<tr>
<td class="postvacancyleft"> </td>
<td class="postvacancyright2"><label></label></td>
</tr>
<tr>
<td class="postvacancyleft">Upgrade Account</td>
<td class="postvacancyright2"><label>
<select name="upgradeaccount" id="upgradeaccount">
<option value="PP Client" <%If (Not
isNull((newaccount.Fields.Item("JBCLAccountType").Value))) Then If ("PP
Client" = CStr((newaccount.Fields.Item("JBCLAccountType").Value))) Then
Response.Write("selected=""selected""") : Response.Write("")%>>PayPal
Client</option>
<option value="Occ User" <%If (Not
isNull((newaccount.Fields.Item("JBCLAccountType").Value))) Then If ("Occ
User" = CStr((newaccount.Fields.Item("JBCLAccountType").Value))) Then
Response.Write("selected=""selected""") : Response.Write("")%>>Occasional
User</option>
<%
While (NOT accounttypes.EOF)
%>
<option
value="<%=(accounttypes.Fields.Item("JBATValue").Value)%>" <%If (Not
isNull((newaccount.Fields.Item("JBCLAccountType").Value))) Then If
(CStr(accounttypes.Fields.Item("JBATValue").Value) =
CStr((newaccount.Fields.Item("JBCLAccountType").Value))) Then
Response.Write("selected=""selected""") : Response.Write("")%>

<%=(accounttypes.Fields.Item("JBATDisplay").Value)%></option>

<%
accounttypes.MoveNext()
Wend
If (accounttypes.CursorType > 0) Then
accounttypes.MoveFirst
Else
accounttypes.Requery
End If
%>
</select>
</label></td>
</tr>
<tr>
<td class="postvacancyleft"><h1><span
class="postvacancyright2">
<input name="PreviousAccountType" type="hidden"
id="PreviousAccountType"
value="<%=(newaccount.Fields.Item("JBCLAccountType").Value)%>" />
</span><span class="postvacancyright2">
<input name="NewAccountdate" type="hidden"
id="NewAccountdate" value="=NOW()" />
</span></h1></td>
<td class="postvacancyright2"> </td>
</tr>
<tr>
<td class="postvacancyleft"></td>
<td class="sendbar"><img src="../images/UPGRADE.jpg"
alt="Submit" width="73" height="20"
onclick="formButtonFever('form1','submit')" /></td>
</tr>
</table></td>
</tr>
</table>




<input type="hidden" name="MM_update" value="form1" />
<input type="hidden" name="MM_recordId" value="<%=
newaccount.Fields.Item("JBCLID").Value %>" />
</form>

any idea what I am doing wrong??
Thanks

 

[daddywhite]

Looks like you passing in the date as a string and it doesnt like that
- easiest way to fix it is let SQL do your date work for you:

JBCLPreviousAccount = ?

is replaced with

JBCLPreviousAccount = getdate()
OR
JBCLPreviousAccount = NOW()

getdate is for sqlserver databasem NOW() is for the access database,

Regards
Dave

 

[Bob Barrows [MVP]]

HELP!!!!

I'm really pulling my hair out with this one, I have a form, which
currently does an update, built with dreamwaevers update behaviour (I
Know i Know) What I am trying to do is capture the date and time of
the update, so my table contains a column JBCLNewAccountDate, which i
hasve set as a timestamp??

my form contains a number of form fields including a hidden field
called NewAccountdate with a value of =NOW(). I'm trying to pass this
field to the JBCLNewAccountDate during the update...

but when i do this, i get the following error -

Microsoft OLE DB Provider for SQL Server error '80040e07'

Operand type clash: text is incompatible with timestamp

/admin/ppupgradeaccountsettings.asp, line 44

Read all of these and then come back to us if you have any questions:
http://www.aspfaq.com/show.asp?id=2313 vbscript
http://www.aspfaq.com/show.asp?id=2040 help with dates
http://www.aspfaq.com/show.asp?id=2260 dd/mm/yyy confusion
http://www.aspfaq.com/show.asp?id=2023 date delimiters

 

[GTN170777]

Thanks Guys, I found a sneaky way around this, i created a recordset --

SELECT (GETDATE()) as Date

And then inserted the value into the hidden field to do the update.

this seems to work....

Thanks again

 

[Bob Barrows [MVP]]

Thanks Guys, I found a sneaky way around this, i created a recordset
--

SELECT (GETDATE()) as Date

And then inserted the value into the hidden field to do the update.

this seems to work....

As far as it goes ... if you always want to insert the current datetime,
why pass it around? Just incorporate the GETDATE() function into your
insert/update statement.