What's Wrong in the code

What's wrong in the code where i want to get sum from the field?
Private Sub Command1_Click()
Dim rs As ADODB.Recordset
Dim sql As String
Set rs = New ADODB.Recordset
    sql = "SELECT SUM(Amount) as 'Total Amount' FROM Deduction WHERE Amount = 'Amount'"
    rs.LockType = adLockOptimistic
    rs.CursorType = adOpenKeyset
    rs.Open sql, cn
    With rs
       text1.Text = (rs("Total Amount"))
    End With
    Set rs = Nothing
End Sub

Open in new window

Whing Dela CruzAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Éric MoreauConnect With a Mentor Senior .Net ConsultantCommented:
I made a mistake, but once again you should have been able to find it. Are you a programmer?

public sub Form_Load
   call open_conn("YourDataBaseName", "YourServerName")
end sub

Public Sub OPEN_CON( db As String, ServerName As String)
0
 
danaseamanCommented:
Try this:

"SELECT SUM(Amount) as 'Total Amount' FROM Deduction"

Open in new window

0
 
Éric MoreauSenior .Net ConsultantCommented:
what kind of problems do you have?

"WHERE Amount = 'Amount'" does not make sense because you also sum that field which presume that it is a numeric field. What kind of filter are you trying to do?
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Whing Dela CruzAuthor Commented:
I',m trying to get the total amount of the field name !amount the fields of the table area as follows
TheCode   N_Name    C_Name      Amount

I want to view  the total of amount in the field name Amount... Thanks
0
 
Whing Dela CruzAuthor Commented:
I try to  use this code but an error appear " Arguments are of wrong type, are out of acceptable range, or are in conflict with one another."
what does it mean?

Private Sub Command1_Click()
Dim rs As ADODB.Recordset
Dim sql As String
Set rs = New ADODB.Recordset
    sql = "SELECT SUM(Amount) as 'Total Amount' FROM Deduction"
    rs.LockType = adLockOptimistic
    rs.CursorType = adOpenKeyset
    rs.Open sql, cn
    With rs
       text1.Text = (rs("Total Amount"))
    End With
    Set rs = Nothing
End Sub

Open in new window

0
 
Éric MoreauSenior .Net ConsultantCommented:
On which line do you have this error?
Is Amount a numeric field?

can you try this:

Private Sub Command1_Click()
Dim rs As ADODB.Recordset
Dim sql As String
Set rs = New ADODB.Recordset
    sql = "SELECT SUM(Amount) as TotalAmount FROM Deduction"
    rs.LockType = adLockOptimistic
    rs.CursorType = adOpenKeyset
    rs.Open sql, cn
    With rs
       text1.Text = (rs("TotalAmount"))
    End With
    Set rs = Nothing
End Sub

Open in new window

0
 
Whing Dela CruzAuthor Commented:
The line
rs.open sql, cn  is be being highlighted as an error

0
 
Éric MoreauSenior .Net ConsultantCommented:
where is cn declared? it should be your connection and you don't seem to have it opened.
0
 
Whing Dela CruzAuthor Commented:
I'm not sure if this declaration is correct..
Please see whats wrong..

Public Sub OPEN_CON(cn As ADODB.Connection, db As String, ServerName As String)
Set cn = New ADODB.Connection
cn.Provider = "sqloledb"
cn.ConnectionTimeout = 25
cn.Properties("Data source").Value = Trim(ServerName)
cn.Properties("Initial Catalog").Value = Trim(db)
cn.Properties("User ID").Value = "sa"
cn.Properties("Password").Value = ""
cn.CursorLocation = adUseClient
cn.Open , "sa", ""
End Sub
 
Private Sub Command1_Click()
Dim rs As ADODB.Recordset
Dim sql As String
Set rs = New ADODB.Recordset
    sql = "SELECT SUM(Amount) as TotalAmount FROM Deduction"
    rs.LockType = adLockOptimistic
    rs.CursorType = adOpenKeyset
    rs.Open sql, cn
    With rs
       Text1.Text = (rs("TotalAmount"))
    End With
    Set rs = Nothing
End Sub

Open in new window

0
 
Éric MoreauSenior .Net ConsultantCommented:
Haven't I already provided code to open a connection in one of your other questions (many of which are still opened)?

The problem with this code is that the cn in the Command1_Click event is not the same as the one in the OPEN_CON method. Do you have cn declared at the module level? Do you call your OPEN_CON somewhere?

You better add "Option Explicit" at the top of each file in your project.
0
 
Whing Dela CruzAuthor Commented:
I add Option explicit at the top.. Now the message says " variable not define" and cn is being higlighted. How to define cn? i think i have already define it but still got wrong. what should i check now?
0
 
Éric MoreauSenior .Net ConsultantCommented:
under Option Explicit, add this line

Private m_cn as ADODB.Connection

this will create a new variable called m_cn available everywhere (in every methods) of this module. The prefix m_ is a "best practice" to quicly recognize a variable declared at the module level (as opposed to declared into a method).

In your Form_Load event, you need to call your OPEN_CON method (and you wouldn't have to pass the connection object in arguments because it is available from every where in your module).

You code would be like this:
option explicit
 
Private m_cn as ADODB.Connection
 
public sub Form_Load
   call open_conn("YourDataBaseName")
end sub
 
Public Sub OPEN_CON( db As String, ServerName As String)
Set m_cn = New ADODB.Connection
with m_cn
.Provider = "sqloledb"
.ConnectionTimeout = 25
.Properties("Data source").Value = Trim(ServerName)
.Properties("Initial Catalog").Value = Trim(db)
.Properties("User ID").Value = "sa"
.Properties("Password").Value = ""
.CursorLocation = adUseClient
.Open , "sa", ""
end with
End Sub
 
Private Sub Command1_Click()
Dim rs As ADODB.Recordset
Dim sql As String
Set rs = New ADODB.Recordset
    sql = "SELECT SUM(Amount) as TotalAmount FROM Deduction"
    rs.LockType = adLockOptimistic
    rs.CursorType = adOpenKeyset
    rs.Open sql, m_cn
    With rs
       Text1.Text = (rs("TotalAmount"))
    End With
    Set rs = Nothing
End Sub

Open in new window

0
 
Whing Dela CruzAuthor Commented:
I'm very sorry but an error appear  " Sub Or Function not define" and under form load
Call open_conn is being highlighted
i add Public conn as adodb.connection below option explicit but do the same error...
I think its almost done thanks...
0
 
Éric MoreauSenior .Net ConsultantCommented:
Sorry I made a typo (that you should have been able to find):

   call OPEN_CON("YourDataBaseName")
0
 
Whing Dela CruzAuthor Commented:
I change call open_conn("ThePxMe")
to
 call OPEN_CON("ThePxMe")
and error says " Argument not Optional call Open_con is being Highlighted
What should i do now

0
 
Éric MoreauSenior .Net ConsultantCommented:
change:
Public Sub OPEN_CON( db As String, ServerName As String)

to :
Public Sub OPEN_CON( ServerName As String)
0
 
Whing Dela CruzAuthor Commented:
hello emoreau,
i changed it but still has an error
.Properties("Initial Catalog").Value = Trim(db)
(db) it is highlighted

0
 
Whing Dela CruzAuthor Commented:
Oh finally I've got it, thanks so much.. I'm very beginner with ...
I hope you understand.. More Power and God Bless!!!
0
All Courses

From novice to tech pro — start learning today.