Link to home
Start Free TrialLog in
Avatar of Murray Brown
Murray BrownFlag for United Kingdom of Great Britain and Northern Ireland

asked on

VB.net Check if a column exists in a SQL Table

Hi
What VB.net code would I use to check if a column exists in a SQL table?
Thanks
Avatar of Pratik Makwana
Pratik Makwana
Flag of India image

This function is check if column is exist or not.....

Dim oField ' As ADODB.Field
      Dim oRecordset ' As ADODB.Recordset
      Dim oConn ' As ADODB.Connection
      Dim nameToCheck
      Dim nameExists
      
      ' The column name you're looking for
      nameToCheck = "ID"
      nameExists = false
      
      ' Create connection
      Set oConn = Server.CreateObject("ADODB.Connection")
      oConn.Open "YourConnectionString"
      
      Set oRecordset = oConn.Execute("SELECT * FROM YourTable")
      
      For Each oField In oRecordset.Fields
            If oField.Name = nameToCheck Then
                  nameExists = True
                  Exit For
            End If
      Next
      
      oRecordset.Close()
      oConn.Close()
      Set oConn = Nothing
      Set oRecordset = Nothing
      
      Response.Write("Field " & nameToCheck & " found: " & nameExists)
ASKER CERTIFIED SOLUTION
Avatar of Éric Moreau
Éric Moreau
Flag of Canada 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
Avatar of OriNetworks
OriNetworks

I think Eric has the best answer here where t.name is the table name you are checking and C.name is the column name you are checking for. If the column exists, it will return one row. If it does not exist, no rows will be returned.
    Public Shared Function ColumnExists(ByVal tableName As String,
                                        ByVal columnName As String,
                                        ByVal conn As SqlClient.SqlConnection) As Boolean
        Dim strSQL As String = "SELECT COUNT(*) FROM information_schema.columns " & _
                               "WHERE table_schema = 'dbo' " & _
                               "AND table_name = '" & tableName & "'" & _
                               "AND column_name = '" & columnName & "';"
        Using cmd As New SqlClient.SqlCommand(strSQL, conn)
            Return CBool(cmd.ExecuteScalar)
        End Using
    End Function

Open in new window

Dim bExists As Boolean
Using conn As New SqlClent.SqlConnection(YourConnectionStringHere)
    conn.Open()
    bExists = ColumnExists(YourTableName, YourColumnName, conn)
    conn.Close()
End Using

Open in new window

Avatar of Murray Brown

ASKER

Thanks