Select statement have got error in VB.Net with mysql

Question

The error message is also available in another threads but in my case it's different. Object reference not set to an instance of an object. When querying the following select statement. What is the problem inside?

 Dim con As New MySqlConnection(ConString)
            Dim sql As String
            Dim idno As Integer
            sql = "select client_id from car_rent where car_id = @carid"
            cmd.Parameters.AddWithValue("@carid", carid.Text.Trim)
            cmd = New MySqlCommand(sql, con)
            idno = cmd.ExecuteScalar()
            If (idno > 0) Then
                MsgBox("The Car is already Rented!", MsgBoxStyle.Exclamation, "Car Rental System")
                Return
            End If

Satpal · Accepted Answer · 2014-02-25 09:58:32Z

1

I don't see you opening the connection anywhere. use

con.open()

edited Feb 25, 2014 at 9:58

Satpal

134k13 gold badges168 silver badges171 bronze badges

answered Feb 25, 2014 at 8:49

EverythingEthical

4345 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Steve · Accepted Answer · 2014-02-25 11:01:09Z

0

Switch the order of these two lines

cmd = New MySqlCommand(sql, con)
cmd.Parameters.AddWithValue("@carid", carid.Text.Trim)

Also the line in which you execute the command seems to be working because you are using Option Strict Off, and I suggest to change to Option Strict On. In the short term you have to solve many problems but it allows better coding practices

 idno = CType(cmd.ExecuteScalar(), Integer)

However, if the command above doesn't find any record matching the parameter passed, ExecuteScalar returns Nothing and so you need to test for this situation

Dim result = cmd.ExecuteScalar()
if result IsNot Nothing Then

    idno = CType(result, Integer)

And, of course, the connection should be opened before, so summarizing everything

Dim sql = "select client_id from car_rent where car_id = @carid"
Using con As New MySqlConnection(ConString)
Using cmd = New MySqlCommand(sql, con)
   con.Open()

   cmd.Parameters.AddWithValue("@carid", carid.Text.Trim)
   Dim result = cmd.ExecuteScalar()
   if result IsNot Nothing Then
       Dim idno = CType(result, Integer)
       If (idno > 0) Then
            MsgBox("The Car is already Rented!", MsgBoxStyle.Exclamation, "Car Rental System")
            Return
       End If
   End If
End Using
End Using

Well probably is enough to test for Nothing on the result of ExecuteScalar to take your decision unless you need the idno variable for other purposes.

edited Feb 25, 2014 at 11:01

answered Feb 25, 2014 at 7:47

Steve

217k22 gold badges242 silver badges296 bronze badges

4 Comments

Ismail.ethio Over a year ago

I tried using your suggestion steel same problem. The error indicates on cmd.ExecuteScalar()

Ismail.ethio Over a year ago

But can u tell me what is IsNot Nothing and what is the secret inside this code?

Steve Over a year ago

As I have said ExecuteScalar could return null (Nothing in Visual Basic), so the operator IsNot says, if your return value is not null then you have found a record that matches your request. (and probably its idno is > 0)

Ismail.ethio Over a year ago

Really in .Net very helpful functions available. It's Ok!

Collectives™ on Stack Overflow

Select statement have got error in VB.Net with mysql

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related