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(c.ToString))
			      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 c.ToString = "code_category" then
				     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(c.ToString))
			      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)
	Dim cancel As New Button
	cancel.Text = "Cancel"
	Me.Controls.Add(cancel)
	'------------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
			'Dim tb As TextBox
			'tb = me.FindControl(c.ToString)
			'column = c.ToString
			'Value = tb.text
			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
