1,105,331 Community Members

auto-increment data entry problem using vb6 and phpmyadmin

Member Avatar
suika
Newbie Poster
3 posts since Mar 2013
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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:

Member Avatar
jovstudios
Junior Poster in Training
53 posts since Mar 2013
Reputation Points: -3 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

Member Avatar
suika
Newbie Poster
3 posts since Mar 2013
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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?

Member Avatar
jovstudios
Junior Poster in Training
53 posts since Mar 2013
Reputation Points: -3 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

Yes Your Right..

Member Avatar
QVeen72
Veteran Poster
1,016 posts since Nov 2006
Reputation Points: 104 [?]
Q&As Helped to Solve: 164 [?]
Skill Endorsements: 7 [?]
 
0
 

Hi,

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

cn.Execute strsql

Regards
Veena

Member Avatar
suika
Newbie Poster
3 posts since Mar 2013
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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.

Member Avatar
hapticz
Newbie Poster
2 posts since Dec 2013
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

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

You
This article has been dead for over three months: Start a new discussion instead
Post:
Start New Discussion
View similar articles that have also been tagged: