-
March 28th, 2013, 01:03 PM
#1
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
-
March 29th, 2013, 08:00 AM
#2
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.
-
April 3rd, 2013, 12:46 PM
#3
Re: Connecting to a Database.
Originally Posted by DataMiser
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
-
April 3rd, 2013, 02:17 PM
#4
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.
-
April 4th, 2013, 08:57 AM
#5
Re: Connecting to a Database.
Originally Posted by DataMiser
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
-
April 4th, 2013, 09:32 AM
#6
Re: Connecting to a Database.
Originally Posted by jammyb
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:or
Code:
cmd.ExecuteNonQuery()
What message do you get?
Victor Nijegorodov
-
April 4th, 2013, 10:20 AM
#7
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
-
April 4th, 2013, 10:33 AM
#8
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.
-
April 4th, 2013, 11:02 AM
#9
Re: Connecting to a Database.
Originally Posted by jammyb
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
-
April 4th, 2013, 11:12 AM
#10
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|