Connecting to an Access 2003 Database from VB 2005 Express

Connecting to an Access 2003 Database from VB 2005 Express

• Michael Kennett

  hi,

  i guess this should work , i didn't try this b4 but anyway

  Dim dt as DateTime   = DateTime.Parse(txtBxDate.Text).ToOADate()

  if it didn't work you can play around the last part

  .ToLongDateString() or

  .ToShortDateString() may be also you can try to parse the textbox text and to remove the time from it , keep trying till it work

  Renee he don't use dataset he want to connect to the database manualy and this is the easest and direct way to connect to database the only thing that he is missing in his code is the command paramerters which i don't know about it too and i use the same way that he using till i find out

  hope this helps

  
  
  

• Gun

  Shak,

  I don't use datasets either.

  
  
  

• Darren8669

   

  Wait....

  You are talking to an Access database You use some Sql commands on Access databases, it's true. But I write a huge amount of Access/ADO.Net code and it doesn't look like that.

  I'm not saying what you are doing is wrong and indeed it may work but it looks really strange me. (I know there are lots of ways to go about this.)

  I use tables...

  Din row as datarow = Table.NewRow

  Public Enum as DataRecord

  RecNo
  RecordType
  Name

  End Enum

  Row(DataRecord.RecNo) = Table.Rows.Count
  Row(DataRecord.RecordType = "Cats"
  Row(DataRecord.Name) = "Phoebe"

  And update... the table appending the new row.

   

   

   

   

  
  
  

• blue3952

   Ok Shak....

   

  There's on piece of code that I hate giving away because although it's simple, It took a long time to get it right.

  My code has a dataset in it. I just never use it and although I bring tables in and out out memory all the time, I never use datasets.

  I usually go my connecting in a common, but I do have an open routine in a module.

   

  Shak,

   

  I can’t tell you the tears that have gone into this code.

  Here is the common for opening a database

   

  And I’m also enclosing the ADO.NET class I use for doing I/O. This is only the second time I’ve ever given this out. This is the heart or at least the core of KnowledgeNavigator and it’s really well tested and it really works. This is not throw it over the wall code. This code has been painfully developed and honed and run hundred of thousands of time.

   

  Please take good care of this Shak because it is my child.

   

  Keep in mind that there is everything here that you need to talk to an Access database and I LOVE them. It’s very hard to let go of this particular code.

   

  Renee

   

   

  Imports System.io

  Imports System.Net

  Imports System.Data

  Imports System.Data.OleDb

   

  Public Module Common

   

  Dim Settings As New My.MySettings
  Public adodb As New ADONET(GetConnectionString)
  Public iosub As New IOSUBS
  Public CategoriesTable As New DataTable
  Public CurrentTable As New DataTable
  Public Adapter As OleDb.OleDbDataAdapter
  Public Dataset As New DataSet
  Public con As OleDb.OleDbConnection = New OleDb.OleDbConnection(GetConnectionString)

   

  Public Function OpenDataBase(ByVal Infilespec As String) As Boolean

          'Common.OpenDatabase - Called by - Common.GetAFileToOpen

          Dim Preamble As String = My.Settings.ConStrPreamble

          Dim connstr As String = Preamble & Infilespec & ""

          Dim builder As New OleDbCommandBuilder(Adapter)

          adodb = New ADONET

          CurrentTable = New DataTable(sAppTblNames.cCategories)

          builder.QuotePrefix = "["

          builder.QuoteSuffix = "]"

          con = New OleDb.OleDbConnection(connstr)

          Try

              con.Open()

          Catch e As Exception

              MsgBox("Knowledge Navigator cannot open file: " & vbCrLf & Infilespec & vbCrLf & e.Message, _

                      MsgBoxStyle.Information, "Unable to complete request")

              Exit Function

          End Try

   

          Try

              Adapter = New OleDbDataAdapter("Select * from [" & sAppTblNames.cCategories & "] order by RecordNo", con)

          Catch e As Exception

              MsgBox(e.Message, MsgBoxStyle.Information, "Unable to complete file request")

              Exit Function

          End Try

          CategoriesTable = New DataTable(sAppTblNames.cCategories)

          Try

              Adapter.Fill(CategoriesTable)

              OpenDataBase = True

              Form1.mnuFileClose.Enabled = True

              Form1.SetFormText = Infilespec

          Catch e As Exception

              MsgBox("Knowledge Navigator cannot open file: " & vbCrLf & Infilespec & vbCrLf & e.Message, _

                      MsgBoxStyle.Information, "Unable to complete request")

              Exit Function

          End Try

      End Function

  End Module

   

   

  Imports System.IO

  Imports System.Data

  Imports System.Data.OleDb

  Imports System.Data.Common

  Public Class ADONET

      '05/01/06 Fix bug in Gettable to allow table Renames

      'This class is a class of I/O routines that knows nothing about the application. It knows only about I/O.

      'It can be considered the bottom layer. All through the code, this class is instantiated as instance adodb

      Public Structure KeyDefs

          Private f As Byte

          Const cPrimaryKey As Integer = 0

          Const cSelectCategoryTable As Integer = 1

      End Structure

      Public Sub New()

      End Sub

      Public Sub New(ByVal ConnectionString As String)

   

          Try

              Adapter = New OleDbDataAdapter(ConnectionString, con)

          Catch e As Exception

              MsgBox("Error connecting to database. Exception was: " & vbCrLf & e.Message _

                          & vbCrLf & "Con contents are: " & vbCrLf & con.ConnectionString & vbCrLf & _

                          "The connection string was: " & ConnectionString & MsgBoxStyle.Information, "ADONET.New")

          End Try

        

          Form1.SetFormText() = ConnectionString

      End Sub

      Public Function PersistTable(ByVal Table As DataTable, Optional ByVal NewRow As DataRow = Nothing) As Boolean

          ' adodb.PersistTable - called by any routine needing to make permanent changes in a table.

          ' Usually this would be midlevel procedures in IOSUBS

          ' PersistTable has an optional argument for appending a new row on a table.

   

          Adapter.SelectCommand = New OleDbCommand("Select * from [" & Table.TableName & "]", con)

          Dim builder As New OleDbCommandBuilder(Adapter)

          builder.QuotePrefix = "["

          builder.QuoteSuffix = "]"

   

          If NewRow IsNot Nothing Then

              NewRow.Item(DataRecords.ciRecordNo) = Table.Rows.Count

              Table.Rows.Add(NewRow)

          End If

   

          Try

              Adapter.Update(Table)

          Catch e As Exception

              'Throw e

              MsgBox("Error persisting Table: " + Table.TableName + vbCrLf + "Exception was: " + e.Message, _

                      MsgBoxStyle.Information, "ADONET.PersistTable")

          End Try

      End Function

      Public Function GetTable(ByVal tableName As String, Optional ByVal Addtable As Boolean = True) As DataTable

   

          ' ADONET.GetTable - Called by: "Everywhere".

          ' Function: Given a Table name string; fill and return a datatable from an adapter

   

          GetTable = Nothing

   

          For Each table As DataTable In Dataset.Tables

              If tableName = table.TableName Then

                  Return table

              End If

          Next

   

          Using adapter As New OleDbDataAdapter("Select * from [" & tableName & "] order by RecordNo", con)

              Dim result As New DataTable(tableName)

              Try

                  adapter.Fill(result)

                  If Addtable Then Dataset.Tables.Add(result)

                  Return result

              Catch e As Exception

                  'If System.Diagnostics.Debugger.IsAttached() Then Throw e ' This is to invite making this visible on the call stack

                  If e.Message.Contains(cDatabaseAlreadyOpened) Then

                      Dim Settings As New My.MySettings

                      MsgBox("The database file: " & Settings.DatabasePath & Settings.DatabaseName & vbCrLf & _

                              cDatabaseAlreadyOpened & " or user program.", MsgBoxStyle.Exclamation, "Closing Database....."

                  Return Nothing

                  End If

                  MsgBox("Error selecting Table: " + tableName + vbCrLf + "Exception was: " + e.Message _

                          & vbCrLf & "The connection string is: " & vbCrLf & con.ConnectionString, _

                          MsgBoxStyle.Information, "ADONET.GetTable")

                  result.Dispose()

                  Return Nothing

              End Try

          End Using

      End Function

  End Class

   

   

  
  
  

• Luc1an

  hi,

  i don't see anything wrong in particular may be its the date field need to be wrapped with ('' date'') note those are 2 single quote b4 and 2 after, not double quotes

  thats if you are sure from the table name , column names... etc

  hope this helps

  
  
  

• rhs_fss

  Shak... I think it was me who told you about Access Databases and reserved names. I certainly bit me enough. But there is a whole other way to go about this.

  I assume the table name is Date_T

  Dim row as DataRow = Date_T.NewRow
  Row("date") = maskedTextBox1.Text
  Row("name") = textBox1.Text
  Row("value")=textBox2.Text
  row.Table.Rows.Add(row)
  Adapter.update

  See shak, it doesn't look at all the same. But this user will be more familiar with this and
  My guess is that this will be faster than what you wrote.

  
  
  

• Michael1138

  hi, Mikeee

  2 hourse and i keep trying in this and i don't know where is the error till i remembered oneday someone was talking about access has preserved words so i have tried to change the field names and it worked , but i had learned how to add parameters to the command text this is the new thing to me

  so you shouldn't name the field date because date is a preserved word , i never counter this problem b4 because i name my fields in different way "fieldName_TableName" not complete table name but a letter or more that helps me with relational tables like id customerID in customers , orders will be like that customerID_cst , customerID_ord you can also user preserved words like this [date] i'm not sure but if i recall well anway here is the code

  Namespace WindowsApplication1 Imports System Imports System.Data Imports System.Data.OleDb Imports System.Windows.Forms Public Class Form1 Inherits Form Private Sub button1_Click(ByVal sender As Object, ByVal e As EventArgs) Dim connStr As String = "provider=Microsoft.jet.oledb.4.0;data source=C:\Documents and Settings\Kakamo\Desktop\dateTesting.mdb"& _ "" Dim conn As OleDbConnection = New OleDbConnection(connStr) Dim dbcomm As OleDbCommand = New OleDbCommand Try conn.Open dbcomm.CommandType = CommandType.Text dbcomm.CommandText = "INSERT INTO dateT(Date_T, Name_T,Value_T) VALUES(@date,@name,@value)" dbcomm.Parameters.Add("date", OleDbType.Date).Value = maskedTextBox1.Text dbcomm.Parameters.Add("name", OleDbType.VarChar).Value = textBox1.Text dbcomm.Parameters.Add("value", OleDbType.Numeric).Value = textBox2.Text dbcomm.Connection = conn Dim RowsAffected As Integer = dbcomm.ExecuteNonQuery MessageBox.Show(RowsAffected.ToString) Catch ex As InvalidOperationException MessageBox.Show(ex.Message) Catch ex As OleDbException MessageBox.Show(ex.Message) Finally MessageBox.Show(dbcomm.CommandText) conn.Close End Try End Sub End Class End Namespace

  hope this helps

  
  
  

• Intelligence

  hi,

  relly i don't know what's the problem , but you can remove all the textboxs.text from sqlstring and to write manual values to see where is the problem, if that didn't work try to check the column names ... etc till it work

  hope this helps

  
  
  

• tash12457769

  I just wished I'd looked carefully at this:

  System.Data.OleDb.OleDbException: Syntax error in INSERT INTO statement.

  The error message is awfully misleading. What it really means is that a database column name conflicts with a reserved word.

  
  
  

• Phalanx

  Hello shakalama & ReneeC !

  I found the answer the same time you did! I renamed the "Date" column in the database to "eventdate" and it works now!

  I only wish I had found it before you spent all that time. Your efforts are very,very,very much appreciated!

  
  

• PSuo

  hi,

  you are welcome Mikee

  Renee about Access reserved words it was in C# forums not here i searched for this thread to find out where did i see this http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=182255&SiteID=1

  anway Renee doesn't the code that you pose looks inserting a row in dataset.table

  if its not and deal directly with the database for sure i'm missing part from it would you complete this code plz i want to know how i can connect to database like this

  thx

  
  
  

• tequila63

  Hi shakalama, thanks for the reply.

  I tried to wrap ''Date'' as you suggested, it still doesn't work. Any other suggestions

  Mikeeeee

  
  

• Shaun Hill

  Hi shakalama!

  I pinpointed the problem. It is the "Date" reference! If I remove it, it works!.

  The reason is this (I think) . "MaskedTextBox1.Text" gets its value from a "DateTimePicker" that has it's format property set to "Short". In the Database I'm trying to send this value to, it's data type is "Date/Time". I think this is where my problem lies. If I remove the "Date" reference from the sql string (along with"MaskedTextBox1.Text"), it works.

  Can you now help me to get my "Date" values over to the DataBase

  Here is the sql sting again:

  sqlStr = "INSERT INTO FsrTable (Date,Bks,Broch,Hrs,Mins,RVs,Nbs) Values ( " & MaskedTextBox1.Text & ",'" & TextBox1.Text & "','" & _

  TextBox11.Text & "','" & TextBox31.Text & "','" & TextBox41.Text & "','" & TextBox51.Text & "','" & TextBox66.Text & "')"

  Mikeeeee

  
  

• moviejunkie

  Hi !

  Well, I 'm not sure how to apply your suggestion Where would it Go

  Do I place it at the top of the Buttons Click event and then substitute "dt" for "MaskedTextBox1.Text" in the sql string (I've tried this)

  I've been also trying to convert "MaskedTextBox1.Text"'s data type to a string, a number, Date e.t.c. but nothing works. Is there something I should Know about the Syntax of the Sql string I should know

  I dont know why this works for "Text" values for a regular "TextBox" but not for a "MaskedTextBox"

  Can you look at the sql string I posted earlier and see if there might be somthing wrong with the syntax

  Mikeeee

  
  
• Connecting to an Access 2003 Database from VB 2005 Express