Solved

ASP.net execute SQL statement based on a connection string

Posted on 2010-11-22
8
589 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
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:murbro
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:murbro
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:murbro
ID: 34187966
That's because I wasn't thinking straight. Thanks for that
0
 

Author Closing Comment

by:murbro
ID: 34187975
thanks a lot!!
0
 

Author Comment

by:murbro
ID: 34188027
0

Featured Post

ScreenConnect 6.0 Free Trial

At ScreenConnect, partner feedback doesn't fall on deaf ears. We collected partner suggestions off of their virtual wish list and transformed them into one game-changing release: ScreenConnect 6.0. Explore all of the extras and enhancements for yourself!

Question has a verified solution.

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

I have developed many web applications with asp & asp.net and to add and use a dropdownlist was always a very simple task, but with the new asp.net, setting the value is a bit tricky and its not similar to the old traditional method. So in this a…
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…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

773 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