Code Help from the wise one

Ferguson · 12-22-1998, 06:57 AM

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 %>

<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">



<CENTER>Welcome to the Work Order System
Crafted & Maintained by Michael E. Ferguson, Sr.,
</CENTER>

<HR SIZE=5>
<%
Set MyDb = Server.CreateObject("AspDB.Pro&#34

%>
<%
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=Please choose which records you want to see"
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>



<%
myvar=request("ID&#34

'** 07
if myvar<>"" then '** 08
%>
<CENTER><h2>Database #2 - Work Order Details for #<%=myvar%></h2></CENTER>
<%
Set Y=Server.CreateObject("AspDB.Pro&#34

'** 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
%>

John · 12-22-1998, 01:29 PM

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 %>

<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">

 FACE="Arial,Helvetica" COLOR="000000"
> size=3>

<CENTER>Welcome to the Work Order
> System
Crafted & Maintained by Michael E. Ferguson,
> Sr.,
</CENTER>

<HR SIZE=5>
<%
Set MyDb =
> Server.CreateObject("AspDB.Pro&#34

%>
<%
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=Please
> choose which records you want to see"
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>



<%
myvar=request("ID&#34

'** 07
if myvar<>""
> then '** 08
%>
<CENTER><h2>Database #2 - Work Order Details for
> #<%=myvar%></h2></CENTER>
<%
Set
> Y=Server.CreateObject("AspDB.Pro&#34

'**
> 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
%>

John · 12-22-1998, 01:38 PM

P.S. Example B3 (http://www.aspdb.com/examples/aspdbexb03.asp) shows
this dbSelectBox in action.

John

Frank · 12-23-1998, 08:34 PM

"#" 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
> %>

<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">


> FACE="Arial,Helvetica" COLOR="000000"
>
> size=3>

<CENTER>Welcome to the Work Order
>
> System
Crafted & Maintained by Michael E. Ferguson,
>
> Sr.,
</CENTER>

<HR SIZE=5>
<%
Set MyDb =
>
> Server.CreateObject("AspDB.Pro&#34

%>
<%
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=Please
> choose which records you want to
> see"
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>



<%
myvar=request("ID&#34

'** 07
if myvar<>""
>
> then '** 08
%>
<CENTER><h2>Database #2 - Work Order Details for
>
> #<%=myvar%></h2></CENTER>
<%
Set
>
> Y=Server.CreateObject("AspDB.Pro&#34

'**
>
> 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
%>

Ferguson · 12-31-1998, 11:39 AM

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 %>

<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">

 FACE="Arial,Helvetica" COLOR="000000"
> size=3>

<CENTER>Welcome to the Work Order
> System
Crafted & Maintained by Michael E. Ferguson,
> Sr.,
</CENTER>

<HR SIZE=5>
<%
Set MyDb =
> Server.CreateObject("AspDB.Pro&#34

%>
<%
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=Please
> choose which records you want to see"
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=&#3
> 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>



<%
myvar=request("ID&#34

'** 07
if myvar<>""
> then '** 08
%>
<CENTER><h2>Database #2 - Work Order Details for
> #<%=myvar%></h2></CENTER>
<%
Set
> Y=Server.CreateObject("AspDB.Pro&#34

'**
> 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
%>

Thread: Code Help from the wise one

Thread Tools

Display

Code Help from the wise one

Code Help from the wise one (reply)

Code Help from the wise one (reply)

Code Help from the wise one (reply)

Code Help from the wise one (reply)

Posting Permissions