Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Pro VB Databases Advanced-level VB coding questions specific to using VB with databases. Beginning-level questions or issues not specific to database use will be redirected to other forums.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Pro VB Databases section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old November 29th, 2003, 02:50 PM
Registered User
 
Join Date: Nov 2003
Location: Ridgeland, MS, USA.
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default Using IF ELSE

Hello, I am trying to make this work in a VB6 app that calls to SQL2000. I get an error stating that the syntax is incorrect near the Keyword "If". Not sure what I am doing wrong. Here is the code;
                ", If (Column1- Column2) > 0 Begin" & _
                " Column3= Column1- Column2" & _
                " End" & _


The code is only part of the whole function, hence the space before If.
Thanks B


  #2 (permalink)  
Old November 29th, 2003, 04:24 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Post all the code that is generating the T/SQL statement(s). That comma before the IF is suspicious...

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
  #3 (permalink)  
Old December 1st, 2003, 03:07 PM
Registered User
 
Join Date: Nov 2003
Location: Ridgeland, MS, USA.
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Jeff here is the beginning to end of the transaction.

mrsTable.Close
                Set mrsTable = Nothing
                mcnTable.BeginTrans
                mcmdTable.ActiveConnection = mcnTable
                mcmdTable.CommandType = adCmdText
                strSQL = "DELETE FROM Table WHERE TableNumber =" & (somebox.Text) & "AND " & _
                "TableValue <> 'ACTIVE'"
                mcmdTable.CommandText = strSQL
                mcmdTable.Execute
                mcnTable.CommitTrans
                mcnTable.BeginTrans
                strSQL = "UPDATE table SET value1 = valu1+ value2" & _
                ", value4 = value4 + value5" & _
                ", column1= col1- col2- col3" & _
                ", db1= Round(((db2/ db3) * 100),0)" & _
                ", per1= Round(((cost1/ (cost2+ cost3)) * 100),0)" & _
                ", money= Round(Round(money2 * money3,2),0)" & _
                ", money5= money6 - money7" & _
                ", If (money1 - money2) > 0 Begin" & _
                " cost1 = cost2 - cost3" & _
                " End" & _
                ", If (money1 - money2) < 0 Begin" & _
                " money8 = Abs(money1 - money5)" & _
                " End" & _
                ", valueF = valueR - valuQ" & _
                ", value1 = value2 - value3" & _
                ", moneyA = moneyB - moneyC" & _
                ", moneyA = Round(((moneyH / moneyF) * 100),0)" & _
                ", moneyZ = moneyH" & _
                ", moneyZ = moneyT - moneyG" & _
                ", moneyZ = Round(((moneyZ / moneyQ) * 100),0)" & _
                "WHERE somename=" & (somebox.Text)

                mcmdTable.CommandText = strSQL
                mcmdTable.Execute

                mcnTable.CommitTrans


Thanks B

  #4 (permalink)  
Old December 1st, 2003, 03:23 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Sparks, NV, USA.
Posts: 101
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to jlick
Default

IF, ELSE blocks are used between statements. You are trying to use it in a statement. You should use a case statement.

From SQL Books Online:

CASE
Evaluates a list of conditions and returns one of multiple possible result expressions.

CASE has two formats:

The simple CASE function compares an expression to a set of simple expressions to determine the result.


The searched CASE function evaluates a set of Boolean expressions to determine the result.
Both formats support an optional ELSE argument.

Syntax
Simple CASE function:

CASE input_expression
    WHEN when_expression THEN result_expression
        [ ...n ]
    [
        ELSE else_result_expression
    ]
END

Searched CASE function:

CASE
    WHEN Boolean_expression THEN result_expression
        [ ...n ]
    [
        ELSE else_result_expression
    ]
END

Arguments
input_expression

Is the expression evaluated when using the simple CASE format. input_expression is any valid Microsoft® SQL Server™ expression.

WHEN when_expression

Is a simple expression to which input_expression is compared when using the simple CASE format. when_expression is any valid SQL Server expression. The data types of input_expression and each when_expression must be the same or must be an implicit conversion.

n

Is a placeholder indicating that multiple WHEN when_expression THEN result_expression clauses, or multiple WHEN Boolean_expression THEN result_expression clauses can be used.

THEN result_expression

Is the expression returned when input_expression equals when_expression evaluates to TRUE, or Boolean_expression evaluates to TRUE. result expression is any valid SQL Server expression.

ELSE else_result_expression

Is the expression returned if no comparison operation evaluates to TRUE. If this argument is omitted and no comparison operation evaluates to TRUE, CASE returns NULL. else_result_expression is any valid SQL Server expression. The data types of else_result_expression and any result_expression must be the same or must be an implicit conversion.

WHEN Boolean_expression

Is the Boolean expression evaluated when using the searched CASE format. Boolean_expression is any valid Boolean expression.

...

Examples
SELECT Category =
      CASE type
         WHEN 'popular_comp' THEN 'Popular Computing'
         WHEN 'mod_cook' THEN 'Modern Cooking'
         WHEN 'business' THEN 'Business'
         WHEN 'psychology' THEN 'Psychology'
         WHEN 'trad_cook' THEN 'Traditional Cooking'
         ELSE 'Not yet categorized'
      END,
   CAST(title AS varchar(25)) AS 'Shortened Title',
   price AS Price
FROM titles
WHERE price IS NOT NULL
ORDER BY type, price
COMPUTE AVG(price) BY type
GO

...


SELECT 'Price Category' =
      CASE
         WHEN price IS NULL THEN 'Not yet priced'
         WHEN price < 10 THEN 'Very Reasonable Title'
         WHEN price >= 10 and price < 20 THEN 'Coffee Table Title'
         ELSE 'Expensive book!'
      END,
   CAST(title AS varchar(20)) AS 'Shortened Title'
FROM titles
ORDER BY price


John R Lick
JohnRLick@hotmail.com






Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.