VB中如何使用SQL语句与数据库表格 *** 作

Private Sub Command2_Click()

If Adodc1RecordsetEOF = False Then

c = MsgBox("您确认要删除该记录吗？", vbOKCancel, "删除提示信息")

If c = vbOK Then

Adodc1RecordsetDelete

Adodc1RecordSource = "select from 人员表"

Adodc1Refresh

End If

Else

MsgBox "当前数据库中没有可删除的数据记录", vbOKOnly, "提示信息"

End If

End Sub

这就是一个删除按钮的代码，希望对你有帮助

1、打开代码窗口，添加引用：Imports SystemDataSqlClient。

2、输入以下代码：

“Public conn1  As SqlConnection = New SqlConnection _

("server=192168179; Initial Catalog= student; User ID= panqe;PWD=shentai768@")”，vb就已经成功连接sql数据库了。

3、代码详解：声明关键字Public（因为是全局变量，所以用Public 来声明）。

4、连接参数。

5、如果SQL 数据库就在本机，则用以下代码连接：

("server=; Integrated Security=False;Initial Catalog= student; User ID= panqe;PWD=shentai768@")。

6：如果代码太长，影响可读性，可以用空格加"_"后，回车换行。

Public cn As ADODBConnection

Public rs As ADODBRecordset

Public cc As String

Sub aa()

Set cn = New ADODBConnection

Set rs = New ADODBRecordset

cc = "Provider=SQLOLEDB1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=library" ‘这里要改成连接的数据库。

cnOpen cc

End Sub

以上是模块代码。

Private Sub Command1_Click()

If Text1Text = "" Then

MsgBox "请输入商品ID！"

Text1SetFocus

Exit Sub

End If

If Text2Text = "" Then

MsgBox "请输入商品名称！"

Text2SetFocus

Exit Sub

End If

If Text3Text = "" Then

MsgBox "请输入商品价格！"

Text3SetFocus

Exit Sub

End If

sql = "select from goods where goodid='" & Text1Text & "'"

Set rs = New ADODBRecordset

rsOpen sql, cn, 1, 3

If rsEOF Then

rsAddNew

rsFields(0) = Text1Text

rsFields(1) = Text2Text

rsFields(2) = Text3Text

rsUpdate

MsgBox "商品信息添加成功！"

Text1Text = ""

Text2Text = ""

Text3Text = ""

Text1SetFocus

Else

MsgBox "此ID已存在，请输入新ID！"

Text1Text = ""

Text1SetFocus

End If

rsClose

Set rs = Nothing

End Sub

Private Sub Form_Load()

Call aa

End Sub

以上是窗体代码。

你好好看一下，这是一个正确的源代码。

参考一下下面这段代码就可以了。

Imports SystemData

'引入数据库 *** 作类命名空间

Imports SystemDataOleDb

'引入ADONET *** 作命名空间

Public Class FrmModifystInfo

Inherits SystemWindowsFormsForm

Public ADOcmd As OleDbDataAdapter

Public ds As DataSet = New DataSet()

'建立DataSet对象

Public mytable As DataDataTable

'建立表单对象

Public myrow As DataDataRow

'建立数据行对象

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 SystemEventArgs) Handles MyBaseLoad

'窗体的载入

TxtSIDEnabled = False

TxtNameEnabled = False

ComboSexEnabled = False

TxtBornDateEnabled = False

TxtClassnoEnabled = False

TxtRuDateEnabled = False

TxtTelEnabled = False

TxtAddressEnabled = False

TxtCommentEnabled = False '设置信息为只读

Dim tablename As String = "student_Info "

SearchSQL = "select from student_Info "

ExecuteSQL(SearchSQL, tablename) '打开数据库

ShowData() '显示记录

End Sub

Private Sub ShowData()

'在窗口中的textbox中显示数据

myrow = mytableRowsItem(rownumber)

TxtSIDText = myrowItem(0)ToString

TxtNameText = myrowItem(1)ToString

ComboSexText = myrowItem(2)ToString

TxtBornDateText = Format(myrowItem(3), "yyyy-MM-dd ")

TxtClassnoText = myrowItem(4)ToString

TxtTelText = myrowItem(5)ToString

TxtRuDateText = Format(CDate(myrowItem(6)), "yyyy-MM-dd ")

TxtAddressText = myrowItem(7)ToString

TxtCommentText = myrowItem(8)ToString

End Sub

Private Sub BtFirst_Click(ByVal sender As SystemObject, ByVal e As SystemEventArgs) Handles BtFirstClick

'指向第一条数据

rownumber = 0

ShowData()

End Sub

Private Sub BtPrev_Click(ByVal sender As SystemObject, ByVal e As SystemEventArgs) Handles BtPrevClick

'指向上一条数据

BtNextEnabled = True

rownumber = rownumber - 1

If rownumber < 0 Then

rownumber = 0 '如果到达记录的首部，行号设为零

BtPrevEnabled = False

End If

ShowData()

End Sub

Private Sub BtNext_Click(ByVal sender As SystemObject, ByVal e As SystemEventArgs) Handles BtNextClick

'指向上一条数据

BtPrevEnabled = True

rownumber = rownumber + 1

If rownumber > mytableRowsCount - 1 Then

rownumber = mytableRowsCount - 1 '判断是否到达最后一条数据

BtNextEnabled = False

End If

ShowData()

End Sub

Private Sub BtLast_Click(ByVal sender As SystemObject, ByVal e As SystemEventArgs) Handles BtLastClick

'指向最后一条数据

rownumber = mytableRowsCount - 1

ShowData()

End Sub

Private Sub BtDelete_Click(ByVal sender As SystemObject, ByVal e As SystemEventArgs) Handles BtDeleteClick

mytableRowsItem(rownumber)Delete() '删除记录

If MsgBox( "确定要删除改记录吗？ ", MsgBoxStyleOKCancel + vbExclamation, "警告 ") = MsgBoxResultOK Then

cmd = New OleDbCommandBuilder(ADOcmd)

'使用自动生成的SQL语句

ADOcmdUpdate(ds, "student_Info ")

BtNextPerformClick()

End If

End Sub

Private Sub BtModify_Click(ByVal sender As SystemObject, ByVal e As SystemEventArgs) Handles BtModifyClick

TxtSIDEnabled = False '关键字段只读

TxtNameEnabled = True '可读写

ComboSexEnabled = True

TxtBornDateEnabled = True

TxtClassnoEnabled = True

TxtRuDateEnabled = True

TxtTelEnabled = True

TxtAddressEnabled = True

TxtCommentEnabled = True

End Sub

Private Sub BtUpdate_Click(ByVal sender As SystemObject, ByVal e As SystemEventArgs) Handles BtUpdateClick

If Not Testtxt(TxtNameText) Then

MsgBox( "请输入姓名！ ", vbOKOnly + vbExclamation, "警告 ")

TxtNameFocus()

Exit Sub

End If

If Not Testtxt(ComboSexText) Then

MsgBox( "请选择性别！ ", vbOKOnly + vbExclamation, "警告 ")

ComboSexFocus()

Exit Sub

End If

If Not Testtxt(TxtClassnoText) Then

MsgBox( "请选择班号！ ", vbOKOnly + vbExclamation, "警告 ")

TxtClassnoFocus()

Exit Sub

End If

If Not Testtxt(TxtTelText) Then

MsgBox( "请输入联系电话！ ", vbOKOnly + vbExclamation, "警告 ")

TxtTelFocus()

Exit Sub

End If

If Not Testtxt(TxtAddressText) Then

MsgBox( "请输入家庭住址！ ", vbOKOnly + vbExclamation, "警告 ")

TxtAddressFocus()

Exit Sub

End If

If Not IsNumeric(Trim(TxtSIDText)) Then

MsgBox( "请输入数字学号！ ", vbOKOnly + vbExclamation, "警告 ")

Exit Sub

TxtSIDFocus()

End If

If Not IsDate(TxtBornDateText) Then

MsgBox( "出生时间应输入日期格式（yyyy-mm-dd）！ ", vbOKOnly + vbExclamation, "警告 ")

Exit Sub

TxtBornDateFocus()

End If

If Not IsDate(TxtRuDateText) Then

MsgBox( "入校时间应输入日期格式（yyyy-mm-dd）！ ", vbOKOnly + vbExclamation, "警告 ")

TxtRuDateFocus()

Exit Sub

End If

myrowItem(0) = Trim(TxtSIDText)

myrowItem(1) = Trim(TxtNameText)

myrowItem(2) = Trim(ComboSexText)

myrowItem(3) = Trim(TxtBornDateText)

myrowItem(4) = Trim(TxtClassnoText)

myrowItem(5) = Trim(TxtTelText)

myrowItem(6) = Trim(TxtRuDateText)

myrowItem(7) = Trim(TxtAddressText)

myrowItem(8) = Trim(TxtCommentText)

mytableGetChanges()

cmd = New OleDbCommandBuilder(ADOcmd)

'使用自动生成的SQL语句

ADOcmdUpdate(ds, "student_Info ")

'对数据库进行更新

MsgBox( "修改学籍信息成功！ ", vbOKOnly + vbExclamation, "警告 ")

TxtNameEnabled = False

ComboSexEnabled = False

TxtBornDateEnabled = False

TxtClassnoEnabled = False

TxtRuDateEnabled = False

TxtTelEnabled = False

TxtAddressEnabled = False

TxtCommentEnabled = False '重新设置信息为只读

End Sub

Private Sub BtCancel_Click(ByVal sender As SystemObject, ByVal e As SystemEventArgs) Handles BtCancelClick

TxtSIDEnabled = False

TxtNameEnabled = False

ComboSexEnabled = False

TxtBornDateEnabled = False

TxtClassnoEnabled = False

TxtRuDateEnabled = False

TxtTelEnabled = False

TxtAddressEnabled = False

TxtCommentEnabled = False

End Sub

Public Function ExecuteSQL(ByVal SQL As String, ByVal table As String)

Try

'建立ADODataSetCommand对象

'数据库查询函数

ADOcmd = New OleDbDataAdapter(SQL, "Provider=MicrosoftJetOLEDB40;Data Source=c:\studentmdb ")

'建立ADODataSetCommand对象

ADOcmdFill(ds, table) '取得表单

mytable = dsTablesItem(0) '取得名为table的表

rownumber = 0 '设置为第一行

myrow = mytableRowsItem(rownumber)

'取得第一行数据

Catch

MsgBox(ErrDescription)

End Try

End Function

End Class

请采纳。

VB与数据库连接可以使用数据库控件进行,也可以引用ADO对象,数据库控件的使用比较简单,下面我就介绍使用ADO对象进行连接,首先打开"工程-引用",选择其中的Microsoft ActiveX Data Objects 20 Library(20以上版本都可以): \x0d\ACCESS数据库名为"学生mdb"其中有张数据库表名为"表",下面就介绍如何使用代码进行连接到数据库"学生",代码如下: \x0d\Function Connection() As String \x0d\'数据库的连接字符设置 \x0d\Connection = "Provider=MicrosoftJetOLEDB40;Data Source=" & AppPath + "\学生mdb" \x0d\End Function \x0d\数据库"学生mdb"在当前目录下,故使用APPPath \x0d\Private Sub Form_Load() \x0d\Dim mr As New ADODBRecordset \x0d\Dim cnn As New ADODBConnection \x0d\Dim Sql As String \x0d\Sql="select from 表" \x0d\cnnOpen Connection '使用cnn对象打开数据库连接 \x0d\mrOpen Sql, cnn, adOpenKeyset, adLockOptimistic \x0d\'打开数据库记录 \x0d\If mreof then \x0d\msgbox "数据库表为空!" \x0d\else \x0d\msgbox "数据库表不为空!" \x0d\end if \x0d\set mr=nothing \x0d\'关闭记录对象 \x0d\set cnn=nothing \x0d\'关闭连接对象 \x0d\End Sub \x0d\OK,程序到此,已经能成功地访问数据库了,并能打开数据库中的表

新建工程时选数据工程，此时VB6集成调试环境左边工具箱内已加载了有关数据库编程必须的控件。

然后在FORM1窗体中添加ADODC控件和DATAGRID控件，将DATAGRID1的属性DATASOURCE选ADODC1，打开ADODC1控件属性页使用连接字符串，选生成，在提供者选项中选MICROSOFT jet 40 OLE DB Provider,然后按要求连接数据库等。在ADODC1控件属性页使用连接字符串空白文本窗口中就有一长串字符串，注意该字符串可复制到程序代码用于编程。ADODC1控件属性页的数据源内有命令文本（SQL）编写窗口可编写SQL查询语言。该窗口的SQL语句可复制到程序代码用于编程。

SQL查询语言主要结构为：

Select 查询字段 from 表名 Where 查询条件语句 [排序语句或分组语句]

查询字段必须分别用（西文）逗号分开或就用一个号代替，上述查询中排序语句建议最好应用。

SQL查询语言如有错程序运行时告诉你出错，作相应改动即可。本人一般先按上述连接，SQL用"SELECT FROM TabelName"作调试，无问题，用一个按钮控件将前述需复制的连接字符串和SQL查询语言先复制于按钮控件的CLICK事件中备着，以便放着以后使用。然后删去ADODC1控件，再添加ADODC1控件再添其他内容。

以上就是关于VB中如何使用SQL语句与数据库表格 *** 作全部的内容，包括:VB中如何使用SQL语句与数据库表格 *** 作、如何用vb连接sql server数据库、VB 向数据库 写入数据等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！