Hi. I would like to create those checked rows in gridview with a button click. For example, an user checks 2 rows in the gridview, and clicks on the create button. I would want the selected row/s to be created to the database. How would I go about doing this?
I'm not sure if you understand my question, but below is my UI codes + BLL, DAL etc.
I'm using:
-Visual Studio 2008
-Visual Basic language
-SQL Server 2005 express(the one with vs 2008)
-Object Data Source
UI
<%@ Page Language="VB" MasterPageFile="~/ProjMaster.Master" AutoEventWireup="false" CodeFile="sign_up.aspx.vb" Inherits="PresenterAssignment_sign_up" title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="head" runat="server">
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<style type="text/css">
h2.style23
{
font-size:medium;
color:#111111;
}
.style41
{
width: 100%;
height: 431px;
}
.style42
{
height: 122px;
}
.style43
{
text-align: center;
}
.style44
{
position: absolute;
top: 378px;
left: 426px;
z-index: 1;
right: 500px;
}
.style45
{
position: absolute;
top: 376px;
left: 569px;
z-index: 1;
height: 26px;
margin-top: 2px;
}
.style46
{
margin-bottom: 0px;
}
</style>
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" runat="server">
<table class="style41">
<tr>
<td>
<asp:Label ID="Label1" runat="server" Font-Names="Aharoni" Font-Size="X-Large"
Text="Presenters Assignment Management (Sign Up) "></asp:Label>
<br />
</td>
<td>
</td>
</tr>
<tr>
<td class="style42">
<div class="style43">
<br />
<asp:GridView ID="GridView1" runat="server" DataSourceID="ObjectDataSource1"
AutoGenerateColumns="False" BackColor="White" BorderColor="#DEDFDE"
BorderStyle="None" BorderWidth="1px" CellPadding="4" ForeColor="Black"
GridLines="Vertical" Width="620px" CssClass="style46">
<RowStyle BackColor="#F7F7DE" />
<Columns>
<asp:CommandField ShowSelectButton="True" />
<asp:BoundField DataField="apptNo" HeaderText="School Appointment ID" />
<asp:BoundField DataField="SchoolName" HeaderText="School Name" />
<asp:BoundField DataField="SchoolTalk" HeaderText="School Talk" />
<asp:BoundField DataField="Dates" HeaderText="Date" />
<asp:BoundField DataField="Time" HeaderText="Time" />
<asp:BoundField DataField="Status" HeaderText="Status" />
<asp:TemplateField>
<ItemTemplate>
<asp:CheckBox ID="apptNo" runat="server" />
</ItemTemplate>
</asp:TemplateField>
</Columns>
<FooterStyle BackColor="#CCCC99" />
<PagerStyle BackColor="#F7F7DE" ForeColor="Black" HorizontalAlign="Right" />
<SelectedRowStyle BackColor="#CE5D5A" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#6B696B" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" />
</asp:GridView>
</div>
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server"
SelectMethod="getRetrieveSignUp" TypeName="BusinessLogic.UserBLL">
<SelectParameters>
<asp:Parameter Name="apptNo" Type="String" />
<asp:Parameter Name="Status" Type="String" />
<asp:Parameter Name="SchoolName" Type="String" />
<asp:Parameter Name="SchoolTalk" Type="String" />
<asp:Parameter Name="Dates" Type="String" />
<asp:Parameter Name="Time" Type="String" />
</SelectParameters>
</asp:ObjectDataSource>
<asp:Button ID="UncheckAll" runat="server" CssClass="style45" Height="25px"
Text="Uncheck All" Width="140px" />
<asp:Button ID="Button1" runat="server" Text="Create" CssClass="style47"
style="z-index: 1; position: absolute; top: 611px; left: 612px" />
<br />
<br />
<br />
<br />
</td>
<td class="style42">
</td>
</tr>
<tr>
<td>
<asp:Button ID="CheckAll" runat="server" CssClass="style44" Height="25px"
Text="Check All" Width="100px" />
<br />
</td>
<td>
</td>
</tr>
<tr>
<td>
</td>
<td>
</td>
</tr>
<tr>
<td>
<br />
</td>
<td>
</td>
</tr>
<tr>
<td>
</td>
<td>
</td>
</tr>
</table>
</asp:Content>UI-code behind
Partial Class PresenterAssignment_sign_up
Inherits System.Web.UI.Page
Private Sub ToggleCheckState(ByVal checkState As Boolean)
' Iterate through the Products.Rows property
For Each row As GridViewRow In GridView1.Rows
' Access the CheckBox
Dim cb As CheckBox = row.FindControl("apptNo")
If cb IsNot Nothing Then
cb.Checked = checkState
End If
Next
End Sub
Protected Sub CheckAll_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles CheckAll.Click
ToggleCheckState(True)
End Sub
Protected Sub UncheckAll_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles UncheckAll.Click
ToggleCheckState(False)
End Sub
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
For Each row As GridViewRow In GridView1.Rows()
Dim box As CheckBox = TryCast(row.FindControl("cbChecked"), CheckBox)
Next
End Sub
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
GetInfoToSave()
End Sub
Protected Sub GetInfoToSave()
For Each row As GridViewRow In GridView1.Rows
'This condition is added since here we are inserting only the rows which are checked
Dim checkbox As CheckBox = DirectCast(row.FindControl("cbChecked"), CheckBox)
' Collect your information below if the checkbox is checked...
If checkbox.Checked Then
Dim lblFirstName As Label = DirectCast(row.FindControl("lblFirstName"), Label)
Dim lblLastName As Label = DirectCast(row.FindControl("lblLastName"), Label)
'After all your information, send to method that is responsible for saving data to databasea
End If
Next
End Sub
End ClassPublic Class UserBLL
Dim UserDA As DataAccess.UserDAL = New DataAccess.UserDAL
Public Function getRetrieveSchAppt(ByVal ApptNo As String, ByVal FirstName As String, ByVal LastName As String, ByVal SchoolName As String, ByVal SchoolTalk As String, ByVal Dates As String, ByVal Time As String, ByVal Status As String) As System.Data.DataSet
Dim UserDA As DataAccess.UserDAL = New DataAccess.UserDAL
Dim ds As Data.DataSet = UserDA.RetrieveSchAppt(ApptNo, FirstName, LastName, SchoolName, SchoolTalk, Dates, Time, Status)
Return ds
End Function
Public Function getRetrieveApptsss(ByVal apptNo As String, ByVal FirstName As String, ByVal LastName As String, ByVal SchoolName As String, ByVal SchoolTalk As String, ByVal Dates As String, ByVal Time As String) As System.Data.DataSet
Dim ds As Data.DataSet = UserDA.RetrieveApptsss(apptNo, FirstName, LastName, SchoolName, SchoolTalk, Dates, Time)
Return ds
End Function
Public Function getRetrieveSignUp(ByVal apptNo As String, ByVal Status As String, ByVal SchoolName As String, ByVal SchoolTalk As String, ByVal Dates As String, ByVal Time As String) As System.Data.DataSet
Dim ds As Data.DataSet = UserDA.RetrieveSignUp(apptNo, Status, Dates, Time, SchoolName, SchoolTalk)
Return ds
End Function
Public Function getUpdateSchAppt(ByVal apptNo As String, ByVal FirstName As String, ByVal LastName As String, ByVal SchoolName As String, ByVal SchoolTalk As String, ByVal Dates As String, ByVal Time As String) As Int32
Dim UserDA As DataAccess.UserDAL = New DataAccess.UserDAL
Dim ds As Integer = UserDA.UpdateSchAppt(apptNo, FirstName, LastName, SchoolName, SchoolTalk, Dates, Time)
Return ds
End Function
Public Function getRetrieveSearchAssignmentResults(ByVal FirstName As String, ByVal LastName As String, ByVal Dates As String, ByVal SchoolName As String, ByVal SchoolTalk As String) As System.Data.DataSet
Dim ds As Data.DataSet = UserDA.SearchAssignment(FirstName, Dates, SchoolName, SchoolTalk, LastName)
Return ds
End Function
Public Function getCreateAssignmentRec(ByVal apptNo As String, ByVal FirstName As String, ByVal LastName As String, ByVal Dates As String, ByVal SchoolName As String, ByVal SchoolTalk As String, ByVal Time As String) As Int32
Dim ds As Integer = UserDA.creatAssignmentRec(apptNo, FirstName, LastName, SchoolName, SchoolTalk, Dates, Time)
Return ds
End Function
End ClassImports System.Data.SqlClient
Public Class UserDAL
Public Function RetrieveSchAppt(ByVal ApptNo As String, ByVal FirstName As String, ByVal LastName As String, ByVal SchoolName As String, ByVal SchoolTalk As String, ByVal Dates As String, ByVal Time As String, ByVal Status As String) As System.Data.DataSet
Dim connectionString As String = My.Settings.dbConnection
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "SELECT * FROM SchTalkAppt"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection
Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.SqlClient.SqlDataAdapter
dataAdapter.SelectCommand = dbCommand
Dim dataSet As System.Data.DataSet = New System.Data.DataSet
dataAdapter.Fill(dataSet)
Return dataSet
End Function
Public Function SearchAssignment(ByVal FirstName As String, ByVal LastName As String, ByVal SchoolName As String, ByVal SchoolTalk As String, ByVal Dates As String) As System.Data.DataSet
Dim connectionString As String = My.Settings.dbConnection
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "SELECT * FROM PresenterAssign WHERE FirstName='" & FirstName & "' OR Dates='" & Dates & "' OR SchoolName='" & SchoolName & "' OR LastName='" & LastName & "' OR SchoolTalk='" & SchoolTalk & "'"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection
Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.SqlClient.SqlDataAdapter
dataAdapter.SelectCommand = dbCommand
Dim dataSet As System.Data.DataSet = New System.Data.DataSet
dataAdapter.Fill(dataSet)
Return dataSet
End Function
Public Function RetrieveApptsss(ByVal apptNo As String, ByVal FirstName As String, ByVal LastName As String, ByVal SchoolName As String, ByVal SchoolTalk As String, ByVal Dates As String, ByVal Time As String) As System.Data.DataSet
Dim connectionString As String = My.Settings.dbConnection
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "SELECT apptNo, FirstName, LastName, SchoolName, SchoolTalk, Dates, Time FROM PresenterAssign"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection
Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.SqlClient.SqlDataAdapter
dataAdapter.SelectCommand = dbCommand
Dim dataSet As System.Data.DataSet = New System.Data.DataSet
dataAdapter.Fill(dataSet)
Return dataSet
End Function
Public Function RetrieveSignUp(ByVal apptNo As String, ByVal Status As String, ByVal SchoolName As String, ByVal SchoolTalk As String, ByVal Dates As String, ByVal Time As String) As System.Data.DataSet
Dim connectionString As String = My.Settings.dbConnection
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "SELECT apptNo, SchoolName, SchoolTalk, Dates, Time, Status FROM SchTalkAppt"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection
Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.SqlClient.SqlDataAdapter
dataAdapter.SelectCommand = dbCommand
Dim dataSet As System.Data.DataSet = New System.Data.DataSet
dataAdapter.Fill(dataSet)
Return dataSet
End Function
Public Function UpdateSchAppt(ByVal apptNo As String, ByVal FirstName As String, ByVal LastName As String, ByVal SchoolName As String, ByVal SchoolTalk As String, ByVal Dates As String, ByVal Time As String) As Int32
Dim connectionString As String = My.Settings.dbConnection
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "UPDATE PresenterAssign SET FirstName='" & FirstName & "',LastName='" & LastName & "',Dates='" & Dates & "',Time='" & Time & "',SchoolName='" & SchoolName & "',SchoolTalk='" & SchoolTalk & "'where apptNo='" & apptNo & "'"
Dim dbCommand As SqlCommand = New SqlCommand(queryString, dbConnection)
Dim rowsAffected As Integer = 0
dbConnection.Open()
Try
rowsAffected = dbCommand.ExecuteNonQuery
Finally
dbConnection.Close()
End Try
Return rowsAffected
End Function
Public Function creatAssignmentRec(ByVal apptNo As String, ByVal FirstName As String, ByVal LastName As String, ByVal SchoolName As String, ByVal SchoolTalk As String, ByVal Dates As String, ByVal Time As String) As Integer
Dim connectionString As String = My.Settings.dbConnection
Dim dbConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "INSERT UserInformation (apptNo,FirstName,LastName,SchoolName,SchoolTalk,Dates,Time) VALUES ('" + apptNo + "','" + FirstName + "','" + LastName + "','" + SchoolName + "','" + SchoolTalk + "','" + Dates + "','" + Time + "')"
Try
Dim dbCommand As System.Data.SqlClient.SqlCommand = New SqlCommand(queryString, dbConnection)
dbCommand.Parameters.AddWithValue("@apptNo", apptNo)
dbCommand.Parameters.AddWithValue("@FirstName", FirstName)
dbCommand.Parameters.AddWithValue("@LastName", LastName)
dbCommand.Parameters.AddWithValue("@SchoolName", SchoolName)
dbCommand.Parameters.AddWithValue("@SchoolTalk", SchoolTalk)
dbCommand.Parameters.AddWithValue("@Dates", Dates)
dbCommand.Parameters.AddWithValue("@Time", Time)
dbConnection.Open()
Return Convert.ToInt32(dbCommand.ExecuteScalar)
Catch ex As Exception
Return -1
Finally
dbConnection.Close()
End Try
End Function
End ClassExtra Information: I'm using the method getRetrieveSignUp to retrieve those pending assignments to allow presenters to select. To create, the method used is getCreateAssignmentRec.