Help with checking entry in SQL database using vb.net

Question

1.00/5 (1 vote)

See more:

Help with checking existence of entry in SQL db using vb.net

I am trying to write a subroutine in vb.net to check an SQL database to see if a record exists in table 1, and if found, go to label 1, otherwise check in table 2, and if found, go to Label 2. If not found in either table 1 or table 2 then go to Label 3. What I have at the moment always goes to the Not Found scenario (Label 3), even though the item is present in at least one of the two tables. Evidently I have a logic problem in what I have written, but I can't figure out what is wrong. Can somebody help solve my dilemma please? It will be greatly appreciated.

This is the troublesome part of the subroutine I have at the moment:

Dim exists As Boolean

sql = "SELECT ASSETTAG from Assets where ASSETTAG ='" & TextBox1.Text & "',"
DBConnect(sql)
While reader.Read
If exists = False Then
GoTo CheckALL_ASSETS
Else
GoTo FoundAssets
End If
DBClose()
End While

CheckALL_ASSETS:
sql = "SELECT ASSETTAG from ALL_ASSETS where ASSETTAG ='" & TextBox1.Text & "',"
DBConnect(sql)
While reader.Read
If exists = False Then
GoTo NotFound
Else
GoTo LookupAllAssets
End If
DBClose()
End While

Posted 20-Jun-14 16:48pm

Member 10503959

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Peter Leow · Answer 1 · 2014-06-20T17:57:00

Solution 1

You can use COUNT(*) in sql to return record count to determine the existence of record. Better to trim the textbox input too. You should also use parameterized query to prevent sql injection. Refer: SqlCommand.Parameters[^]

Dim count As Int16 = 0

Dim commandText As String = _
     "SELECT COUNT(*)  from Assets   where ASSETTAG = @para" 

Using connection As New SqlConnection(connectionString)
        Dim command As New SqlCommand(commandText, connection)

        command.Parameters.AddWithValue("@para", Trim(TextBox1.Text) )

count = command.ExecuteScalar  
   
End Using 

If count = 0 Then
GoTo CheckALL_ASSETS
Else
GoTo FoundAssets
End If

Posted 20-Jun-14 17:57pm

Peter Leow

Updated 20-Jun-14 18:05pm

v3

Comments

Member 10503959 22-Jun-14 19:35pm

Thank you for your suggestion Peter, however, I tried it and get the following error message when I run the code: "ExecuteScalar requires an open and available connection. The connection's current state is closed." It looks to me like it hasn't opened the SQL connection from that message, but I am not very good with VB.net yet.

I Have included the entire relevant code here to help analyse why:

Public Class EHC
Private Const CB_FINDSTRING = &H14C
Private Const CB_SHOWDROPDOWN = &H14F
Private Const LB_FINDSTRING = &H18F
Private Const CB_ERR = (-1)
Private reader As SqlDataReader = Nothing
Private sql As String = Nothing
Private cmd As SqlCommand = Nothing
Private conn As SqlConnection = Nothing
Private connectionstring As String = Nothing
----------------------------------------------------------------------------------------------------------
Private Sub DBConnect(ByVal sql As String)
'Connection string to Server
connectionstring = "Server=URAOLY-TS01\ODASSETS;Database=ODASSETS;User Id=sa;Password=$t0p@((e$$"
conn = New SqlConnection
conn.ConnectionString = connectionstring 'Set the Connection String
conn.Open()
cmd = New SqlCommand
cmd.Connection = conn 'Sets the Connection to use with the SQL Command
cmd.CommandText = sql 'Sets the SQL String
End Sub
----------------------------------------------------------------------------------------------------------
Private Sub LoadFromDBToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles LoadFromDBToolStripMenuItem.Click

If TextBox1.Text = "" Then
MsgBox("You must type in an asset to load.")
Exit Sub
End If
ClearAllNotAsset()

Dim count As Int16 = 0
Dim commandText As String = _
"SELECT COUNT(*) from Assets where ASSETTAG = @para"

Using connection As New SqlConnection(connectionstring)
Dim command As New SqlCommand(commandText, connection)
command.Parameters.AddWithValue("@para", Trim(TextBox1.Text))
count = command.ExecuteScalar
Try
connection.Open()
Catch ex As Exception

If count = 0 Then
GoTo NotFound
Else
GoTo FoundAssets
End If
End Try
End Using

Thank you again for your assistance.