I will now dive into the painful details about what im trying to do. I
have written an asp.net page (page.aspx) , which serves 2
functionalities.
1. RECENT TABLE. It shows a table of the 10 most recent records in my
mySql database, along with 2 links (one called pcl and another ecn)
for each record which brings the user to 2 asp.net pages which are
forms. The forms are partially filled, using data from the database
corresponding to the record clicked on by the user.
2. SEARCH TABLE. If the record is not amongst the 10 most recent, then
the user may choose to enter it in the search text box. A search
results table is then displayed, which has the same format as the
Recent Table.
The Problem: The form links from the recent table is fine, bringing
the user to the forms, and grabing data from the database, and filling
it sinto appropriate sections of the form. However, both the pcl and
ecn form links are not working from the search table. Note that the
links to which the recent and search tables bring the user is the
same. Ie, say for record1, we link to pcl.aspx and ecn.aspx. Accessing
these links from the recent table is fine, but from the search table,
they are not fine. Because of this, I think the issue lies in
page.aspx and not in pcl.aspx or ecn.aspx. The error messages that
comes up are associated with pcl.aspx and ecn.aspx however, and are
also different.
The exception from the pcl form link is:
Exception Details: MySql.Data.MySqlClient.MySqlException: Invalid
attempt to access a field before calling Read()
<code in pcl.aspx>
Sub Page_Load
if Not Page.IsPostBack Then
Dim myConnection As MySqlConnection
myConnection = New MySqlConnection("server=localhost; user
id=myuser; database=database1; pooling=false;")
myConnection.Open
Dim index, str
index = CType(PreviousPage.FindControl("index_sub"), Label).Text
str = "SELECT part_no, customer, cust_part_no, cust_code FROM table1
WHERE id= @index;"
Dim sqlComm As New MySqlCommand(str, myConnection)
sqlComm.Parameters.AddWithValue("@index", index)
Dim sqlRead As MySqlDataReader = sqlComm.ExecuteReader()
sqlRead.Read()
txt_part_no.Text = sqlRead(0)
txt_customer.Text = sqlRead(1)
txt_cust_part_no.Text = sqlRead(2)
txt_cust_code.Text = sqlRead(3)
sqlRead.Close
myConnection.Close
end if
End Sub
</code>
The line which gets highlighted by the error msg is "txt_part_no.Text
= sqlRead(0)"
The exception from the ecn form link is:
Exception Details: MySql.Data.MySqlClient.MySqlException: You have an
error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near '' at line 1
Googling tells me that its a bug, reported in 2005. Has it been fixed
by now? (http://bugs.mysql.com/bug.php?id=9668) Though I believe the
occurence of this error shows that something is wrong, though my code
is probably not wrong there, but somewhere else. Because I did
call .Read() before accessing .Read(0). And also, when this page
(ecn.aspx) is accessed via the recent table, there are no errors and
the page loads and works fine.
<code in ecn.aspx>
Sub Page_Load
If Not Page.IsPostBack then
Dim myConnection As MySqlConnection
myConnection = New MySqlConnection("server=localhost; user
id=myuser; database=database1; pooling=false;")
myConnection.Open
Dim index, str
index = CType(PreviousPage.FindControl("index_sub"), Label).Text
str = "SELECT part_no, customer FROM table1 WHERE id=" & index & ";"
Dim sqlComm As New MySqlCommand(str, myConnection)
Dim sqlRead As MySqlDataReader = sqlComm.ExecuteReader()
</code>
The code continues, but it is the last line of code here that gets
highlighted.
I really think the issue lies in page.aspx, which has 3 subs. One
fills the recent table (page_load) and another, the search table (upon
the click of the search button). The third is called by both of these
and saves the record number so that form link clicked on by the user
knows which record to retrieve.
<code in page.aspx>
<%@ Page Language="VB" debug="true" EnableViewState="false" %>
<%@ Import Namespace = "System.Data" %>
<%@ Import Namespace = "MySql.Data.MySqlClient" %>
<script language="VB" type="text/vbscript" runat="server">
Sub Page_Load
'Link to database
Dim myConnection As MySqlConnection
myConnection = New MySqlConnection("server=localhost; user
id=myuser; database=database1; pooling=false;")
myConnection.Open
'The following loop is needed to dynamically display the data table.
dim i As Integer
dim headRow As New TableRow
dim headCell As New TableHeaderCell
headRow = New TableRow()
headCell = New TableHeaderCell()
headCell.Controls.Add(New LiteralControl("Part no."))
headRow.Cells.Add(headCell)
headCell = New TableHeaderCell()
headCell.Controls.Add(New LiteralControl("Customer P/N"))
headRow.Cells.Add(headCell)
headCell = New TableHeaderCell()
headCell.Controls.Add(New LiteralControl("Customer"))
headRow.Cells.Add(headCell)
headCell = New TableHeaderCell()
headCell.Controls.Add(New LiteralControl("PCL"))
headRow.Cells.Add(headCell)
headCell = New TableHeaderCell()
headCell.Controls.Add(New LiteralControl("ECN"))
headRow.Cells.Add(headCell)
Table2.Rows.Add(headRow)
dim strIndex
strIndex = "SELECT id FROM table1 ORDER BY id DESC LIMIT 1;"
Dim sqlComm As New MySqlCommand(strIndex, myConnection)
Dim index As Integer
index = sqlComm.ExecuteScalar()
For i= index-9 to index
Dim r As New TableRow()
Dim c As New TableCell()
Dim Lbl, Lbl_cust_part_no, Lbl_customer As New label()
Dim Lbl_pcl, Lbl_ecn As New LinkButton()
Dim strSQL As String
strSQL = "SELECT part_no FROM table1 WHERE id=" & i & " "
sqlComm = New MySqlCommand(strSQL, myConnection)
Lbl.Text = sqlComm.ExecuteScalar()
c.Controls.Add(Lbl)
r.Cells.Add(c)
c = New TableCell()
strSQL = "SELECT cust_part_no FROM table1 WHERE id=" & i & " "
sqlComm = New MySqlCommand(strSQL, myConnection)
Lbl_cust_part_no.Text = sqlComm.ExecuteScalar()
c.Controls.Add(Lbl_cust_part_no)
r.Cells.Add(c)
c = New TableCell()
strSQL = "SELECT customer FROM table1 WHERE id=" & i & " "
sqlComm = New MySqlCommand(strSQL, myConnection)
Lbl_customer.Text = sqlComm.ExecuteScalar()
c.Controls.Add(Lbl_customer)
r.Cells.Add(c)
c = New TableCell()
AddHandler Lbl_pcl.Command, AddressOf forms_Click
Lbl_pcl.CommandName = i
Lbl_pcl.Text = "PCL for " & i
Lbl_pcl.PostBackUrl = "pcl.aspx"
c.Controls.Add(Lbl_pcl)
r.Cells.Add(c)
c = New TableCell()
AddHandler Lbl_ecn.Command, AddressOf forms_Click
Lbl_ecn.CommandName = i
Lbl_ecn.Text = "ECN"
Lbl_ecn.PostBackUrl = "ecn.aspx"
c.Controls.Add(Lbl_ecn)
r.Cells.Add(c)
Table2.Rows.Add(r)
Next
myConnection.Close
End Sub
Sub Submit_Search(ByVal obj As System.Object, ByVal e As
System.EventArgs) 'Handles btn_submit.Click
'This function gets the part number entered by the user and finds it
in the database, and returns the data for this part number.
'Show the search results table
Label1.Visible = true
Table1.Visible = true
dim headRow As New TableRow
dim headCell As New TableHeaderCell
'The header row of the search results table
headRow = New TableRow()
headCell = New TableHeaderCell()
headCell.Controls.Add(New LiteralControl("Part no."))
headRow.Cells.Add(headCell)
headCell = New TableHeaderCell()
headCell.Controls.Add(New LiteralControl("Customer P/N"))
headRow.Cells.Add(headCell)
headCell = New TableHeaderCell()
headCell.Controls.Add(New LiteralControl("Customer"))
headRow.Cells.Add(headCell)
headCell = New TableHeaderCell()
headCell.Controls.Add(New LiteralControl("PCL"))
headRow.Cells.Add(headCell)
headCell = New TableHeaderCell()
headCell.Controls.Add(New LiteralControl("ECN"))
headRow.Cells.Add(headCell)
Table1.Rows.Add(headRow)
'Link to database
Using myConnection As New MySqlConnection("server=localhost; user
id=myuser; database=database1; pooling=false;")
Dim str_search = New String("SELECT id, part_no, cust_part_no,
customer FROM table1 WHERE part_no LIKE concat
('%',@input_part_no,'%')")
Dim sqlComm As New MySqlCommand(str_search,myConnection)
sqlComm.Parameters.AddWithValue("@input_part_no",
search_part_no.Text)
Dim dr As MySqlDataReader
myConnection.Open()
dr = sqlComm.ExecuteReader
If dr.HasRows Then
'Return rows of data found from the database
Do While dr.Read()
Dim id_found As Integer
Dim r As New TableRow()
Dim c As New TableCell()
Dim Lbl, Lbl_cust_part_no, Lbl_customer As New label()
Dim Lbl_pcl, Lbl_ecn As New LinkButton()
Dim strSQL As String
id_found = dr.GetValue(0).ToString
Lbl.Text = dr.GetValue(1).ToString
c.Controls.Add(Lbl)
r.Cells.Add(c)
c = New TableCell()
Lbl_cust_part_no.Text = dr.GetValue(2).ToString
c.Controls.Add(Lbl_cust_part_no)
r.Cells.Add(c)
c = New TableCell()
Lbl_customer.Text = dr.GetValue(3).ToString
c.Controls.Add(Lbl_customer)
r.Cells.Add(c)
c = New TableCell()
AddHandler Lbl_pcl.Command, AddressOf forms_Click
Lbl_pcl.CommandName = id_found
Lbl_pcl.Text = "PCL for " & id_found
Lbl_pcl.PostBackUrl = "pcl1_3.aspx"
c.Controls.Add(Lbl_pcl)
r.Cells.Add(c)
c = New TableCell()
AddHandler Lbl_ecn.Command, AddressOf forms_Click
Lbl_ecn.CommandName = id_found
Lbl_ecn.Text = "ECN " & id_found
Lbl_ecn.PostBackUrl = "ecn5.aspx"
c.Controls.Add(Lbl_ecn)
r.Cells.Add(c)
Table1.Rows.Add(r)
Loop
'No data matched from database
Else
label1.Text = "Sorry, your part number was not found. Do try again.
=)"
table1.Visible = false
End If
myConnection.Close()
End Using
End Sub
Sub forms_Click(sender As Object, e As CommandEventArgs)
'index_sub is simply a label that is used to store a variable which
can then be accessed by the next page.
index_sub.Text = e.CommandName
End Sub
</code>
Is it something wrong with the Sub forms_Click? Maybe it is not being
called from Submit_Search? But I don't know how to check this, since
upon the click of the linkbutton, the form page loads.
I am really clueless about this strange error. Help is much
appreciated!
