how to get asp.net sql select statement to get its value from textbox?

S

Scotty Doesnt Know

New member
Yes. You'll have to build the SelectCommand in a string first, like this:

Dim commandText as String = "SELECT [ID], [School_name], [School_Gender] FROM [Schools] WHERE [School_name] = '" & Trim(ttbox.Text) & "'"

This will work in most cases, but it won't work if you have a single quote as part of the text in the text box. Let's say the school name is "Anna O'Brien Middle School". This will cause a problem because the single quote in the middle of the string will be the terminator of the sql School_name field, making the school name "Anna O" and then you'll have "Brien Middle School" left over to generate a syntax error.

So the best way to do this is with a Parameterized Query. Here's the way you'd do it.


SelectCommand="SELECT [ID], [School_name], [School_Gender] FROM [Schools] WHERE [School_name] = @SCHOOLNAME

SelectCommand.Parameters.Clear()
SelectCommand.Parameters.Add(New SqlParameter _
("@SCHOOLNAME",Trim(ttbox.Text)))

By doing this, SQL deals with any special characters that might be in the text box and makes sure they don't get interpreted as part of the delimiters or commands or anything else that isn't part of the data itself.

In this way, "Anna O'Brien Middle School" will work just fine.

Good luck.
 
I am building sql form using asp.net 3.5 using visual web developer express. this is my sql statement: SelectCommand="SELECT [ID], [School_name], [School_Gender] FROM [Schools] WHERE [School_name] = 'School A' "

Instead of typing School A, i want the value to be passed from a textbox that i created with an id: ttbox

<asp:TextBox ID="ttbox" runat="server" ontextchanged="TextBox1_TextChanged"></asp:TextBox>


Is it possible to do that?
 
Back
Top