VB.NET增删改查的问题

clamli007 2009-05-20 01:53:32
头大,学VB.neT学郁闷了,我肯定是方法不对。
直接问问大家,能帮我完成这一段代码吗?
是一个类下面对数据增删改查的问题
数据库是SQL2000,pubs
以下是我写的代码
帮我完成下吧,谢谢大家了。
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Text
Imports System.Collections.Generic


Public Class DBTool
Public Sub connect()
Dim strconnect As String
strconnect = "provider=SQLOLEDB;data source=.;initial catalog=pubs;user id=sa;password=;"
Dim conn As SqlConnection
conn = New SqlConnection(strconnect)
End Sub

Public Sub insertsql()

End Sub

Public Sub deletesql()

End Sub

Public Sub updatesql()

End Sub

Public Sub selectsql()

End Sub
End Class
...全文
462 12 打赏 收藏 转发到动态 举报
写回复
用AI写文章
12 条回复
切换为时间正序
请发表友善的回复…
发表回复
fadsasd 2011-08-06
  • 打赏
  • 举报
回复
你们谁有修改的代码啊
gjw_8888 2009-05-21
  • 打赏
  • 举报
回复

Imports System
Imports System.Data
Imports System.Data.OleDb
Public Class dboper
Private strconn As String = "provider=SQLOLEDB;data source=.;initial catalog=pubs;user id=sa;password=;"
Private connect As New OleDbConnection
Private adapter As New OleDbDataAdapter
'聯接初始化
Public Sub InitConnection()
Try
connect = New OleDbConnection(strconn)
If (connect.State = ConnectionState.Closed) Then
connect.Open()
End If
Catch ex As Exception
End Try
End Sub
'關閉聯接
Public Sub CloseConnection()
If (connect.State = ConnectionState.Open) Then
connect.Close()
End If
End Sub
'查詢返回一個表
Public Function getTable(ByVal sql As String) as DataTable
Try
Me.InitConnection()
Me.adapter = New OleDbDataAdapter(sql, Me.connect)
Dim dt As New DataTable
Me.adapter.Fill(dt)
Me.adapter.Update(dt)
Return dt
Catch e As Exception
End Try
End Function

Public Sub NoResultSQL(ByVal sql As String) ''insert,delete,update
Try
Me.InitConnection()
Me.adapter.SelectCommand = New OleDbCommand(sql, connect)
Me.adapter.SelectCommand.ExecuteNonQuery()
Catch e As Exception
End Try
End Sub
End Class
没有昵称阿 2009-05-21
  • 打赏
  • 举报
回复
Public Sub DeleteX()

Dim rstRoySched As ADODB.Recordset
Dim strCnn As String
Dim strMsg As String
Dim strTitleID As String
Dim intLoRange As Integer
Dim intHiRange As Integer
Dim intRoyalty As Integer

' 打开 RoySched 表。
strCnn = "Provider=sqloledb;" & _
"Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; "
Set rstRoySched = New ADODB.Recordset
rstRoySched.CursorLocation = adUseClient
rstRoySched.CursorType = adOpenStatic
rstRoySched.LockType = adLockBatchOptimistic
rstRoySched.Open "SELECT * FROM roysched " & _
"WHERE royalty = 20", strCnn, , , adCmdText

' 提示删除记录。
strMsg = "Before delete there are " & _
rstRoySched.RecordCount & _
" titles with 20 percent royalty:" & vbCr & vbCr
Do While Not rstRoySched.EOF
strMsg = strMsg & rstRoySched!title_id & vbCr
rstRoySched.MoveNext
Loop
strMsg = strMsg & vbCr & vbCr & _
"Enter the ID of a record to delete:"
strTitleID = UCase(InputBox(strMsg))

' 移到记录并保存数据以使其可被恢复。
rstRoySched.Filter = "title_id = '" & strTitleID & "'"
intLoRange = rstRoySched!lorange
intHiRange = rstRoySched!hirange
intRoyalty = rstRoySched!royalty

' 删除记录。
rstRoySched.Delete
rstRoySched.UpdateBatch

' 显示结果。
rstRoySched.Filter = adFilterNone
rstRoySched.Requery
strMsg = ""
strMsg = "After delete there are " & _
rstRoySched.RecordCount & _
" titles with 20 percent royalty:" & vbCr & vbCr
Do While Not rstRoySched.EOF
strMsg = strMsg & rstRoySched!title_id & vbCr
rstRoySched.MoveNext
Loop
MsgBox strMsg

' 恢复数据,因为这只是演示。
rstRoySched.AddNew
rstRoySched!title_id = strTitleID
rstRoySched!lorange = intLoRange
rstRoySched!hirange = intHiRange
rstRoySched!royalty = intRoyalty
rstRoySched.UpdateBatch

rstRoySched.Close

End Sub


Public Sub UpdateX()

Dim rstEmployees As ADODB.Recordset
Dim strOldFirst As String
Dim strOldLast As String
Dim strMessage As String

' 使用雇员表中的姓名打开记录集。
strCnn = "Provider=sqloledb;" & _
"Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; "
Set rstEmployees = New ADODB.Recordset
rstEmployees.CursorType = adOpenKeyset
rstEmployees.LockType = adLockOptimistic
rstEmployees.Open "SELECT fname, lname " & _
"FROM Employee ORDER BY lname", strCnn, , , adCmdText

' 储存原始数据。
strOldFirst = rstEmployees!fname
strOldLast = rstEmployees!lname
' 更改编辑缓冲区中的数据。
rstEmployees!fname = "Linda"
rstEmployees!lname = "Kobara"

' 显示缓冲区的内容并获取用户输入。
strMessage = "Edit in progress:" & vbCr & _
" Original data = " & strOldFirst & " " & _
strOldLast & vbCr & " Data in buffer = " & _
rstEmployees!fname & " " & rstEmployees!lname & vbCr & vbCr & _
"Use Update to replace the original data with " & _
"the buffered data in the Recordset?"

If MsgBox(strMessage, vbYesNo) = vbYes Then
rstEmployees.Update
Else
rstEmployees.CancelUpdate
End If

' 显示结果数据。
MsgBox "Data in recordset = " & rstEmployees!fname & " " & _
rstEmployees!lname

' 恢复原始数据,因为这只是演示。
If Not (strOldFirst = rstEmployees!fname And _
strOldLast = rstEmployees!lname) Then
rstEmployees!fname = strOldFirst
rstEmployees!lname = strOldLast
rstEmployees.Update
End If

rstEmployees.Close

End Sub

该范例连同 AddNew 方法说明 Update 方法。

Public Sub UpdateX2()

Dim cnn1 As ADODB.Connection
Dim rstEmployees As ADODB.Recordset
Dim strEmpID As String
Dim strOldFirst As String
Dim strOldLast As String
Dim strMessage As String

' 打开连接。
Set cnn1 = New ADODB.Connection
strCnn = "Provider=sqloledb;" & _
"Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; "
cnn1.Open strCnn

' 使用雇员表中的数据打开记录集。
Set rstEmployees = New ADODB.Recordset
rstEmployees.CursorType = adOpenKeyset
rstEmployees.LockType = adLockOptimistic
rstEmployees.Open "employee", cnn1, , , adCmdTable

rstEmployees.AddNew
strEmpID = "B-S55555M"
rstEmployees!emp_id = strEmpID
rstEmployees!fname = "Bill"
rstEmployees!lname = "Sornsin"

' 显示缓冲区内容并获取用户输入。
strMessage = "AddNew in progress:" & vbCr & _
"Data in buffer = " & rstEmployees!emp_id & ", " & _
rstEmployees!fname & " " & rstEmployees!lname & vbCr & vbCr & _
"Use Update to save buffer to recordset?"

If MsgBox(strMessage, vbYesNoCancel) = vbYes Then
rstEmployees.Update
' 转到新记录并显示结果数据。
MsgBox "Data in recordset = " & rstEmployees!emp_id & ", " & _
rstEmployees!fname & " " & rstEmployees!lname
Else
rstEmployees.CancelUpdate
MsgBox "No new record added."
End If

' 删除新数据,因为这只是演示。
cnn1.Execute "DELETE FROM employee WHERE emp_id = '" & strEmpID & "'"

rstEmployees.Close

End Sub
没有昵称阿 2009-05-21
  • 打赏
  • 举报
回复
Public Sub AddNewX()

Dim cnn1 As ADODB.Connection
Dim rstEmployees As ADODB.Recordset
Dim strCnn As String
Dim strID As String
Dim strFirstName As String
Dim strLastName As String
Dim booRecordAdded As Boolean

' 打开连接。
Set cnn1 = New ADODB.Connection
strCnn = "Provider=sqloledb;" & _
"Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=;"
cnn1.Open strCnn

' 打开 Employee 表。
Set rstEmployees = New ADODB.Recordset
rstEmployees.CursorType = adOpenKeyset
rstEmployees.LockType = adLockOptimistic
rstEmployees.Open "employee", cnn1, , , adCmdTable

' 从用户获取数据,雇员 ID 的格式应为:
' 名、中间名和姓的三个首字母,
' 五位数字,以及性别标识 M 或 F。
' 例如,Bill Sornsin 的雇员 ID 为:B-S55555M。
strID = Trim(InputBox("Enter employee ID:"))
strFirstName = Trim(InputBox("Enter first name:"))
strLastName = Trim(InputBox("Enter last name:"))

' 只在用户输入姓和名之后进行。
If (strID <> "") And (strFirstName <> "") _
And (strLastName <> "") Then

rstEmployees.AddNew
rstEmployees!emp_id = strID
rstEmployees!fname = strFirstName
rstEmployees!lname = strLastName
rstEmployees.Update
booRecordAdded = True

' 显示新添加的数据。
MsgBox "New record: " & rstEmployees!emp_id & " " & _
rstEmployees!fname & " " & rstEmployees!lname

Else
MsgBox "Please enter an employee ID, " & _
"first name, and last name."
End If

' 删除新记录,因为这只是演示。
cnn1.Execute "DELETE FROM employee WHERE emp_id = '" & strID & "'"

rstEmployees.Close
cnn1.Close

End Sub
tayirvb 2009-05-21
  • 打赏
  • 举报
回复
学习学习
tanghuiming 2009-05-21
  • 打赏
  • 举报
回复
二种方法:
1.SqlDataAdapter方法.
Public DA As SqlDataAdapter=new SqlDataAdapter("SQL语句",Sqlconn)
If DS.HasChanges Then
SqlCB = New SqlCommandBuilder(DA)
DA.Update(DS)
MessageBox.Show("数据保存成功!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information)
Else
MessageBox.Show("没有数据修改!", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information)
End If

2.SqlCommand方法.
Dim Sqlcmd As SqlCommand = New SqlCommand("SQL语句" Sqlconn)
Sqlcmd.ExecuteNonQuery()

clamli007 2009-05-21
  • 打赏
  • 举报
回复
up
凤宝儿 2009-05-21
  • 打赏
  • 举报
回复
不会增删改差的sql语句还是不会底层方法?
这样的例子网上找不到哈?
.................
zzWind 2009-05-21
  • 打赏
  • 举报
回复
用sqlcommand即可。
kzccomputer 2009-05-20
  • 打赏
  • 举报
回复
你可以利用sqlcommand
smq0808 2009-05-20
  • 打赏
  • 举报
回复
Public Sub insertsql()
dim str as string
Dim str1, str2 As String
conn.open()
dim cmd as new sqlclient.command
str1 = "id," : str2 = "'" & Trim(Str(id)) & "',"
str1 += "姓名," : str2 += "'" & Me.txtxm.Text & "',"
str1 += "性别," : str2 += "'" & Me.Txtxb.Text & "',"
str1 += "年龄," : str2 += "'" & Txtnl.Text & "',"
str1 += "班级," : str2 += "'" & Me.Txtbj.Text & "',"
str1 += "联系方式," : str2 += "'" & Me.Txtlxfs.Text & "',"
str1 += "家庭地址," : str2 += "'" & Me.jtdz.Text & "',"
str1 += "生活状况" : str2 += "'" & Me.TxtshZk.Text & "'"
strsql = "insert student (" & str1 & ") values (" & str2 & ")"
Mycmd.CommandText = strsql
Mycmd.Connection = Conn1
If Mycmd.ExecuteNonQuery() > 0 Then
MsgBox("添加成功")
End If


End Sub
clamli007 2009-05-20
  • 打赏
  • 举报
回复
表任意,无所谓。
可以尽量简单

16,549

社区成员

发帖
与我相关
我的任务
社区描述
VB技术相关讨论,主要为经典vb,即VB6.0
社区管理员
  • VB.NET
  • 水哥阿乐
  • 无·法
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧