CODEWITHSUNDEEP
mysqlvb.net
Ismail.ethio
Ismail.ethio

Reputation: 35

Select statement have got error in VB.Net with mysql

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

Upvotes: 0

Views: 162

Answers (2)

Steve
Steve

Reputation: 216293

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.

Upvotes: 0

EverythingEthical
EverythingEthical

Reputation: 434

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

con.open()

Upvotes: 1

Related Questions