Searching a database with an input field

[mana2]

I'm having a problem searching on a field in a database:

I have a two pages. The first is a form that contains an input field Product Type. The second page takes the field and runs it in a select statement.

If I hard code the value, it works:

Dim strSQL As String = "SELECT * FROM ProductType where ProductType= 'Book';"

But if I add the variable, I get an error:

Dim strSQL As String = "SELECT * FROM ProductType where ProductType= " & strProductType & ""

This is the error:
Invalid column name 'Book'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Invalid column name 'book'.


This is the complete code:

<asp:datagrid id="dgrEmployees" runat="server" />

<script language="vb" runat="server">
Sub Page_Load()

Dim strProductType As String

strProductType = Context.Items("ProductType")

Dim strSQL As String = "SELECT * FROM ProductType where ProductType= " & strProductType & ""

Dim strConnection As String = "Serverinfo;database=dbinfo; Integrated Security=True;"

Dim objDataSet As New DataSet()
Dim objConnection As New SqlConnection(strConnection)

Dim objDataAdapter As New SqlDataAdapter(strSQL, objConnection)

objDataAdapter.Fill(objDataSet, "Employees")

Dim objDataView As New DataView(objDataSet.Tables("Employees"))

dgrEmployees.DataSource = objDataView
dgrEmployees.DataBind()
End Sub


</script>


Thanks

 

[Turkbear]

Hi,
To test the string, try using a Response.Write(strSQL)
to see exactly how that string is being constructed - you may need to use 's like:
Dim strSQL As String = "SELECT * FROM ProductType where ProductType= '" & strProductType & "'"





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[ca8msm]

You are missing single quotes, so your sql ends up looking for a column e.g.

SELECT * FROM ProductType where ProductType = Book

instead of

SELECT * FROM ProductType where ProductType = 'Book'

However, at a minimum, you should read up on why you need to use sql parameters, and also how to use the debugger in Visual Studio.


Mark,

Darlington Web Design[tab]|[tab]Experts, Information, Ideas & Knowledge[tab]|[tab]ASP.NET Tips & Tricks

 

[jmeckley]

in addition to marks advice use parameterized query instead of sql injection

Jason Meckley
Programmer

faq855-7190
faq732-7259