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=subscribe&forumid=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
Access SQL question
Hi:
thanks, but it did not work.
I tried to make changes to it for Access 2000
(tried ltrim for reverse, could not find function for charindex (tried
locate, instr))
and could not get it to work.
Any other trys?
thanks
Sandy
Subject: Access SQL question
From: "Mahmut Basaran" <[EMAIL PROTECTED]>
Date: Sun, 16 Mar 2003 20:29:38 -0800
Thread: http://www.houseoffusion.com/cf_lists/index.cfm?method=messages&;
threadid=22424&forumid=4#113155
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=subscribe&forumid=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
Access/sql question
I need to make a list from one table information and make a new table containing the list. I am using an access2000 frontend with a sql200 backend. For example Table one SalesID 1 2 3 4 5 6 7 SalesmanID 3 3 3 3 4 4 4 Total Sales 150 160 120 200 9 500 6 I need the new table to be: NewID 1 2 SalesmanID 3 4 Total Sales 150, 160, 120, 200 9,500, 6 So basically I want to flatten out the data. Any ideas? Thanks in advance, j __ Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation · $99/Month · Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusiona 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
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: 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]
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]
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
// 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;
}
---
[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=subscribe&forumid=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
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=subscribe&forumid=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
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: 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 Identity you 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 FROM myTable 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 FROM myTable 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) SELECT id FROM myTable WHEREparent in (#parents#) DELETE FROM myTable WHEREparent in (#parents#) _ 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 FROM myTable 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
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
ALTER PROCEDURE dbo.upu_Instance_Delete
@InstanceID int
AS
-- Variables
DECLARE @lCurrentID int
DECLARE @lError int
DECLARE @lInstanceID int
DECLARE @lPreviousID int
--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)
FROM Instance 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 AL Amersfoort
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
keep for 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
ALTER PROCEDURE dbo.upu_Instance_Delete
@InstanceID int
AS
-- Variables
DECLARE @lCurrentID int
DECLARE @lError int
DECLARE @lInstanceID int
DECLARE @lPreviousID int
--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)
FROM Instance 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 AL Amersfoort
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 :(
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=subscribe&forumid=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
>
>
> // 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;
> }
>
>
>
> ---
> [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=subscribe&forumid=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
>
>
> // 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;
> }
>
>
>
> ---
> [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=subscribe&forumid=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
>
>
> // 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;
> }
>
>
>
> ---
> [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=subscribe&forumid=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
>
>
> // 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
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
> >
> >
> > // 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 wil
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:
#preservesinglequotes(myQueryString)#
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
// 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 :(
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:
#preservesinglequotes(myQueryString)#
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
// 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){
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:
#preservesinglequotes(myQueryString)#
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
// 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;
}
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: 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]
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=subscribe&forumid=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=subscribe&forumid=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=subscribe&forumid=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=subscribe&forumid=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=subscribe&forumid=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=subscribe&forumid=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: WOT: Access SQL Question - Round 2
Getting Access to do those sorts of things is always hideously difficult. If you really need to stick with a single query, you can use a view -- iirc Access calls them Queries. The view would include all the data from the issues table, with the owner column modified as such SELECT *, left(owner + ',', inStr(owner,',')-1) as ownername FROM issues then in your cf query you would be able to use a normal join on that added column to the users.name column. SELECT * FROM v_issues, users WHERE v_issues.ownername = users.name s. isaac dealey954-776-0046 new epoch http://www.turnkey.to lead architect, tapestry cms http://products.turnkey.to tapestry api is opensource http://www.turnkey.to/tapi certified advanced coldfusion 5 developer http://www.macromedia.com/v1/handlers/index.cfm?ID=21816 > 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=subscribe&forumid=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/uns > ubscribe.cfm?user=633.558.4 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=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: WOT: Access SQL Question - Round 2
I would have to disagree with you. Access is not hideously difficult to work with. I actually think it's easier because the functions are right from visual basic - meaning intuitive for the most part. I think the reason that this query has been hard to work with because we're not thinking this problem all the way through - the instr function is returning a value in the negative range when it reaches a non-comma-containing field, and then the left function can't run with a negative length to get. I think we would have had the same problem with SQL Server or MySQL - user error. IMHO 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: S. Isaac Dealey [mailto:[EMAIL PROTECTED]] Sent: Friday, January 17, 2003 12:07 PM To: CF-Talk Subject: Re: WOT: Access SQL Question - Round 2 Getting Access to do those sorts of things is always hideously difficult. If you really need to stick with a single query, you can use a view -- iirc Access calls them Queries. The view would include all the data from the issues table, with the owner column modified as such SELECT *, left(owner + ',', inStr(owner,',')-1) as ownername FROM issues then in your cf query you would be able to use a normal join on that added column to the users.name column. SELECT * FROM v_issues, users WHERE v_issues.ownername = users.name s. isaac dealey954-776-0046 new epoch http://www.turnkey.to lead architect, tapestry cms http://products.turnkey.to tapestry api is opensource http://www.turnkey.to/tapi certified advanced coldfusion 5 developer http://www.macromedia.com/v1/handlers/index.cfm?ID=21816 > 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=subscribe&forumid=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/uns > ubscribe.cfm?user=633.558.4 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=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: WOT: Access SQL Question - Round 2
Matthew and Isaac have provided you with two excellent workarounds. I just have one thing to add. The "where pk=fk" syntax doesn't produce an inner join in Access. Instead use "from issues i inner join users u on i.owner = u.name". Too bad you have to go thru all of this...a normalized database schema would've prevented this problem...which you probably haven't seen the last of... :) ~Dina ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=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: WOT: Access SQL Question - Round 2
I'm know any anything about inner/outer joins - how is using an inner join different from using pk=fk, and why would you want to use it in this case? Thanks, 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: Dina Hess [mailto:[EMAIL PROTECTED]] Sent: Friday, January 17, 2003 12:31 PM To: CF-Talk Subject: Re: WOT: Access SQL Question - Round 2 Matthew and Isaac have provided you with two excellent workarounds. I just have one thing to add. The "where pk=fk" syntax doesn't produce an inner join in Access. Instead use "from issues i inner join users u on i.owner = u.name". Too bad you have to go thru all of this...a normalized database schema would've prevented this problem...which you probably haven't seen the last of... :) ~Dina ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=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: WOT: Access SQL Question - Round 2
> Too bad you have to go thru all of this...a normalized database > schema would've prevented this problem...which you probably > haven't seen the last of... :) > > ~Dina Ouch! Database - Refactoring 101 http://www.agiledata.org/essays/databaseRefactoring.html Always fix broken windows... WG ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=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: WOT: Access SQL Question - Round 2
In Access, if you use "FROM issues, owners WHERE issues.owner = users.name" you're doing a cartesian join, which produces every possible combination of the two tables. An inner join, on the other hand, will return only the rows that match your join condition. In SQL Server, however, the "WHERE issues.owner = users.name" syntax *does* produce an inner join. Does that help? BTW, kudos on that workaround...I was still scratching over it when your post came through. :) I'm know any anything about inner/outer joins - how is using an inner join different from using pk=fk, and why would you want to use it in this case? ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: WOT: Access SQL Question - Round 2
I'm not too sure what you're getting at...maybe I wasn't clear. My point was that a list of values should not be stored in a DB field. This is a basic criterion of a normalized database. It tends to become increasingly difficult to work with such databases. > Too bad you have to go thru all of this...a normalized database > schema would've prevented this problem...which you probably > haven't seen the last of... :) > > ~Dina Ouch! Database - Refactoring 101 http://www.agiledata.org/essays/databaseRefactoring.html Always fix broken windows... ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=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: WOT: Access SQL Question - Round 2
Isacc's solution gives me what I need - thanks for all the input. I won't get into the debate about Access vs. SQL. However, Access does have an advantage over SQL in small, light use, cases ($$$), but a well designed DB is much easier to deal with, no matter what system it is in. Thanks -- Jeff -- Original Message -- From: S. Isaac Dealey <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] Date: Fri, 17 Jan 2003 12:06:30 -0500 >Getting Access to do those sorts of things is always hideously difficult. If >you really need to stick with a single query, you can use a view -- iirc >Access calls them Queries. The view would include all the data from the >issues table, with the owner column modified as such > >SELECT *, left(owner + ',', inStr(owner,',')-1) as ownername FROM issues > >then in your cf query you would be able to use a normal join on that added >column to the users.name column. > >SELECT * FROM v_issues, users >WHERE v_issues.ownername = users.name > >s. isaac dealey954-776-0046 > >new epoch http://www.turnkey.to > >lead architect, tapestry cms http://products.turnkey.to > >tapestry api is opensource http://www.turnkey.to/tapi > >certified advanced coldfusion 5 developer >http://www.macromedia.com/v1/handlers/index.cfm?ID=21816 > >> 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=subscribe&forumid=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/uns >> ubscribe.cfm?user=633.558.4 > > > ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=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: WOT: Access SQL Question - Round 2
> In Access, if you use "FROM issues, owners WHERE issues.owner > = users.name" you're doing a cartesian join, which produces > every possible combination of the two tables. An inner join, > on the other hand, will return only the rows that match your > join condition. Admittedly, I don't use Access that often, but that hasn't been my experience. My experience has been that Access will treat non-ANSI joins as described above as inner joins, at least with regard to passthrough SQL statements from CF. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=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: WOT: Access SQL Question - Round 2
I'm with you on this, Dave. I think the existence of the join condition eliminates the Cartesian cross-product result. 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: Dave Watts [mailto:[EMAIL PROTECTED]] Sent: Friday, January 17, 2003 1:16 PM To: CF-Talk Subject: RE: WOT: Access SQL Question - Round 2 > In Access, if you use "FROM issues, owners WHERE issues.owner > = users.name" you're doing a cartesian join, which produces > every possible combination of the two tables. An inner join, > on the other hand, will return only the rows that match your > join condition. Admittedly, I don't use Access that often, but that hasn't been my experience. My experience has been that Access will treat non-ANSI joins as described above as inner joins, at least with regard to passthrough SQL statements from CF. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=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: WOT: Access SQL Question - Round 2
No, I agree! Just saying that it's always better to fix the underlying problem (i.e. the db design) than coding around it. The metaphor "Fix broken windows" refer to the idea, that if you leave even small bits of bad code in your code base, it spreads to other code. Better to bite the bullet and fix the DB design *apparently this comes from a study, that showed, if a broken window in housing block is left unrepaired, that occupants start to disrespect the property and that broken window leads to further problems* WG > -Original Message- > From: Dina Hess [mailto:[EMAIL PROTECTED]] > Sent: 17 January 2003 18:06 > To: CF-Talk > Subject: Re: WOT: Access SQL Question - Round 2 > > > I'm not too sure what you're getting at...maybe I wasn't clear. > > My point was that a list of values should not be stored in a DB > field. This is a basic criterion of a normalized database. It > tends to become increasingly difficult to work with such databases. > > > > Too bad you have to go thru all of this...a normalized database > > schema would've prevented this problem...which you probably > > haven't seen the last of... :) > > > > ~Dina > > Ouch! Database - Refactoring 101 > http://www.agiledata.org/essays/databaseRefactoring.html > > Always fix broken windows... > > > ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=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: WOT: Access SQL Question - Round 2
>Admittedly, I don't use Access that often, but that hasn't been my >experience. My experience has been that Access will treat non-ANSI joins as >described above as inner joins, at least with regard to passthrough SQL >statements from CF. I just tried Matthew's query in Access and it worked just fine. So...my apologies, Matthew, for misleading you and thank you, Dave, for sharing the wisdom of your experience. :) ~Dina ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=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: WOT: Access SQL Question - Round 2
While the recordsets produced by both of these queries are the same, the second query (with the ANSI join) performs *much* better. My simple test returned a recordset of only two records, yet the time on the first query was 135ms compared to only 15ms on the second query. SELECT u.name FROM issues i, users u WHERE u.name = left(i.owner,len(u.name)) SELECT u.name FROM issues i inner join users u on left(i.owner,len(u.name))= u.name I'm with you on this, Dave. I think the existence of the join condition eliminates the Cartesian cross-product result. > In Access, if you use "FROM issues, owners WHERE issues.owner > = users.name" you're doing a cartesian join, which produces > every possible combination of the two tables. An inner join, > on the other hand, will return only the rows that match your > join condition. ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=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: WOT: Access SQL Question - Round 2
> I'm know any anything about inner/outer joins - how is > using an inner join different from using pk=fk, and why > would you want to use it in this case? An inner join is reasonably similar to using pk=fk in a where clause. I'm not sure what the internal differences are, re: performance, etc. An outer join produces a query wherein data from table 1 is required and data from table 2 is optional, i.e. "find all issues associated with user x" wherein you want to retreive data from the users table and zero or more associated records from the issues table. With an inner join or a typical pk=fk where clause, no data is returned from the users table if there are no issues associated with the user. In this particular case he probably didn't want an outer join, although it's possible an inner join might improve performance over the where clause, but again that's just conjecture. hth s. isaac dealey954-776-0046 new epoch http://www.turnkey.to lead architect, tapestry cms http://products.turnkey.to tapestry api is opensource http://www.turnkey.to/tapi certified advanced coldfusion 5 developer http://www.macromedia.com/v1/handlers/index.cfm?ID=21816 > Thanks, > 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: Dina Hess [mailto:[EMAIL PROTECTED]] > Sent: Friday, January 17, 2003 12:31 PM > To: CF-Talk > Subject: Re: WOT: Access SQL Question - Round 2 > Matthew and Isaac have provided you with two excellent > workarounds. I > just have one thing to add. > The "where pk=fk" syntax doesn't produce an inner join in > Access. > Instead use "from issues i inner join users u on i.owner = > u.name". > Too bad you have to go thru all of this...a normalized > database schema > would've prevented this problem...which you probably > haven't seen the > last of... :) > ~Dina > ~~ > ~~~| > Archives: > http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 > Subscription: http://www.houseoffusion.com/cf_lists/index. > cfm?method=subscribe&forumid=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/uns > ubscribe.cfm?user=633.558.4 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=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: WOT: Access SQL Question - Round 2
> An inner join is reasonably similar to using pk=fk > in a where clause. That statement deserves a little clarification. Both of these queries use inner joins: SELECT t1.*, t2.* FROM t1, t2 WHERE t1.pk = t2.fk SELECT t1.*, t2.* FROM t1 INNER JOIN t2 ON t1.pk = t2.fk The difference is that the second query uses the ANSI syntax for its inner join, while the first doesn't. Specific database engines may conceivably perform differently when analyzing each query, or may only support one syntax or the other, but they're still both inner joins. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ phone: 202-797-5496 fax: 202-797-5444 ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=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
