p2p.wrox.com: Archives - asp

asp_databases thread: Syntax error, Incomplete Query clause

Message #1 by "Shawn Campbell" <scam77@h...> on Fri, 9 Jun 2000 20:18:36

Below is the code I'm trying to run(I know that the variables are not so 

good but I was in a hurry) It takes input from a form and enters it into a 

Access 2000 database when I try and run the page it gives me Syntax Error, 

Incomplete Query Clause. Any Help would great. Thanks 

Shawn,scam77@h...





<%  

var oConn; 

var oRs;

var filePath; 

var fname;

var lname;

var dayof;

var machineno;

var workord;

var utime;

var unitg;

var units;

var breaks;

var handl;

var train;

var car;

var machmat;

var guag;

var rework;

var tot;

var monthof;

var rsWorkers;

var totalhours;

var pcshour;

var totunits;

var direct;

var year1;

var temp;

var temp1;

var temp2;

var temp3;

var temp4;

var temp5;

var temp6;

var temp7;

var temp8;

var temp9;

var temp10;

var temp11;

var temp12;

var temp13;

var temp14;

var stgWorkers;

var field;

var stgYear;

										

filePath = Server.MapPath("labor.mdb");

oConn = Server.CreateObject("ADODB.Connection");

oConn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath);

				

year1 = Request.Form("yea");

fname = Request.Form("firstname");

lname = Request.Form("lastname");

dayof = Request.Form("day");

monthof = Request.Form("month");

machineno = Request.Form("machine");

workord = Request.Form("workorder");

utime = Request.Form("time");

unitg = Request.Form("good");

units = Request.Form("scrap");

breaks = Request.Form("breaks");

handl = Request.Form("handling");

train = Request.Form("training");

car = Request.Form("care");

machmat = Request.Form("machinemat");

guag = Request.Form("guage");

rework1 = Request.Form("sort");

tot = Request.Form("total");

totunits = unitg + units;

temp = utime;

temp1 = breaks;

temp2 = temp + temp1;

temp3 = handl;

temp4 = train;

temp5 = temp3 + temp4;

temp6 = temp2 + temp5;

temp7 = car;

temp8 = machmat;

temp9 = temp7 + temp8;

temp10 = temp9 + temp6;

temp11 = guag;

temp12 = rework1;

temp13 = temp11 + temp12;

temp14 = temp10 + temp13;

totalhours = (temp14)/60;

pcshour = totunits/totalhours;

direct = totalhours/pcshour;

rsWorkers = Server.CreateObject("ADODB.Recordset");

stgWorkers = fname + lname + monthof;

stgYear = year1;

field = stgWorkers + stgYear;

stgSql = oConn.Execute("SELECT * FROM '" + field + "' ");

rsWorkers.Open( field, oConn, stgSql, adLockOptimistic );

		

rsWorkers.Fields("dayofmonth") = dayof;

rsWorkers.Update;

rsWorkers.Fields("machine no") = machineno;

rsWorkers.Update;

rsWorkers.Fields("workorder") = workord;

rsWorkers.Update;

rsWorkers.Fields("uptime") = utime;

rsWorkers.Update;

rsWorkers.Fields("goodunits") = unitg;

rsWorkers.Update;

rsWorkers.Fields("scrapunits") = units;

rsWorkers.Update;

rsWorkers.Fields("breaks") = breaks;

rsWorkers.Update;

rsWorkers.Fields("handling") = handl;

rsWorkers.Update;

rsWorkers.Fields("training") = train;

rsWorkers.Update;

rsWorkers.Fields("tool") = care;

rsWorkers.Update;

rsWorkers.Fields("machinemat") = machmat;

rsWorkers.Update;

rsWorkers.Fields("guage") = guag;

rsWorkers.Update;

rsWorkers.Fields("total") = tot;

rsWorkers.Update;

rsWorkers.Fields("totalhours") = totalhours;

rsWorkers.Update;

rsWorkers.Fields("pcshour") = pcshour;

rsWorkers.Update;

rsWorkers.Fields("direct") = direct;

rsWorkers.Update;

rsWorkers.Fields("rework") = rework1;

rsWorkers.Update;

Response.Write("<br>All Fields Updated<br>");

Response.Write("<a href='directlaborreport.html'>Do another report</a>");

				

%>

Message #2 by smartin@c... on Sat, 10 Jun 2000 13:59:57 -0400

The problem is probably here:



> stgSql = oConn.Execute("SELECT * FROM '" + field + "' ");



Do you actually have a seperate table for each worker?  My guess is that

you're trying to pull a record from a table where the record matches some

criteria in a given field.  The syntax should be something like:



SELECT fieldnames FROM tablename WHERE criteria for selection (substitute

your own data for what I have in lowercase)



Also, you don't need to call .update after setting each recordset field.

That's a lot of work for the DB.  You can set all your field and call

.update when you're done.



-Stephen

Message #3 by "Ken Schaefer" <ken.s@a...> on Sun, 11 Jun 2000 23:04:05 +1000

Your error is here (I think):



> rsWorkers.Open( field, oConn, stgSql, adLockOptimistic );



A recordset.Open method takes the following parameters:



objRS.Open Source, Connection, CursorType, LockType, Options



Source = SQL String, Table name, Stored Procedure name etc

Connection = Connection String or object

Cursor Type = adOpenForwardOnly, adOpenStatic etc



So, field isn't a valid Source (unless it's the name of a table) and stgSql

is not a valid Cursor Type



So you should be doing:



objRS.Open strSQL, oConn, adOpenForwardOnly, adLockOptimistic, adCmdText



HTH



Cheers

Ken



----- Original Message -----

From: "Shawn Campbell"

To: "ASP Databases" <asp_databases@p...>

Sent: Friday, June 09, 2000 8:18 PM

Subject: [asp_databases] Syntax error, Incomplete Query clause





> Below is the code I'm trying to run(I know that the variables are not so

> good but I was in a hurry) It takes input from a form and enters it into a

> Access 2000 database when I try and run the page it gives me Syntax Error,

> Incomplete Query Clause. Any Help would great. Thanks

> Shawn,scam77@h...



<snipped for your viewing comfort>

Message #4 by "Robert Larsson" <robert.l@m...> on Mon, 12 Jun 2000 10:28:35 -0700

For a start, if you're using VB Script and want's to put 2 variables

togheter, use the & char instead of + char, as + makes a mathematical

operation and & puts them togheter. Check out your connection string.





Mvh,

Robert Larsson!

Web-production Coordinator

---------------------------------------------------------------------

Mindflow AB | www.mindflow.se | 08-545 635 10





----- Original Message -----

From: "Shawn Campbell" 

To: "ASP Databases" <asp_databases@p...>

Sent: Friday, June 09, 2000 8:18 PM

Subject: [asp_databases] Syntax error, Incomplete Query clause

Message #5 by "Shawn Campbell" <scam77@h...> on Mon, 12 Jun 2000 17:32:44

I tried using that and the same error. I used field because it is a table 

name so I tried the following code. I appericate the help so far.

I recieved a object or provider does not support that action error.

var oConn; 

                var oRs;

                var filePath; 

		        var fname;

				var lname;

				var dayof;

				var machineno;

				var workord;

				var utime;

				var unitg;

				var units;

				var breaks;

				var handl;

				var train;

				var car;

				var machmat;

				var guag;

				var rework;

				var tot;

				var monthof;

				var rsWorkers;

				var totalhours;

				var pcshour;

				var totunits;

				var direct;

				var year1;

				var temp;

				var temp1;

				var temp2;

				var temp3;

				var temp4;

				var temp5;

				var temp6;

				var temp7;

				var temp8;

				var temp9;

				var temp10;

				var temp11;

				var temp12;

				var temp13;

				var temp14;

				var stgWorkers;

				var field;

				var stgYear;

												

				

				

				year1 = Request.Form("yea");

                fname = Request.Form("firstname");

				lname = Request.Form("lastname");

				dayof = Request.Form("day");

				monthof = Request.Form("month");

				machineno = Request.Form("machine");

				workord = Request.Form("workorder");

				utime = Request.Form("time");

				unitg = Request.Form("good");

				units = Request.Form("scrap");

				breaks = Request.Form("breaks");

				handl = Request.Form("handling");

				train = Request.Form("training");

				car = Request.Form("care");

				machmat = Request.Form("machinemat");

				guag = Request.Form("guage");

				rework1 = Request.Form("sort");

				tot = Request.Form("total");

				totunits = unitg + units;

				temp = utime;

				temp1 = breaks;

				temp2 = temp + temp1;

				temp3 = handl;

				temp4 = train;

				temp5 = temp3 + temp4;

				temp6 = temp2 + temp5;

				temp7 = car;

				temp8 = machmat;

				temp9 = temp7 + temp8;

				temp10 = temp9 + temp6;

				temp11 = guag;

				temp12 = rework1;

				temp13 = temp11 + temp12;

				temp14 = temp10 + temp13;

				totalhours = (temp14)/60;

				pcshour = totunits/totalhours;

				direct = totalhours/pcshour;

				rsWorkers = Server.CreateObject("ADODB.Recordset");

				stgWorkers = fname + lname + monthof;

				stgYear = year1;

				field = stgWorkers + stgYear;

				filePath = Server.MapPath("labor.mdb");

                oConn = Server.CreateObject("ADODB.Connection");

                oConn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" 

+ filePath);

				

				rsWorkers.Open(field, oConn);





				

				rsWorkers.Fields("dayofmonth") = dayof;

				

				rsWorkers.Fields("machine no") = machineno;

				

				rsWorkers.Fields("workorder") = workord;

				

				rsWorkers.Fields("uptime") = utime;

				

				rsWorkers.Fields("goodunits") = unitg;

				

				rsWorkers.Fields("scrapunits") = units;

				

				rsWorkers.Fields("breaks") = breaks;

				

				rsWorkers.Fields("handling") = handl;

				

				rsWorkers.Fields("training") = train;

				

				rsWorkers.Fields("tool") = care;

				

				rsWorkers.Fields("machinemat") = machmat;

				

				rsWorkers.Fields("guage") = guag;

				

				rsWorkers.Fields("total") = tot;

				

				rsWorkers.Fields("totalhours") = totalhours;

				

				rsWorkers.Fields("pcshour") = pcshour;

				

				rsWorkers.Fields("direct") = direct;

				

				rsWorkers.Fields("rework") = rework1;

				rsWorkers.Update;

				Response.Write("<br>All Fields Updated<br>");

				Response.Write("<a href='directlaborreport.html'>Do another 

report</a>");

Message #6 by "Ken Schaefer" <ken.s@a...> on Tue, 13 Jun 2000 18:57:57 +1000

Shawn,



a) Don't concantenate string values with +, use the & operator instead

b) Don't send lots of code with var in them - in any case var doesn't do

anything, you need to use DIM instead, eg:

<%

Option Explicit

Dim intSomeNumber

Dim strSomeString

%>



Cheers

Ken



----- Original Message -----

From: "Shawn Campbell" 

To: "ASP Databases" <asp_databases@p...>

Sent: Monday, June 12, 2000 5:32 PM

Subject: [asp_databases] Re: Syntax error, Incomplete Query clause





> I tried using that and the same error. I used field because it is a table

> name so I tried the following code. I appericate the help so far.

> I recieved a object or provider does not support that action error.

> var oConn;

>                 var oRs;

>                 var filePath;

>         var fname;

> var lname;

> var dayof;

> var machineno;



<snipped for your viewing comfort>

Return to Index

P2P Archives