Member Avatar for shivya jain

Hello to all,
I want to auto generate id that should be access from my sql database.

I try this code-

Dim j As Integer = 0
        Dim cmd As New SqlCommand("select staff_id from staff ", c.con)

        Dim dr As SqlDataReader
        dr = cmd.ExecuteReader()
        While (dr.Read())
            txtstaff_id.Text = dr("staff_id").ToString()

            j = Convert.ToInt32("txtstaff_id.text".ToString())

            j = j + 1
            txtstaff_id.Text = j.ToString()
        End While
        dr.Close()

error occurs in

j = Convert.ToInt32("txtstaff_id.text".ToString())

please check, Is this in right format?
please reply soon, i have to submit my project on monday.

Edited by Narue because: added code tags
  • 5 Contributors
  • 7 Replies
  • 277 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by lolafuertes

Dani AI

Generated

A few focused points to close this out and make the solution robust.

The immediate bug came from treating the control name as a string (quotes) and from using a conversion that will throw on empty or null text. A safer local fix is to parse the textbox with Integer.TryParse and fall back to zero when parsing fails:

Dim nextId As Integer
If Not Integer.TryParse(txtstaff_id.Text, nextId) Then
    nextId = 0
End If
nextId += 1
txtstaff_id.Text = nextId.ToString()

For retrieving the current maximum from the database, prefer a single-value call instead of iterating with a reader. Use the command that returns one scalar value and check for DBNull before converting:

Dim obj = cmd.ExecuteScalar()
Dim currentMax As Integer = If(obj Is Nothing OrElse Convert.IsDBNull(obj), 0, Convert.ToInt32(obj))
Dim newId = currentMax + 1

Important production notes: computing MAX(id)+1 in the application is vulnerable to race conditions — two users can get the same id. For reliable behaviour use a database-assigned id (IDENTITY in SQL Server, AUTO_INCREMENT in MySQL, or a sequence) and then read the generated id back (for SQL Server use SCOPE_IDENTITY() or the OUTPUT clause; for MySQL use LAST_INSERT_ID()). If you must manage ids yourself, do it inside a serialized transaction or with an atomic counter table.

Also use Using blocks to ensure connections/commands are disposed, verify the staff_id column type (int vs varchar), and add Try/Catch for unexpected errors. Thanks to , , and for the helpful pointers already in the thread.

Recommended Answers

All 7 Replies

Member Avatar for debasisdas

try this.

j = Convert.ToInt32(txtstaff_id.text.ToString())

also change

select staff_id from staff

to

select max(staff_id) from staff
Edited by debasisdas because: n/a
Member Avatar for shivya jain

try this.

j = Convert.ToInt32(txtstaff_id.text.ToString())

also change

select staff_id from staff

to

select max(staff_id) from staff

thanks debasisdas,
is there need to take sqldatareader?
i am using this

c.con.Open()
Dim j As Integer = 0
Dim cmd As New SqlCommand("select max(staff_id) from staff ", c.con)
Dim dr As SqlDataReader
dr = cmd.ExecuteReader()
While (dr.Read())
txtstaff_id.Text = dr("staff_id").ToString()

j = Convert.ToInt32("txtstaff_id.text".ToString())

j = j + 1
txtstaff_id.Text = j.ToString()
End While
dr.Close()
c.con.Close()

txtstaff_id.Text = dr("staff_id").ToString()

this line occurs error
please help me if you can..

Member Avatar for GeekByChoiCe

Should be:
txtstaff_id.Text = dr("staff_id").ToString()

j = Convert.ToInt32(txtstaff_id.Text)

Member Avatar for lolafuertes

I would suggest to use

j = dr.GetInt32(0)
txtstaff_id.Text= j.ToString()

You can not use txtstaff_id.Text = dr("staff_id").ToString() because there is no field called staff_id in your select.

When you ask for the MAX(staff_id ) the returned field name is some thing like MaxOfstaff_id.

You can force the returned name to be (IE) MaxId with select max(staff_id) as MaxId from staff and then use dr("MaxId").

Hope this helps

Member Avatar for Momerath

Should be:
txtstaff_id.Text = dr("staff_id").ToString()

You'll get a "no dr exists in this context" error if you do this. 'dr' is an object, and you are treating it as a method. Square brackets is the proper way to do it.

Member Avatar for shivya jain

I would suggest to use

j = dr.GetInt32(0)
txtstaff_id.Text= j.ToString()

You can not use txtstaff_id.Text = dr("staff_id").ToString() because there is no field called staff_id in your select.

When you ask for the MAX(staff_id ) the returned field name is some thing like MaxOfstaff_id.

You can force the returned name to be (IE) MaxId with select max(staff_id) as MaxId from staff and then use dr("MaxId").

Hope this helps

In my database, staff_id is my column name and my table name is staff.
i can't get your code please
again post reply using my table's column name.

Member Avatar for lolafuertes

In green my suggestions.

c.con.Open()
Dim j As Integer = 0
Dim cmd As New SqlCommand("select max(staff_id) from staff ", c.con)
Dim dr As SqlDataReader
dr = cmd.ExecuteReader()
If dr.Read() then
    j = dr.GetInt32(0) + 1
    txtstaff_id.Text = j.ToString()
End If
dr.Close()
c.con.Close()

Hope this helps

Edited by lolafuertes because: n/a
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.