query database

[adeline]

what is the syntax to calculate all the numbers(total) tht is in the db to out put in a text box?
example in the db column there is values 5,5,5
how to out put in a text box the total = 15? what is the query?

[Strider]

Select SUM(<columnName>) FROM <TableName>
eg: Select SUM(cost) FROM tblSales

or

Do you mean you are storing the values "5,5,5" in the one column for a row in the database

[adeline]

'Create a connection to the new database.
Dim ssceconn As New SqlCeConnection("Data Source = \My Documents\test.sdf")
'ssceconn.Open()

'Initialize DataSet and SqlCeConnection.
dsNamePhone = New DataSet

'daName phone is a data adaptor
daNamePhone = New SqlCeDataAdapter("SELECT f_name from People", ssceconn)
'fills dataset with only results from string
daNamePhone.Fill(dsNamePhone, "People")

'bind to textbox
txt.DataBindings.Add("Text", dsNamePhone.Tables("People"), "f_name")

where do i put the sum coding in?
for this coding the out put is just the first row name.

[Strider]

'daName phone is a data adaptor
daNamePhone = New SqlCeDataAdapter("SELECT f_name, SUM(columnname) As total from People", ssceconn)
'fills dataset with only results from string
daNamePhone.Fill(dsNamePhone, "People")

'bind to textbox
txt.DataBindings.Add("Text", dsNamePhone.Tables("People"), "f_name")
txt1.DataBindings.Add("Text", dsNamePhone.Tables("People"), "total")




BUT

For queries that return a value i would recommend using the SQLCommand and ExecuteScalar

EG:
Dim result As Integer
Dim sqlstmt As String = "SELECT SUM(colname) FROM People"
Dim cmd As SqlCeCommand= New SqlCeCommand(sqlstmt,ssceconn)
result = cmd.executescalar
textbox1.text = result.tostring

[adeline]

just use this coding and it will output the result in the textbox?

[Strider]

The ExecuteScalar is used when you want to return a single value from a SQL statement.
i.e: SELECT Count(), Select MAX(), Select MIN(), Select SUM(), Select f_name FROM... Where

so if you just want the SUM value use the second option.

If you want more values that the sum return use the dataset

[adeline]

i do not know how to use ExecuteScalar command. i do not know where to put and what else i need in it. can u recomment any website that show a brief way to use this command? thanks !

[Strider]

I just showed you how to use it in the Sample above...

Come on have some inititive here...

You can see above ExecuteScalar is part of the SqlCeCommand Class

So go to www.google.com enter in SqlCeCommand and you will find a site that will explain it to you (1st site that comes up is http://samples.gotdotnet.com/quickst...izedquery.aspx)

http://www.dotnetspider.com/tutorials/
http://www.amazon.com/gp/product/186...lance&n=283155

[adeline]

there is where i get most of the coding for my project. i used tht site for my input to the database and output. but i could not see the executescalar in there.

[Strider]

I have given you a sample already, here it is again

Dim result As Integer
Dim sqlstmt As String = "SELECT SUM(colname) FROM People"
Dim cmd As SqlCeCommand= New SqlCeCommand(sqlstmt,ssceconn)
result = cmd.executescalar
textbox1.text = result.tostring

EXECUTESCALAR is a method of SQLCECOMMAND...
SQLCECOMMAND IS SOMETHING YOU NEED TO KNOW

here is a webpage all about the SQLCommand (the full framework version)
http://www.csharp-station.com/Tutori.../Lesson03.aspx

the samples are in c#, surely you will be able to understand them

[Strider]

i'll be proactive and presume you'll ask for them in vb.net so here is a link to a c# converter
http://kamalpatel.net/ConvertCSharp2VB.aspx

[adeline]

"SELECT SUM(nec_amo) where nec_type ='Food' As total from nec"
is this statement correct?

[Strider]

have you learned SQL before? it doesn't seem like it

http://www.w3schools.com/sql/default.asp

the where clause is only used after the tablename

SELECT SUM(nec_amo) As total FROM nec WHERE nec_type= 'Food'