4 Replies - 686 Views - Last Post: 25 July 2012 - 07:10 PM Rate Topic: -----

#1 scrwdpoint  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 6
  • Joined: 25-July 12

Error in retrieving data from ms access db

Posted 25 July 2012 - 05:27 PM

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


[code]


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\I\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
[code]

Is This A Good Question/Topic? 0
  • +

Replies To: Error in retrieving data from ms access db

#2 sela007  Icon User is offline

  • D.I.C Addict

Reputation: 138
  • View blog
  • Posts: 838
  • Joined: 21-December 11

Re: Error in retrieving data from ms access db

Posted 25 July 2012 - 05:34 PM

Quote

I catch error in binding source and error in retrieving data from the other functions.

what error?

This post has been edited by sela007: 25 July 2012 - 05:43 PM

Was This Post Helpful? 0
  • +
  • -

#3 scrwdpoint  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 6
  • Joined: 25-July 12

Re: Error in retrieving data from ms access db

Posted 25 July 2012 - 06:21 PM

View Postsela007, on 25 July 2012 - 05:34 PM, said:

Quote

I catch error in binding source and error in retrieving data from the other functions.

what error?

My own Error Message Boxes.
Was This Post Helpful? 0
  • +
  • -

#4 sela007  Icon User is offline

  • D.I.C Addict

Reputation: 138
  • View blog
  • Posts: 838
  • Joined: 21-December 11

Re: Error in retrieving data from ms access db

Posted 25 July 2012 - 06:27 PM

that's not helping. Show exception message.

where is the error appear? in which line?
Was This Post Helpful? 0
  • +
  • -

#5 scrwdpoint  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 6
  • Joined: 25-July 12

Re: Error in retrieving data from ms access db

Posted 25 July 2012 - 07:10 PM

View Postsela007, on 25 July 2012 - 06:27 PM, said:

that's not helping. Show exception message.

where is the error appear? in which line?


A first chance exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll
A first chance exception of type 'System.Exception' occurred in Project2 VideoDB.exe
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1