How do I reference the content of a textbox as the search criteria in an SQL statement?

I have been playing around with the 'pubs' sample SQL database (to help me learn SQL). I have a dropdownlist called 'oAuthors' and a textbox called 'TextBox1'.

I want to be able to put the last name of an author from the 'Authors' table into TextBox1, click a button, and have this author's name data pull from the SQL table and bind to the 'oAuthors' dropdownlist----thus, it would be the  only name in the dropdownlist. The way that I am referring to TextBox1.Text in the code gives me an error that says that TextBox1.Text is not a column name in the table.

How can I reference the actual 'content' of this textbox? Thanks....

Here is my pageload code:

<script language="VB" runat="server">

Sub Page_Load(Source As Object, E As EventArgs)
  if Not Page.IsPostBack Then
    Dim oConn As SQLConnection
    Dim oComm As SQLCommand
    Dim oReader As SQLDataReader
    Dim sSQL As String
    sSQL= "SELECT au_id, au_fname + ' ' + au_lname AS AuthorName "
    sSQL+= "FROM Authors WHERE au_lname = TextBox1.Text"
    sConn= "server=(local);database=pubs;Trusted_Connection=yes"

    oConn = New SQLConnection(sConn)

    oComm = New SQLCommand(sSQL,oConn)
    oReader = oComm.ExecuteReader()

    oAuthors.DataSource = oReader
  end if
End Sub


For example, it works if I do it the following way:

"FROM Authors WHERE exists au_lname = 'Flintstone'" ..... then it will work.

However, I want the flexibility of being able to specify using only the last name....thanks....

PS:  Also, a msgbox that said "name not found" or something like that would be a nice too if the query had no matching name found....

Who is Participating?
tusharashahConnect With a Mentor Commented:
1) You can concatnate your Textbox value in your sSQL string like following:
    sSQL= "SELECT au_id, au_fname + ' ' + au_lname AS AuthorName "
    sSQL+= "FROM Authors WHERE au_lname = '" + TextBox1.Text + "'"

2) You can check HasRows property of DataReader for this
    oComm = New SQLCommand(sSQL,oConn)
    oReader = oComm.ExecuteReader()
     If dr.HasRows Then
       oAuthors.DataSource = oReader
       Label1.Text = "Name not found"
     End If
jazjefAuthor Commented:
Thanks a ton tusharashah... it works great!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.