Option Strict Off
Imports System
Imports System.Web
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Data
Imports System.Data.OleDb
Imports Microsoft.VisualBasic
Imports System.Collections 

Namespace Generic
  Public Class GenEditAdd : Inherits Control : Implements INamingContainer
    Private ls_display as string
    Private ls_where as string
    Private ls_sql as string
    Private ls_ConnStr as string
    Private ls_keyField as string
    Private ls_keyValue as string
    Private ls_Insertprocedure as string
    Private ls_Updateprocedure as string
    Private ls_exitpage as string  
    Private lt_datatable as datatable
    Private ls_mode as string
    Private ls_RequiredFields as string
    Private ls_editable as string
    Private ls_DropDown as string
    Private ls_fieldNames as string
    Protected mytbl as table
    Protected DDLColumn(10) as string
    Protected CodeField(10) as string
    Protected DisplayField(10) as string
    Protected DDLSql(10) as string
    Protected FieldNamesArray(15) as string
    Public Property FieldNames as string 
      Get
        Return Cstr(ViewState("ls_FieldNames"))
      End Get
      Set
        ViewState("ls_FieldNames") = value
      End Set
    End Property
    Public Property InsertProcedure as string 
      Get
        Return Cstr(ViewState("ls_Insertprocedure"))
      End Get
      Set
        ViewState("ls_Insertprocedure") = value
      End Set
    End Property
    Public Property UpdateProcedure as string 
      Get
        Return Cstr(ViewState("ls_Insertprocedure"))
      End Get
      Set
        ViewState("ls_Insertprocedure") = value
      End Set
    End Property
    Public Property DropDown as string 
      Get
        Return Cstr(ViewState("ls_DropDown"))
      End Get
      Set
        ViewState("ls_DropDown") = value
      End Set
    End Property
    Public Property editable as string 
      Get
        Return Cstr(ViewState("ls_editable"))
      End Get
      Set
        ViewState("ls_editable") = value
      End Set
    End Property
    Public Property RequiredFields as string 
      Get
        Return Cstr(ViewState("ls_RequiredFields"))
      End Get
      Set
        ViewState("ls_RequiredFields") = value
      End Set
    End Property
    Public Property Mode as string
      Get
        Return Cstr(ViewState("ls_mode"))
      End Get
      Set
        ViewState("ls_mode") = value
      End Set
    End Property
    Public Property ExitPage as string
      Get
        Return Cstr(ViewState("ls_exitpage"))
      End Get
      Set
        ViewState("ls_exitpage") = value
      End Set
    End Property
    Public Property t as datatable 
      Get
        Return lt_datatable
      End Get
      Set
        lt_datatable = value
      End Set
    End Property
    Public Property KeyField as string 
      Get
        Return Cstr(ViewState("ls_keyfield"))
      End Get
      Set
        ViewState("ls_keyfield") = value
      End Set
    End Property
    Public Property KeyValue as string 
      Get
        Return Cstr(ViewState("ls_keyvalue"))
      End Get
      Set
        ViewState("ls_keyvalue") = value
      End Set
    End Property
    Public Property display as string 
      Get
        Return Cstr(ViewState("ls_display"))
      End Get
      Set
        ViewState("ls_display") = value
      End Set
    End Property
    Public Property Where as string 
      Get
        Return Cstr(ViewState("ls_where"))
      End Get
      Set
        ViewState("ls_where") = value
      End Set
    End Property
    Public Property SQL as string 
      Get
        Return Cstr(ViewState("ls_sql"))
      End Get
      Set
        ViewState("ls_sql") = value
      End Set
    End Property
    Public Property ConnStr as string 
      Get
        Return Cstr(ViewState("ls_ConnStr") )
      End Get
      Set
        ViewState("ls_ConnStr")  = value
     End Set
    End Property
    Protected Overrides Sub CreateChildControls()
      '-----Parse the DropDown property
      ParseDropDown
      '---Parse FieldNames
      ParseFieldNames
      '---------------Input Form-------------
      Dim dv As DataView
      Dim myConnection As OleDbConnection
      Dim myCommand As OleDbDataAdapter
      Dim ds As New DataSet
      Dim vSql As string
      If len(Where) < 1 then
        vSql = SQL
        mode = "insert"
      Else
        vSql = SQL + Where
        mode = "update"
      End If
      myConnection = New OleDbConnection(ConnStr)    
      myCommand = New OleDbDataAdapter(vSql, myConnection)
      myCommand.Fill(ds, "vtable")
      dv = new DataView(ds.Tables("vtable"))
      Dim Fields As Integer
      t = dv.Table
      Dim r As DataRow
      Dim c As DataColumn
      Dim cell As TableCell
      Dim row As DataRow
      Dim Fieldscount As integer 
      Dim s As string
      Dim vdisplay As string
      Dim vRequired As string
      Dim veditable As string
      Dim idx As integer
      Dim vDisplayField As string
      Dim vCodeField As string
      Dim vDDLSql As string
      Dim VFieldName As string
      vDisplay = Display + "000000000000000000000000000000000000000000"
      vRequired = RequiredFields+"000000000000000000000000000000000000000000"
      veditable = editable + "000000000000000000000000000000000000000000"
      FieldsCount = 0
      s = "<A HREF=" + ExitPage + ">Back</A>"
      me.Controls.Add(new LiteralControl(s))
      s= "<table bgcolor ='antiquewhite' style='font: 8pt verdana'>"
      me.Controls.Add(new LiteralControl(s))
      me.Controls.Add(new LiteralControl("<tr>"))
      If mode = "insert" then
        s="<td colspan='2' bgcolor='#aaaadd'"
        s= s + " style='font:10pt verdana'>Add a New Record:</td>" 
        me.Controls.Add(new LiteralControl(s))
      Else
        s="<td colspan='2' bgcolor='#aaaadd' "
        s= s + " style='font:10pt verdana'>Edit Record:</td> "
        me.Controls.Add(new LiteralControl(s))
      End If
      me.Controls.Add(new LiteralControl("</tr>"))
      '**************************************
          'UPDATE MODE
      '*************************************
      If mode = "update" then
        For Each r in t.Rows
          For Each c in t.Columns
            'Get the field name
            vFieldName = FieldNamesArray(FieldsCount)
            If len(vFieldName) < 1 then
              vFieldName = c.ToString
            End If
            'Don't show this field
            If vdisplay.chars(FieldsCount) = "0" or c.ToString = KeyField then
            Else
              me.Controls.Add(new LiteralControl("<tr>"))
              'label
              me.Controls.Add(new LiteralControl("<td>"))
              me.Controls.Add(new LiteralControl(vFieldName))
              me.Controls.Add(new LiteralControl("</td>"))
              'value
              '------Read only field  hence label 
              If veditable.chars(FieldsCount) = "0"
                me.Controls.Add(new LiteralControl("<td>"))
                me.Controls.Add(new LiteralControl(r(c).ToString))
                me.Controls.Add(new LiteralControl("</td>"))
                '--------Editable Fields--------
              Else
                '--------Drop Down List----------
                If isDropDown(c.ToString)  then
                  'get the index & other parameters
                  idx = GetDDLindex((c.ToString))
                  vDisplayField =DisplayField(idx)
                  vCodeField =CodeField(idx)
                  vDDLSql =DDLSql(idx)
                  me.Controls.Add(new LiteralControl("<td>"))
                  Dim DDL As New DropDownList
                  DDL.ID = c.ToString
                  DDL.DataTextField = vDisplayField
                  DDL.DataValueField = vCodeField
                  me.Controls.Add(DDL) 
                  '---Populate the drop down----
                  Dim mSql As String
                  msql = vDDLSQL
                  myConnection = New OleDbConnection(ConnStr)    
                  myCommand = New OleDbDataAdapter(mSql, myConnection)
                  myCommand.Fill(ds, c.ToString)
                  DDL.DataSource = ds.Tables(c.ToString).DefaultView
                  DDL.DataBind
                  'set the display field
                  Dim dv2 As dataview
                  dv2 = new DataView(ds.Tables(c.ToString))
                  If c.DataType.ToString = "System.String" Then
                    msql = vCodeField + " = '" + r(c).ToString +"'"
                  Else
                    msql = vCodeField + " = " + r(c).ToString  
                  End If
                  dv2.RowFilter = msql
                  DDL.Selecteditem.text = dv2(0)(vDisplayField).ToString
                  DDL.Selecteditem.value = dv2(0)(vCodeField).ToString
                  me.Controls.Add(new LiteralControl("</td>"))
                Else
                  '---------Text Boxes-------
                  me.Controls.Add(new LiteralControl("<td>"))
                  Dim Box As New TextBox
                  Box.Text = r(c).ToString
                  Box.ID = c.ToString
                  me.Controls.Add(box) 
                  me.Controls.Add(new LiteralControl("</td>"))
                End If
                '------Required field ------
                If vRequired.chars(FieldsCount) = "1"
                  Dim vReq As New RequiredFieldValidator
                  vReq.controltovalidate = c.ToString
                  vReq.errormessage= "Please enter " + c.ToString 
                  me.Controls.Add(vReq) 
                End If
              End If
            End If
            FieldsCount = FieldsCount + 1
          Next c
        Next r   
      '**************************************
         'INSERT MODE
      '*************************************
      Else
        For Each c in t.Columns
          'Get the field name
          vFieldName = FieldNamesArray(FieldsCount)
          If len(vFieldName) < 1 then
            vFieldName = c.ToString
          End If
          'Don't show this field
          If vdisplay.chars(FieldsCount) = "0" or c.ToString = KeyField _
          or veditable.chars(FieldsCount) = "0" then
          Else
            me.Controls.Add(new LiteralControl("<tr>"))
            'label
            me.Controls.Add(new LiteralControl("<td>"))
            me.Controls.Add(new LiteralControl(vFieldName))
            me.Controls.Add(new LiteralControl("</td>"))
            If isDropDown(c.ToString)  then
              'get the index & other parameters
              idx = GetDDLindex((c.ToString))
              vDisplayField =DisplayField(idx)
              vCodeField =CodeField(idx)
              vDDLSql =DDLSql(idx)
              me.Controls.Add(new LiteralControl("<td>"))
              Dim DDL As New DropDownList
              DDL.ID = c.ToString
              DDL.DataTextField = vDisplayField
              DDL.DataValueField = vCodeField
              me.Controls.Add(DDL) 
              '---Populate the drop down----
              Dim mSql As String
              msql = vDDLSQL
              myCommand = New OleDbDataAdapter(mSql, myConnection)
              myCommand.Fill(ds, c.ToString)
              DDL.DataSource = ds.Tables(c.ToString).DefaultView
              DDL.DataBind
              me.Controls.Add(new LiteralControl("</td>"))
            Else
              'value
              me.Controls.Add(new LiteralControl("<td>"))
              Dim Box As New TextBox
              Box.ID = c.ToString
              me.Controls.Add(box) 
              me.Controls.Add(new LiteralControl("</td>"))
            End If
            '------Required field ------
            If vRequired.chars(FieldsCount) = "1"
              Dim vReq As New RequiredFieldValidator
              vReq.controltovalidate = c.ToString
              vReq.errormessage= "Please enter " + c.ToString 
              me.Controls.Add(vReq) 
            End If
          End If
          FieldsCount = FieldsCount + 1
        Next c
      End If
      me.Controls.Add(new LiteralControl("</tr>"))
      me.Controls.Add(new LiteralControl("</Table>"))
      '--------add button
      Dim AddButton As New Button
      If mode = "insert" then
        AddButton.Text = "Add"
      Else
        AddButton.Text = "Update"
      End If
      AddHandler AddButton.Click, AddressOf AddBtn_Click
      Me.Controls.Add(AddButton)
      '------------Validation Summary
      me.Controls.Add(new LiteralControl("<br><br>"))
      Dim vSummary As New ValidationSummary
      vSummary.headertext="There were errors on the page:"
      me.Controls.Add(vSummary) 
    End Sub
    Private Sub AddBtn_Click(Sender As Object, E As EventArgs)
      'Build the procedure call
      Dim s As String 
      Dim r As DataRow
      Dim c As DataColumn
      Dim cell As TableCell
      Dim row As DataRow
      Dim column As string
      Dim Value As string
      Dim Fieldscount As integer 
      Dim vdisplay As string
      Dim veditable As string
      veditable = editable + "000000000000000000000000000000000000000000"
      vDisplay = Display + "000000000000000000000000000000000000000"
      FieldsCount = 0
      If mode = "update" then
        s = "Execute " + Updateprocedure + "" 
        For Each r in t.Rows
          For Each c in t.Columns
            If vdisplay.chars(FieldsCount) = "0" or c.ToString = KeyField _
            or veditable.chars(FieldsCount) = "0" then
            Else
              If isDropDown(c.ToString)  then    
                Dim vdropdown As DropDownList
                vdropdown = me.FindControl(c.ToString)
                column = c.ToString
                value = vdropdown.SelectedItem.value
              Else
                Dim tb As TextBox
                tb = me.FindControl(c.ToString)
                column = c.ToString
                Value = tb.text
              End If
              If Value = "" then 
                Value = "NULL"
              End If
              If c.DataType.ToString = "System.String" Then
                If Value = "NULL" then
                  s = s + " @" + column + "=" + value + ", "
                Else
                  s = s + " @" + column + "='" + value + "', " 
                End If
              Else 
                s = s + " @" + column + "=" + value + ", " 
              End If
            End If
            FieldsCount = FieldsCount + 1
          Next c
        Next r   
        s = s + "@" + KeyField + "=" + KeyValue
        me.Controls.Add(new LiteralControl(s))
        RunSql(s)
      Else
        s = "Execute " + Insertprocedure + "" 
        'Insert mode
        For Each c in t.Columns
          If vdisplay.chars(FieldsCount) = "0" or c.ToString = KeyField _
          or veditable.chars(FieldsCount) = "0"then
          Else
         If isDropDown(c.ToString)  then    
              Dim vdropdown As DropDownList
              vdropdown = me.FindControl(c.ToString)
              column = c.ToString
              value = vdropdown.SelectedItem.value
            Else
              Dim tb As TextBox
              tb = me.FindControl(c.ToString)
              column = c.ToString
              Value = tb.text
            End If
            If Value = "" then 
              Value = "NULL"
            End If
            If c.DataType.ToString = "System.String" Then
              If Value = "NULL" then
                s = s + " @" + column + "=" + value + ", " 
              Else
                s = s + " @" + column + "='" + value + "', " 
              End If
            Else 
              s = s + " @" + column + "=" + value + ", " 
            End If
          End If
          FieldsCount = FieldsCount + 1
        Next c
        s = s + "@" + KeyField + "=NULL" 
        me.Controls.Add(new LiteralControl(s))
        RunSql(s)
      End If
    End Sub
    Sub RunSql(vSql as string)
      try
        Dim s As string
        Dim myConnection As OleDbConnection
        myConnection = New OleDbConnection(ConnStr) 
        Dim mycommand As New OleDbCommand(vsql,myConnection)
        myconnection.Open()
        myCommand.ExecuteNonQuery()
        myconnection.Close()
        Catch ex As OleDbException
          ' SQL error
          Dim errItem As OleDbError
          Dim errString As String
          Dim s As string
          For Each errItem In ex.Errors
            errString += ex.Message + "<br/>"
          Next
          s = "<br/><br/>SQL Error.Details follow:<br/>" & errString
          me.Controls.Add(new LiteralControl(s))
        Catch myException as Exception
          me.Controls.Add(new LiteralControl("Exception: " + myException.ToString()))
      End try
    End Sub
    '-----------These Functions associated with DropDown Property--------------
    Function ParseDropDown()
      Dim strChar As Object
      Dim s As String
      Dim j As Integer
      Dim count As Integer
      Dim idx As integer
      count = 1
      idx = 0
      strChar = Split(DropDown, ";")
      For j = 0 To UBound(strChar)
        If Len(strChar(j)) = 0 Then
        Else
          s = CStr(strChar(j))
          If count = 1 then
            DDLColumn(idx) = s
          Elseif count = 2 then
            CodeField(idx) = s
          Elseif count = 3 then
            DisplayField(idx) = s
          Elseif count = 4 then
            DDLSql(idx) = s
            count = 0
            idx = idx + 1
          End If
          count = count + 1 
        End If
        Next
    End Function
    Function GetDDLIndex(vDDLColumn as string) as integer
      'Pass in the column where a drop-down list must appear
      ' this function returns the row where its details are
      ' stored in the array
      Dim i As integer, vKey As integer
      For i= 0 to UBound(DDlColumn)
        If DDlColumn(i) = vDDLColumn
          vKey = i
        End if
      Next
      Return vKey
    End Function
    Function IsDropDown(vDDLColumn as string) as boolean
      Dim i As integer, vflag As integer
      vFlag = -1
      For i= 0 to UBound(DDlColumn)
        If DDlColumn(i) = vDDLColumn
          vflag = 1
        End if
      Next
      If vFlag = 1 then
        Return True
      Else 
        Return False
      End If
    End Function
    '-----------------Drop Down Functions end---------
    Function ParseFieldNames()
      'This function parses the FieldNames property
      Dim strChar As Object
      Dim s As String
      Dim j As Integer
      Dim count As Integer
      If len(FieldNames) <1 then
        Exit function
      End If
      strChar = Split(FieldNames, ";")
      For j = 0 To UBound(strChar)
        If Len(strChar(j)) = 0 Then
        Else
          s = CStr(strChar(j))
          FieldNamesArray(j) = s
        End If
      Next
    End Function
  End Class
End Namespace
