Solved

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

Posted on 2007-11-16
5
835 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 62

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 62

Accepted Solution

by:
Fernando Soto earned 500 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 62

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 62

Expert Comment

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

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Visual Studio - "Windows Form Designer generated code" 2 39
Please explain "Multi-Tenant Services" 5 62
Visual Studio 2015 Source Code Control 10 29
Variable Event ? 3 22
Welcome my friends to the second instalment and follow-up to our Minify and Concatenate Your Scripts and Stylesheets (http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/A_4334-Minify-and-Concatenate-Your-Scripts-and-Stylesheets.html)…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Edureka is one of the fastest growing and most effective online learning sites.  We are here to help you succeed.

911 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

17 Experts available now in Live!

Get 1:1 Help Now