Link to home
Start Free TrialLog in
Avatar of TraciShultz
TraciShultzFlag for United States of America

asked on

Why would an SQL string work in Query analyzer but not if it is created and executed in Visual Basic code?

Why would an SQL string work in Query analyzer but not if it is created and executed in Visual Basic code?

The below string is created from VB code and executed in VB:
      ssql = "IF NOT EXISTS" & vbCrLf
      ssql = ssql & "(SELECT c.*" & vbCrLf
      ssql = ssql & "FROM syscolumns c" & vbCrLf
      ssql = ssql & "JOIN sysobjects o ON c.id = o.id" & vbCrLf
      ssql = ssql & "WHERE o.name = '" & RTrim(roTable.TableName) & "'" & vbCrLf
      ssql = ssql & "AND c.name = '" & RTrim(roTable.Field) & "')" & vbCrLf
      ssql = ssql & "BEGIN" & vbCrLf
      ssql = ssql & "-- Field does not exist" & vbCrLf
      ssql = ssql & "ALTER TABLE [dbo].[" & RTrim(roTable.TableName) & "] ADD [" & RTrim(roTable.Field) & "] " & RTrim(roTable.FieldType) & " NULL" & vbCrLf
      ssql = ssql & "Print 'Created'" & vbCrLf
      ssql = ssql & "IF  EXISTS" & vbCrLf
      ssql = ssql & "(SELECT c.*" & vbCrLf
      ssql = ssql & "FROM syscolumns c" & vbCrLf
      ssql = ssql & "JOIN sysobjects o ON c.id = o.id" & vbCrLf
      ssql = ssql & "WHERE o.name = '" & RTrim(roTable.TableName) & "'" & vbCrLf
      ssql = ssql & "AND c.name = '" & RTrim(roTable.Field) & "')" & vbCrLf
      ssql = ssql & "BEGIN" & vbCrLf
      ssql = ssql & "UPDATE [dbo].[" & RTrim(roTable.TableName) & "] SET [" & RTrim(roTable.Field) & "] = " & RTrim(roTable.FieldValue) & vbCrLf
      ssql = ssql & "Print 'Update'" & vbCrLf
      ssql = ssql & "End" & vbCrLf
      ssql = ssql & "End" & vbCrLf
      ssql = ssql & "Else" & vbCrLf
      ssql = ssql & "BEGIN" & vbCrLf
      ssql = ssql & "-- Field does exist" & vbCrLf
      ssql = ssql & "Print 'Field Exists'" & vbCrLf
      ssql = ssql & "End" & vbCrLf
***********************************************************************
The Above looks like:
IF NOT EXISTS
(SELECT c.*
FROM syscolumns c
JOIN sysobjects o ON c.id = o.id
WHERE o.name = 'RackInfo'
AND c.name = 'SealTite')
BEGIN
-- Field does not exist
ALTER TABLE [dbo].[RackInfo] ADD [SealTite] bit NULL
Print 'Created'
IF  EXISTS
(SELECT c.*
FROM syscolumns c
JOIN sysobjects o ON c.id = o.id
WHERE o.name = 'RackInfo'
AND c.name = 'SealTite')
BEGIN
UPDATE [dbo].[RackInfo] SET [SealTite] = 0
Print 'Update'
End
End
Else
BEGIN
-- Field does exist
Print 'Field Exists'
End

*****************************************
If executed from vb (ADO I get the following Error:
 "EXCEPTION: clsDataHandler, GetRecordSet  - Err = Invalid column name 'SealTite'."
If I execute the same string In Query Analyzer It works?

******************************************
Another strange behavior is if I execute the same string in vb but preform it on a different table it works?

Any help is greatly appreciated
Avatar of Jai S
Jai S
Flag of India image

immedietly seeing your code it appears you have not left any space between you key words...a sample below
ssql = "IF NOT EXISTS" & vbCrLf
      ssql = ssql & "(SELECT c.*" & vbCrLf
      ssql = ssql & "FROM syscolumns c" & vbCrLf
      ssql = ssql & "JOIN sysobjects o ON c.id = o.id" & vbCrLf

it should be
ssql = "IF NOT EXISTS" & vbCrLf
      ssql = ssql & "(SELECT c.* " & vbCrLf
      ssql = ssql & "FROM syscolumns c " & vbCrLf
      ssql = ssql & "JOIN sysobjects o ON c.id = o.id  " & vbCrLf
or
ssql = "IF NOT EXISTS" & vbCrLf
      ssql = ssql & "(SELECT c.*" & vbCrLf
      ssql = ssql & " FROM syscolumns c" & vbCrLf
      ssql = ssql & " JOIN sysobjects o ON c.id = o.id" & vbCrLf

Avatar of TraciShultz

ASKER

The solution is:
A table cannot be altered and then the new columns referenced in the same batch.
 so my sql statement had to be broken down into 3 steps.

Check if field exists
Create Field if needed
Update field
Thanks
How do I close this question if I answered it myself?
ASKER CERTIFIED SOLUTION
Avatar of Computer101
Computer101
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial