Hi guys Im building a program for an assignment, but this is really bugging me now been at it for hrs and still cant work it out, Ive done it on my other1 but this 1 aint having it
Basically I need to connect my prog to MS access database "project" where i have 4 tables and I am trying to connect to 1 of them "ExhibitorCompanies", Can any1 tell me where i've gone wrong please?
Here's what ive done....
Public Class Form1
Dim con As New OleDb.OleDbConnection
Dim dbProvider As String
Dim dbSource As String
Dim ds As New DataSet
Dim da As OleDb.OleDbDataAdapter
Dim sql As String
Dim inc As String
Dim MaxRows As Integer
Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs)
dsNewRow.Item("Number of Stands") = TxtNumbStands.Text
dsNewRow.Item("Price of Stands") = TxtPrice.Text
ds.Tables("ExibitorCompanies").Rows.Add(dsNewRow)
da.Update(ds, "ExibitorCompanies")
MsgBox("New Record added to the Database")
BtnCommit.Enabled = False
BtnNew.Enabled = True
btnUpdate.Enabled = True
btnDelete.Enabled = True
End If
End Sub
Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
Dim cb As New OleDb.OleDbCommandBuilder(da)
If MessageBox.Show("Please confirm you want to Delete this Record?", "Delete", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning) = Windows.Forms.DialogResult.No Then
Question
puppet_master
Hi guys Im building a program for an assignment, but this is really bugging me now been at it for hrs and still cant work it out, Ive done it on my other1 but this 1 aint having it
Basically I need to connect my prog to MS access database "project" where i have 4 tables and I am trying to connect to 1 of them "ExhibitorCompanies", Can any1 tell me where i've gone wrong please?
Here's what ive done....
Public Class Form1
Dim con As New OleDb.OleDbConnection
Dim dbProvider As String
Dim dbSource As String
Dim ds As New DataSet
Dim da As OleDb.OleDbDataAdapter
Dim sql As String
Dim inc As String
Dim MaxRows As Integer
Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs)
dbProvider = "PROVIDER=Microsoft.ace.OLEDB.12.0;"
dbSource = "Data Source = Project.accdb"
con.ConnectionString = dbProvider & dbSource
con.Open()
sql = "SELECT * FROM ExhibitorCompanies"
da = New OleDb.OleDbDataAdapter(sql, con)
da.Fill(ds, "Project")
con.Close()
MaxRows = ds.Tables("ExibitorCompanies").Rows.Count
inc = -1
End Sub
Private Sub navigateRecords()
TxtCompid.Text = ds.Tables("ExibitorCompanies").Rows(inc).Item(0)
TxtCompName.Text = ds.Tables("ExibitorCompanies").Rows(inc).Item(1)
TxtAddress.Text = ds.Tables("ExibitorCompanies").Rows(inc).Item(2)
TxtCity.Text = ds.Tables("ExibitorCompanies").Rows(inc).Item(3)
TxtTelNumb.Text = ds.Tables("ExibitorCompanies").Rows(inc).Item(4)
TxtNumbStands.Text = ds.Tables("ExibitorCompanies").Rows(inc).Item(5)
TxtPrice.Text = ds.Tables("ExibitorCompanies").Rows(inc).Item(6)
End Sub
Private Sub BtnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click
If inc <> MaxRows - 1 Then
inc = inc + 1
navigateRecords()
Else
MsgBox("No More Rows")
End If
End Sub
Private Sub BtnPrev_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnPrev.Click
If inc > 0 Then
inc = inc - 1
navigateRecords()
ElseIf inc = -1 Then
MsgBox("No Records Yet")
ElseIf inc = 0 Then
MsgBox("First Record")
End If
End Sub
Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
Dim cb As New OleDb.OleDbCommandBuilder(da)
ds.Tables("ExibitorCompanies").Rows(inc).Item(0) = TxtCompid.Text
ds.Tables("ExibitorCompanies").Rows(inc).Item(1) = TxtCompName.Text
ds.Tables("ExibitorCompanies").Rows(inc).Item(2) = TxtAddress.Text
ds.Tables("ExibitorCompanies").Rows(inc).Item(3) = TxtCity.Text
ds.Tables("ExibitorCompanies").Rows(inc).Item(4) = TxtTelNumb.Text
ds.Tables("ExibitorCompanies").Rows(inc).Item(5) = TxtNumbStands.Text
ds.Tables("ExibitorCompanies").Rows(inc).Item(6) = TxtPrice.Text
da.Update(ds, "ExibitorCompanies")
MsgBox("Data Updated")
End Sub
Private Sub btnAddNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnNew.Click
BtnCommit.Enabled = True
BtnNew.Enabled = False
BtnUpdate.Enabled = False
BtnDelete.Enabled = False
TxtCompid.Clear()
TxtCompName.Clear()
TxtAddress.Clear()
TxtCity.Clear()
TxtTelNumb.Clear()
TxtNumbStands.Clear()
TxtPrice.Clear()
End Sub
Private Sub btnClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClear.Click
BtnCommit.Enabled = False
BtnNew.Enabled = True
btnUpdate.Enabled = True
btnDelete.Enabled = True
inc = 0
navigateRecords()
End Sub
Private Sub btnCommit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCommit.Click
If inc <> -1 Then
Dim cb As New OleDb.OleDbCommandBuilder(da)
Dim dsNewRow As DataRow
dsNewRow = ds.Tables("Project").NewRow()
dsNewRow.Item("Company ID") = TxtCompid.Text
dsNewRow.Item("Company Name") = TxtCompName.Text
dsNewRow.Item("Address") = txtaddress.Text
dsNewRow.Item("City") = TxtCity.Text
dsNewRow.Item("Telephone Number") = TxtTelNumb.Text
dsNewRow.Item("Number of Stands") = TxtNumbStands.Text
dsNewRow.Item("Price of Stands") = TxtPrice.Text
ds.Tables("ExibitorCompanies").Rows.Add(dsNewRow)
da.Update(ds, "ExibitorCompanies")
MsgBox("New Record added to the Database")
BtnCommit.Enabled = False
BtnNew.Enabled = True
btnUpdate.Enabled = True
btnDelete.Enabled = True
End If
End Sub
Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
Dim cb As New OleDb.OleDbCommandBuilder(da)
If MessageBox.Show("Please confirm you want to Delete this Record?", "Delete", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning) = Windows.Forms.DialogResult.No Then
MsgBox("Operation Canceled")
Exit Sub
End If
ds.Tables("ExibitorCompanies").Rows(inc).Delete()
MaxRows = MaxRows - 1
inc = 0
navigateRecords()
da.Update(ds, "ExibitorCompanies")
End Sub
End Class
Link to comment
Share on other sites
3 answers to this question
Recommended Posts