CODEWITHSUNDEEP
sql-servervb.net
CormacD
CormacD

Reputation: 23

ExecuteScalar connection property has not not been initialized

When I run this piece of code I get the error:

ExecuteScalar connection property has not not been initialized

I can't seem to find why the connection isn't working.

Code:

Protected Sub btnTrackRepair_Click(sender As Object, e As EventArgs) Handles btnTrackRepair.Click

    Using conn As New SqlConnection("Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\ITrepair.mdf;Integrated Security=True")
        conn.Open()
        Dim cmd As New SqlCommand(conn.ToString)
        Dim txtTracking As String
        cmd.CommandText = "SELECT Repair_Status FROM Repair WHERE Tracking_Number =" & txtTrack.Text
        txtTracking = If(IsDBNull(cmd.ExecuteScalar), "", cmd.ExecuteScalar)
        If txtTracking <> "" Then
            MsgBox("Record Found!", MsgBoxStyle.Information, "Update")
            txtStatus.Text = ""
            txtStatus.Text = txtTracking
        Else
            MsgBox("No Record Found!", MsgBoxStyle.Information, "INFO.")
        End If
    End Using

End Sub

The code breaks at txtTracking = If(IsDBNull(cmd.ExecuteScalar), "", cmd.ExecuteScalar)

I have looked at other questions regarding this error however most are C# and I'm using VB.NET so I have found it difficult to find a solution.

Upvotes: 2

Views: 810

Answers (1)

Tim Schmelter
Tim Schmelter

Reputation: 460238

You are using the SqlCommand constructor which takes only a string. But this is not the connection-string but the text of the query. So you've done it wrong.

Dim cmd As New SqlCommand("SELECT Repair_Status FROM Repair WHERE Tracking_Number = @Tracking_Number", conn)

Apart from that you should really get familiar with parameterized queries. Don't use string concatenation to build your queries to avoid (among other issues) sql-injection attacks.

Here is the complete method:

Protected Sub btnTrackRepair_Click(sender As Object, e As EventArgs)
    Dim sqlQuery = "SELECT Repair_Status FROM Repair WHERE Tracking_Number = @Tracking_Number"

    Using conn As New SqlConnection("Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\ITrepair.mdf;Integrated Security=True")
        conn.Open()
        Dim cmd As New SqlCommand(sqlQuery, conn)
        cmd.Parameters.Add("@Tracking_Number", SqlDbType.NVarChar).Value = txtTrack.Text

        Dim statusObj = cmd.ExecuteScalar()
        Dim status = If(statusObj is DBNull.Value, Nothing, DirectCast(statusObj, string))

        If not String.IsNullOrEmpty(status) Then
            MsgBox("Record Found!", MsgBoxStyle.Information, "Update")
            ' ... '
        Else
            MsgBox("No Record Found!", MsgBoxStyle.Information, "INFO.")
        End If
    End Using
End Sub

If the Tracking_Number is not a varchar/nvarchar but an int(for example) in the database, you should parse it already here and use the correct SqlDbType.

Upvotes: 1

Related Questions