G
Guest
Can someone please tell me why the datagrid doesn't databind and display rows
once the Add button is clicked and the cmdSaveToDataset event fires, to add a
row to the dataset? I've copied the code:
-------------------
Public Class WebForm1
Inherits System.Web.UI.Page
#Region " Web Form Designer Generated Code "
'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private Sub
InitializeComponent()
Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection
Me.DataView1 = New System.Data.DataView
Me.SqlDataAdapter1 = New System.Data.SqlClient.SqlDataAdapter
Me.SqlSelectCommand1 = New System.Data.SqlClient.SqlCommand
Me.SqlInsertCommand1 = New System.Data.SqlClient.SqlCommand
Me.SqlUpdateCommand1 = New System.Data.SqlClient.SqlCommand
Me.SqlDeleteCommand1 = New System.Data.SqlClient.SqlCommand
Me.DataSet21 = New _051003._2.DataSet2
CType(Me.DataView1,
System.ComponentModel.ISupportInitialize).BeginInit()
CType(Me.DataSet21,
System.ComponentModel.ISupportInitialize).BeginInit()
'
'SqlConnection1
'
Me.SqlConnection1.ConnectionString = "data source=MAGELLAN;initial
catalog=SS;password=;persist security info=Tr" & _
"ue;user id=ssUser2;workstation id=""HOPPER-EVO"";packet size=4096"
'
'DataView1
'
Me.DataView1.Table = Me.DataSet21.tblProjects
'
'SqlDataAdapter1
'
Me.SqlDataAdapter1.DeleteCommand = Me.SqlDeleteCommand1
Me.SqlDataAdapter1.InsertCommand = Me.SqlInsertCommand1
Me.SqlDataAdapter1.SelectCommand = Me.SqlSelectCommand1
Me.SqlDataAdapter1.TableMappings.AddRange(New
System.Data.Common.DataTableMapping() {New
System.Data.Common.DataTableMapping("Table", "tblProjects", New
System.Data.Common.DataColumnMapping() {New
System.Data.Common.DataColumnMapping("Name", "Name"), New
System.Data.Common.DataColumnMapping("Description", "Description"), New
System.Data.Common.DataColumnMapping("Department", "Department"), New
System.Data.Common.DataColumnMapping("CurrentStatus", "CurrentStatus"), New
System.Data.Common.DataColumnMapping("ProjectLead", "ProjectLead"), New
System.Data.Common.DataColumnMapping("LastUpdate", "LastUpdate"), New
System.Data.Common.DataColumnMapping("ID", "ID")})})
Me.SqlDataAdapter1.UpdateCommand = Me.SqlUpdateCommand1
'
'SqlSelectCommand1
'
Me.SqlSelectCommand1.CommandText = "SELECT Name, Description,
Department, CurrentStatus, ProjectLead, LastUpdate, ID " & _
"FROM dbo.tblProjects"
Me.SqlSelectCommand1.Connection = Me.SqlConnection1
'
'SqlInsertCommand1
'
Me.SqlInsertCommand1.CommandText = "INSERT INTO
dbo.tblProjects(Name, Description, Department, CurrentStatus, Project" & _
"Lead, LastUpdate) VALUES (@Name, @Description, @Department,
@CurrentStatus, @Pro" & _
"jectLead, @LastUpdate); SELECT Name, Description, Department,
CurrentStatus, Pro" & _
"jectLead, LastUpdate, ID FROM dbo.tblProjects WHERE (ID =
@@IDENTITY)"
Me.SqlInsertCommand1.Connection = Me.SqlConnection1
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Name", System.Data.SqlDbType.VarChar,
50, "Name"))
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Description",
System.Data.SqlDbType.VarChar, 300, "Description"))
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Department",
System.Data.SqlDbType.VarChar, 100, "Department"))
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@CurrentStatus",
System.Data.SqlDbType.VarChar, 50, "CurrentStatus"))
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@ProjectLead",
System.Data.SqlDbType.VarChar, 50, "ProjectLead"))
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@LastUpdate",
System.Data.SqlDbType.DateTime, 8, "LastUpdate"))
'
'SqlUpdateCommand1
'
Me.SqlUpdateCommand1.CommandText = "UPDATE dbo.tblProjects SET Name
= @Name, Description = @Description, Department =" & _
" @Department, CurrentStatus = @CurrentStatus, ProjectLead =
@ProjectLead, LastUp" & _
"date = @LastUpdate WHERE (ID = @Original_ID) AND (CurrentStatus =
@Original_Curr" & _
"entStatus OR @Original_CurrentStatus IS NULL AND CurrentStatus IS
NULL) AND (Dep" & _
"artment = @Original_Department OR @Original_Department IS NULL AND
Department IS" & _
" NULL) AND (Description = @Original_Description OR
@Original_Description IS NULL" & _
" AND Description IS NULL) AND (LastUpdate = @Original_LastUpdate OR
@Original_La" & _
"stUpdate IS NULL AND LastUpdate IS NULL) AND (Name = @Original_Name
OR @Original" & _
"_Name IS NULL AND Name IS NULL) AND (ProjectLead =
@Original_ProjectLead OR @Ori" & _
"ginal_ProjectLead IS NULL AND ProjectLead IS NULL); SELECT Name,
Description, De" & _
"partment, CurrentStatus, ProjectLead, LastUpdate, ID FROM
dbo.tblProjects WHERE " & _
"(ID = @ID)"
Me.SqlUpdateCommand1.Connection = Me.SqlConnection1
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Name", System.Data.SqlDbType.VarChar,
50, "Name"))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Description",
System.Data.SqlDbType.VarChar, 300, "Description"))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Department",
System.Data.SqlDbType.VarChar, 100, "Department"))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@CurrentStatus",
System.Data.SqlDbType.VarChar, 50, "CurrentStatus"))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@ProjectLead",
System.Data.SqlDbType.VarChar, 50, "ProjectLead"))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@LastUpdate",
System.Data.SqlDbType.DateTime, 8, "LastUpdate"))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_ID", System.Data.SqlDbType.Int,
4, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0,
Byte), "ID", System.Data.DataRowVersion.Original, Nothing))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_CurrentStatus",
System.Data.SqlDbType.VarChar, 50, System.Data.ParameterDirection.Input,
False, CType(0, Byte), CType(0, Byte), "CurrentStatus",
System.Data.DataRowVersion.Original, Nothing))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_Department",
System.Data.SqlDbType.VarChar, 100, System.Data.ParameterDirection.Input,
False, CType(0, Byte), CType(0, Byte), "Department",
System.Data.DataRowVersion.Original, Nothing))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_Description",
System.Data.SqlDbType.VarChar, 300, System.Data.ParameterDirection.Input,
False, CType(0, Byte), CType(0, Byte), "Description",
System.Data.DataRowVersion.Original, Nothing))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_LastUpdate",
System.Data.SqlDbType.DateTime, 8, System.Data.ParameterDirection.Input,
False, CType(0, Byte), CType(0, Byte), "LastUpdate",
System.Data.DataRowVersion.Original, Nothing))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_Name",
System.Data.SqlDbType.VarChar, 50, System.Data.ParameterDirection.Input,
False, CType(0, Byte), CType(0, Byte), "Name",
System.Data.DataRowVersion.Original, Nothing))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_ProjectLead",
System.Data.SqlDbType.VarChar, 50, System.Data.ParameterDirection.Input,
False, CType(0, Byte), CType(0, Byte), "ProjectLead",
System.Data.DataRowVersion.Original, Nothing))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@ID", System.Data.SqlDbType.Int, 4, "ID"))
'
'SqlDeleteCommand1
'
Me.SqlDeleteCommand1.CommandText = "DELETE FROM dbo.tblProjects
WHERE (ID = @Original_ID) AND (CurrentStatus = @Origi" & _
"nal_CurrentStatus OR @Original_CurrentStatus IS NULL AND
CurrentStatus IS NULL) " & _
"AND (Department = @Original_Department OR @Original_Department IS
NULL AND Depar" & _
"tment IS NULL) AND (Description = @Original_Description OR
@Original_Description" & _
" IS NULL AND Description IS NULL) AND (LastUpdate =
@Original_LastUpdate OR @Ori" & _
"ginal_LastUpdate IS NULL AND LastUpdate IS NULL) AND (Name =
@Original_Name OR @" & _
"Original_Name IS NULL AND Name IS NULL) AND (ProjectLead =
@Original_ProjectLead" & _
" OR @Original_ProjectLead IS NULL AND ProjectLead IS NULL)"
Me.SqlDeleteCommand1.Connection = Me.SqlConnection1
Me.SqlDeleteCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_ID", System.Data.SqlDbType.Int,
4, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0,
Byte), "ID", System.Data.DataRowVersion.Original, Nothing))
Me.SqlDeleteCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_CurrentStatus",
System.Data.SqlDbType.VarChar, 50, System.Data.ParameterDirection.Input,
False, CType(0, Byte), CType(0, Byte), "CurrentStatus",
System.Data.DataRowVersion.Original, Nothing))
Me.SqlDeleteCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_Department",
System.Data.SqlDbType.VarChar, 100, System.Data.ParameterDirection.Input,
False, CType(0, Byte), CType(0, Byte), "Department",
System.Data.DataRowVersion.Original, Nothing))
Me.SqlDeleteCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_Description",
System.Data.SqlDbType.VarChar, 300, System.Data.ParameterDirection.Input,
False, CType(0, Byte), CType(0, Byte), "Description",
System.Data.DataRowVersion.Original, Nothing))
Me.SqlDeleteCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_LastUpdate",
System.Data.SqlDbType.DateTime, 8, System.Data.ParameterDirection.Input,
False, CType(0, Byte), CType(0, Byte), "LastUpdate",
System.Data.DataRowVersion.Original, Nothing))
Me.SqlDeleteCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_Name",
System.Data.SqlDbType.VarChar, 50, System.Data.ParameterDirection.Input,
False, CType(0, Byte), CType(0, Byte), "Name",
System.Data.DataRowVersion.Original, Nothing))
Me.SqlDeleteCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_ProjectLead",
System.Data.SqlDbType.VarChar, 50, System.Data.ParameterDirection.Input,
False, CType(0, Byte), CType(0, Byte), "ProjectLead",
System.Data.DataRowVersion.Original, Nothing))
'
'DataSet21
'
Me.DataSet21.DataSetName = "DataSet2"
Me.DataSet21.Locale = New System.Globalization.CultureInfo("en-US")
CType(Me.DataView1,
System.ComponentModel.ISupportInitialize).EndInit()
CType(Me.DataSet21,
System.ComponentModel.ISupportInitialize).EndInit()
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 "Public Property Declarations"
Public ptySQLDataAdapter As SqlClient.SqlDataAdapter
Public ptySQLDataConnection As SqlClient.SqlConnection
Public ptyDataSet As DataSet
#End Region
#Region "Control Event Declarations"
Protected WithEvents Label1 As System.Web.UI.WebControls.Label
Protected WithEvents TextBox1 As System.Web.UI.WebControls.TextBox
Protected WithEvents TextBox2 As System.Web.UI.WebControls.TextBox
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 txtFirstName As System.Web.UI.WebControls.TextBox
Protected WithEvents txtLastName As System.Web.UI.WebControls.TextBox
Protected WithEvents txtPhone As System.Web.UI.WebControls.TextBox
Protected WithEvents txtAddress As System.Web.UI.WebControls.TextBox
Protected WithEvents txtID As System.Web.UI.WebControls.TextBox
Protected WithEvents cmdMoveForward As System.Web.UI.WebControls.Button
Protected WithEvents txtRecordNumber As System.Web.UI.WebControls.TextBox
Protected WithEvents cmdMoveBack As System.Web.UI.WebControls.Button
Protected WithEvents cmdAdd As System.Web.UI.WebControls.Button
Protected WithEvents cmdDelete As System.Web.UI.WebControls.Button
Protected WithEvents cmdSave As System.Web.UI.WebControls.Button
Protected WithEvents cmdEdit As System.Web.UI.WebControls.Button
Protected WithEvents cmdSaveToDataset As System.Web.UI.WebControls.Button
Protected WithEvents lblMode As System.Web.UI.WebControls.Label
Protected WithEvents SqlConnection1 As System.Data.SqlClient.SqlConnection
Protected WithEvents DataView1 As System.Data.DataView
Protected WithEvents SqlDataAdapter1 As
System.Data.SqlClient.SqlDataAdapter
Protected WithEvents SqlSelectCommand1 As System.Data.SqlClient.SqlCommand
Protected WithEvents SqlInsertCommand1 As System.Data.SqlClient.SqlCommand
Protected WithEvents SqlUpdateCommand1 As System.Data.SqlClient.SqlCommand
Protected WithEvents SqlDeleteCommand1 As System.Data.SqlClient.SqlCommand
Protected WithEvents DataSet21 As _051003._2.DataSet2
Protected WithEvents grdAuthors As System.Web.UI.WebControls.DataGrid
#End Region
Public Property RecordNumber() As Long
Get
Return Me.txtRecordNumber.Text
End Get
Set(ByVal Value As Long)
Me.txtRecordNumber.Text = Value
End Set
End Property
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
Dim strSQL As String
If Me.IsPostBack Then
ptyDataSet = Me.Session.Item(0)
ptySQLDataAdapter = Me.Session.Item(1)
ptySQLDataConnection = Me.Session.Item(2)
'SqlDataAdapter1.Fill(ptyDataSet)
'grdAuthors.DataBind()
Else
Me.RecordNumber = 1
Try
'Creating the connection object and opening the connection
ptySQLDataConnection = New SqlClient.SqlConnection("data
source=MAGELLAN;initial catalog=SS;password=;persist security info=True;user
id=ssUser2;workstation id='HOPPER-EVO';packet size=4096;")
ptySQLDataConnection.Open()
strSQL = "SELECT * FROM tblProjects"
'Creating the Data Adapter that will fill the DataSet
ptySQLDataAdapter = New SqlClient.SqlDataAdapter(strSQL,
ptySQLDataConnection)
ptyDataSet = New DataSet
'Filling the DataSet
ptySQLDataAdapter.Fill(ptyDataSet)
'Setting the DataGrids DataSource Property
'If Not Me.IsPostBack Then
grdAuthors.DataSource = ptyDataSet
grdAuthors.DataBind()
'End If
grdAuthors.SelectedIndex = Me.RecordNumber - 1 'The index
property is 0 based so record number 1 is index 0
Me.MoveToRecord(Me.RecordNumber)
Me.Session.Item("Dataset") = ptyDataSet
Me.Session.Item("DataAdapter") = ptySQLDataAdapter
Me.Session.Item("DataConnection") = ptySQLDataConnection
SqlDataAdapter1.Fill(DataSet21)
grdAuthors.DataBind()
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try
End If
End Sub
Public Sub MoveToRecord(ByVal Number As Long)
'Moving to the record that is currently displayed in record textbox
With ptyDataSet.Tables(0).Rows.Item(Number - 1)
Me.txtID.Text = .Item("ID")
Me.txtFirstName.Text = .Item("Description")
Me.txtLastName.Text = .Item("Department")
Me.txtPhone.Text = .Item("CurrentStatus")
Me.txtAddress.Text = .Item("ProjectLead")
End With
End Sub
Public Sub ChangeRecord()
Dim objDataRow As DataRow
Select Case Session.Item("RecordType")
Case 0
objDataRow = ptyDataSet.Tables(0).NewRow
With objDataRow
.Item("ID") = txtID.Text
.Item("Department") = txtLastName.Text
.Item("Description") = txtFirstName.Text
.Item("CurrentStatus") = txtPhone.Text
.Item("ProjectLead") = txtAddress.Text
End With
ptyDataSet.Tables(0).Rows.Add(objDataRow)
Case 2
objDataRow = ptyDataSet.Tables(0).Rows.Item(Me.RecordNumber
- 1)
objDataRow.Delete()
Case 1
With ptyDataSet.Tables(0).Rows.Item(Me.RecordNumber - 1)
.Item("ID") = txtID.Text
.Item("Department") = txtLastName.Text
.Item("Description") = txtFirstName.Text
.Item("CurrentStatus") = txtPhone.Text
.Item("ProjectLead") = txtAddress.Text
End With
End Select
SqlDataAdapter1.Update(ptyDataSet)
grdAuthors.DataBind()
End Sub
Public Sub cmdMoveForward_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles cmdMoveForward.Click
'If Me.RecordNumber >= ptyDataSet.Tables(0).Rows.Count Then Exit Sub
If Me.RecordNumber >= ptyDataSet.Tables(0).Rows.Count Then Exit Sub
Me.RecordNumber += 1
With grdAuthors
.SelectedIndex = Me.RecordNumber - 1
End With
Me.MoveToRecord(Me.RecordNumber)
End Sub
Private Sub cmdMoveBack_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles cmdMoveBack.Click
If Me.RecordNumber <= 1 Then Exit Sub
Me.RecordNumber -= 1
With grdAuthors
.SelectedIndex = Me.RecordNumber - 1
End With
Me.MoveToRecord(Me.RecordNumber)
End Sub
Private Sub cmdSave_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles cmdSave.Click
Try
Dim objCB As SqlClient.SqlCommandBuilder = New
SqlClient.SqlCommandBuilder(ptySQLDataAdapter)
ptySQLDataAdapter.Update(ptyDataSet)
Catch ex As Exception
'MsgBox(ex.Message)
End Try
lblMode.Text = "Saved record to DB"
End Sub
Private Sub cmdAdd_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles cmdAdd.Click
Session.Item("RecordType") = 0
Me.txtID.Text = ""
Me.txtFirstName.Text = ""
Me.txtLastName.Text = ""
Me.txtPhone.Text = ""
Me.txtAddress.Text = ""
lblMode.Text = "Add Mode"
End Sub
Private Sub cmdEdit_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles cmdEdit.Click
Session.Item("RecordType") = 1
lblMode.Text = "Edit Mode"
End Sub
Private Sub cmdDelete_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles cmdDelete.Click
Session.Item("RecordType") = 2
lblMode.Text = "Deleted record from DB"
Me.ChangeRecord()
With Me.grdAuthors
.DataSource = ptyDataSet
.DataBind()
End With
End Sub
Private Sub cmdSaveToDataset_Click(ByVal sender As System.Object, ByVal
e As System.EventArgs) Handles cmdSaveToDataset.Click
Me.ChangeRecord()
'SqlDataAdapter1.Update(ptyDataSet)
'SqlDataAdapter1.Fill(ptyDataSet)
With Me.grdAuthors
SqlDataAdapter1.Fill(ptyDataSet)
'.DataSource = ptyDataSet
.DataBind()
End With
grdAuthors.SelectedIndex = Me.RecordNumber - 1 'The index property
is 0 based so record number 1 is index 0
Me.MoveToRecord(Me.RecordNumber)
Me.Session.Item("Dataset") = ptyDataSet
Me.Session.Item("DataAdapter") = ptySQLDataAdapter
Me.Session.Item("DataConnection") = ptySQLDataConnection
'SqlDataAdapter1.Fill(DataSet21)
'grdAuthors.DataBind()
lblMode.Text = "Saved record to DS"
'grdAuthors.DataBind()
End Sub
End Class
once the Add button is clicked and the cmdSaveToDataset event fires, to add a
row to the dataset? I've copied the code:
-------------------
Public Class WebForm1
Inherits System.Web.UI.Page
#Region " Web Form Designer Generated Code "
'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private Sub
InitializeComponent()
Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection
Me.DataView1 = New System.Data.DataView
Me.SqlDataAdapter1 = New System.Data.SqlClient.SqlDataAdapter
Me.SqlSelectCommand1 = New System.Data.SqlClient.SqlCommand
Me.SqlInsertCommand1 = New System.Data.SqlClient.SqlCommand
Me.SqlUpdateCommand1 = New System.Data.SqlClient.SqlCommand
Me.SqlDeleteCommand1 = New System.Data.SqlClient.SqlCommand
Me.DataSet21 = New _051003._2.DataSet2
CType(Me.DataView1,
System.ComponentModel.ISupportInitialize).BeginInit()
CType(Me.DataSet21,
System.ComponentModel.ISupportInitialize).BeginInit()
'
'SqlConnection1
'
Me.SqlConnection1.ConnectionString = "data source=MAGELLAN;initial
catalog=SS;password=;persist security info=Tr" & _
"ue;user id=ssUser2;workstation id=""HOPPER-EVO"";packet size=4096"
'
'DataView1
'
Me.DataView1.Table = Me.DataSet21.tblProjects
'
'SqlDataAdapter1
'
Me.SqlDataAdapter1.DeleteCommand = Me.SqlDeleteCommand1
Me.SqlDataAdapter1.InsertCommand = Me.SqlInsertCommand1
Me.SqlDataAdapter1.SelectCommand = Me.SqlSelectCommand1
Me.SqlDataAdapter1.TableMappings.AddRange(New
System.Data.Common.DataTableMapping() {New
System.Data.Common.DataTableMapping("Table", "tblProjects", New
System.Data.Common.DataColumnMapping() {New
System.Data.Common.DataColumnMapping("Name", "Name"), New
System.Data.Common.DataColumnMapping("Description", "Description"), New
System.Data.Common.DataColumnMapping("Department", "Department"), New
System.Data.Common.DataColumnMapping("CurrentStatus", "CurrentStatus"), New
System.Data.Common.DataColumnMapping("ProjectLead", "ProjectLead"), New
System.Data.Common.DataColumnMapping("LastUpdate", "LastUpdate"), New
System.Data.Common.DataColumnMapping("ID", "ID")})})
Me.SqlDataAdapter1.UpdateCommand = Me.SqlUpdateCommand1
'
'SqlSelectCommand1
'
Me.SqlSelectCommand1.CommandText = "SELECT Name, Description,
Department, CurrentStatus, ProjectLead, LastUpdate, ID " & _
"FROM dbo.tblProjects"
Me.SqlSelectCommand1.Connection = Me.SqlConnection1
'
'SqlInsertCommand1
'
Me.SqlInsertCommand1.CommandText = "INSERT INTO
dbo.tblProjects(Name, Description, Department, CurrentStatus, Project" & _
"Lead, LastUpdate) VALUES (@Name, @Description, @Department,
@CurrentStatus, @Pro" & _
"jectLead, @LastUpdate); SELECT Name, Description, Department,
CurrentStatus, Pro" & _
"jectLead, LastUpdate, ID FROM dbo.tblProjects WHERE (ID =
@@IDENTITY)"
Me.SqlInsertCommand1.Connection = Me.SqlConnection1
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Name", System.Data.SqlDbType.VarChar,
50, "Name"))
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Description",
System.Data.SqlDbType.VarChar, 300, "Description"))
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Department",
System.Data.SqlDbType.VarChar, 100, "Department"))
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@CurrentStatus",
System.Data.SqlDbType.VarChar, 50, "CurrentStatus"))
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@ProjectLead",
System.Data.SqlDbType.VarChar, 50, "ProjectLead"))
Me.SqlInsertCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@LastUpdate",
System.Data.SqlDbType.DateTime, 8, "LastUpdate"))
'
'SqlUpdateCommand1
'
Me.SqlUpdateCommand1.CommandText = "UPDATE dbo.tblProjects SET Name
= @Name, Description = @Description, Department =" & _
" @Department, CurrentStatus = @CurrentStatus, ProjectLead =
@ProjectLead, LastUp" & _
"date = @LastUpdate WHERE (ID = @Original_ID) AND (CurrentStatus =
@Original_Curr" & _
"entStatus OR @Original_CurrentStatus IS NULL AND CurrentStatus IS
NULL) AND (Dep" & _
"artment = @Original_Department OR @Original_Department IS NULL AND
Department IS" & _
" NULL) AND (Description = @Original_Description OR
@Original_Description IS NULL" & _
" AND Description IS NULL) AND (LastUpdate = @Original_LastUpdate OR
@Original_La" & _
"stUpdate IS NULL AND LastUpdate IS NULL) AND (Name = @Original_Name
OR @Original" & _
"_Name IS NULL AND Name IS NULL) AND (ProjectLead =
@Original_ProjectLead OR @Ori" & _
"ginal_ProjectLead IS NULL AND ProjectLead IS NULL); SELECT Name,
Description, De" & _
"partment, CurrentStatus, ProjectLead, LastUpdate, ID FROM
dbo.tblProjects WHERE " & _
"(ID = @ID)"
Me.SqlUpdateCommand1.Connection = Me.SqlConnection1
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Name", System.Data.SqlDbType.VarChar,
50, "Name"))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Description",
System.Data.SqlDbType.VarChar, 300, "Description"))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Department",
System.Data.SqlDbType.VarChar, 100, "Department"))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@CurrentStatus",
System.Data.SqlDbType.VarChar, 50, "CurrentStatus"))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@ProjectLead",
System.Data.SqlDbType.VarChar, 50, "ProjectLead"))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@LastUpdate",
System.Data.SqlDbType.DateTime, 8, "LastUpdate"))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_ID", System.Data.SqlDbType.Int,
4, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0,
Byte), "ID", System.Data.DataRowVersion.Original, Nothing))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_CurrentStatus",
System.Data.SqlDbType.VarChar, 50, System.Data.ParameterDirection.Input,
False, CType(0, Byte), CType(0, Byte), "CurrentStatus",
System.Data.DataRowVersion.Original, Nothing))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_Department",
System.Data.SqlDbType.VarChar, 100, System.Data.ParameterDirection.Input,
False, CType(0, Byte), CType(0, Byte), "Department",
System.Data.DataRowVersion.Original, Nothing))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_Description",
System.Data.SqlDbType.VarChar, 300, System.Data.ParameterDirection.Input,
False, CType(0, Byte), CType(0, Byte), "Description",
System.Data.DataRowVersion.Original, Nothing))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_LastUpdate",
System.Data.SqlDbType.DateTime, 8, System.Data.ParameterDirection.Input,
False, CType(0, Byte), CType(0, Byte), "LastUpdate",
System.Data.DataRowVersion.Original, Nothing))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_Name",
System.Data.SqlDbType.VarChar, 50, System.Data.ParameterDirection.Input,
False, CType(0, Byte), CType(0, Byte), "Name",
System.Data.DataRowVersion.Original, Nothing))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_ProjectLead",
System.Data.SqlDbType.VarChar, 50, System.Data.ParameterDirection.Input,
False, CType(0, Byte), CType(0, Byte), "ProjectLead",
System.Data.DataRowVersion.Original, Nothing))
Me.SqlUpdateCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@ID", System.Data.SqlDbType.Int, 4, "ID"))
'
'SqlDeleteCommand1
'
Me.SqlDeleteCommand1.CommandText = "DELETE FROM dbo.tblProjects
WHERE (ID = @Original_ID) AND (CurrentStatus = @Origi" & _
"nal_CurrentStatus OR @Original_CurrentStatus IS NULL AND
CurrentStatus IS NULL) " & _
"AND (Department = @Original_Department OR @Original_Department IS
NULL AND Depar" & _
"tment IS NULL) AND (Description = @Original_Description OR
@Original_Description" & _
" IS NULL AND Description IS NULL) AND (LastUpdate =
@Original_LastUpdate OR @Ori" & _
"ginal_LastUpdate IS NULL AND LastUpdate IS NULL) AND (Name =
@Original_Name OR @" & _
"Original_Name IS NULL AND Name IS NULL) AND (ProjectLead =
@Original_ProjectLead" & _
" OR @Original_ProjectLead IS NULL AND ProjectLead IS NULL)"
Me.SqlDeleteCommand1.Connection = Me.SqlConnection1
Me.SqlDeleteCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_ID", System.Data.SqlDbType.Int,
4, System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0,
Byte), "ID", System.Data.DataRowVersion.Original, Nothing))
Me.SqlDeleteCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_CurrentStatus",
System.Data.SqlDbType.VarChar, 50, System.Data.ParameterDirection.Input,
False, CType(0, Byte), CType(0, Byte), "CurrentStatus",
System.Data.DataRowVersion.Original, Nothing))
Me.SqlDeleteCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_Department",
System.Data.SqlDbType.VarChar, 100, System.Data.ParameterDirection.Input,
False, CType(0, Byte), CType(0, Byte), "Department",
System.Data.DataRowVersion.Original, Nothing))
Me.SqlDeleteCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_Description",
System.Data.SqlDbType.VarChar, 300, System.Data.ParameterDirection.Input,
False, CType(0, Byte), CType(0, Byte), "Description",
System.Data.DataRowVersion.Original, Nothing))
Me.SqlDeleteCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_LastUpdate",
System.Data.SqlDbType.DateTime, 8, System.Data.ParameterDirection.Input,
False, CType(0, Byte), CType(0, Byte), "LastUpdate",
System.Data.DataRowVersion.Original, Nothing))
Me.SqlDeleteCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_Name",
System.Data.SqlDbType.VarChar, 50, System.Data.ParameterDirection.Input,
False, CType(0, Byte), CType(0, Byte), "Name",
System.Data.DataRowVersion.Original, Nothing))
Me.SqlDeleteCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@Original_ProjectLead",
System.Data.SqlDbType.VarChar, 50, System.Data.ParameterDirection.Input,
False, CType(0, Byte), CType(0, Byte), "ProjectLead",
System.Data.DataRowVersion.Original, Nothing))
'
'DataSet21
'
Me.DataSet21.DataSetName = "DataSet2"
Me.DataSet21.Locale = New System.Globalization.CultureInfo("en-US")
CType(Me.DataView1,
System.ComponentModel.ISupportInitialize).EndInit()
CType(Me.DataSet21,
System.ComponentModel.ISupportInitialize).EndInit()
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 "Public Property Declarations"
Public ptySQLDataAdapter As SqlClient.SqlDataAdapter
Public ptySQLDataConnection As SqlClient.SqlConnection
Public ptyDataSet As DataSet
#End Region
#Region "Control Event Declarations"
Protected WithEvents Label1 As System.Web.UI.WebControls.Label
Protected WithEvents TextBox1 As System.Web.UI.WebControls.TextBox
Protected WithEvents TextBox2 As System.Web.UI.WebControls.TextBox
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 txtFirstName As System.Web.UI.WebControls.TextBox
Protected WithEvents txtLastName As System.Web.UI.WebControls.TextBox
Protected WithEvents txtPhone As System.Web.UI.WebControls.TextBox
Protected WithEvents txtAddress As System.Web.UI.WebControls.TextBox
Protected WithEvents txtID As System.Web.UI.WebControls.TextBox
Protected WithEvents cmdMoveForward As System.Web.UI.WebControls.Button
Protected WithEvents txtRecordNumber As System.Web.UI.WebControls.TextBox
Protected WithEvents cmdMoveBack As System.Web.UI.WebControls.Button
Protected WithEvents cmdAdd As System.Web.UI.WebControls.Button
Protected WithEvents cmdDelete As System.Web.UI.WebControls.Button
Protected WithEvents cmdSave As System.Web.UI.WebControls.Button
Protected WithEvents cmdEdit As System.Web.UI.WebControls.Button
Protected WithEvents cmdSaveToDataset As System.Web.UI.WebControls.Button
Protected WithEvents lblMode As System.Web.UI.WebControls.Label
Protected WithEvents SqlConnection1 As System.Data.SqlClient.SqlConnection
Protected WithEvents DataView1 As System.Data.DataView
Protected WithEvents SqlDataAdapter1 As
System.Data.SqlClient.SqlDataAdapter
Protected WithEvents SqlSelectCommand1 As System.Data.SqlClient.SqlCommand
Protected WithEvents SqlInsertCommand1 As System.Data.SqlClient.SqlCommand
Protected WithEvents SqlUpdateCommand1 As System.Data.SqlClient.SqlCommand
Protected WithEvents SqlDeleteCommand1 As System.Data.SqlClient.SqlCommand
Protected WithEvents DataSet21 As _051003._2.DataSet2
Protected WithEvents grdAuthors As System.Web.UI.WebControls.DataGrid
#End Region
Public Property RecordNumber() As Long
Get
Return Me.txtRecordNumber.Text
End Get
Set(ByVal Value As Long)
Me.txtRecordNumber.Text = Value
End Set
End Property
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
Dim strSQL As String
If Me.IsPostBack Then
ptyDataSet = Me.Session.Item(0)
ptySQLDataAdapter = Me.Session.Item(1)
ptySQLDataConnection = Me.Session.Item(2)
'SqlDataAdapter1.Fill(ptyDataSet)
'grdAuthors.DataBind()
Else
Me.RecordNumber = 1
Try
'Creating the connection object and opening the connection
ptySQLDataConnection = New SqlClient.SqlConnection("data
source=MAGELLAN;initial catalog=SS;password=;persist security info=True;user
id=ssUser2;workstation id='HOPPER-EVO';packet size=4096;")
ptySQLDataConnection.Open()
strSQL = "SELECT * FROM tblProjects"
'Creating the Data Adapter that will fill the DataSet
ptySQLDataAdapter = New SqlClient.SqlDataAdapter(strSQL,
ptySQLDataConnection)
ptyDataSet = New DataSet
'Filling the DataSet
ptySQLDataAdapter.Fill(ptyDataSet)
'Setting the DataGrids DataSource Property
'If Not Me.IsPostBack Then
grdAuthors.DataSource = ptyDataSet
grdAuthors.DataBind()
'End If
grdAuthors.SelectedIndex = Me.RecordNumber - 1 'The index
property is 0 based so record number 1 is index 0
Me.MoveToRecord(Me.RecordNumber)
Me.Session.Item("Dataset") = ptyDataSet
Me.Session.Item("DataAdapter") = ptySQLDataAdapter
Me.Session.Item("DataConnection") = ptySQLDataConnection
SqlDataAdapter1.Fill(DataSet21)
grdAuthors.DataBind()
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try
End If
End Sub
Public Sub MoveToRecord(ByVal Number As Long)
'Moving to the record that is currently displayed in record textbox
With ptyDataSet.Tables(0).Rows.Item(Number - 1)
Me.txtID.Text = .Item("ID")
Me.txtFirstName.Text = .Item("Description")
Me.txtLastName.Text = .Item("Department")
Me.txtPhone.Text = .Item("CurrentStatus")
Me.txtAddress.Text = .Item("ProjectLead")
End With
End Sub
Public Sub ChangeRecord()
Dim objDataRow As DataRow
Select Case Session.Item("RecordType")
Case 0
objDataRow = ptyDataSet.Tables(0).NewRow
With objDataRow
.Item("ID") = txtID.Text
.Item("Department") = txtLastName.Text
.Item("Description") = txtFirstName.Text
.Item("CurrentStatus") = txtPhone.Text
.Item("ProjectLead") = txtAddress.Text
End With
ptyDataSet.Tables(0).Rows.Add(objDataRow)
Case 2
objDataRow = ptyDataSet.Tables(0).Rows.Item(Me.RecordNumber
- 1)
objDataRow.Delete()
Case 1
With ptyDataSet.Tables(0).Rows.Item(Me.RecordNumber - 1)
.Item("ID") = txtID.Text
.Item("Department") = txtLastName.Text
.Item("Description") = txtFirstName.Text
.Item("CurrentStatus") = txtPhone.Text
.Item("ProjectLead") = txtAddress.Text
End With
End Select
SqlDataAdapter1.Update(ptyDataSet)
grdAuthors.DataBind()
End Sub
Public Sub cmdMoveForward_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles cmdMoveForward.Click
'If Me.RecordNumber >= ptyDataSet.Tables(0).Rows.Count Then Exit Sub
If Me.RecordNumber >= ptyDataSet.Tables(0).Rows.Count Then Exit Sub
Me.RecordNumber += 1
With grdAuthors
.SelectedIndex = Me.RecordNumber - 1
End With
Me.MoveToRecord(Me.RecordNumber)
End Sub
Private Sub cmdMoveBack_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles cmdMoveBack.Click
If Me.RecordNumber <= 1 Then Exit Sub
Me.RecordNumber -= 1
With grdAuthors
.SelectedIndex = Me.RecordNumber - 1
End With
Me.MoveToRecord(Me.RecordNumber)
End Sub
Private Sub cmdSave_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles cmdSave.Click
Try
Dim objCB As SqlClient.SqlCommandBuilder = New
SqlClient.SqlCommandBuilder(ptySQLDataAdapter)
ptySQLDataAdapter.Update(ptyDataSet)
Catch ex As Exception
'MsgBox(ex.Message)
End Try
lblMode.Text = "Saved record to DB"
End Sub
Private Sub cmdAdd_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles cmdAdd.Click
Session.Item("RecordType") = 0
Me.txtID.Text = ""
Me.txtFirstName.Text = ""
Me.txtLastName.Text = ""
Me.txtPhone.Text = ""
Me.txtAddress.Text = ""
lblMode.Text = "Add Mode"
End Sub
Private Sub cmdEdit_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles cmdEdit.Click
Session.Item("RecordType") = 1
lblMode.Text = "Edit Mode"
End Sub
Private Sub cmdDelete_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles cmdDelete.Click
Session.Item("RecordType") = 2
lblMode.Text = "Deleted record from DB"
Me.ChangeRecord()
With Me.grdAuthors
.DataSource = ptyDataSet
.DataBind()
End With
End Sub
Private Sub cmdSaveToDataset_Click(ByVal sender As System.Object, ByVal
e As System.EventArgs) Handles cmdSaveToDataset.Click
Me.ChangeRecord()
'SqlDataAdapter1.Update(ptyDataSet)
'SqlDataAdapter1.Fill(ptyDataSet)
With Me.grdAuthors
SqlDataAdapter1.Fill(ptyDataSet)
'.DataSource = ptyDataSet
.DataBind()
End With
grdAuthors.SelectedIndex = Me.RecordNumber - 1 'The index property
is 0 based so record number 1 is index 0
Me.MoveToRecord(Me.RecordNumber)
Me.Session.Item("Dataset") = ptyDataSet
Me.Session.Item("DataAdapter") = ptySQLDataAdapter
Me.Session.Item("DataConnection") = ptySQLDataConnection
'SqlDataAdapter1.Fill(DataSet21)
'grdAuthors.DataBind()
lblMode.Text = "Saved record to DS"
'grdAuthors.DataBind()
End Sub
End Class