Query error

jeva39

Well-known member
Joined
Jan 28, 2005
Messages
135
Location
Panama
Programming Experience
1-3
I use this Query in a Access DataBase:

VB.NET:
[/color]
[/color][/size][/font][size=2][font=Verdana]dta = [/font][/size][font=Verdana][size=2][color=#0000ff]New[/color][/size][size=2]  OleDbDataAdapter("Select sum(saldo) as SALDOS from Clientes", con)

And I receive this error:

Syntax error (missing operator) in Query expression 'Sum(Saldo)'

The error is equal if I use CommandText or another thing
Please, what is the problem?

Thanks....
 

kulrom

Well-known member
Joined
May 10, 2005
Messages
2,854
Location
Republic of Macedonia
Programming Experience
10+
jeva39 said:
I use this Query in a Access DataBase:

VB.NET:
[/color]
[/color][/size][/font][size=2][font=Verdana]dta = [/font][/size][font=Verdana][size=2][color=#0000ff]New[/color][/size][size=2] OleDbDataAdapter("Select sum(saldo) as SALDOS from Clientes", con)

And I receive this error:

Syntax error (missing operator) in Query expression 'Sum(Saldo)'

The error is equal if I use CommandText or another thing
Please, what is the problem?

Thanks....



try this:
Dim strSQL As String = "Select sum(saldo) as "SALDOS" from Clientes"
dta = New OleDbCommand(strSQL, con)

Happy coding :cool:
 

jeva39

Well-known member
Joined
Jan 28, 2005
Messages
135
Location
Panama
Programming Experience
1-3
Thanks for your answer but don't work because I need the DataAdapter and DataSet to fill a Datagrid. Regards,
 

kulrom

Well-known member
Joined
May 10, 2005
Messages
2,854
Location
Republic of Macedonia
Programming Experience
10+
jeva39 said:
Thanks for your answer but don't work because I need the DataAdapter and DataSet to fill a Datagrid. Regards,
Oh ... sorry i haven't noted that you are working with dataset class ... but it's still the same error ... you ommited double quotation and that's why your code doesn't work properly ...

dta = New OleDbDataAdapter("Select sum(saldo) as "SALDOS" from Clientes", con)
Kind regards ;)
 

jeva39

Well-known member
Joined
Jan 28, 2005
Messages
135
Location
Panama
Programming Experience
1-3
Really this error can me conver in a crazy man :).. I have one week trying all the possibilities but...NOTHING!! And I need the solution because the value of this Sum in table CLIENTES (Clients) I need to assign in a variable (defined Public in a a Module) and use in the Table PRESTAMOS (Loans)

I try your code in three ways:

1- dta = New OleDbDataAdapter("Select sum(saldo) as "SALDOS" from Clientes", con)

' This double quotation in SALDOS don't work because the SELECT is enclosed in a double quotation

2- dta = New OleDbDataAdapter("Select sum(saldo) as 'SALDOS' from
Clientes", con)

3-dta = New OleDbDataAdapter("Select sum(saldo) from Clientes", con)

Result: the same error in the three options :-(

The curious thing is: if you use the Query Analyzer of Sql Server 2000 the query work OK!!!! Strange, right?

I think that Access Database have anything that produce this error but I don´t find the cause. I always use Sql Databases but for this application the machine where the application must be installed don´t have the Sql Server and the people of Caritas Panamá can´t buy the license and only have Windows 98. Resuming: Sql can create for them many problems. Is the reason for select Access. And finally, I don't have the time to change all the application to SQL system (9 Tables!!! and your relations!!!) That's is the situation...

A last thing: in all the manuals I have of SQL the syntax for Aggregate Functions (I.E sum) is the same that you and me use

Really thanks for your intentions and your help....
 

jeva39

Well-known member
Joined
Jan 28, 2005
Messages
135
Location
Panama
Programming Experience
1-3
This is the complete code I use in the routine that I can get the vaules of SALDOS sum:

VB.NET:
...All the variables (dta, dst, con,oCB) are declared......

Private Sub btnSaldos_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSaldos_click
	Try
	 Me.con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Application.StartupPath & "\caritas.mdb"
	  dta = New OleDbDataAdapter("Select * sum(saldo) as 'SALDOS' from Clientes", con)
	  Dim oCB As OleDbCommandBuilder = New OleDbCommandBuilder(dta)
	  dst = New DataSet
	  dta.Fill(dst, "Clientes")
	  Me.myGrid.DataSource = dst
	  Me.myGrid.DataMember = "Clientes"
	 Me.myGrid.CaptionText="Estado de Cuenta"
	  con.Close()
	Catch eUpdate As System.Exception
	  System.Windows.Forms.MessageBox.Show(eUpdate.ToString())
	End Try
  End Sub
To jmcilhinney:

I try with square brackets but nothing! The error is the same : ...(missing operator) in 'sum(Saldo)' (Saldo is a Number field, obviously...)

I am very near of a crazy state :eek:

Thanks...
 

kulrom

Well-known member
Joined
May 10, 2005
Messages
2,854
Location
Republic of Macedonia
Programming Experience
10+
I'm not sure why you insist on dataset for so small data but anyway it's up to you ... you are the boss ... your code should look like:

VB.NET:
Private[/color][/size][size=2][color=#0000ff]Sub[/color][/size][size=2] Button2_Click([/size][size=2][color=#0000ff]ByVal[/color][/size][size=2] sender [/size][size=2][color=#0000ff]As[/color][/size][size=2] System.Object, [/size][size=2][color=#0000ff]ByVal[/color][/size][size=2] e [/size][size=2][color=#0000ff]As[/color][/size][size=2] System.EventArgs) [/size][size=2][color=#0000ff]Handles[/color][/size][size=2] Button2.Click
[/size][size=2][color=#0000ff]Try
 
[/color][/size][size=2]oledbcon.Open()
 
da = [/size][size=2][color=#0000ff]New[/color][/size][size=2] OleDbDataAdapter("SELECT SUM(saldo) As SALDOS from Clientes", con)
 
[/size][size=2][color=#0000ff]' Dim[/color][/size][size=2] oCB [/size][size=2][color=#0000ff]As[/color][/size][size=2] OleDbCommandBuilder = [/size][size=2][color=#0000ff]New[/color][/size][size=2] OleDbCommandBuilder(da) *****
 
ds = [/size][size=2][color=#0000ff]New[/color][/size][size=2] DataSet
 
da.Fill(ds, "SALDOS")
 
[/size][size=2][color=#0000ff]Me[/color][/size][size=2].myGrid.DataSource = ds
 
[/size][size=2][color=#0000ff]Me[/color][/size][size=2].myGrid.DataMember = "SALDOS"
 
[/size][size=2][color=#0000ff]Me[/color][/size][size=2].myGrid.CaptionText = "Estado de Cuenta"
 
oledbcon.Close()
 
[/size][size=2][color=#0000ff]Catch[/color][/size][size=2] eUpdate [/size][size=2][color=#0000ff]As[/color][/size][size=2] System.Exception
 
System.Windows.Forms.MessageBox.Show(eUpdate.ToString())
 
[/size][size=2][color=#0000ff]End[/color][/size][size=2][color=#0000ff]Try
 
[/color][/size][size=2][color=#0000ff]End[/color][/size][size=2][color=#0000ff]Sub
Happy coding ;)

i also attached the project for any case ... :)
 

Attachments

Last edited:

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,322
Location
Sydney, Australia
Programming Experience
10+
I notice that in your last post, jeva39, that you are using "Select * sum(saldo)..." and a CommandBuilder. In your previous posts you have not included the *. If you are retrieving just the sum of the saldo field, there is no way you can use a CommandBuilder to update your table as you cannot update an aggregated field. If you are retrieving other fields, you need to specify them explicitly and include each in a GROUP BY clause. You cannot use a * with aggregate functions because you must include a GROUP BY clause and you cannot group by fields that have not been specified explicitly. Here is an example of what I mean:
VB.NET:
"SELECT field1, field2, SUM(saldo) FROM Clientes GROUP BY field1, field2"
 

jeva39

Well-known member
Joined
Jan 28, 2005
Messages
135
Location
Panama
Programming Experience
1-3
Thanks but this not work simply because SALDOS is not a Table in Caritas.Mdb. Another thing, not is a "Small Data". At the moment Caritas have more that 2500 users and any Users have Loans,Payments, Projects, Communities, and many, many more data. And finally, the code allways stop and give the error in the line of SELECT, doesn´t matter what occurs in the rest of code

I am very gratefull for your help but I lost many time and my decision is a modification in the tables and, for now, forget this problem. Caritas need urgent this application. Thanks again but the better way is closing definitively this topic.

Regards,
 

kulrom

Well-known member
Joined
May 10, 2005
Messages
2,854
Location
Republic of Macedonia
Programming Experience
10+
hey ... wait a minute don't give up on this ... i'm pretty sure we'll find solution ... first tell me did you get the attached file i made for you ... i think it works right like you want ... btw, SALDOS is not the table but it takes its value/s ... so you cannot fill dataset with table if you put this in the SUM function ... stay calm ... and download the last project within post No. 10 ... Kind regards :)
 

jeva39

Well-known member
Joined
Jan 28, 2005
Messages
135
Location
Panama
Programming Experience
1-3
jmcilhinney:

Please forget the sign *. This is an "finger error" :-( I don't know why I include the sign in the sent code.

If you see my code above, I don't use the * and in my real code the * don't exists. But your suggestion are very valid and I try to see the results. Thanks :)
 

jeva39

Well-known member
Joined
Jan 28, 2005
Messages
135
Location
Panama
Programming Experience
1-3
jmcilhinney:

GOAL!!!!!!! Your solution solve all the problem and the code work fine. Really thank you very much!!!! Only a last question: in a Module I have defined a Public Variablle to manage the result of SALDOS (Sum). Please, how I can do to assign this value to the variable?

kulrom:

I think you are wrong in your apreciation about me. I don't never ignore a person that help me or at least try to help me. Simply, jmcilhinney send me the correct solution. Believe me or not, I am very gratefull with you. Sorry if you think thus about me. But if you are angry or very angry with me, please ignore all my posts :)
 

jeva39

Well-known member
Joined
Jan 28, 2005
Messages
135
Location
Panama
Programming Experience
1-3
kulrom:

Only now I have the time to see your examples in the Zips. Thanks!. A great help for me. I hope you now don't angry or at least less angry with me.. :)

Regards
 

kulrom

Well-known member
Joined
May 10, 2005
Messages
2,854
Location
Republic of Macedonia
Programming Experience
10+
You see ... i'm not angry i just want to help you but you ignored my posts with attached adequate solution for your problem ... that's it.
If you get second SUM.zip file you'll see that certain solution works as you wanted ... but i'm still wondering why you want to use datagrid for this purpose .... anyhow, i hope we'll have not misunderstods like this in the future ... Cheers :)
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,322
Location
Sydney, Australia
Programming Experience
10+
jeva39 said:
jmcilhinney:

GOAL!!!!!!! Your solution solve all the problem and the code work fine. Really thank you very much!!!! Only a last question: in a Module I have defined a Public Variablle to manage the result of SALDOS (Sum). Please, how I can do to assign this value to the variable?
If you just want to retrieve an aggregated value to a variable I would suggest using the ExecuteScalar() function of the OleDbCommand class like this:
VB.NET:
[color=Blue]Dim[/color] saldosSumCommand [color=Blue]As New[/color] OleDbCommand("SELECT SUM(saldo) FROM Clientes", con)
[color=Blue] Dim[/color] totalSaldos [color=Blue]As Integer[/color]

[color=Blue] Try[/color]
	con.Open()
	totalSaldos = saldosSumCommand.ExecuteScalar()
[color=Blue] Catch[/color]
	[color=Green]'...[/color]
[color=Blue] Finally[/color]
	con.Close()
[color=Blue] End Try[/color]
I hope this is helpful.
 

jeva39

Well-known member
Joined
Jan 28, 2005
Messages
135
Location
Panama
Programming Experience
1-3
Really your solution in both Sums Zips are very good. Tomorrow I go to study my code and change somethings to Command and Datareader. I admit that I am a big fan of Datasets but allways exists a first time to change things in the life :)

Thanks again and please keep in mind that all your posts and the posts of all people that help me was very, very importants for me. This Forum is the best thing I find in the Net!!! But I have a relative difficult to express my ideas because my English is not THE BEST OF THE WORLD........:-(

Regards..
 
Top Bottom