Solved

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

Posted on 2011-03-25
6
510 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

This article discusses the ASP.NET AJAX ModalPopupExtender control. In this article we will show how to use the ModalPopupExtender control, how to display/show/call the ASP.NET AJAX ModalPopupExtender control from javascript, how to show/display/cal…
Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

734 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