strange SQL error
I have a action page that accepts any form page and its variables then based on the stored proc sp_columns builds sql statment for update or insert. When I try to use it I get an Datefield overflow error (SQL 2000). But if I output the sql statment and then copy and past it into my sql enterprise manager the statement will work fine. If I remove the form date field I then get an syntax error, but I can copy and past it into SQL enterprise manager and it works. Here is the page that builds the sql statment: !--- Int Fields with default --- cfset FieldNameValue= cfset ColumnNames= cfset FieldNameValues= cfparam name=Form.ID default=0 !--- Get Column Names, Data Types from Form Field TableName and DSN(should be an hidden form fields --- cfquery name=GetTableInfodatasource=#Form.DSN# sp_columns #Form.TableName# /cfquery !--- Loop through each column in table --- cfloop query=GetTableInfo cfif ListFindNoCase(Form.FIELDNAMES,Column_Name) NEQ 0 AND Trim(Evaluate(ListGetAt(Form.FieldNames,ListFindNoCase(Form.FIELDNAMES,Column_Name NEQ cfset ElePos = ListFindNoCase(Form.FIELDNAMES,Column_Name) !--- Check to see what data type each column is and build insert based on value --- CFSWITCH EXPRESSION=#GetTableInfo.Type_Name# cfcase value=varchar cfset FieldNameValues = FieldNameValues , Column_Name = ' Trim(Evaluate(ListGetAt(Form.FieldNames,ElePos))) ' /cfcase cfcase value=datetime cfset FieldNameValues = FieldNameValues , Column_Name = CreateODBCDateTime(Trim(Evaluate(ListGetAt(Form.FieldNames,ElePos /cfcase cfcase value=text cfset FieldNameValues = FieldNameValues , Column_Name = ' Trim(Evaluate(ListGetAt(Form.FieldNames,ElePos))) ' /cfcase cfcase value=timestamp cfset FieldNameValues = FieldNameValues , Column_Name = ' Trim(Evaluate(ListGetAt(Form.FieldNames,ElePos))) ' /cfcase cfcase value=smalldatetime cfset FieldNameValues = FieldNameValues , Column_Name = ' Trim(Evaluate(ListGetAt(Form.FieldNames,ElePos))) ' /cfcase CFDEFAULTCASE cfset FieldNameValues = FieldNameValues , Column_Name = Trim(Evaluate(ListGetAt(Form.FieldNames,ElePos))) /CFDEFAULTCASE /CFSWITCH /cfif /cfloop !--- Check to see if there is a ,(comma) at the START of the string --- CFIF Left(FieldNameValues,1) EQ ,cfset FieldNameValues = Right(FieldNameValues,Len(FieldNameValues)-1)/CFIF CFIF Left(ColumnNames,1) EQ ,cfset ColumnNames = Right(ColumnNames,Len(ColumnNames)-1)/CFIF !--- Sample Insert statement --- cfquery name=Update datasource=#Form.DSN# Update #Form.Tablename# SET #FieldNameValues# WHERE ID = #Form.ID# /cfquery Here is the output of the sql statment it builds and then error i get. Update tbl_PreOp SET id=5, PreRegType='Hosp', Allergies='Tape,Iodine', LatexDiagnosis='Yes', EpipenAnaphylaxis='Yes', RubberReaction='Yes', TypeReation='test1', AvacadoBannaETC='Yes', TypeReactionToAvacadoBananaETC='test2', MedCondition='test3', UnexplainedAllergyType='test4' WHERE ID = 5 Queries GetTableInfo (Records=57, Time=63ms) SQL = sp_columns tbl_PreOp /PLAINTEXTPRE/PRE/Q/S/SAMP/SCRIPT/SELECT/SMALL/STRIKE/STRONG/SUB/SUP/TABLE/TD/TEXTAREA/TH/TITLE/TR/TT/U UL/UL/VAR/WBRXMP/XMP HR H3Error Occurred While Processing Request/H3 P TABLE border=1 TBODY TR TD H4Error Diagnostic Information/H4 PODBC Error Code = 37000 (Syntax error or access violation) P P[Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect syntax near 'Hosp'. P PSQL = Update tbl_PreOp SET id=5, PreRegType=''Hosp'', Allergies=''Tape,Iodine'', LatexDiagnosis=''Yes'', EpipenAnaphylaxis=''Yes'', RubberReaction=''Yes'', TypeReation=''test1'', AvacadoBannaETC=''Yes'', TypeReactionToAvacadoBananaETC=''test2'', MedCondition=''test3'', UnexplainedAllergyType=''test4'' WHERE ID = 5 PData Source = INTERNET_PHA P PThe error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (55:1) to (55:47) in the template file I:\INETPUB\WWWROOT\PALMETTOHEALTH\PREREGISTRATION\..\INCLUDE\CFM\INC_AUTOFORMUPDATE.CFM./P P PDate/Time: 06/06/02 09:55:33BRBrowser: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; Q312461)BRRemote Address: 204.116.185.37BRHTTP Referrer: http://intertest/preregistration/preop2.cfm?PreRegType=Hospamp;Drugs=Noamp;ID=5/P __ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: strange SQL error
do this: cfquery name=Update datasource=#Form.DSN# Update #preserveSingleQuotes(Form.Tablename)# SET #preserveSingleQuotes(FieldNameValues)# WHERE ID = #preserveSingleQuotes(Form.ID)# /cfquery +---+ Bryan Love Macromedia Certified Professional Internet Application Developer Database Analyst Telecommunication Systems [EMAIL PROTECTED] +---+ ...'If there must be trouble, let it be in my day, that my child may have peace'... - Thomas Paine, The American Crisis -Original Message- From: David Brown [mailto:[EMAIL PROTECTED]] Sent: Thursday, June 06, 2002 11:26 AM To: CF-Talk Subject: strange SQL error I have a action page that accepts any form page and its variables then based on the stored proc sp_columns builds sql statment for update or insert. When I try to use it I get an Datefield overflow error (SQL 2000). But if I output the sql statment and then copy and past it into my sql enterprise manager the statement will work fine. If I remove the form date field I then get an syntax error, but I can copy and past it into SQL enterprise manager and it works. Here is the page that builds the sql statment: !--- Int Fields with default --- cfset FieldNameValue= cfset ColumnNames= cfset FieldNameValues= cfparam name=Form.ID default=0 !--- Get Column Names, Data Types from Form Field TableName and DSN(should be an hidden form fields --- cfquery name=GetTableInfodatasource=#Form.DSN# sp_columns #Form.TableName# /cfquery !--- Loop through each column in table --- cfloop query=GetTableInfo cfif ListFindNoCase(Form.FIELDNAMES,Column_Name) NEQ 0 AND Trim(Evaluate(ListGetAt(Form.FieldNames,ListFindNoCase(Form.FIELDNAMES,Colum n_Name NEQ cfset ElePos = ListFindNoCase(Form.FIELDNAMES,Column_Name) !--- Check to see what data type each column is and build insert based on value --- CFSWITCH EXPRESSION=#GetTableInfo.Type_Name# cfcase value=varchar cfset FieldNameValues = FieldNameValues , Column_Name = ' Trim(Evaluate(ListGetAt(Form.FieldNames,ElePos))) ' /cfcase cfcase value=datetime cfset FieldNameValues = FieldNameValues , Column_Name = CreateODBCDateTime(Trim(Evaluate(ListGetAt(Form.FieldNames,ElePos /cfcase cfcase value=text cfset FieldNameValues = FieldNameValues , Column_Name = ' Trim(Evaluate(ListGetAt(Form.FieldNames,ElePos))) ' /cfcase cfcase value=timestamp cfset FieldNameValues = FieldNameValues , Column_Name = ' Trim(Evaluate(ListGetAt(Form.FieldNames,ElePos))) ' /cfcase cfcase value=smalldatetime cfset FieldNameValues = FieldNameValues , Column_Name = ' Trim(Evaluate(ListGetAt(Form.FieldNames,ElePos))) ' /cfcase CFDEFAULTCASE cfset FieldNameValues = FieldNameValues , Column_Name = Trim(Evaluate(ListGetAt(Form.FieldNames,ElePos))) /CFDEFAULTCASE /CFSWITCH /cfif /cfloop !--- Check to see if there is a ,(comma) at the START of the string --- CFIF Left(FieldNameValues,1) EQ ,cfset FieldNameValues = Right(FieldNameValues,Len(FieldNameValues)-1)/CFIF CFIF Left(ColumnNames,1) EQ ,cfset ColumnNames = Right(ColumnNames,Len(ColumnNames)-1)/CFIF !--- Sample Insert statement --- cfquery name=Update datasource=#Form.DSN# Update #Form.Tablename# SET #FieldNameValues# WHERE ID = #Form.ID# /cfquery Here is the output of the sql statment it builds and then error i get. Update tbl_PreOp SET id=5, PreRegType='Hosp', Allergies='Tape,Iodine', LatexDiagnosis='Yes', EpipenAnaphylaxis='Yes', RubberReaction='Yes', TypeReation='test1', AvacadoBannaETC='Yes', TypeReactionToAvacadoBananaETC='test2', MedCondition='test3', UnexplainedAllergyType='test4' WHERE ID = 5 Queries GetTableInfo (Records=57, Time=63ms) SQL = sp_columns tbl_PreOp /PLAINTEXTPRE/PRE/Q/S/SAMP/SCRIPT/SELECT/SMALL/STRIKE/S TRONG/SUB/SUP/TABLE/TD/TEXTAREA/TH/TITLE/TR/TT/U UL/UL/VAR/WBRXMP/XMP HR H3Error Occurred While Processing Request/H3 P TABLE border=1 TBODY TR TD H4Error Diagnostic Information/H4 PODBC Error Code = 37000 (Syntax error or access violation) P P[Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect syntax near 'Hosp'. P PSQL = Update tbl_PreOp SET id=5, PreRegType=''Hosp'', Allergies=''Tape,Iodine'', LatexDiagnosis=''Yes'', EpipenAnaphylaxis=''Yes'', RubberReaction=''Yes'', TypeReation=''test1'', AvacadoBannaETC=''Yes'', TypeReactionToAvacadoBananaETC=''test2'', MedCondition=''test3'', UnexplainedAllergyType=''test4'' WHERE ID = 5 PData Source = INTERNET_PHA P PThe error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (55:1) to (55:47) in the template file I:\INETPUB\WWWROOT\PALMETTOHEALTH\PREREGISTRATION\..\INCLUDE\CFM\INC_AUTOFOR MUPDATE.CFM./P P PDate/Time: 06/06/02 09:55:33BRBrowser: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1
Strange SQL error
In all my time developing SQL I have never come across sommat like this - please someone tell me I'm being stoopid as it's a Fri afternoon. I have the following SP which refuses to release - it says it cannot find parameter JuryTitle. If I comment out JuryTitle and the corresponding field insert it works. If I change the name of the variable it doesn'tsomeone slap me with a kipper so I can see where I've gone wrong... CREATE PROCEDURE JuryInsert -- Any parameters here CountryID int , JuryName varchar(100) , JuryTitle varchar(50) , JuryCompanyvarchar(50) , JuryCity varchar(50) , JuryTypeID int AS BEGIN INSERT INTO tblJury ( CountryID , JuryName , JuryTitle , JuryCompany , JuryCity , JuryTypeID ) VALUES ( CountryID , JuryName , test , JuryCompany , JuryCity , JuryTypeID ) IF error 0 BEGIN GOTO ERROR_EXIT END RETURN error ERROR_EXIT: RETURN error END GO -- Andy Ewings Director Thoughtbubble Ltd http://www.thoughtbubble.net -- United Kingdom http://www.thoughtbubble.net Tel: +44 (0) 20 7387 8890 -- New Zealand http://www.thoughtbubble.co.nz/ Tel: +64 (0) 9 419 4235 -- The information in this email and in any attachments is confidential and intended solely for the attention and use of the named addressee(s). Any views or opinions presented are solely those of the author and do not necessarily represent those of Thoughtbubble. This information may be subject to legal, professional or other privilege and further distribution of it is strictly prohibited without our authority. If you are not the intended recipient, you are not authorised to disclose, copy, distribute, or retain this message. Please notify us on +44 (0)207 387 8890. __ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Strange SQL error
I assume in this bit: VALUES ( @CountryID , @JuryName , @test , @JuryCompany , @JuryCity , @JuryTypeID you're actually using VALUES ( @CountryID , @JuryName , @JuryTitle , @JuryCompany , @JuryCity , @JuryTypeID ? -Original Message- From: Andy Ewings [mailto:[EMAIL PROTECTED]] Sent: 17 May 2002 16:56 To: CF-Talk Subject: Strange SQL error In all my time developing SQL I have never come across sommat like this - please someone tell me I'm being stoopid as it's a Fri afternoon. I have the following SP which refuses to release - it says it cannot find parameter @JuryTitle. If I comment out @JuryTitle and the corresponding field insert it works. If I change the name of the variable it doesn'tsomeone slap me with a kipper so I can see where I've gone wrong... CREATE PROCEDURE JuryInsert -- Any parameters here @CountryID int , @JuryName varchar(100) , @JuryTitle varchar(50) , @JuryCompanyvarchar(50) , @JuryCity varchar(50) , @JuryTypeID int AS BEGIN INSERT INTO tblJury ( CountryID , JuryName , JuryTitle , JuryCompany , JuryCity , JuryTypeID ) VALUES ( @CountryID , @JuryName , @test , @JuryCompany , @JuryCity , @JuryTypeID ) IF @@error 0 BEGIN GOTO ERROR_EXIT END RETURN @@error ERROR_EXIT: RETURN @@error END GO -- Andy Ewings Director Thoughtbubble Ltd http://www.thoughtbubble.net -- United Kingdom http://www.thoughtbubble.net Tel: +44 (0) 20 7387 8890 -- New Zealand http://www.thoughtbubble.co.nz/ Tel: +64 (0) 9 419 4235 -- The information in this email and in any attachments is confidential and intended solely for the attention and use of the named addressee(s). Any views or opinions presented are solely those of the author and do not necessarily represent those of Thoughtbubble. This information may be subject to legal, professional or other privilege and further distribution of it is strictly prohibited without our authority. If you are not the intended recipient, you are not authorised to disclose, copy, distribute, or retain this message. Please notify us on +44 (0)207 387 8890. __ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Strange SQL error
ah - yes sorry - forgot to change it back -Original Message- From: Rich Wild [mailto:[EMAIL PROTECTED]] Sent: 17 May 2002 16:50 To: CF-Talk Subject: RE: Strange SQL error I assume in this bit: VALUES ( @CountryID , @JuryName , @test , @JuryCompany , @JuryCity , @JuryTypeID you're actually using VALUES ( @CountryID , @JuryName , @JuryTitle , @JuryCompany , @JuryCity , @JuryTypeID ? -Original Message- From: Andy Ewings [mailto:[EMAIL PROTECTED]] Sent: 17 May 2002 16:56 To: CF-Talk Subject: Strange SQL error In all my time developing SQL I have never come across sommat like this - please someone tell me I'm being stoopid as it's a Fri afternoon. I have the following SP which refuses to release - it says it cannot find parameter @JuryTitle. If I comment out @JuryTitle and the corresponding field insert it works. If I change the name of the variable it doesn'tsomeone slap me with a kipper so I can see where I've gone wrong... CREATE PROCEDURE JuryInsert -- Any parameters here @CountryID int , @JuryName varchar(100) , @JuryTitle varchar(50) , @JuryCompanyvarchar(50) , @JuryCity varchar(50) , @JuryTypeID int AS BEGIN INSERT INTO tblJury ( CountryID , JuryName , JuryTitle , JuryCompany , JuryCity , JuryTypeID ) VALUES ( @CountryID , @JuryName , @test , @JuryCompany , @JuryCity , @JuryTypeID ) IF @@error 0 BEGIN GOTO ERROR_EXIT END RETURN @@error ERROR_EXIT: RETURN @@error END GO -- Andy Ewings Director Thoughtbubble Ltd http://www.thoughtbubble.net -- United Kingdom http://www.thoughtbubble.net Tel: +44 (0) 20 7387 8890 -- New Zealand http://www.thoughtbubble.co.nz/ Tel: +64 (0) 9 419 4235 -- The information in this email and in any attachments is confidential and intended solely for the attention and use of the named addressee(s). Any views or opinions presented are solely those of the author and do not necessarily represent those of Thoughtbubble. This information may be subject to legal, professional or other privilege and further distribution of it is strictly prohibited without our authority. If you are not the intended recipient, you are not authorised to disclose, copy, distribute, or retain this message. Please notify us on +44 (0)207 387 8890. __ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Strange SQL error
Right fixed it - all we did was to move the declaration of the JuryTitle param to the bottom of the list and itr works! SQL schmeequel -Original Message- From: Rich Wild [mailto:[EMAIL PROTECTED]] Sent: 17 May 2002 16:50 To: CF-Talk Subject: RE: Strange SQL error I assume in this bit: VALUES ( @CountryID , @JuryName , @test , @JuryCompany , @JuryCity , @JuryTypeID you're actually using VALUES ( @CountryID , @JuryName , @JuryTitle , @JuryCompany , @JuryCity , @JuryTypeID ? -Original Message- From: Andy Ewings [mailto:[EMAIL PROTECTED]] Sent: 17 May 2002 16:56 To: CF-Talk Subject: Strange SQL error In all my time developing SQL I have never come across sommat like this - please someone tell me I'm being stoopid as it's a Fri afternoon. I have the following SP which refuses to release - it says it cannot find parameter @JuryTitle. If I comment out @JuryTitle and the corresponding field insert it works. If I change the name of the variable it doesn'tsomeone slap me with a kipper so I can see where I've gone wrong... CREATE PROCEDURE JuryInsert -- Any parameters here @CountryID int , @JuryName varchar(100) , @JuryTitle varchar(50) , @JuryCompanyvarchar(50) , @JuryCity varchar(50) , @JuryTypeID int AS BEGIN INSERT INTO tblJury ( CountryID , JuryName , JuryTitle , JuryCompany , JuryCity , JuryTypeID ) VALUES ( @CountryID , @JuryName , @test , @JuryCompany , @JuryCity , @JuryTypeID ) IF @@error 0 BEGIN GOTO ERROR_EXIT END RETURN @@error ERROR_EXIT: RETURN @@error END GO -- Andy Ewings Director Thoughtbubble Ltd http://www.thoughtbubble.net -- United Kingdom http://www.thoughtbubble.net Tel: +44 (0) 20 7387 8890 -- New Zealand http://www.thoughtbubble.co.nz/ Tel: +64 (0) 9 419 4235 -- The information in this email and in any attachments is confidential and intended solely for the attention and use of the named addressee(s). Any views or opinions presented are solely those of the author and do not necessarily represent those of Thoughtbubble. This information may be subject to legal, professional or other privilege and further distribution of it is strictly prohibited without our authority. If you are not the intended recipient, you are not authorised to disclose, copy, distribute, or retain this message. Please notify us on +44 (0)207 387 8890. __ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Strange SQL error
uh? -Original Message- From: Andy Ewings [mailto:[EMAIL PROTECTED]] Sent: 17 May 2002 17:18 To: CF-Talk Subject: RE: Strange SQL error Right fixed it - all we did was to move the declaration of the JuryTitle param to the bottom of the list and itr works! SQL schmeequel -Original Message- From: Rich Wild [mailto:[EMAIL PROTECTED]] Sent: 17 May 2002 16:50 To: CF-Talk Subject: RE: Strange SQL error I assume in this bit: VALUES ( @CountryID , @JuryName , @test , @JuryCompany , @JuryCity , @JuryTypeID you're actually using VALUES ( @CountryID , @JuryName , @JuryTitle , @JuryCompany , @JuryCity , @JuryTypeID ? -Original Message- From: Andy Ewings [mailto:[EMAIL PROTECTED]] Sent: 17 May 2002 16:56 To: CF-Talk Subject: Strange SQL error In all my time developing SQL I have never come across sommat like this - please someone tell me I'm being stoopid as it's a Fri afternoon. I have the following SP which refuses to release - it says it cannot find parameter @JuryTitle. If I comment out @JuryTitle and the corresponding field insert it works. If I change the name of the variable it doesn'tsomeone slap me with a kipper so I can see where I've gone wrong... CREATE PROCEDURE JuryInsert -- Any parameters here @CountryID int , @JuryName varchar(100) , @JuryTitle varchar(50) , @JuryCompanyvarchar(50) , @JuryCity varchar(50) , @JuryTypeID int AS BEGIN INSERT INTO tblJury ( CountryID , JuryName , JuryTitle , JuryCompany , JuryCity , JuryTypeID ) VALUES ( @CountryID , @JuryName , @test , @JuryCompany , @JuryCity , @JuryTypeID ) IF @@error 0 BEGIN GOTO ERROR_EXIT END RETURN @@error ERROR_EXIT: RETURN @@error END GO -- Andy Ewings Director Thoughtbubble Ltd http://www.thoughtbubble.net -- United Kingdom http://www.thoughtbubble.net Tel: +44 (0) 20 7387 8890 -- New Zealand http://www.thoughtbubble.co.nz/ Tel: +64 (0) 9 419 4235 -- The information in this email and in any attachments is confidential and intended solely for the attention and use of the named addressee(s). Any views or opinions presented are solely those of the author and do not necessarily represent those of Thoughtbubble. This information may be subject to legal, professional or other privilege and further distribution of it is strictly prohibited without our authority. If you are not the intended recipient, you are not authorised to disclose, copy, distribute, or retain this message. Please notify us on +44 (0)207 387 8890. __ Get the mailserver that powers this list at http://www.coolfusion.com FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Strange SQL error
What Andy meant was that he moved the @JuryTitle param to the bottom of the list like so: -- Any parameters here @CountryID int , @JuryName varchar(100) , @JuryCompanyvarchar(50) , @JuryCity varchar(50) , @JuryTypeID int , @JuryTitle varchar(50) @JuryTitle was originally declared after the second param @JuryName. For some reason the SP then released perfectly. Bizarrely I then moved the @JuryTitle param back to where it was and then the SP relases again. For the last 2 hours the SP would not release and we are at a loss as to why it didn't work. Any Ideas SQL gurus what may have happened? -Original Message- From: Rich Wild [mailto:[EMAIL PROTECTED]] Sent: 17 May 2002 17:10 To: CF-Talk Subject: RE: Strange SQL error uh? -Original Message- From: Andy Ewings [mailto:[EMAIL PROTECTED]] Sent: 17 May 2002 17:18 To: CF-Talk Subject: RE: Strange SQL error Right fixed it - all we did was to move the declaration of the JuryTitle param to the bottom of the list and itr works! SQL schmeequel -Original Message- From: Rich Wild [mailto:[EMAIL PROTECTED]] Sent: 17 May 2002 16:50 To: CF-Talk Subject: RE: Strange SQL error I assume in this bit: VALUES ( @CountryID , @JuryName , @test , @JuryCompany , @JuryCity , @JuryTypeID you're actually using VALUES ( @CountryID , @JuryName , @JuryTitle , @JuryCompany , @JuryCity , @JuryTypeID ? -Original Message- From: Andy Ewings [mailto:[EMAIL PROTECTED]] Sent: 17 May 2002 16:56 To: CF-Talk Subject: Strange SQL error In all my time developing SQL I have never come across sommat like this - please someone tell me I'm being stoopid as it's a Fri afternoon. I have the following SP which refuses to release - it says it cannot find parameter @JuryTitle. If I comment out @JuryTitle and the corresponding field insert it works. If I change the name of the variable it doesn'tsomeone slap me with a kipper so I can see where I've gone wrong... CREATE PROCEDURE JuryInsert -- Any parameters here @CountryID int , @JuryName varchar(100) , @JuryTitle varchar(50) , @JuryCompanyvarchar(50) , @JuryCity varchar(50) , @JuryTypeID int AS BEGIN INSERT INTO tblJury ( CountryID , JuryName , JuryTitle , JuryCompany , JuryCity , JuryTypeID ) VALUES ( @CountryID , @JuryName , @test , @JuryCompany , @JuryCity , @JuryTypeID ) IF @@error 0 BEGIN GOTO ERROR_EXIT END RETURN @@error ERROR_EXIT: RETURN @@error END GO -- Andy Ewings Director Thoughtbubble Ltd http://www.thoughtbubble.net -- United Kingdom http://www.thoughtbubble.net Tel: +44 (0) 20 7387 8890 -- New Zealand http://www.thoughtbubble.co.nz/ Tel: +64 (0) 9 419 4235 -- The information in this email and in any attachments is confidential and intended solely for the attention and use of the named addressee(s). Any views or opinions presented are solely those of the author and do not necessarily represent those of Thoughtbubble. This information may be subject to legal, professional or other privilege and further distribution of it is strictly prohibited without our authority. If you are not the intended recipient, you are not authorised to disclose, copy, distribute, or retain this message. Please notify us on +44 (0)207 387 8890. __ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Strange SQL error
Something that's happened to me before is that, depending on what you used to write the code originally, a hidden illegal character may have been introduced. When you moved it, once again depending on what you were using, the cut and paste would eliminate any illegal characters. I usually pass code through notepad if I've touched anything like Word or Wordpad for example because of the illegal characters. Margaret -Original Message- From: Stephen Galligan [mailto:[EMAIL PROTECTED]] Sent: Friday, May 17, 2002 9:25 AM To: CF-Talk Subject: RE: Strange SQL error What Andy meant was that he moved the @JuryTitle param to the bottom of the list like so: -- Any parameters here @CountryID int , @JuryName varchar(100) , @JuryCompanyvarchar(50) , @JuryCity varchar(50) , @JuryTypeID int , @JuryTitle varchar(50) @JuryTitle was originally declared after the second param @JuryName. For some reason the SP then released perfectly. Bizarrely I then moved the @JuryTitle param back to where it was and then the SP relases again. For the last 2 hours the SP would not release and we are at a loss as to why it didn't work. Any Ideas SQL gurus what may have happened? -Original Message- From: Rich Wild [mailto:[EMAIL PROTECTED]] Sent: 17 May 2002 17:10 To: CF-Talk Subject: RE: Strange SQL error uh? -Original Message- From: Andy Ewings [mailto:[EMAIL PROTECTED]] Sent: 17 May 2002 17:18 To: CF-Talk Subject: RE: Strange SQL error Right fixed it - all we did was to move the declaration of the JuryTitle param to the bottom of the list and itr works! SQL schmeequel -Original Message- From: Rich Wild [mailto:[EMAIL PROTECTED]] Sent: 17 May 2002 16:50 To: CF-Talk Subject: RE: Strange SQL error I assume in this bit: VALUES ( @CountryID , @JuryName , @test , @JuryCompany , @JuryCity , @JuryTypeID you're actually using VALUES ( @CountryID , @JuryName , @JuryTitle , @JuryCompany , @JuryCity , @JuryTypeID ? -Original Message- From: Andy Ewings [mailto:[EMAIL PROTECTED]] Sent: 17 May 2002 16:56 To: CF-Talk Subject: Strange SQL error In all my time developing SQL I have never come across sommat like this - please someone tell me I'm being stoopid as it's a Fri afternoon. I have the following SP which refuses to release - it says it cannot find parameter @JuryTitle. If I comment out @JuryTitle and the corresponding field insert it works. If I change the name of the variable it doesn'tsomeone slap me with a kipper so I can see where I've gone wrong... CREATE PROCEDURE JuryInsert -- Any parameters here @CountryID int , @JuryName varchar(100) , @JuryTitle varchar(50) , @JuryCompanyvarchar(50) , @JuryCity varchar(50) , @JuryTypeID int AS BEGIN INSERT INTO tblJury ( CountryID , JuryName , JuryTitle , JuryCompany , JuryCity , JuryTypeID ) VALUES ( @CountryID , @JuryName , @test , @JuryCompany , @JuryCity , @JuryTypeID ) IF @@error 0 BEGIN GOTO ERROR_EXIT END RETURN @@error ERROR_EXIT: RETURN @@error END GO -- Andy Ewings Director Thoughtbubble Ltd http://www.thoughtbubble.net -- United Kingdom http://www.thoughtbubble.net Tel: +44 (0) 20 7387 8890 -- New Zealand http://www.thoughtbubble.co.nz/ Tel: +64 (0) 9 419 4235 -- The information in this email and in any attachments is confidential and intended solely for the attention and use of the named addressee(s). Any views or opinions presented are solely those of the author and do not necessarily represent those of Thoughtbubble. This information may be subject to legal, professional or other privilege and further distribution of it is strictly prohibited without our authority. If you are not the intended recipient, you are not authorised to disclose, copy
Strange SQL Error...
Has anyone seen this? There are no "subqueries" going on in this SQL statement.. But yet we get this error. == ODBC Error Code = 21000 () [Microsoft][ODBC SQL Server Driver][SQL Server]Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, , = , , = or when the subquery is used as an expression. SQL = "UPDATE tbl2inspectorformgeneration SET inspected = '5/5/1'" == Clues anyone? TTAIA! Lee ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Strange SQL Error...
Try: SQL = 'UPDATE tbl2inspectorformgeneration SET inspected = ''5/5/1''' Single quotes make strings, not double quotes. Two single quotes together makes for a single quote inside of a string. -Original Message- From: Lee Fuller [mailto:[EMAIL PROTECTED]] Sent: Wednesday, April 04, 2001 12:33 PM To: CF-Talk Subject: Strange SQL Error... Has anyone seen this? There are no "subqueries" going on in this SQL statement.. But yet we get this error. == ODBC Error Code = 21000 () [Microsoft][ODBC SQL Server Driver][SQL Server]Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, , = , , = or when the subquery is used as an expression. SQL = "UPDATE tbl2inspectorformgeneration SET inspected = '5/5/1'" == Clues anyone? TTAIA! Lee ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: Strange SQL Error...
Do you have an update trigger on the table? -Original Message- From: Lee Fuller [mailto:[EMAIL PROTECTED]] Sent: Wednesday, April 04, 2001 2:33 PM To: CF-Talk Subject: Strange SQL Error... Has anyone seen this? There are no "subqueries" going on in this SQL statement.. But yet we get this error. == ODBC Error Code = 21000 () [Microsoft][ODBC SQL Server Driver][SQL Server]Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, , = , , = or when the subquery is used as an expression. SQL = "UPDATE tbl2inspectorformgeneration SET inspected = '5/5/1'" == Clues anyone? TTAIA! Lee ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
RE: strange SQL error code
What version ODBC driver are you using? http://support.microsoft.com/support/kb/articles/q234/9/87.asp INFO: SQL Server Driver Version 3.70 May Return Fractional Truncation Message When Processing DateTime Parameter Joe Hoffman mailto:[EMAIL PROTECTED] National Institutes of Health Center for Information Technology Division of Computer System Services -Original Message- From: Warrick, Mark [mailto:[EMAIL PROTECTED]] Sent: Tuesday, November 07, 2000 2:12 PM To: CF-Talk Subject: strange SQL error code Anyone ever seen this one? ODBC Error Code = 01004 (Data truncated) [Microsoft][ODBC SQL Server Driver]Fractional truncation On that note, does anyone have a list of Microsoft SQL Error Codes? Please copy me with your reply. Thanks. -- Mark Warrick Phone: (714) 547-5386 Efax.com Fax: (801) 730-7289 Personal Email: [EMAIL PROTECTED] Personal URL: http://www.warrick.net Business Email: [EMAIL PROTECTED] Business URL: http://www.fusioneers.com ICQ: 346566 -- Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED] Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED]
RE: strange SQL error code
On that note, does anyone have a list of Microsoft SQL Error Codes? They are listed in the online books online-reference. If you like I'll post you a text file offlist. Regards, Anthony Geoghegan. Lead Developer, What's On Where (WOW!) http://www.wow.ie mailto:[EMAIL PROTECTED] Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message with 'unsubscribe' in the body to [EMAIL PROTECTED]
