Solved

ASP.net execute SQL statement based on a connection string

Posted on 2010-11-22
8
585 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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to access a DataBinder.Eval variable using C# ? 4 27
ADO.NET ENTITY DATA MODEL 3 32
Two different visual studio versions 3 23
Adware on IIS hosted asp.net website 1 17
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 …
User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

896 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

18 Experts available now in Live!

Get 1:1 Help Now