'Conversion from type dbnull to type integer is not valid' problem

[axes2t2]

Hello,

I am using a code to generate an id for a table.But it is giving 'Conversion from type dbnull to type integer is not valid' exception.

Now I know that this is due to the table being an empty set.But it works properly if there is already a row in the table.

How to rectify this?

Private Sub GenerateID()
        Dim com As New MySqlCommand
        If conn.State = ConnectionState.Closed Then
            conn.Open()
        End If
        Try
            com.Connection = conn
            com.CommandText = "select max(right(cust_id,length(cust_id)-3)) from customer"
            last = com.ExecuteScalar
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        If conn.State = ConnectionState.Open Then
            conn.Close()
        End If
    End Sub

Private Sub new_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        b1.Enabled = True
        GenerateID()
        last = last + 1
        cid = "c" & "0" & "0" & "0" & "0" & last
        t1.Text = cid
        End Sub

When I click on 'new' button it generates an id pattern in the textbox 't1'.But it gives that dbnull exception first.

I am using mysql as backend and Visual Studio 2008 as frontend.Any ideas?

[Pgmer]

Use IIf(IsDBNull(urobject), "", Data ur fetching
IIf(Isdubnull(Object tocheck for null),True part,Falsepart)

Hope it helps u

[axes2t2]

Sorry,I am not able to understand.

Could you please provide a more detailed example?

[Pgmer]

In which line your getting the error message?

[sandeepparekh9]

when u try to get the Cust_id and there are no record in database then value of your last will be null..

so check like this:

if IsDBNull(last) then
last =0
end if

u cant set last here to watever your starting cust_id is rather than 0.

[Pgmer]

IIf(Isdubnull(Last),0,Last)

Hope it works :)

[axes2t2]

@sandeepparekh9 and @Pgmer

I tried your codes but it still gives me that error.It's on the line

last = com.ExecuteScalar

My table in the database is as follows

create table customer(
cust_id varchar(7),
cust_name varchar(50),
cust_address text(150),
cust_phone int,
cust_email varchar(30),
PRIMARY KEY(cust_id));