MSSQL Nested queries

Hi,

I have been trying to write a nested query, however i am facing errors while executing that.

The code is as below.

<%

Dim MyConnection =New SqlConnection()

MyConnection.ConnectionString = ConfigurationManager.ConnectionStrings("AppConnectionString1").ConnectionString

MyConnection.Open()

Dim cmdAsNew SqlCommandDim rsAs SqlDataReaderDim rs2As SqlDataReaderDim query ="select count(bank_id) from bank_master"Dim query2AsString

cmd.CommandType = CommandType.Text

cmd.Connection = MyConnection

cmd.CommandText = queryrs = cmd.ExecuteReaderIf rs.Read()ThenDim rec = rs(0)If (rec > 0)Then

Response.Write(rec &

" banks registered in the system")rs.Close()

query2 ="select * from bank_master order by bank_id"

rs2 = cmd.ExecuteReader(query2)

If rs2.Read()Then

Response.Write(rs2(0))

Else

Response.Write(

"No banks yet registered in the system")EndIfElse

Response.Write(

"No banks yet registered in the system")EndIfEndIf

%>

The error message that i am getting at runtime:

Server Error in '/Accounts' Application.

---

Input string was not in a correct format.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.FormatException: Input string was not in a correct format.

Source Error:

Line 117: Line 118: query2 = "select * from bank_master order by bank_id"Line 119: rs2 = cmd.ExecuteReader(query2)Line 120: If rs2.Read() ThenLine 121: Response.Write(rs2(0))

Source File:E:\WEBHOME\Accounts\bank.aspx Line:119

Could you please help?

Best Regards,

Debashis

Hi Debashis,

It looks to me like the problem may be that you don't have the line:

cmd.CommandText = query2

So you could have your code like:

 Dim MyConnection =New SqlConnection() MyConnection.ConnectionString = ConfigurationManager.ConnectionStrings("AppConnectionString1").ConnectionString MyConnection.Open()Dim cmdAs New SqlCommandDim rsAs SqlDataReaderDim rs2As SqlDataReaderDim queryAs String ="select count(bank_id) from bank_master"Dim query2As String ="select * from bank_master order by bank_id" cmd.CommandType = CommandType.Text cmd.Connection = MyConnection cmd.CommandText = query rs = cmd.ExecuteReaderIf rs.Read()Then Dim rec = rs(0)If (rec > 0)Then Response.Write(rec &" banks registered in the system") rs.Close() cmd.CommandText = query2 rs2 = cmd.ExecuteReader()If rs2.Read()Then Response.Write(rs2(0))Else Response.Write("No banks yet registered in the system")End If Else Response.Write("No banks yet registered in the system")End If End If

I haven't actually tried this code yet, but if this doesn't work, you could try having a cmd2 instead of re-using the single cmd line. I'm also not too sure why you need to run two queries as you could try this instead (unless I missed something ):

 Dim resultAs Integer = 0' Initialise connectionDim MyConnection =New SqlConnection(ConfigurationManager.ConnectionStrings("AppConnectionString1").ConnectionString)' Initialise commandDim cmdAs SqlCommand =New SqlCommand() cmd.Connection = MyConnection cmd.CommandText ="select count(bank_id) from bank_master"' Execute command Using MyConnection result =CType(cmd.ExecuteScalar(),Integer)End UsingIf result > 0Then Response.Write(result &" banks registered in the system")Else Response.Write("No banks yet registered in the system")End If

Hope this helps.

All the best,

Paul

|||

Hi Peter,

Hi Peter,
Thanks for your reply. It seems to be working with: cmd.CommandText = query2
However I am still unable to workout the recordcount from the query, as it returns -1.
The code is:

query2 = "select * from bank_master order by bank_id"
cmd2.CommandType = CommandType.Text
cmd2.Connection = MyConnection
cmd2.CommandText = query2
rs2 = cmd2.ExecuteReader
If rs2.HasRows Then

%>
<table>
<%
While rs2.Read()
%>
<tr>
<td>
<%=rs2(1)%>
</td>
</tr>
<%
End While
%>
</table>
<%
Else
Response.Write("No banks yet registered in the system")
End If

Please note that I havent included the recordcount code, as it wrong!

Thanks

Debashis

|||

Hi Debashis,

Sorry, I had missed a line from my code:

Dim resultAs Integer = 0' Initialise connectionDim MyConnection =New SqlConnection(ConfigurationManager.ConnectionStrings("AppConnectionString1").ConnectionString)' Initialise commandDim cmdAs SqlCommand =New SqlCommand() cmd.Connection = MyConnection cmd.CommandText ="select count(bank_id) from bank_master"' Execute command Using MyConnectionMyConnection.open() result =CType(cmd.ExecuteScalar(),Integer)End UsingIf result > 0Then Response.Write(result &" banks registered in the system")Else Response.Write("No banks yet registered in the system")End If

I've tested it this time and is fine. The other code I gave you also worked, but because there was an extra If ... End if statement than necessary, it also puts the id of the last bank at the end of the line 'banks registered in the system'. therefore, going back to your original code, this also works:

Imports System.DataImports System.Data.SqlClientPartialClass BankInherits System.Web.UI.PageProtected Sub Page_Load(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles Me.LoadDim MyConnection =New SqlConnection() MyConnection.ConnectionString = ConfigurationManager.ConnectionStrings("AppConnectionString1").ConnectionString MyConnection.Open()Dim cmdAs New SqlCommandDim rsAs SqlDataReaderDim queryAs String ="select count(bank_id) from bank_master" cmd.CommandType = CommandType.Text cmd.Connection = MyConnection cmd.CommandText = query rs = cmd.ExecuteReaderIf rs.Read()Then Dim recAs Integer = Convert.ToInt32(rs(0))If (rec > 0)Then Response.Write(rec &" banks registered in the system")Else Response.Write("No banks yet registered in the system")End If rs.Close()End If End SubEnd Class 

I put this code in the Page_Load event, and ran it OK.

If this has resolved the issue, please don't forget to mark it as the Answer.

Hope it helps,

Paul (not Peter !)

|||

Hi Paul,

Apologies for the mistake.

It seems to be working fine. Thanks for the help..