Solved

ASP.net execute SQL statement based on a connection string

Posted on 2010-11-22
8
599 Views
Last Modified: 2012-06-27
Hi

I connected to an Access database in ASP.net using the code below.
How do I then retrieve data from the database using a SQL statement
<connectionStrings>
    <clear />
    <add name="WhatEverNameYouWant" connectionString="Microsoft.ACE.OLEDB.12.0;Data Source=Database\Contacts.accdb;" />
  </connectionStrings>

Open in new window

0
Comment
Question by:Murray Brown
[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
  • 5
  • 3
8 Comments
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 34187753
A very simple example:
// define a connection to the database
OleDbConnection cn = new OleDbConnection(ConfigurationManager.ConnectionStrings["WhatEverNameYouWant"].ConnectionString)

// define the sql statement to execute
OleDbCommand cmd = new OleDbCommand("SELECT * FROM [YourTable]", cn)

try
{
     // open the connection
    cn.Open();

    // execute the sql statement
    using (OleDbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
    {
        while (reader.Read())
        {
             // this loops through all of the returned records
        }
    }
}
catch
{
}
finally
{
      // close the connection again
     if (cn.State != ConnectionState.Close)
         cn.Close();
}

Open in new window

0
 

Author Comment

by:Murray Brown
ID: 34187816
Hi

I pasted that into my VB button click event but there are a number of errors. I am new to ASP.net
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 34187832
Ok, post the errors and we can walk you through them.
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:Murray Brown
ID: 34187851
Error      1      Syntax error.      C:\Users\murbro\Documents\Visual Studio 2010\Projects\WebApplication_DotNet2\WebApplication_DotNet2\Default.aspx.vb      11      9      WebApplication_DotNet2
Error      2      'OleDbConnection' is not declared. It may be inaccessible due to its protection level.      C:\Users\murbro\Documents\Visual Studio 2010\Projects\WebApplication_DotNet2\WebApplication_DotNet2\Default.aspx.vb      12      1      WebApplication_DotNet2
Error      3      'cn' is not declared. It may be inaccessible due to its protection level.      C:\Users\murbro\Documents\Visual Studio 2010\Projects\WebApplication_DotNet2\WebApplication_DotNet2\Default.aspx.vb      12      17      WebApplication_DotNet2
Error      4      Method arguments must be enclosed in parentheses.      C:\Users\murbro\Documents\Visual Studio 2010\Projects\WebApplication_DotNet2\WebApplication_DotNet2\Default.aspx.vb      12      17      WebApplication_DotNet2
Error      5      Type 'OleDbConnection' is not defined.      C:\Users\murbro\Documents\Visual Studio 2010\Projects\WebApplication_DotNet2\WebApplication_DotNet2\Default.aspx.vb      12      26      WebApplication_DotNet2
Error      6      Identifier expected.      C:\Users\murbro\Documents\Visual Studio 2010\Projects\WebApplication_DotNet2\WebApplication_DotNet2\Default.aspx.vb      12      81      WebApplication_DotNet2
Error      7      Syntax error.      C:\Users\murbro\Documents\Visual Studio 2010\Projects\WebApplication_DotNet2\WebApplication_DotNet2\Default.aspx.vb      14      1      WebApplication_DotNet2
Error      8      'OleDbCommand' is not declared. It may be inaccessible due to its protection level.      C:\Users\murbro\Documents\Visual Studio 2010\Projects\WebApplication_DotNet2\WebApplication_DotNet2\Default.aspx.vb      15      9      WebApplication_DotNet2
Error      9      'cmd' is not declared. It may be inaccessible due to its protection level.      C:\Users\murbro\Documents\Visual Studio 2010\Projects\WebApplication_DotNet2\WebApplication_DotNet2\Default.aspx.vb      15      22      WebApplication_DotNet2
Error      10      Type 'OleDbCommand' is not defined.      C:\Users\murbro\Documents\Visual Studio 2010\Projects\WebApplication_DotNet2\WebApplication_DotNet2\Default.aspx.vb      15      32      WebApplication_DotNet2
Error      11      Syntax error.      C:\Users\murbro\Documents\Visual Studio 2010\Projects\WebApplication_DotNet2\WebApplication_DotNet2\Default.aspx.vb      18      1      WebApplication_DotNet2
Error      12      Syntax error.      C:\Users\murbro\Documents\Visual Studio 2010\Projects\WebApplication_DotNet2\WebApplication_DotNet2\Default.aspx.vb      19      6      WebApplication_DotNet2
Error      13      'cn' is not declared. It may be inaccessible due to its protection level.      C:\Users\murbro\Documents\Visual Studio 2010\Projects\WebApplication_DotNet2\WebApplication_DotNet2\Default.aspx.vb      20      5      WebApplication_DotNet2
Error      14      Character is not valid.      C:\Users\murbro\Documents\Visual Studio 2010\Projects\WebApplication_DotNet2\WebApplication_DotNet2\Default.aspx.vb      20      14      WebApplication_DotNet2
Error      15      Syntax error.      C:\Users\murbro\Documents\Visual Studio 2010\Projects\WebApplication_DotNet2\WebApplication_DotNet2\Default.aspx.vb      22      5      WebApplication_DotNet2
Error      16      'Using' must end with a matching 'End Using'.      C:\Users\murbro\Documents\Visual Studio 2010\Projects\WebApplication_DotNet2\WebApplication_DotNet2\Default.aspx.vb      23      5      WebApplication_DotNet2
Error      17      'OleDbDataReader' is not declared. It may be inaccessible due to its protection level.      C:\Users\murbro\Documents\Visual Studio 2010\Projects\WebApplication_DotNet2\WebApplication_DotNet2\Default.aspx.vb      23      12      WebApplication_DotNet2
Error      18      ')' expected.      C:\Users\murbro\Documents\Visual Studio 2010\Projects\WebApplication_DotNet2\WebApplication_DotNet2\Default.aspx.vb      23      28      WebApplication_DotNet2
Error      19      Syntax error.      C:\Users\murbro\Documents\Visual Studio 2010\Projects\WebApplication_DotNet2\WebApplication_DotNet2\Default.aspx.vb      24      5      WebApplication_DotNet2
Error      20      'While' must end with a matching 'End While'.      C:\Users\murbro\Documents\Visual Studio 2010\Projects\WebApplication_DotNet2\WebApplication_DotNet2\Default.aspx.vb      25      17      WebApplication_DotNet2
Error      21      Syntax error.      C:\Users\murbro\Documents\Visual Studio 2010\Projects\WebApplication_DotNet2\WebApplication_DotNet2\Default.aspx.vb      26      9      WebApplication_DotNet2
Error      22      Syntax error.      C:\Users\murbro\Documents\Visual Studio 2010\Projects\WebApplication_DotNet2\WebApplication_DotNet2\Default.aspx.vb      27      14      WebApplication_DotNet2
Error      23      Syntax error.      C:\Users\murbro\Documents\Visual Studio 2010\Projects\WebApplication_DotNet2\WebApplication_DotNet2\Default.aspx.vb      28      9      WebApplication_DotNet2
Error      24      Syntax error.      C:\Users\murbro\Documents\Visual Studio 2010\Projects\WebApplication_DotNet2\WebApplication_DotNet2\Default.aspx.vb      29      5      WebApplication_DotNet2
Error      25      Syntax error.      C:\Users\murbro\Documents\Visual Studio 2010\Projects\WebApplication_DotNet2\WebApplication_DotNet2\Default.aspx.vb      30      1      WebApplication_DotNet2
Error      26      Syntax error.      C:\Users\murbro\Documents\Visual Studio 2010\Projects\WebApplication_DotNet2\WebApplication_DotNet2\Default.aspx.vb      32      1      WebApplication_DotNet2
Error      27      Syntax error.      C:\Users\murbro\Documents\Visual Studio 2010\Projects\WebApplication_DotNet2\WebApplication_DotNet2\Default.aspx.vb      33      1      WebApplication_DotNet2
Error      28      'Finally' must end with a matching 'End Try'.      C:\Users\murbro\Documents\Visual Studio 2010\Projects\WebApplication_DotNet2\WebApplication_DotNet2\Default.aspx.vb      34      9      WebApplication_DotNet2
Error      29      Syntax error.      C:\Users\murbro\Documents\Visual Studio 2010\Projects\WebApplication_DotNet2\WebApplication_DotNet2\Default.aspx.vb      35      1      WebApplication_DotNet2
Error      30      Syntax error.      C:\Users\murbro\Documents\Visual Studio 2010\Projects\WebApplication_DotNet2\WebApplication_DotNet2\Default.aspx.vb      36      7      WebApplication_DotNet2
Error      31      'If' must end with a matching 'End If'.      C:\Users\murbro\Documents\Visual Studio 2010\Projects\WebApplication_DotNet2\WebApplication_DotNet2\Default.aspx.vb      37      6      WebApplication_DotNet2
Error      32      'cn' is not declared. It may be inaccessible due to its protection level.      C:\Users\murbro\Documents\Visual Studio 2010\Projects\WebApplication_DotNet2\WebApplication_DotNet2\Default.aspx.vb      37      10      WebApplication_DotNet2
Error      33      Identifier expected.      C:\Users\murbro\Documents\Visual Studio 2010\Projects\WebApplication_DotNet2\WebApplication_DotNet2\Default.aspx.vb      37      20      WebApplication_DotNet2
Error      34      'Close' is not a member of 'System.Data.ConnectionState'.      C:\Users\murbro\Documents\Visual Studio 2010\Projects\WebApplication_DotNet2\WebApplication_DotNet2\Default.aspx.vb      37      22      WebApplication_DotNet2
Error      35      'cn' is not declared. It may be inaccessible due to its protection level.      C:\Users\murbro\Documents\Visual Studio 2010\Projects\WebApplication_DotNet2\WebApplication_DotNet2\Default.aspx.vb      38      10      WebApplication_DotNet2
Error      36      Character is not valid.      C:\Users\murbro\Documents\Visual Studio 2010\Projects\WebApplication_DotNet2\WebApplication_DotNet2\Default.aspx.vb      38      20      WebApplication_DotNet2
Error      37      Syntax error.      C:\Users\murbro\Documents\Visual Studio 2010\Projects\WebApplication_DotNet2\WebApplication_DotNet2\Default.aspx.vb      39      1      WebApplication_DotNet2
0
 
LVL 52

Accepted Solution

by:
Carl Tawn earned 500 total points
ID: 34187951
Oh, ok, you didn't say you were using VB so I assumed C#. Conversion below:
'// define a connection to the database
Dim cn As New OleDbConnection(ConfigurationManager.ConnectionStrings("WhatEverNameYouWant").ConnectionString)

'// define the sql statement to execute
Dim cmd As New OleDbCommand("SELECT * FROM [YourTable]", 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
        End While

    End Using

Catch ex As Exception

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

Open in new window

0
 

Author Comment

by:Murray Brown
ID: 34187966
That's because I wasn't thinking straight. Thanks for that
0
 

Author Closing Comment

by:Murray Brown
ID: 34187975
thanks a lot!!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In an ASP.NET application, I faced some technical problems. In this article, I list them out and show the solutions that I found.  I hope it will be useful. Problem: After closing a pop-up window, the parent page should be refreshed automaticall…
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…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

627 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