Prev: Request.ClientCerficate
Next: UTF-8, UTF-16 - ASP VB MSSQL Server - It just doesn't work.
From: GTN170777 on 26 Feb 2008 10:56
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

From: daddywhite on 26 Feb 2008 11:51
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
From: Bob Barrows [MVP] on 26 Feb 2008 12:06
GTN170777 wrote:
> 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

--
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.


From: GTN170777 on 26 Feb 2008 12:48
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]" wrote:

> GTN170777 wrote:
> > 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
>
> --
> 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.
>
>
>
From: Bob Barrows [MVP] on 26 Feb 2008 13:42
GTN170777 wrote:
> 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.

--
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.


 | 
Pages: 1
Prev: Request.ClientCerficate
Next: UTF-8, UTF-16 - ASP VB MSSQL Server - It just doesn't work.