CODEWITHSUNDEEP
sqlvb.net
Supriya Honawale
Supriya Honawale

Reputation: 11

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

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

Upvotes: 0

Views: 3479

Answers (1)

Joel Coehoorn
Joel Coehoorn

Reputation: 416131

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

Upvotes: 2

Related Questions