How do I retrieve a value from an SQL query and store it in a variable in VB.NET?

Question

I am trying to find the max product ID and store the value in a local variable "MaxID" and return this value. I am trying to convert the result of the query into an Integer type but I am not able to do it. Below is the code:

Public Function GetMaxID(ByVal TableName As String, ByVal ID As String) As Integer
        Dim MaxID As Integer
        Dim sqlquery As SqlCommand
        Dim field_name As String = ID
        Dim con As SqlConnection
        con = New SqlConnection()
        con.ConnectionString = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename='D:\Docs Dump\Work\Srinath\SrinathDB.mdf';Integrated Security=True;Connect Timeout=30"
        con.Open()
        Try
            sqlquery = New SqlCommand("SELECT MAX( @field ) FROM @table ", con)
            sqlquery.Parameters.AddWithValue("@field", field_name)
            sqlquery.Parameters.AddWithValue("@table", TableName)
            MaxID = CInt(sqlquery.ToString)
            con.Close()
            Return MaxID
        Catch ex As Exception
            Return 0
            Exit Function
            con.Close()
        End Try
    End Function
End Class

You should call ExecuteScalar from the command instance and then get its result — Steve
– Steve, Commented Nov 26, 2019 at 17:51
You cannot use parameters to define fields or table names sure you can and then use them in dynamic sql, but in this case, no. — Trevor
– Trevor, Commented Nov 26, 2019 at 20:20
Why do you want Max ID? If you are wanting to get the next ID for your next Insert. DO NOT DO IT THIS WAY!! Think about a multi user database. There could be 5 new records inserted by other users before your code gets around to inserting. Let the database do the work with an auto increment. Or Select SCOPE_IDENTITY if you need it for a foreign key. — Mary
– Mary, Commented Nov 27, 2019 at 3:37

Joel Coehoorn · Accepted Answer · 2019-11-26 21:33:38Z

2

MaxID = CInt(sqlquery.ExecuteScalar())

You also should know about SqlCommand.ExecuteReader(), SqlCommand.ExecuteNonQuery() (for inserts/updates/deletes), and SqlDataAdapter.Fill().

Where you'll still have a problem is you can't use a parameter value for the table name or column name. The Sql Server engine has a "compile" step, where it has to be able to work out an execution plan, including permissions/security, at the beginning of the query, but variable names like @table and @field aren't resolved until later. It's not what actually happens, but think of it as if you had string literals in those places; imagine trying to run this:

SELECT MAX('ID') FROM 'MyTable'

MAX('ID') will always return the string value ID, and not anything from an ID column in any rows. But the MyTable part is not the correct place for a string literal, and such a query wouldn't even compile.

I also see people here from time to time try to create functions like GetMaxId(), and it's almost always misguided in the first place. If the intended use for this function is the same as what I usually see, you're setting up a major race condition issue in your application (one that probably won't show up in any testing, too). Sql Server gives you features like identity columns, sequences, and the scope_identity() function. You should be using those in such a way that new IDs are resolved on the server as they are created, and only (and immediately) then returned to your application code.

But that issue aside, here's a better way to structure this function:

Public Class DB
    Private conString As String = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename='D:\Docs Dump\Work\Srinath\SrinathDB.mdf';Integrated Security=True;Connect Timeout=30"

    'You want a separate method per-table that already knows the table and column names
    Public Function GetMyTableMaxID() As Integer
        Dim sql As String = "SELECT MAX(ID) FROM MyTable"

        Using con As New SqlConnection(conString), _
              sqlQuery As New SqlCommand(sql, con)

            'Parameters would go here. 
            'Do NOT use AddWithValue()! It creates performance issues.
            ' Instead, use an Add() overload where you provide specific type information.

            'No exception handling at this level. The UI or business layers are more equipped to deal with them
            con.Open()
            Return CInt(sqlQuery.ExecuteScalar())

        End Using
        'No need to call con.Close()
        'It was completely missed in the old code, but handled by the Using block here
    End Function
End Class

edited Nov 26, 2019 at 21:33

answered Nov 26, 2019 at 17:51

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Steve Over a year ago

There is also a very confused exit from the Catch block

Supriya Honawale Over a year ago

I understand but this is an assignment program, hence, I have to generate an ID manually. Thanks. I still have a doubt, will ExecuteScalar() return the Max(Product_ID) ?

Joel Coehoorn Over a year ago

@SupriyaHonawale No, it won't, because of what I talked about in the paragraph beginning "Where you'll still have a problem"

Supriya Honawale Over a year ago

Hey, its working, ExecuteScalar() does return the Max(Product_ID).

Collectives™ on Stack Overflow

How do I retrieve a value from an SQL query and store it in a variable in VB.NET?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related