473,320 Members | 2,094 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

VB as Front end and SQL as database

19
How to code in VB to get connected with SQL database and to perform delete function in one of the tables
Aug 8 '07 #1
4 2361
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
Aug 9 '07 #2
hariharanmca
1,977 1GB
How to code in VB to get connected with SQL database and to perform delete function in one of the tables
But still you did not mention your versions?
Aug 9 '07 #3
But still you did not mention your versions?
sorry i am in the work and my company they don't use VB6, They use VB2005
and VB6 in own computer at home.
if i free time i will answer this question in VB6 as soon as possible na..
bye bye..
Aug 9 '07 #4
BSB
19
sorry i am in the work and my company they don't use VB6, They use VB2005
and VB6 in own computer at home.
if i free time i will answer this question in VB6 as soon as possible na..
bye bye..

thank you...
i got it run
Aug 9 '07 #5

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Matt | last post by:
I always heard people saying IIS ASP front end, and MS-SQL back end. ASP is for server side programming and dynamic content generation, how could it is called front end? Because I thought it is...
7
by: rickcheney | last post by:
I just changed my Access 2002 database to a SQL Server ADP project. I had a form where the user entered a value into a text box and when a command button on the form was clicked a Report was...
3
by: Bob Hynes | last post by:
Hi All, Does anyone know of a place where a corrupted mdb(front-end) can be sent and have that place be able to tell me what got corrupted within the db? Here's the issue; All pc's are WindowsNT...
7
by: http://vmdd.tech.mylinuxisp.com/catalog/ | last post by:
After having seen my co-worker reinventing the wheel when he busily labored to build MS Access framework, I want to share my perspective. Having experienced with Access, Visual Basic, Visual...
1
by: Julia | last post by:
Hello there. I have a question somewhat related to this topic, and I don't know where else to go. I hope somebody can help. I've created a database in access, that I'd like to share with less...
7
by: dad | last post by:
REQ: any utilities for designing front-ends for databases
3
by: rdemyan via AccessMonster.com | last post by:
I'm thinking about providing a relatively easy method for users to restore the front end from a backup. The purpose is to allow for restoring if the front end becomes corrupt. Here are some...
5
by: rdemyan via AccessMonster.com | last post by:
I have code in my front end that opens a form for backing up the front end. I'll give a brief description of what the form does: 1) When the backup form opens, it closes all open forms except for...
5
by: The alMIGHTY N | last post by:
Hi all, Let's say I have a simple math formula: sum (x * y / 1000) / (sum z / 1000) I have to do this across 50 items, each with an x, y and z value, when the page first loads AND when a...
4
by: Nano | last post by:
Hey, I have been studying and working on databases for quite a time now. I know quite a lot about databases. Now I have been assigned a project where I have to build an application using the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.