Re: Access SQL Question
Qasim Rasheed wrote: I want to recursively delete descendants at all levels. Any thoughts? How about a self-referencing foreign key with ON DELETE CASCADE? Jochem [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: Access SQL Question
Or,
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
ALTERPROCEDURE dbo.upu_Instance_Delete
@InstanceIDint
AS
-- Variables
DECLARE@lCurrentID int
DECLARE@lError int
DECLARE@lInstanceIDint
DECLARE@lPreviousIDint
--Find Children and kill them all.
BEGIN TRAN trInstanceDelete
SET@lPreviousID = -1
SET@lCurrentID = 0
WHILE (@lCurrentID @lPreviousID) BEGIN
SET@lPreviousID = @lCurrentID
-- Volgende record ophalen
SELECT@lCurrentID= MIN
(I.InstanceID)
FROMInstance I
WHERE(I.ParentID = @InstanceID)
AND(I.InstanceID
@lPreviousID)
SET@lError = @@ERROR
IF (@lError 0) BEGIN
RAISERROR ('Fout bij ophalen
Instance! Fout: %d', 16, 1, @lError)
ROLLBACK TRAN trInstanceDelete
RETURN (-1)
END
-- Controleren of er een nieuwe gevonden is
SET@lCurrentID = ISNULL (@lCurrentID, 0)
IF ((@lCurrentID 0) AND (@lCurrentID
@lPreviousID)) BEGIN
-- Delete children
EXEC upu_Instance_Delete
@InstanceID = @lCurrentID
END
END
DELETEInstance
WHERE(InstanceID = @InstanceID)
SET@lError = @@ERROR
IF (@lError 0) BEGIN
RAISERROR ('Fout bij verwijderen instance! Fout:
%d', 16, 1, @lError)
ROLLBACK TRAN trInstanceDelete
RETURN (-1)
END
COMMIT TRAN trInstanceDelete
-- The End
RETURN (0)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Micha Schopman
Software Engineer
Modern Media, Databankweg 12 M, 3821 ALAmersfoort
Tel 033-4535377, Fax 033-4535388
KvK Amersfoort 39081679, Rabo 39.48.05.380
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
[Donations and Support]
Re: Access SQL Question
Micha,
Unfortunately I am using Access, so stored procedures are not an
option, however thanks for sending me an excellent code which I would
keepfor future reference.
- Original Message -
From: Micha Schopman [EMAIL PROTECTED]
Date: Thu, 19 Aug 2004 12:53:49 +0200
Subject: RE: Access SQL Question
To: CF-Talk [EMAIL PROTECTED]
Or,
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
ALTERPROCEDURE dbo.upu_Instance_Delete
@InstanceIDint
AS
-- Variables
DECLARE@lCurrentID int
DECLARE@lError int
DECLARE@lInstanceIDint
DECLARE@lPreviousIDint
--Find Children and kill them all.
BEGIN TRAN trInstanceDelete
SET@lPreviousID = -1
SET@lCurrentID = 0
WHILE (@lCurrentID @lPreviousID) BEGIN
SET@lPreviousID = @lCurrentID
-- Volgende record ophalen
SELECT@lCurrentID= MIN
(I.InstanceID)
FROMInstance I
WHERE(I.ParentID = @InstanceID)
AND(I.InstanceID
@lPreviousID)
SET@lError = @@ERROR
IF (@lError 0) BEGIN
RAISERROR ('Fout bij ophalen
Instance! Fout: %d', 16, 1, @lError)
ROLLBACK TRAN trInstanceDelete
RETURN (-1)
END
-- Controleren of er een nieuwe gevonden is
SET@lCurrentID = ISNULL (@lCurrentID, 0)
IF ((@lCurrentID 0) AND (@lCurrentID
@lPreviousID)) BEGIN
-- Delete children
EXEC upu_Instance_Delete
@InstanceID = @lCurrentID
END
END
DELETEInstance
WHERE(InstanceID = @InstanceID)
SET@lError = @@ERROR
IF (@lError 0) BEGIN
RAISERROR ('Fout bij verwijderen instance! Fout:
%d', 16, 1, @lError)
ROLLBACK TRAN trInstanceDelete
RETURN (-1)
END
COMMIT TRAN trInstanceDelete
-- The End
RETURN (0)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Micha Schopman
Software Engineer
Modern Media, Databankweg 12 M, 3821 ALAmersfoort
Tel 033-4535377, Fax 033-4535388
KvK Amersfoort 39081679, Rabo 39.48.05.380
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
[Donations and Support]
Re: Access SQL Question
SQL Server manages auto-incremented fields differently than Access does. I believe you have to go into SQL Server Enterprise Manager and modify that field in the table so that SQL Server knows it is an autonumber field. - Original Message - From: Donna French To: CF-Talk Sent: Wednesday, August 18, 2004 1:17 PM Subject: Access SQL Question I have a table that I transferred from Access to SQL and had a field setup as auto number. Now I am getting an error message when I try to submit new info to the table in SQL. Here's the error: ODBC Error Code = 23000 (Integrity constraint violation) [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert the value NULL into column 'id', table 'mydatabase.user.tblMemo'; column does not allow nulls. INSERT fails. The error occurred while processing an element with a general identifier of (CFINSERT), occupying document position (6:1) to (11:28). Here's the CFInsert code: cfinsert dbname=mydbname username=myusername password=mypassword datasource=mydsn tablename=tblMemo formfields=subject, memo Any help appreciated. Thank you, Donna French [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: Access SQL Question (Resolved)
Just wanted to say thanks - changed the field in Enterprise Manager. Thank you!!! Donna French -Original Message- From: G [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 18, 2004 1:37 PM To: CF-Talk Subject: Re: Access SQL Question SQL Server manages auto-incremented fields differently than Access does. I believe you have to go into SQL Server Enterprise Manager and modify that field in the table so that SQL Server knows it is an autonumber field. - Original Message - From: Donna French To: CF-Talk Sent: Wednesday, August 18, 2004 1:17 PM Subject: Access SQL Question I have a table that I transferred from Access to SQL and had a field setup as auto number. Now I am getting an error message when I try to submit new info to the table in SQL. Here's the error: ODBC Error Code = 23000 (Integrity constraint violation) [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert the value NULL into column 'id', table 'mydatabase.user.tblMemo'; column does not allow nulls. INSERT fails. The error occurred while processing an element with a general identifier of (CFINSERT), occupying document position (6:1) to (11:28). Here's the CFInsert code: cfinsert dbname=mydbname username=myusername password=mypassword datasource=mydsn tablename=tblMemo formfields=subject, memo Any help appreciated. Thank you, Donna French _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: Access SQL Question
I have a table that I transferred from Access to SQL and had a field setup as auto number. Now I am getting an error message when I try to submit new info to the table in SQL. Yes You will need to go into SQL enterprise manager and make the col that was auto number in access to Identityyou set the Identity seed (number to start) and Identity increment to how you want it to increment. Mark Holm [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: Access SQL Question
Is this what you want? DELETE FROMmyTable WHEREparent = 3 Or do you want to be able to recursively delete descendents (i.e. children, cgrandchildren.) at all levels? _ From: Qasim Rasheed [mailto:[EMAIL PROTECTED] Sent: Thursday, 19 August 2004 8:32 a.m. To: CF-Talk Subject: Access SQL Question Is there a way in access where you can delete all the child of a parent e.g. if I am deleting a record which a children and children(s) may or may not have child. Here is db schema id,name,parent 1,test1,0 2,test2,0 3,test3,1 4,test4,3 any help is appreciated _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: Access SQL Question
I want to recursively delete descendants at all levels. Any thoughts? - Original Message - From: Matthew Walker [EMAIL PROTECTED] Date: Thu, 19 Aug 2004 10:25:19 +1200 Subject: RE: Access SQL Question To: CF-Talk [EMAIL PROTECTED] Is this what you want? DELETE FROMmyTable WHEREparent = 3 Or do you want to be able to recursively delete descendents (i.e. children, cgrandchildren.) at all levels? _ From: Qasim Rasheed [mailto:[EMAIL PROTECTED] Sent: Thursday, 19 August 2004 8:32 a.m. To: CF-Talk Subject: Access SQL Question Is there a way in access where you can delete all the child of a parent e.g. if I am deleting a record which a children and children(s) may or may not have child. Here is db schema id,name,parent 1,test1,0 2,test2,0 3,test3,1 4,test4,3 any help is appreciated _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
RE: Access SQL Question
Um, how about something like this? (Untested) cfset parents = 3 !--- the id of the ancestor --- cfset notDone=true cfloop condition=#notDone# cfquery name=children SELECTid FROMmyTable WHEREparent in (#parents#) /cfquery cfquery DELETE FROMmyTable WHEREparent in (#parents#) /cfquery cfif children.recordCount cfset parents = valueList(children.id) cfelse cfset notDone = false /cfif /cfloop _ From: Qasim Rasheed [mailto:[EMAIL PROTECTED] Sent: Thursday, 19 August 2004 3:09 p.m. To: CF-Talk Subject: Re: Access SQL Question I want to recursively delete descendants at all levels. Any thoughts? - Original Message - From: Matthew Walker [EMAIL PROTECTED] Date: Thu, 19 Aug 2004 10:25:19 +1200 Subject: RE: Access SQL Question To: CF-Talk [EMAIL PROTECTED] Is this what you want? DELETE FROMmyTable WHEREparent = 3 Or do you want to be able to recursively delete descendents (i.e. children, cgrandchildren.) at all levels? _ From: Qasim Rasheed [mailto:[EMAIL PROTECTED] Sent: Thursday, 19 August 2004 8:32 a.m. To: CF-Talk Subject: Access SQL Question Is there a way in access where you can delete all the child of a parent e.g. if I am deleting a record which a children and children(s) may or may not have child. Here is db schema id,name,parent 1,test1,0 2,test2,0 3,test3,1 4,test4,3 any help is appreciated _ _ [Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
Re: Access SQL Question :(
Tim Heald wrote: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '''new make'''. Any Ideas? BTW, this is a dynamic script I am writing PreserveSingleQuotes? Jochem ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Access SQL Question :(
can you show us what the query looks like in the debug output? Wait, does CF
4.5 output that i forget. :-) If so, send that to the list. That would
let us see what the dynamic query looked like when it failed i think,
and we can go from there.
mike
- Original Message -
From: Tim Heald [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Friday, August 01, 2003 9:19 AM
Subject: Access SQL Question :(
Evening,
I am trying to work out an update statement for Access. It's on CF 4.5.
I
am generating the SQL in a cfscript, it will be below. The final script
looks like this:
update printers set MAKE = 'new make', MODEL = 'new model', PRODUCTTYPE =
'new product', PRINTERGROUP = 'new grogugp', CATEGORY = 'new cgategory',
CSPEED = 0, ONSALEPRICE = '$0.00', MSRP = '$0.00', SOL2 = 1, SOL4 = 1,
SOL6
= 1, SOL7 = 1, SOL8 = 1, sol1 = 0, sol3 = 0, sol5 = 0 where printerID =
120
It runs fine in Access, but throws this through CF:
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator)
in
query expression '''new make'''.
Any Ideas? BTW, this is a dynamic script I am writing, the form has a
great
many fields, and would just be a major pain to write conditionals for each
and every one.
Tim
cfscript
// this is the beginning of the query string, I will append values to it
as
I go
variables.myQuery = 'update printers set ';
// loop through form.fieldnames and find those that have a length and
append them to the goodFields list
variables.goodFields = '';
for(i = 1; i lte listLen(form.fieldNames); i = i + 1){
if(len(form[listGetAt(form.fieldNames,i)])){
variables.goodFields =
listAppend(variables.goodFields,listGetAt(form.fieldNames,i));
}
}
// this is the total count of the good field names
goodCount = listLen(variables.goodFields);
// now that we have the list of good fields we have to get the list of
fields that must be set back to zero
variables.myList = sol1,sol2,sol3,sol4,sol5,sol6,sol7,sol8;
zerolist = '';
for (i=1; i LTE ListLen(myList, ,); i=i+1) {
if (NOT ListFindNoCase(form.fieldNames, ListGetAt(myList, i, ,),
,)){
zeroList = ListAppend(zeroList, ListGetAt(myList, i, ,), ,);
}
}
// this is the total count of the zero field names
zeroCount = listLen(variables.zeroList);
// append the two list to each other and count them to know how many loops
we will have until there are no more commas
totalCount = zeroCount + goodCount;
// this variable will keep track of how many total loops have been
executed
loopCount = 1;
// now we will begin to generate the sql statement
for(i = 1; i lte goodCount; i = i + 1){
if(lcase(listGetAt(goodFields, i)) neq printerid){
// get the current field
thisField = listGetAt(goodFields, i);
// append it to the query string
variables.myQuery = variables.myQuery thisField = ;
if(isNumeric(form[thisField])){
variables.myQuery = variables.myQuery form[thisField];
}else{
variables.myQuery = variables.myQuery ' form[thisField] ';
}
if(loopCount neq totalCount){
variables.myQuery = variables.myQuery , ;
}
}
// incriment loopCount to keep track of the total loops
loopCount = loopCount + 1;
}
// now we add the ones that have to be set back to zero to the query
for(i = 1; i lte zeroCount; i = i + 1){
// get the current field
thisField = listGetAt(zeroList, i);
// append the value to the query
variables.myQuery = variables.myQuery thisField = 0;
if(loopCount neq totalCount){
variables.myQuery = variables.myQuery , ;
}
// incriment loopCount to keep track of the total loops
loopCount = loopCount + 1;
}
/cfscript
---
[This E-mail scanned for viruses by Declude Virus]
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Get the mailserver that powers this list at
http://www.coolfusion.com
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Access SQL Question :(
try to use [ ] in column names, like [MAKE], you can also partially delete
the sql statement one by one to find the bad row.
- Original Message -
From: Tim Heald [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Friday, August 01, 2003 4:19 PM
Subject: Access SQL Question :(
Evening,
I am trying to work out an update statement for Access. It's on CF 4.5.
I
am generating the SQL in a cfscript, it will be below. The final script
looks like this:
update printers set MAKE = 'new make', MODEL = 'new model', PRODUCTTYPE =
'new product', PRINTERGROUP = 'new grogugp', CATEGORY = 'new cgategory',
CSPEED = 0, ONSALEPRICE = '$0.00', MSRP = '$0.00', SOL2 = 1, SOL4 = 1,
SOL6
= 1, SOL7 = 1, SOL8 = 1, sol1 = 0, sol3 = 0, sol5 = 0 where printerID =
120
It runs fine in Access, but throws this through CF:
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator)
in
query expression '''new make'''.
Any Ideas? BTW, this is a dynamic script I am writing, the form has a
great
many fields, and would just be a major pain to write conditionals for each
and every one.
Tim
cfscript
// this is the beginning of the query string, I will append values to it
as
I go
variables.myQuery = 'update printers set ';
// loop through form.fieldnames and find those that have a length and
append them to the goodFields list
variables.goodFields = '';
for(i = 1; i lte listLen(form.fieldNames); i = i + 1){
if(len(form[listGetAt(form.fieldNames,i)])){
variables.goodFields =
listAppend(variables.goodFields,listGetAt(form.fieldNames,i));
}
}
// this is the total count of the good field names
goodCount = listLen(variables.goodFields);
// now that we have the list of good fields we have to get the list of
fields that must be set back to zero
variables.myList = sol1,sol2,sol3,sol4,sol5,sol6,sol7,sol8;
zerolist = '';
for (i=1; i LTE ListLen(myList, ,); i=i+1) {
if (NOT ListFindNoCase(form.fieldNames, ListGetAt(myList, i, ,),
,)){
zeroList = ListAppend(zeroList, ListGetAt(myList, i, ,), ,);
}
}
// this is the total count of the zero field names
zeroCount = listLen(variables.zeroList);
// append the two list to each other and count them to know how many loops
we will have until there are no more commas
totalCount = zeroCount + goodCount;
// this variable will keep track of how many total loops have been
executed
loopCount = 1;
// now we will begin to generate the sql statement
for(i = 1; i lte goodCount; i = i + 1){
if(lcase(listGetAt(goodFields, i)) neq printerid){
// get the current field
thisField = listGetAt(goodFields, i);
// append it to the query string
variables.myQuery = variables.myQuery thisField = ;
if(isNumeric(form[thisField])){
variables.myQuery = variables.myQuery form[thisField];
}else{
variables.myQuery = variables.myQuery ' form[thisField] ';
}
if(loopCount neq totalCount){
variables.myQuery = variables.myQuery , ;
}
}
// incriment loopCount to keep track of the total loops
loopCount = loopCount + 1;
}
// now we add the ones that have to be set back to zero to the query
for(i = 1; i lte zeroCount; i = i + 1){
// get the current field
thisField = listGetAt(zeroList, i);
// append the value to the query
variables.myQuery = variables.myQuery thisField = 0;
if(loopCount neq totalCount){
variables.myQuery = variables.myQuery , ;
}
// incriment loopCount to keep track of the total loops
loopCount = loopCount + 1;
}
/cfscript
---
[This E-mail scanned for viruses by Declude Virus]
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics.
http://www.fusionauthority.com/signup.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Access SQL Question :(
The query you see is my output from the variable I created containing the
SQL statement.
Man I hate access.
Tim
-Original Message-
From: Michael T. Tangorre [mailto:[EMAIL PROTECTED]
Sent: Friday, August 01, 2003 9:28 AM
To: CF-Talk
Subject: Re: Access SQL Question :(
can you show us what the query looks like in the debug output? Wait, does CF
4.5 output that i forget. :-) If so, send that to the list. That would
let us see what the dynamic query looked like when it failed i think,
and we can go from there.
mike
- Original Message -
From: Tim Heald [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Friday, August 01, 2003 9:19 AM
Subject: Access SQL Question :(
Evening,
I am trying to work out an update statement for Access. It's on CF 4.5.
I
am generating the SQL in a cfscript, it will be below. The final script
looks like this:
update printers set MAKE = 'new make', MODEL = 'new model', PRODUCTTYPE =
'new product', PRINTERGROUP = 'new grogugp', CATEGORY = 'new cgategory',
CSPEED = 0, ONSALEPRICE = '$0.00', MSRP = '$0.00', SOL2 = 1, SOL4 = 1,
SOL6
= 1, SOL7 = 1, SOL8 = 1, sol1 = 0, sol3 = 0, sol5 = 0 where printerID =
120
It runs fine in Access, but throws this through CF:
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator)
in
query expression '''new make'''.
Any Ideas? BTW, this is a dynamic script I am writing, the form has a
great
many fields, and would just be a major pain to write conditionals for each
and every one.
Tim
cfscript
// this is the beginning of the query string, I will append values to it
as
I go
variables.myQuery = 'update printers set ';
// loop through form.fieldnames and find those that have a length and
append them to the goodFields list
variables.goodFields = '';
for(i = 1; i lte listLen(form.fieldNames); i = i + 1){
if(len(form[listGetAt(form.fieldNames,i)])){
variables.goodFields =
listAppend(variables.goodFields,listGetAt(form.fieldNames,i));
}
}
// this is the total count of the good field names
goodCount = listLen(variables.goodFields);
// now that we have the list of good fields we have to get the list of
fields that must be set back to zero
variables.myList = sol1,sol2,sol3,sol4,sol5,sol6,sol7,sol8;
zerolist = '';
for (i=1; i LTE ListLen(myList, ,); i=i+1) {
if (NOT ListFindNoCase(form.fieldNames, ListGetAt(myList, i, ,),
,)){
zeroList = ListAppend(zeroList, ListGetAt(myList, i, ,), ,);
}
}
// this is the total count of the zero field names
zeroCount = listLen(variables.zeroList);
// append the two list to each other and count them to know how many loops
we will have until there are no more commas
totalCount = zeroCount + goodCount;
// this variable will keep track of how many total loops have been
executed
loopCount = 1;
// now we will begin to generate the sql statement
for(i = 1; i lte goodCount; i = i + 1){
if(lcase(listGetAt(goodFields, i)) neq printerid){
// get the current field
thisField = listGetAt(goodFields, i);
// append it to the query string
variables.myQuery = variables.myQuery thisField = ;
if(isNumeric(form[thisField])){
variables.myQuery = variables.myQuery form[thisField];
}else{
variables.myQuery = variables.myQuery ' form[thisField] ';
}
if(loopCount neq totalCount){
variables.myQuery = variables.myQuery , ;
}
}
// incriment loopCount to keep track of the total loops
loopCount = loopCount + 1;
}
// now we add the ones that have to be set back to zero to the query
for(i = 1; i lte zeroCount; i = i + 1){
// get the current field
thisField = listGetAt(zeroList, i);
// append the value to the query
variables.myQuery = variables.myQuery thisField = 0;
if(loopCount neq totalCount){
variables.myQuery = variables.myQuery , ;
}
// incriment loopCount to keep track of the total loops
loopCount = loopCount + 1;
}
/cfscript
---
[This E-mail scanned for viruses by Declude Virus]
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Get the mailserver that powers this list at
http://www.coolfusion.com
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Access SQL Question :(
Here's what it's producing now:
update printers set [MAKE] = 'new make', [MODEL] = 'new model',
[PRODUCTTYPE] = 'new product', [PRINTERGROUP] = 'new grogugp', [CATEGORY] =
'new cgategory', [CSPEED] = 0, [ONSALEPRICE] = '$0.00', [MSRP] = '$0.00',
[SOL2] = 1, [SOL4] = 1, [SOL6] = 1, [SOL8] = 1, [sol1] = 0, [sol3] = 0,
[sol5] = 0, [sol7] = 0 where printerID = 120
Same errror:
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in
query expression '''new make'''
But it runs fine in SQL view in access.
Weird
Tim
-Original Message-
From: Tim Heald [mailto:[EMAIL PROTECTED]
Sent: Friday, August 01, 2003 9:48 AM
To: CF-Talk
Subject: RE: Access SQL Question :(
The query you see is my output from the variable I created containing the
SQL statement.
Man I hate access.
Tim
-Original Message-
From: Michael T. Tangorre [mailto:[EMAIL PROTECTED]
Sent: Friday, August 01, 2003 9:28 AM
To: CF-Talk
Subject: Re: Access SQL Question :(
can you show us what the query looks like in the debug output? Wait, does CF
4.5 output that i forget. :-) If so, send that to the list. That would
let us see what the dynamic query looked like when it failed i think,
and we can go from there.
mike
- Original Message -
From: Tim Heald [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Friday, August 01, 2003 9:19 AM
Subject: Access SQL Question :(
Evening,
I am trying to work out an update statement for Access. It's on CF 4.5.
I
am generating the SQL in a cfscript, it will be below. The final script
looks like this:
update printers set MAKE = 'new make', MODEL = 'new model', PRODUCTTYPE =
'new product', PRINTERGROUP = 'new grogugp', CATEGORY = 'new cgategory',
CSPEED = 0, ONSALEPRICE = '$0.00', MSRP = '$0.00', SOL2 = 1, SOL4 = 1,
SOL6
= 1, SOL7 = 1, SOL8 = 1, sol1 = 0, sol3 = 0, sol5 = 0 where printerID =
120
It runs fine in Access, but throws this through CF:
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator)
in
query expression '''new make'''.
Any Ideas? BTW, this is a dynamic script I am writing, the form has a
great
many fields, and would just be a major pain to write conditionals for each
and every one.
Tim
cfscript
// this is the beginning of the query string, I will append values to it
as
I go
variables.myQuery = 'update printers set ';
// loop through form.fieldnames and find those that have a length and
append them to the goodFields list
variables.goodFields = '';
for(i = 1; i lte listLen(form.fieldNames); i = i + 1){
if(len(form[listGetAt(form.fieldNames,i)])){
variables.goodFields =
listAppend(variables.goodFields,listGetAt(form.fieldNames,i));
}
}
// this is the total count of the good field names
goodCount = listLen(variables.goodFields);
// now that we have the list of good fields we have to get the list of
fields that must be set back to zero
variables.myList = sol1,sol2,sol3,sol4,sol5,sol6,sol7,sol8;
zerolist = '';
for (i=1; i LTE ListLen(myList, ,); i=i+1) {
if (NOT ListFindNoCase(form.fieldNames, ListGetAt(myList, i, ,),
,)){
zeroList = ListAppend(zeroList, ListGetAt(myList, i, ,), ,);
}
}
// this is the total count of the zero field names
zeroCount = listLen(variables.zeroList);
// append the two list to each other and count them to know how many loops
we will have until there are no more commas
totalCount = zeroCount + goodCount;
// this variable will keep track of how many total loops have been
executed
loopCount = 1;
// now we will begin to generate the sql statement
for(i = 1; i lte goodCount; i = i + 1){
if(lcase(listGetAt(goodFields, i)) neq printerid){
// get the current field
thisField = listGetAt(goodFields, i);
// append it to the query string
variables.myQuery = variables.myQuery thisField = ;
if(isNumeric(form[thisField])){
variables.myQuery = variables.myQuery form[thisField];
}else{
variables.myQuery = variables.myQuery ' form[thisField] ';
}
if(loopCount neq totalCount){
variables.myQuery = variables.myQuery , ;
}
}
// incriment loopCount to keep track of the total loops
loopCount = loopCount + 1;
}
// now we add the ones that have to be set back to zero to the query
for(i = 1; i lte zeroCount; i = i + 1){
// get the current field
thisField = listGetAt(zeroList, i);
// append the value to the query
variables.myQuery = variables.myQuery thisField = 0;
if(loopCount neq totalCount){
variables.myQuery = variables.myQuery , ;
}
// incriment loopCount to keep track of the total loops
loopCount = loopCount + 1;
}
/cfscript
---
[This E-mail scanned for viruses by Declude Virus]
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists
Re: Access SQL Question :(
what are the data types for the columns?
what version of access of are you using?
- Original Message -
From: Tim Heald [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Friday, August 01, 2003 9:52 AM
Subject: RE: Access SQL Question :(
Here's what it's producing now:
update printers set [MAKE] = 'new make', [MODEL] = 'new model',
[PRODUCTTYPE] = 'new product', [PRINTERGROUP] = 'new grogugp', [CATEGORY]
=
'new cgategory', [CSPEED] = 0, [ONSALEPRICE] = '$0.00', [MSRP] = '$0.00',
[SOL2] = 1, [SOL4] = 1, [SOL6] = 1, [SOL8] = 1, [sol1] = 0, [sol3] = 0,
[sol5] = 0, [sol7] = 0 where printerID = 120
Same errror:
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator)
in
query expression '''new make'''
But it runs fine in SQL view in access.
Weird
Tim
-Original Message-
From: Tim Heald [mailto:[EMAIL PROTECTED]
Sent: Friday, August 01, 2003 9:48 AM
To: CF-Talk
Subject: RE: Access SQL Question :(
The query you see is my output from the variable I created containing the
SQL statement.
Man I hate access.
Tim
-Original Message-
From: Michael T. Tangorre [mailto:[EMAIL PROTECTED]
Sent: Friday, August 01, 2003 9:28 AM
To: CF-Talk
Subject: Re: Access SQL Question :(
can you show us what the query looks like in the debug output? Wait, does
CF
4.5 output that i forget. :-) If so, send that to the list. That
would
let us see what the dynamic query looked like when it failed i think,
and we can go from there.
mike
- Original Message -
From: Tim Heald [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Friday, August 01, 2003 9:19 AM
Subject: Access SQL Question :(
Evening,
I am trying to work out an update statement for Access. It's on CF 4.5.
I
am generating the SQL in a cfscript, it will be below. The final script
looks like this:
update printers set MAKE = 'new make', MODEL = 'new model', PRODUCTTYPE
=
'new product', PRINTERGROUP = 'new grogugp', CATEGORY = 'new cgategory',
CSPEED = 0, ONSALEPRICE = '$0.00', MSRP = '$0.00', SOL2 = 1, SOL4 = 1,
SOL6
= 1, SOL7 = 1, SOL8 = 1, sol1 = 0, sol3 = 0, sol5 = 0 where printerID =
120
It runs fine in Access, but throws this through CF:
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing
operator)
in
query expression '''new make'''.
Any Ideas? BTW, this is a dynamic script I am writing, the form has a
great
many fields, and would just be a major pain to write conditionals for
each
and every one.
Tim
cfscript
// this is the beginning of the query string, I will append values to it
as
I go
variables.myQuery = 'update printers set ';
// loop through form.fieldnames and find those that have a length and
append them to the goodFields list
variables.goodFields = '';
for(i = 1; i lte listLen(form.fieldNames); i = i + 1){
if(len(form[listGetAt(form.fieldNames,i)])){
variables.goodFields =
listAppend(variables.goodFields,listGetAt(form.fieldNames,i));
}
}
// this is the total count of the good field names
goodCount = listLen(variables.goodFields);
// now that we have the list of good fields we have to get the list of
fields that must be set back to zero
variables.myList = sol1,sol2,sol3,sol4,sol5,sol6,sol7,sol8;
zerolist = '';
for (i=1; i LTE ListLen(myList, ,); i=i+1) {
if (NOT ListFindNoCase(form.fieldNames, ListGetAt(myList, i, ,),
,)){
zeroList = ListAppend(zeroList, ListGetAt(myList, i, ,), ,);
}
}
// this is the total count of the zero field names
zeroCount = listLen(variables.zeroList);
// append the two list to each other and count them to know how many
loops
we will have until there are no more commas
totalCount = zeroCount + goodCount;
// this variable will keep track of how many total loops have been
executed
loopCount = 1;
// now we will begin to generate the sql statement
for(i = 1; i lte goodCount; i = i + 1){
if(lcase(listGetAt(goodFields, i)) neq printerid){
// get the current field
thisField = listGetAt(goodFields, i);
// append it to the query string
variables.myQuery = variables.myQuery thisField = ;
if(isNumeric(form[thisField])){
variables.myQuery = variables.myQuery form[thisField];
}else{
variables.myQuery = variables.myQuery ' form[thisField] ';
}
if(loopCount neq totalCount){
variables.myQuery = variables.myQuery , ;
}
}
// incriment loopCount to keep track of the total loops
loopCount = loopCount + 1;
}
// now we add the ones that have to be set back to zero to the query
for(i = 1; i lte zeroCount; i = i + 1){
// get the current field
thisField = listGetAt(zeroList, i);
// append the value to the query
variables.myQuery = variables.myQuery thisField = 0;
if(loopCount neq totalCount
RE: Access SQL Question :(
Tim,
AS has been mentioned, if you are creating a string and passing it to the
cfquery tag, make sure and use preserveSinglequotes( ) around it:
cfquery name=update datasource=blah
#preservesinglequotes(myQueryString)#
/cfquery
If you do not, the CF escapes the single quotes FOR you and you end up
with:
update printers set MAKE = '' new make'',
So the driver interprets that as Make = ''... then it trys to do something
with the word new. Since there is no comma, it is expecting a WHERE or
some other key word - hence the violation. Why don't you send the full
detaisl of the error to the list - where the sytax is visible in the
details. That might be a better clue.
-Mark
-Original Message-
From: Tim Heald [mailto:[EMAIL PROTECTED]
Sent: Friday, August 01, 2003 8:19 AM
To: CF-Talk
Subject: Access SQL Question :(
Evening,
I am trying to work out an update statement for Access. It's on CF 4.5. I
am generating the SQL in a cfscript, it will be below. The final script
looks like this:
update printers set MAKE = 'new make', MODEL = 'new model', PRODUCTTYPE =
'new product', PRINTERGROUP = 'new grogugp', CATEGORY = 'new cgategory',
CSPEED = 0, ONSALEPRICE = '$0.00', MSRP = '$0.00', SOL2 = 1, SOL4 = 1, SOL6
= 1, SOL7 = 1, SOL8 = 1, sol1 = 0, sol3 = 0, sol5 = 0 where printerID = 120
It runs fine in Access, but throws this through CF:
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in
query expression '''new make'''.
Any Ideas? BTW, this is a dynamic script I am writing, the form has a great
many fields, and would just be a major pain to write conditionals for each
and every one.
Tim
cfscript
// this is the beginning of the query string, I will append values to it as
I go
variables.myQuery = 'update printers set ';
// loop through form.fieldnames and find those that have a length and
append them to the goodFields list
variables.goodFields = '';
for(i = 1; i lte listLen(form.fieldNames); i = i + 1){
if(len(form[listGetAt(form.fieldNames,i)])){
variables.goodFields =
listAppend(variables.goodFields,listGetAt(form.fieldNames,i));
}
}
// this is the total count of the good field names
goodCount = listLen(variables.goodFields);
// now that we have the list of good fields we have to get the list of
fields that must be set back to zero
variables.myList = sol1,sol2,sol3,sol4,sol5,sol6,sol7,sol8;
zerolist = '';
for (i=1; i LTE ListLen(myList, ,); i=i+1) {
if (NOT ListFindNoCase(form.fieldNames, ListGetAt(myList, i, ,),
,)){
zeroList = ListAppend(zeroList, ListGetAt(myList, i, ,), ,);
}
}
// this is the total count of the zero field names
zeroCount = listLen(variables.zeroList);
// append the two list to each other and count them to know how many loops
we will have until there are no more commas
totalCount = zeroCount + goodCount;
// this variable will keep track of how many total loops have been executed
loopCount = 1;
// now we will begin to generate the sql statement
for(i = 1; i lte goodCount; i = i + 1){
if(lcase(listGetAt(goodFields, i)) neq printerid){
// get the current field
thisField = listGetAt(goodFields, i);
// append it to the query string
variables.myQuery = variables.myQuery thisField = ;
if(isNumeric(form[thisField])){
variables.myQuery = variables.myQuery
form[thisField];
}else{
variables.myQuery = variables.myQuery '
form[thisField] ';
}
if(loopCount neq totalCount){
variables.myQuery = variables.myQuery , ;
}
}
// incriment loopCount to keep track of the total loops
loopCount = loopCount + 1;
}
// now we add the ones that have to be set back to zero to the query
for(i = 1; i lte zeroCount; i = i + 1){
// get the current field
thisField = listGetAt(zeroList, i);
// append the value to the query
variables.myQuery = variables.myQuery thisField = 0;
if(loopCount neq totalCount){
variables.myQuery = variables.myQuery , ;
}
// incriment loopCount to keep track of the total loops
loopCount = loopCount + 1;
}
/cfscript
---
[This E-mail scanned for viruses by Declude Virus]
~|
RE: Access SQL Question :(
ohhh I thought it was around the field value.
You got it.
Thanks everyone
Tim
-Original Message-
From: Mark A. Kruger - CFG [mailto:[EMAIL PROTECTED]
Sent: Friday, August 01, 2003 10:05 AM
To: CF-Talk
Subject: RE: Access SQL Question :(
Tim,
AS has been mentioned, if you are creating a string and passing it to the
cfquery tag, make sure and use preserveSinglequotes( ) around it:
cfquery name=update datasource=blah
#preservesinglequotes(myQueryString)#
/cfquery
If you do not, the CF escapes the single quotes FOR you and you end up
with:
update printers set MAKE = '' new make'',
So the driver interprets that as Make = ''... then it trys to do something
with the word new. Since there is no comma, it is expecting a WHERE or
some other key word - hence the violation. Why don't you send the full
detaisl of the error to the list - where the sytax is visible in the
details. That might be a better clue.
-Mark
-Original Message-
From: Tim Heald [mailto:[EMAIL PROTECTED]
Sent: Friday, August 01, 2003 8:19 AM
To: CF-Talk
Subject: Access SQL Question :(
Evening,
I am trying to work out an update statement for Access. It's on CF 4.5. I
am generating the SQL in a cfscript, it will be below. The final script
looks like this:
update printers set MAKE = 'new make', MODEL = 'new model', PRODUCTTYPE =
'new product', PRINTERGROUP = 'new grogugp', CATEGORY = 'new cgategory',
CSPEED = 0, ONSALEPRICE = '$0.00', MSRP = '$0.00', SOL2 = 1, SOL4 = 1, SOL6
= 1, SOL7 = 1, SOL8 = 1, sol1 = 0, sol3 = 0, sol5 = 0 where printerID = 120
It runs fine in Access, but throws this through CF:
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in
query expression '''new make'''.
Any Ideas? BTW, this is a dynamic script I am writing, the form has a great
many fields, and would just be a major pain to write conditionals for each
and every one.
Tim
cfscript
// this is the beginning of the query string, I will append values to it as
I go
variables.myQuery = 'update printers set ';
// loop through form.fieldnames and find those that have a length and
append them to the goodFields list
variables.goodFields = '';
for(i = 1; i lte listLen(form.fieldNames); i = i + 1){
if(len(form[listGetAt(form.fieldNames,i)])){
variables.goodFields =
listAppend(variables.goodFields,listGetAt(form.fieldNames,i));
}
}
// this is the total count of the good field names
goodCount = listLen(variables.goodFields);
// now that we have the list of good fields we have to get the list of
fields that must be set back to zero
variables.myList = sol1,sol2,sol3,sol4,sol5,sol6,sol7,sol8;
zerolist = '';
for (i=1; i LTE ListLen(myList, ,); i=i+1) {
if (NOT ListFindNoCase(form.fieldNames, ListGetAt(myList, i, ,),
,)){
zeroList = ListAppend(zeroList, ListGetAt(myList, i, ,), ,);
}
}
// this is the total count of the zero field names
zeroCount = listLen(variables.zeroList);
// append the two list to each other and count them to know how many loops
we will have until there are no more commas
totalCount = zeroCount + goodCount;
// this variable will keep track of how many total loops have been executed
loopCount = 1;
// now we will begin to generate the sql statement
for(i = 1; i lte goodCount; i = i + 1){
if(lcase(listGetAt(goodFields, i)) neq printerid){
// get the current field
thisField = listGetAt(goodFields, i);
// append it to the query string
variables.myQuery = variables.myQuery thisField = ;
if(isNumeric(form[thisField])){
variables.myQuery = variables.myQuery
form[thisField];
}else{
variables.myQuery = variables.myQuery '
form[thisField] ';
}
if(loopCount neq totalCount){
variables.myQuery = variables.myQuery , ;
}
}
// incriment loopCount to keep track of the total loops
loopCount = loopCount + 1;
}
// now we add the ones that have to be set back to zero to the query
for(i = 1; i lte zeroCount; i = i + 1){
// get the current field
thisField = listGetAt(zeroList, i);
// append the value to the query
variables.myQuery = variables.myQuery thisField = 0;
if(loopCount neq totalCount){
variables.myQuery = variables.myQuery
RE: Access SQL Question :(
no problem...
-Original Message-
From: Tim Heald [mailto:[EMAIL PROTECTED]
Sent: Friday, August 01, 2003 9:08 AM
To: CF-Talk
Subject: RE: Access SQL Question :(
ohhh I thought it was around the field value.
You got it.
Thanks everyone
Tim
-Original Message-
From: Mark A. Kruger - CFG [mailto:[EMAIL PROTECTED]
Sent: Friday, August 01, 2003 10:05 AM
To: CF-Talk
Subject: RE: Access SQL Question :(
Tim,
AS has been mentioned, if you are creating a string and passing it to the
cfquery tag, make sure and use preserveSinglequotes( ) around it:
cfquery name=update datasource=blah
#preservesinglequotes(myQueryString)#
/cfquery
If you do not, the CF escapes the single quotes FOR you and you end up
with:
update printers set MAKE = '' new make'',
So the driver interprets that as Make = ''... then it trys to do something
with the word new. Since there is no comma, it is expecting a WHERE or
some other key word - hence the violation. Why don't you send the full
detaisl of the error to the list - where the sytax is visible in the
details. That might be a better clue.
-Mark
-Original Message-
From: Tim Heald [mailto:[EMAIL PROTECTED]
Sent: Friday, August 01, 2003 8:19 AM
To: CF-Talk
Subject: Access SQL Question :(
Evening,
I am trying to work out an update statement for Access. It's on CF 4.5. I
am generating the SQL in a cfscript, it will be below. The final script
looks like this:
update printers set MAKE = 'new make', MODEL = 'new model', PRODUCTTYPE =
'new product', PRINTERGROUP = 'new grogugp', CATEGORY = 'new cgategory',
CSPEED = 0, ONSALEPRICE = '$0.00', MSRP = '$0.00', SOL2 = 1, SOL4 = 1, SOL6
= 1, SOL7 = 1, SOL8 = 1, sol1 = 0, sol3 = 0, sol5 = 0 where printerID = 120
It runs fine in Access, but throws this through CF:
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in
query expression '''new make'''.
Any Ideas? BTW, this is a dynamic script I am writing, the form has a great
many fields, and would just be a major pain to write conditionals for each
and every one.
Tim
cfscript
// this is the beginning of the query string, I will append values to it as
I go
variables.myQuery = 'update printers set ';
// loop through form.fieldnames and find those that have a length and
append them to the goodFields list
variables.goodFields = '';
for(i = 1; i lte listLen(form.fieldNames); i = i + 1){
if(len(form[listGetAt(form.fieldNames,i)])){
variables.goodFields =
listAppend(variables.goodFields,listGetAt(form.fieldNames,i));
}
}
// this is the total count of the good field names
goodCount = listLen(variables.goodFields);
// now that we have the list of good fields we have to get the list of
fields that must be set back to zero
variables.myList = sol1,sol2,sol3,sol4,sol5,sol6,sol7,sol8;
zerolist = '';
for (i=1; i LTE ListLen(myList, ,); i=i+1) {
if (NOT ListFindNoCase(form.fieldNames, ListGetAt(myList, i, ,),
,)){
zeroList = ListAppend(zeroList, ListGetAt(myList, i, ,), ,);
}
}
// this is the total count of the zero field names
zeroCount = listLen(variables.zeroList);
// append the two list to each other and count them to know how many loops
we will have until there are no more commas
totalCount = zeroCount + goodCount;
// this variable will keep track of how many total loops have been executed
loopCount = 1;
// now we will begin to generate the sql statement
for(i = 1; i lte goodCount; i = i + 1){
if(lcase(listGetAt(goodFields, i)) neq printerid){
// get the current field
thisField = listGetAt(goodFields, i);
// append it to the query string
variables.myQuery = variables.myQuery thisField = ;
if(isNumeric(form[thisField])){
variables.myQuery = variables.myQuery
form[thisField];
}else{
variables.myQuery = variables.myQuery '
form[thisField] ';
}
if(loopCount neq totalCount){
variables.myQuery = variables.myQuery , ;
}
}
// incriment loopCount to keep track of the total loops
loopCount = loopCount + 1;
}
// now we add the ones that have to be set back to zero to the query
for(i = 1; i lte zeroCount; i = i + 1){
// get the current field
thisField = listGetAt(zeroList, i);
// append the value to the query
variables.myQuery
Re: Access SQL question
This one works fine in sql server, but you need to test it with ms access.
SELECT RIGHT(emailAddress, CHARINDEX('@', REVERSE(emailAddress))-1) AS
[domainName]
FROM dbo.customers
GROUP BY RIGHT(emailAddress, CHARINDEX('@', REVERSE(emailAddress))-1)
ORDER BY [domainName]
- Original Message -
From: [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Sent: Sunday, March 16, 2003 9:29 AM
Subject: Access SQL question
Hi all:
I have an Access 2000 database with an email field in it.
I would like to have an SQL query that gives me a count of the ISPs
in the email field.
For example:
take the following records:
1. [EMAIL PROTECTED]
2. [EMAIL PROTECTED]
3. [EMAIL PROTECTED]
4. [EMAIL PROTECTED]
5. [EMAIL PROTECTED]
I would like a query to give me:
1. aol.com 2
2. hotmail.com 3
I have tried many ways, but can't get it to work.
any suggestions?
thanks
Sandy
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
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
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Access SQL Question - Round 2
No access guru, (anymore:-/) but try SELECT Firstinlist as (or = ???)left(issues.owner, inStr(issues.owner, ',')-1) FROM issues, users WHERE Firstinlist = users.name Something like that... WG -Original Message- From: Jeff Chastain [mailto:[EMAIL PROTECTED]] Sent: 17 January 2003 15:44 To: CF-Talk Subject: WOT: Access SQL Question - Round 2 Okay, I am still having problems with this ... so here is an attempt to simplify the problem. I have a hand-me-down Access database which I am trying to get some data out of. I have a table called issues with a field called owner. The owner field may contain a single name, or it may contain a comma-separated list of names. I have a table called users with a field called name. I am needing to join the two tables up based on the first or only name in the issues.owner field matched to the users.name field. Whenever I try using inStr in the WHERE clause, access throws an invalid procedure error. Anybody got any suggestions? This does not seem to work with Access and even if it did, it would only work for records with a list of users in the issues table, not just a single entry SELECT * FROM issues, users WHERE left(issues.owner, inStr(issues.owner, ',')-1) = users.name Thanks -- Jeff ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Access SQL Question - Round 2
No, that is along the lines of what I have been trying without any luck. Okay, I have the following tables and data: table: issues (id, owner) 1 Doe 2 Klein, Doe 3 James 4 Doe, James table: owners (name, email) Doe [EMAIL PROTECTED] James[EMAIL PROTECTED] Klein[EMAIL PROTECTED] The results I need to get back are ... (issue.id, owners.name, owners.email) 1 Doe [EMAIL PROTECTED] 2 Klein [EMAIL PROTECTED] 3 James [EMAIL PROTECTED] 4 Doe [EMAIL PROTECTED] Where the name and email come from the first (or only) name in the issues table. I would not think that this would be that hard, but for some reason it appears to be. Thanks -- Jeff -- Original Message -- From: webguy [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] Date: Fri, 17 Jan 2003 15:56:19 - No access guru, (anymore:-/) but try SELECT Firstinlist as (or = ???)left(issues.owner, inStr(issues.owner, ',')-1) FROM issues, users WHERE Firstinlist = users.name Something like that... WG -Original Message- From: Jeff Chastain [mailto:[EMAIL PROTECTED]] Sent: 17 January 2003 15:44 To: CF-Talk Subject: WOT: Access SQL Question - Round 2 Okay, I am still having problems with this ... so here is an attempt to simplify the problem. I have a hand-me-down Access database which I am trying to get some data out of. I have a table called issues with a field called owner. The owner field may contain a single name, or it may contain a comma-separated list of names. I have a table called users with a field called name. I am needing to join the two tables up based on the first or only name in the issues.owner field matched to the users.name field. Whenever I try using inStr in the WHERE clause, access throws an invalid procedure error. Anybody got any suggestions? This does not seem to work with Access and even if it did, it would only work for records with a list of users in the issues table, not just a single entry SELECT * FROM issues, users WHERE left(issues.owner, inStr(issues.owner, ',')-1) = users.name Thanks -- Jeff ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Access SQL Question - Round 2
I'm not sure but try puting the left(issues.owner, inStr(issues.owner, ',')-1) up in the select as left(issues.owner, inStr(issues.owner, ',')-1) as LastName. That should get rid of the error. -Original Message- From: Jeff Chastain [mailto:[EMAIL PROTECTED]] Sent: Friday, January 17, 2003 11:19 AM To: CF-Talk Subject: RE: Access SQL Question - Round 2 No, that is along the lines of what I have been trying without any luck. Okay, I have the following tables and data: table: issues (id, owner) 1 Doe 2 Klein, Doe 3 James 4 Doe, James table: owners (name, email) Doe [EMAIL PROTECTED] James[EMAIL PROTECTED] Klein[EMAIL PROTECTED] The results I need to get back are ... (issue.id, owners.name, owners.email) 1 Doe [EMAIL PROTECTED] 2 Klein [EMAIL PROTECTED] 3 James [EMAIL PROTECTED] 4 Doe [EMAIL PROTECTED] Where the name and email come from the first (or only) name in the issues table. I would not think that this would be that hard, but for some reason it appears to be. Thanks -- Jeff -- Original Message -- From: webguy [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] Date: Fri, 17 Jan 2003 15:56:19 - No access guru, (anymore:-/) but try SELECT Firstinlist as (or = ???)left(issues.owner, inStr(issues.owner, ',')-1) FROM issues, users WHERE Firstinlist = users.name Something like that... WG -Original Message- From: Jeff Chastain [mailto:[EMAIL PROTECTED]] Sent: 17 January 2003 15:44 To: CF-Talk Subject: WOT: Access SQL Question - Round 2 Okay, I am still having problems with this ... so here is an attempt to simplify the problem. I have a hand-me-down Access database which I am trying to get some data out of. I have a table called issues with a field called owner. The owner field may contain a single name, or it may contain a comma-separated list of names. I have a table called users with a field called name. I am needing to join the two tables up based on the first or only name in the issues.owner field matched to the users.name field. Whenever I try using inStr in the WHERE clause, access throws an invalid procedure error. Anybody got any suggestions? This does not seem to work with Access and even if it did, it would only work for records with a list of users in the issues table, not just a single entry SELECT * FROM issues, users WHERE left(issues.owner, inStr(issues.owner, ',')-1) = users.name Thanks -- Jeff ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Access SQL Question - Round 2
That is where 'webguy' had it - he just had the AS clause reversed. Am I missing something? -- Original Message -- From: [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] Date: Fri, 17 Jan 2003 11:44:41 -0500 I'm not sure but try puting the left(issues.owner, inStr(issues.owner, ',')-1) up in the select as left(issues.owner, inStr(issues.owner, ',')-1) as LastName. That should get rid of the error. -Original Message- From: Jeff Chastain [mailto:[EMAIL PROTECTED]] Sent: Friday, January 17, 2003 11:19 AM To: CF-Talk Subject: RE: Access SQL Question - Round 2 No, that is along the lines of what I have been trying without any luck. Okay, I have the following tables and data: table: issues (id, owner) 1 Doe 2 Klein, Doe 3 James 4 Doe, James table: owners (name, email) Doe [EMAIL PROTECTED] James[EMAIL PROTECTED] Klein[EMAIL PROTECTED] The results I need to get back are ... (issue.id, owners.name, owners.email) 1 Doe [EMAIL PROTECTED] 2 Klein [EMAIL PROTECTED] 3 James [EMAIL PROTECTED] 4 Doe [EMAIL PROTECTED] Where the name and email come from the first (or only) name in the issues table. I would not think that this would be that hard, but for some reason it appears to be. Thanks -- Jeff -- Original Message -- From: webguy [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] Date: Fri, 17 Jan 2003 15:56:19 - No access guru, (anymore:-/) but try SELECT Firstinlist as (or = ???)left(issues.owner, inStr(issues.owner, ',')-1) FROM issues, users WHERE Firstinlist = users.name Something like that... WG -Original Message- From: Jeff Chastain [mailto:[EMAIL PROTECTED]] Sent: 17 January 2003 15:44 To: CF-Talk Subject: WOT: Access SQL Question - Round 2 Okay, I am still having problems with this ... so here is an attempt to simplify the problem. I have a hand-me-down Access database which I am trying to get some data out of. I have a table called issues with a field called owner. The owner field may contain a single name, or it may contain a comma-separated list of names. I have a table called users with a field called name. I am needing to join the two tables up based on the first or only name in the issues.owner field matched to the users.name field. Whenever I try using inStr in the WHERE clause, access throws an invalid procedure error. Anybody got any suggestions? This does not seem to work with Access and even if it did, it would only work for records with a list of users in the issues table, not just a single entry SELECT * FROM issues, users WHERE left(issues.owner, inStr(issues.owner, ',')-1) = users.name Thanks -- Jeff ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Access SQL Question - Round 2
I got it... SELECT issues.id, owners.name, owners.email FROM issues, owners WHERE owners.name = left(issues.owner,len(owners.name)) This works, I know for a fact because I tried it. Matthew Small IT Supervisor Showstopper National Dance Competitions 3660 Old Kings Hwy Murrells Inlet, SC 29576 843-357-1847 http://www.showstopperonline.com -Original Message- From: Jeff Chastain [mailto:[EMAIL PROTECTED]] Sent: Friday, January 17, 2003 11:19 AM To: CF-Talk Subject: RE: Access SQL Question - Round 2 No, that is along the lines of what I have been trying without any luck. Okay, I have the following tables and data: table: issues (id, owner) 1 Doe 2 Klein, Doe 3 James 4 Doe, James table: owners (name, email) Doe [EMAIL PROTECTED] James[EMAIL PROTECTED] Klein[EMAIL PROTECTED] The results I need to get back are ... (issue.id, owners.name, owners.email) 1 Doe [EMAIL PROTECTED] 2 Klein [EMAIL PROTECTED] 3 James [EMAIL PROTECTED] 4 Doe [EMAIL PROTECTED] Where the name and email come from the first (or only) name in the issues table. I would not think that this would be that hard, but for some reason it appears to be. Thanks -- Jeff -- Original Message -- From: webguy [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] Date: Fri, 17 Jan 2003 15:56:19 - No access guru, (anymore:-/) but try SELECT Firstinlist as (or = ???)left(issues.owner, inStr(issues.owner, ',')-1) FROM issues, users WHERE Firstinlist = users.name Something like that... WG -Original Message- From: Jeff Chastain [mailto:[EMAIL PROTECTED]] Sent: 17 January 2003 15:44 To: CF-Talk Subject: WOT: Access SQL Question - Round 2 Okay, I am still having problems with this ... so here is an attempt to simplify the problem. I have a hand-me-down Access database which I am trying to get some data out of. I have a table called issues with a field called owner. The owner field may contain a single name, or it may contain a comma-separated list of names. I have a table called users with a field called name. I am needing to join the two tables up based on the first or only name in the issues.owner field matched to the users.name field. Whenever I try using inStr in the WHERE clause, access throws an invalid procedure error. Anybody got any suggestions? This does not seem to work with Access and even if it did, it would only work for records with a list of users in the issues table, not just a single entry SELECT * FROM issues, users WHERE left(issues.owner, inStr(issues.owner, ',')-1) = users.name Thanks -- Jeff ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
