Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2007-04-03
2
183 Views
Last Modified: 2010-03-19
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)
    oConn.Open()

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

    oAuthors.DataSource = oReader
    oAuthors.DataBind()
   
  end if
End Sub

</script>



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....

0
Comment
Question by:jazjef
2 Comments
 
LVL 18

Accepted Solution

by:
tusharashah earned 500 total points
ID: 18844884
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
       oAuthors.DataBind()
     Else
       Label1.Text = "Name not found"
     End If
0
 
LVL 4

Author Comment

by:jazjef
ID: 18845358
Thanks a ton tusharashah... it works great!

0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

A quick way to get a menu to work on our website, is using the Menu control and assign it to a web.sitemap using SiteMapDataSource. Example of web.sitemap file: (CODE) Sample code to add to the page menu: (CODE) Running the application, we wi…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
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 …
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

808 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