Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Retrieving a Max value from a table and storing in VB.net

Posted on 2007-11-16
5
Medium Priority
?
852 Views
Last Modified: 2013-11-26
My SQL statement looks like this
select max(SEQ_NO) AS expr1 where fld1 = 'strFld1'.  According to the data this should return a number from 01 to 99, or null.  I can execute the query separate from the program and it works.  I need to know how to return the single value to process it.  I am used to returning the values to a dataset and placing in a grid.  I have 2 problems:  It is reurning a 0 or null that produces an error when I try to return it t to a dataset.  2nd.  How may I best refer to just one value returned from the query?  
0
Comment
Question by:garyinmiami2003
  • 4
5 Comments
 
LVL 64

Expert Comment

by:Fernando Soto
ID: 20304360
Can you post a section of code showing how you are attempting to get the results?
0
 
LVL 64

Accepted Solution

by:
Fernando Soto earned 2000 total points
ID: 20304585
Hi garyinmiami2003;

Here is sample code using the Northwind database from SQL Server Express using the orders table and finding the max cost of shipping for customer HANAR.
Imports System.Data.SqlClient
 
        Dim connStr As String = "Data Source=localhost\sqlexpress; Initial Catalog=Northwind; Integrated Security=SSPI"
        Dim conn As New SqlConnection(connStr)
        Dim cmd As SqlCommand = conn.CreateCommand()
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "SELECT MAX(Freight) AS exprt1 FROM Orders WHERE CustomerID = 'HANAR'"
        conn.Open()
        Dim money As Decimal = CDec(cmd.ExecuteScalar())
        conn.Close()
        MessageBox.Show(money.ToString("C"))

Open in new window

0
 
LVL 64

Expert Comment

by:Fernando Soto
ID: 20304607
Hi garyinmiami2003;

Your SQL select statement needs a FROM clause to identify the table

select max(SEQ_NO) AS expr1 FROM TableName where fld1 = 'strFld1'

Fernando
0
 

Author Comment

by:garyinmiami2003
ID: 20307396
Dim money As Decimal = CDec(cmd.ExecuteScalar())


Was the statement I needed.  Fernando, Thanks again for the high quality assistance
0
 
LVL 64

Expert Comment

by:Fernando Soto
ID: 20307520
Not a problem, glad I was able to help. ;=)
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

972 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