lawboy

Hello,

I would like to know the proper approach to sum a column in my sqlmobile table and display that total in a textbox on my form using vb.net TIA.

Here is a snippet of what I have been testing with but without success.

Dim sqlConn As SqlCeConnection = Nothing

sqlConn = New SqlCeConnection("Data Source =\SDMMC\test.sdf")

Dim insertCommand As SqlCeCommand = sqlConn.CreateCommand()

sqlConn.Open()

Dim SqlString As String = "SELECT SUM(QTY) FROM tblTest"

Dim select_cmd As New SqlCeCommand(SqlString, sqlConn)

Dim reader As SqlCeDataReader = select_cmd.ExecuteReader() ' I recieve error on this statement

While reader.Read

txtqty.Text = reader.ToString

End While

sqlConn.Close()



Re: Smart Devices VB and C# Projects Sum SQlMobile Column

Ilya Tumanov

Which error Make sure to print errors from error collection if you got SqlCeException, see SQL CE books online for a sample code if you don't know how to do that.

Also you should probably use ExecuteScalar() to do that.






Re: Smart Devices VB and C# Projects Sum SQlMobile Column

lawboy

Hello Ilya,

The error message is:

The specified argument value for the function is not valid. [ Argument # = 1,Name of function(if known) = SUM ]

Thanks





Re: Smart Devices VB and C# Projects Sum SQlMobile Column

lawboy

After much testing, I have finally achieved the desired results with the following snippet. Here it is in case anyone else needs it.

Dim sqlConn As SqlCeConnection = Nothing

sqlConn = New SqlCeConnection("Data Source =\SDMMC\test.sdf")

Dim insertCommand As SqlCeCommand = sqlConn.CreateCommand()

sqlConn.Open()

Dim SqlString As String = "SELECT SUM(qty) AS QTYSUM FROM tblTest"

Dim select_cmd As New SqlCeCommand(SqlString, sqlConn)

Dim reader As SqlCeDataReader = select_cmd.ExecuteReader

Try

While reader.Read()

txtqty.Text = reader.GetValue(0).ToString()

End While

If txtqty.Text = "" Then

txtqty.Text = 0

End If

Finally

reader.Close()

sqlConn.Close()

End Try

End Sub