How to run sum() Function in VB6?

[neo008]

Hi All,

How to run sum() Function in VB6?

Adodc1.recordso urce="select sum(quantity) from purchase"
adodc1.refresh

1.) but how to retrieve result in desired text box like i took- AvailabeQuantit y.Text

somewhere i read that query is like "Select sum(quantity) as SumOfQuantity from purchase"

2.) in above statement what is "as SumOfQuantity"

can you please help me

NE☼

[ansumansahu]

Hi All,

How to run sum() Function in VB6?

Adodc1.recordso urce="select sum(quantity) from purchase"
adodc1.refresh

1.) but how to retrieve result in desired text box like i took- AvailabeQuantit y.Text

somewhere i read that query is like "Select sum(quantity) as SumOfQuantity from purchase"

2.) in above statement what is "as SumOfQuantity"

can you please help me

NE☼

Hi ,

when you execute this query the quantity will be populated in the column named SumOfQuantity

-ansuman sahu

[cmrhema]

Hi All,

How to run sum() Function in VB6?

Adodc1.recordso urce="select sum(quantity) from purchase"
adodc1.refresh

1.) but how to retrieve result in desired text box like i took- AvailabeQuantit y.Text

somewhere i read that query is like "Select sum(quantity) as SumOfQuantity from purchase"

2.) in above statement what is "as SumOfQuantity"

can you please help me

NE☼

I am not sure about adodc control but if you can change to adodb connection then put down the code as below.
Also remove adodc and
add reference Microsoft Active X Data objects 2.0 library by clicking on Project--References
Now the code

Dim MyCon As New ADODB.Connection
Dim Myrs As New ADODB.Recordset
Dim sConnect As String


sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                       "Data Source=" & _
                        App.Path & "emp.mdb"
'Replace emp with your file name
MyCon.Open sConnect
Myrs.Open "select sum(quantity) from purchase", MyCon, adOpenDynamic, adLockPessimistic
text1.text=Myrs.Fields(0)
Myrs.Close
MyCon.Close

it will display the result.

as far as as "SumOfQuant ity" it should be used in sql query statement in sql not in visual basic.

[neo008]

Thank you Sahu, Thank you Cmrehma,

I have total 4 fields in my 'Purchase' table -(product_id, product_name, quantity, price)

what should be code for sum(quantity) if i use Adodc connection.

i have tried --
adodc1.recordso urce="select sum(quantity) from purchase"
adodc1.refresh

now how to get result in a text box????????

I want to retrieve data into a text box. THIS IS WHAT I WANT

as you said Sahu, SumOfQuantity is column in which my result will be populated. so how a result will go to database table? moreover in the form of column?

as you said cmrehma, forgive me if i misspelt you,
you said ---------->adodc1.fields( 0)
but i dont find any method with adodc named "Fields" so I got an error.

can you please sort out my need.

NE☼

[cmrhema]

Thank you Sahu, Thank you Cmrehma,

I have total 4 fields in my 'Purchase' table -(product_id, product_name, quantity, price)

what should be code for sum(quantity) if i use Adodc connection.

i have tried --
adodc1.recordso urce="select sum(quantity) from purchase"
adodc1.refresh

now how to get result in a text box????????

I want to retrieve data into a text box. THIS IS WHAT I WANT

as you said Sahu, SumOfQuantity is column in which my result will be populated. so how a result will go to database table? moreover in the form of column?

as you said cmrehma, forgive me if i misspelt you,
you said ---------->adodc1.fields( 0)
but i dont find any method with adodc named "Fields" so I got an error.

can you please sort out my need.

NE☼

Check out the new reply. I replaced the old one

[neo008]

Check out the new reply. I replaced the old one

Thanx Cmrhema, I just check it out.

[neo008]

I am not sure about adodc control but if you can change to adodb connection then put down the code as below.
Also remove adodc and
add reference Microsoft Active X Data objects 2.0 library by clicking on Project--References
Now the code

Dim MyCon As New ADODB.Connection
Dim Myrs As New ADODB.Recordset
Dim sConnect As String


sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                       "Data Source=" & _
                        App.Path & "emp.mdb"
'Replace emp with your file name
MyCon.Open sConnect
Myrs.Open "select sum(quantity) from purchase", MyCon, adOpenDynamic, adLockPessimistic
text1.text=Myrs.Fields(0)
Myrs.Close
MyCon.Close

it will display the result.

as far as as "SumOfQuant ity" it should be used in sql query statement in sql not in visual basic.

Thanx for your interest, I am new to VB and not used Adodb before.
I have used adodc in entier project.

Is it possible to change Adodc connection to Adodb for a piece of code
(As you are suggesting me)
If it is so, are you sure it would not affect rest of my project.


Cmrhema, I really appreciate your interest.
Even if you can't tell me about Adodc I thank you by heart.

NE☼

[neo008]

I am not sure about adodc control but if you can change to adodb connection then put down the code as below.
Also remove adodc and
add reference Microsoft Active X Data objects 2.0 library by clicking on Project--References
Now the code

Dim MyCon As New ADODB.Connection
Dim Myrs As New ADODB.Recordset
Dim sConnect As String


sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                       "Data Source=" & _
                        App.Path & "emp.mdb"
'Replace emp with your file name
MyCon.Open sConnect
Myrs.Open "select sum(quantity) from purchase", MyCon, adOpenDynamic, adLockPessimistic
text1.text=Myrs.Fields(0)
Myrs.Close
MyCon.Close

it will display the result.

as far as as "SumOfQuant ity" it should be used in sql query statement in sql not in visual basic.

Hi Cmrhema, My reply had not been posted last time so I'm writing again.

I am very new to VB and never used Adodb before.
I have used Adodc only in my entire project.

Is it possible to use Adodb for a piece of code without affecting the rest of the project having code for Adodc.?

I appreciate your interest. Even if you cant tell me much about Adodc, I thank you by heart for your support.

NE☼

[cmrhema]

Thanx for your interest, I am new to VB and not used Adodb before.
I have used adodc in entier project.

Is it possible to change Adodc connection to Adodb for a piece of code
(As you are suggesting me)
If it is so, are you sure it would not affect rest of my project.


Cmrhema, I really appreciate your interest.
Even if you can't tell me about Adodc I thank you by heart.

NE☼

Usually it is not desirable to put both into one project. But because you have no choice go for it.

[neo008]

Check out the new reply. I replaced the old one

I am replying you 3rd time.

I dont know wht's the problem behind this but...

Actually Cmrhema, I have never used Adodb and my entire project is having Adodc connection.

So is it possible to use Adodb for a piece of code without affecting the Adodc code of rest of the project.

NE☼

[cmrhema]

I am replying you 3rd time.

I dont know wht's the problem behind this but...

Actually Cmrhema, I have never used Adodb and my entire project is having Adodc connection.

So is it possible to use Adodb for a piece of code without affecting the Adodc code of rest of the project.

NE☼

lookup the previous replu

[cmrhema]

I am replying you 3rd time.

I dont know wht's the problem behind this but...

Actually Cmrhema, I have never used Adodb and my entire project is having Adodc connection.

So is it possible to use Adodb for a piece of code without affecting the Adodc code of rest of the project.

NE☼

lookup the previous reply

[neo008]

Dear Cmrhema,

it's giving error message : "Not a valid file name."

I have put the code....

Dim MyCon As New ADODB.Connection
                Dim Myrs As New ADODB.Recordset
                Dim sConnect As String
                
                sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                       "Data Source=" & _
                                        App.Path & "E:\Projects\Rishika Enterprises Project\Database\Rishika070601.mdb"
                'Replace emp with your file name
                MyCon.Open sConnect
                Myrs.Open "select sum(quantity) from purchase", MyCon, adOpenDynamic, adLockPessimistic
                Text1.Text = Myrs.Fields(0)
                Myrs.Close
                MyCon.Close

When I put only Database file name "Rishika070601. mdb" it says can not find file E:\VBProjects\I nventoryRishika 07061.mdb

[neo008]

Hi Cmrhema,

It's working now.

Thanx a lot. Actually I'm from embedded side and new to VB that's why troubled you so much.

I appreciate your helping nature.

-NE☼