ASP.net execute SQL statement based on a connection string

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

Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Carl TawnConnect With a Mentor Systems and Integration DeveloperCommented:
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
 
Carl TawnSystems and Integration DeveloperCommented:
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
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Hi

I pasted that into my VB button click event but there are a number of errors. I am new to ASP.net
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Carl TawnSystems and Integration DeveloperCommented:
Ok, post the errors and we can walk you through them.
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
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
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
That's because I wasn't thinking straight. Thanks for that
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
thanks a lot!!
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
0
All Courses

From novice to tech pro — start learning today.