CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2013
    Posts
    5

    Connecting to a Database.

    I am fairly new to VB.NET.
    I am struggling to to get my head around
    how to connect to a database and update the fields once a button is
    clicked.

    I am using Visual Studio 2010 and Access 2010.

    Any help would be much appreciated.

    Below is the current code I have

    Code:
     Dim conn As New System.Data.OleDb.OleDbConnection()
            Dim cmd As New System.Data.OleDb.OleDbCommand()
    
            conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" & _
                "C:\Users\Jonathan\Desktop\BeechSports.accdb"
            cmd.CommandText = "INSERT INTO Register (First_Name)" & _
                      " Values (" & Me.txtFirstName.Text & "')"
            Try
                conn.Open()
                cmd.ExecuteNonQuery()
            Catch ex As Exception
                MessageBox.Show("Failed to connect to data source: " & vbLf & ex.Message)
            Finally
                conn.Close()
            End Try

  2. #2
    Join Date
    Jul 2008
    Location
    WV
    Posts
    5,362

    Re: Connecting to a Database.

    Jet provider is for Access databases 2003 and earlier *.mdb files. For newer Access databases you need to use the ACE provider.
    Always use [code][/code] tags when posting code.

  3. #3
    Join Date
    Mar 2013
    Posts
    5

    Re: Connecting to a Database.

    Quote Originally Posted by DataMiser View Post
    Jet provider is for Access databases 2003 and earlier *.mdb files. For newer Access databases you need to use the ACE provider.
    Thanks for your relpy. This is the code I have now, but still joy.

    Code:
            Dim conn As New System.Data.OleDb.OleDbConnection()
            Dim cmd As New System.Data.OleDb.OleDbCommand()
    
            conn.ConnectionString = " Provider=Microsoft.ACE.OLEDB.12.0;Data Source= C:\Users\Jonathan\Desktop\BeechSports.accdb;Persist Security Info=False;"
            cmd.CommandText = "INSERT INTO Register (First_Name)" & _
                      " Values (" & Me.txtFirstName.Text & "')"
            Try
                conn.Open()
                cmd.ExecuteNonQuery()
            Catch ex As Exception
                ' MessageBox.Show("Failed to connect to data source: " & vbLf & ex.Message)
            Finally
                conn.Close()
            End Try

  4. #4
    Join Date
    Jul 2008
    Location
    WV
    Posts
    5,362

    Re: Connecting to a Database.

    Well for one thing you should not have the database on the desktop, I'm not sure if this allows write access or not.

    You should tell us what problem you are having. Are you getting an error, if so what is the error message.
    Always use [code][/code] tags when posting code.

  5. #5
    Join Date
    Mar 2013
    Posts
    5

    Re: Connecting to a Database.

    Quote Originally Posted by DataMiser View Post
    Well for one thing you should not have the database on the desktop, I'm not sure if this allows write access or not.

    You should tell us what problem you are having. Are you getting an error, if so what is the error message.

    This is the code I have now, when I first used it wrote the data to the database, but when I tried
    adding other fields to update it wouldnt work. So I went back to my original code and now get the error
    'operation must be an updateable query'


    Code:
            Dim conn As New System.Data.OleDb.OleDbConnection()
            Dim cmd As New System.Data.OleDb.OleDbCommand()
    
            conn.ConnectionString = " Provider=Microsoft.ACE.OLEDB.12.0;Data Source= C:\BeechSports.accdb;Persist Security Info=False;"
            cmd.CommandText = "INSERT INTO Register (First_Name)" & _
                      " Values ('" & Me.txtFirstName.Text & "')"
            Try
                cmd.Connection = conn
                conn.Open()
                cmd.ExecuteNonQuery()
            Catch ex As Exception
                '  MessageBox.Show("Failed to connect to data source: " & vbLf & ex.Message)
                MsgBox(ex.Message)
            Finally
                conn.Close()
            End Try

  6. #6
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Hanover Germany
    Posts
    20,395

    Re: Connecting to a Database.

    Quote Originally Posted by jammyb View Post
    Thanks for your relpy. This is the code I have now, but still joy.

    Code:
            Dim conn As New System.Data.OleDb.OleDbConnection()
            Dim cmd As New System.Data.OleDb.OleDbCommand()
    
            conn.ConnectionString = " Provider=Microsoft.ACE.OLEDB.12.0;Data Source= C:\Users\Jonathan\Desktop\BeechSports.accdb;Persist Security Info=False;"
            cmd.CommandText = "INSERT INTO Register (First_Name)" & _
                      " Values (" & Me.txtFirstName.Text & "')"
            Try
                conn.Open()
                cmd.ExecuteNonQuery()
            Catch ex As Exception
                ' MessageBox.Show("Failed to connect to data source: " & vbLf & ex.Message)
            Finally
                conn.Close()
            End Try
    What exatctly causes the exception:
    Code:
                conn.Open()
    or
    Code:
                cmd.ExecuteNonQuery()
    What message do you get?
    Victor Nijegorodov

  7. #7
    Join Date
    Mar 2013
    Posts
    5

    Re: Connecting to a Database.

    Code:
            Dim conn As New System.Data.OleDb.OleDbConnection()
            Dim cmd As New System.Data.OleDb.OleDbCommand()
    
            conn.ConnectionString = " Provider=Microsoft.ACE.OLEDB.12.0;Data Source= G:\BeechSportsDB.accdb;Persist Security Info=False;"
            cmd.CommandText = "INSERT INTO Register(Username, First_Name, Surname, Addres_Line1, Address_Line2, Town/City, Post_Code )" & _
                      " Values (' " & Me.txtUsername.Text & "', '" & Me.txtFirstName.Text & "',' " & Me.txtSurname.Text & "' , '" & Me.txtAddressLine1.Text & "', '" & Me.txtAddressLine2.Text & "', '" & Me.txtTownCity.Text & "' , '" & Me.txtPostCode.Text & "' )"
            Try
                cmd.Connection = conn
                conn.Open()
                cmd.ExecuteNonQuery()
            Catch ex As Exception
                '  MessageBox.Show("Failed to connect to data source: " & vbLf & ex.Message)
                MsgBox(ex.Message)
            Finally
                conn.Close()
            End Try
    This is the code I know have and I ma getting an error is;
    Syntax error in INSERT to statement

  8. #8
    Join Date
    Jul 2008
    Location
    WV
    Posts
    5,362

    Re: Connecting to a Database.

    Is post code a numeric field in the DB? If so then the single quotes you have around the value will cause an error but I would expect type mismatch in that case

    the other error you mention about must be updateable query I would think is because of where you have your database. Windows Vista and later restricts write access to several folders, not sure if the desktop is one of them but I would not be surprised to find that it is. This would cause that error and once you get past your current error then you will be back to that one again.

    The syntax of your statement looks ok but there is a chance that the error is due to something in the data. a single or double quote in one of your textboxes will cause such an error

    I would suggest that you use a message box or something to display the content of the command text after you have assigned it but before you try to execute it. If you don;t see the issue then post the actual content of that text once is is built and we should be able to see where the error is.
    Always use [code][/code] tags when posting code.

  9. #9
    VictorN's Avatar
    VictorN is offline Super Moderator Power Poster
    Join Date
    Jan 2003
    Location
    Hanover Germany
    Posts
    20,395

    Re: Connecting to a Database.

    Quote Originally Posted by jammyb View Post
    Syntax error in INSERT to statement
    Debug your code and see the complete INSERT statement. Then copy it and paste to MS ACCESS trying to execute with your DB. Then you will see where exactly the problem is.
    Victor Nijegorodov

  10. #10
    Join Date
    Mar 2013
    Posts
    5

    Re: Connecting to a Database.

    I appear to have sorted all my issues now.

    Thank you very much to everyone for their help and advice,
    much appreciated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  





Click Here to Expand Forum to Full Width

Featured