Click here to Skip to main content
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 20:14pm
surkhi623
Edited 28-Apr-13 20:46pm
Maciej Los150.9K
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)



Advertise | Privacy | Mobile
Web03 | 2.8.1411022.1 | Last Updated 29 Apr 2013
Copyright © CodeProject, 1999-2014
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