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]

It's a function that evaluaties the parameter to see if it's null, and if it is, it returns the value of the second parameter.

[tehon3299]

Is this function already written??

[quwiltw]

I don't think so in VB.NET it's built in to Access. My guess is that Robby has it in some utility module that he's implemented himself. Otherwise maybe I've 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(), it's 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 don't get an error, once you get this working and understand it, I'll spring that on you. (not that it's 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...I'll try that and get back to you...Thanks a lot!