ASP.NET Discussion - Help with SQL Query in Label Control

Print Page | Close Window

Help with SQL Query in Label Control

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: ASP.NET Discussion
Forum Description: Discussion and chat on ASP.NET related topics.
URL: https://forums.webwiz.net/forum_posts.asp?TID=15992
Printed Date: 28 March 2026 at 3:40am
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: Help with SQL Query in Label Control

Posted By: Misty
Subject: Help with SQL Query in Label Control
Date Posted: 26 July 2005 at 6:34pm

I need help with fixing the sql query below. I tested the sql query in Query Analyzer and it worked. I used a number for the Client ID of course. I am using it for a label control on an ASP.net web page. I am getting the following error message: Compiler Error Message: BC30648: String constants must end with a double quote.

Source Error:

Line 222:    
Line 223:  
Line 224:    objCommand = New SqlCommand( "SELECT SumOfPaymentOwed = CASE WHEN ISNULL(Sum(Amounts.PaymentOwed),0) = 0 THEN 0
Line 225:     ELSE CONVERT(DECIMAL(10,2),Sum(Amounts.PaymentOwed))
Line 226:    END

Here's my code:

Sub AmountOwed()
    Dim strConnect As String
    Dim objConnect As SqlConnection
    Dim objCommand As SqlCommand
    Dim strClientID As String

    'Get connection string from Web.Config
    strConnect = ConfigurationSettings.AppSettings("ConnectionString")
    objConnect = New SqlConnection(strConnect)

objConnect.Open()

'Get incoming Client ID
strClientID = request.params("qryID")

    objCommand = New SqlCommand( "SELECT SumOfPaymentOwed = CASE WHEN ISNULL(Sum(Amounts.PaymentOwed),0) = 0 THEN 0
     ELSE CONVERT(DECIMAL(10,2),Sum(Amounts.PaymentOwed))
    END
FROM Amounts WHERE PaidDate is Null AND ClientID =" & strClientID , objConnect )

'Display Payment owed
lblAmounts.Text = "Amount Owed $" & objcommand.ExecuteScalar()

    objConnect.Close()
End Sub

Replies:

Posted By: michael
Date Posted: 26 July 2005 at 9:43pm

strClientID is a String. Try it like this:

objCommand = New SqlCommand( String.Format("SELECT SumOfPaymentOwed = CASE WHEN ISNULL(Sum(Amounts.PaymentOwed),0) = 0 THEN 0
ELSE CONVERT(DECIMAL(10,2),Sum(Amounts.PaymentOwed))
END
FROM Amounts WHERE PaidDate is Null AND ClientID = '{0}'", strClientID) , objConnect )

If the client ID is an Integer, you should define it as such.

-------------
http://baumannphoto.com" rel="nofollow - Blog | http://mpgtracker.com" rel="nofollow - MPG Tracker

Posted By: Misty
Date Posted: 26 July 2005 at 10:42pm

This didn't work. I still got the following error message:

Compiler Error Message: BC30648: String constants must end with a double quote.

Source Error:

Line 222:    
Line 223:  
Line 224:    objCommand = New SqlCommand( String.Format("SELECT SumOfPaymentOwed = CASE WHEN ISNULL(Sum(Amounts.PaymentOwed),0) = 0 THEN 0
Line 225:     ELSE CONVERT(DECIMAL(10,2),Sum(Amounts.PaymentOwed))

I solved this problem by using: objCommand = New SqlCommand( "SELECT SumOfPaymentOwed = CASE WHEN ISNULL(Sum(Amounts.PaymentOwed),0) = 0 THEN 0 ELSE CONVERT(DECIMAL(10,2),Sum(Amounts.PaymentOwed)) END FROM Amounts WHERE PaidDate is Null AND ClientID = '" & strClientID & "'" , objConnect )

Posted By: Mart
Date Posted: 27 July 2005 at 4:19am

You can't use multiline strings like that, if you really want the string to be multiline you can use:

objCommand = New SqlCommand( "SELECT SumOfPaymentOwed" & _ "= CASE WHEN ISNULL(Sum(Amounts.PaymentOwed),0) = 0" & _ "THEN 0" & _
"ELSE CONVERT(DECIMAL(10,2),Sum(Amounts.PaymentOwed))" &_
"END" &_
"FROM Amounts WHERE PaidDate is Null AND ClientID =" & strClientID , objConnect )

But it would be much cleaner if that made that into a stored procecdure, then a sql injection wouldn't be possible aswell

Posted By: Misty
Date Posted: 27 July 2005 at 12:29pm

Mart,

I have some experience with creating stored procedures, but not this kind of stored procedure. Could you please show me how you would write the above sql query in a stored procedure? Thanks!

Posted By: Mart
Date Posted: 27 July 2005 at 1:20pm

Sure, in query analyzer you would execute:

CREATE PROCEDURE [name]
(
@ClientId int
)
AS

SELECT SumOfPaymentOwed = CASE WHEN ISNULL(Sum(Amounts.PaymentOwed),0) = 0 THEN 0 CONVERT(DECIMAL(10,2),Sum(Amounts.PaymentOwed))
END FROM Amounts WHERE PaidDate is Null AND ClientID = @ClientId

then you would use something like:

Dim command As New SqlCommand()

command.Connection = connection
command.CommandType = CommandType.StoredProcedure
command.CommandText = "StoredProcedureName"

command.Parameters.Add("@ClientId", CInt(strClientId))

Dim reader As SqlDataReader = command.ExecuteReader
-- or do whatever you want with the command object

Posted By: Misty
Date Posted: 28 July 2005 at 2:36am

I executed the stored procedure, AmountsOwed. I had to modify your code a little bit to make it work. However, I'm getting the following error message on the ASP.net page: Compiler Error Message: BC30452: Operator '&' is not defined for types 'String' and 'System.Data.SqlClient.SqlDataReader'.

Source Error:

Line 232:
Line 233:    'Display Payment owed
Line 234:    lblAmounts.Text = "Amount Owed $" & objcommand.ExecuteReader()

. I am very interested in using more complicated stored procedures.

Here's my code:

Sub AmountOwed()
    Dim strConnect As String
    Dim objConnect As SqlConnection
    Dim objCommand As New SqlCommand
    Dim strClientID As String

    'Get connection string from Web.Config
    strConnect = ConfigurationSettings.AppSettings("ConnectionString")
    objConnect = New SqlConnection(strConnect)

'objConnect.Open()

'Get incoming Client ID
strClientID = request.params("qryID")

    objCommand.Connection = objConnect
    objCommand.CommandType = CommandType.StoredProcedure
    objCommand.CommandText = "AmountsOwed"

objCommand.Parameters.Add("@ClientId", CInt(strClientId))

'objCommand = New SqlCommand( "SELECT SumOfPaymentOwed = CASE WHEN ISNULL(Sum(Amounts.PaymentOwed),0) = 0 THEN 0 ELSE CONVERT(DECIMAL(10,2),Sum(Amounts.PaymentOwed)) END FROM Amounts WHERE PaidDate is Null AND ClientID = '" & strClientID & "'" , objConnect )

'Display Payment owed
lblAmounts.Text = "Amount Owed $" & objcommand.ExecuteReader()

    objConnect.Close()
End Sub

Posted By: Mart
Date Posted: 28 July 2005 at 4:13am

Change that line to:

lblAmounts.Text = "Amount Owed $" & objcommand.ExecuteScalar().ToString()

Posted By: Misty
Date Posted: 28 July 2005 at 2:45pm

I changed it. I am getting the following error message now: Exception Details: System.InvalidOperationException: ExecuteReader requires an open and available Connection. The connection's current state is Closed.

Source Error:

Line 232:
Line 233:    'Display Payment owed
Line 234:    lblAmounts.Text = "Amount Owed $" & objcommand.ExecuteScalar().ToString()

I don't understand why. Please look at my code in my last posting. I open the connectionString and close it after this line of code.

Posted By: Mart
Date Posted: 28 July 2005 at 3:17pm

Probably because you commented out the line that opened it in the first place Smile

Doh!

Posted By: Misty
Date Posted: 28 July 2005 at 3:30pm

Thank you for noticing the typo! I forgot to remove the '.