by Piyush Ranjan Panda (1 Submission)
Category: Databases/Data Access/DAO/ADO
Compatability: VB 6.0
Difficulty: Unknown Difficulty
Originally Published: Fri 13th May 2005
Date Added: Mon 8th February 2021
Rating: (1 Votes)
This is a simple code about fetching data from a table. This code is tested in VB6 and MSAccess.
Dim str3 As String
'Dim compr As String
'compr = Mid(txtname.Text, InStr(1, txtname.Text, "-") + 1)
Dim con As New ADODB.Connection
con.Open "CableBill"
str3 = "Delete from Customer_Details where Customer_ID = " & txtid & ""
con.BeginTrans
con.Execute str3
con.CommitTrans
con.Close
Set con = Nothing
MsgBox "Deletion successful"
End Sub
Private Sub Form_Load()
Me.Height = 7000
Me.Width = 8000
Me.Left = 1000
Me.Top = 600
txtid.Enabled = False
List1.AddItem "Name" & "-" & "ID"
List1.AddItem "-----------------"
fetch_from_database
fetch_from_database1
End Sub
Private Sub fetch_from_database1()
Set con = New ADODB.Connection
con.Open "CableBill"
Set rs = New ADODB.Recordset
str1 = "select Customer_ID,Customer_Name from Customer_Details"
rs.Open str1, con
Set rs = con.Execute(str1)
If rs.EOF <> True And rs.BOF <> True Then
Do While Not rs.EOF
List1.AddItem rs(1) & "-" & rs(0)
rs.MoveNext
Loop
rs.MoveFirst
End If
rs.Close
Set rs = Nothing
con.Close
Set con = Nothing
End Sub
Private Sub List1_DblClick()
Dim str4 As String
Dim compr As String
Set con = New ADODB.Connection
con.Open "CableBill"
Set rs = New ADODB.Recordset
compr = Mid(List1.Text, InStr(1, List1.Text, "-") + 1)
'str4 = " select * from Billing_Details where Bill_No = '" & compr & "' "
str4 = " select * from Customer_Details where Customer_ID = " & compr & ""
rs.Open str4, con
If rs.EOF <> True And rs.BOF <> True Then
Do While Not rs.EOF
txtdate.Text = rs(0)
txtid.Text = rs(1)
txtname.Text = rs(2)
txtaddress.Text = rs(3)
txtphone.Text = rs(4)
rs.MoveNext
Loop
rs.MoveFirst
Else
MsgBox "No Records Found"
'Textcustomer.Text = ""
'Textcustomer.SetFocus
End If
rs.Close
Set rs = Nothing
con.Close
Set con = Nothing
Submit.Enabled = False
End Sub
Private Sub Refresh_Click()
Unload Me
Load Me
Me.Show
End Sub
Private Sub Submit_Click()
Dim str1 As String
Dim con As New ADODB.Connection
'con.ConnectionString = "DSN=CableBill;UID=admin;PWD=;"
con.Open "CableBill"
If txtdate.Text = "" Or txtname.Text = "" Or txtaddress = "" Or txtphone = "" Then
MsgBox " Date,Customer Name,Address,Phone No Shouldn't be empty "
ElseIf txtdate.Text <> "" And txtname.Text <> "" And txtaddress.Text <> "" And txtphone.Text <> "" Then
str1 = "insert into Customer_Details(Date_Connection,Customer_Name,Address,Phone_No) Values ('" & CDate(txtdate) & "','" & txtname & "','" & txtaddress & "','" & txtphone & "')"
con.BeginTrans
con.Execute str1
con.CommitTrans
con.Close
MsgBox "Save successful"
End If
Set con = Nothing
End Sub
Private Sub Update_Click()
Dim str2 As String
Dim con As New ADODB.Connection
'Dim compr As String
'compr = Mid(txtcustomer.Text, InStr(1, txtcustomer.Text, "-") + 1)
con.Open "CableBill"
str2 = " UPDATE Customer_Details set Date_Connection = '" & CDate(txtdate.Text) & "', Customer_Name = '" & txtname.Text & "', Address = '" & txtaddress.Text & "', Phone_No = '" & txtphone.Text & "' where Customer_ID = " & txtid.Text & ""
con.BeginTrans
con.Execute str2
con.CommitTrans
con.Close
Set con = Nothing
MsgBox " Updation successful"
End Sub
Private Sub fetch_from_database()
Set con = New ADODB.Connection
con.Open "CableBill"
Set rs = New ADODB.Recordset
str1 = "select Customer_ID,Customer_Name from Customer_Details"
rs.Open str1, con
Set rs = con.Execute(str1)
If rs.EOF <> True And rs.BOF <> True Then
Do While Not rs.EOF
txtname.AddItem rs(1) & "-" & rs(0)
rs.MoveNext
Loop
rs.MoveFirst
End If
rs.Close
Set rs = Nothing
con.Close
Set con = Nothing
End Sub
No comments have been posted about This is a simple code about fetching data from a table. This code is tested in VB6 and MSAccess.. Why not be the first to post a comment about This is a simple code about fetching data from a table. This code is tested in VB6 and MSAccess..