2. ASPRunnerPro General questions
  3. Edit SQL query error
This topic is locked

Edit SQL query error

 		1/5/2006 6:03:30 PM
ASPRunnerPro General questions
K
 kevinl author

I used asp runner to build a interface for a SQL database but I can't used edit because the query wasnt right some how.
This is the error it give everytime i click on edit

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'and'.
/testing/SVCServiceAgreements_edit.asp, line 615


This is line 615

rs.open strSQL, dbConnection


and this is what it refer to

' build SQL query to retrieve data



strSQL = gstrSQL

sWhere = AddWrappers(strKeyField) & "=" & gstrQuote & PrepareKeyColumnValue(strTableName, strKeyField, GetRequestForm("editid")) & gstrQuote

if strKeyField2<>"" then sWhere=sWhere & " and " & AddWrappers(strKeyField2) & "=" & gstrQuote2 & Replace(GetRequestForm("editid2"),"'","''") & gstrQuote2

if strKeyField3<>"" then sWhere=sWhere & " and " & AddWrappers(strKeyField3) & "=" & gstrQuote3 & Replace(GetRequestForm("editid3"),"'","''") & gstrQuote3

strSQL = AddWhere(strSQL, sWhere)


and this is the variables file

strKeyField="LotSerialNumber"

strKeyField2="InvoiceNumber"

strKeyField3=""

strTableName="[dbo].[SVCServiceAgreements]"

PageSize=20

session("pagesize") = PageSize

strLeftWrapper="["

strRightWrapper="]"

gstrOrderBy="ORDER BY [RecordCreationDateTime] DESC"

bKeyFieldEditable1=False

bKeyFieldEditable2=False

bKeyFieldEditable3=False

gstrSQL = "select A.*,(select Min(TermInMonths) from SVCServiceItemTerms T where A.SVCItemNumber=T.SVCNumber) as TermInMonths from SVCServiceAgreements A"

gstrQuote = Session(strTableName & "_gstrQuote")

gstrQuote2 = Session(strTableName & "_gstrQuote2")

gstrQuote3 = Session(strTableName & "_gstrQuote3")
Admin 1/6/2006

Kevin,
your query is too complex to work in ASPRunner.

We adding a workaround for this issue that will be available in couple of days.
Also you need to modify SQL query the following way:

select SVCServiceAgreements.*, (select Min(TermInMonths) from SVCServiceItemTerms T where SVCServiceAgreements.SVCItemNumber=T.SVCNumber) as TermInMonths

from SVCServiceAgreements where 1=1


where 1=1 is required to help ASPRunner find correct WHERE clause.
Please note - this will work with next ASPRunnerPro update only.
K
 		kevinl author 1/6/2006

Hi,
I used your query, everything work fine, i builted one interface and save the project file.
Then I wanted to go back to change the label of the column to Service Term (In Months) instead of just leave it like in SQL database (TermInMonths). It gave me the error again. Please help.

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'as'.
/testing/SVCServiceAgreements_edit.asp, line 646
Admin 1/6/2006

Kevin,
try to set vDebug variable in include/commonfunctions.asp to True, proceed to the edit page and post SQL query that Edit page generates.
K
 		kevinl author 1/11/2006

Here is the query it came up with

select SVCServiceAgreements.*, (select Min(TermInMonths) from SVCServiceItemTerms T where SVCServiceAgreements.SVCItemNumber=T.SVCNumber) as TermInMonths from SVCServiceAgreements where 1=1 ORDER BY [RecordCreationDateTime] DESC


but all records show up. But when I try to edit one record it give me this

select SVCServiceAgreements.*, (select Min(TermInMonths) from SVCServiceItemTerms T where (SVCServiceAgreements.SVCItemNumber=T.SVCNumber) as TermInMonths from SVCServiceAgreements where 1=1) and ([LotSerialNumber]='M174318' and [InvoiceNumber]='225907')
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'as'.
/testing2/SVCServiceAgreements_edit.asp, line 646
K
 		kevinl author 1/11/2006

When it build the query the program add the close parenthesis at the end like this
select SVCServiceAgreements.*, (select Min(TermInMonths) from SVCServiceItemTerms T where (SVCServiceAgreements.SVCItemNumber=T.SVCNumber) as TermInMonths from SVCServiceAgreements where 1=1)
This cause the error in the query, where do I fix this problem in AspRunner or in the code?
Admin 1/11/2006

Kevin,
please download build 78 and use the following query:

select SVCServiceAgreements.*, (select Min(TermInMonths) from SVCServiceItemTerms T where SVCServiceAgreements.SVCItemNumber=T.SVCNumber) as TermInMonths

from SVCServiceAgreements where 1=1


Let me know if it works.
K
 		kevinl author 1/11/2006

What's build 78 ?? I already download the 3.2.
Admin 1/11/2006

Everything I said regarding this issue applies to ASPRunnerPro 4.0. Latest build number is 78.

http://www.asprunner.com/forums/index.php?showtopic=2250
K
 		kevinl author 1/12/2006

I see, but I have version 3.2 register. If I use 4.0, I need to register again. Is there anyway I can fix this in 3.2?
The query still have a parenthesis added to the end.
K
 		kevinl author 1/12/2006

Hi,
Here the query come up when I used version 4.0 with the query you gave me.

select SVCServiceAgreements.*, (select Min(TermInMonths) from SVCServiceItemTerms T where SVCServiceAgreements.SVCItemNumber=T.SVCNumber) as TermInMonths from SVCServiceAgreements where 1=1 ORDER BY [RecordCreationDateTime] DESC

select count(*) from SVCServiceItemTerms T where SVCServiceAgreements.SVCItemNumber=T.SVCNumber) as TermInMonths from SVCServiceAgreements where 1=1
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near ')'.
/testing2/ServiceAgreements_list.asp, line 748
K
 		kevinl author 1/12/2006

Hi,
Okay I blocked out the count function which help bring up the list of records. But there are still errors in View, Edit, Search
View not support request

select SVCServiceAgreements.*, (select Min(TermInMonths) from SVCServiceItemTerms T where (SVCServiceAgreements.SVCItemNumber=T.SVCNumber) as TermInMonths from SVCServiceAgreements where 1=1) and ([LotSerialNumber]='M719922' and [InvoiceNumber]='224037')
Microsoft OLE DB Provider for ODBC Drivers error '80040e21'
ODBC driver does not support the requested properties.
/testing/ServiceAgreements_view.asp, line 72


Edit driver not support

select SVCServiceAgreements.*, (select Min(TermInMonths) from SVCServiceItemTerms T where (SVCServiceAgreements.SVCItemNumber=T.SVCNumber) as TermInMonths from SVCServiceAgreements where 1=1) and ([dbo].[SVCServiceAgreements].[LotSerialNumber]='M719922' and [dbo].[SVCServiceAgreements].[InvoiceNumber]='224037')
Microsoft OLE DB Provider for ODBC Drivers error '80040e21'
ODBC driver does not support the requested properties.
/testing/SVCServiceAgreements_edit.asp, line 627


Search have parenthesis added at the end

select SVCServiceAgreements.*, (select Min(TermInMonths) from SVCServiceItemTerms T where (SVCServiceAgreements.SVCItemNumber=T.SVCNumber) as TermInMonths from SVCServiceAgreements where 1=1) and (1=0 or upper([dbo].[SVCServiceAgreements].[SVCItemNumber] ) like upper('%kevin%') or upper([dbo].[SVCServiceAgreements].[LotSerialNumber] ) like upper('%kevin%') or [dbo].[SVCServiceAgreements].[StartDate] like upper('%kevin%') or upper([dbo].[SVCServiceAgreements].[ItemNumber] ) like upper('%kevin%') or upper([dbo].[SVCServiceAgreements].[CustomerNumber] ) like upper('%kevin%') or upper([dbo].[SVCServiceAgreements].[InvoiceNumber] ) like upper('%kevin%') or [dbo].[SVCServiceAgreements].[RecordCreationDateTime] like upper('%kevin%') or [dbo].[SVCServiceAgreements].[EndDate] like upper('%kevin%') or upper([dbo].[SVCServiceAgreements].[CustomerName] ) like upper('%kevin%') or upper([dbo].[SVCServiceAgreements].[Notes1] ) like upper('%kevin%')) ORDER BY [RecordCreationDateTime] DESC
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'as'.
/testing/SVCServiceAgreements_list.asp, line 773


All of the query have the parenthesis added to the end, I think that's what cause the error.
Admin 1/12/2006

Kevin,
please send me generated ASP files, project file and SQL script that creates required tables in SQL Server.