[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 282
  • Last Modified:

Calling a SQL Sever query returns in correct value

I have a query on a MS SQL Server called vMaxID that has this code:

SELECT     MAX(RefID) AS Expr1
FROM         dbo.Referees

When I run the query in SQL Server; it returns the maximum value in that field, however, when I call the query in VB.NET, I get a different number.  

The data in the field has previously been generated by a MS Access program that takes the max value and adds 7 to it to get the new RefID.  I'm trying to code a complimentary app that does the same thing, but the max value I get in my vb code is where the first blank in the series is, not the maximum of the field.  (i.e. if the data in the column is 1, 8, 15, 22; my code returns 1 while the query in SQL server returns 22).

My vb.net code is this:

    Public Shared cn As SqlConnection
    Public Shared da As SqlDataAdapter
    Public Shared ds As DataSet
    Public Shared sConn As String 'connection string info

addDataBind(sConn, "SELECT * FROM vMaxID", "getID")

Shared Sub addDataBind(ByVal connString As String, ByVal sqlStatement As String, ByVal tableName As String)
        cn = New SqlConnection(connString)
        cn.Open()
        da.SelectCommand = New SqlCommand(sqlStatement, cn)
        da.Fill(ds, tableName)
        cn.Close()
        cn.Dispose()
    End Sub

I need to get the max value from the RefID column; not fill in the holes in the series.

Thanks!
0
dhk3140
Asked:
dhk3140
  • 5
  • 2
  • 2
  • +3
2 Solutions
 
RikeRCommented:
Why not creating a stored procedure and passing the  MAX(RefID) as an output

example:
IF OBJECT_ID('proc_sel_vMaxID') IS NOT NULL
      DROP PROCEDURE proc_sel_vMaxID
GO
CREATE PROCEDURE dbo.proc_sel_vMaxID (@vMaxID bigint OUTPUT) AS
      SET NOCOUNT ON
      SELECT  MAX(RefID) FROM dbo.Referees
      SET NOCOUNT OFF
GO

Then execute this query using the following example:
    Public Function proc_sel_vMaxID() As Long
        Dim tempIDValue As Long
        Dim MyConn As New SqlConnection(YOURCONNECTIONSTRING)

        Dim MyCommand As New SqlCommand
        MyCommand.CommandText = "proc_sel_vMaxID"
        MyCommand.Connection = MyConn
        MyCommand.CommandType = CommandType.StoredProcedure

        Dim Param As New SqlParameter
        Param.ParameterName = "@vMaxID"
        Param.Direction = ParameterDirection.Output
        Param.SqlDbType = SqlDbType.BigInt

        MyCommand.Parameters.Add(Param)


        MyCommand.Connection.Open()
        MyCommand.ExecuteNonQuery()
        MyConn.Close()
        If Param.Value Is System.DBNull.Value Then
            tempIDValue = 0
        Else
            tempIDValue = Param.value
        End If

        MyConn = Nothing
        MyCommand = Nothing
        Param = Nothing
        Return tempIDValue
    End Function
0
 
dhk3140Author Commented:
RikeR:

I copied and pasted your code in place of my code, and it returns the same thing as my view.

Thanks.
0
 
dhk3140Author Commented:
Just to clarify, it's not the result I need.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
ctm5Commented:
How about something like:

SELECT MAX FROM (SELECT RefID FROM dbo.Referees) AS Expr1

not sure of the syntax, and I haven't tested this at all (i.e., I'm guessing) but something along these lines should work.
0
 
mastooCommented:
Going back to your original query, how about trying this:

SELECT  MAX( CONVERT(INT, RefID)) AS Expr1
FROM         dbo.Referees

0
 
dhk3140Author Commented:
ctm5:
   I'm not sure how to make this work.

mastoo:
  Works the same as my original query.

0
 
dhk3140Author Commented:
Some additional info:

Calling the query in the vb code, I get a variable number of records returned.  For example:  Max returned was 38.  I got a recordset of 50 records with a field value of 38 in each one, except  the 50th record was blank.  I've not tested it to see if it is a empty string or null value.  I'll be playing with it later tonight.

Thanks all.
0
 
HavaganCommented:
I have a few questions:

1) What is the datatype of the RefID field? MAX() only works properly on numeric values. i.e.

RefID
----------
1
8
15
22

RefID as integer -- MAX(RefID) returns 22
RefID as nvarchar -- MAX(RefID) returns 8

2) So what your VB.NET code should do is find the current max and add 7 to it?

Why use a dataset? That's a lot of overhead just to return an integer.

Dim newRefID as Integer = Convert.ToInt32(sqlcommand.ExecuteScalar("SELECT ...")) + 7

3) Where in your code are you accessing the value returned in your dataset?

I don't see anything to the effect of: newRefID = Convert.ToInt32(ds.Tables(0).Rows(0)....) + 7

Paul



0
 
grayeCommented:
This whole thing doesn't make any sense to me....

It shouldn't matter how the SQL Server View was called, or what program called it.   The *VIEW* itself should always return the same value.

The MAX() aggregate function is being executed on the sql server, not in your code.   So, no amount of programming on your part is gonna have any effect on it's behavior.
0
 
HavaganCommented:
graye,

I agree. Chances are good the view is returning the proper value. SELECT MAX() FROM ... isn't exactly complex. He states he gets a 50 row recordset back when MAX() should return a single row containing a single column. I'm thinking the problem is in his .NET code.

Paul
0
 
grayeCommented:
Hummm.... I wonder if the DataTable already exists... and has data in it.

The Fill() method of the DataAdapter will attempt to "synchronize" with the datasource by adding new data and updating existing data.   So if your code was expecting to look at the first row (for what should have been an ExecuteScalar), it would get bogus data left over in the DataTable.

Try this immediately before the Fill method as a test

ds.Tables(tableName).clear()
0
 
dhk3140Author Commented:
graye & Hayagen:

You guys are sanity savers!

Thanks!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now