本文目录
请朋友帮忙：VB2008操作Access数据库完成连接、添加、修改、删除、读某个列值至变量等完整的代码示例谢
VB access,添加/删除/修改记录代码 给你一个示例,这个是用户登录系统 ’准备工作: 数据库 D:\Mydb.mdb 数据库中,建数据表:UserInfo,数据表中,建字段:User,Password,并添加一条记录 ’点工程-》引用 选中 Microsoft Activex Data Objects 2.1 Library ’控件:添加一个文本框,一个命令按钮Private Sub Command1_Click() Dim s As String Dim Conn As New ADODB.Connection Dim Rs As New ADODB.Recordset Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\mydb.mdb" ’请将数据库名及路径换成你的实际数据库名及路径 Dim UserName As String Dim Password As Strings = Text1.Text If s = "1" Or s = "2" Then UserName = InputBox("请输入用户名") Password = InputBox("请输入用户密码") sql = "Select * From where User=’" & UserName & "’" ’UserInfo请换成你的实际数据表名Rs.Open sql, Conn, 1, 3 If Rs.EOF Then MsgBox "没有找到此用户" Else If Rs("Password") = Password Then If s = "1" Then MsgBox "123" ElseIf s = "2" Then MsgBox "456" End If Else MsgBox "密码错误" End If End If End If End Sub 上面的例子实现了查询 如果是要添加,删除,更新等操作,只要编写相应的SQL语句,再用 Conn.Execute SQL 就可以了,如: 添加的: SQL="Insert Into (User,Password) Values(’aaaa’,’1234’)" Conn.Execute SQL ’执行后,就添加了一条记录 删除: SQL="Delete From Where User=’aaaa’" Conn.Execute SQL ’删除用户名为aaaa的用户记录 更新: SQL="Update Set Password=’abcdefg’ Where User=’aaaa’" Conn.Execute SQL ’执行后,修改用户aaaa的密码为abcdefg 以上只是示例,在实际使用过程中,可以将一些内容用控件输入等方式进行,这样就有很大的灵活性了动态创建数据库引用 microsoft DAP 3.6 Object Library Dim myDB As DAO.Database Set myDB = CreateDatabase(App.Path + "\111.mdb", dbLangGeneral) ’如果不存在数据库 Set myDB = OpenDatabase(App.Path + "\111.mdb") ’如果存在数据库 Dim str_SQL As String str_SQL = "Create Table NewTable1(Field1 Text(10),Field2 Short)" myDB.Execute str_SQL str_SQL = "Create Table NewTable2(Field1 Text(10),Field2 Short)" myDB.Execute str_SQL myDB.Close向已经建好的数据库中添加记录 建立数据库a，表名字b,字段c，文本格式,在窗体上画一command ,画一个文本框，这个代码就是将文本内容写入数据库要先引用microsoft activeX data object 2.5 library 代码如下：Private Sub Command1_Click() Dim cnDk As New ADODB.Connection Dim strDk As String Dim rDk As New ADODB.Recordset Dim sql As StringstrDk = "DBQ=" & App.Path & "\a.mdb;Driver={Microsoft Access Driver (*.mdb)};" cnDk.Open strDk sql = "insert into b (c) values (’" & text1.text& "’)" cnDk.Execute sql cnDk.Close Set cnDk = Nothing End Sub在数据库中添加多条记录只要可以进行输入文本的都可以 改一下这些吧，你单击一次就提示输入一次，输完了自动保存 改后 Data1.Recordset.AddNew Data1.Recordset.Fields("username") = trim(inputbox("请输入名字")) Data1.Recordset("password") =val(trim(inputbox("请输入电话"))) Data1.UpdateRecord —————————————————————————————— Data1.Recordset.AddNew Data1.Recordset.Fields("username") = "张三|" Data1.Recordset("password") = "fsdfsd" Data1.UpdateRecord Data1.Recordset.AddNew Data1.Recordset.Fields("username") = "李四|" Data1.Recordset("password") = "fsdf432d" Data1.UpdateRecord Data1.Recordset.AddNew Data1.Recordset.Fields("username") = "王五|" Data1.Recordset("password") = "f42342d" Data1.UpdateRecord MsgBox "adad"======================================================================================== vb+access 数据库的记录的添加与删除 Dim cn As New ADODB.Connection Dim rs As New ADODB.RecordsetPrivate Sub Command2_Click() Set rs = cn.Execute("select * from wenzhang where 内容=’" & Text1.Text & "’") If Text1.Text = "" Then MsgBox ("文章不能为空！") ElseIf rs.EOF = False Then MsgBox ("文章不能重复！") Else cn.Execute ("insert into wenzhang values(’" & Combo1.Text & "’,’" & Text3.Text & "’,’" & Text4.Text & "’,’" &Text1.Text & "’)") MsgBox ("ok") End If End Sub ’添加按钮 Private Sub Command3_Click() Dim myval As String Set rs = cn.Execute("select * from wenzhang where 内容=’" & Text1.Text & "’") If rs.EOF Then myval = MsgBox("是否保存文章？", vbInformation + vbYesNoCancel, "提示") If myval = vbYes Then cn.Execute ("insert into wenzhang values(’" & Combo1.Text & "’,’" & Text3.Text & "’,’" & Text4.Text & "’,’" &Text1.Text & "’)") MsgBox ("保存成功") Else Combo1.Text = "" End If Else Text1.Text = "" Combo1.Text = "" End If End Sub ’删除按钮 Private Sub Command4_Click() If Text1.Text = "" Then MsgBox ("不能删除空记录！") Else Dim myval As String myval = MsgBox("是否删除文章？", vbInformation + vbYesNo, "提示") If myval = vbYes Then Set rs = cn.Execute("delete from wenzhang where 内容 =’" & Text1.Text & "’") MsgBox ("删除成功！") End If End If End Sub ’连接数据库 Private Sub Form_Load() cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\vb.net\temp.mdb;Persist Security Info=False" cn.CursorLocation = adUseClient cn.Open End Sub
vb.net数据库操作
参考一下下面这段代码就可以了。Imports System.Data’引入数据库操作类命名空间Imports System.Data.OleDb’引入ADO.NET操作命名空间Public Class FrmModifystInfo Inherits System.Windows.Forms.Form Public ADOcmd As OleDbDataAdapter Public ds As DataSet = New DataSet() ’建立DataSet对象 Public mytable As Data.DataTable ’建立表单对象 Public myrow As Data.DataRow ’建立数据行对象 Public rownumber As Integer ’定义一个整型变量来存放当前行数 Public SearchSQL As String Public cmd As OleDbCommandBuilder’======================================================#Region " Windows 窗体设计器生成的代码 "#End Region’====================================================== Private Sub FrmModifystInfo_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load ’窗体的载入 TxtSID.Enabled = False TxtName.Enabled = False ComboSex.Enabled = False TxtBornDate.Enabled = False TxtClassno.Enabled = False TxtRuDate.Enabled = False TxtTel.Enabled = False TxtAddress.Enabled = False TxtComment.Enabled = False ’设置信息为只读 Dim tablename As String = "student_Info " SearchSQL = "select * from student_Info " ExecuteSQL(SearchSQL, tablename) ’打开数据库 ShowData() ’显示记录 End SubPrivate Sub ShowData() ’在窗口中的textbox中显示数据 myrow = mytable.Rows.Item(rownumber) TxtSID.Text = myrow.Item(0).ToString TxtName.Text = myrow.Item(1).ToString ComboSex.Text = myrow.Item(2).ToString TxtBornDate.Text = Format(myrow.Item(3), "yyyy-MM-dd ") TxtClassno.Text = myrow.Item(4).ToString TxtTel.Text = myrow.Item(5).ToString TxtRuDate.Text = Format(CDate(myrow.Item(6)), "yyyy-MM-dd ") TxtAddress.Text = myrow.Item(7).ToString TxtComment.Text = myrow.Item(8).ToString End SubPrivate Sub BtFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtFirst.Click ’指向第一条数据 rownumber = 0 ShowData() End Sub Private Sub BtPrev_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtPrev.Click ’指向上一条数据 BtNext.Enabled = True rownumber = rownumber - 1 If rownumber 《 0 Then rownumber = 0 ’如果到达记录的首部，行号设为零 BtPrev.Enabled = False End If ShowData() End Sub Private Sub BtNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtNext.Click ’指向上一条数据 BtPrev.Enabled = True rownumber = rownumber + 1 If rownumber 》 mytable.Rows.Count - 1 Then rownumber = mytable.Rows.Count - 1 ’判断是否到达最后一条数据 BtNext.Enabled = False End If ShowData() End Sub Private Sub BtLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtLast.Click ’指向最后一条数据 rownumber = mytable.Rows.Count - 1 ShowData() End Sub Private Sub BtDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtDelete.Click mytable.Rows.Item(rownumber).Delete() ’删除记录 If MsgBox( "确定要删除改记录吗？ ", MsgBoxStyle.OKCancel + vbExclamation, "警告 ") = MsgBoxResult.OK Then cmd = New OleDbCommandBuilder(ADOcmd) ’使用自动生成的SQL语句 ADOcmd.Update(ds, "student_Info ") BtNext.PerformClick() End If End Sub Private Sub BtModify_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtModify.Click TxtSID.Enabled = False ’关键字段只读 TxtName.Enabled = True ’可读写 ComboSex.Enabled = True TxtBornDate.Enabled = True TxtClassno.Enabled = True TxtRuDate.Enabled = True TxtTel.Enabled = True TxtAddress.Enabled = True TxtComment.Enabled = True End Sub Private Sub BtUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtUpdate.Click If Not Testtxt(TxtName.Text) Then MsgBox( "请输入姓名！ ", vbOKOnly + vbExclamation, "警告 ") TxtName.Focus() Exit Sub End If If Not Testtxt(ComboSex.Text) Then MsgBox( "请选择性别！ ", vbOKOnly + vbExclamation, "警告 ") ComboSex.Focus() Exit Sub End If If Not Testtxt(TxtClassno.Text) Then MsgBox( "请选择班号！ ", vbOKOnly + vbExclamation, "警告 ") TxtClassno.Focus() Exit Sub End If If Not Testtxt(TxtTel.Text) Then MsgBox( "请输入联系电话！ ", vbOKOnly + vbExclamation, "警告 ") TxtTel.Focus() Exit Sub End If If Not Testtxt(TxtAddress.Text) Then MsgBox( "请输入家庭住址！ ", vbOKOnly + vbExclamation, "警告 ") TxtAddress.Focus() Exit Sub End If If Not IsNumeric(Trim(TxtSID.Text)) Then MsgBox( "请输入数字学号！ ", vbOKOnly + vbExclamation, "警告 ") Exit Sub TxtSID.Focus() End If If Not IsDate(TxtBornDate.Text) Then MsgBox( "出生时间应输入日期格式（yyyy-mm-dd）！ ", vbOKOnly + vbExclamation, "警告 ") Exit Sub TxtBornDate.Focus() End If If Not IsDate(TxtRuDate.Text) Then MsgBox( "入校时间应输入日期格式（yyyy-mm-dd）！ ", vbOKOnly + vbExclamation, "警告 ") TxtRuDate.Focus() Exit Sub End If myrow.Item(0) = Trim(TxtSID.Text) myrow.Item(1) = Trim(TxtName.Text) myrow.Item(2) = Trim(ComboSex.Text) myrow.Item(3) = Trim(TxtBornDate.Text) myrow.Item(4) = Trim(TxtClassno.Text) myrow.Item(5) = Trim(TxtTel.Text) myrow.Item(6) = Trim(TxtRuDate.Text) myrow.Item(7) = Trim(TxtAddress.Text) myrow.Item(8) = Trim(TxtComment.Text) mytable.GetChanges() cmd = New OleDbCommandBuilder(ADOcmd) ’使用自动生成的SQL语句 ADOcmd.Update(ds, "student_Info ") ’对数据库进行更新 MsgBox( "修改学籍信息成功！ ", vbOKOnly + vbExclamation, "警告 ") TxtName.Enabled = False ComboSex.Enabled = False TxtBornDate.Enabled = False TxtClassno.Enabled = False TxtRuDate.Enabled = False TxtTel.Enabled = False TxtAddress.Enabled = False TxtComment.Enabled = False ’重新设置信息为只读 End Sub Private Sub BtCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtCancel.Click TxtSID.Enabled = False TxtName.Enabled = False ComboSex.Enabled = False TxtBornDate.Enabled = False TxtClassno.Enabled = False TxtRuDate.Enabled = False TxtTel.Enabled = False TxtAddress.Enabled = False TxtComment.Enabled = False End Sub Public Function ExecuteSQL(ByVal SQL As String, ByVal table As String) Try ’建立ADODataSetCommand对象 ’数据库查询函数 ADOcmd = New OleDbDataAdapter(SQL, "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\student.mdb ") ’建立ADODataSetCommand对象 ADOcmd.Fill(ds, table) ’取得表单 mytable = ds.Tables.Item(0) ’取得名为table的表 rownumber = 0 ’设置为第一行 myrow = mytable.Rows.Item(rownumber) ’取得第一行数据 Catch MsgBox(Err.Description) End Try End FunctionEnd Class
如何在VB中操作数据库
要查找数据库信息，关键是要解决链接数据库的问题，下面简述Vb如何SQL数据库：有两种方法可以实现，一种是通过ODBC数据源的配置进行连接，一种是通过纯代码进行连接，下面我们就详细介绍一下这两种连接方法。ODBC数据源VB连接SQL数据库一、配置ODBC数据源1、在控制面板中，双击管理工具，然后打开ODBC数据源管理器。2、在“系统DSN”选项卡中，单击“添加”按钮，打开“创建新数据源”对话框，在“名称”列表框中选择“SQLServer”。选好单击完成3、在打开“建立新的数据源到SQLServer”对话框，在“名称”文本框输入新数据源的名称，描述数据源按你理解的方式来写(随意)。“服务器”就选择你要连接到的服务器。4、选择使用用户输入登录的ID和密码的SQL验证。选连接SQL默认设置5、再下一步下一步，完成。测试数据源看连接是否成功就行了。成功后按确定。二、VB中设置连接1、添加部件MircrosoftADODataControl6.0(OLEDB),把部件拖到窗体。2、对ADO部件点右键选属性，选择使用连接字符串，按生成。3、选择MircosoftOLEDBProvidarforSQLServer按下一步4、1）输入服务器名称2）使用指定的服务器信息3）在服务器上选择数据库这时就选择你在SQLServer建好的数据库就行了5、测试连接可以看到连接是否成功！通过代码VB连接SQL数据库PublicconnAsNewADODB.ConnectionPublicrsAsNewADODB.RecordsetPublicaddFlagAsBooleanPublicFunctionOpenCn(ByValCipAsString)AsBooleanDimmagAsStringOnErrorGoTostrerrmagSetconn=NewADODB.Connectionconn.ConnectionTimeout=25conn.Provider="sqloledb"conn.Properties("datasource").Value=Cip’服务器的名字conn.Properties("initialcatalog").Value="zqoa"’库名’conn.Properties("integratedsecurity").Value="SSPI"’登陆类型conn.Properties("userid").Value="sa"conn.Properties("password").Value="sa"conn.OpenOpenCn=TrueaddFlag=TrueExitFunctionstrerrmag:mag="Datacan’tconnect"CallMsgBox(mag,vbOKOnly,"Error:Dataconnect")addFlag=FalseExitFunctionEndFunctionPublicSubcloCn()OnErrorResumeNextIfconn.StateadStateClosedThenconn.CloseSetconn=NothingEndSubPublicFunctionopenRs(ByValstrsqlAsString)AsBoolean’连接数据库记录集DimmagAsStringDimrpyAsBooleanOnErrorGoTostrerrmagSetrs=NewADODB.RecordsetIfaddFlag=FalseThenrpy=TrueWithrs.ActiveConnection=conn.CursorLocation=adUseClient.CursorType=adOpenKeyset.LockType=adLockOptimistic.OpenstrsqlEndWithaddFlag=TrueopenRs=TrueEnd’ExitFunctionstrerrmag:mag="datanotconnect"CallMsgBox(mag,vbOKOnly,"error:connect")openRs=FalseEnd’ExitFunctionEndFunctionPublicSubcloRs()OnErrorResumeNextIfrs.StateadStateClosedThenrs.CloneSetrs=NothingEndSub
