How to add data in ms access 2010 using VB.net 2010

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..

Re: How to add data in ms access 2010 using VB.net 2010

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 Sub

Let me know if you run into any problems. Sorry have not tested it.

Regards

David

Re: How to add data in ms access 2010 using VB.net 2010

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..

Re: How to add data in ms access 2010 using VB.net 2010

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

Re: How to add data in ms access 2010 using VB.net 2010

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

Re: How to add data in ms access 2010 using VB.net 2010

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

Re: How to add data in ms access 2010 using VB.net 2010

PhapaCY
Answer was error because of reserved keyword

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

Re: How to add data in ms access 2010 using VB.net 2010

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