Click here to Skip to main content
11,631,369 members (74,906 online)
Rate this: bad
good
Please Sign up or sign in to vote.
Hello,
How can we create a single record with a unique number(primary key) which includes number of lines of records in listview control using Vb6 and MS-Access connectivity???
I have tried something like this...
It saves one record at a time but I wanna add records(multiple) to listview and then save all with single id.
Private Sub BtnAdd3_Click()
    If .RecordCount > 0 Then
   .MoveFirst
    Do While Not .EOF
For i = 1 To ListView1.ListItems.Count
 
  SQL = "SELECT A.*,B.* FROM [ASSET] A,[NOTE] B " & _
      " WHERE A.AID=B.AID AND BITT='FALSE'"
 
For j = 1 To ListView1.ListItems(i).ListSubItems.Count
  SQL = SQL & "'" & ListView1.ListItems(i).ListSubItems(j).Text & "'"
 
   .Open SQL, CON, 1, 2
.AddNew
        !DOC_NO = Text1.Text                //contains pk of table
        !AID = Text3.Text
        !DOC_SUBNO = Text4.Text            //contains no. for each line(duplicates)
        !LOC_ID = Combo4.ItemData(Combo4.ListIndex)
        !SUP_ID = Combo5.ItemData(Combo5.ListIndex)
.Update
   .MoveNext
    Loop
    End If
End Sub

But it's not giving proper results... plz help....
Posted 28-Apr-13 19:14pm
surkhi623
Edited 28-Apr-13 19:46pm
Maciej Los187.2K
v2

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Have a look at your code and imagine what you are trying to achieve.

SQL variable is global variable, am i right?
In this line:
SQL = "SELECT A.*,B.* FROM [ASSET] A,[NOTE] B " & _
    " WHERE A.AID=B.AID AND BITT='FALSE'"
you set value for variable correctly, but depends on count of listview subitems and values, your SQL variable:
SQL = SQL & "'" & ListView1.ListItems(i).ListSubItems(j).Text & "'"
will be looks like:
'first loop
"SELECT A.*,B.* FROM [ASSET] A,[NOTE] B " & _
    " WHERE A.AID=B.AID AND BITT='FALSE''subitem1';"
'second loop
"SELECT A.*,B.* FROM [ASSET] A,[NOTE] B " & _
    " WHERE A.AID=B.AID AND BITT='FALSE''subitem1''subitem2';"
'and so on...
"SELECT A.*,B.* FROM [ASSET] A,[NOTE] B " & _
    " WHERE A.AID=B.AID AND BITT='FALSE''subitem1''subitem2'...'subitem99999';"

Use debugger!

Your query is wrong, because of 2 reasons:
1) Not using JOIN's
2) Is not updatable (probably)
Instead
"SELECT A.*,B.* FROM [ASSET] A,[NOTE] B " & _
    " WHERE A.AID=B.AID AND BITT='FALSE'"
use
"SELECT A.*, B.* FROM [ASSET] AS A LEFT JOIN [NOTE] AS B " _
    " ON A.AID=B.AID WHERE BITT='FALSE'"

More about JOINS you'll find here:
http://www.w3schools.com/sql/sql_join.asp[^]
Visual Representation of SQL Joins[^]

If you want to add new record to recordset, your query need to be updatable: AddNew method[^]

If BITT field is Bit data type, your query should looks like:
"SELECT A.*, B.* FROM [ASSET] AS A LEFT JOIN [NOTE] AS B " _
    " ON A.AID=B.AID WHERE BITT=FALSE"
or
"SELECT A.*, B.* FROM [ASSET] AS A LEFT JOIN [NOTE] AS B " _
    " ON A.AID=B.AID WHERE BITT=0"

Please, follow the below links:
Using ADO with MS Visual Basic[^]
ADO Code Examples in Visual Basic[^]
  Permalink  
Comments
cool_sari at 29-Apr-13 4:49am
   
thank you sir,but I don't have problem at join or add record,after adding record it sh'd check every line in listview and then save all lines as a whole record(using 1 number I w'd like to access that bunch of records)
plz help......
Maciej Los at 29-Apr-13 5:11am
   
Sorry, i do not understand you...
cool_sari at 29-Apr-13 7:24am
   
I mean(suppose this is listview....)

doc_no sub-no aid loc_id sup_id
101 01 64 3 33
02 78 7 12
03 36 6 37
102 01 65 25 15
likewise.......

so here <101> is saved as whole record id(containing 3 lines of records)in access n while I'll take <101> it'll show all it's lines of records....
but I don't know how to code to get these results???

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS


Advertise | Privacy | Mobile
Web02 | 2.8.150723.1 | Last Updated 29 Apr 2013
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100