Library Management System

Question

stacyann 0 Newbie Poster

14 Years Ago

Hi
I am working on a project for my course. I am creating a library system. However I am getting a lot of errors right now. Whenever it adds the record it does not add it to the database. Whenever it updates the record I got this message
Syntax error (missing operator) in query expression '((Loan Card Number = ?) AND ((? = 1 AND Name IS NULL) OR (Name = ?)) AND ((? = 1 AND Status IS NULL) OR (Status = ?)) AND ((? = 1 AND Loan Limit IS NULL) OR (Loan Limit = ?)))'. and whenever I tried to delete the record I got the same message for this field da.Update(ds, "K_S")

Imports System.Data

Public Class Borrower
    Dim inc As Integer
    Dim MaxRows As Integer
    Dim ds As New DataSet
    Dim da As OleDb.OleDbDataAdapter
    Dim sql As String
    Dim cb As New OleDb.OleDbCommandBuilder(da)
    Private Sub Borrower_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim con As New OleDb.OleDbConnection

        con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Documents and Settings\Family\Desktop\K_S.mdb"
        con.Open()
        sql = "SELECT * FROM tblBorrower"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(ds, "k_S")

        con.Close()

        MaxRows = ds.Tables("K_S").Rows.Count
        inc = -1

        ' Load first record
        If inc <> MaxRows - 1 Then
            inc = inc + 1
            NavigateRecords()
        Else
            MsgBox("No More Rows")
        End If

    End Sub

    Private Sub NavigateRecords()

        txtloancardnumber.Text = ds.Tables("K_S").Rows(inc).Item(0)
        txtname.Text = ds.Tables("K_S").Rows(inc).Item(1)
        txtstatus.Text = ds.Tables("K_S").Rows(inc).Item(2)
        txtloanlimit.Text = ds.Tables("K_S").Rows(inc).Item(3)
    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 btnPrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrevious.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 btnFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFirst.Click
        If inc <> 0 Then
            inc = 0
            NavigateRecords()
        End If
    End Sub

    Private Sub btnLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLast.Click
        If inc <> MaxRows - 1 Then
            inc = MaxRows - 1
            NavigateRecords()
        End If
    End Sub

    Private Sub btnadd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddNew.Click
        btnCommit.Enabled = True
        btnAddNew.Enabled = False
        btnUpdate.Enabled = False
        btndelete.Enabled = False

        txtloancardnumber.Clear()
        txtname.Clear()
        txtstatus.Clear()
        txtloanlimit.Clear()
    End Sub

    Private Sub btnClaear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClaear.Click
        btnCommit.Enabled = False
        btnAddNew.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("K_S").NewRow()

            dsNewRow.Item("Loan Card Number") = txtloancardnumber.Text
            dsNewRow.Item("Name") = txtname.Text
            dsNewRow.Item("Status") = txtstatus.Text
            dsNewRow.Item("Loan Limit in Days") = txtloanlimit.Text

            ds.Tables("K_S").Rows.Add(dsNewRow)


            MsgBox("New Record added to the Database")

            btnCommit.Enabled = False
            btnAddNew.Enabled = True
            btnUpdate.Enabled = True
            btndelete.Enabled = True

        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)
        Dim dsNewRow As DataRow

        dsNewRow = ds.Tables("K_S").NewRow()
        ds.Tables("K_S").Rows(inc).Item(0) = txtloancardnumber.Text
        ds.Tables("K_S").Rows(inc).Item(1) = txtname.Text
        ds.Tables("K_S").Rows(inc).Item(2) = txtstatus.Text
        ds.Tables("K_S").Rows(inc).Item(3) = txtloanlimit.Text

        da.Update(ds, "K_S")

        MsgBox("Data updated")


    End Sub

    Private Sub btndelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btndelete.Click
        If MessageBox.Show("Do you really want to Delete this Record?", "Delete", MessageBoxButtons.YesNo, _
MessageBoxIcon.Warning) = DialogResult.No Then


            MsgBox("Operation Cancelled")
            Exit Sub

        End If
        Dim cb As New OleDb.OleDbCommandBuilder(da)

        ds.Tables("K_S").Rows(inc).Delete()
        MaxRows = MaxRows - 1

        inc = 0
        NavigateRecords()
        da.Update(ds, "K_S")
    End Sub

  
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnhome.Click
        home.Show()
        Me.Hide()
    End Sub
End Class

vb.net

3 Contributors
3 Replies
335 Views
2 Days Discussion Span
Latest Post 14 Years Ago Latest Post by SolutionExpert

kvprajapati 1,826 Posting Genius

14 Years Ago

Do not instantiate the CommandBuilder in each & every event/action.

Imports System.Data

Public Class Borrower
    Dim inc As Integer
    Dim MaxRows As Integer
    Dim ds As New DataSet
    Dim da As OleDb.OleDbDataAdapter
    Dim sql As String
    Dim cb As OleDb.OleDbCommandBuilder
    Private Sub Borrower_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim con As New OleDb.OleDbConnection

        con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Documents and Settings\Family\Desktop\K_S.mdb"
      
        sql = "SELECT * FROM tblBorrower"
        da = New OleDb.OleDbDataAdapter(sql, con)

        cb=new OleDb.OleDbCommandBuilder(da)

        da.Fill(ds, "k_S")

        MaxRows = ds.Tables("K_S").Rows.Count
        inc = -1

        ' Load first record
        If inc <> MaxRows - 1 Then
            inc = inc + 1
            NavigateRecords()
        Else
            MsgBox("No More Rows")
        End If

    End Sub

    Private Sub NavigateRecords()

        txtloancardnumber.Text = ds.Tables("K_S").Rows(inc).Item(0)
        txtname.Text = ds.Tables("K_S").Rows(inc).Item(1)
        txtstatus.Text = ds.Tables("K_S").Rows(inc).Item(2)
        txtloanlimit.Text = ds.Tables("K_S").Rows(inc).Item(3)
    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 btnPrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrevious.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 btnFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFirst.Click
        If inc <> 0 Then
            inc = 0
            NavigateRecords()
        End If
    End Sub

    Private Sub btnLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLast.Click
        If inc <> MaxRows - 1 Then
            inc = MaxRows - 1
            NavigateRecords()
        End If
    End Sub

    Private Sub btnadd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddNew.Click
        btnCommit.Enabled = True
        btnAddNew.Enabled = False
        btnUpdate.Enabled = False
        btndelete.Enabled = False

        txtloancardnumber.Clear()
        txtname.Clear()
        txtstatus.Clear()
        txtloanlimit.Clear()
    End Sub

    Private Sub btnClaear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClaear.Click
        btnCommit.Enabled = False
        btnAddNew.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 dsNewRow As DataRow

            dsNewRow = ds.Tables("K_S").NewRow()

            dsNewRow.Item("Loan Card Number") = txtloancardnumber.Text
            dsNewRow.Item("Name") = txtname.Text
            dsNewRow.Item("Status") = txtstatus.Text
            dsNewRow.Item("Loan Limit in Days") = txtloanlimit.Text

            ds.Tables("K_S").Rows.Add(dsNewRow)

            da.Update(ds,"K_S") 
            MsgBox("New Record added to the Database")

            btnCommit.Enabled = False
            btnAddNew.Enabled = True
            btnUpdate.Enabled = True
            btndelete.Enabled = True

        End If
    End Sub

    Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
       
        ds.Tables("K_S").Rows(inc).Item(0) = txtloancardnumber.Text
        ds.Tables("K_S").Rows(inc).Item(1) = txtname.Text
        ds.Tables("K_S").Rows(inc).Item(2) = txtstatus.Text
        ds.Tables("K_S").Rows(inc).Item(3) = txtloanlimit.Text

        da.Update(ds, "K_S")

        MsgBox("Data updated")


    End Sub

    Private Sub btndelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btndelete.Click
        If MessageBox.Show("Do you really want to Delete this Record?", "Delete", MessageBoxButtons.YesNo, _
MessageBoxIcon.Warning) = DialogResult.No Then


            MsgBox("Operation Cancelled")
            Exit Sub

        End If
     
        ds.Tables("K_S").Rows(inc).Delete()
        MaxRows = MaxRows - 1
        da.Update(ds, "K_S") 
        inc = 0
        NavigateRecords()
       
    End Sub

  
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnhome.Click
        home.Show()
        Me.Hide()
    End Sub
End Class

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

stacyann 0 Newbie Poster · Answer 1 · 2010-04-24T07:34:39+00:00

Thank you
I would like to add a search button to the code to search for any member. How do I do that

SolutionExpert 0 Newbie Poster · Answer 2 · 2010-04-24T08:16:25+00:00

Hi
Your Database Table field name is “Name” this is not valid or Name is a keyword so,
rename that field by Pname or something.
Replace ds.Tables(“K_S”) to ds.Tables("tblBorrower")
Replace da.Update(ds, "K_S") to da.Update(ds, "tblBorrower")
Try this , your code will work.

If it works then please let me know.

Thanks,
Solution Expert