SQL Query Syntax

The following works, returns the correct count:
           Dim BsWt_n1 As Integer
            Dim conn1 As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\Process1\db\Main Application.mdb")
            Dim qry1 As String
            qry1 = "SELECT COUNT(BsWtAvg) FROM tblProd2"
            Dim cmd1 As New OleDb.OleDbCommand(qry1, conn1)
            conn1.Open()
            BsWt_n1=cmd1.ExecuteScalar()
            conn1.Close()
            TextBox1.Text = BsWt_n1

The following does not work, returns a count of 0:
            Dim BsWt_n1 As Integer
            Dim conn1 As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\Process1\db\Main Application.mdb")
            Dim qry1 As String
            qry1 = "SELECT COUNT(BsWtAvg) AS [BsWt_n1] FROM tblProd2"
            Dim cmd1 As New OleDb.OleDbCommand(qry1, conn1)
            conn1.Open()
            cmd1.ExecuteScalar()
            conn1.Close()
            TextBox1.Text = BsWt_n1

Since I need to include several other aggregate functions (Avg, StDev, Min, Max, etc.) I need to find out how the latter example needs to be constructed correctly to allow each result to be assigned to a variable.

Can someone provide an example?
Tim313Asked:
Who is Participating?
 
Tim313Connect With a Mentor Author Commented:
Got the results needed by using a dataset.
0
 
Patrick MatthewsCommented:
In your second example, shouldn't:

           cmd1.ExecuteScalar()

be:

           BsWt_n1=cmd1.ExecuteScalar()

?  In that second example, looks to me like you never actually populated the variable, and so it took the intiialized value of 0.
0
 
Tim313Author Commented:
Possibly, but how would I construct it if I were to have the following statement?:

qry1 = "SELECT COUNT(BsWtAvg) AS [BsWt_n1], AVG(BsWtAvg) AS [AvgBsWt], StDev(BsWtAvg) AS [DevBsWtAvg] FROM tblProd2"

0
 
Tim313Author Commented:
Found correct method to do what I needed.
0
All Courses

From novice to tech pro — start learning today.