Solved

ASP.net Insert data into an Access table VB.net

Posted on 2011-03-25
6
504 Views
Last Modified: 2012-05-11
Hi

In ASP.net 4 I am using the code below to try to insert data into an Access table.
What VB code do I use to execute the Insert SQL statement
Protected Sub oInsert()
        '// define a connection to the database
        Dim cs As String = ConfigurationManager.ConnectionStrings("WhatEverNameYouWant").ConnectionString
        cs = cs.Replace("App_Data\GC.accdb", Server.MapPath("App_Data\GC.accdb"))

        Dim cn As New OleDbConnection(cs)


        Dim sFirstName As String
        sFirstName = "Test1"
        Dim sLastName As String
        sLastName = "Test2"
        Dim sEmail As String
        sEmail = "Test3"

        'If sEmail <> "murbro9@yahoo.com" Then
        'Me.TextBox3.Text = "#INVALID EMAIL#"
        'Exit Sub
        'End If

        Dim sSQL As String
        sSQL = "INSERT INTO Test ( [A], [B], [C] ) SELECT '" & sFirstName & "' AS Expr1, '" & sLastName & "' AS Expr2, '" & sEmail & "' AS Expr3"

        '// define the sql statement to execute
        Dim cmd As New OleDbCommand(sSQL, cn)

        Try


            '// open the connection
            cn.Open()

            '// execute the sql statement
            'Using reader As OleDbDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)

            ' While reader.Read()
            '// this loops through all of the returned records
            'Response.Write("blah")
            'End While

            'GridView1.DataSource = reader
            'GridView1.DataBind()



            'End Using

        Catch ex As Exception
            Response.Write(ex.Message)

        Finally
            If cn.State <> ConnectionState.Closed Then
                cn.Close()
            End If
        End Try
    End Sub

Open in new window

0
Comment
Question by:murbro
6 Comments
 
LVL 39

Assisted Solution

by:Pratima Pharande
Pratima Pharande earned 200 total points
Comment Utility
you need to use

          cmd.ExecuteNonQuery()

try this code
Protected Sub oInsert()
        '// define a connection to the database
        Dim cs As String = ConfigurationManager.ConnectionStrings("WhatEverNameYouWant").ConnectionString
        cs = cs.Replace("App_Data\GC.accdb", Server.MapPath("App_Data\GC.accdb"))

        Dim cn As New OleDbConnection(cs)


        Dim sFirstName As String
        sFirstName = "Test1"
        Dim sLastName As String
        sLastName = "Test2"
        Dim sEmail As String
        sEmail = "Test3"

        'If sEmail <> "murbro9@yahoo.com" Then
        'Me.TextBox3.Text = "#INVALID EMAIL#"
        'Exit Sub
        'End If

        Dim sSQL As String
        sSQL = "INSERT INTO Test ( [A], [B], [C] ) SELECT '" & sFirstName & "' AS Expr1, '" & sLastName & "' AS Expr2, '" & sEmail & "' AS Expr3"

        '// define the sql statement to execute
        Dim cmd As New OleDbCommand(sSQL, cn)

        Try

          cmd.ExecuteNonQuery()

    
        Catch ex As Exception
            Response.Write(ex.Message)

        Finally
            If cn.State <> ConnectionState.Closed Then
                cn.Close()
            End If
        End Try
    End Sub

Open in new window

0
 
LVL 52

Accepted Solution

by:
Carl Tawn earned 300 total points
Comment Utility
You just need to call:
cmd.ExecuteNonQuery()

Open in new window

0
 
LVL 27

Expert Comment

by:nmarun
Comment Utility
If you're in need of a full tutorial regarding .net and access database, read this:

http://msdn.microsoft.com/en-us/library/ms971485.aspx

Arun
0
 
LVL 52

Expert Comment

by:Carl Tawn
Comment Utility
Well if we're being picky then pratima_mcs' solution will throw an exception because the connection is never opened.
0
 

Author Closing Comment

by:murbro
Comment Utility
thanks
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
A quick way to get a menu to work on our website, is using the Menu control and assign it to a web.sitemap using SiteMapDataSource. Example of web.sitemap file: (CODE) Sample code to add to the page menu: (CODE) Running the application, we wi…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now