TraciShultz
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
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
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
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
ASKER
How do I close this question if I answered it myself?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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