[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How do I convert a SQL IF statement to a string?

Posted on 2014-08-02
7
Medium Priority
?
118 Views
Last Modified: 2014-08-02
I run this query in SQL and it works great.  It is to add a column if it doesn't exist in the existing table.  

IF NOT EXISTS (SELECT *                
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tblInventory' AND COLUMN_NAME = 'Image')
ALTER TABLE tblInventory ADD image IMAGE

I want to add it to my application code and I can't seem to get it to work.  Here is one of the MANY variations I have been trying:

Dim strTableAlter As String

Dim myConnT1 As SqlConnection = New SqlConnection("Server=" & machine & "\" & Inst & ";Trusted_Connection=True;")


strTableAlter = "USE [AddBook]" & _
                        "IF NOT EXISTS (SELECT *" & _
                        "FROM INFORMATION_SCHEMA.COLUMNS" & _
                        "WHERE [tblInventory] AND [IMAGE])" & _
                        "ALTER TABLE [tblInventory] ADD [image] [IMAGE]"


 Dim myCommandT2 As SqlCommand = New SqlCommand(strTableAlter, myConnT1)

Try
  myConnT1.Open()
  myCommandT2.ExecuteNonQuery()
  MsgBox("Your Household Inventory Table has been updated to the latest version.", MsgBoxStyle.OkOnly, "Table   Upgrade")

Catch ex As Exception
MessageBox.Show(ex.Message, My.Application.Info.Title, MessageBoxButtons.OK, MessageBoxIcon.Information)
End Try

Any help is appreciated.
Thanks!!
0
Comment
Question by:Karen Wilson
  • 4
  • 3
7 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40236336
You are missing semi-colons.  Try it this way:
strTableAlter = "USE [AddBook]; " & _
                        "IF NOT EXISTS (SELECT *" & _
                        "FROM INFORMATION_SCHEMA.COLUMNS" & _
                        "WHERE [tblInventory] AND [IMAGE]); " & _
                        "ALTER TABLE [tblInventory] ADD [image] [IMAGE]"
0
 

Author Comment

by:Karen Wilson
ID: 40236338
I get an incorrect syntax near the keyword 'AND'  

This section has been causing the headache!

 "WHERE [tblInventory] AND [IMAGE]); " & _
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 40236350
Sorry it should be:
strTableAlter = "USE [AddBook]; " & _
         "IF NOT EXISTS (SELECT * " & _
          "FROM INFORMATION_SCHEMA.COLUMNS " & _
          "WHERE TABLE_NAME = 'tblInventory' AND COLUMN_NAME = 'Image'); " & _
                  "ALTER TABLE [tblInventory] ADD [image] [IMAGE]"

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:Karen Wilson
ID: 40236369
strTableAlter = "USE [AddBook]; " & _
                    "IF NOT EXISTS (SELECT * " & _
                    "FROM INFORMATION_SCHEMA.COLUMNS " & _
                    "WHERE TABLE_NAME = 'tblInventory' AND COLUMN_NAME = 'Image') " & _
                    "ALTER TABLE [tblInventory] ADD [image] [IMAGE]"

I removed the ; after 'Image' and it worked perfectly.  Thanks!  I was going crazy....  :O)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40236388
Glad you got it working.  Incidentally the image data type is deprecated and you really should replace it.
0
 

Author Comment

by:Karen Wilson
ID: 40236393
What do you mean... I just started playing with Image.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40236485
Read up in the SQL Server Help for the image data type:
ntext, text, and image (Transact-SQL)
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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question