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?

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

Hope it helps u

Edited 5 Years Ago by Pgmer: spelling mistakes

Sorry,I am not able to understand.

Could you please provide a more detailed example?

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.

@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));
This article has been dead for over six months. Start a new discussion instead.