Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 370
  • Last Modified:

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

0
megnin
Asked:
megnin
3 Solutions
 
devlab2012Commented:
try this code:
Protected Sub Page_Load(ByVal...
        Dim DBConn As New SqlConnection(ConfigurationManager.ConnectionStrings("PurchaseReqConnectionString").ConnectionString)
        Dim DBCmd1, DBCmd4 As New SqlCommand
        Dim sql1, sql4 As String
        sql1 = "SELECT [Department], [FirstName], [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)
            DBCmd4 = New SqlCommand(sql4, DBConn)
            SqlDataReader dr = DBCmd1.ExecuteReader();
            dr.Read();
            varDepartment = dr["Department"].ToString(); 'Convert.ToString(DBCmd1.ExecuteScalar())
            varFirstName = dr["FirstName"].ToString();
            varLastName = dr["LastName"].ToString();
            dr.Close();
            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

0
 
CtrlAltDlCommented:
I think you want to use the SqlDataReader:
sql = "SELECT [Department],[FirstName], [LastName] FROM [Users] WHERE [LogonName] = '" & iUserName & "'"
Dim DBReader As SqlDataReader = Nothing
        Try
            DBConn.Open()
            DBCmd = New SqlCommand(sql, DBConn)
            
            DBReader = DBCmd.ExecuteReader()

            If (DBReader.Read()) Then
                 varFirstName  = DBReader("FirstName")
                 varLastName = DBReader("LastName")
                 varDepartment = DBReader("Department")
            End If
            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 
        DBCmd.Dispose()
        DBConn.Close()
        DBConn = Nothing

Open in new window

0
 
CtrlAltDlCommented:
Doh, I guess DevLab beat me to it. lol
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
devlab2012Commented:
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
0
 
CodeCruiserCommented:
If you dont want to use a datareader then you can still use ExecuteScalar()

sql1 = "SELECT [Department] + '|' +  [FirstName] + '|' + [LastName] FROM [Users] WHERE [LogonName] = '" & iUserName & "'"

DBCmd1 = New SqlCommand(sql1, DBConn)
Dim Result As String = DBCmd1.ExecuteScalar()
VarDepartment = Result.Split("|")(0)
VarFirstName = Result.Split("|")(1)
VarLastName = Result.Split("|")(2)
0
 
megninAuthor Commented:
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
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now