ASP.NET

1-Aug-07 1:15

Hi all, I have a page which works fine on my local computer, but when I upload it to my server it gives the following error. Can any one tell me the reason of that error and possible solution for that.

System.Data.OleDb.OleDbException: Cannot open any more tables. at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.OleDb.OleDbCommand.ExecuteReader() at cust_order.custOrdDes() in c:\domains\etradehouse.com\wwwroot\code\adm\cust_det\adm_order_det.aspx.vb:line 2090

and the code of that sub is given belown

Public Sub custOrd()
customerAsc.Clear()
customer.Clear()
Try
If Conn.State <> 0 Then
Conn.Close()
End If
Conn.Open()
SelectQuery = "select order_id from cust_order"
command = New OleDbCommand(SelectQuery, Conn)
total = command.ExecuteReader()
While total.Read
ordAsc.Add(total.GetValue(0))
End While
Dim i As Integer
For i = 0 To ordAsc.Count - 1
SelectQuery = "SELECT customers.customer_id,customers.customer_type FROM customers INNER JOIN cust_order ON customers.customer_id=cust_order.customer_id WHERE cust_order.order_id=" & ordAsc(i) & ""
command = New OleDbCommand(SelectQuery, Conn)
total = command.ExecuteReader()
While total.Read()
cstAsc.Add(total.GetValue(0))
typAsc.Add(total.GetValue(1))
End While
Next
total.Close()
total = Nothing
Dim k As Integer
For k = 0 To cstAsc.Count - 1
If typAsc(k) = 1 Then
SelectQuery = "Select comp_name, customer_id from biz_cust_extra where customer_id = " & cstAsc(k)
command = New OleDbCommand(SelectQuery, Conn)
total = command.ExecuteReader()
While total.Read()
If Not total.IsDBNull(0) Then
customerAsc.Add(total.GetValue(0) & "," & total.GetValue(1))
End If
End While
Else
SelectQuery = "Select fname, lname, customer_id from customers where customer_id = " & cstAsc(k)
command = New OleDbCommand(SelectQuery, Conn)
total = command.ExecuteReader()
While total.Read()
If Not total.IsDBNull(0) Then
customerAsc.Add(total.GetValue(0) & " " & total.GetValue(1) & "," & total.GetValue(2))
End If
End While
End If
Next
total.Close()
total = Nothing
Dim customer As New ArrayList
Dim cust As Array
customerAsc.Sort()
Dim j As Integer
For j = 0 To customerAsc.Count - 1
cust = Split(customerAsc(j).ToString, ",")
customer.Add(cust(1))
Next
Dim l As Integer
Dim str As String = ""
For l = 0 To customer.Count - 1
If l = 0 Then
str &= "txtSearch=" & customer(l)
Else
str &= "&txtSearch=" & customer(l)
End If
Next
Response.Redirect("/code/adm/cust_det/adm_order_det.aspx?index=32&" & str)
Catch ex As Exception
Response.Write(ex.ToString)
Finally
Conn.Close()
End Try
End Sub

the line 2090 is underline in above code.

Pls help me its very urgent.

Thanks in Advance.

_mubashir1-Aug-07 1:59

1-Aug-07 1:59

I see you havnt closed your reader 'total' after the first query.
try, total.Close()

Mubashir
Software Architect
Storan Technologies Inc, USA
Every job is a self portrait of the person who did it.

Khan.Bangash1-Aug-07 2:04

1-Aug-07 2:04

Now I close it but it still gives the same error. Can u tell me what is the reason and possible solution for that.

_mubashir1-Aug-07 2:13

1-Aug-07 2:13

OK, see this

<br />
For i = 0 To ordAsc.Count - 1<br />
SelectQuery = "SELECT customers.customer_id,customers.customer_type FROM customers INNER JOIN cust_order ON customers.customer_id=cust_order.customer_id WHERE cust_order.order_id=" & ordAsc(i) & ""<br />
command = New OleDbCommand(SelectQuery, Conn)<br />
total = command.ExecuteReader()<br />
While total.Read()<br />
cstAsc.Add(total.GetValue(0))<br />
typAsc.Add(total.GetValue(1))<br />
End While<br />
Next<br />

Here you are looping thru some list and executing reader without closing it. That is why it is throwing exception. Make sure you close the reader after you finish fetching records.

Mubashir
Software Architect
Storan Technologies Inc, USA
Every job is a self portrait of the person who did it.

Khan.Bangash1-Aug-07 2:22

1-Aug-07 2:22

Can u pls tell me where should i close reader in my code. I m new to programming so why i m facig problems.

_mubashir1-Aug-07 2:27

1-Aug-07 2:27

Sure..

<br />
For i = 0 To ordAsc.Count - 1<br />
SelectQuery = "SELECT customers.customer_id,customers.customer_type FROM customers INNER JOIN cust_order ON customers.customer_id=cust_order.customer_id WHERE cust_order.order_id=" & ordAsc(i) & ""<br />
command = New OleDbCommand(SelectQuery, Conn)<br />
total = command.ExecuteReader()<br />
While total.Read()<br />
cstAsc.Add(total.GetValue(0))<br />
typAsc.Add(total.GetValue(1))<br />
End While<br />
total.Close()   '<---<br />
Next<br />

See above, right after the while loop. I see you have the same mistake in all your code. Fix them with the same

Mubashir
Software Architect
Storan Technologies Inc, USA
Every job is a self portrait of the person who did it.

Khan.Bangash1-Aug-07 2:41

1-Aug-07 2:41

I have done according to ur answer but now another problem occured
and following error occured.
The page cannot be displayed
The page you are looking for is currently unavailable. The Web site might be experiencing technical difficulties, or you may need to adjust your browser settings

_mubashir1-Aug-07 5:34

1-Aug-07 5:34

Good, so you are out of one problem.
Now it seems like your program stuck into a never ending loop. It is a typical logical error, try debugging your code.

Mubashir
Software Architect
Storan Technologies Inc, USA
Every job is a self portrait of the person who did it.

Khan.Bangash1-Aug-07 5:53

1-Aug-07 5:53

Thank u very much dear.

Now I will find out what is the error.

I think u r Pakistani. I m also Pakistani. My name is Arif Bangash. And I m new to programming. Will u help me in making myself a good programmer. Can u pls give me ur email address with help of which I can contact u.

_mubashir1-Aug-07 6:01