• Home
 · 
• Databases/Data Access/DAO/ADO
 · 
• This is a simple code about fetching data from a table. This code is tested in VB6 and MSAccess.

This is a simple code about fetching data from a table. This code is tested in VB6 and MSAccess.

This is a simple code about fetching data from a table. This code is tested in VB6 and MSAccess.

Rate This is a simple code about fetching data from a table. This code is tested in VB6 and MSAccess.

• 1
• 2
• 3
• 4
• 5

(1(1 Vote))

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