Assigning Values

[Cap2010]

HI,

I want to the value from the below statement

select count(party_id) from Master

assigned to a variable. so that I can
use for do while condition.

Will this do

dim vCount as variant

vCount = cn.Execute ("select count(party_id) from Master&quot

Really will solve much of my problems.

Lad

 

[KirkJewell]

I only use the dataenvironment for developing purposes, ie view the data whilst in Vb. I set up recordsets which point to the tables in the database.

dim rs as new recordset
set rs = rsStaff.recordset.clone

' In this example rsStaff is a recordset already connected to a table in my database called Staff_t. You may want to use your data environment to connect you recordset (or use the ADO Data Control.

Once you have set up a connection and a recordset (using the sql select statement - something like 'Select * From Master Order By Party_id')

You now you have a recordset variable with its own methods for searching.

You can now get the number of records in the table by :-
vCount = rs.recordcount
or you could change your Loop

While rs.RecordCount < 10
' Do something
Wend

NB.
If you know how many records in your table you can step through in a 'For Next'. If you don't know how many records you can do the following

If rs.RecordCount > 0 then ' Only do if at least 1 record
rs.MoveFirst ' start at the beginning of table
Do
' Do Something
rs.MoveNext ' goto to next record (if there is one)
Loop Until rs.Eof ' Keep looping until no more records
endif

rs.movefirst ' start at the beginning of the table

 

[Antzz]

1) Create a recordset object
2) Assign the SQL statement :
ls_SQL = &quot;select total = count(party_id) from Master&quot;
3) Execute the SQL statement using the recordset object which will be something like :
rec.Open ls_SQL <cursor type>
4) You will get the total records in
rec(&quot;total&quot field.

Try it and lemme know.

Thanks

PS : Of course you have to create a database connection first.

 

[Cap2010]

Things worked fine after assigning count.
Now things are a bit different, recordset moves only once and it comes to end of file!!!!!!(Really bugs me)
Question is
how can i move to the next group record where next other party record exist for insertion.


set rs3 = new adodb.recordset
Set RS5 = New ADODB.Recordset

rs3.Open &quot;select * from dChallan order by dParty_id ASC,dChallan_date ASC&quot;, cn, adOpenDynamic, adLockOptimistic, adCmdText

RS5.Open &quot;InvMaster&quot;, cn, adOpenDynamic, adLockOptimistic, adCmdTable


rs3.MoveFirst

Do While Not rs3.EOF
vParty_id = Trim(rs3!dParty_id)
vSiteId = rs3!dsite

If rs3!dParty_id = vParty_id And rs3!dsite = vSiteId Then

strSQL = &quot;SELECT dChallan.dparty_id, dChallan.dsite, Count(*) AS vCount&quot;
strSQL = strSQL & &quot; From dChallan, sites&quot;
strSQL = strSQL & &quot; Where (((dChallan.dsite) = [sites].[sid] and &quot;
strSQL = strSQL & &quot; dChallan.dParty_Id = '&quot; & vParty_id & &quot;' And dChallan.dSite = '&quot; & vSiteId & &quot;' ))&quot;
strSQL = strSQL & &quot; GROUP BY dChallan.dParty_id, dChallan.dSite&quot;
strSQL = strSQL & &quot; HAVING (((Count(*))>=1));&quot;

Set rs3 = cn.Execute(strSQL)

vCount = rs3.Fields(&quot;vCount&quot.Value

strInsert = &quot;insert into invMaster (InvNo, InvDate, CustCode, Site)&quot;
strInsert = strInsert & &quot; Values ('&quot; & vInvNo & &quot;','&quot;
strInsert = strInsert & EndDate & &quot;','&quot;
strInsert = strInsert & vParty_id & &quot;','&quot;
strInsert = strInsert & vSiteId &&quot&quot;

cn.Execute (strInsert)

vInvNo = vInvNo + 1
If rs3.EOF = True Then
Exit Do
End If
End If

[red] 'Here the rs3 comes to eof after the below command which is not supposed
to do

rs3.MoveNext
[/red]
Loop

 

[Cap2010]

How can i have conditional insert
Below statement has invno,invdate getting value from a variable where user is prompted to enter;
and the values is assigned to grossamt after calculation, remaining is CustCode and Site
can have values assigned

Only things it should be inserted as per conditions

statement


insert into invMaster (InvNo, InvDate, CustCode, Site, GrossAmt) Values ('103','31-May-00','AL','New',6400) Select dParty_id,dSite from dChallan where dParty_id = 'CNC' and dSite = 'New';

or

insert into invMaster (InvNo, InvDate, CustCode, Site, GrossAmt) Values ('103','31-May-00','AL','New',6400) where dParty_id = 'AL' and dSite = 'New';

both the above gives [red]error[/red] of terminating statement

Want the above statement to run with the conditions. How

 

[Cap2010]

HI,
Have done my work.

Thanks.

Lad