Error with ExecuteScalar in code 1

[amberlynn]

I'm not sure if I should be posting this here or in the SQL forum.
I'm having a problem retrieving a value from SQL using the following code:

Dim strSQL As String = "select myDate from myTable where myField = '" & CPID & "'"
Dim Conn As New SqlConnection(strConn)
Dim Cmd As New SqlCommand(strSQL, Conn)
Conn.Open()
Try
strCPApprovedDt = Cmd.ExecuteScalar().ToString
Catch ex As Exception
MsgBox(ex.Message)
End Try

If there is a record that matches this in my SQL table, it works great, but if there is no record, instead of returning null, or nothing, I get the error: "Object reference not set to an instance of an object".
I tried wrapping the ExecuteScalar line of code with an If IsNothing or IsDBNull, but they both trigger this error too.

How can I fix this?

Thanks!

Amber

 

[PGoRule]

If you are using Cmd.ExecuteScalar(), it returns an Object. You have already declared strCPApprovedDt as String. Remove .ToString from it. This will give you result as Nothing if the record is not present.

strCPApprovedDt = Cmd.ExecuteScalar()

Sharing the best from my side...

--Prashant--