Query error

[jeva39]

I use this Query in a Access DataBase:

[/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]

I use this Query in a Access DataBase:

[/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

 

[jeva39]

Thanks for your answer but don't work because I need the DataAdapter and DataSet to fill a Datagrid. Regards,

 

[kulrom]

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]

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....

 

[jmcilhinney]

I use Access quite a bit and have not had any issues with aggregate functions, including SUM. Your syntax looks OK as far as I can tell.

 

[kulrom]

Ok this is an example that works with SUM function ... btw, you could send the code you are working with as we could find the misstake '.. i hope
Cheers

 

[jmcilhinney]

I've tried querying an Access database with the same syntax as you're using and had no issue. The only thing I can think of to try is putting square brackets around the field name.

 

[jeva39]

This is the complete code I use in the routine that I can get the vaules of SALDOS sum:

...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

Thanks...

 

[kulrom]

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:

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 ...

 

[jmcilhinney]

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:

"SELECT field1, field2, SUM(saldo) FROM Clientes GROUP BY field1, field2"

 

[jeva39]

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]

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]

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

 

[kulrom]

Hey are you trying to ignore me or it seems to me like that ?
Ok, it's up to you ... i'm just trying help you out