auto-increment data entry problem using vb6 and phpmyadmin

[suika]

Hi, I am using VB6 and phpMyAdmin connected by WAMPServer for my system.
I am new in this language. Here's the code in my that seems to be wrong:

       Private Sub cmdsubmit_Click()
        Dim rs As ADODB.Recordset
        Dim strsql As String
        strsql = "insert into table1 ( surname, firstname, middlename, house, street, village, brgy, city, gender, kind, birthday) values (" _
        & "'" & txtsurname.Text & "'," _
        & "'" & txtfirstname.Text & "'," _
        & "'" & txtmiddlename.Text & "'," _
        & "'" & txthouse.Text & "'," _
        & "'" & txtstreet.Text & "'," _
        & "'" & txtvillage.Text & "'," _
        & "'" & txtbrgy.Text & "'," _
        & "'" & txtcity.Text & "'," _
        & "'" & combogender.Text & "'," _
        & "'" & combokind.Text & "'," _
        & "'" & DTPicker2.Value & "')"
        Set rs = cn.Execute(strsql)
        Set rs = Nothing

I have ID as the primary key from table1, and it is autoincremented.
I also have a form wherein I use filllistview to view all of these.

My problem is that everytime I enter a new data and look at it in the database or in the listview, another data with the next assigned ID number gets automatically inserted right after it with all the other fields as blank.

Can someone help me with this? D:

[jovstudios]

Just change you then Datafield of you ID in Your Database to Number.

Then This is the One Example to Increment The Value of ID to Your DataBase

Dim rsAuto As New ADODB.Recordset

Dim AutoID As String

rsAuto.Open "SELECT * FROM Table1"

        AutoID = rsAuto.RecordCount + 1

       Id.Text = "100" + AutoID

Hope It can help you

[suika]

Thank you for responding. :) So, I don't have to check auto-increment in the database and just autoincrement it in the code? Am I right?

[jovstudios]

Yes Your Right..

[QVeen72]

Hi,

You dont need a recordset to execute..
change 16 an 17 line to one line...

cn.Execute strsql

Regards
Veena

[suika]

Thank you for helping, guys. But it is still unsolved. I am thinking that maybe it is not the submit form that has a problem because I altered the codes a lot of times already but it still doesn't stop the program to insert an additional blank entry after the last data submitted.

I sthere a chance that the problem is in the SearchUpdateandView form? Here's my codes there:

Sub filllistview()
Dim strsql As String
Dim rs As ADODB.Recordset
strsql = " select * from table1"
Set rs = cn.Execute(strsql)
ListView1.ListItems.Clear
Do While Not rs.EOF
Set item = ListView1.ListItems.Add(, , rs!id)
item.SubItems(1) = rs!surname & ""
item.SubItems(2) = rs!firstname & ""
item.SubItems(3) = rs!middlename & ""
item.SubItems(4) = rs!house & ""
item.SubItems(5) = rs!street & ""
item.SubItems(6) = rs!village & ""
item.SubItems(7) = rs!brgy & ""
item.SubItems(8) = rs!city & ""
rs.MoveNext
Loop
Set rs = Nothing
End Sub


Private Sub cmdupdate_Click()
Dim strsql As String
Dim rs As ADODB.Recordset
strsql = "update table1 set surname='" & txtsurname.Text & "'," _
& "firstname='" & txtfirstname.Text & "'," _
& "middlename='" & txtmiddlename.Text & "'," _
& "house='" & txthouse.Text & "'," _
& "street='" & txtstreet.Text & "'," _
& "village='" & txtvillage.Text & "'," _
& "brgy='" & txtbrgy.Text & "'," _
& "city='" & txtcity.Text & "'" _
& "where id='" & labelid.Caption & "'"
Set rs = cn.Execute(strsql)
Set rs = Nothing
MsgBox "Record has been updated. :}"
If yourmsg = vbOK Then frmgeneral.Show
Me.filllistview
End Sub


Private Sub listview1_itemclick(ByVal item As MSComctlLib.ListItem)
With item
labelid.Caption = item
txtsurname.Text = .SubItems(1)
txtfirstname.Text = .SubItems(2)
txtmiddlename.Text = .SubItems(3)
txthouse.Text = .SubItems(4)
txtstreet.Text = .SubItems(5)
txtvillage.Text = .SubItems(6)
txtbrgy.Text = .SubItems(7)
txtcity.Text = .SubItems(8)
End With
End Sub


Private Sub txtsearch_Change()
Me.searchlistview
End Sub


Sub searchlistview()
Dim strsql As String
Dim rs As ADODB.Recordset
strsql = " select * from table1 where surname like '" & txtsearch.Text & "%';"
Set rs = cn.Execute(strsql)
ListView1.ListItems.Clear
Do While Not rs.EOF
Set item = ListView1.ListItems.Add(, , rs!id)
item.SubItems(1) = rs!surname & ""
item.SubItems(2) = rs!firstname & ""
item.SubItems(3) = rs!middlename & ""
item.SubItems(4) = rs!house & ""
item.SubItems(5) = rs!street & ""
item.SubItems(6) = rs!village & ""
item.SubItems(7) = rs!brgy & ""
item.SubItems(8) = rs!city & ""
rs.MoveNext
Loop
Set rs = Nothing
End Sub

The database doesn't have any problems, I think. Because of course, when I insert data via phpMyAdmin, no additional entry is inserted.

[hapticz]

peerhaps you can fudge it, write some code to 'eliminate/delete' the entry that is being automatically inserted/created.