作者su35 (su35)
看板Visual_Basic
標題[.NET] 如何寫入資料庫
時間Wed Dec 24 19:54:57 2008
我用vb2005.net 開ms sql的資料庫 檔名cNorthWind.mdf
表單名稱 員工
問題一Dim da As New SqlClient.SqlDataAdapter(SQL, conn)
Dim dc As New SqlClient.SqlCommand(SQL, conn)
這兩個有什麼不同 一樣都是SQL語法 還是說一個只有查詢 一個是加入刪除的功能
問題二 DBACCRESS.ExecuteCmd(....,.....)
{
這涵數 當parameters有值的話 它到底做了什麼事
parameters 它儲存什麼東西
}
問題三
我在Button2 做了把第一列的欄位資料刪除
它在不關掉程式 的確那欄資料不見了
可是根本沒寫入到資料庫內
為什麼會這樣 還有要怎麼樣才能寫進去
感謝
Public Class Form1
Dim DB As DbAccess
Dim DT As New DataTable
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
UpData()
End Sub
Sub UpData()
DB = New DbAccess("cNorthWind.mdf")
DT = DB.GetDataTable("select * from 員工")
DB = Nothing
DataGridView1.DataSource = DT '將DataTable 傳給DataGridView
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
DB = New DbAccess("cNorthWind.mdf")
DT = DB.GetDataTable("select * from 員工 where 員工編號 = '" & TextBox1.Text & "'")
DataGridView1.DataSource = DT
DB = Nothing
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
SQL_string = "delete from 員工 where 員工編號 = '1'"
DB = New DbAccess("cNorthWind.mdf")
DB.ExecuteCmd(SQL_string)
DB = Nothing
End Sub
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
DB = New DbAccess("cNorthWind.mdf") '建立資料庫連線
DT = DB.GetDataTable("select * from 員工") '寫sql 讀取資料到DataTable
DB = Nothing '解除物件
DataGridView1.DataSource = DT '將DataTable 傳給DataGridView
End Sub
Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
DB = New DbAccess("cNorthWind.mdf") '建立資料庫連線
DT = DB.GetDataTable("select * from 員工") '寫sql 讀取資料到DataTable
Dim DA As DataAdapter
DA.Update(DS)
End Sub
End Class
Public Class DbAccess
Private conn As SqlClient.SqlConnection
Private _ErrorMessage As String
'回傳錯誤訊息
Public Property ErrorMessage() As String
Get
Return _ErrorMessage
End Get
Set(ByVal value As String)
_ErrorMessage = value
End Set
End Property
Sub New(ByVal SqlExpressDbName As String)
'建立連線
If System.IO.Path.GetDirectoryName(SqlExpressDbName) = "" Then
conn = New SqlClient.SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\" & SqlExpressDbName & ";Integrated Security=True;User Instance=True")
Else
conn = New SqlClient.SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=" & SqlExpressDbName & ";Integrated Security=True;User Instance=True")
End If
End Sub
'讀取資料
Function GetDataTable(ByVal SQL As String) As DataTable
Try
Dim dt As New DataTable
Dim da As New SqlClient.SqlDataAdapter(SQL, conn)
da.Fill(dt)
Return dt
Catch ex As Exception
MsgBox("讀取資料時發生錯誤(" & SQL & ") " & vbCrLf & "錯誤訊息:" & ex.Message, MsgBoxStyle.Critical)
Return Nothing
End Try
End Function
'執行命令
Function ExecuteCmd(ByVal SQL As String) As Integer
Return ExecuteCmd(SQL, Nothing)
End Function
'執行命令
Function ExecuteCmd(ByVal SQL As String, ByVal parameters As SqlClient.SqlParameterCollection) As Integer
Try
If conn.State <> ConnectionState.Open Then conn.Open()
Dim dc As New SqlClient.SqlCommand(SQL, conn)
'處理參數
If parameters IsNot Nothing Then
dc.Parameters.Clear()
For Each item As SqlClient.SqlParameter In parameters
dc.Parameters.AddWithValue(item.ParameterName, item.Value)
Next
End If
'執行
Return dc.ExecuteNonQuery
Catch ex As Exception
MsgBox("執行命令時發生錯誤(" & SQL & ") " & vbCrLf & "錯誤訊息:" & ex.Message, MsgBoxStyle.Critical)
Return Nothing
End Try
End Function
End Class
--
"◢██◣ 呼呼呼～ 為什麼…我這麼愛你…
↙操B跑車 " ,█◥◥◥ ˊ 對不起，他有車。 ˋ ◢██◣
▄◣ ◤ ◎ ◎ ◣◢██◣ˊ █◥◥◥▌
◣◥◣ ◥ ▼◤ ▉█◥◥◥ ◤ ▇ ▇◤
██████ ◣ ╲██◣ ▉◤ ▄ ▄ ◥ █▲◤
██◤▄◥█▃ ◢ █▃●╲ ◤╲█"█◤ g121630作 ◢ ◣ ◣
--
※ 發信站: 批踢踢實業坊(ptt.cc)
◆ From: 122.116.105.76