Incorrect syntax near ')'. Incorrect syntax near the keyword 'on'
I am new to VB.NET. I'm trying to add a table to an ASPX page. But I get the above error with the following code. Can you tell what I am doing wrong? I want many table rows returned; one for each product, where first cell = product name, cell 2 = NumOpen, and cell 3 = 95%.
<HTML>
<SCRIPT LANGUAGE="VB" RUNAT="Server">
Sub Page_Load(ByVal Sender As Object, ByVal E As EventArgs)
If Not IsPostBack Then
Dim YearDate As Date = "1/1/05"
Dim arrYear As New ArrayList()
While YearDate <= Today
arrYear.Add(YearDate.ToString("yyyy"))
YearDate = YearDate.AddYears(1)
End While
dYear.DataSource = arrYear
dYear.DataBind()
dYear.SelectedValue = Today.AddMonths(-1).ToString("yyyy")
Dim ListMonth As Date = "1/1/08"
Dim arrListMonth As New ArrayList()
While ListMonth <= "12/1/08"
arrListMonth.Add(ListMonth.ToString("MMM"))
ListMonth = ListMonth.AddMonths(1)
End While
dEndMonth.DataSource = arrListMonth
dEndMonth.DataBind()
dEndMonth.SelectedValue = Today.AddMonths(-1).ToString("MMM")
Else
End If
Main()
End Sub
Sub Main()
Dim FirstMonthDate As Date = CDate(dYear.SelectedValue & "-" & dEndMonth.SelectedValue & "-1")
FirstMonthDate = FirstMonthDate.AddMonths(-2)
Dim LastMonthDate As Date = GlobalFunctions.GlobalF.MonthLastDate(开发者_StackOverflow社区dYear.SelectedValue & "-" & dEndMonth.SelectedValue & "-1")
Dim arrMonths As New ArrayList()
While FirstMonthDate <= LastMonthDate
arrMonths.Add(GlobalFunctions.GlobalF.MonthLastDate(FirstMonthDate).ToString)
FirstMonthDate = FirstMonthDate.AddMonths(1)
End While
Dim monthString As String
Dim ProductList As String = "SELECT DISTINCT PRODUCT FROM EXCEL.DMR_PRODUCT"
Dim ProductListData As New System.Data.DataSet
ProductListData = GlobalFunctions.GlobalF.GetDevSQLServerDataSet(ProductList)
Dim ProductRow As DataRow
Dim y As Integer = 0
Dim arrayProducts() As String
Dim ProductTableRow As New HtmlTableRow
Dim ProductTableCell As New HtmlTableCell
ProductTableCell.InnerText = "Products"
ProductTableRow.Cells.Add(ProductTableCell)
ProductTableCell.Attributes.Add("class", "HeaderRow")
Dim NumOpenCell As New HtmlTableCell
NumOpenCell.InnerText = "Number Open"
ProductTableRow.Cells.Add(NumOpenCell)
NumOpenCell.Attributes.Add("class", "HeaderRow")
NumOpenCell.Width = 100
Dim ComplaintCell As New HtmlTableCell
ComplaintCell.InnerText = "% Of Open Complaints Complaints < 90 Days - Rolling 3 Month"
ProductTableRow.Cells.Add(ComplaintCell)
ComplaintCell.Attributes.Add("class", "HeaderRow")
ComplaintCell.Width = 100
Dim NumOpenList As String
table1.Rows.Add(ProductTableRow)
For Each ProductRow In ProductListData.Tables(0).Rows
For Each monthString In arrMonths
Next
ReDim Preserve arrayProducts(y)
arrayProducts(y) = ProductRow("PRODUCT")
ProductTableRow = New HtmlTableRow
ProductTableCell = New HtmlTableCell
ProductTableCell.InnerText = arrayProducts(y)
ProductTableRow.Cells.Add(ProductTableCell)
NumOpenList = "SELECT COUNT([FORMAT NUMBER]) " & _
"FROM(ALL_COMPLAINTS) " & _
" join (SELECT * FROM dbo.ProductPartNumbers(' " & _
arrayProducts(y) & _
" ')) on EPA_PRD_CODE like '%' + [FORMAT NUMBER] + '%' "
Dim NumOpenListData As New System.Data.DataSet
NumOpenListData = GlobalFunctions.GlobalF.GetDevSQLServerDataSet(NumOpenList)
NumOpenCell = New HtmlTableCell
NumOpenCell.InnerText = NumOpenList
ProductTableRow.Cells.Add(NumOpenCell)
ComplaintCell = New HtmlTableCell
ComplaintCell.InnerText = "95%"
ProductTableRow.Cells.Add(ComplaintCell)
table1.Rows.Add(ProductTableRow)
y = y + 1
Next
End Sub
Print out numOpenList. This is how you set it.
NumOpenList = "SELECT COUNT([FORMAT NUMBER]) " & _
"FROM(ALL_COMPLAINTS) " & _
" join (SELECT * FROM dbo.ProductPartNumbers(' " & _
arrayProducts(y) & _
" ')) on EPA_PRD_CODE like '%' + [FORMAT NUMBER] + '%' "
Now, set a breakpoint and get the actual string. Then check to make sure you have an equal number of ( and ). I am betting you will find more of one of the other. Fix it and your code is fixed.
If I am correct, you are probably blowing up here:
NumOpenListData = GlobalFunctions.GlobalF.GetDevSQLServerDataSet(NumOpenList)
Or in the actual GetDevSqlServerDataSet function in your GlobalFunctions library.
The problem is here:
NumOpenList = "SELECT COUNT([FORMAT NUMBER]) " & _
"FROM(ALL_COMPLAINTS) " & _
" join (SELECT * FROM dbo.ProductPartNumbers(' " & _
arrayProducts(y) & _
" ')) on EPA_PRD_CODE like '%' + [FORMAT NUMBER] + '%' "
The problem is your SQL is invalid. You can't join a (SELECT ...) directly without an alias following the SELECT. (I should also mention this is very poorly written code, and you should break the habit of using non-parameterized queries.)
Your SQL is boiling down to:
SELECT COUNT([FORMAT NUMBER]) FROM (ALL_COMPLAINTS)
join (SELECT * FROM dbo.ProductPartNumbers('somepartnumber'))
on EPA_PRD_CODE like '%something%'
If you try to populate the parts you're concatenating manually and running the SQL, it won't work.
加载中,请稍侯......
精彩评论