If you changed your Query to:
SQL = "select (max(TotalPayable) - max(TotalPaid)) AS TotalOwing from Payments Group by StudentId having StudentId = '" & Trim(cboStudentId.SelectedItem.Value) & "'"
Then you could get the single value using ExecuteScalar()
And simply write that value to the textbox like:
textbox.text = db0.ExecuteScalar()

Some good stuff here from tillgeffken...
No need to loop through the records though, there'll only be one record returned...
This:
SQLdr = SQLCmd.ExecuteReader
While dr.Read()
OpeningBalance.Text = dr.("Balance").ToString()
End While
Loop While SQLdr.NextResult() 'Move to the Next Record
Could be replaced with:
OpeningBalance.Text = SQLCmd.ExecuteScalar()

Try using this because in case the value returned is zero or null or empty:
aOpeningBalance.Text = GetOpeningBalance(aStudentId.SelectedItem.Value.Trim()).ToString("0.00")
For more formatting options, see this link too: http://msdn.microsoft.com/en-us/library/0c899ak8.aspx

The application am working on was originally developed as a Windows application but then I want it to be upgraded to a Web system. The attached code runs well in the Windows application. It is its ASP.NET equivalent which I need.

I am new to ASP.NET.

Nduguyettu

sql = "select max(TotalPayable)-max(TotalPaid) from Payments Group by StudentId having StudentId='" & Trim(cboStudentId.Text) & "'"
comm.CommandText = sql
rs = comm.Execute
If Not rs.EOF Then
txtOpeningBalance.Text = rs.Fields(0).Value
End If

Is there anywhere else where you possibly assign a value to the textbox? (That might be overwritting this value?) Can you step through the code and see what it's generating?
And, I've been looking at something else here:

You need to insert a Break-Point, for it to be able to stop...
And, I'd change the code to be like this: (It'll make it easier to debug)
BTW, if you assign a value to aOpeningBalance.Text does it display the value? ( aOpeningBalance.Text = "A test")
You'll notice that I added a "Response.Write" into the code below... (That should write the value to the browser (top of the page))

The reason that 9999 is displaying is because the SQL Query is generating an error...
(At least we know that part is now working... :-) )
Now to determine why the SQL isn't working... if you "look" into the SQL table, should this query give you a valid answer? (Is there a record that matches aStudentId, and that doesn't have nulls in either of the fields that you are attempting to add (then subtract)?)
I'd start by "playing" with query... change it to something like:
Dim cmd As New SqlCommand("SELECT count(TotalPayable) FROM Payments WHERE StudentId='" & Trim(aStudentId.SelectedItem.Value) & "'", con)
con.Open()
dim OpeningBalance as double = cmd.ExecuteScalar()
aOpeningBalance.Text = OpeningBalance.ToString
response.write("OpeningBalance = " & OpeningBalance)
con.Close()
And see what you get, then we can take it further from there...

I'd add a user, WITH a password, and see if you can login with those credentials...
Here's a connString that I use:
<add key="connString" value="initial catalog=DBNAME;data source=(local);user idUSERNAME;password=PASSWORD" />

Exactly where should I put that connString? Should it bear the information as specified above or with other information like a specified USERNAME? As I said the database has no password, should I just type PASSWORD?

That connString I put in the Web.Config...
Then I pull it like this:
Public Conn0 As New SqlConnection(ConfigurationSettings.AppSettings("connString"))
You need to specify a Username and Password... that's why I suggested ADDING a user to the DB (even if just for testing) that has a password...
BTW, what version of .Net are you working with?

I ve put several break points but the application just runs through and returns the attached Exception

Server Error in '/NdejjeManagementApplication' Application.
--------------------------------------------------------------------------------
Cast from type 'DBNull' to type 'Double' is not valid.
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.InvalidCastException: Cast from type 'DBNull' to type 'Double' is not valid.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[InvalidCastException: Cast from type 'DBNull' to type 'Double' is not valid.]
Microsoft.VisualBasic.CompilerServices.DoubleType.FromObject(Object Value, NumberFormatInfo NumberFormat)
Microsoft.VisualBasic.CompilerServices.DoubleType.FromObject(Object Value)
NdejjeManagementApplication.FeesPayments.aSemester_SelectedIndexChanged(Object sender, EventArgs e)
System.Web.UI.WebControls.ListControl.OnSelectedIndexChanged(EventArgs e)
System.Web.UI.WebControls.DropDownList.System.Web.UI.IPostBackDataHandler.RaisePostDataChangedEvent()
System.Web.UI.Page.RaiseChangedEvents()
System.Web.UI.Page.ProcessRequestMain()
--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:1.1.4322.2407; ASP.NET Version:1.1.4322.2407

This error will be occurring because the value that you are getting from the DB is null...
This means that the system is not able to do the calculation...
In Enterprise Manager it's possible to design (and TEST) the Query... and I suggest you do that 1st...

I run a query in Enterprise manager. The query and the results are attached. But when I incorporate it in my ASP.NET code shown below

Dim cmd As New SqlCommand("SELECT MAX(TotalPayable) - MAX(TotalPaid) AS OpeningBalance FROM FeesPayments GROUP BY StudentId HAVING StudentId='" & Trim(aStudentId.SelectedItem.Value) & "'", con)

It returns the Exception in the attachement (Last portion)

SELECT StudentId, MAX(TotalPayable) AS TPayable, MAX(TotalPaid) AS TPaid, MAX(TotalPayable) - MAX(TotalPaid) AS Bal
FROM dbo.Payments
GROUP BY StudentId
===================================================================
StudentId TPayable TPaid Bal
00/BED/100 712000 0 712000
01//BA/064 400000 0 400000
01/BA/006 510000 0 510000
===================================================================
Object reference not set to an instance of an object.
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.NullReferenceException: Object reference not set to an instance of an object.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[NullReferenceException: Object reference not set to an instance of an object.]
NdejjeManagementApplication.FeesPayments.aSemester_SelectedIndexChanged(Object sender, EventArgs e)
System.Web.UI.WebControls.ListControl.OnSelectedIndexChanged(EventArgs e)
System.Web.UI.WebControls.DropDownList.System.Web.UI.IPostBackDataHandler.RaisePostDataChangedEvent()
System.Web.UI.Page.RaiseChangedEvents()
System.Web.UI.Page.ProcessRequestMain()

So, what do you get from within Enterprise Manager if you run this query?
SELECT (max(TotalPayable)-max(TotalPaid)) As OpeningBalance FROM Payments WHERE StudentId='00/BED/100'
Or with any of the other StudentID's...

I wish to thank Espavo for the several option from which I managed to get the correct answer. I wish to thank him for the patience because he persistently sent me options which I tried and sent back information regarding the failure of the system. He never failed to give another solution. Please keep it up. Nduguyettu

0

Featured Post

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

AJAX ModalPopupExtender has a required property "TargetControlID" which may seem to be very confusing to new users. It means the server control that will be extended by the ModalPopup, for instance, if when you click a button, a ModalPopup displays,…

This article discusses the ASP.NET AJAX ModalPopupExtender control. In this article we will show how to use the ModalPopupExtender control, how to display/show/call the ASP.NET AJAX ModalPopupExtender control from javascript, how to show/display/cal…