Storing SQL Query in Variable??

[tehon3299]

Alright guys -

I am looking to query a table in my database in SQL and return the value that it finds into a variable. I am saying:

 				SQL="SELECT Balance FROM tmTransactions WHERE UserRecord = " & user & ""

. This will ALWAYS only return ONE value. How can I make it so the value that it finds can be stored in a variable? Then I am going to put that variable in my global.asax file??

Thanks!

 

[Robby]

You can change some of the variables to hard-coded if you wish...

    Private Function GetTableValue(ByVal nID As Integer, ByVal sField As String, ByVal sTable As String) As Integer
        Dim drSqlReader As SqlDataReader
        Dim SqlCMD As SqlCommand
        Dim SqlCN As New SqlConnection(Conn)
        Dim strSql As String, intTemp As Integer
        Try
            If SqlCN.State = ConnectionState.Closed Then SqlCN.Open()
            strSql = "SELECT " & sField & " FROM " & sTable & " WHERE ID = " & nID
            SqlCMD = New SqlCommand(strSql, SqlCN)
            drSqlReader = SqlCMD.ExecuteReader()

            While drSqlReader.Read
                intTemp = CType(Nz(drSqlReader.Item(sField), ""), Integer)
            End While
        Catch
            intTemp = -1
        Finally
            If Not SqlCN.State = ConnectionState.Closed Then SqlCN.Close()
            If Not drSqlReader.IsClosed Then drSqlReader.Close()
            If Not SqlCMD Is Nothing Then SqlCMD.Dispose()
        End Try
        Return intTemp
    End Function

 

[tehon3299]

Well what I am trying to do is query my table for a username in the table and I want to store that username in a variable say, User. How can I set the return value of the query to my variable??

Thanks

 

[tehon3299]

What is Nz in the While drSqlReader.Read??

Thanks

 

[quwiltw]

Its a function that evaluaties the parameter to see if its null, and if it is, it returns the value of the second parameter.

 

[tehon3299]

Is this function already written??

 

[quwiltw]

I dont think so in VB.NET its built in to Access. My guess is that Robby has it in some utility module that hes implemented himself. Otherwise maybe Ive just not found it in .NET yet. Should be simple enough to write though.

 

[tehon3299]

Is there an easier way to get information out of an SQL table and store it in variables??

 

[Robby]

Sorry about the NZ(), its my own function.

 

[tehon3299]

Is it really necessary??

 

[Robby]

try this...

Private Function GetTableValue(ByVal strVal As String, ByVal sField As String, ByVal sTable As String) As string
        Dim drSqlReader As SqlDataReader
        Dim SqlCMD As SqlCommand
        Dim SqlCN As New SqlConnection(Conn)
        Dim strSql As String, sTemp As string =""
        Try
            If SqlCN.State = ConnectionState.Closed Then SqlCN.Open()
            strSql = "SELECT " & sField & " FROM " & sTable & " WHERE User = " & strVal & ""
            SqlCMD = New SqlCommand(strSql, SqlCN)
            drSqlReader = SqlCMD.ExecuteReader()

            While drSqlReader.Read
                sTemp = drSqlReader.Item(sField)
            End While
        Catch
           sTemp = "error found"
        Finally
            If Not SqlCN.State = ConnectionState.Closed Then SqlCN.Close()
            If Not drSqlReader.IsClosed Then drSqlReader.Close()
            If Not SqlCMD Is Nothing Then SqlCMD.Dispose()
        End Try
        Return sTemp
    End Function

and call it like this...
dim sResults as string = GetTableValue("somename","User", "tmTransactions")
messagebox.show(sResults)

 

[tehon3299]

Will messagebox.show(sResults) work on an ASPX page??

 

[Robby]

The NZ was to check if the field is null so you dont get an error, once you get this working and understand it, Ill spring that on you. (not that its very complicated)

 

[tehon3299]

Alright I see. Does that messagebox.show show a message box on an ASPX page?

 

[Robby]

messagebox.show will not show on an aspx, step-through your code or use response.write(sResults)

 

[tehon3299]

OK...Great...Ill try that and get back to you...Thanks a lot!