Comments by Member 10503959 (Top 1 by date)

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.