asp.net (web forms with VB.Net) connecting to a sql database

I'm trying to write a method in VB.net so that when I click a button it queries the database and returns all the values which match a textbox which is located next to the button. I have no idea how to do this, I assume in the onclick method for the button I will need 开发者_如何学运维to pull in the value from the textbox, connect to the database and display the results to a gridview?

Any help is greatly appreciated.

thanks :)

Marc

The two "best" options are to either use a Table Adapter or Entity Framework.

• http://msdn.microsoft.com/en-us/library/bz9tthwx(v=vs.80).aspx (Table Adapter)
• http://msdn.microsoft.com/en-us/library/bb399567.aspx (Entity Framework)

Both options will give you a GUI interface to build the connection and back end database queries. Entity Framework is the newer technology of the two. Table Adapters are probably easier to learn/understand if your unsure. (Que "easy" comments now)

I would give code examples but you'll have to understand some basics of either for them to make any sense. The basic examples in either link should be enough for what you need.

Both options will give you the ability to databind your datagrid to the results.

DISCLAIMER: This code is prone to SQL injection attacks and should not be used in a production environment. For testing only. Specifically:

strSQL = "SELECT * from Table where charindex ('" & TextBox1.Text & "', columnname) > 0 "

First in the web.config add this section that points to your database:

<connectionStrings>
<add name="ApplicationServices"
     connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\aspnetdb.mdf;User Instance=true"
     providerName="System.Data.SqlClient" />
</connectionStrings>

For the example, I've just used the standard database which is attached when you start a new vb.net web application in VisualStudio 2010.

Then in your Default.aspx, have something like this:

<asp:TextBox runat="server" ID="TextBox1"></asp:TextBox>
<asp:Button runat="server" Text="Button" ID="Button1" />

And in the code behind you could do something like this:

Imports System.Data.SqlClient

Public Class _Default Inherits System.Web.UI.Page

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Dim strSQL As String = String.Empty

    ' Define your select statement
    strSQL = "SELECT * from Table where charindex ('" & TextBox1.Text & "', columnname) > 0 "
    ' Fire up SQLConnection with a DataReader
    Using connection As New SqlConnection(ConfigurationManager.ConnectionStrings("ApplicationServices").ConnectionString)
        Dim command As New SqlCommand(strSQL, connection)
        connection.Open()
        Dim reader As SqlDataReader = command.ExecuteReader()

        While reader.Read()
            Try
                ' Do some magic with reader.GetValue()
            Catch ex As Exception
            End Try
        End While

        reader.Close()
        connection.Close()
    End Using
End Sub

End Class

Ofcourse you'd have to validate the textbox.text before placing it directly into the select statement, but this will do the trick.

The 'CharIndex' will loop through the column specified as the second parameter and check if there's a match between the column data and the textbox.text, if so it will return the row.

The reader will loop through the results and with the reader.GetValue you can retrieve the data and do your magic.

Instead of using a SQLDataReader you can of course attach it to a Databound Grid or something else...