asked on

Read values from a SQL Table and assign them to variables

I have the block of code below in my Page_Load event.

Instead of making that many calls to the server I'd like to use a DataReader and one SELECT statement and put the values into variables or an array of variables, whatever is best.

SELECT FirstName, LastName, Department FROM Users WHERE LogonName = @iUserName

iUserName is the logon name of one user so only one record or value per field is returned. I just wanted to make that clear. The return of the sql above would be like "David, Megnin, IT Department".

I just need to put each value into a variable I can use where needed.

Thanks a bunch.

Protected Sub Page_Load(ByVal...
        Dim DBConn As New SqlConnection(ConfigurationManager.ConnectionStrings("PurchaseReqConnectionString").ConnectionString)
        Dim DBCmd1, DBCmd2, DBCmd3, DBCmd4 As New SqlCommand
        Dim sql1, sql2, sql3, sql4 As String
        sql1 = "SELECT [Department] FROM [Users] WHERE [LogonName] = '" & iUserName & "'"
        sql2 = "SELECT [FirstName]  FROM [Users] WHERE [LogonName] = '" & iUserName & "'"
        sql3 = "SELECT [LastName]   FROM [Users] WHERE [LogonName] = '" & iUserName & "'"
        sql4 = "SELECT SUM(TotalDollars) FROM [PurchaseRequisitions].[dbo].[Requisitions] WHERE [SubmittedBy] = '" & iUserName & "'"
        DBConn.Open()
        Try
            'Add UPDATE Statement 
            DBCmd1 = New SqlCommand(sql1, DBConn)
            DBCmd2 = New SqlCommand(sql2, DBConn)
            DBCmd3 = New SqlCommand(sql3, DBConn)
            DBCmd4 = New SqlCommand(sql4, DBConn)
            varDepartment = DBCmd1.ExecuteScalar() 'Convert.ToString(DBCmd1.ExecuteScalar())
            varFirstName = DBCmd2.ExecuteScalar()
            varLastName = DBCmd3.ExecuteScalar()
            lblSumTotal.Text = DBCmd4.ExecuteScalar()
            lblDepartment.Text = "Your Department: " & varDepartment
            lblUserName.Text = " Requisitions submitted by: " & varFirstName & " " & varLastName
            Session("Department") = varDepartment
        Catch exp As Exception
            Response.Write(exp.Message)
        End Try
        'Close Database connection 
        DBCmd1.Dispose()
        DBConn.Close()
        DBConn = Nothing
End Sub

Open in new window

SOLUTION

devlab2012

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

CtrlAltDl

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

CtrlAltDl

Doh, I guess DevLab beat me to it. lol

devlab2012

In my comment, please change the line:
SqlDataReader dr = DBCmd1.ExecuteReader();

to

Dim dr As SqlDataReader
dr = DBCmd1.ExecuteReader()

By mistake, I posted the C# syntax.

Also, its good to check that the query returned a record or not. You can do this by using If statement with dr.Read() like (as posted by @CtrlAltDl)
If dr.Read() Then
'set variables here
End If

SOLUTION

Nasir Razzaq

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

David Megnin

ASKER

Thanks! Three excellent solutions. I can make use of all three.

I actually Googled some more yesterday and came up with this version which is very close. Since I needed to get some values from one table and then a sum from a different table I'm using a DataReader and ExecuteScalar() both. I've got them jumbled together and it took a it of trial and error to get it to even work, so if anyone wants to "nitpick" this code to make it work or even look better, please, feel free. I'd appreciate it. ;-)

Dim DBConn As New SqlConnection(ConfigurationManager.ConnectionStrings("PurchaseReqConnectionString").ConnectionString)
Dim DBCmd1 As New SqlCommand("SELECT Department, FirstName, LastName FROM Users WHERE LogonName = '" & iUserName & "'", DBConn)
Dim DBCmd2 As New SqlCommand("SELECT SUM(TotalDollars) FROM [PurchaseRequisitions].[dbo].[Requisitions] WHERE [SubmittedBy] = '" & iUserName & "'", DBConn)
DBConn.Open()
Try
      Dim r As SqlDataReader = DBCmd1.ExecuteReader()
      While r.Read()
       varDepartment = CStr(r("Department"))
       varFirstName = CStr(r("FirstName"))
       varLastName = CStr(r("LastName"))
      End While
      r.Close()

      lblDepartment.Text = "Your Department: " & varDepartment
      lblUserName.Text = " Requisitions submitted by: " & varFirstName & " " & varLastName
      Session("Department") = varDepartment
      sumTotal = DBCmd2.ExecuteScalar
      lblSumTotal.Text = "Your Total: " & sumTotal


      Dim DBCmd3 As New SqlCommand("SELECT SUM(TotalDollars) FROM [PurchaseRequisitions].[dbo].[Requisitions] WHERE [SubmittedBy] IN (SELECT LogonName FROM Users WHERE Department = '" & varDepartment & "')", DBConn)
      Dim tempVal = DBCmd3.ExecuteScalar
      Response.Write("Department Total: " & tempVal)

Catch exp As Exception
Response.Write(exp.Message)
End Try
'Close Database connection
DBCmd1.Dispose()
DBCmd2.Dispose()
DBConn.Close()
DBConn = Nothing