Solved

Display Query results into a Textbox

Posted on 2009-05-12
39
463 Views
Last Modified: 2012-05-06
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
0
Comment
Question by:nduguyettu
  • 19
  • 16
  • 2
  • +1
39 Comments
 
LVL 10

Expert Comment

by:Espavo
ID: 24361917
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()
0
 
LVL 9

Expert Comment

by:tillgeffken
ID: 24361962
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

0
 
LVL 9

Expert Comment

by:tillgeffken
ID: 24361984
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

0
 
LVL 10

Expert Comment

by:Espavo
ID: 24361993
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()
0
 
LVL 11

Expert Comment

by:Muhammad Ousama Ghazali
ID: 24362010
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

0
 

Author Comment

by:nduguyettu
ID: 24362541
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

0
 
LVL 10

Expert Comment

by:Espavo
ID: 24362701
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

0
 

Author Comment

by:nduguyettu
ID: 24363161
Hello Espavo,

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

Nduguyettu
0
 
LVL 10

Expert Comment

by:Espavo
ID: 24363227
If you run the Query directly in SQL does it give you an answer?
 
0
 

Author Comment

by:nduguyettu
ID: 24363244
How should I run it directly in SQL?

Nduguyettu
0
 
LVL 10

Expert Comment

by:Espavo
ID: 24363266
Could be that there is no data to display...
What is your datatype here:
StudentId='" & Trim(aStudentId.SelectedItem.Value) & "'
0
 
LVL 10

Expert Comment

by:Espavo
ID: 24363273
From within SQL Server Management Studio...

(I seem to think it can even be done from within VS)
0
 
LVL 11

Expert Comment

by:Muhammad Ousama Ghazali
ID: 24363283
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
0
 

Author Comment

by:nduguyettu
ID: 24363365
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

0
 
LVL 10

Expert Comment

by:Espavo
ID: 24363449
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  
0
 

Author Comment

by:nduguyettu
ID: 24363491
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.

0
 
LVL 10

Expert Comment

by:Espavo
ID: 24363671
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

0
 

Author Comment

by:nduguyettu
ID: 24363803
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?

0
 
LVL 10

Expert Comment

by:Espavo
ID: 24364354
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?
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:nduguyettu
ID: 24364813
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.

0
 
LVL 10

Expert Comment

by:Espavo
ID: 24364984
This is progress...
Try this:

dim OpeningBalance as double = 99999

try : cmd.ExecuteScalar() : catch : end try

Open in new window

0
 

Author Comment

by:nduguyettu
ID: 24365784
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?

0
 
LVL 10

Expert Comment

by:Espavo
ID: 24369466
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...
 
0
 

Author Comment

by:nduguyettu
ID: 24373088
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?



0
 
LVL 10

Expert Comment

by:Espavo
ID: 24373360
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" />
 
0
 

Author Comment

by:nduguyettu
ID: 24373396
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?
0
 
LVL 10

Expert Comment

by:Espavo
ID: 24373485
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?
0
 

Author Comment

by:nduguyettu
ID: 24373522
I am working with Visual Studio 2003, ASP.NET 1.1 and SQL Server 2000

0
 
LVL 10

Expert Comment

by:Espavo
ID: 24373554
Great, then what I've given you here should work fine...
BTW, do you have MS Enterprise Manager available to access your DB?
0
 

Author Comment

by:nduguyettu
ID: 24374023
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.
0
 
LVL 10

Expert Comment

by:Espavo
ID: 24374057
What happens if you step-through the code?
0
 

Author Comment

by:nduguyettu
ID: 24375112
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

0
 
LVL 10

Expert Comment

by:Espavo
ID: 24375331
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...
0
 

Author Comment

by:nduguyettu
ID: 24375862
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

0
 
LVL 10

Expert Comment

by:Espavo
ID: 24376638
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...
 
0
 
LVL 10

Expert Comment

by:Espavo
ID: 24376650
I suspect that the issue is due to the way that you're inputting the StudentID...

What is the DataType for StudentId?
0
 

Author Comment

by:nduguyettu
ID: 24376949
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
0
 
LVL 10

Accepted Solution

by:
Espavo earned 500 total points
ID: 24377631
With the query above you are not pulling a SPECIFIC record... (You're pulling all the records)

Have you tried the query I asked you try earlier? (We need to try pull ONE record)
0
 

Author Closing Comment

by:nduguyettu
ID: 31580427
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 Gives IT Their Time Back

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!

Join & Write a Comment

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…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now