Problem with multiple select statements and DataReader execution 1

[jn03]

Hi all,

Could someone please explain why the following doesn't work/works:

Ok, I have 3 select statements in my aspx.vb file:
sql1 - which is a long query
sql2 - short query
sql3 - longer than sql1 and have the same pattern as sql1

Then, I use ExecuteReader() method of DataReader to execute the 3 statements together in the following order:
sql1
sql2
sql3

Upon running the project, I got an error stating that a column in the sql1 cannot be found, which is not true (there is nothing wrong with sql1)

But, if the order of the sql statements is changed to
sql2
sql1
sql3
upon the DataReader execution (using ExecuteReader()), then it works!

Also, if I use ExecuteReader to execute each sql statement individually (only one at a time, the other two are commented out), then sql2 and sql3 work, but not sql3 (same error as above). That surprises me since sql1 and sql3 are of the same format and pattern; the sql3 query is even longer than the sql1 query.

I don't know why it happens this way. Please share your thoughts. Thanks.

 

[Rhys666]

Can you post code? (inc. SQL)...

Rhys
Thought out... Maybe,
Opinionated... Probably
But it is only an opinion!

 

[jn03]

Hi Rhys,

Thanks for taking your time looking into this. Please bear with the long sql statements.

Here is the code that works (execute sql2, then sql1 and sql3):
:
in my asp.vb file:

-----------------------------
Public Class _default
Inherits System.Web.UI.Page
Protected WithEvents dvnum As System.Web.UI.WebControls.Label
Protected WithEvents returnhdr, agentName, returndtl As System.Web.UI.WebControls.Repeater

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load


'Put user code to initialize the page here

Dim oConn As OleDbConnection
Dim sConnString As String
Dim oCmd As OleDbCommand
Dim strSQL_returnhdr, strSQL_agentName, strSQL_returndtl As String
Dim oDR_returnhdr, oDR_agentName, oDR_returndtl As OleDbDataReader

If Not (Page.IsPostBack) Then
'get passed query string
dvnum.Text = Request.Params("Dvnum&quot
End If

'sql1 - the problematic one
strSQL_returnhdr = "SELECT accounts.danum, returnhdr.dvnum,trnsptype.trnspdesc,SUM(retpackage.pkgnum) AS pkgnum," _
& "SUM(retpackage.pkgmass)AS pkgmass, agents.agent_name,returnhdr.wbillno," _
& "returnhdr.STATUS,returnhdr.Recdesc, returnhdr.createdate, returnhdr.rectcost," _
& "returnhdr.geninfo, returnhdr.inspdate,returnhdr.rectdate,accounts.daname, accounts.phone1, Division.divname," _
& "Branches.brname, carrier.cariername, Transtype.transdesc " _
& "FROM returnhdr,accounts, Division, Branches " _
& "LEFT OUTER JOIN Transtype ON returnhdr.ttype = Transtype.transcode " _
& "LEFT OUTER JOIN carrier ON carrier.cariercode = returnhdr.cariercode " _
& "LEFT OUTER JOIN retpackage ON retpackage.dvnum=returnhdr.dvnum " _
& "LEFT OUTER JOIN agents ON agents.agent_num=returnhdr.inspagent " _
& "LEFT OUTER JOIN trnsptype ON trnsptype.trnspcode=returnhdr.trnspcode " _
& "WHERE returnhdr.dvnum = " & dvnum.Text.ToString & " AND returnhdr.danum = accounts.danum " _
& "AND accounts.divnum = Division.divnum AND Division.brnum = Branches.brnum"


'sql2
strSQL_agentName = "SELECT agents.agent_name " _
& "FROM returnhdr LEFT OUTER JOIN agents ON agents.agent_num = returnhdr.rectagent " _
& "WHERE returnhdr.dvnum = " & dvnum.Text.ToString

'sql3
strSQL_returndtl = "SELECT returnhdr.dvnum,accounts.daname,nsnpc.sdescript,returndtl.transcode, " _
& "returndtl.nsn, returndtl.qty,Nsn_loc.location, Returnseria.eic, Returnseria.serial, " _
& "returndtl.outqty, returndtl.unknown,returnhdr.STATUS, " _
& "returndtl.createdate, returndtl.createby, returndtl.modifyby, " _
& "returndtl.modifydate,Transtype.transdesc " _
& "FROM returnhdr,accounts,nsnpc,returndtl LEFT OUTER JOIN Returnseria " _
& "ON returndtl.dvnum = Returnseria.dvnum AND returndtl.nsn = Returnseria.nsn " _
& "AND returndtl.transcode=returnseria.transcode " _
& "LEFT OUTER JOIN Nsn_loc ON returndtl.nsn = Nsn_loc.nsn " _
& "LEFT OUTER JOIN Transtype ON returndtl.transcode = Transtype.transcode " _
& "WHERE returnhdr.dvnum = " & dvnum.Text.ToString & " AND returnhdr.dvnum = returndtl.dvnum " _
& "AND nsnpc.nsn = returndtl.nsn AND returnhdr.danum = accounts.danum " _
& "UNION SELECT returnhdr.dvnum,accounts.daname,nsnpc.sdescript,returndtl.transcode,returndtl.nsn, returndtl.qty, " _
& "Nsn_loc.location, Returnseria.eic, Returnseria.serial, " _
& "returndtl.outqty, returndtl.unknown,returnhdr.STATUS, " _
& "returndtl.createdate, returndtl.createby, returndtl.modifyby, " _
& "returndtl.modifydate,Transtype.transdesc " _
& "FROM retrelation,accounts,nsnpc,returnhdr,returndtl LEFT OUTER JOIN Returnseria " _
& "ON returndtl.dvnum = Returnseria.dvnum AND returndtl.nsn = Returnseria.nsn " _
& "LEFT OUTER JOIN Nsn_loc ON returndtl.nsn = Nsn_loc.nsn " _
& "LEFT OUTER JOIN Transtype ON returndtl.transcode = Transtype.transcode " _
& "WHERE retrelation.dvnum = " & dvnum.Text.ToString & " AND retrelation.childvnum = returnhdr.dvnum " _
& "AND returnhdr.dvnum = returndtl.dvnum AND nsnpc.nsn = returndtl.nsn AND returnhdr.danum = accounts.danum " _
& "ORDER BY 3"


'database connection
sConnString = "provider=VFPOLEDB.1 ;data source= 'C:\data\mydatabase.DBC';password='';user id=''"
oConn = New OleDbConnection(sConnString)
oCmd = New OleDbCommand(strSQL_agentName, oConn)

Try
oConn.Open()

'execute and bind sql2
oDR_agentName = oCmd.ExecuteReader()

With agentName
.DataSource() = oDR_agentName
.DataBind()
End With

oDR_agentName.Close()

'execute and bind sql1
oCmd.CommandText = strSQL_returnhdr
oDR_returnhdr = oCmd.ExecuteReader

With returnhdr
.DataSource() = oDR_returnhdr
.DataBind()
End With

oDR_returnhdr.Close()

'execute and bind sql3
oCmd.CommandText = strSQL_returndtl
oDR_returndtl = oCmd.ExecuteReader()

With returndtl
.DataSource() = oDR_returndtl
.DataBind()
End With
oDR_returndtl.Close()

Catch exc As OleDbException
Response.Write(exc)
Finally
oCmd.Dispose()
If oConn.State = ConnectionState.Open Then
oConn.Close()
End If
oConn.Dispose()
End Try

End Sub

End Class

==========================
And the following one would not work (execute sql1, then sql2 and sql3):
==========================
....
oCmd = New OleDbCommand(strSQL_returnhdr, oConn)

Try
oConn.Open()

'execute and bind sql1
oDR_returnhdr = oCmd.ExecuteReader()

With returnhdr
.DataSource=oDR_returnhdr
.DataBind()
End With

oDR_returnhdr.Close()

'execute and bind sql2
oCmd.CommandText = strSQL_agentName oDR_agentName = oCmd.ExecuteReader

With agentName
.DataSource() = oDR_agentName
.DataBind()
End With

oDR_agentName.Close()

'execute and bind sql3
oCmd.CommandText = strSQL_returndtl
oDR_returndtl = oCmd.ExecuteReader()

With returndtl
.DataSource() = oDR_returndtl
.DataBind()
End With
oDR_returndtl.Close()

Catch exc As OleDbException
Response.Write(exc)
Finally
oCmd.Dispose()
If oConn.State = ConnectionState.Open Then
oConn.Close()
End If
oConn.Dispose()
End Try
.......

The error points to ExecuteReader(), saying the a column in sql1 statement is not found.

 

[Rhys666]

I've not used the repeater control, so am unsure on this one but the documentation discusses the ItemTemplate of the repeater control, controlling the display of data within it. Have you checked this out?


Rhys
Thought out... Maybe,
Opinionated... Probably
But it is only an opinion!

 

[harmmeijer]

The code looks OK, you don't use the command behavior closeconnection when you perform executereader:
.ExecuteReader(CommandBehavior.CloseConnection)
That would close the connection when you close the reader (making another executereader raise an error because the connection is closed).
And you close the reader before you perform an executereader on the command again (if you didn't the connection would not be available because it is held by the previous datareader).

So now we know what isn't the problem. My gues is that there is something wrong with sql.
What happens if you just execute the sql1 like this:


Dim oConn As OleDbConnection
Dim sConnString As String
Dim oCmd As OleDbCommand
Dim strSQL_returnhdr As String
Dim oDR_returnhdr As OleDbDataReader

'sql1 - the problematic one
strSQL_returnhdr = "SELECT accounts.danum, returnhdr.dvnum,trnsptype.trnspdesc,SUM(retpackage.pkgnum) AS pkgnum," _
& "SUM(retpackage.pkgmass)AS pkgmass, agents.agent_name,returnhdr.wbillno," _
& "returnhdr.STATUS,returnhdr.Recdesc, returnhdr.createdate, returnhdr.rectcost," _
& "returnhdr.geninfo, returnhdr.inspdate,returnhdr.rectdate,accounts.daname, accounts.phone1, Division.divname," _
& "Branches.brname, carrier.cariername, Transtype.transdesc " _
& "FROM returnhdr,accounts, Division, Branches " _
& "LEFT OUTER JOIN Transtype ON returnhdr.ttype = Transtype.transcode " _
& "LEFT OUTER JOIN carrier ON carrier.cariercode = returnhdr.cariercode " _
& "LEFT OUTER JOIN retpackage ON retpackage.dvnum=returnhdr.dvnum " _
& "LEFT OUTER JOIN agents ON agents.agent_num=returnhdr.inspagent " _
& "LEFT OUTER JOIN trnsptype ON trnsptype.trnspcode=returnhdr.trnspcode " _
& "WHERE returnhdr.dvnum = " & dvnum.Text.ToString & " AND returnhdr.danum = accounts.danum " _
& "AND accounts.divnum = Division.divnum AND Division.brnum = Branches.brnum"


'database connection
sConnString = "provider=VFPOLEDB.1 ;data source= 'C:\data\mydatabase.DBC';password='';user id=''"
oConn = New OleDbConnection(sConnString)
oCmd = New OleDbCommand(strSQL_agentName, oConn)

Try
oConn.Open()
'execute sql1
oCmd.CommandText = strSQL_returnhdr
oDR_returnhdr = oCmd.ExecuteReader

With returnhdr
.DataSource() = oDR_returnhdr
.DataBind()
End With
catch ex as exception
response.write ex.tostring()
end try


I will be interested in the exact error/exception.



Greetings, Harm Meijer

 

[jn03]

Hi Rhys, I doubt this has anything to do with repeater control since the error seems to point at the sql execution of the ExecuteReader method. As well, if the order of execution is changed to sql2,sql1,sql3 then the code works, so it means the repeater control is ok. But, to make sure, I'll look into that document you suggested since I'm clueless here. Thanks.

Hi Harm Meijer, I ran the piece of code like you suggested, and I got the same error as always:

----------------------------------
System.Data.OleDb.OleDbException: SQL: Column 'TTYPE' is not found. at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr) 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 efel._default.Page_Load(Object sender, EventArgs e) in C:\Feisdemo\efel\default.aspx.vb:line 122
------------------------------------

I'm sure this sql is valid since I have run it in another environment (Visual FoxPro); as well, like I have mentioned, it works if the order of execution is altered.

Thanks, guys

 

[harmmeijer]

I looked for TTYPE in the object browser looking in System.data (match case, match substring) but TTYPE could not be found.
Think this is a Database specific error, looked on google but could not find it.



Greetings, Harm Meijer

 

[Rhys666]

TType is a field used in a join in the problem SQL,

(& "LEFT OUTER JOIN Transtype ON returnhdr.ttype = Transtype.transcode " _)



Rhys
Thought out... Maybe,
Opinionated... Probably
But it is only an opinion!

 

[jn03]

Yep, TTYPE is just a field (column) in the sql. If I comment that line out, the error will point to another field and so on until I comment out almost everything, and the sql becomes something simple like "select ... from ... where..." then it'll work

 

[Rhys666]

I think SQL is confused because of the syntax of the select and from clauses. The tables accounts, Division and Branches aren't joined anywhere in the statement.

I believe you intend you SQL to come out...
SELECT
accounts.danum,
returnhdr.dvnum,
trnsptype.trnspdesc,
SUM(retpackage.pkgnum) AS pkgnum,
SUM(retpackage.pkgmass)AS pkgmass,
agents.agent_name,
returnhdr.wbillno,
returnhdr.STATUS,
returnhdr.Recdesc,
returnhdr.createdate,
returnhdr.rectcost,
returnhdr.geninfo,
returnhdr.inspdate,
returnhdr.rectdate,
accounts.daname,
accounts.phone1,
Division.divname,
Branches.brname,
carrier.cariername,
Transtype.transdesc
FROM
returnhdr,
accounts,
Division,
Branches,
LEFT OUTER JOIN Transtype
ON returnhdr.ttype = Transtype.transcode
LEFT OUTER JOIN carrier
ON carrier.cariercode = returnhdr.cariercode
LEFT OUTER JOIN retpackage
ON retpackage.dvnum=returnhdr.dvnum
LEFT OUTER JOIN agents
ON agents.agent_num=returnhdr.inspagent
LEFT OUTER JOIN trnsptype
ON trnsptype.trnspcode=returnhdr.trnspcode
WHERE
returnhdr.dvnum = " & dvnum.Text.ToString & "
AND
returnhdr.danum = accounts.danum
AND
accounts.divnum = Division.divnum
AND
Division.brnum = Branches.brnum

I believe you need to find a way to join tables accounts, Division, and Branches proprly fo the query to work. Try joining accounts to returnhdr on the danum field, Division to accounts on divnum, and Division to Branches on brnum. Try this...
strSQL_returnhdr = "SELECT accounts.danum, returnhdr.dvnum,trnsptype.trnspdesc,SUM(retpackage.pkgnum) AS pkgnum," _
& "SUM(retpackage.pkgmass)AS pkgmass, agents.agent_name,returnhdr.wbillno," _
& "returnhdr.STATUS,returnhdr.Recdesc, returnhdr.createdate, returnhdr.rectcost," _
& "returnhdr.geninfo, returnhdr.inspdate,returnhdr.rectdate,accounts.daname, accounts.phone1, Division.divname," _
& "Branches.brname, carrier.cariername, Transtype.transdesc " _
& "FROM returnhdr INNER JOIN accounts ON returnhdr.danum = accounts.danum " _
& "INNER JOIN Division ON accounts.dvnum = Division.dvnum " _
& "INNER JOIN Branches ON Branches.brnum = Division.brnum " _
& "LEFT OUTER JOIN Transtype ON returnhdr.ttype = Transtype.transcode " _
& "LEFT OUTER JOIN carrier ON carrier.cariercode = returnhdr.cariercode " _
& "LEFT OUTER JOIN retpackage ON retpackage.dvnum=returnhdr.dvnum " _
& "LEFT OUTER JOIN agents ON agents.agent_num=returnhdr.inspagent " _
& "LEFT OUTER JOIN trnsptype ON trnsptype.trnspcode=returnhdr.trnspcode " _
& "WHERE returnhdr.dvnum = " & dvnum.Text.ToString "

...although you may have to play with the sequence of joins...


Rhys
Thought out... Maybe,
Opinionated... Probably
But it is only an opinion!

 

[Rhys666]

Actually, just looking again you'll probably need to add a GROUP BY clause to the SQL as your using aggregate functions, (SUM), so you'll need to change the last line down to...
& "WHERE returnhdr.dvnum = " & dvnum.Text.ToString " _
& "GROUP BY accounts.danum, returnhdr.dvnum, trnsptype.trnspdesc, " _
& "agents.agent_name, returnhdr.wbillno, " _
& "returnhdr.STATUS, returnhdr.Recdesc, returnhdr.createdate, returnhdr.rectcost, " _
& "returnhdr.geninfo, returnhdr.inspdate, returnhdr.rectdate, accounts.daname, accounts.phone1, Division.divname," _
& "Branches.brname, carrier.cariername, Transtype.transdesc "

Apologies for missing that...

Rhys
Thought out... Maybe,
Opinionated... Probably
But it is only an opinion!

 

[jn03]

Hi Rhys,

It still doesn't work with either way you suggested, same error as always Right now I just go ahead with the order sql2,sql1,sql3 of execution, but have no clue on why it works this way...

Anyway, thank you very much for your help. I appreciated it. A star for ya!

Cheers.

 

[Rhys666]

This is definitely an SQL problem, (I hope!). It appears to be because SQL isn't recognising TType as a column in one of your tables so a few more suggestions for you...

1) Have you tried setting a break point and using the command window to get the value of strSQL_returnhdr at run time. You can then run the SQL directly in SQL Query Analyzer and manipulate it from ther until it works.

2) Have you tried explicitly giving each of the tables in the query a distinct pseudoname, (I.E.,
& "FROM returnhdr rh INNER JOIN accounts a ON rh.danum = a.danum " _
& "INNER JOIN Division dv ON a.dvnum = dv.dvnum " _
& "INNER JOIN Branches br ON br.brnum = dv.brnum " _
etc...
Not forgetting that the table names in the select statement will also need to be the given pseudonames)

3) Have you tried closing the connection object between DataReaders calls? (I.E.,
'database connection
sConnString = "provider=VFPOLEDB.1 ;data source= 'C:\data\mydatabase.DBC';password='';user id=''"
oConn = New OleDbConnection(sConnString)
oCmd = New OleDbCommand(strSQL_agentName, oConn)
Try
oCmd.Connection.Open()

'execute and bind sql2
oDR_agentName = oCmd.ExecuteReader()

With agentName
.DataSource() = oDR_agentName
.DataBind()
End With

oDR_agentName.Close()
oCmd.Connection.Open()

'execute and bind sql1
oCmd.CommandText = strSQL_returnhdr

oCmd.Connection.Open()
oDR_returnhdr = oCmd.ExecuteReader

With returnhdr
.DataSource() = oDR_returnhdr
.DataBind()
End With

oDR_returnhdr.Close()
oCmd.Connection.Close()

'execute and bind sql3
oCmd.CommandText = strSQL_returndtl
oCmd.Connection.Open();
oDR_returndtl = oCmd.ExecuteReader()

With returndtl
.DataSource() = oDR_returndtl
.DataBind()
End With
oDR_returndtl.Close()
oCmd.Connection.Close()
...etc...)

Rhys
Thought out... Maybe,
Opinionated... Probably
But it is only an opinion!