Hi Ken and thanks for the reply! (My Code below!!!)
What i'm trying to do is a datagrid that has Add,Update,Delete commands.My
Add command is working but my Delete and Update command is giving error :-
System.NullReferenceException: Object reference not set to an instance of an
object.
Refering to the line code:- ItemNumber =
CType(Args.Item.FindControl("ItemNumber"), TextBox).Text
Don't know whats wrong IT WORKS WITH ASP.NETWEBMATRIX!!
But VS.Net give such errors!
Any help Thanks!!
'Option Explicit On
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.UI.WebControls
Public Class datagrid_edit_delete
Inherits System.Web.UI.Page
'Dim ItemTypes As New ArrayList
Protected ItemTypes As New ArrayList
'Protected WithEvents ItemType As System.Web.UI.WebControls.DropDownList
Protected WithEvents Button As System.Web.UI.WebControls.Button
Protected WithEvents PageButtons As System.Web.UI.WebControls.PlaceHolder
Protected WithEvents UpdateMessage As System.Web.UI.WebControls.Label
Protected WithEvents DataGridDisplay As System.Web.UI.WebControls.DataGrid
Protected WithEvents TextBox1 As System.Web.UI.WebControls.TextBox
'Dim ItemNumberList As New ArrayList
'Protected WithEvents ItemTypes As System.Web.UI.WebControls.DropDownList
#Region " Web Form Designer Generated Code "
'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private Sub
InitializeComponent()
End Sub
'NOTE: The following placeholder declaration is required by the Web Form
Designer.
'Do not delete or move it.
Private designerPlaceholderDeclaration As System.Object
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
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 DBConnection As SqlConnection
Dim DBCommand As SqlCommand
Dim DBReader As SqlDataReader
Dim SQLString As String
Dim PageSize As Integer = 6
'-- Load array ItemNumberList with item numbers
'Dim ItemNumberList = New ArrayList
Dim ItemNumberList = New ArrayList
DBConnection = New
SqlConnection("server=(local);database=Northwind;integrated security=true;")
DBConnection.Open()
SQLString = "SELECT ItemNumber FROM products1 ORDER BY ItemNumber"
DBCommand = New SqlCommand(SQLString, DBConnection)
DBReader = DBCommand.ExecuteReader()
While DBReader.Read()
ItemNumberList.Add(DBReader("ItemNumber"))
End While
DBReader.Close()
DBConnection.Close()
ItemNumberList.TrimToSize()
'-- Create Paging Buttons
Dim StartIndex = 0
Dim EndIndex As Integer
Dim StartKey As String
Dim EndKey As String
Dim i As Integer
PageButtons.Controls.Clear()
For i = 1 To Math.Ceiling(ItemNumberList.Count / PageSize)
EndIndex = StartIndex + PageSize - 1
If EndIndex > ItemNumberList.Count - 1 Then
EndIndex = ItemNumberList.Count - 1
End If
StartKey = ItemNumberList(StartIndex)
EndKey = ItemNumberList(EndIndex)
Dim PageButton As Button
PageButton = New Button
PageButton.Text = i
PageButton.ID = "P" & i
PageButton.CommandName = StartKey & "|" & EndKey
PageButton.Style("width") = "20px"
PageButton.Style("background-color") = "#F0F0F0"
AddHandler PageButton.Command, AddressOf GetPage
PageButtons.Controls.Add(PageButton)
StartIndex += PageSize
Next
'== CREATE ARRAYLIST FOR BINDING ITEM TYPES
ItemTypes = New ArrayList
DBConnection = New
SqlConnection("server=(local);database=Northwind;integrated security=true;")
DBConnection.Open()
SQLString = "SELECT DISTINCT ItemType FROM products1 ORDER BY
ItemType"
DBCommand = New SqlCommand(SQLString, DBConnection)
DBReader = DBCommand.ExecuteReader()
While DBReader.Read()
ItemTypes.Add(DBReader("ItemType"))
End While
DBReader.Close()
DBConnection.Close()
If Not Page.IsPostBack Or ViewState("Updated") = True Then
DBConnection = New
SqlConnection("server=(local);database=Northwind;integrated security=true;")
DBConnection.Open()
SQLString = "SELECT TOP " & PageSize & " * FROM products1 ORDER
BY ItemNumber"
DBCommand = New SqlCommand(SQLString, DBConnection)
DBReader = DBCommand.ExecuteReader()
DataGridDisplay.DataSource = DBReader
DataGridDisplay.DataBind()
DBReader.Close()
DBConnection.Close()
Dim FirstButton As Button
FirstButton = CType(PageButtons.FindControl("P1"), Button)
FirstButton.Style("background-color") = "#990000"
FirstButton.Style("color") = "#FFFFFF"
ViewState("Updated") = False
End If
End Sub
Sub GetPage(ByVal Src As Object, ByVal Args As CommandEventArgs)
Dim DBConnection As SqlConnection
Dim DBCommand As SqlCommand
Dim DBReader As SqlDataReader
Dim SQLString As String
Dim Keys() As String
Keys = Split(Args.CommandName, "|")
DBConnection = New
SqlConnection("server=(local);database=Northwind;integrated security=true;")
DBConnection.Open()
SQLString = "SELECT * FROM products1 WHERE " & _
"ItemNumber >= '" & Keys(0) & "' AND " & _
"ItemNumber <= '" & Keys(1) & "' " & _
"ORDER BY ItemNumber"
DBCommand = New SqlCommand(SQLString, DBConnection)
DBReader = DBCommand.ExecuteReader()
DataGridDisplay.DataSource = DBReader
DataGridDisplay.DataBind()
DBReader.Close()
DBConnection.Close()
'-- Highlight clicked button
Dim Item As Button
Dim ThisButton As Button
For Each Item In PageButtons.Controls
ThisButton = CType(Item, Button)
ThisButton.Style("background-color") = "#F0F0F0"
ThisButton.Style("color") = "#000000"
Next
ThisButton = CType(PageButtons.FindControl(Src.id), Button)
ThisButton.Style("background-color") = "#990000"
ThisButton.Style("color") = "#FFFFFF"
End Sub
Function SetIndex(ByVal TheItem As String)
Dim i As Integer
For i = 0 To ItemTypes.Count - 1
If TheItem = ItemTypes(i) Then
Return i
End If
Next
End Function
Sub EditRecord(ByVal Src As Object, ByVal Args As
DataGridCommandEventArgs)
Dim Command As String
Dim ItemNumber As String
Dim ItemType As String
Dim ItemSupplier As String
Dim ItemName As String
Dim ItemDescription As String
Dim ItemPrice As String
Dim ItemQuantity As String
Dim DBConnection As SqlConnection
Dim DBCommand As SqlCommand
Dim DBReader As SqlDataReader
Dim SQLString As String
Command = Args.CommandSource.Text
ItemNumber = CType(Args.Item.FindControl("ItemNumber"), TextBox).Text
ItemType = CType(Args.Item.FindControl("ItemType"),
DropDownList).SelectedItem.Text
ItemSupplier = CType(Args.Item.FindControl("ItemSupplier"),
TextBox).Text
ItemName = CType(Args.Item.FindControl("ItemName"), TextBox).Text
ItemDescription = CType(Args.Item.FindControl("ItemDescription"),
TextBox).Text
ItemPrice = CType(Args.Item.FindControl("ItemPrice"), TextBox).Text
ItemQuantity = CType(Args.Item.FindControl("ItemQuantity"),
TextBox).Text
UpdateMessage.Text = " "
Dim ValidRecord As Boolean = True
If Command = "Add" Or Command = "Update" Then
'-- CHECK FOR VALID RECORD ---
'-- Check for valid ItemNumber
If Len(ItemNumber) <> 6 Then
UpdateMessage.Text &= "- Invalid Item Number length "
ValidRecord = False
ElseIf Not IsNumeric(Right(ItemNumber, 4)) Then
UpdateMessage.Text &= "- Invalid Item Number format "
ValidRecord = False
Else
ItemNumber = UCase(ItemNumber)
End If
'-- Check for missing Item Supplier
If ItemSupplier = "" Then
UpdateMessage.Text &= "- Missing Supplier "
ValidRecord = False
End If
'-- Check for missing Item Name
If ItemName = "" Then
UpdateMessage.Text &= "- Missing Name "
ValidRecord = False
End If
'-- Check for missing Item Description
If ItemDescription = "" Then
UpdateMessage.Text &= "- Missing Description "
ValidRecord = False
End If
'-- Check for valid Item Price
If Not IsNumeric(ItemPrice) Then
UpdateMessage.Text &= "- Invalid Price format "
ValidRecord = False
End If
'-- Check for valid Item Quantity
If Not IsNumeric(ItemQuantity) Then
UpdateMessage.Text &= "- Invalid Quantity format "
ValidRecord = False
End If
If ValidRecord = True And Command = "Add" Then
'--- CHECK FOR DUPLICATE RECORD ---
DBConnection = New
SqlConnection("server=(local);database=Northwind;integrated security=true;")
DBConnection.Open()
SQLString = "SELECT Count(*) FROM products1 WHERE ItemNumber
= '" & ItemNumber & "'"
DBCommand = New SqlCommand(SQLString, DBConnection)
If DBCommand.ExecuteScalar() <> 0 Then
UpdateMessage.Text = "- Duplicate Item Number. Record
not added."
ValidRecord = False
End If
DBConnection.Close()
End If
End If
If ValidRecord = True Then
Select Case Command
Case "Add"
SQLString = "INSERT INTO products1 " & _
"(ItemNumber, ItemType, ItemSupplier, ItemName, " & _
"ItemDescription, ItemPrice, ItemQuantity) VALUES(" & _
"'" & ItemNumber & "', " & _
"'" & ItemType & "', " & _
"'" & Replace(ItemSupplier, "'", "''") & "', " & _
"'" & Replace(ItemName, "'", "''") & "', " & _
"'" & Replace(ItemDescription, "'", "''") & "', " & _
ItemPrice & ", " & _
ItemQuantity & ")"
UpdateMessage.Text = "- Record " & ItemNumber & " added"
Case "Update"
SQLString = "UPDATE products1 SET " & _
"ItemType = '" & ItemType & "', " & _
"ItemSupplier = '" & Replace(ItemSupplier, "'", "''")
& "', " & _
"ItemName = '" & Replace(ItemName, "'", "''") & "', "
& _
"ItemDescription = '" & Replace(ItemDescription, "'",
"''") & "', " & _
"ItemPrice = " & ItemPrice & ", " & _
"ItemQuantity = " & ItemQuantity & " " & _
"WHERE ItemNumber = '" & ItemNumber & "'"
UpdateMessage.Text = "- Record " & ItemNumber & " updated"
Case "Delete"
SQLString = "DELETE FROM products1 WHERE ItemNumber = '"
& ItemNumber & "'"
UpdateMessage.Text = "- Record " & ItemNumber & " deleted"
End Select
DBConnection = New
SqlConnection("server=(local);database=Northwind;integrated security=true;")
DBConnection.Open()
DBCommand = New SqlCommand(SQLString, DBConnection)
DBCommand.ExecuteNonQuery()
DBConnection.Close()
If Command = "Add" Or Command = "Delete" Then
ViewState("Updated") = True
Page_Load(Me, EventArgs.Empty)
'Page_Load(ByRef sender As System.Object, ByRef e As
System.EventArgs)
End If
End If
End Sub
End Class
