Solved

ASP.net execute SQL statement based on a connection string

Posted on 2010-11-22
8
581 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:murbro
  • 5
  • 3
8 Comments
 
LVL 52

Expert Comment

by:Carl Tawn
Comment Utility
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:murbro
Comment Utility
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
Comment Utility
Ok, post the errors and we can walk you through them.
0
 

Author Comment

by:murbro
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 52

Accepted Solution

by:
Carl Tawn earned 500 total points
Comment Utility
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:murbro
Comment Utility
That's because I wasn't thinking straight. Thanks for that
0
 

Author Closing Comment

by:murbro
Comment Utility
thanks a lot!!
0
 

Author Comment

by:murbro
Comment Utility
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Today is the age of broadband.  More and more people are going this route determined to experience the web and it’s multitude of services as quickly and painlessly as possible. Coupled with the move to broadband, people are experiencing the web via …
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…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

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

11 Experts available now in Live!

Get 1:1 Help Now