Problem in Query
-
I am making a small program. Actually on my form there are two comboboxes. From these two comboboxes, user specify the search criteria. In first combobox, there are alphabets from A-Z. In second combobox, there are selection criteria like "search By Author", "search by publisher" etc. I just show data in a datagird. I use the following code. ''' This code is used for concatenation % sign to use in like operator''''''' Dim var1 var1 = Me.ComboAlphabets.Text() var1 = var1 & "%" MsgBox(var1) ''''Gettiong values of combobox''''''''''''' Dim searchby searchby = Me.ComboSearch.Text() MsgBox(searchby) Dim da As New SqlClient.SqlDataAdapter("SELECT * from books where '" & Me.ComboSearch.text & "' like '" & var1 & "' ", cn) Dim ds As New DataSet If da.Fill(ds, "books") = Nothing Then MsgBox("No Record Found") Me.ViewBookGrid.Hide() Else ds.Clear() Me.ViewBookGrid.Show() Try da.Fill(ds, searchby) Me.ViewBookGrid.DataSource = ds.Tables(searchby) Catch ex As Exception MsgBox(ex.Message) End Try MsgBox("Records Found") End If Everything is run fine , but there is a problem in select query after the where clause. Actually i want to select those records from database which user has mentioned from combobox. Like if user select "Publisher name" from combo box, then it select record from "publishername" column only. I use the query, it works 100% fine 'Dim da As New SqlClient.SqlDataAdapter("SELECT * from books where publihsername like '" & var1 & "' ", cn) At "publishername", i want to mention combosearch.text values. Means if i select "Publishername" from combobox, then my query becomes. 'Dim da As New SqlClient.SqlDataAdapter("SELECT * from books where publihsername like '" & var1 & "' ", cn) If i select "AuthorName", then my query becomes 'Dim da As New SqlClient.SqlDataAdapter("SELECT * from books where authorname like '" & var1 & "' ", cn) I try a lot and use this query Dim da As New SqlClient.SqlDataAdapter("SELECT * from books where '" & Me.ComboSearch.Text & "' like '" & var1 & "' ", cn) But this query cannot fetch correct result. If i exclude me.combosearch.text from query and replace this with "publishername" or "authorname", then it
-
I am making a small program. Actually on my form there are two comboboxes. From these two comboboxes, user specify the search criteria. In first combobox, there are alphabets from A-Z. In second combobox, there are selection criteria like "search By Author", "search by publisher" etc. I just show data in a datagird. I use the following code. ''' This code is used for concatenation % sign to use in like operator''''''' Dim var1 var1 = Me.ComboAlphabets.Text() var1 = var1 & "%" MsgBox(var1) ''''Gettiong values of combobox''''''''''''' Dim searchby searchby = Me.ComboSearch.Text() MsgBox(searchby) Dim da As New SqlClient.SqlDataAdapter("SELECT * from books where '" & Me.ComboSearch.text & "' like '" & var1 & "' ", cn) Dim ds As New DataSet If da.Fill(ds, "books") = Nothing Then MsgBox("No Record Found") Me.ViewBookGrid.Hide() Else ds.Clear() Me.ViewBookGrid.Show() Try da.Fill(ds, searchby) Me.ViewBookGrid.DataSource = ds.Tables(searchby) Catch ex As Exception MsgBox(ex.Message) End Try MsgBox("Records Found") End If Everything is run fine , but there is a problem in select query after the where clause. Actually i want to select those records from database which user has mentioned from combobox. Like if user select "Publisher name" from combo box, then it select record from "publishername" column only. I use the query, it works 100% fine 'Dim da As New SqlClient.SqlDataAdapter("SELECT * from books where publihsername like '" & var1 & "' ", cn) At "publishername", i want to mention combosearch.text values. Means if i select "Publishername" from combobox, then my query becomes. 'Dim da As New SqlClient.SqlDataAdapter("SELECT * from books where publihsername like '" & var1 & "' ", cn) If i select "AuthorName", then my query becomes 'Dim da As New SqlClient.SqlDataAdapter("SELECT * from books where authorname like '" & var1 & "' ", cn) I try a lot and use this query Dim da As New SqlClient.SqlDataAdapter("SELECT * from books where '" & Me.ComboSearch.Text & "' like '" & var1 & "' ", cn) But this query cannot fetch correct result. If i exclude me.combosearch.text from query and replace this with "publishername" or "authorname", then it
eshban284 wrote: Dim da As New SqlClient.SqlDataAdapter("SELECT * from books where '" & Me.ComboSearch.Text & "' like '" & var1 & "' ", cn) You're putting quotes around the tablename. You shouldn't. Also, LIKE doesn't do anything unless you put % on either side of the string. And finally, you're wide open for SQL injection attacks with this code. Christian Graus - Microsoft MVP - C++
-
eshban284 wrote: Dim da As New SqlClient.SqlDataAdapter("SELECT * from books where '" & Me.ComboSearch.Text & "' like '" & var1 & "' ", cn) You're putting quotes around the tablename. You shouldn't. Also, LIKE doesn't do anything unless you put % on either side of the string. And finally, you're wide open for SQL injection attacks with this code. Christian Graus - Microsoft MVP - C++
-
eshban284 wrote: Dim da As New SqlClient.SqlDataAdapter("SELECT * from books where '" & Me.ComboSearch.Text & "' like '" & var1 & "' ", cn) You're putting quotes around the tablename. You shouldn't. Also, LIKE doesn't do anything unless you put % on either side of the string. And finally, you're wide open for SQL injection attacks with this code. Christian Graus - Microsoft MVP - C++
-
Christian Graus wrote: You're putting quotes around the tablename. You shouldn't. I think you mean column name and not table name. And to clarify, you shouldn't be putting single quotes around the column name.