• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 203
  • Last Modified:

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
0
TraciShultz
Asked:
TraciShultz
  • 2
1 Solution
 
Jai STech ArchCommented:
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

0
 
TraciShultzAuthor Commented:
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
0
 
TraciShultzAuthor Commented:
How do I close this question if I answered it myself?
0
 
Computer101Commented:
PAQed with points refunded (500)

Computer101
EE Admin
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now