Web Wiz - Green Windows Web Hosting
Web Wiz Homepage Search Web Wiz

  New Posts New Posts RSS Feed - Help with SQL Query in Label Control
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

Help with SQL Query in Label Control
 Post Reply Post Reply Page  12>
Author
  Topic Search Topic Search  Topic Options Topic Options
Misty View Drop Down
Senior Member
Senior Member
Avatar

Joined: 06 February 2002
Location: United States
Status: Offline
Points: 711 		Post Options Post Options   Thanks (0) Thanks(0)   Quote Misty Quote  Post ReplyReply Direct Link To This Post Topic: Help with SQL Query in Label Control
    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
Back to Top
michael View Drop Down
Senior Member
Senior Member
Avatar

Joined: 08 April 2002
Location: United States
Status: Offline
Points: 4670 		Post Options Post Options   Thanks (0) Thanks(0)   Quote michael Quote  Post ReplyReply Direct Link To This Post 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.
Blog | MPG Tracker
Back to Top
Misty View Drop Down
Senior Member
Senior Member
Avatar

Joined: 06 February 2002
Location: United States
Status: Offline
Points: 711 		Post Options Post Options   Thanks (0) Thanks(0)   Quote Misty Quote  Post ReplyReply Direct Link To This Post 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 )

Edited by Misty - 26 July 2005 at 11:07pm
Back to Top
Mart View Drop Down
Senior Member
Senior Member
Avatar

Joined: 30 November 2002
Status: Offline
Points: 2304 		Post Options Post Options   Thanks (0) Thanks(0)   Quote Mart Quote  Post ReplyReply Direct Link To This Post 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
Back to Top
Misty View Drop Down
Senior Member
Senior Member
Avatar

Joined: 06 February 2002
Location: United States
Status: Offline
Points: 711 		Post Options Post Options   Thanks (0) Thanks(0)   Quote Misty Quote  Post ReplyReply Direct Link To This Post 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!
Back to Top
Mart View Drop Down
Senior Member
Senior Member
Avatar

Joined: 30 November 2002
Status: Offline
Points: 2304 		Post Options Post Options   Thanks (0) Thanks(0)   Quote Mart Quote  Post ReplyReply Direct Link To This Post 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
Back to Top
Misty View Drop Down
Senior Member
Senior Member
Avatar

Joined: 06 February 2002
Location: United States
Status: Offline
Points: 711 		Post Options Post Options   Thanks (0) Thanks(0)   Quote Misty Quote  Post ReplyReply Direct Link To This Post 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
Back to Top
Mart View Drop Down
Senior Member
Senior Member
Avatar

Joined: 30 November 2002
Status: Offline
Points: 2304 		Post Options Post Options   Thanks (0) Thanks(0)   Quote Mart Quote  Post ReplyReply Direct Link To This Post Posted: 28 July 2005 at 4:13am
Change that line to:

lblAmounts.Text = "Amount Owed $" & objcommand.ExecuteScalar().ToString()
Back to Top
 Post Reply Post Reply Page  12>

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 12.08
Copyright ©2001-2026 Web Wiz Ltd.

Become a Fan on Facebook Follow us on X Connect with us on LinkedIn Web Wiz Blogs
About Web Wiz | Contact Web Wiz | Terms & Conditions | Cookies | Privacy Notice

Web Wiz is the trading name of Web Wiz Ltd. Company registration No. 05977755. Registered in England and Wales.
Registered office: Web Wiz Ltd, Unit 18, The Glenmore Centre, Fancy Road, Poole, Dorset, BH12 4FB, UK.

Prices exclude VAT at 20% unless otherwise stated. VAT No. GB988999105 - $, € prices shown as a guideline only.

Copyright ©2001-2026 Web Wiz Ltd. All rights reserved.