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 placeSmile Doh!


Posted By: Misty
Date Posted: 28 July 2005 at 3:30pm
Thank you for noticing the typo! I forgot to remove the '. Smile



Print Page | Close Window

Forum Software by Web Wiz Forums® version 12.08 - https://www.webwizforums.com
Copyright ©2001-2026 Web Wiz Ltd. - https://www.webwiz.net