-
Code Help from the wise one
Thanks for your help
Here is my situation
an MDB with a table with customer name, address, phone
another table with work order details for customers such as name, when, where, etc
First part of code drp dwn list of all customers in table
select a name and click apply filter
new screen with that customers name, address ect with the name being
clickable
HERE IS MY PROBLEM
click name and it should search the second table for all occurances of that name. When I do that I get an error that says:
Error # = -2147217900
Description = [Microsoft][ODBC Microsoft Access 97 Driver] Syntax error (missing operator) in query expression 'Customer=Cazolas Pizza'.
Source = Microsoft OLE DB Provider for ODBC Drivers
SQL State = 37000
NativeError = -3100
or another error that says
Error # = -2147217900
Description = [Microsoft][ODBC Microsoft Access 97 Driver] Syntax error (comma) in query expression 'Customer=Alpert, Robert'.
Source = Microsoft OLE DB Provider for ODBC Drivers
SQL State = 37000
NativeError = -3100
The content of the Customer Name field could be Gates, Bill or a company namelike Microsoft.
HERE IS A SAMPLE OF MY CODE:
<% response.buffer=true %>
<!--ASP-db-->
<HTML>
<HEAD><title>Work Order System</title>
<Meta name="Meta Name" Content="Meta Content">
<Meta name="Author" Content="Michael E. Ferguson Sr.,">
</HEAD>
<BODY BGCOLOR="lightblue" TEXT=Black ALINK="red" VLINK="blue">
<FONT FACE="Arial,Helvetica" COLOR="000000" size=3>
<CENTER><B><I>Welcome to the Work Order System</B></I><P>
<B>Crafted & Maintained by Michael E. Ferguson, Sr.,</B>
</CENTER>
</FONT>
<HR SIZE=5>
<%
Set MyDb = Server.CreateObject("AspDB.Pro"
%>
<%
MyDb.dbDSN="dsnwosystem"
MyDb.DBColor = "4"
MyDb.dbGridtabletag = "border=8 cellspacing=3 cellpadding=3"
MyDb.DbMode = "Grid"
MyDb.dbGridInc = 15
Mydb.dbButtonAnchor=false
Mydb.dbStartUp= "Filter"
S = "Select * from Customers"
MyDb.dbSQL = S
'Mydb.dbSelectBox = "10,Pick a Driver,Click to Select; #Driver# "
MyDb.dbFilterFlds="Customer Name, heading=<P><B><I>Please choose which records you want to see</I></B>"
MyDb.dbFilterDropFlds = "Customer Name,,Customers,Customer name; WO#,,WORKORDER,Work Order number"
Mydb.dbFilterParams ="TableTag=Border=3, DropButtonText=none"
MyDb.DbGridInc="10"
MyDb.dbImageDir="images/"
Mydb.dbNavigation="top"
Mydb.dbNavigationItem="filter"
Mydb.dbNavigationIcon="std"
MyDb.dbUnit = 10113
MyDb.dbMagicCell="1,,<A HREF=""#me#?ID=#1#"">#1#</a>" '** 05
MyDb.ASPdbPRO
RECS = Mydb.dbRecordCount
%>
<HR SIZE=5>
</BODY>
</HTML>
<!--SECOND HALF OF CODE-->
<%
myvar=request("ID" '** 07
if myvar<>"" then '** 08
%>
<CENTER><h2>Database #2 - Work Order Details for #<%=myvar%></h2></CENTER>
<%
Set Y=Server.CreateObject("AspDB.Pro" '** 09
Y.dbDSN="dsnwosystem" '** 10
Y.dbmode="Grid"
Y.dbUnit=2 '** 11
Y.dbcolor="4,auto"
Y.dbGridTableTag="BORDER=1"
Y.dbNavigation="none"
Y.dbSQL="Select * FROM [WORKORDER] where Customer=" & myvar & " " '** 12
Y.aspdbPRO '** 13
end if
%>
-
Code Help from the wise one (reply)
Hi Ferguson,
I think the problem may be in you use of "#" in a field name (WO#). That
may be screwing things up.
What you should do, is to turn debug mode on (dbDebug=true) and then copy
and paste the SQL statement that's getting executed into an Access query
(create a new query, choose View SQL and the paste it). Once you get the
syntax to work there you're probably all set. If not, then check all your
field names and make sure they don't contain any reserved words. Access is
VERY sloppy about what it allows you to put in field names (#, ?, *, etc.)
as was well as words like AND or FROM.
Another approach to your problem might be to use the neat dbSelectBox
feature! You could show all your customers (with address & phone if you wish)
in a scrollable select box and then when the user clicks on one and clicks
"SELECT" (or whatever you name the button), then it will "drill down" and
show all the details for that record.
Thanks and good luck!
John
On 12/22/98 6:57:49 AM, Ferguson wrote:
> Thanks for your help
Here is my situation
an MDB with a table with
> customer name, address, phone
another table with work order details for
> customers such as name, when, where, etc
First part of code drp dwn list
> of all customers in table
select a name and click apply filter
new
> screen with that customers name, address ect with the name
> being
clickable
HERE IS MY PROBLEM
click name and it should search the
> second table for all occurances of that name. When I do that I get an error
> that says:
Error # = -2147217900
Description = [Microsoft][ODBC
> Microsoft Access 97 Driver] Syntax error (missing operator) in query
> expression 'Customer=Cazolas Pizza'.
Source = Microsoft OLE DB
> Provider for ODBC Drivers
SQL State = 37000
NativeError = -3100
or
> another error that says
Error # = -2147217900
Description =
> [Microsoft][ODBC Microsoft Access 97 Driver] Syntax error (comma) in query
> expression 'Customer=Alpert, Robert'.
Source = Microsoft OLE DB
> Provider for ODBC Drivers
SQL State = 37000
NativeError = -3100
The
> content of the Customer Name field could be Gates, Bill or a company
> namelike Microsoft.
HERE IS A SAMPLE OF MY CODE:
<%
> response.buffer=true %>
<!--ASP-db-->
<HTML>
<HEAD><title>Work Order
> System</title>
<Meta name="Meta Name" Content="Meta
> Content">
<Meta name="Author" Content="Michael E. Ferguson
> Sr.,">
</HEAD>
<BODY BGCOLOR="lightblue" TEXT=Black
> ALINK="red" VLINK="blue">
<FONT
> FACE="Arial,Helvetica" COLOR="000000"
> size=3>
<CENTER><B><I>Welcome to the Work Order
> System</B></I><P>
<B>Crafted & Maintained by Michael E. Ferguson,
> Sr.,</B>
</CENTER>
</FONT>
<HR SIZE=5>
<%
Set MyDb =
> Server.CreateObject("AspDB.Pro"
%>
<%
MyDb.dbDSN="dsnwosyste
> m"
MyDb.DBColor = "4"
MyDb.dbGridtabletag = "border=8
> cellspacing=3 cellpadding=3"
MyDb.DbMode =
> "Grid"
MyDb.dbGridInc =
> 15
Mydb.dbButtonAnchor=false
Mydb.dbStartUp= "Filter"
S =
> "Select * from Customers"
MyDb.dbSQL = S
'Mydb.dbSelectBox
> = "10,Pick a Driver,Click to Select; #Driver#
> "
MyDb.dbFilterFlds="Customer Name, heading=<P><B><I>Please
> choose which records you want to see</I></B>"
MyDb.dbFilterDropFlds =
> "Customer Name,,Customers,Customer name; WO#,,WORKORDER,Work Order
> number"
Mydb.dbFilterParams ="TableTag=Border=3,
> DropButtonText=none"
MyDb.DbGridInc="10"
MyDb.dbImageDir=&
> #34;images/"
Mydb.dbNavigation="top"
Mydb.dbNavigationItem=
> 4;filter"
Mydb.dbNavigationIcon="std"
MyDb.dbUnit =
> 10113
MyDb.dbMagicCell="1,,<A
> HREF=""#me#?ID=#1#"">#1#</a>" '**
> 05
MyDb.ASPdbPRO
RECS = Mydb.dbRecordCount
%>
<HR
> SIZE=5>
</BODY>
</HTML>
<!--SECOND HALF OF
> CODE-->
<%
myvar=request("ID" '** 07
if myvar<>""
> then '** 08
%>
<CENTER><h2>Database #2 - Work Order Details for
> #<%=myvar%></h2></CENTER>
<%
Set
> Y=Server.CreateObject("AspDB.Pro" '**
> 09
Y.dbDSN="dsnwosystem" '**
> 10
Y.dbmode="Grid"
Y.dbUnit=2 '**
> 11
Y.dbcolor="4,auto"
Y.dbGridTableTag="BORDER=1"
Y.dbNav
> igation="none"
Y.dbSQL="Select * FROM [WORKORDER] where
> Customer=" & myvar & " " '** 12
Y.aspdbPRO '** 13
end if
%>
-
Code Help from the wise one (reply)
P.S. Example B3 (http://www.aspdb.com/examples/aspdbexb03.asp) shows
this dbSelectBox in action.
John
-
Code Help from the wise one (reply)
"#" is the date wrapper of ACCESS - (e.g. #12/23/98#) . If you have a variable called #Var# you will be be able to get too far before you hit some kind of conflict.
Frank
On 12/22/98 1:29:38 PM, John wrote:
> Hi Ferguson,
I think the problem may be in you use of "#" in a
> field name (WO#). That
may be screwing things up.
What you should do,
> is to turn debug mode on (dbDebug=true) and then copy
and paste the SQL
> statement that's getting executed into an Access query
(create a new
> query, choose View SQL and the paste it). Once you get the
syntax to work
> there you're probably all set. If not, then check all your
field
> names and make sure they don't contain any reserved words. Access
> is
VERY sloppy about what it allows you to put in field names (#, ?, *,
> etc.)
as was well as words like AND or FROM.
Another approach to your
> problem might be to use the neat dbSelectBox
feature! You could show all
> your customers (with address & phone if you wish)
in a scrollable select
> box and then when the user clicks on one and clicks
"SELECT" (or
> whatever you name the button), then it will "drill down" and
show
> all the details for that record.
Thanks and good luck!
John
On
> 12/22/98 6:57:49 AM, Ferguson wrote:
> Thanks for your help
Here is my
> situation
an MDB with a table with
> customer name, address,
> phone
another table with work order details for
> customers such as
> name, when, where, etc
First part of code drp dwn list
> of all
> customers in table
select a name and click apply filter
new
>
> screen with that customers name, address ect with the name
>
> being
clickable
HERE IS MY PROBLEM
click name and it should search the
>
> second table for all occurances of that name. When I do that I get an
> error
> that says:
Error # = -2147217900
Description =
> [Microsoft][ODBC
> Microsoft Access 97 Driver] Syntax error (missing
> operator) in query
> expression 'Customer=Cazolas Pizza'.
Source
> = Microsoft OLE DB
> Provider for ODBC Drivers
SQL State =
> 37000
NativeError = -3100
or
> another error that says
Error # =
> -2147217900
Description =
> [Microsoft][ODBC Microsoft Access 97 Driver]
> Syntax error (comma) in query
> expression 'Customer=Alpert,
> Robert'.
Source = Microsoft OLE DB
> Provider for ODBC Drivers
SQL
> State = 37000
NativeError = -3100
The
> content of the Customer Name
> field could be Gates, Bill or a company
> namelike Microsoft.
HERE IS
> A SAMPLE OF MY CODE:
<%
> response.buffer=true
> %>
<!--ASP-db-->
<HTML>
<HEAD><title>Work Order
>
> System</title>
<Meta name="Meta Name" Content="Meta
>
> Content">
<Meta name="Author" Content="Michael E. Ferguson
>
> Sr.,">
</HEAD>
<BODY BGCOLOR="lightblue" TEXT=Black
>
> ALINK="red" VLINK="blue">
<FONT
>
> FACE="Arial,Helvetica" COLOR="000000"
>
> size=3>
<CENTER><B><I>Welcome to the Work Order
>
> System</B></I><P>
<B>Crafted & Maintained by Michael E. Ferguson,
>
> Sr.,</B>
</CENTER>
</FONT>
<HR SIZE=5>
<%
Set MyDb =
>
> Server.CreateObject("AspDB.Pro"
%>
<%
MyDb.dbDSN="dsnwosyste
>
> m"
MyDb.DBColor = "4"
MyDb.dbGridtabletag = "border=8
>
> cellspacing=3 cellpadding=3"
MyDb.DbMode =
>
> "Grid"
MyDb.dbGridInc =
>
> 15
Mydb.dbButtonAnchor=false
Mydb.dbStartUp= "Filter"
S =
>
> "Select * from Customers"
MyDb.dbSQL =
> S
'Mydb.dbSelectBox
> = "10,Pick a Driver,Click to Select;
> #Driver#
> "
MyDb.dbFilterFlds="Customer Name,
> heading=<P><B><I>Please
> choose which records you want to
> see</I></B>"
MyDb.dbFilterDropFlds =
> "Customer
> Name,,Customers,Customer name; WO#,,WORKORDER,Work Order
>
> number"
Mydb.dbFilterParams ="TableTag=Border=3,
>
> DropButtonText=none"
MyDb.DbGridInc="10"
MyDb.dbImageDir=&
>
>
> #34;images/"
Mydb.dbNavigation="top"
Mydb.dbNavigationItem=
> > 4;filter"
Mydb.dbNavigationIcon="std"
MyDb.dbUnit =
>
> 10113
MyDb.dbMagicCell="1,,<A
>
> HREF=""#me#?ID=#1#"">#1#</a>" '**
>
> 05
MyDb.ASPdbPRO
RECS = Mydb.dbRecordCount
%>
<HR
>
> SIZE=5>
</BODY>
</HTML>
<!--SECOND HALF OF
>
> CODE-->
<%
myvar=request("ID" '** 07
if myvar<>""
>
> then '** 08
%>
<CENTER><h2>Database #2 - Work Order Details for
>
> #<%=myvar%></h2></CENTER>
<%
Set
>
> Y=Server.CreateObject("AspDB.Pro" '**
>
> 09
Y.dbDSN="dsnwosystem" '**
>
> 10
Y.dbmode="Grid"
Y.dbUnit=2 '**
>
> 11
Y.dbcolor="4,auto"
Y.dbGridTableTag="BORDER=1"
Y.dbNav
>
> igation="none"
Y.dbSQL="Select * FROM [WORKORDER] where
>
> Customer=" & myvar & " " '** 12
Y.aspdbPRO '**
> 13
end if
%>
-
Code Help from the wise one (reply)
Thank you guys. My problem is fixed. Preciate it
On 12/22/98 6:57:49 AM, Ferguson wrote:
> Thanks for your help
Here is my situation
an MDB with a table with
> customer name, address, phone
another table with work order details for
> customers such as name, when, where, etc
First part of code drp dwn list
> of all customers in table
select a name and click apply filter
new
> screen with that customers name, address ect with the name
> being
clickable
HERE IS MY PROBLEM
click name and it should search the
> second table for all occurances of that name. When I do that I get an error
> that says:
Error # = -2147217900
Description = [Microsoft][ODBC
> Microsoft Access 97 Driver] Syntax error (missing operator) in query
> expression 'Customer=Cazolas Pizza'.
Source = Microsoft OLE DB
> Provider for ODBC Drivers
SQL State = 37000
NativeError = -3100
or
> another error that says
Error # = -2147217900
Description =
> [Microsoft][ODBC Microsoft Access 97 Driver] Syntax error (comma) in query
> expression 'Customer=Alpert, Robert'.
Source = Microsoft OLE DB
> Provider for ODBC Drivers
SQL State = 37000
NativeError = -3100
The
> content of the Customer Name field could be Gates, Bill or a company
> namelike Microsoft.
HERE IS A SAMPLE OF MY CODE:
<%
> response.buffer=true %>
<!--ASP-db-->
<HTML>
<HEAD><title>Work Order
> System</title>
<Meta name="Meta Name" Content="Meta
> Content">
<Meta name="Author" Content="Michael E. Ferguson
> Sr.,">
</HEAD>
<BODY BGCOLOR="lightblue" TEXT=Black
> ALINK="red" VLINK="blue">
<FONT
> FACE="Arial,Helvetica" COLOR="000000"
> size=3>
<CENTER><B><I>Welcome to the Work Order
> System</B></I><P>
<B>Crafted & Maintained by Michael E. Ferguson,
> Sr.,</B>
</CENTER>
</FONT>
<HR SIZE=5>
<%
Set MyDb =
> Server.CreateObject("AspDB.Pro"
%>
<%
MyDb.dbDSN="dsnwosyste
> m"
MyDb.DBColor = "4"
MyDb.dbGridtabletag = "border=8
> cellspacing=3 cellpadding=3"
MyDb.DbMode =
> "Grid"
MyDb.dbGridInc =
> 15
Mydb.dbButtonAnchor=false
Mydb.dbStartUp= "Filter"
S =
> "Select * from Customers"
MyDb.dbSQL = S
'Mydb.dbSelectBox
> = "10,Pick a Driver,Click to Select; #Driver#
> "
MyDb.dbFilterFlds="Customer Name, heading=<P><B><I>Please
> choose which records you want to see</I></B>"
MyDb.dbFilterDropFlds =
> "Customer Name,,Customers,Customer name; WO#,,WORKORDER,Work Order
> number"
Mydb.dbFilterParams ="TableTag=Border=3,
> DropButtonText=none"
MyDb.DbGridInc="10"
MyDb.dbImageDir=&
> #34;images/"
Mydb.dbNavigation="top"
Mydb.dbNavigationItem=
> 4;filter"
Mydb.dbNavigationIcon="std"
MyDb.dbUnit =
> 10113
MyDb.dbMagicCell="1,,<A
> HREF=""#me#?ID=#1#"">#1#</a>" '**
> 05
MyDb.ASPdbPRO
RECS = Mydb.dbRecordCount
%>
<HR
> SIZE=5>
</BODY>
</HTML>
<!--SECOND HALF OF
> CODE-->
<%
myvar=request("ID" '** 07
if myvar<>""
> then '** 08
%>
<CENTER><h2>Database #2 - Work Order Details for
> #<%=myvar%></h2></CENTER>
<%
Set
> Y=Server.CreateObject("AspDB.Pro" '**
> 09
Y.dbDSN="dsnwosystem" '**
> 10
Y.dbmode="Grid"
Y.dbUnit=2 '**
> 11
Y.dbcolor="4,auto"
Y.dbGridTableTag="BORDER=1"
Y.dbNav
> igation="none"
Y.dbSQL="Select * FROM [WORKORDER] where
> Customer=" & myvar & " " '** 12
Y.aspdbPRO '** 13
end if
%>
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|