Error In Retrieving Data From Ms Access Db

[umariyoob]

I have been searching for a way to retrieve records from my database. I was able to find and create a function specific to my program, but it wont work. Will someone advise me on how I can go about this task? I catch error in binding source and error in retrieving data from the other functions.

Thanks

Imports System.Data.OleDb
Imports System.Configuration
Public Class Form1
    Public Shared Function GetBindingSource(ByVal cmd As OleDbCommand) As BindingSource
        Dim oBindingSource As New BindingSource()
        Dim daGet As New OleDbDataAdapter(cmd)
        Dim dtGet As New DataTable()

        cmd.CommandTimeout = 240
        dtGet.Locale = System.Globalization.CultureInfo.InvariantCulture

        Try
            daGet.Fill(dtGet)
        Catch ex As Exception
            MsgBox("Error in GetBindingSource")
            Return Nothing
        End Try

        oBindingSource.DataSource = dtGet

        Return oBindingSource
    End Function

    Public Shared Sub HandleConnection(ByVal conn As OleDbConnection)
        With conn
            Select Case .State
                Case ConnectionState.Open
                    .Close()
                    .Open()
                Case ConnectionState.Closed
                    .Open()
                    Exit Select
                Case Else
                    .Close()
                    .Open()
                    Exit Select
            End Select
        End With
    End Sub

    Public Shared Function GetConnectionString() As String
        Dim strReturn As New String("")

        strReturn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Iyoob\Documents\Dbtest.accdb"

        Return strReturn

    End Function

    Public Shared Function InsertNewRecord(ByVal item1 As Integer, ByVal item2 As String, ByVal item3 As String) As Boolean
        Dim cnInsert As New OleDbConnection(GetConnectionString)
        Dim cmdInsert As New OleDbCommand
        Dim query As String = "INSERT INTO Video (ID, Title, Genre) VALUES (item1,item2,item3)"

        Dim iSqlStatus As Integer
        cmdInsert.Parameters.Clear()
        Try
            With cmdInsert
                .CommandText = query
                .CommandType = CommandType.Text
                .Parameters.AddWithValue("@ID", item1)
                .Parameters.AddWithValue("@Title", item2)
                .Parameters.AddWithValue("@Genre", item3)
                .Connection = cnInsert
            End With

            HandleConnection(cnInsert)
            iSqlStatus = cmdInsert.ExecuteNonQuery

            If Not iSqlStatus = 0 Then
                MsgBox("SQL Fail")
                Return False
            Else
                MsgBox("SQL Success")
                Return True
            End If
        Catch ex As Exception
            MsgBox("Error")
            Return False
        Finally
            HandleConnection(cnInsert)

        End Try
    End Function

    Public Shared Function GetRecords() As BindingSource

        Dim query As String = "SELECT * FROM Video"
        Dim cnGetRecords As New OleDbConnection(GetConnectionString)
        Dim cmdGetRecords As New OleDbCommand()
        Dim daGetRecords As New OleDbDataAdapter()
        Dim dsGetRecords As New DataSet()

        cmdGetRecords.Parameters.Clear()
        Try
            With cmdGetRecords
                .CommandText = query
                .CommandType = CommandType.Text
                .Connection = cnGetRecords
            End With

            HandleConnection(cnGetRecords)

            Dim oBindingSource As BindingSource = GetBindingSource(cmdGetRecords)

            If Not oBindingSource Is Nothing Then

                Return oBindingSource
            Else
                Throw New Exception("There was no BindingSource returned")
                Return Nothing
            End If
        Catch ex As Exception
            MsgBox("Error Retrieving Data")
            Return Nothing
        Finally
            HandleConnection(cnGetRecords)
        End Try

    End Function

    Public Shared Function GetVideoByID(ByVal value As Integer) As BindingSource

        Dim query As String = "SELECT value1,value2,value3 FROM Video WHERE ID = value"
        Dim cnGetRecords As New OleDbConnection(GetConnectionString)
        Dim cmdGetRecords As New OleDbCommand()
        Dim daGetRecords As New OleDbDataAdapter()
        Dim dsGetRecords As New DataSet()

        cmdGetRecords.Parameters.Clear()
        Try
            With cmdGetRecords
                .CommandText = query
                .CommandType = CommandType.Text
                .Parameters.AddWithValue("@ID", value)
                .Connection = cnGetRecords
            End With

            HandleConnection(cnGetRecords)

            Dim oBindingSource As BindingSource = GetBindingSource(cmdGetRecords)

            If Not oBindingSource Is Nothing Then
                Return oBindingSource
            Else
                Throw New Exception("There was no Binding Source returned")
                Return Nothing
            End If
        Catch ex As Exception
            MsgBox("Error Retrieving Data")
            Return Nothing
        Finally
            HandleConnection(cnGetRecords)
        End Try
    End Function

    Private Sub BtnAddVid_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnAddVid.Click

        InsertNewRecord(txtvidadd.Text, txttitleadd.Text, txtgenreadd.Text)
        'DbtestDataSetTableAdapters.VideoTableAdapter()
        'Dim cmdauto As New OleDbCommand

        'cmdauto.CommandText


        dgvData.DataSource = DirectCast(GetRecords(), BindingSource)
    End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    End Sub

    Private Sub Btn_Search_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Btn_Search.Click
        ListBoxResults.Text = GetVideoByID(TxtSrc_VidID.Text)

    End Sub

End Class

[hericles]

Do you mean you get an error in the GetBindingSource function? What error messages do you get (and I mean the ones generated by VS, not your MsgBox messages that you have made).
You mention other errors but you haven't referred to what causes them. Where/when do they appear?

[G_Waddell]

I think it is your strings you are using for your queries

e.g.

SELECT value1,value2,value3 FROM Video WHERE ID = value

Then you try and add parameters

.Parameters.AddWithValue("@ID", value)

But your select statement is just a string i.e. the database has no idea what value is. You seamed to have mixed two methods up of passing data to your SQL query.

You either want to do something like this:

dim query as string = "SELECT * FROM Video WHERE ID =" &Value 'Value is added to string but not part of it.

Or This:

Dim Query As String = "SELECT * FROM Video Where ID = @ID"
'....
Cmd.Parameters.AddWithValue("@ID", Value)