<% @ Page Language="VB" Debug="true" %>
<% @ Import Namespace="System.Data" %>
<% @ Import Namespace="System.Data.OleDb" %>
<html>
<head>
<title>第二十四例 数据库的简单修改操作</title>
</head>

<Script Language="VB" Runat="Server">

	Dim public myConnstring as string	'联结字符串为公有变量
	Dim MyConnection as OleDbConnection	
	Dim public Shared convar as integer = 0 '控制进行插入操作或修改操作的选择（1为插入，2为修改）

	public Sub Page_Load(sender As Object,e As EventArgs)
		Div2.visible = false
		If convar = 0 then
			Div1.visible = false
		End If
	End Sub	'在初始化页面时，Div1，Div2均为不可见
	
	Function Myconn()
		myConnstring ="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=f:/Asp_net/书稿/" + Textbox1.text
		MyConnection = new OleDbConnection(myConnstring)
	End Function '定义设置联结的函数

	public Sub TextChange1(sender As Object,e As EventArgs)
		Myconn()
		ConData("Select * from Student Order by ID",0)
	End Sub	'当输入文本框内容改变时，查询指定数据库的数据

	Function ConData(constr as String,Show as integer) as boolean
		try
			Dim MyCommand as OleDbCommand = new OleDbCommand(constr,MyConnection)
			MyConnection.Open()
			Dim MyDataReader as OleDbDataReader = MyCommand.ExecuteReader()
			If Show=0 then
				DataGrid1.DataSource=myDataReader
				DataGrid1.DataBind()
			End If
			myDataReader.Close()
			MyConnection.Close()
			Info.Text="访问数据库成功"
			Textbox1.Visible=false
			Div1.visible = true
			Return true
		catch ee As Exception
			MyConnection.Close()
			Info.Text="发生错误:"+ee.Message +" 或者，请确认您输入的数据库名称无误："
			Textbox1.Visible=true
			return false
		finally
		End try
	End Function	'访问数据库的函数，当访问成功返回true，否则返回false

	public Sub btn_click1(sender As Object,e As EventArgs)
		Myconn()
		ConData("delete * from Student where ID=" + input1.value,1)
		ConData("Select * from Student Order by ID",0)
	End Sub	'删除操作

	public Sub btn_click2(sender As Object,e As EventArgs)
		Convar = 2
		Myconn()
		Dim Upstr as String = "Select * from student where ID = " + Input2.value
		if ConData(Upstr,1) then
		 	Myconn()
			Dim MyCommand as OleDbCommand = new OleDbCommand(Upstr,MyConnection)
			MyConnection.Open()
			Dim MyDataReader as OleDbDataReader = MyCommand.ExecuteReader()
			 myDataReader.Read()
			 Input3.value = myDataReader("学号").ToString
			 Input4.value = myDataReader("姓名")
			 Input5.value = myDataReader("性别")
			 Input6.value = myDataReader("成绩").ToString
			 myDataReader.Close()
			 MyConnection.Close()
			 info.text="修改第" + input2.value + "条记录的操作！"
			 Div2.visible = true
			 Div1.visible = false
		Else 
			Div2.visible = false
			Div1.visible = true
		End if
	End Sub	'修改操作

	public Sub btn_click3(sender As Object,e As EventArgs)
		Convar = 1
		Myconn()
		info.text="插入操作！"
		Div2.visible = true
		Div1.visible = false
	End Sub '插入操作

	public Sub btn_click4(sender As Object,e As EventArgs)
		Myconn()
		info.text=convar.toString
		if Convar = 2 then
			Dim Upstr as String = "UPDATE Student SET 学号=" + Input3.value + ", 姓名='" + Input4.value + "', 性别=  '" + Input5.value + "', 成绩= " + Input6.value + " where ID=" + Input2.value
			if ConData(Upstr,1) then
			ConData("Select * from Student Order by ID",0)
			info.text="修改成功！"
			Else
			Div2.visible=true
			End If
		else if Convar = 1 then
			Dim Insstr as String = "Insert Into Student(学号,姓名,性别,成绩) values (" + Input3.value + ", '"+Input4.value+"' , '" + Input5.value + "', " + Input6.value + ")"
			if ConData(Insstr,1) then
			ConData("Select * from Student Order by ID",0)
			info.text="插入成功！"
			Else
			Div2.visible=true
			End If
		else
			Div2.visible=true
		End if
	End Sub	'输入数据的提交按钮

	public Sub btn_click5(sender As Object,e As EventArgs)
		Div1.visible = true
		Div2.visible = false
	End Sub	'取消提交

</Script>

<body>
	<h3><font face="Verdana">第二十四例 数据库的简单修改操作</font></h3>
	<form runat="server">
	<asp:Label id="Info" runat=server text="请输入数据库文件名称："/>
	<asp:TextBox id="TextBox1" AutoPostBack=true OnTextChanged=TextChange1 runat=server /><br>
	<ASP:DataGrid id="DataGrid1" runat="server" BorderColor="black" BorderWidth="1" GridLines="Both" CellPadding="3" CellSpacing="0" Font-Name="Verdana" Font-Size="8pt" HeaderStyle-BackColor="#aaaadd" AlternatingItemStyle-BackColor="#eeeeee"
/><br>
	<Div id=Div1 runat=server visible=false><asp:Label id="label1" runat="server" Text="请输入您要删除的ID号：" />
	<Input id=Input1 Size=3 runat=server OnServerChange="Btn_Click1"/>
	<asp:button id="Btn1" runat=server OnClick=Btn_Click1 text="确定" /><br>请输入您要修改的ID号：
	<Input id=Input2 Size=3 runat=server OnServerChange="Btn_Click2"/>
	<asp:button id="Btn2" runat=server OnClick=Btn_Click2 text="确定" /><br>如果你要插入新记录请点这里-->
	<asp:button id="Btn3" runat=server OnClick=Btn_Click3 text="插入" /></div><br>
	<DIV style="BACKGROUND-COLOR: aqua" id="Div2" runat=server>请输入您的修改值<br>学号：
	<Input id=Input3 Size=5 runat=server/>姓名：
	<Input id=Input4 Size=5 runat=server/>性别：
	<Input id=Input5 Size=2 runat=server/>成绩：
	<Input id=Input6 Size=3 runat=server/>
	<asp:Button id="Btn4" runat=server OnClick="Btn_Click4" text="提交"/>
	<asp:Button runat=server OnClick="Btn_Click5" text="取消"/>
	</Div>
	</form>
</body>