5 Replies - 3717 Views - Last Post: 04 September 2009 - 08:33 AM Rate Topic: -----

#1 Nuela  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 39
  • Joined: 13-June 09

checkbox list

Posted 22 July 2009 - 06:43 AM

hi!..How can i connect a checkboxlist with a database?
Is This A Good Question/Topic? 0
  • +

Replies To: checkbox list

#2 bflosabre91  Icon User is offline

  • go sabres

Reputation: 105
  • View blog
  • Posts: 1,439
  • Joined: 22-February 08

Re: checkbox list

Posted 22 July 2009 - 07:05 AM

please elaborate because that doesnt really make sense.
Was This Post Helpful? 0
  • +
  • -

#3 Nuela  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 39
  • Joined: 13-June 09

Re: checkbox list

Posted 22 July 2009 - 07:29 AM

View Postbflosabre91, on 22 Jul, 2009 - 06:05 AM, said:

please elaborate because that doesnt really make sense.


I bound a checkboxlist control to a sql server table to populate the list - now I want to take the user's selections and store each checkbox in the list's input in a different field in the database.how can you table
Was This Post Helpful? 0
  • +
  • -

#4 PDUNZ  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 43
  • View blog
  • Posts: 376
  • Joined: 17-July 09

Re: checkbox list

Posted 22 July 2009 - 08:20 AM

Please try doing it before asking how to do it, and if you have tried and its not working - post the code and we will help you though it.

So, you want to store if its Checked or Unchecked?

You need to get the state of each check box ie GetItemCheckState, and add or update that in the database.

This post has been edited by PDUNZ: 22 July 2009 - 08:20 AM

Was This Post Helpful? 0
  • +
  • -

#5 ajwsurfer  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 21
  • View blog
  • Posts: 373
  • Joined: 24-October 06

Re: checkbox list

Posted 04 September 2009 - 08:03 AM

OK, here is something that I just cooked up, I did not figure out how to store the data yet, but I can add it in a day or so when I get it worked out.

For pre-requisites I made a few functions and classes to handle queries and building of the SQLDataReader

I stored all the queries in an ini file and used the IniReader class available from http://www.mentalis..../class.qpx?id=6 to read them in. So I have a class called QueryStringBuilder that is a little quirky but gets the job done.
Imports Microsoft.VisualBasic

Imports Org.Mentalis.Files

Namespace dbUtilities

	Public Class QueryStringBuilder

		'/// <summary>
		'/// ToDo: need to document all code 
		'/// </summary>
		Public Sub New()
			_iniFile = New IniReader(_rootDir + "App_Data\ini\queries.ini")
		End Sub

		Public Function buildString(ByVal queryType As String, ByVal queryName As String) As String
			buildString = _iniFile.ReadString(queryType, queryName)
		End Function

		Public Function buildString(ByVal queryType As String, ByVal queryName As String, _
									ByVal FirstParam As String, ByVal ParamArray NextParams() As String) As String
			Dim returnStr As StringBuilder = New StringBuilder("")
			Dim qryStr As String = _iniFile.ReadString(queryType, queryName)

			Try
				returnStr.Append(qryStr.Substring(0, qryStr.IndexOf("@"))) ' Add the string prefix (first set of logic)
				returnStr.Append(FirstParam) ' Add the first parameter to the string
			Catch e As ArgumentOutOfRangeException
				Return "Error -> Problem with query  """ & queryName & """ in the ""queries.ini"" file!"
			End Try


			Dim i As Int16 = getNextIndex(qryStr) ' get index at end of parameter placeholder
			Dim j As Int16 = 0

			If i <> -1 Then
				qryStr = qryStr.Substring(i, qryStr.Length - i) ' remove prefix and parameter placeholder from string
				If qryStr.IndexOf("@") = -1 Then
					returnStr.Append(qryStr) ' add final stage of logic to the string
				End If
			Else
				qryStr = "" ' there is no logic at the end of the string so remove the entire string
			End If

			Try

				While qryStr.IndexOf("@") <> -1
					returnStr.Append(qryStr.Substring(0, qryStr.IndexOf("@"))) ' add the next stage of logic
					returnStr.Append(NextParams(j))						  ' add the next parameter

					i = getNextIndex(qryStr)						   ' get index at end of the next parameter placeholder
					If i <> -1 Then
						qryStr = qryStr.Substring(i, qryStr.Length - i)	  ' remove next set of logic and paramter placeholder from the string
						If qryStr.IndexOf("@") = -1 Then
							returnStr.Append(qryStr)
						End If
						j += 1
					Else
						qryStr = "" ' there is no logic at the end of the string so remove the entire string
						Exit While
					End If
				End While
			Catch e As IndexOutOfRangeException
				Return "Error -> Problem parameters of the query  """ & queryName & """ in the ""queries.ini"" file!"
			End Try


			Return returnStr.ToString()
		End Function

		Public Function buildString() As String ' Test Function 
			Dim returnStr As StringBuilder = New StringBuilder("")
			Dim qryStr As String = _iniFile.ReadString("select", "projectNameIdPair")
			returnStr.Append(qryStr)
			Return returnStr.ToString()
		End Function


		Private Shared _iniFile As IniReader
		' Can change to shared for smaller memory footprint 
		' if parameter constructor is commented out
		'Public Sub New(ByVal iniFile As String)
		'	_iniFile = New IniReader(_rootDir + "App_Data\" + iniFile)
		'End Sub
		Private Shared _rootDir As String = AppDomain.CurrentDomain.SetupInformation.ApplicationBase()

		Private i As Int16
		Private j As Int16
		Private k As Int16
		Private Function getNextIndex(ByVal Str As String) As Int16
			i = j = k = -1
			i = Str.IndexOf(" ", Str.IndexOf("@") + 1)
			j = Str.IndexOf("'", Str.IndexOf("@") + 1)
			k = Str.IndexOf(",", Str.IndexOf("@") + 1)

			If i <> -1 And (i < j Or j = -1) And (i < k Or k = -1) Then
				Return i
			ElseIf j <> -1 And (j < k Or k = -1) Then
				Return j
			ElseIf k <> -1 Then
				Return k
			End If
			Return i
		End Function
	End Class

End Namespace


The next class is called DBAccess and it handles the messy stuff involved with database connections by using public one line functions:

Imports Microsoft.VisualBasic
Imports System.Data.SqlClient
Imports System.Data.OleDb
Imports System.IO
Imports Microsoft.VisualBasic.ApplicationServices
Imports System.Web.Configuration

Namespace dbUtilities


	Public Class DBAccess

		Private Shared _rootDir As String = AppDomain.CurrentDomain.SetupInformation.ApplicationBase()


		Public Function GetSingleDBValue(ByVal conStr As String, ByVal qryStr As String) As Object
			Dim sqlCon As New SqlConnection(conStr)
			Dim sqlCmd As New SqlCommand
			Dim retVal As Object

			sqlCmd.CommandText = qryStr
			sqlCmd.CommandType = Data.CommandType.Text
			sqlCmd.Connection = sqlCon

			sqlCon.Open()
			retVal = sqlCmd.ExecuteScalar()
			If IsDBNull(retVal) Then
				retVal = Nothing
			End If
			sqlCon.Close()

			Return retVal
		End Function


		Public Function GetDataReader(ByVal conStr As String, ByVal qryStr As String) As SqlDataReader
			Dim sqlCon As New SqlConnection(conStr)
			Dim sqlCmd As New SqlCommand(qryStr)

			sqlCmd.CommandType = Data.CommandType.Text
			sqlCmd.Connection = sqlCon

			sqlCon.Open()

			Return sqlCmd.ExecuteReader()
		End Function

		Public Sub ExecuteNoReturnQuery(ByVal conStr As String, ByVal qryStr As String)
			Dim sqlCon As New SqlConnection(conStr)
			Dim sqlCmd As New SqlCommand(qryStr)

			sqlCmd.CommandType = Data.CommandType.Text
			sqlCmd.Connection = sqlCon

			sqlCon.Open()
			sqlCmd.ExecuteNonQuery()

			sqlCon.Close()

		End Sub

		Public Function GetOleDataReader(ByVal conStr As String, ByVal qryStr As String) As OleDbDataReader
			Dim oleCon As OleDbConnection = New OleDbConnection(conStr)
			oleCon.Open()
			Dim oleDbCmd As OleDbCommand = New OleDbCommand(qryStr, oleCon)

			Return oleDbCmd.ExecuteReader()
		End Function


		Public Function BuildOleDbfConnectionString(ByVal dir As String) As String

			Return "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
				  _rootDir & "App_Data\dbfFiles\" & dir & "\" & _
				 ";Extended Properties=dBASE IV;User ID=Admin;Password=;"
		End Function


		Public Function BuildOleExcelConnectionString(ByVal dir As String, ByRef fileName As String) As String
			Return "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
				   _rootDir & "App_Data\dbfFiles\" & dir & "\" & fileName & ".xls" & _
				   ";Jet OLEDB:Engine Type=23;Extended Properties=Excel 8.0"
		End Function


		Public Function BuildOleAccessConnectionString(ByVal dir As String, ByRef fileName As String) As String
			Return "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
				   _rootDir & "App_Data\dbfFiles\" & dir & "\" & fileName & ".mdb"
		End Function



		Public Sub CopyDbfFile(ByVal sourceFile As String, ByVal destFile As String)
			Dim dir As String = _rootDir & "App_Data\dbfFiles\"
			File.Delete(dir & destFile)
			If File.Exists(dir & sourceFile) = True Then
				File.Copy(dir & sourceFile, dir & destFile)
			End If
		End Sub


		Public Function IsUserAuthorized( _
				ByVal UserID As String, _
				ByVal pid As String, _
				ByVal PhaseID As String, _
				ByVal conStr As String) As Boolean
			Dim qsb As New QueryStringBuilder

			If Roles.IsUserInRole("staff") Or Roles.IsUserInRole("admin") Then
				Return True
			ElseIf pid = 3 _
				And GetSingleDBValue(conStr, qsb.buildString("select", "isDatasetUserAuthorized", _
															UserID, pid)) > 0 Then
				Return True
			ElseIf GetSingleDBValue(conStr, qsb.buildString("select", "isUserAuthorized", _
															UserID, pid, PhaseID)) > 0 Then
				Return True
			End If
			Return False

		End Function

	End Class


End Namespace



So here we have it all together now.

In the query.ini file:
  ...
  [select]
  selectFitReachStorageList=SELECT reachIDCombo, reachIDName FROM dbo.vwFITSelectBox WHERE ProjectID = @pid
  ...



On the ASP Side:
		...
		<asp:CheckBoxList ID="ReachCheckBoxList" runat="server">
		</asp:CheckBoxList>
		...


And finally on the VB Side we have:
  Imports System.Web.Configuration
  Imports System.Data.SqlClient

  Imports dbUtilities
  ...
Partial Class projects_phase1_fit_store
	Inherits System.Web.UI.Page

	Private _conStr As String
	Private _ds As SqlDataSource
	Private _dataReader As SqlDataReader
	Private _chbl As CheckBoxList

	Private _qsb As QueryStringBuilder
	Private _dba As DBAccess
	

	Protected pid As String
	...
	Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
		_qsb = New QueryStringBuilder()
		_dba = New DBAccess	   'data access class used to get project name and sgat version  
		_conStr = WebConfigurationManager.ConnectionStrings("RiversDMSConStr").ConnectionString
		...
		pid = Request.QueryString("pid")
		...
		_chbl = ReachCheckBoxList
		' Fill the checkbox list
		_dataReader = _dba.GetDataReader(_conStr, _qsb.buildString("select", "selectFitReachStorageList", pid))

			   If Not Page.IsPostBack Then
		   While _dataReader.Read
			_chbl.Items.Add(New ListItem(_dataReader("reachIDName"), _dataReader("reachIDCombo")))
		   End While
			   End If

	End Sub

End Class




I hope this helps ;)

This post has been edited by ajwsurfer: 04 September 2009 - 08:12 AM

Was This Post Helpful? 0
  • +
  • -

#6 ajwsurfer  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 21
  • View blog
  • Posts: 373
  • Joined: 24-October 06

Re: checkbox list

Posted 04 September 2009 - 08:33 AM

Lets add the "select all" and "select none" options:
(from http://www.dotnetcur...cle.aspx?ID=77)

ASP:
		<asp:LinkButton ID="lbAll" runat="server">Select All</asp:LinkButton>
		&nbsp;&nbsp;&nbsp;&nbsp;
		<asp:LinkButton ID="lbNone" runat="server">Select None</asp:LinkButton>
		
		<asp:CheckBoxList ID="ReachCheckBoxList" runat="server">
		</asp:CheckBoxList>


VB:
	Protected Sub lbAll_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lbAll.Click
		For Each li In _chbl.Items
			li.Selected = True
		Next
	End Sub

	Protected Sub lbNone_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles lbNone.Click
		For Each li In _chbl.Items
			li.Selected = False
		Next
	End Sub


Was This Post Helpful? 0
  • +
  • -

Page 1 of 1