Click here to Skip to main content
15,867,568 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
eg i need to check if busid =5 already exist in the table or not
how do i extract data of all the busid field from the database to compare it with the new input i just entered for busid (so that busid is not repeated in the db)
Posted

Adaption of pdoxtrader's code

Public Function BusIdExists(ByVal busid As String) As Boolean
        Dim thisSql As String = "SELECT Count(*) FROM [yourtable] WHERE busid=@busid"
        Dim rowCount As Integer

        ' Get the list of rows with that busid
        Using conn As New SqlConnection("your sql connect string")
            conn.Open()

            Using comm As New SqlCommand(thisSql, conn)
                comm.Parameters.AddWithValue("@busid", busid)

                rowCount = Convert.ToInt32(comm.ExecuteScalar())
            End Using
        End Using

        Return (rowCount > 0)
    End Function


1) I skipped the DataTable and DataReader objects - no need to instanciate those for this
2) I changed the sql and added Count(*) and removed the database name (that's selfevident from the connection string)
3) I added the busid using a parameter instead of concatenating a string. That's generally better praxis to avoid sql injections in more complex cases
 
Share this answer
 
Comments
pdoxtader 17-Apr-13 10:48am    
Awesome. Thanks man!

- Pete
You could use this function:

VB
Public Function BusIdExists(ByVal busid As String) As Boolean
        Dim theTable As DataTable = New DataTable()
        Dim thisSql As String = ""

        ' Prepare our select statement
        thisSql = "SELECT [some_colume_name] FROM [" & yourdatabase & "].[yourtable] WHERE busid=" & busid
        
        ' Get the list of rows with that busid
        Using conn As New SqlConnection("your sql connect string")
            conn.Open()
            Using comm As New SqlCommand(thisSql, conn)
                Using reader As SqlDataReader = comm.ExecuteReader()
                    theTable.Load(reader)
                    conn.Close()
                End Using
            End Using
        End Using

        If theTable.Rows.Count > 0 then Return True

        Return False
    End Function


You would use it like this:

VB
if BusIdExists("5") then
   ' code to handle an existing record with the busid 5
else
   ' Code to handle discovering that busid 5 isn't in the database
End If
 
Share this answer
 
Comments
Johnny J. 17-Apr-13 10:21am    
You don't need to fire up a DataReader to do this. That would be poor practice. Just select Count(...) and cast the returnvalue from ExecuteScalar to an Integer...
pdoxtader 17-Apr-13 10:26am    
I knew there was a better way. Thanks for pointing that out. I'll keep that in mind the next time I need to do something like this.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900