Const ConnectionString As String = "Driver={Microsoft Access Driver (*.mdb)};Dbq=c:\somepath\mydb.mdb;"
Protected Sub btnInsert_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnInsert.Click
Dim conn As OdbcConnection = New OdbcConnection(ConnectionString)
Dim cmd As OdbcCommand = New OdbcCommand("INSERT INTO accdb (GName, LName, Age) VALUES (?, ?, ?)", conn)
cmd.Parameters.Add("@GName", OdbcType.VarChar, 255).Value = txtGName.Text
cmd.Parameters.Add("@LName", OdbcType.DateTime).Value = txtLName.Text
cmd.Parameters.Add("@Age", OdbcType.VarChar, 255).Value = txtAge.Text
Try
conn.Open()
cmd.ExecuteNonQuery()
Catch ex As OdbcException
Throw ex
Finally
conn.Close()
End Try
End Sub
Let me know if you run into any problems. Sorry have not tested it.
Sorry been on a training course for two days. What is the error? Have you set the connection string properly? Also, the "?" are the positional parameters for the query i.e. the values for GName, LName and Age. Leave these as is.
If you move to SQL for example, these would become named parameters e.g. @GName, @LName, @Age
When connecting to an Access database, you should better uses OLEDB instead of ODBC. But when using Access 2010 (or 2007), when you move your code to a production server (most likely a shared host?), you could run into a problem because of missing drivers.
So it's better to using MDB files, which you can create using Access 2010 also.
Most likely the reserved Word was "Password". But in your original question, you were talking about totally different fieldnames! Also you were talking about Access 2010, and now your using a mdb file.
And although you may think this works, it is very bad practice, because it is vulnarable to SQL Injections. I already gave you a link that explains this and how to avoid it:
In this article, you also see a better syntax to use, because in your example you don't close the connection which eventually will cause problems when using Access....
An example of a simple login system can be found here:
PhapaCY
Member
23 Points
8 Posts
How to add data in ms access 2010 using VB.net 2010
Feb 28, 2011 07:28 AM|LINK
Good day, I am a little bit beginner in programming regarding database and here in asp.net.
I am using MS Access 2010 and VB.net 2010 running in Win 7 x86 OS.
I want to add new data in access using vb.net, pls help me with the codes..
PROCEDURE:
I created an ms access db file with Field name Gname, Lname and age. (table name is accdb)
While in VB.net i created 3 labelbox for Given Name, Last Name and Age.
Then a 3 textbox for Given name, Last name and Age.
and a button for add. (to update the DB)
the data in textbox Given Name will be record in DB Field Gname.
the data in textbox Last Name will be record in DB Field Lname.
the data in textbox Age Name will be record in DB Field Age.
Hope some one will help with the code..
i know u have a better code than i am..
tnx in advance..
Dave_Winches...
Contributor
3051 Points
716 Posts
Re: How to add data in ms access 2010 using VB.net 2010
Mar 01, 2011 10:24 AM|LINK
Hi
Here is a quick tutorial - hope it is ok.
Const ConnectionString As String = "Driver={Microsoft Access Driver (*.mdb)};Dbq=c:\somepath\mydb.mdb;" Protected Sub btnInsert_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnInsert.Click Dim conn As OdbcConnection = New OdbcConnection(ConnectionString) Dim cmd As OdbcCommand = New OdbcCommand("INSERT INTO accdb (GName, LName, Age) VALUES (?, ?, ?)", conn) cmd.Parameters.Add("@GName", OdbcType.VarChar, 255).Value = txtGName.Text cmd.Parameters.Add("@LName", OdbcType.DateTime).Value = txtLName.Text cmd.Parameters.Add("@Age", OdbcType.VarChar, 255).Value = txtAge.Text Try conn.Open() cmd.ExecuteNonQuery() Catch ex As OdbcException Throw ex Finally conn.Close() End Try End SubLet me know if you run into any problems. Sorry have not tested it.
Regards
David
Please mark as ANSWER if this is the solution.
PhapaCY
Member
23 Points
8 Posts
Re: How to add data in ms access 2010 using VB.net 2010
Mar 03, 2011 11:49 PM|LINK
Good Day Sir David;
Thanks for your help.
Ive try the code but it gives me an error.
The error occurs after the catch ex..
and i want to ask if what was the ? mark means in the codes..
should i leave it as is or i must enter a value??.
thanks..
Cyril..
Dave_Winches...
Contributor
3051 Points
716 Posts
Re: How to add data in ms access 2010 using VB.net 2010
Mar 04, 2011 08:15 AM|LINK
Hi
Sorry been on a training course for two days. What is the error? Have you set the connection string properly? Also, the "?" are the positional parameters for the query i.e. the values for GName, LName and Age. Leave these as is.
If you move to SQL for example, these would become named parameters e.g. @GName, @LName, @Age
Can you post of code as you have it. Thanks
David
Please mark as ANSWER if this is the solution.
hans_v
All-Star
35986 Points
6550 Posts
Re: How to add data in ms access 2010 using VB.net 2010
Mar 04, 2011 09:13 AM|LINK
When connecting to an Access database, you should better uses OLEDB instead of ODBC. But when using Access 2010 (or 2007), when you move your code to a production server (most likely a shared host?), you could run into a problem because of missing drivers. So it's better to using MDB files, which you can create using Access 2010 also.
For code examples, read:
http://www.mikesdotnetting.com/Article/78/AccessDataSource-SqlDataSource-and-connecting-to-Access-databases-in-ASP.NET
http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-Access
http://www.mikesdotnetting.com/Article/76/80040E14-MS-Access-Syntax-Error-messages
But when using VB.NET 2010 (.NET 4.0), there's a much better alternative:
http://blogs.msdn.com/b/sqlservercompact/archive/2011/01/12/microsoft-sql-server-compact-4-0-is-available-for-download.aspx
PhapaCY
Member
23 Points
8 Posts
Re: How to add data in ms access 2010 using VB.net 2010
Mar 05, 2011 09:59 AM|LINK
Good Day..
Got the answer im looking for
_______________________________________________________________________________________________________________________________
Imports System.Data.OleDb
Public Class Form1
Private Sub addbtn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles addbtn.Click
Dim connection As OleDb.OleDbConnection
Dim mydb, mystr As String
mystr = ("Provider=Microsoft.JET.OLEDB.4.0;" & _
"Data Source=..\useracc.mdb")
connection = New OleDb.OleDbConnection(mystr)
connection.Open()
mydb = "INSERT INTO accdb (Uname,UPassword) values ('" & idtext.Text & "','" & passtext.Text & "')"
Dim run = New OleDb.OleDbCommand
Try
run = New OleDbCommand(mydb, connection)
run.ExecuteNonQuery()
MsgBox("connection opened")
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Critical, "Oledb Error")
End Try
End Sub
End Class
____________________________________________________________________________________________________
The error was answered..due to name reserved keyword by ms access....tnx alot
Used software:
*MS access 2010
*VB.Net 2010
*Win 7 x86 OS
hans_v
All-Star
35986 Points
6550 Posts
Re: How to add data in ms access 2010 using VB.net 2010
Mar 05, 2011 11:08 AM|LINK
Most likely the reserved Word was "Password". But in your original question, you were talking about totally different fieldnames! Also you were talking about Access 2010, and now your using a mdb file.
And although you may think this works, it is very bad practice, because it is vulnarable to SQL Injections. I already gave you a link that explains this and how to avoid it:
http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-Access
In this article, you also see a better syntax to use, because in your example you don't close the connection which eventually will cause problems when using Access....
An example of a simple login system can be found here:
http://www.mikesdotnetting.com/Article/75/Simple-Login-and-Redirect-for-ASP.NET-and-Access
But instead of reinventing the wheel. you could also use the build in membership. role and profile providers with Access ( or any other database):
http://imar.spaanjaars.com/404/using-the-microsoft-access-providers-to-replace-the-built-in-sql-server-providers
http://imar.spaanjaars.com/560/using-the-microsoft-access-providers-for-membership-roles-and-profile-under-aspnet-4
But I really think you should consider to use SQL Server Compact 4.0 instead
PhapaCY
Member
23 Points
8 Posts
Re: How to add data in ms access 2010 using VB.net 2010
Mar 05, 2011 02:02 PM|LINK
Thank you for the links...well actually what really happen was..
i was looking for a good code in which i can understand easily for a beginner..then Mr. David gave me a code and i try it...
then i look for some tutorials...then found the oledb and also try it..then it works for me...
I have a little bit long term self project that i was working on...
and as i can see theres a lot of people who can help me...tnx alot..
ill post again if i have questions...tnx...
God Bless