A
Aaron
I am building an ASP.NET application. Everything is working, but I am
not
sure if I am doing things correctly.
I have a datagrid bound to a dataset. The datagrid binds fine and all of
my records are displayed correctly.
When I select a row using a button column, I need the values of the data
in
the selected row to populate several textboxes on the form. The
textboxes
are for editing the row's data.
I do not want to edit directly in the datagrid.
The way I am doing this now is with the following code:
Sub FillTextBoxes(ByVal e As DataGridCommandEventArgs)
If e.Item.Cells(1).Text.Equals(" ") Then
txtFullname.Text = ""
Else
txtFullname.Text = e.Item.Cells(1).Text
End If
If e.Item.Cells(2).Text.Equals(" ") Then
txtCompany.Text = ""
Else
txtCompany.Text = e.Item.Cells(2).Text
End If
If e.Item.Cells(3).Text.Equals(" ") Then
txtTitle.Text = ""
Else
txtTitle.Text = e.Item.Cells(3).Text
End If
If e.Item.Cells(4).Text.Equals(" ") Then
txtNotes.Text = ""
Else
txtNotes.Text = e.Item.Cells(4).Text
End If
If e.Item.Cells(5).Text.Equals(" ") Then
txtConfNotes.Text = ""
Else
txtConfNotes.Text = e.Item.Cells(5).Text
End If
If e.Item.Cells(6).Text.Equals(" ") Then
txtActDate.Text = ""
Else
txtActDate.Text = e.Item.Cells(6).Text
End If
If e.Item.Cells(7).Text.Equals(" ") Then
txtFollowUpDate.Text = ""
Else
txtFollowUpDate.Text = e.Item.Cells(7).Text
End If
End Sub
This is checking the values of each cell of the selected row in the
datagrid.
Is there a better approach?
Is there any way to know which row has been selected in the DATASET as
opposed to the values rendered in the datagrid? If so, is it possible to
maintain concurrency with the selected datagrid row and the textboxes?
How
would this be implemented?
Here is all of my code:
Public Class WebForm1
#Region " Boring Shit "
Inherits System.Web.UI.Page
Protected WithEvents DropDownList1 As
System.Web.UI.WebControls.DropDownList
Protected WithEvents Button1 As System.Web.UI.WebControls.Button
Protected WithEvents Label1 As System.Web.UI.WebControls.Label
Protected WithEvents Label2 As System.Web.UI.WebControls.Label
Protected WithEvents Label3 As System.Web.UI.WebControls.Label
Protected WithEvents Label4 As System.Web.UI.WebControls.Label
Protected WithEvents Label5 As System.Web.UI.WebControls.Label
Protected WithEvents Label6 As System.Web.UI.WebControls.Label
Protected WithEvents Label7 As System.Web.UI.WebControls.Label
Protected WithEvents Label8 As System.Web.UI.WebControls.Label
Protected WithEvents Label9 As System.Web.UI.WebControls.Label
Protected WithEvents Label10 As System.Web.UI.WebControls.Label
Protected WithEvents Button2 As System.Web.UI.WebControls.Button
Protected WithEvents Label11 As System.Web.UI.WebControls.Label
Protected WithEvents Label12 As System.Web.UI.WebControls.Label
Protected WithEvents CheckBoxList1 As
System.Web.UI.WebControls.CheckBoxList
Protected WithEvents txtFullname As System.Web.UI.WebControls.TextBox
Protected WithEvents txtCompany As System.Web.UI.WebControls.TextBox
Protected WithEvents txtTitle As System.Web.UI.WebControls.TextBox
Protected WithEvents txtConfNotes As
System.Web.UI.WebControls.TextBox
Protected WithEvents txtActDate As System.Web.UI.WebControls.TextBox
Protected WithEvents txtFollowUpDate As
System.Web.UI.WebControls.TextBox
Protected WithEvents txtBPhone As System.Web.UI.WebControls.TextBox
Protected WithEvents txtWorkExt As System.Web.UI.WebControls.TextBox
Protected WithEvents Label13 As System.Web.UI.WebControls.Label
Protected WithEvents txtSearchDate As
System.Web.UI.WebControls.TextBox
Protected WithEvents Label14 As System.Web.UI.WebControls.Label
Protected WithEvents DataGrid1 As System.Web.UI.WebControls.DataGrid
Protected WithEvents Label15 As System.Web.UI.WebControls.Label
Protected WithEvents DataGrid2 As System.Web.UI.WebControls.DataGrid
Protected WithEvents txtClientCell As
System.Web.UI.WebControls.TextBox
Protected WithEvents Button3 As System.Web.UI.WebControls.Button
Protected WithEvents Button4 As System.Web.UI.WebControls.Button
Protected WithEvents Label16 As System.Web.UI.WebControls.Label
Protected WithEvents Label17 As System.Web.UI.WebControls.Label
Protected WithEvents Label18 As System.Web.UI.WebControls.Label
Protected WithEvents Label19 As System.Web.UI.WebControls.Label
Protected WithEvents txtNotes As System.Web.UI.WebControls.TextBox
#End Region
#Region " Web Form Designer Generated Code (More Boring Shit) "
<System.Diagnostics.DebuggerStepThrough()> Private Sub
InitializeComponent()
End Sub
Private Sub Page_Init(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub
#End Region
#Region " Developer Generated Code (The Real Shit) "
Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Utilities.CreateConfirmBox(Me.Button2, _
"Are you sure you want to update the record?")
End Sub
Sub BindDataGrid(ByVal DataGridName As
System.Web.UI.WebControls.DataGrid, ByVal FuncToCall As DataSet, ByVal
DGKeyField As String, ByVal DGDataMember As String)
Dim ds As DataSet
ds = FuncToCall
DataGridName.DataSource = ds 'Define which dataset to be used by
datagrid1
DataGridName.DataKeyField = DGKeyField ' Define a primary key for
the Dataset
DataGridName.DataMember = DGDataMember
DataGridName.DataBind() 'Bind the datagrid to it's datasource
End Sub
Sub FillTextBoxes(ByVal e As DataGridCommandEventArgs)
If e.Item.Cells(1).Text.Equals(" ") Then
txtFullname.Text = ""
Else
txtFullname.Text = e.Item.Cells(1).Text
End If
If e.Item.Cells(2).Text.Equals(" ") Then
txtCompany.Text = ""
Else
txtCompany.Text = e.Item.Cells(2).Text
End If
If e.Item.Cells(3).Text.Equals(" ") Then
txtTitle.Text = ""
Else
txtTitle.Text = e.Item.Cells(3).Text
End If
If e.Item.Cells(4).Text.Equals(" ") Then
txtNotes.Text = ""
Else
txtNotes.Text = e.Item.Cells(4).Text
End If
If e.Item.Cells(5).Text.Equals(" ") Then
txtConfNotes.Text = ""
Else
txtConfNotes.Text = e.Item.Cells(5).Text
End If
If e.Item.Cells(6).Text.Equals(" ") Then
txtActDate.Text = ""
Else
txtActDate.Text = e.Item.Cells(6).Text
End If
If e.Item.Cells(7).Text.Equals(" ") Then
txtFollowUpDate.Text = ""
Else
txtFollowUpDate.Text = e.Item.Cells(7).Text
End If
'LoadSessionValues()
End Sub
Sub LoadSessionValues()
Session("FullName") = txtFullname.Text.ToString
Session("Title") = txtTitle.Text.ToString
Session("Company") = txtCompany.Text.ToString
Session("ActDate") = txtActDate.Text.ToString
Session("FollowUpDate") = txtFollowUpDate.Text.ToString
Session("Notes") = txtNotes.Text.ToString
Session("ConfNotes") = txtConfNotes.Text.ToString
End Sub
Sub ClearTextBoxes()
txtFullname.Text() = ""
txtTitle.Text() = ""
txtCompany.Text() = ""
txtNotes.Text() = ""
txtConfNotes.Text() = ""
txtActDate.Text() = ""
txtFollowUpDate.Text() = ""
txtBPhone.Text() = ""
txtWorkExt.Text() = ""
txtClientCell.Text() = ""
End Sub
Sub UpdateLabels()
'Shows page count for DG paging events
Label14.Text = "Displaying page " & (DataGrid1.CurrentPageIndex +
1).ToString() & " of " & DataGrid1.PageCount
End Sub
Sub UpdateRecord()
LoadSessionValues()
Dim SqlCon As New SqlClient.SqlConnection()
Dim DA As New SqlClient.SqlDataAdapter()
Dim SqlCmd As New SqlClient.SqlCommand()
Dim DS As New DataSet()
Dim SQLSelectStatement As String
Dim ColumnNames As String
Dim FromClause As String
Dim WhereClause As String
Dim OrderClause As String
SqlCon = WebBizDev.Utilities.SQLConnect("ServerName", "USER",
"PASSWORD", "DBNAME")
'Dim sqltran1 As SqlClient.SqlTranUSERction
'Dim sqlcmd0 As New SqlClient.SqlCommand()
''Dim sqlcon = DBConnect()
'Dim ds2 As New DataSet()
'Dim da2 As New SqlClient.SqlDataAdapter()
'Dim Now() As DateTime
''sqlcmd0.Connection = sqlcon
''sqltran1 = sqlcon.BeginTranUSERction
'sqlcmd0.TranUSERction = sqltran1
'sqlcmd0.CommandText = "Update person set name_full = @NameFull,
Company1 = @Company, Title1 = @Title, Bphone = @BPhone, BPhone_Extension
=
@BphoneExt, CellPhone= @Cell where person_ID =" & Session("CurrentID") &
";Update Activity set Notes = @Notes, Notes_Confidential = @ConfNotes,
Date
= @ActDate, FollowUp = @FollowUpDate, BDCallsMade = @BDCallsMade,
BDEmailsSent = @BDEmailsSent where activity_ID = " & Session
("CurrentActivityID")
'Dim SplitName As Array
'Dim lName
'Dim fName
''Splitting SearchBox at the comma
'SplitName = Split(txtFullname.Text(), ",")
''Load lName into the first position of the array
'lName = SplitName(0)
''Append a percent sign for wildcard
'Session("LastName") = lName
''Load fName into second position of the array
''fName = SplitName(1)
''Append a percent sign for wildcard
'Session("FirstName") = fName
''----------------------------------------------
''Add sql parameters
''----------------------------------------------
''FullName
'sqlcmd0.Parameters.Add("@NameFull", DbType.String)
'sqlcmd0.Parameters("@NameFull").Direction =
ParameterDirection.Input
'sqlcmd0.Parameters("@NameFull").Value = Session
("FullName").ToString
'''FirstName
''sqlcmd0.Parameters.Add("@FirstName", DbType.String)
''sqlcmd0.Parameters("@FirstName").Direction =
ParameterDirection.Input
''sqlcmd0.Parameters("@FirstName").Value = Session("FirstName")
'''LastName
''sqlcmd0.Parameters.Add("@LastName", DbType.String)
''sqlcmd0.Parameters("@LastName").Direction =
ParameterDirection.Input
''sqlcmd0.Parameters("@LastName").Value = Session("LastName")
''Company
'sqlcmd0.Parameters.Add("@Company", DbType.String)
'sqlcmd0.Parameters("@Company").Direction =
ParameterDirection.Input
'sqlcmd0.Parameters("@Company").Value = txtCompany.Text
''Title
'sqlcmd0.Parameters.Add("@Title", DbType.String)
'sqlcmd0.Parameters("@Title").Direction =
ParameterDirection.Input
'sqlcmd0.Parameters("@Title").Value = txtTitle.Text
''Bphone
'sqlcmd0.Parameters.Add("@Bphone", DbType.VarNumeric)
'sqlcmd0.Parameters("@Bphone").Direction =
ParameterDirection.Input
'sqlcmd0.Parameters("@Bphone").Value = txtBPhone.Text
''Bphone Ext
'sqlcmd0.Parameters.Add("@BphoneExt", DbType.VarNumeric)
'sqlcmd0.Parameters("@BphoneExt").Direction =
ParameterDirection.Input
'sqlcmd0.Parameters("@BphoneExt").Value = txtWorkExt.Text
''Cell
'sqlcmd0.Parameters.Add("@Cell", DbType.VarNumeric)
'sqlcmd0.Parameters("@Cell").Direction = ParameterDirection.Input
'sqlcmd0.Parameters("@Cell").Value = txtClientCell.Text
''Notes
'sqlcmd0.Parameters.Add("@Notes", DbType.String)
'sqlcmd0.Parameters("@Notes").Direction =
ParameterDirection.Input
'sqlcmd0.Parameters("@Notes").Value = txtNotes.Text
''ConfNotes
'sqlcmd0.Parameters.Add("@ConfNotes", DbType.String)
'sqlcmd0.Parameters("@ConfNotes").Direction =
ParameterDirection.Input
'sqlcmd0.Parameters("@ConfNotes").Value = txtConfNotes.Text
''BDCallsMade
'sqlcmd0.Parameters.Add("@BDCallsMade", DbType.String)
'sqlcmd0.Parameters("@BDCallsMade").Direction =
ParameterDirection.Input
'sqlcmd0.Parameters("@BDCallsMade").Value = Label18.Text
''BDEmailsSent
'sqlcmd0.Parameters.Add("@BDEmailsSent", DbType.String)
'sqlcmd0.Parameters("@BDEmailsSent").Direction =
ParameterDirection.Input
'sqlcmd0.Parameters("@BDEmailsSent").Value = Label19.Text
''ActDate
'sqlcmd0.Parameters.Add("@ActDate", DbType.Date)
'sqlcmd0.Parameters("@ActDate").Direction =
ParameterDirection.Input
'sqlcmd0.Parameters("@ActDate").Value = txtActDate.Text
''FollowUpDate
'sqlcmd0.Parameters.Add("@FollowUpDate", DbType.Date)
'sqlcmd0.Parameters("@FollowUpDate").Direction =
ParameterDirection.Input
'sqlcmd0.Parameters("@FollowUpDate").Value = txtFollowUpDate.Text
''execute sql command, not using dataset, so ExecuteNonQuery must
be used
'sqlcmd0.ExecuteNonQuery()
''if everything was successfull, commit to sql
'sqltran1.Commit()
''sqlcon.Close()
End Sub
Sub DataGrid1_Paging(ByVal sender As Object, ByVal e As
DataGridPageChangedEventArgs)
DataGrid1.CurrentPageIndex = e.NewPageIndex ' tell the datagrid
that we are on a new page
BindDataGrid(DataGrid1, GetPersonRecords, "Client_Person_ID",
"First") ' Call Drawdata to fill the dataset and bind the datagrid
UpdateLabels() ' call to update labels to show record and page
counts
ClearTextBoxes()
DataGrid1.SelectedIndex = -1 ' Tell the datagrid that nothing is
selected
End Sub
Sub DataGrid_Select(ByVal sender As Object, ByVal e As
DataGridCommandEventArgs)
Dim ds As DataSet
Session("CurrentID") = e.Item.Cells(8).Text.ToString
Session("CurrentActivityID") = e.Item.Cells(9).Text.ToString
ds = GetCurrentActivity(Session("CurrentActivityID"))
BindDataGrid(DataGrid2, ds, "ActID", "Second")
FillTextBoxes(e)
LoadSessionValues()
DataGrid2.Visible = True
Label18.Visible = True
Label18.Text = ds.Tables("Second").Rows(0).Item(0).ToString
Label19.Visible = True
Label19.Text = ds.Tables("Second").Rows(0).Item(1).ToString
Button3.Visible = True
Button4.Visible = True
Label16.Visible = True
Label17.Visible = True
Button3.Enabled = True
Button4.Enabled = True
DataGrid1.Visible = False
End Sub
Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim ds As DataSet
ds = GetPersonRecords()
ClearTextBoxes() 'Clear Data from textboxes
BindDataGrid(DataGrid1, ds, "Client_person_ID", "First") 'Query
for
data
DataGrid1.CurrentPageIndex = 0 ' Returns DG to page 0
UpdateLabels() ' call to update labels to show record and page
counts
'Scorecard buttons and labels are hidden when a query is issued
'-->
Button3.Enabled = True
Button4.Enabled = True
Label16.Visible = False
Label17.Visible = False
Label18.Visible = False
Label19.Visible = False
Button3.Visible = False
Button4.Visible = False
'<--
DataGrid1.Visible = True '>> Used to show second DG with
activity
history
DataGrid2.Visible = False '>> Used to show second DG with
activity
history
Label13.Text = ds.Tables("First").Rows.Count.ToString & " Records
Found" ' Fill the label with the number of records in the data set DS
End Sub
Sub Button2_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button2.Click
Button3.Enabled = True
Button4.Enabled = True
Label16.Visible = False
Label17.Visible = False
Label18.Visible = False
Label19.Visible = False
Button3.Visible = False
Button4.Visible = False
ClearTextBoxes()
DataGrid2.Visible = False
DataGrid1.Visible = True
UpdateRecord()
End Sub
Sub Button3_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button3.Click
Label18.Text = Label18.Text + 1
Button3.Enabled = False
End Sub
Sub Button4_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button4.Click
Label19.Text = Label19.Text + 1
Button4.Enabled = False
End Sub
Function GetCurrentActivity(ByVal CurrentActivityID As String)
Dim SqlCon As New SqlClient.SqlConnection()
Dim DA As New SqlClient.SqlDataAdapter()
Dim SqlCmd As New SqlClient.SqlCommand()
Dim DS As New DataSet()
Dim SQLSelectStatement As String
Dim ColumnNames As String
Dim FromClause As String
Dim WhereClause As String
Dim OrderClause As String
SqlCon = WebBizDev.Utilities.SQLConnect("ServerName", "USER",
"PASSWORD", "DBNAME")
ColumnNames = WebBizDev.Utilities.SQLColumnNames("BDCallsMade as
'Calls', BDEmailsSent as 'Emails', activity_ID as ActID, company.company
as
Company, date as 'Activity Date', followup as 'Follow Up', notes as
Notes,
notes_confidential as Confidential")
FromClause = WebBizDev.Utilities.SQLFromClause("activity Activity
INNER JOIN Company ON Activity.Client_Company_ID = Company.Company_ID")
WhereClause = WebBizDev.Utilities.SQLWhereClause("(activity_cd =
2
and client_id =" & Session("CurrentID") & ")")
OrderClause = WebBizDev.Utilities.SQLOrderClause("date Desc")
SqlCmd.CommandText = "Select " & ColumnNames & FromClause &
WhereClause & OrderClause
SqlCmd.Connection = SqlCon
DA.SelectCommand = SqlCmd
DA.Fill(DS, "Second")
SqlCon.Close()
Return DS
End Function
Function GetPersonRecords()
Dim SqlCon As New SqlClient.SqlConnection()
Dim DA As New SqlClient.SqlDataAdapter()
Dim SqlCmd As New SqlClient.SqlCommand()
Dim DS As New DataSet()
Dim SQLSelectStatement As String
Dim ColumnNames As String
Dim FromClause As String
Dim WhereClause As String
Dim OrderClause As String
SqlCon = WebBizDev.Utilities.SQLConnect("ServerName", "USER",
"PASSWORD", "DBNAME")
ColumnNames = WebBizDev.Utilities.SQLColumnNames("Activity.Date
AS
Activity_Date, Activity.FollowUp AS Activity_FollowUp, Activity.Notes AS
Activity_Notes, Activity.Notes_Confidential AS
Activity_Notes_Confidential,
Client.Name_Last AS Client_Name_Last, Client.Name_First AS
Client_Name_First, Client.Name_Full AS Client_Name_Full,
Client.Title_Long
AS Client_Title_Long, Company1.Company AS Client_Company,
Client.Person_ID
AS Client_Person_ID, Activity_ID")
FromClause = WebBizDev.Utilities.SQLFromClause("Person Applicant
LEFT OUTER JOIN Company ON Applicant.Company_ID = Company.Company_ID
RIGHT
OUTER JOIN Person Client RIGHT OUTER JOIN Job RIGHT OUTER JOIN Activity
LEFT OUTER JOIN Company Company1 ON Activity.Client_Company_ID =
Company1.Company_ID ON Job.Job_ID = Activity.Job_ID ON Client.Person_ID =
Activity.Client_ID ON Applicant.Person_ID = Activity.Applicant_ID")
WhereClause = WebBizDev.Utilities.SQLWhereClause
("(Activity.Activity_CD = 2) and (Activity.Date >='" & txtSearchDate.Text
()
& "') and (Activity.agent like '" & DropDownList1.SelectedItem.Text &
"')")
OrderClause = WebBizDev.Utilities.SQLOrderClause
("Activity.FollowUp
DESC")
SqlCmd.CommandText = "Select " & ColumnNames & FromClause &
WhereClause & OrderClause
SqlCmd.Connection = SqlCon
DA.SelectCommand = SqlCmd
DA.Fill(DS, "First")
SqlCon.Close()
Return DS
End Function
#End Region
End Class
Public Class Utilities
#Region " Utility "
Public Shared Sub CreateConfirmBox(ByRef btn As WebControls.Button,
ByVal strMesUSERge As String)
btn.Attributes.Add("onclick", "return confirm('" & strMesUSERge &
"');")
End Sub
Shared Function SQLConnect(ByVal ServerName As String, ByVal UserName
As String, ByVal Password As String, ByVal Database As String) As
SqlClient.SqlConnection
Dim sqlcon As New SqlClient.SqlConnection() ' Dim new SQL
connection object
sqlcon.ConnectionString = "Server=" & ServerName & ";User ID=" &
UserName & ";password=" & Password & ";Database=" & Database ' Connection
string
sqlcon.Open() ' Open a connection to the DB
Return sqlcon
End Function
Shared Function SQLColumnNames(ByVal Columns As String) As String
Dim ColumnNames As String
ColumnNames = Columns
Return ColumnNames
End Function
Shared Function SQLFromClause(ByVal TableName As String) As String
Dim FromClause As String
FromClause = " From " & TableName
Return FromClause
End Function
Shared Function SQLWhereClause(ByVal Criteria As String) As String
Dim WhereClause As String
WhereClause = " Where " & Criteria
Return WhereClause
End Function
Shared Function SQLOrderClause(ByVal OrderColumn As String) As String
Dim OrderClause As String
OrderClause = " Order by " & OrderColumn
Return OrderClause
End Function
#End Region
End Class
not
sure if I am doing things correctly.
I have a datagrid bound to a dataset. The datagrid binds fine and all of
my records are displayed correctly.
When I select a row using a button column, I need the values of the data
in
the selected row to populate several textboxes on the form. The
textboxes
are for editing the row's data.
I do not want to edit directly in the datagrid.
The way I am doing this now is with the following code:
Sub FillTextBoxes(ByVal e As DataGridCommandEventArgs)
If e.Item.Cells(1).Text.Equals(" ") Then
txtFullname.Text = ""
Else
txtFullname.Text = e.Item.Cells(1).Text
End If
If e.Item.Cells(2).Text.Equals(" ") Then
txtCompany.Text = ""
Else
txtCompany.Text = e.Item.Cells(2).Text
End If
If e.Item.Cells(3).Text.Equals(" ") Then
txtTitle.Text = ""
Else
txtTitle.Text = e.Item.Cells(3).Text
End If
If e.Item.Cells(4).Text.Equals(" ") Then
txtNotes.Text = ""
Else
txtNotes.Text = e.Item.Cells(4).Text
End If
If e.Item.Cells(5).Text.Equals(" ") Then
txtConfNotes.Text = ""
Else
txtConfNotes.Text = e.Item.Cells(5).Text
End If
If e.Item.Cells(6).Text.Equals(" ") Then
txtActDate.Text = ""
Else
txtActDate.Text = e.Item.Cells(6).Text
End If
If e.Item.Cells(7).Text.Equals(" ") Then
txtFollowUpDate.Text = ""
Else
txtFollowUpDate.Text = e.Item.Cells(7).Text
End If
End Sub
This is checking the values of each cell of the selected row in the
datagrid.
Is there a better approach?
Is there any way to know which row has been selected in the DATASET as
opposed to the values rendered in the datagrid? If so, is it possible to
maintain concurrency with the selected datagrid row and the textboxes?
How
would this be implemented?
Here is all of my code:
Public Class WebForm1
#Region " Boring Shit "
Inherits System.Web.UI.Page
Protected WithEvents DropDownList1 As
System.Web.UI.WebControls.DropDownList
Protected WithEvents Button1 As System.Web.UI.WebControls.Button
Protected WithEvents Label1 As System.Web.UI.WebControls.Label
Protected WithEvents Label2 As System.Web.UI.WebControls.Label
Protected WithEvents Label3 As System.Web.UI.WebControls.Label
Protected WithEvents Label4 As System.Web.UI.WebControls.Label
Protected WithEvents Label5 As System.Web.UI.WebControls.Label
Protected WithEvents Label6 As System.Web.UI.WebControls.Label
Protected WithEvents Label7 As System.Web.UI.WebControls.Label
Protected WithEvents Label8 As System.Web.UI.WebControls.Label
Protected WithEvents Label9 As System.Web.UI.WebControls.Label
Protected WithEvents Label10 As System.Web.UI.WebControls.Label
Protected WithEvents Button2 As System.Web.UI.WebControls.Button
Protected WithEvents Label11 As System.Web.UI.WebControls.Label
Protected WithEvents Label12 As System.Web.UI.WebControls.Label
Protected WithEvents CheckBoxList1 As
System.Web.UI.WebControls.CheckBoxList
Protected WithEvents txtFullname As System.Web.UI.WebControls.TextBox
Protected WithEvents txtCompany As System.Web.UI.WebControls.TextBox
Protected WithEvents txtTitle As System.Web.UI.WebControls.TextBox
Protected WithEvents txtConfNotes As
System.Web.UI.WebControls.TextBox
Protected WithEvents txtActDate As System.Web.UI.WebControls.TextBox
Protected WithEvents txtFollowUpDate As
System.Web.UI.WebControls.TextBox
Protected WithEvents txtBPhone As System.Web.UI.WebControls.TextBox
Protected WithEvents txtWorkExt As System.Web.UI.WebControls.TextBox
Protected WithEvents Label13 As System.Web.UI.WebControls.Label
Protected WithEvents txtSearchDate As
System.Web.UI.WebControls.TextBox
Protected WithEvents Label14 As System.Web.UI.WebControls.Label
Protected WithEvents DataGrid1 As System.Web.UI.WebControls.DataGrid
Protected WithEvents Label15 As System.Web.UI.WebControls.Label
Protected WithEvents DataGrid2 As System.Web.UI.WebControls.DataGrid
Protected WithEvents txtClientCell As
System.Web.UI.WebControls.TextBox
Protected WithEvents Button3 As System.Web.UI.WebControls.Button
Protected WithEvents Button4 As System.Web.UI.WebControls.Button
Protected WithEvents Label16 As System.Web.UI.WebControls.Label
Protected WithEvents Label17 As System.Web.UI.WebControls.Label
Protected WithEvents Label18 As System.Web.UI.WebControls.Label
Protected WithEvents Label19 As System.Web.UI.WebControls.Label
Protected WithEvents txtNotes As System.Web.UI.WebControls.TextBox
#End Region
#Region " Web Form Designer Generated Code (More Boring Shit) "
<System.Diagnostics.DebuggerStepThrough()> Private Sub
InitializeComponent()
End Sub
Private Sub Page_Init(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub
#End Region
#Region " Developer Generated Code (The Real Shit) "
Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Utilities.CreateConfirmBox(Me.Button2, _
"Are you sure you want to update the record?")
End Sub
Sub BindDataGrid(ByVal DataGridName As
System.Web.UI.WebControls.DataGrid, ByVal FuncToCall As DataSet, ByVal
DGKeyField As String, ByVal DGDataMember As String)
Dim ds As DataSet
ds = FuncToCall
DataGridName.DataSource = ds 'Define which dataset to be used by
datagrid1
DataGridName.DataKeyField = DGKeyField ' Define a primary key for
the Dataset
DataGridName.DataMember = DGDataMember
DataGridName.DataBind() 'Bind the datagrid to it's datasource
End Sub
Sub FillTextBoxes(ByVal e As DataGridCommandEventArgs)
If e.Item.Cells(1).Text.Equals(" ") Then
txtFullname.Text = ""
Else
txtFullname.Text = e.Item.Cells(1).Text
End If
If e.Item.Cells(2).Text.Equals(" ") Then
txtCompany.Text = ""
Else
txtCompany.Text = e.Item.Cells(2).Text
End If
If e.Item.Cells(3).Text.Equals(" ") Then
txtTitle.Text = ""
Else
txtTitle.Text = e.Item.Cells(3).Text
End If
If e.Item.Cells(4).Text.Equals(" ") Then
txtNotes.Text = ""
Else
txtNotes.Text = e.Item.Cells(4).Text
End If
If e.Item.Cells(5).Text.Equals(" ") Then
txtConfNotes.Text = ""
Else
txtConfNotes.Text = e.Item.Cells(5).Text
End If
If e.Item.Cells(6).Text.Equals(" ") Then
txtActDate.Text = ""
Else
txtActDate.Text = e.Item.Cells(6).Text
End If
If e.Item.Cells(7).Text.Equals(" ") Then
txtFollowUpDate.Text = ""
Else
txtFollowUpDate.Text = e.Item.Cells(7).Text
End If
'LoadSessionValues()
End Sub
Sub LoadSessionValues()
Session("FullName") = txtFullname.Text.ToString
Session("Title") = txtTitle.Text.ToString
Session("Company") = txtCompany.Text.ToString
Session("ActDate") = txtActDate.Text.ToString
Session("FollowUpDate") = txtFollowUpDate.Text.ToString
Session("Notes") = txtNotes.Text.ToString
Session("ConfNotes") = txtConfNotes.Text.ToString
End Sub
Sub ClearTextBoxes()
txtFullname.Text() = ""
txtTitle.Text() = ""
txtCompany.Text() = ""
txtNotes.Text() = ""
txtConfNotes.Text() = ""
txtActDate.Text() = ""
txtFollowUpDate.Text() = ""
txtBPhone.Text() = ""
txtWorkExt.Text() = ""
txtClientCell.Text() = ""
End Sub
Sub UpdateLabels()
'Shows page count for DG paging events
Label14.Text = "Displaying page " & (DataGrid1.CurrentPageIndex +
1).ToString() & " of " & DataGrid1.PageCount
End Sub
Sub UpdateRecord()
LoadSessionValues()
Dim SqlCon As New SqlClient.SqlConnection()
Dim DA As New SqlClient.SqlDataAdapter()
Dim SqlCmd As New SqlClient.SqlCommand()
Dim DS As New DataSet()
Dim SQLSelectStatement As String
Dim ColumnNames As String
Dim FromClause As String
Dim WhereClause As String
Dim OrderClause As String
SqlCon = WebBizDev.Utilities.SQLConnect("ServerName", "USER",
"PASSWORD", "DBNAME")
'Dim sqltran1 As SqlClient.SqlTranUSERction
'Dim sqlcmd0 As New SqlClient.SqlCommand()
''Dim sqlcon = DBConnect()
'Dim ds2 As New DataSet()
'Dim da2 As New SqlClient.SqlDataAdapter()
'Dim Now() As DateTime
''sqlcmd0.Connection = sqlcon
''sqltran1 = sqlcon.BeginTranUSERction
'sqlcmd0.TranUSERction = sqltran1
'sqlcmd0.CommandText = "Update person set name_full = @NameFull,
Company1 = @Company, Title1 = @Title, Bphone = @BPhone, BPhone_Extension
=
@BphoneExt, CellPhone= @Cell where person_ID =" & Session("CurrentID") &
";Update Activity set Notes = @Notes, Notes_Confidential = @ConfNotes,
Date
= @ActDate, FollowUp = @FollowUpDate, BDCallsMade = @BDCallsMade,
BDEmailsSent = @BDEmailsSent where activity_ID = " & Session
("CurrentActivityID")
'Dim SplitName As Array
'Dim lName
'Dim fName
''Splitting SearchBox at the comma
'SplitName = Split(txtFullname.Text(), ",")
''Load lName into the first position of the array
'lName = SplitName(0)
''Append a percent sign for wildcard
'Session("LastName") = lName
''Load fName into second position of the array
''fName = SplitName(1)
''Append a percent sign for wildcard
'Session("FirstName") = fName
''----------------------------------------------
''Add sql parameters
''----------------------------------------------
''FullName
'sqlcmd0.Parameters.Add("@NameFull", DbType.String)
'sqlcmd0.Parameters("@NameFull").Direction =
ParameterDirection.Input
'sqlcmd0.Parameters("@NameFull").Value = Session
("FullName").ToString
'''FirstName
''sqlcmd0.Parameters.Add("@FirstName", DbType.String)
''sqlcmd0.Parameters("@FirstName").Direction =
ParameterDirection.Input
''sqlcmd0.Parameters("@FirstName").Value = Session("FirstName")
'''LastName
''sqlcmd0.Parameters.Add("@LastName", DbType.String)
''sqlcmd0.Parameters("@LastName").Direction =
ParameterDirection.Input
''sqlcmd0.Parameters("@LastName").Value = Session("LastName")
''Company
'sqlcmd0.Parameters.Add("@Company", DbType.String)
'sqlcmd0.Parameters("@Company").Direction =
ParameterDirection.Input
'sqlcmd0.Parameters("@Company").Value = txtCompany.Text
''Title
'sqlcmd0.Parameters.Add("@Title", DbType.String)
'sqlcmd0.Parameters("@Title").Direction =
ParameterDirection.Input
'sqlcmd0.Parameters("@Title").Value = txtTitle.Text
''Bphone
'sqlcmd0.Parameters.Add("@Bphone", DbType.VarNumeric)
'sqlcmd0.Parameters("@Bphone").Direction =
ParameterDirection.Input
'sqlcmd0.Parameters("@Bphone").Value = txtBPhone.Text
''Bphone Ext
'sqlcmd0.Parameters.Add("@BphoneExt", DbType.VarNumeric)
'sqlcmd0.Parameters("@BphoneExt").Direction =
ParameterDirection.Input
'sqlcmd0.Parameters("@BphoneExt").Value = txtWorkExt.Text
''Cell
'sqlcmd0.Parameters.Add("@Cell", DbType.VarNumeric)
'sqlcmd0.Parameters("@Cell").Direction = ParameterDirection.Input
'sqlcmd0.Parameters("@Cell").Value = txtClientCell.Text
''Notes
'sqlcmd0.Parameters.Add("@Notes", DbType.String)
'sqlcmd0.Parameters("@Notes").Direction =
ParameterDirection.Input
'sqlcmd0.Parameters("@Notes").Value = txtNotes.Text
''ConfNotes
'sqlcmd0.Parameters.Add("@ConfNotes", DbType.String)
'sqlcmd0.Parameters("@ConfNotes").Direction =
ParameterDirection.Input
'sqlcmd0.Parameters("@ConfNotes").Value = txtConfNotes.Text
''BDCallsMade
'sqlcmd0.Parameters.Add("@BDCallsMade", DbType.String)
'sqlcmd0.Parameters("@BDCallsMade").Direction =
ParameterDirection.Input
'sqlcmd0.Parameters("@BDCallsMade").Value = Label18.Text
''BDEmailsSent
'sqlcmd0.Parameters.Add("@BDEmailsSent", DbType.String)
'sqlcmd0.Parameters("@BDEmailsSent").Direction =
ParameterDirection.Input
'sqlcmd0.Parameters("@BDEmailsSent").Value = Label19.Text
''ActDate
'sqlcmd0.Parameters.Add("@ActDate", DbType.Date)
'sqlcmd0.Parameters("@ActDate").Direction =
ParameterDirection.Input
'sqlcmd0.Parameters("@ActDate").Value = txtActDate.Text
''FollowUpDate
'sqlcmd0.Parameters.Add("@FollowUpDate", DbType.Date)
'sqlcmd0.Parameters("@FollowUpDate").Direction =
ParameterDirection.Input
'sqlcmd0.Parameters("@FollowUpDate").Value = txtFollowUpDate.Text
''execute sql command, not using dataset, so ExecuteNonQuery must
be used
'sqlcmd0.ExecuteNonQuery()
''if everything was successfull, commit to sql
'sqltran1.Commit()
''sqlcon.Close()
End Sub
Sub DataGrid1_Paging(ByVal sender As Object, ByVal e As
DataGridPageChangedEventArgs)
DataGrid1.CurrentPageIndex = e.NewPageIndex ' tell the datagrid
that we are on a new page
BindDataGrid(DataGrid1, GetPersonRecords, "Client_Person_ID",
"First") ' Call Drawdata to fill the dataset and bind the datagrid
UpdateLabels() ' call to update labels to show record and page
counts
ClearTextBoxes()
DataGrid1.SelectedIndex = -1 ' Tell the datagrid that nothing is
selected
End Sub
Sub DataGrid_Select(ByVal sender As Object, ByVal e As
DataGridCommandEventArgs)
Dim ds As DataSet
Session("CurrentID") = e.Item.Cells(8).Text.ToString
Session("CurrentActivityID") = e.Item.Cells(9).Text.ToString
ds = GetCurrentActivity(Session("CurrentActivityID"))
BindDataGrid(DataGrid2, ds, "ActID", "Second")
FillTextBoxes(e)
LoadSessionValues()
DataGrid2.Visible = True
Label18.Visible = True
Label18.Text = ds.Tables("Second").Rows(0).Item(0).ToString
Label19.Visible = True
Label19.Text = ds.Tables("Second").Rows(0).Item(1).ToString
Button3.Visible = True
Button4.Visible = True
Label16.Visible = True
Label17.Visible = True
Button3.Enabled = True
Button4.Enabled = True
DataGrid1.Visible = False
End Sub
Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim ds As DataSet
ds = GetPersonRecords()
ClearTextBoxes() 'Clear Data from textboxes
BindDataGrid(DataGrid1, ds, "Client_person_ID", "First") 'Query
for
data
DataGrid1.CurrentPageIndex = 0 ' Returns DG to page 0
UpdateLabels() ' call to update labels to show record and page
counts
'Scorecard buttons and labels are hidden when a query is issued
'-->
Button3.Enabled = True
Button4.Enabled = True
Label16.Visible = False
Label17.Visible = False
Label18.Visible = False
Label19.Visible = False
Button3.Visible = False
Button4.Visible = False
'<--
DataGrid1.Visible = True '>> Used to show second DG with
activity
history
DataGrid2.Visible = False '>> Used to show second DG with
activity
history
Label13.Text = ds.Tables("First").Rows.Count.ToString & " Records
Found" ' Fill the label with the number of records in the data set DS
End Sub
Sub Button2_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button2.Click
Button3.Enabled = True
Button4.Enabled = True
Label16.Visible = False
Label17.Visible = False
Label18.Visible = False
Label19.Visible = False
Button3.Visible = False
Button4.Visible = False
ClearTextBoxes()
DataGrid2.Visible = False
DataGrid1.Visible = True
UpdateRecord()
End Sub
Sub Button3_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button3.Click
Label18.Text = Label18.Text + 1
Button3.Enabled = False
End Sub
Sub Button4_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button4.Click
Label19.Text = Label19.Text + 1
Button4.Enabled = False
End Sub
Function GetCurrentActivity(ByVal CurrentActivityID As String)
Dim SqlCon As New SqlClient.SqlConnection()
Dim DA As New SqlClient.SqlDataAdapter()
Dim SqlCmd As New SqlClient.SqlCommand()
Dim DS As New DataSet()
Dim SQLSelectStatement As String
Dim ColumnNames As String
Dim FromClause As String
Dim WhereClause As String
Dim OrderClause As String
SqlCon = WebBizDev.Utilities.SQLConnect("ServerName", "USER",
"PASSWORD", "DBNAME")
ColumnNames = WebBizDev.Utilities.SQLColumnNames("BDCallsMade as
'Calls', BDEmailsSent as 'Emails', activity_ID as ActID, company.company
as
Company, date as 'Activity Date', followup as 'Follow Up', notes as
Notes,
notes_confidential as Confidential")
FromClause = WebBizDev.Utilities.SQLFromClause("activity Activity
INNER JOIN Company ON Activity.Client_Company_ID = Company.Company_ID")
WhereClause = WebBizDev.Utilities.SQLWhereClause("(activity_cd =
2
and client_id =" & Session("CurrentID") & ")")
OrderClause = WebBizDev.Utilities.SQLOrderClause("date Desc")
SqlCmd.CommandText = "Select " & ColumnNames & FromClause &
WhereClause & OrderClause
SqlCmd.Connection = SqlCon
DA.SelectCommand = SqlCmd
DA.Fill(DS, "Second")
SqlCon.Close()
Return DS
End Function
Function GetPersonRecords()
Dim SqlCon As New SqlClient.SqlConnection()
Dim DA As New SqlClient.SqlDataAdapter()
Dim SqlCmd As New SqlClient.SqlCommand()
Dim DS As New DataSet()
Dim SQLSelectStatement As String
Dim ColumnNames As String
Dim FromClause As String
Dim WhereClause As String
Dim OrderClause As String
SqlCon = WebBizDev.Utilities.SQLConnect("ServerName", "USER",
"PASSWORD", "DBNAME")
ColumnNames = WebBizDev.Utilities.SQLColumnNames("Activity.Date
AS
Activity_Date, Activity.FollowUp AS Activity_FollowUp, Activity.Notes AS
Activity_Notes, Activity.Notes_Confidential AS
Activity_Notes_Confidential,
Client.Name_Last AS Client_Name_Last, Client.Name_First AS
Client_Name_First, Client.Name_Full AS Client_Name_Full,
Client.Title_Long
AS Client_Title_Long, Company1.Company AS Client_Company,
Client.Person_ID
AS Client_Person_ID, Activity_ID")
FromClause = WebBizDev.Utilities.SQLFromClause("Person Applicant
LEFT OUTER JOIN Company ON Applicant.Company_ID = Company.Company_ID
RIGHT
OUTER JOIN Person Client RIGHT OUTER JOIN Job RIGHT OUTER JOIN Activity
LEFT OUTER JOIN Company Company1 ON Activity.Client_Company_ID =
Company1.Company_ID ON Job.Job_ID = Activity.Job_ID ON Client.Person_ID =
Activity.Client_ID ON Applicant.Person_ID = Activity.Applicant_ID")
WhereClause = WebBizDev.Utilities.SQLWhereClause
("(Activity.Activity_CD = 2) and (Activity.Date >='" & txtSearchDate.Text
()
& "') and (Activity.agent like '" & DropDownList1.SelectedItem.Text &
"')")
OrderClause = WebBizDev.Utilities.SQLOrderClause
("Activity.FollowUp
DESC")
SqlCmd.CommandText = "Select " & ColumnNames & FromClause &
WhereClause & OrderClause
SqlCmd.Connection = SqlCon
DA.SelectCommand = SqlCmd
DA.Fill(DS, "First")
SqlCon.Close()
Return DS
End Function
#End Region
End Class
Public Class Utilities
#Region " Utility "
Public Shared Sub CreateConfirmBox(ByRef btn As WebControls.Button,
ByVal strMesUSERge As String)
btn.Attributes.Add("onclick", "return confirm('" & strMesUSERge &
"');")
End Sub
Shared Function SQLConnect(ByVal ServerName As String, ByVal UserName
As String, ByVal Password As String, ByVal Database As String) As
SqlClient.SqlConnection
Dim sqlcon As New SqlClient.SqlConnection() ' Dim new SQL
connection object
sqlcon.ConnectionString = "Server=" & ServerName & ";User ID=" &
UserName & ";password=" & Password & ";Database=" & Database ' Connection
string
sqlcon.Open() ' Open a connection to the DB
Return sqlcon
End Function
Shared Function SQLColumnNames(ByVal Columns As String) As String
Dim ColumnNames As String
ColumnNames = Columns
Return ColumnNames
End Function
Shared Function SQLFromClause(ByVal TableName As String) As String
Dim FromClause As String
FromClause = " From " & TableName
Return FromClause
End Function
Shared Function SQLWhereClause(ByVal Criteria As String) As String
Dim WhereClause As String
WhereClause = " Where " & Criteria
Return WhereClause
End Function
Shared Function SQLOrderClause(ByVal OrderColumn As String) As String
Dim OrderClause As String
OrderClause = " Order by " & OrderColumn
Return OrderClause
End Function
#End Region
End Class