Option Strict Off
Imports System
Imports System.Web
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Data
Imports System.Data.OleDb

Namespace Generic_Chap7
Public Class GenEditAdd_Chap7 : 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_procedure as string
Private ls_exitpage as string  
Private lt_datatable as datatable
Private ls_mode as string
Protected mytbl as table
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 Procedure as string 
	Get
		Return Cstr(ViewState("ls_procedure"))
	End Get
	Set
		ViewState("ls_procedure") = 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()
		Dim dv as DataView
		Dim myConnection As OleDbConnection
		Dim myCommand    As OleDbDataAdapter
		Dim ds           As New DataSet
		Dim vSql as string
		
		If Where.Length < 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 为 DataTable
		   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
		   vDisplay = Display + "000000000000000000000000000000000000000000"
		   FieldsCount = 0
		   s = "<A HREF=" + ExitPage + ">Back</A>"
		   me.Controls.Add(new LiteralControl(s))
		   me.Controls.Add(new LiteralControl("<table bgcolor ='antiquewhite' style='font: 8pt verdana'>"))
		   me.Controls.Add(new LiteralControl("<tr>"))
		   If mode = "insert" then
		   	me.Controls.Add(new LiteralControl("<td colspan='2' bgcolor='#aaaadd' style='font:10pt verdana'>Add a New Record:</td>"))
		   Else
			me.Controls.Add(new LiteralControl("<td colspan='2' bgcolor='#aaaadd' style='font:10pt verdana'>Edit Record:</td>"))
		   End if
		   me.Controls.Add(new LiteralControl("</tr>"))
		   If mode = "update" then
		      For Each r in t.Rows
			      For Each c in t.Columns
			      '此区域不显示
			      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("</td>"))	
				     'value
				     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	
         	     	 FieldsCount = FieldsCount + 1
         	     	Next c
		     Next r   
		  Else  ' 插入模式
				For Each c in t.Columns
				  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("</td>"))	
				     '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
				 FieldsCount = FieldsCount + 1
				Next c
		  End If
		 me.Controls.Add(new LiteralControl("</tr>"))
		   me.Controls.Add(new LiteralControl("</Table>"))
		'--------添加模式
	   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)
        End Sub
	Private Sub AddBtn_Click(Sender As Object, E As EventArgs)
	   
	   '创建存储过程调用
	   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
	   vDisplay = Display + "000000000000000000000000000000000000000"
	   FieldsCount = 0
	   s = "Execute " + procedure + ""
	  If mode = "update" then
		  For Each r in t.Rows
		      For Each c in t.Columns
			IF  vdisplay.chars(FieldsCount) = "0" or c.ToString = KeyField then
			Else
				Dim tb As TextBox
				tb = me.FindControl(c.ToString)
				column = c.ToString
				Value = tb.text
				If c.DataType.ToString = "System.String" Then
					s = s + " @" + column + "='" + value + "', " 
				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
	 	For Each c in t.Columns
			IF  vdisplay.chars(FieldsCount) = "0" or c.ToString = KeyField then
			Else
				Dim tb As TextBox
				tb = me.FindControl(c.ToString)
				column = c.ToString
				Value = tb.text
				If c.DataType.ToString = "System.String" Then
					s = s + " @" + column + "='" + value + "', " 
				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 错误
		     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 错误细节如下:<br/>" & errString
		     me.Controls.Add(new LiteralControl(s))
	 catch myException as Exception
		  me.Controls.Add(new LiteralControl("出现异常: " + myException.ToString()))
	end try
End sub
    End Class
End Namespace
