Solved

SQL Query Syntax

Posted on 2011-09-09
4
297 Views
Last Modified: 2012-05-12
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?
0
Comment
Question by:Tim313
  • 3
4 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36509927
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
 

Author Comment

by:Tim313
ID: 36510141
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
 

Accepted Solution

by:
Tim313 earned 0 total points
ID: 36510753
Got the results needed by using a dataset.
0
 

Author Closing Comment

by:Tim313
ID: 36534677
Found correct method to do what I needed.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Suggested Solutions

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now