Solved

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

Posted on 2011-03-25
6
509 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 39

Assisted Solution

by:Pratima Pharande
Pratima Pharande earned 200 total points
ID: 35214357
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
ID: 35214358
You just need to call:
cmd.ExecuteNonQuery()

Open in new window

0
 
LVL 27

Expert Comment

by:nmarun
ID: 35214488
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
ID: 35215119
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
ID: 35216741
thanks
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I recently went through the process of creating a Calendar Control of events with the basis of using a database to keep track of the dates that are selectable, one requirement was to have the selected date pop-up in a simple lightbox.  At first this…
In .NET 2.0, Microsoft introduced the Web Site.  This was the default way to create a web Project in Visual Studio 2005.  In Visual Studio 2008, the Web Application has been restored as the default web Project in Visual Studio/.NET 3.x The Web Si…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

749 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