Assigning SQL Query Results to Variable

[MCuthill]

Hi Everyone,

I am writing the code for an online library and having some trouble setting up the functionality of the the button used to signout a resource. I think that I want to take the primary key of the item in the data list, then assign differnt values to the fields relating to the status of this resource but I am not sure the best way to go about doing this.

Below is what I have thus far:

Private Sub SearchResults_ItemCommandByVal source As Object, ByVal e As DataListCommandEventArgs) Handles SearchResults.ItemCommand
   Dim SQL As String
   Dim primaryKey As Integer
   primaryKey = CInt(e.CommandArgument)

   If e.CommandName = "SignOut" Then
       SQL = "SELECT [IsAvailable] FROM [dbo_Activity] WHERE [ResourceID] =" & primaryKey
         
   End If
End Sub

I know I need to check the value of [dbo_Activity].[IsAvailable] before the user is able to signout an item - my question there is how to take the value returned from the SQL I have and assign it to a variable that I can do a check on.

The other, and more critical question(s) is in regards to the necessary steps to take AFTER the resource has been checked for availablity.

Any help would be greatly appreciated, Thanks.

Mike
"It Seems All My Problems Exist Between Keyboard And Chair.

 

[jbenson001]

Private Sub SearchResults_ItemCommandByVal source As Object, ByVal e As DataListCommandEventArgs) Handles SearchResults.ItemCommand
   Dim SQL As String
   Dim primaryKey As Integer
   primaryKey = CInt(e.CommandArgument)

   If e.CommandName = "SignOut" Then
      SQL = "SELECT [IsAvailable] FROM [dbo_Activity] WHERE [ResourceID] =" & primaryKey

[b]        Dim objConn As New SqlConnection
        Dim objComm As New SqlCommand
        objConn.ConnectionString = "connection string"

        objComm.Connection = objConn
        objComm.CommandType = CommandType.Text
        objComm.CommandText = SQL
        Try
           Dim x As String = objComm.ExecuteScalar
        Catch ex as SqlException
           'Do somehting here
        End Try
       
        'Do  your check on the variable here...   [/b]      

   End If
End Sub

Jim

 

[djj55]

One way is to use a data reader. Here is a partial code - the connection is already assigned

odbc_Command.CommandText = SQL
odbc_Reader = odbc_Command.ExecuteReader
odbc_Reader.Read()
if odbc_Reader.Item("IsAvailable") = true then
   ... code here
End If

or you could assign the value to a variable and use it later.
Good luck
djj

 

[MCuthill]

Thanks For The Help Guys,

I am having some trouble with the coding though. It does not recognise the type declarations in the code that Jim suggested, is there a class or a setting I must enable to utilize the SQLConnection & SQLCommand types?

I do not think the following changes the code needed (not significantly), but there has been a change in design with the site. I would like to change the visibility of the command button within the DataList based on the IsAvailable at Page_Load (therefore removing the need to check AFTER the end user clicks the button), is this possible to do? If so, would I use similar coding to that suggested above?

Thanks,

 

[jbenson001]

1. Add this to your page:
Imports System.Data.SqlClient

2. Unclear as to what you want. Is there a command button in each row of the datalist? If so use the data list's ItemDataBound event, get a reference to the button and set the visible property based on your flag

 

[MCuthill]

Thanks Jim,

Yes, there is a command button on each row of the datalist. What I want to do is have this button not visible for those rows in the datalist where the item is not available to be signed out by the end user. (The datalist is a listing of the contents of a library)

I will try the ItemDataBound event and let you know.

Mike
"It Seems All My Problems Exist Between Keyboard and Chair"

 

[jbenson001]

Alright.. let me know how it goes..

 

[chrissie1]

Please, pretty please, try to use parameters whenerver needed. SO.

Private Sub SearchResults_ItemCommandByVal source As Object, ByVal e As DataListCommandEventArgs) Handles SearchResults.ItemCommand
   Dim SQL As String
   Dim primaryKey As Integer
   primaryKey = CInt(e.CommandArgument)

   If e.CommandName = "SignOut" Then
      SQL = "SELECT [IsAvailable] FROM [dbo_Activity] WHERE [ResourceID] = @primaryKey"

        Dim objConn As New SqlConnection
        Dim objComm As New SqlCommand
        objConn.ConnectionString = "connection string"

        objComm.Connection = objConn
        objComm.CommandType = CommandType.Text
        objComm.CommandText = SQL
        objComm.Parameters.Add("@primarykey",sqldbtype.int)
        objcomm.parameters(0).value = primarykey
        Try
           Dim x As String = objComm.ExecuteScalar
        Catch ex as SqlException
           'Do somehting here
        End Try
       
        'Do  your check on the variable here...         

   End If
End Sub

Christiaan Baes
Belgium

"My new site" - Me

 

[MCuthill]

Success!

Thanks For All The Input Jim! I researched the DataListItemEventHandler on MSDN and ended up with the following code.

Partial Class search
    Inherits System.Web.UI.Page
    Public Event DataItemBound As DataListItemEventHandler


    Sub Item_Bound(ByVal sender As Object, ByVal e As DataListItemEventArgs)
        'Pull Contents of the SignedOutTo Field
        Dim Borrower As Label = CType(e.Item.FindControl("SignedOutTo"), Label)
        'Cast Contents to String
        Dim BorrowerStr As String = Val(Borrower)
        'Flag to check availability
        Dim IsAvailable As Boolean = True

        'If SignedOutTo Empty, Value of "0" appears in string. 
        'If String is "0" resource not currently signed out.
        If BorrowerStr = "0" Then
            IsAvailable = True
        Else
            IsAvailable = False
        End If

        'If Signed Out (Not Available), Hide Sign Out Button on Resource.
        If IsAvailable = False Then
            Dim cmdbtn As Object = CType(e.Item.FindControl("cmd_SignOut"), Object)
            cmdbtn.Visible = False
        End If

    End Sub

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        AddHandler SearchResults.ItemDataBound, AddressOf Item_Bound
    End Sub

Thanks,

Mike
"It Seems All My Problems Exist Between Keyboard and Chair"

 

[jbenson001]

Cool.. glad you got it..

Jim