Hi i have this sign up page
backupserver1.123-backup.com:8080/client/signup.cfm
When i enter all the details i get this error in the next cfm page call insert-user.cfm
Error as follows Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Operand type clash: text is incompatible with tinyint
I think the problem something to with a column not being in the correct context but i' am not sure what i need it do be
Lee
Can you post the code for the insert-user.cfm page?
Or catch the sql query that is being used and post that for inspection?
Hi Thank you for you reply,
here is the the insert-user.cfm page, I 'am not sure how to catch the sql query sorry.
<cfif isdefined ('form.email')>
<!--- Set Defaults for New User - if not passed by form --->
<cfparam name="form.LanguageID" default="19" >
<cfparam name="form.TypeID" default="1" >
<cfparam name="form.ClientTypeID" default="1" >
<cfparam name="form.Quota" default="104857600" >
<cfparam name="form.EnableMSSQL" default="On" >
<cfparam name="form.EnableMSExchange" default="On" >
<cfparam name="form.EnableOracle" default="On" >
<cfparam name="form.EnableLotusNotes" default="On" >
<cfparam name="form.EnableLotusDomino" default="On" >
<cfparam name="form.EnableMySQL" default="On" >
<cfparam name="form.EnableInFileDelta" default="On" >
<cfparam name="form.EnableShadowCopy" default="On">
<cfparam name="form.EnableExchangeMailbox" default="On" >
<cfparam name="form.ExchangeMailboxQuota" default="1" >
<cfparam name="form.TimezoneID" default="112">
<cfparam name="form.Bandwidth" default="0" >
<!--- Convert "Enable" Values to Y or N --->
<cfif #Form.EnableMSSQL# eq "on"><cfset request.EnableMSSQL = "Y"><cfelse><cfset request.EnableMSSQL ="N"></cfif>
<cfif #Form.EnableMSExchange# eq "on"><cfset request.EnableMSExchange = "Y"><cfelse><cfset request.EnableMSExchange = "N"></cfif>
<cfif #Form.EnableOracle# eq "on"><cfset request.EnableOracle = "Y"><cfelse><cfset request.EnableOracle = "N"></cfif>
<cfif #Form.EnableLotusNotes# eq "on"><cfset request.EnableLotusNotes = "Y"><cfelse><cfset request.EnableLotusNotes = "N"></cfif>
<cfif #Form.EnableLotusDomino# eq "on"><cfset request.EnableLotusDomino = "Y"><cfelse><cfset request.EnableLotusDomino = "N"></cfif>
<cfif #Form.EnableMySQL# eq "on"><cfset request.EnableMySQL = "Y"><cfelse><cfset request.EnableMySQL = "N"></cfif>
<cfif #Form.EnableInFileDelta# eq "on"><cfset request.EnableInFileDelta = "Y"><cfelse><cfset request.EnableInFileDelta = "N"></cfif>
<cfif #Form.EnableShadowCopy# eq "on"><cfset request.EnableShadowCopy="Y"><cfelse><cfset request.EnableShadowCopy="N"></cfif>
<cfif #Form.EnableExchangeMailbox# eq "on"><cfset request.EnableExchangeMailbox = "Y"><cfelse><cfset request.EnableExchangeMailbox = "N"></cfif>
<!--- GetLanguage --->
<cfquery name="qLanguage" datasource="#request.DSN#">
Select LanguageCode, LanguageName from Language
where LanguageID = #form.LanguageID#
</cfquery>
<!--- GetTimeZone --->
<cfquery name="qTimeZone" datasource="#request.DSN#">
Select TimeZone from TimeZone
where TimeZoneID = #form.TimeZoneID#
</cfquery>
<!--- GetType --->
<cfquery name="qType" datasource="#request.DSN#">
Select TypeID, Type from Type
Where TypeID = #form.TypeID#
</cfquery>
<!--- GetType --->
<cfquery name="qClientType" datasource="#request.DSN#">
Select ClientTypeID, ClientType from ClientType
where ClientTypeID = #form.ClientTypeID#
</cfquery>
<cfquery name="InsertUser" datasource="#request.dsn#">
BEGIN TRANSACTION
SET NOCOUNT ON
Insert into UserDetails(
<!---LoginName, --->
Password,
Alias,
LanguageID,
TypeID,
ClientTypeID,
<!---AdGroupID, --->
Quota,
<!---UserHome, --->
EnableMSSQL,
EnableMSExchange,
EnableOracle,
EnableLotusNotes,
EnableLotusDomino,
EnableMySQL,
EnableInFileDelta,
EnableShadowCopy,
EnableExchangeMailbox,
ExchangeMailboxQuota,
TimezoneID,
Bandwidth,
VersionID,
CreateDate
<!---,Notes, --->
<!---Email--->
)
Values
(
<!---'#Form.LoginName#', --->
'#Form.Password#',
'#Form.Alias#',
#form.LanguageID#,
#form.TypeID#,
#form.ClientTypeID#,
<!---#form.AdGroupID#,--->
#form.Quota#,
<!---UserHome, --->
<cfif #Form.EnableMSSQL# eq "on">1<cfelse>0</cfif>,
<cfif #Form.EnableMSExchange# eq "on">1<cfelse>0</cfif>,
<cfif #Form.EnableOracle# eq "on">1<cfelse>0</cfif>,
<cfif #Form.EnableLotusNotes# eq "on">1<cfelse>0</cfif>,
<cfif #Form.EnableLotusDomino# eq "on">1<cfelse>0</cfif>,
<cfif #Form.EnableMySQL# eq "on">1<cfelse>0</cfif>,
<cfif #Form.EnableInFileDelta# eq "on">1<cfelse>0</cfif>,
<cfif #Form.EnableShadowCopy# eq "on">1<cfelse>0</cfif>,
<cfif #Form.EnableExchangeMailbox# eq "on">1<cfelse>0</cfif>,
#form.ExchangeMailboxQuota#,
#Form.TimezoneID#,
#form.Bandwidth# ,
#form.VersionID#,
#createodbcdatetime(now())#
<!---,'#form.Notes#', --->
<!---'#Form.Email#' --->
)
SET NOCOUNT OFF
SELECT @@Identity AS 'NewUserID'
COMMIT TRANSACTION
</cfquery>
<cfquery name="InsertContact" datasource="#request.dsn#">
Insert into UserContacts(
UserID,
ContactName,
ContactEmail
)
Values
(
#InsertUser.NewUserID#,
'#Form.Alias#',
'#Form.Email#'
)
</cfquery>
<cfset request.NewLoginName = "CD#InsertUser.NewUserID#">
<cfhttp method="get" url="AddUser.do" >
<cfhttpparam name="SysUser" type="url" value="#request.SysUser#">
<cfhttpparam name="SysPwd" type="url" value="#request.SysPwd#">
<cfhttpparam name="LoginName" type="url" value="#request.NewLoginName#">
<cfhttpparam name="Password" type="url" value="#form.Password#">
<cfhttpparam name="Alias" type="url" value="#form.Alias#">
<cfhttpparam name="Language" type="url" value="#qLanguage.LanguageCode#">
<cfhttpparam name="Type" type="url" value="#qType.Type#">
<cfhttpparam name="ClientType" type="url" value="#qClientType.ClientType#">
<!---<cfhttpparam name="AdGroup" type="url" value="">--->
<cfhttpparam name="Quota" type="url" value="#form.Quota#">
<cfhttpparam name="UserHome" type="url" value="#request.UserHome#">
<cfhttpparam name="EnableMSSQL" type="url" value="#request.EnableMSSQL#">
<cfhttpparam name="EnableMSExchange" type="url" value="#request.EnableMSExchange#">
<cfhttpparam name="EnableOracle" type="url" value="#request.EnableOracle#">
<cfhttpparam name="EnableLotusNotes" type="url" value="#request.EnableLotusNotes#">
<cfhttpparam name="EnableLotusDomino" type="url" value="#request.EnableLotusDomino#">
<cfhttpparam name="EnableMySQL" type="url" value="#request.EnableMySQL#">
<cfhttpparam name="EnableInFileDelta" type="url" value="#request.EnableInFileDelta#">
<cfhttpparam name="EnableShadowCopy" type="url" value="#request.EnableShadowCopy#">
<cfhttpparam name="EnableExchangeMailbox" type="url" value="#request.EnableExchangeMailbox#">
<cfhttpparam name="ExchangeMailboxQuota" type="url" value="#form.ExchangeMailboxQuota#">
<!--- Don't user - Contact1 goes in as Contact 2
<cfhttpparam name="Contact1" type="url" value="#Form.Alias#">
<cfhttpparam name="Email1" type="url" value="#Form.Email#">--->
<cfhttpparam name="Timezone" type="url" value="#qTimeZone.TimeZone#">
<cfhttpparam name="Bandwidth" type="url" value="#form.Bandwidth#">
<!---<cfhttpparam name="Notes" type="url" value="">--->
<cfhttpparam name="Email" type="url" value="#Form.Email#">
<cfhttpparam name="SendWelcomeMail" type="url" value="Y">
</cfhttp>
<cfdump var="#cfhttp.fileContent#">
<cfif cfhttp.fileContent eq "<OK/>">
<cfset session.UserID = #InsertUser.NewUserID#>
<cflocation url="thankyou.cfm">
<p>You new account has been created. Your username is CD<cfoutput>#InsertUser.NewUserID#</cfoutput>.</p>
<cfelse>
<p>Error: Your account could not be created at this time.</p>
<cfquery name="InsertError" datasource="#request.dsn#">
Insert into UserErrorLog(
UserID,
ErrorTypeID,
ErrorDetails,
ErrorDate
)
Values
(
#InsertUser.NewUserID#,
1,
'#cfhttp.fileContent#' ,
#CreateODBCDateTime(Now())#
)
</cfquery>
</cfif>
<cfset objXml = createObject("component","includes.cfc.xml2Struct")>
<!--- append the returned structure to some existing structure--->
<cfset str = structnew()>
<cfset str.existingElement = "some text here">
<cfset str = objXml.ConvertXmlToStruct(cfhttp.fileContent, str)>
<!--- create a new structure --->
<cfset APINewUser = objXml.ConvertXmlToStruct(cfhttp.fileContent, StructNew())>
<cfdump var = "#APINewUser#"> <!------>
<cfmail to="#Form.Email#" from="support@123-backup.com" subject="123-Backup - New Account Confirmation" type="html">
<p>Thank you for trialing our 123-backup Backup. To complete your registration, please click on the link below or paste it into a browser to confirm your e-mail address. You will then be redirected to Blah Blah to begin your download.
your address .cfm?ID=#InsertUser.NewUserID#>
</p>
</cfmail>
<p>Thank you, you will soon recieve an email at the address you specified. Please follow the instructinon contained in this email to complete your registration.</p>
</cfif>Looks like the VersionID in your form is possibly causing the problem as the option for Windows is set to value="1 ". I feel the spaces after the one is causing the value to to look like a string which is causing the problem.
If not, please try catching the query causing the problem by wrapping your code in the following:
<cftry>
<!---Your code here--->
<cfcatch type="any">
<cfoutput>#cfcatch.sql#</cfoutput>
</cfcatch>
</cftry>Looks like the VersionID in your form is possibly causing the problem as the option for Windows is set to value="1 ". I feel the spaces after the one is causing the value to to look like a string which is causing the problem.
If not, please try catching the query causing the problem by wrapping your code in the following:
<cftry>
<!---Your code here--->
<cfcatch type="any">
<cfoutput>#cfcatch.sql#</cfoutput>
</cfcatch>
</cftry><cfquery name="InsertUser" datasource="#request.dsn#">
BEGIN TRANSACTION
SET NOCOUNT ON
Nothing to do with your error, but the TRANSACTION is in the wrong place. In it's current spot, it doesn't do anything because there's only 1 statement in that cfquery. Transactions apply to multiple statements.
I'd bet dollars to donuts, whoever wrote that code meant to wrap it around both the UserDetails and UserContacts inserts..
<cfif #Form.EnableMSSQL# eq "on">
Is this a flash form?
As far as i know it cf form thats uses MS SQL database
Spiderant, I will try what you suggested to day many thanks for you help on this.
Lee
Ok Spiderant, I have try what you suggested i think we are getting a little further now.
I get this error message
Context validation error for the cfquery tag.
The start tag must have a matching end tag. An explicit end tag can be provided by adding </cfquery>. If the body of the tag is empty, you can use the shortcut <cfquery .../>.
Any ideas where i need to add this tag </cfquery> ?
Sounds like you put the code in the wrong place. The CFTRY needs to wrap your queries
<cftry>
<!---Your code here--->
<cfquery ...>.... sql .... </cfquery>
<cfquery ...>.... sql .... </cfquery>
....
<cfcatch type="any">
<cfoutput>#cfcatch.sql#</cfoutput>
</cfcatch>
</cftry>hi arrgh, I see what you mean about wrap it around both the user details and user contacts inserts
I guess it needs to be something like the below? If so i' am still receiving the same error message :(
Sorry to be a pain!
<cftry>
<cfquery name="InsertUser" datasource="#request.dsn#">
</cfquery>
BEGIN TRANSACTION
SET NOCOUNT ON
Insert into UserDetails(
<!---LoginName, --->
Password,
Alias,
LanguageID,
TypeID,
ClientTypeID,
<!---AdGroupID, --->
Quota,
<!---UserHome, --->
EnableMSSQL,
EnableMSExchange,
EnableOracle,
EnableLotusNotes,
EnableLotusDomino,
EnableMySQL,
EnableInFileDelta,
EnableShadowCopy,
EnableExchangeMailbox,
ExchangeMailboxQuota,
TimezoneID,
Bandwidth,
VersionID,
CreateDate
<!---,Notes, --->
<!---Email--->
)
Values
(
<!---'#Form.LoginName#', --->
'#Form.Password#',
'#Form.Alias#',
#form.LanguageID#,
#form.TypeID#,
#form.ClientTypeID#,
<!---#form.AdGroupID#,--->
#form.Quota#,
<!---UserHome, --->
<cfif #Form.EnableMSSQL# eq "on">1<cfelse>0</cfif>,
<cfif #Form.EnableMSExchange# eq "on">1<cfelse>0</cfif>,
<cfif #Form.EnableOracle# eq "on">1<cfelse>0</cfif>,
<cfif #Form.EnableLotusNotes# eq "on">1<cfelse>0</cfif>,
<cfif #Form.EnableLotusDomino# eq "on">1<cfelse>0</cfif>,
<cfif #Form.EnableMySQL# eq "on">1<cfelse>0</cfif>,
<cfif #Form.EnableInFileDelta# eq "on">1<cfelse>0</cfif>,
<cfif #Form.EnableShadowCopy# eq "on">1<cfelse>0</cfif>,
<cfif #Form.EnableExchangeMailbox# eq "on">1<cfelse>0</cfif>,
#form.ExchangeMailboxQuota#,
#Form.TimezoneID#,
#form.Bandwidth# ,
#form.VersionID#,
#createodbcdatetime(now())#
<!---,'#form.Notes#', --->
<!---'#Form.Email#' --->
)
SET NOCOUNT OFF
SELECT @@Identity AS 'NewUserID'
COMMIT TRANSACTION
<cfcatch type="any">
<cfoutput>#cfcatch.sql#</cfoutput>
</cfcatch>
</cftry>No, any SQL has to be inside the <cfquery> </cfquery> tags.
Easiest thing to do is go back to your original code. Then add the <cftry> to very 1st line and add the cfcatch after the very last line of the page. It's not how you'd normally do it. But it's impossible to mess up :)
<cfcatch type="any">
<cfoutput>#cfcatch.sql#</cfoutput>
</cfcatch>
</cftry>Many thanks Arrgh, Warrping my code from top to bottom got my page working
Once again many thanks!
<cftry>
<!---Your code here--->
<cfcatch type="any">
<cfoutput>#cfcatch.sql#</cfoutput>
</cfcatch>
</cftry>No.. it's not fixed. You're misunderstanding what a cftry/cfcatch does. It only traps error messages. It does nothing to fix the problem. On your page, it will print out the SQL statement that failed.
ie <cfoutput>#cfcatch.sql#</cfoutput>
Post the result here. Then someone can help you figure out why the query isn't working.
Ok sorry, i was getting ahead of my self hoping that it was working!!
The output from the cftry/cfcatch is as follows
. Select LanguageCode, LanguageName from Language where LanguageID = 19
Doesn't seem like would cause the original error: operand clash.
1. Can you post the DDL for the "language" table? Get your db person to help if you need.
2. Dump the complete error message after the SQL statement
<cfcatch type="any">
<cfoutput>#cfcatch.sql#</cfoutput>
<cfdump var="#cfcatch#"> <!--- dumps complete error details --->
</cfcatch>Hi arrgh, I have asked my webguy to have a look but he his also new to coldfusion, I think i have got down to the problem.
In my MS Sql Database it has a Language table so the users can select which language they want to use before downloading my software, Within the table the code needs 5 columns to work there are LanguageID, LanguageName, LanguageCode, EN and UK ect
The problem i have I' am unsure on what the data type need to be so the insert-user.cfm can work, I have spent a few hours tyring different data types out. Is there anyway i can find out what the datatypes need to be set at?
You can see more to the error if you fill out the form
backupserver1.123-backup.com:8080/client/signup.cfm
Cheers Lee
Please don't take this the wrong way. But if you're not familiar with db's, how to generate a table DLL or CF .. I think you need more assistance than we can provide.
I'd suggest you should either ask to the person that gave you the code -OR- hire a CF person to fix it. Someone who knows the basics *and* has access to your code and db will be able to figure it out in no time.
It will help you solve your problem a LOT faster than us trying to explain how to use the various tools, so you can provide us with the debug info we need, ... one slow post at at time.
Hi arrgh,
thank you for the advice, i will try looking on people per hour.
This script is on my development server so i dont mind if someone has access to it.
do you know anyone who would be interested in helping me?
Lee
What did the person that gave you the script say? They should know exactly what the problem is.. it's their code ;-)
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.