Solved

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

Posted on 2007-11-16
5
841 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
5 Comments
 
LVL 63

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 63

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 63

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 63

Expert Comment

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

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

632 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