Link to home
Start Free TrialLog in
Avatar of nduguyettu
nduguyettu

asked on

Display Query results into a Textbox

I have a SelectedIndexChanged procedure in which I have an SQL statement. I need the results from the SQL statement to be displayed in a text box.

The SQL statement is given below

SQL = "select max(TotalPayable) - max(TotalPaid) from Payments Group by StudentId having StudentId = '" & Trim(cboStudentId.SelectedItem.Value) & "'"

I have a textbox OpeningBalance in which I want the results from the above SQL statement to be displayed. How do I do it?

Nduguyettu
Avatar of Espavo
Espavo
Flag of South Africa image

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()
Avatar of tillgeffken
tillgeffken

1. Don't trust data posted by a form (SQL Injections). Always use SQL parameters.

2. Try the following:

Dim SQLStr As String = "SELECT (MAX(TotalPayable) - MAX(TotalPaid)) AS Balance FROM Payments GROUB BY StudentId HAVING StudentId = @StudentID"
SQLStr.Parameters.Add("@StudentID", SqlDbType.Int).Value = "toasters" 'Put in your SqlDbtype (possibly SqlDbType.Int or SqlDbType.VarChar)
Dim SQLConn As New SqlConnection()
Dim SQLCmd As New SqlCommand()
Dim SQLdr As SqlDataReader
 
SQLConn.ConnectionString = ConnString
SQLConn.Open
SQLCmd.Connection = SQLConn
SQLCmd.CommandText = SQLStr
SQLdr = SQLCmd.ExecuteReader
While dr.Read()
  OpeningBalance.Text = dr.("Balance").ToString()
End While
Loop While SQLdr.NextResult() 'Move to the Next Record
SQLdr.Close 'Close the SQLDataReader 

Open in new window

args. forgot the ddlValue.. You might want to try/catch the Convert.ToInt

Dim SQLStr As String = "SELECT (MAX(TotalPayable) - MAX(TotalPaid)) AS Balance FROM Payments GROUB BY StudentId HAVING StudentId = @StudentID"
SQLStr.Parameters.Add("@StudentID", SqlDbType.Int).Value = Convert.ToInt(Trim(cboStudentId.SelectedItem.Value)) 'Put in your SqlDbtype (possibly SqlDbType.Int or SqlDbType.VarChar)
Dim SQLConn As New SqlConnection()
Dim SQLCmd As New SqlCommand()
Dim SQLdr As SqlDataReader
 
SQLConn.ConnectionString = ConnString
SQLConn.Open
SQLCmd.Connection = SQLConn
SQLCmd.CommandText = SQLStr
SQLdr = SQLCmd.ExecuteReader
While dr.Read()
  OpeningBalance.Text = dr.("Balance").ToString()
End While
Loop While SQLdr.NextResult() 'Move to the Next Record
SQLdr.Close 'Close the SQLDataReader 

Open in new window

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()
I hope the attached code helps. As tillgeffken suggested above, use of parameters is always recommended and to make it more secure from SQL injections, use stored procedures.

'Write this on top of the code file
Imports System.Data.SqlClient
 
'Write this function within your page's code file
Private Function GetOpeningBalance(ByVal studentId As String) As Double
 
	Dim strConnectionString As String = "Server=SQL_SERVER_MACHINE_NAME_OR_IP;Database=DB_NAME;UID=DB_USER_NAME;PWD=DB_PASSWORD"
	Dim strQuery As String = "SELECT MAX(TotalPayable) - MAX(TotalPaid) AS OpeningBalance FROM Payments GROUP BY StudentId HAVING StudentId = '" & studentId & "'"
 
	Dim cnnTemp As New SqlConnection(strConnectionString)
	Dim cmdTemp As New SqlCommand(strQuery, cnnTemp)
 
	Try
		cnnTemp.Open()
		Return CDbl(cmdTemp.ExecuteScalar())
 
	Catch ex As Exception
		Throw
 
	Finally
		cmdTemp.Dispose()
		cmdTemp = Nothing
		cnnTemp.Dispose()
		cnnTemp = Nothing
 
	End Try
 
End Function
 
'Call the above functions as follows 
TextBox1.Text = GetOpeningBalance(cboStudentId.SelectedItem.Value.Trim()).ToString()

Open in new window

Avatar of nduguyettu

ASKER

Thanks all

Actually I ve tried all methods but failed to get a working solution.

Espavo may you get me more information about ExecuteScalar. The one I tried returned a blank. I am attaching the code.

Moghazali, I have also tried using the function as advised but it also returned a blank. I have likewise attached the code.

Tillgeffeken, when I tried your option I got an error at SQLstr.Parameters - 'Parameters' is not a member of 'String'.

May I get more solutions.

Nduguyettu
Private Sub aSemester_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles aSemester.SelectedIndexChanged
 
 
        Dim SQL As String = "select (max(TotalPayable)-max(TotalPaid)) As OpeningBalance from Payments Group by StudentId having StudentId='" & Trim(aStudentId.SelectedItem.Value) & "'"
        Dim con As New SqlConnection
        con.ConnectionString = conStr
        Dim cmd As New SqlCommand
 
        con.Open()
        cmd.Connection = con
        cmd.CommandText = SQL
 
        aOpeningBalance.Text = Integer.Parse(cmd.ExecuteScalar.ToString)
        con.Close()
 
End Sub
 
 
Private Sub aSemester_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles aSemester.SelectedIndexChanged
 
        aOpeningBalance.Text = GetOpeningBalance(aStudentId.SelectedItem.Value.Trim()).ToString
 
End Sub
    
Private Function GetOpeningBalance(ByVal StudentId As String) As Double
 
        Dim conStr As String = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Ndejje;Data Source=localhost"
        Dim SQL As String = "select max(TotalPayable)-max(TotalPaid) As OpeningBalance from Payments Group by StudentId having StudentId='" & Trim(aStudentId.SelectedItem.Value) & "'"
 
        Dim cnnTemp As New SqlConnection(conStr)
        Dim cmdTemp As New SqlCommand(SQL, cnnTemp)
 
        Try
            cnnTemp.Open()
            Return CDbl(cmdTemp.ExecuteScalar)
        Catch ex As Exception
            Throw
        Finally
            cmdTemp.Dispose()
            cmdTemp = Nothing
            cnnTemp.Dispose()
            cnnTemp = Nothing
        End Try
End Function

Open in new window

Here's how I would typically do it:
        Dim con As New SqlConnection
        con.ConnectionString = conStr
        Dim cmd As New SqlCommand("SELECT (max(TotalPayable)-max(TotalPaid)) As OpeningBalance FROM Payments WHERE StudentId='" & Trim(aStudentId.SelectedItem.Value) & "'", con)
        con.Open()
        aOpeningBalance.Text = cmd.ExecuteScalar().ToString
        con.Close()

Open in new window

Hello Espavo,

The information is not displayed in the textbox. Any more advice?

Nduguyettu
If you run the Query directly in SQL does it give you an answer?
 
How should I run it directly in SQL?

Nduguyettu
Could be that there is no data to display...
What is your datatype here:
StudentId='" & Trim(aStudentId.SelectedItem.Value) & "'
From within SQL Server Management Studio...

(I seem to think it can even be done from within VS)
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 
Dear all,

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

Open in new window

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:
(max(TotalPayable)-max(TotalPaid)) As OpeningBalance
Surely this should be:
(sum(TotalPayable)-sum(TotalPaid)) As OpeningBalance  
I ve tried to step through the code but it just runs through without stopping anywhere.

I have changed to sum from max but the effect is the same.

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))

Dim con As New SqlConnection
        con.ConnectionString = conStr
        Dim cmd As New SqlCommand("SELECT (max(TotalPayable)-max(TotalPaid)) As OpeningBalance 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()

Open in new window

As I told you earlier, I ve inserted break-points but to no effect. Is it possible that the procedure am using is the one failing? Do you think using OnBlur/OnFocus an help?

If you've inserted break-points, and there is no effect, then you aren't triggering your sub-routine:
Private Sub aSemester_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles aSemester.SelectedIndexChanged
End Sub
Does aSemester have AutoPostBack set to True?
Actually aSemester's AutoPostBack had not been set to True. I have done that and run the system. I returned the following Exception:

Exception Details: System.InvalidCastException: Cast from type 'DBNull' to type 'Double' is not valid.

I am trying to find a solution but if you have a quick one please assist.

This is progress...
Try this:

dim OpeningBalance as double = 99999
try : cmd.ExecuteScalar() : catch : end try

Open in new window

The Value 9999 is now displayed on the screen as you stated earlier. May I now know how the value will be displayed into the TextBox?

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...
 
Hello Espavo,

Another probolem has come up that is disturbing me. I ve been using the following connection string without any problem:

conStr = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Ndejje;Data Source=localhost"

When I apply it in your example, I get the following error:

Keyword not supported: 'provider'

When I change the connection string to:

con.ConnectionString = "Server=localhost;Database=Ndejje;UID="";PWD="""

I get the following error:

Login failed for user ';PWD='

When I check the SQL Database, I see USER - ASPNET  With LOGIN NAME - MUKAAGA\ASPNET, and USER - IUSR_MUKAAGA with LOGIN NAME - MUKAAGA\IUSR_MUKAAGA. MJUKAAGA is the Computer Name.

The database has no Password.

When I use any of the above User Names of Login Names, I still get a Login Failed Exception.

Where am I going wrong?



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 am working with Visual Studio 2003, ASP.NET 1.1 and SQL Server 2000

Great, then what I've given you here should work fine...
BTW, do you have MS Enterprise Manager available to access your DB?
I have managed to create a User Login and Password but I ve realised that the SQLCommand may have a problem because even when I change the table name I don't get any difference in the output.
What happens if you step-through the code?
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 

Open in new window

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...
Just pray for me not to run mad.

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()
 
 
			

Open in new window

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 suspect that the issue is due to the way that you're inputting the StudentID...

What is the DataType for StudentId?
StudentId is of Data Type Char of length 12

The result which I get from the Query is given here below:

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/000        510000      0      510000
ASKER CERTIFIED SOLUTION
Avatar of Espavo
Espavo
Flag of South Africa image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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