How to know if the table column is unique

[Eniekko]

Hello,

currently, what i want to do is, add a column in a table "default" during runtime. Well, I've got that covered already.

The problem is, the column names are actually taken from the users input through a textbox or drop down list and of course, if the same column is added already in the table, it generates an error since it has to be unique.

That is why i wonder if it is possible to know if a column already exists without viewing the form which contains the grid view of that table?

Thanks if you can help :$

[thines01]

What type of database?
Does it allow the DESCRIBE or DESC command to be used on a table?

[Eniekko]

uhm, i'm using sql server as database and i haven't tried using desc
please help...

[thines01]

Some databases have a DESCRIBE method that tells the columns of a table.
SQL Server does not.
You can, however, retrieve the columns of a table by querying the COLUMNS table under the owner INFORMATION_SCHEMA under your given database.

Let's say you have a database named E911 and a table named JipAreaCode.

SELECT
   COLUMN_NAME,
   ORDINAL_POSITION 
FROM
   E911.INFORMATION_SCHEMA.COLUMNS 
WHERE
   TABLE_NAME='JipAreaCode'

Assuming you're comfortable with turning SQL Queries into VB Code, you can set up a query to check the columns and positions and compare that to what the user is trying to input.

[Reverend Jim]

Here is another method that uses the NorthWind database as an example. Probably not the best solution but it IS "a" solution.

Dim con As New ADODB.Connection
con.Open("Driver={SQL Server};Server=.\SQLEXPRESS;Database=NorthWind;Trusted_Connection=yes;")

Dim rec As New ADODB.Recordset
rec.Open("select top 1 * from Categories", con, CursorTypeEnum.adOpenForwardOnly)

For i As Integer = 0 To rec.Fields.Count - 1
    MsgBox(rec.Fields(i).Name)
Next

rec.Close()
con.Close()

Each iteration through the loop displays the name of the next field.

[poojavb]

Hello,

currently, what i want to do is, add a column in a table "default" during runtime. Well, I've got that covered already.

The problem is, the column names are actually taken from the users input through a textbox or drop down list and of course, if the same column is added already in the table, it generates an error since it has to be unique.

That is why i wonder if it is possible to know if a column already exists without viewing the form which contains the grid view of that table?

Thanks if you can help :$

What u can do is....

before inserting the value into database u need to check if the value exists in the table
using the select query

"select colname from tablename where colname="'+textbox1.text+"'"

If the reader returns a true value then u shid not save the value in the database instead show a msg box that value already exists...

else save the value in database....

hope it helps u...

for eg...

Try
             Dim myCommand As SqlCommand
            myCommand = New SqlCommand("select  colname from tablename where colname='" + Textbox1.Text + "'", Connection)
            Dim reader As SqlDataReader= myCommand.ExecuteReader
            If reader.Read = True Then
              MsgBox("Input already exists")
            Else
               'Save query goes here
            End If
            reader.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

u can also add the distinct keyword in the select query.... and also u can code for ignore case

[Eniekko]

wow thank you for all the replies.. i'll try all of your suggestions... thank you thank you so much :)