How to code in VB to get connected with SQL database and to perform delete function in one of the tables
From VB.NET
---------------------
connect direct to SqlSever (MS SQL Server Studio 2005)
************************************************** ***************
Imports System.Data.SqlClient
Dim Cnn As New SqlConnection
Dim CnnStr$
CnnStr = "Data Source=PC2\SQLEXPRESS;Initial Catalog=KhmerDictionary;Integrated Security=True;Pooling=False;"
Cnn = New SqlConnection(CnnStr)
Cnn.Open()
************************************************** ****************
Connect Indirect to Sql Server Database File
----------------------------------------------------------------------------------
Imports System.Data.SqlClient
Dim Cnn As New SqlConnection
Dim CnnStr$
CnnStr="Data Source=.\SQLEXPRESS;AttachDbFilename="C:\NimolProj ect\Dictionary Testing\Original DBDictionary\KhmerDictionary.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True"
Cnn = New SqlConnection(CnnStr)
Cnn.Open()
-----------------------------------------------------------------------------------------
Delete All Record or one Record by use Sql Command
************************************************** *********************
Below is some usefull of DataBase Procedure Insert /Update/Delete
'Module2
-------------------------------------------------------------------------------------------------
Imports System.Data.SqlClient
Module Module2
Dim cnn As New SqlClient.SqlConnection
Public LocPos As Integer
Public TotalPos As Integer
Public Enum CustomDel
DellAll = 1
DelCustom = 2
End Enum
Public Enum Optionshow
SHowAllFields = 1
SHowSomeField = 2
End Enum
Sub DeleteData(ByVal tbl As String, ByVal FieldCon As String, ByVal ValueCon As String, ByVal Deloption As CustomDel)
Try
Dim cm As New SqlClient.SqlCommand
Dim del As Integer
Dim SQLa As String : Dim SQLc As String
SQLa = "delete * from " & tbl
SQLc = "delete from " & tbl & " where " & FieldCon & "='" & ValueCon & "'"
Select Case Deloption
Case 1 : del = CustomDel.DellAll : cm.CommandText = SQLa
Case 2 : del = CustomDel.DelCustom : cm.CommandText = SQLc
End Select
cm.Connection = cnn
cm.ExecuteNonQuery()
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
Sub TransferData2txt(ByVal frm As Form, ByVal tbl As String, ByVal Pos As Integer, ByVal ParamArray Txt() As String)
Dim cm As New SqlClient.SqlCommand
Dim ds As New DataSet
Dim adp As New SqlClient.SqlDataAdapter
Dim ct As Control : Dim i As Integer
On Error GoTo err
cm.CommandText = "select * from " & tbl
cm.Connection = cnn
adp = New SqlClient.SqlDataAdapter("select * from " & tbl, cnn)
ds = New DataSet(tbl)
adp.Fill(ds, tbl)
TotalPos = ds.Tables(tbl).Rows.Count - 1
adp.Dispose()
Dim dr As SqlClient.SqlDataReader = cm.ExecuteReader
If ds Is Nothing Then Return
With ds.Tables(tbl).Rows(Pos)
For Each ct In frm.Controls
If TypeOf ct Is TextBox Then
For i = 0 To UBound(Txt)
If LCase(Txt(i)) = LCase(ct.Name) Then
ct.Text = .Item(i).ToString
i = i + 1 : Exit For
End If
Next
End If
Next
End With
dr.Close()
err: Exit Sub
End Sub
Function IDcreator(ByVal tbl As String, ByVal IDStyle As String, ByVal Connector As String, ByVal FormatNumber As String, ByVal Field As String) As String
Dim cm As New SqlClient.SqlCommand
Dim da As New SqlClient.SqlDataAdapter
Dim ds As New DataSet()
Dim Tem As String
Try
ds = New DataSet(tbl)
da = New SqlClient.SqlDataAdapter("select * from " & tbl, cnn)
da.Fill(ds, tbl)
Dim RecordCount = ds.Tables(tbl).Rows.Count() 'count all records in one table
Tem = IDStyle & Connector & Format(RecordCount + 1, FormatNumber)
Dim i = 1
Do
i = i + 1
cm.CommandText = "select * from " & tbl & " where " & Field & " = '" & Tem & "'"
cm.Connection = cnn
Dim rst As SqlClient.SqlDataReader = cm.ExecuteReader
If rst.HasRows Then
Tem = IDStyle & Connector & Format(RecordCount + i, FormatNumber)
rst.Close()
Else : IDcreator = Tem : Exit Function
End If
Loop
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Function
Sub showDataTolst(ByVal tbl As String, ByVal lst As ListView, ByVal showOption As Optionshow, ByVal ParamArray SelectField() As String)
Dim cm As New SqlClient.SqlCommand
Dim da As New SqlClient.SqlDataAdapter
Dim ds As New DataSet()
Dim Opt As Integer, TemField As String
Dim a As Integer, Sql As String, i%, ii%
Dim ColH As ColumnHeader
lst.View = View.Details
lst.Clear()
Select Case showOption
Case 1 : Opt = Optionshow.SHowAllFields
Sql = "select * from " & tbl
Case 2 : Opt = Optionshow.SHowSomeField
For a = 0 To UBound(SelectField)
TemField = TemField & SelectField(a) & ","
Next
TemField = Strings.Left(TemField, Len(TemField) - 1)
Sql = "select " & TemField & " from " & tbl
End Select
cm.CommandText = Sql
cm.Connection = cnn
da = New SqlClient.SqlDataAdapter(Sql, cnn)
ds = New DataSet(tbl)
da.Fill(ds, tbl)
For i = 0 To ds.Tables(tbl).Columns.Count - 1
Dim fieldName = ds.Tables(tbl).Columns(i).ColumnName ' find caption of field name
ColH = New ColumnHeader()
ColH.Text = fieldName
lst.Columns.Add(ColH)
Next
For Each ColH In lst.Columns
ColH.Width = 90
Next
Dim dr As SqlClient.SqlDataReader = cm.ExecuteReader
While dr.Read
Dim lstitem As ListViewItem
For i = 0 To dr.FieldCount - 1
lstitem = New ListViewItem(dr.Item(i).ToString)
For ii = 1 To dr.FieldCount - 1
lstitem.SubItems.Add(dr.Item(ii).ToString)
Next
lst.Items.Add(lstitem)
Exit For
Next
End While
dr.Close()
End Sub
Sub AddNewRecord(ByVal tbl As String, ByVal ParamArray Data() As String)
Dim cm As New SqlClient.SqlCommand
Dim da As New SqlClient.SqlDataAdapter
Dim ds As New DataSet()
Dim Temp As String
Dim FTemp As String
Try
ds = New DataSet(tbl)
da = New SqlClient.SqlDataAdapter("select * from " & tbl, cnn)
da.Fill(ds, tbl)
Dim i As Integer
For i = 0 To UBound(Data)
Dim fieldName = ds.Tables(tbl).Columns(i).ColumnName ' find caption of field name
Temp = Temp & fieldName & ","
FTemp = FTemp & "'" & Data(i) & "'" & ","
Next
Temp = Strings.Left(Temp, Len(Temp) - 1)
FTemp = Strings.Left(FTemp, Len(FTemp) - 1)
Dim sql As String
sql = "Insert into " & tbl & "(" & Temp & ")" & " Values(" & FTemp & ")"
cm.CommandText = sql
cm.Connection = cnn
cm.ExecuteNonQuery()
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
Sub UpDateDataToTable(ByVal tbl As String, ByVal FieldCon As String, ByVal ValueCon As String, ByVal ParamArray Data() As String)
Try
Dim Temp As String
Dim cm As New SqlClient.SqlCommand
Dim da As New SqlClient.SqlDataAdapter
Dim ds As New DataSet()
ds = New DataSet(tbl)
da = New SqlClient.SqlDataAdapter("select * from " & tbl, cnn)
da.Fill(ds, tbl)
Dim i As Integer
For i = 0 To UBound(Data)
Dim fieldName = ds.Tables(tbl).Columns(i).ColumnName ' find caption of field name
Temp = Temp & fieldName & "='" & Data(i) & "',"
Next
Temp = Strings.Left(Temp, Len(Temp) - 1)
cm.CommandText = "update " & tbl & " set " & Temp & " where " & FieldCon & "='" & ValueCon & "'"
cm.Connection = cnn
cm.ExecuteNonQuery()
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
End Module
------------------------------------------------------------------------------------------------------------------
Example
************************************************** *****************************************
sub Form_load
TransferData2txt (Me, "ProductInfo", , "TextBox1", "TextBox2", "TextBox3", "TextBox4")
showDataTolst("ProductInfo", Me.Lst, Optionshow.SHowAllFields)
end sub
Private Sub CmdFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CmdFirst.Click
Lst.Items(LocPos).Selected = False
LocPos = 0 : Me.lblrecord.Text = "1 / " & TotalPos + 1
TransferData2txt(Me, "ProductInfo", LocPos, "TextBox1", "TextBox2", "TextBox3", "TextBox4")
Lst.Items(LocPos).Selected = True
End Sub
Private Sub CmdLast_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles CmdLast.Click
Lst.Items(LocPos).Selected = False
LocPos = TotalPos : Me.lblrecord.Text = TotalPos + 1 & " / " & TotalPos + 1
TransferData2txt(Me, "ProductInfo", LocPos, "TextBox1", "TextBox2", "TextBox3", "TextBox4")
Lst.Items(LocPos).Selected = True
End Sub
Private Sub CmdNext_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles CmdNext.Click
Lst.Items(LocPos).Selected = False
If LocPos >= TotalPos Then
lblrecord.Text = TotalPos + 1 & " OF " & TotalPos + 1
Else
LocPos = LocPos + 1
lblrecord.Text = LocPos + 1 & " / " & TotalPos + 1
TransferData2txt(Me, "ProductInfo", LocPos, "TextBox1", "TextBox2", "TextBox3", "TextBox4")
Lst.Items(LocPos).Selected = True
End If
End Sub
Private Sub CmdPre_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CmdPre.Click
Lst.Items(LocPos).Selected = False
If LocPos = 0 Then
lblrecord.Text = 1 & " OF " & TotalPos + 1
Else
LocPos = LocPos - 1
lblrecord.Text = LocPos + 1 & " / " & TotalPos + 1
TransferData2txt(Me, "ProductInfo", LocPos, "TextBox1", "TextBox2", "TextBox3", "TextBox4")
Lst.Items(LocPos).Selected = True
End If
End Sub
Private Sub CmdNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CmdNew.Click
TextBox1.Text = IDcreator("ProductInfo", "Pro", "-", "0000", "ProID")
TextBox2.Clear() : TextBox3.Clear() : TextBox4.Clear()
End Sub
Private Sub CmdSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CmdSave.Click
If TextBox3.Text = "" Or IsNumeric(TextBox3.Text) = False Then
ErrorProvider1.SetError(TextBox3, "Invalid Data ! Please Check Your Data")
ElseIf TextBox4.Text = "" Or IsNumeric(TextBox4.Text) = False Then
ErrorProvider2.SetError(TextBox4, "Invalid Data ! Please Check Your Data")
Else
AddNewRecord("Productinfo", TextBox1.Text, TextBox2.Text, TextBox3.Text, TextBox4.Text)
showDataTolst("ProductInfo", Me.Lst, Optionshow.SHowAllFields)
End If
End Sub
Private Sub CmdDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CmdDelete.Click
DeleteData("ProductInfo", "Proid", TextBox1.Text, CustomDel.DelCustom)
showDataTolst("ProductInfo", Me.Lst, Optionshow.SHowAllFields)
End Sub
Private Sub CmdUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CmdUpdate.Click
UpDateDataToTable("ProductInfo", "Proid", TextBox1.Text, TextBox1.Text, TextBox2.Text, TextBox3.Text, TextBox4.Text)
showDataTolst("ProductInfo", Me.Lst, Optionshow.SHowAllFields)
End Sub
Private Sub Lst_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Lst.SelectedIndexChanged
Dim i%
i = Lst.FocusedItem.Index
'MsgBox(Lst.Items(i).ToString)
TransferData2txt(Me, "ProductInfo", i, "TextBox1", "TextBox2", "TextBox3", "TextBox4")
End Sub
That All From
Anatha Man bye bye..
E-mail address removed by Moderator
if my code does not work well pls contact my Email