۰۷-بهمن-۱۳۸۸, ۱۹:۵۰:۵۷
۱۰-بهمن-۱۳۸۸, ۲۱:۱۵:۵۰
به چه دیتابیسی میخوای insert کنی؟
۱۱-بهمن-۱۳۸۸, ۰۱:۱۱:۰۲
ديتابيس اكسز
۱۱-بهمن-۱۳۸۸, ۱۳:۳۶:۰۰
فایل
افزودن رکورد
نقل قول: C:\SignIn.mdb
افزودن رکورد
کد:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
FirstName = txtFName.Text
LastName = txtLName.Text
Dates = DisplayDate.Text
Time = DisplayTime.Text
User = DisplayUsername.Text
PC = DisplayPCName.Text
Valid = False
If FirstName = Nothing Then
MsgBox("Please enter First Name")
Valid = False
Else
Valid = True
End If
If LastName = Nothing Then
MsgBox("Please enter Last Name")
Valid = False
Else
Valid = True
End If
If Valid = True Then
Dim Cmd As OleDbCommand
Dim SQL As String
Dim objCmd As New OleDbCommand
Dim Con = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\SignIn.mdb")
SQL = "INSERT INTO table VALUES ('" & FirstName & "', '" & LastName & "', '" & Dates & "', '" & Time & "', '" & User & "', '" & PC & "')"
Cmd = New OleDbCommand(SQL, Con)
Con.Open()
objCmd = New OleDbCommand(SQL, Con)
objCmd.ExecuteNonQuery()
Con.Close()
End If
End Sub۱۱-بهمن-۱۳۸۸, ۱۶:۰۵:۳۹
من این کلاس رو نوشتم و ازش استفاده میکنم. طبق نیاز خودم کم کم بهش چیزایی رو اضافه کردم. این برای SqlServer هسته ولی خیلی راحت با یه replace من برای SQLite هم استفاده کردم.
کد:
Imports System.Data.SqlClient
Public Class DATAC
Public cnn As SqlConnection
Dim da As SqlDataAdapter
Public server As String
Public dbname As String
Public Sub New(ByVal serverAddress As String, ByVal DatabaseName As String)
server = serverAddress
cnn = New SqlConnection("Data Source=" & serverAddress & ";Initial Catalog=" & DatabaseName & ";Integrated Security=True")
End Sub
Public Function GetData(ByVal sqls As String) As DataTable
da = New SqlDataAdapter(sqls, cnn)
Dim dt As New DataTable
Try
da.Fill(dt)
Catch ex As Exception
Throw ex
End Try
Return dt
End Function
Public Function Execute(ByVal sqls As String, ByVal ParamArray par() As Object)
Dim c As New SqlCommand(sqls, cnn)
If par.Length > 0 Then
For i As Integer = 0 To par.Length - 1 Step 2
c.Parameters.Add(New SqlParameter(CStr(par(i)), par(i + 1)))
Next
End If
If cnn.State = ConnectionState.Closed Then cnn.Open()
c.ExecuteNonQuery()
cnn.Close()
Return 0
End Function
Public Function ExecuteScaler(ByVal sqls As String, ByVal ParamArray par() As Object) As Object
Dim c As New SqlCommand(sqls, cnn)
If par.Length > 0 Then
For i As Integer = 0 To par.Length - 1 Step 2
c.Parameters.Add(New SqlParameter(CStr(par(i)), par(i + 1)))
Next
End If
cnn.Open()
Dim o As Object = c.ExecuteScalar()
If TypeOf o Is DBNull Then o = 0
cnn.Close()
Return o
End Function
Public Function CSelect(ByVal tbl As String, ByVal fields As String, ByVal condition As String) As DataTable
Return GetData("SELECT " & fields & " FROM " & tbl & IIf(condition = "", "", " Where " & condition))
End Function
Public Function CInsert(ByVal tbl As String, ByVal fields As String, ByVal values As String) As Integer
Return Execute("Insert into " & tbl & "(" & fields & ") values(" & values & ")")
End Function
Public Function CUpdate(ByVal tbl As String, ByVal values As String, ByVal condition As String) As Integer
Return Execute("update " & tbl & " Set " & values & IIf(condition = "", "", " Where " & condition))
End Function
Public Function CRemove(ByVal tbl As String, ByVal condition As String) As Integer
Return Execute("Delete from " & tbl & IIf(condition = "", "", " Where " & condition))
End Function
Public Sub ClearTable(ByVal tbl As String)
CRemove(tbl, "")
End Sub
Public Sub SetField(ByVal tbl As String, ByVal field As String, ByVal value As Object, Optional ByVal condition As String = "")
Execute("update " & tbl & " set " & field & "=@p01 " & IIf(condition = "", "", " Where " & condition), "p01", value)
End Sub
Public Sub AddField(ByVal tbl As String, ByVal field As String, ByVal value As String, Optional ByVal condition As String = "")
Execute("update " & tbl & " set " & field & "=" & field & "+" & value & IIf(condition = "", "", " Where " & condition))
End Sub
Public Function GetByID(ByVal tbl As String, ByVal id As Int32, Optional ByVal f As String = "name") As String
Dim dt As DataTable = GetData("select * from " & tbl & " where id=" & id)
If TypeOf dt.Rows(0)(f) Is DBNull Then
Return 0
Else
Return dt.Rows(0)(f)
End If
End Function
Public Function GetByField(ByVal tbl As String, ByVal fieldvalue As String, Optional ByVal searchfname As String = "name", Optional ByVal retfield As String = "id") As String
Dim dt As DataTable = GetData("select * from " & tbl & " where " & searchfname & "='" & fieldvalue & "'")
If dt.Rows.Count = 0 Then Return ""
Return dt.Rows(0)(retfield)
End Function
Public Function GetRowByID(ByVal tbl As String, ByVal id As Int32) As DataTable
Dim dt As DataTable = GetData("select * from " & tbl & " where id=" & id)
Return dt
End Function
Public Function GetRowByCondition(ByVal tbl As String, ByVal Condition As String) As DataTable
Dim dt As DataTable = GetData("select * from " & tbl & IIf(Condition = "", "", " where " & Condition))
Return dt
End Function
End Class