Ok so i have a search form that users can fill in.
On submit I put the contents of the search box into a variable with request.form and then use that variable as a placeholder in my SQL
However when i run the page and hit the search button it displays the error:
" Input string was not in a correct format."
Here is the top section of my code. I think the place holders are put in correctly. Obviously it has somethign to do with the type that i put into the variable :S
if(Request["Action1"]=="Search"){vardb=Database.Open("Blog");varuser_search_term=Request.Form["Search"];varselectQueryString=@"SELECT * FROM blog_container c , blog_content d , blog_image e WHERE( c.blog_title LIKE '%'+@0+'%' OR d.content_text LIKE '%'+@0+'%') AND c.blog_status = 'L' AND c.blog_id = d.blog_id AND d.line_no in (select min(chk.line_no) from blog_content chk where c.blog_id = chk.blog_id ) AND e.blog_image_no = c.blog_image_no";
What kind of value are you storing within your user_search_term variable?
I tried to improve the readability of your SQL string a bit, which hopefully might help a little with finding the issue and changed the areas around your LIKE statements to use a parameter rather along with the necessary LIKE syntax :
//Your Search term
var user_search_term = Request.Form["Search"];
//Your Query
string s = "SELECT * FROM blog_container c ," +
"blog_content d ," +
"blog_image e " +
"WHERE (c.blog_title LIKE @searchTerm OR d.content_text LIKE @searchTerm)" +
"AND (c.blog_status = 'L')" +
"AND (c.blog_id = d.blog_id)" +
"AND (d.line_no IN ( SELECT min(chk.line_no) " +
"FROM blog_content chk " +
"WHERE c.blog_id = chk.blog_id)" +
"AND (e.blog_image_no = c.blog_image_no)";
//Your SQL Command (For example purposes, create as you are already using)
SqlCommand cmd = new SqlCommand(s);
//Add your parameters using the Parameters.Add() method
cmd.Parameters.Add("@searchTerm", "%" + user_search_term +"%")
Thanks for that, yeah basically what ever the user inputs into the search box so im guessing string? They can enter anythign as long as its not html mark up or script.
Revising your SQLCommand to pass the values in using the Parameters.Add() method should clear up some for your issues hopefully. You can also try out this slightly different SQL to see if that makes any difference.
if(Request["Action1"] == "Search")
{
var db = Database.Open("Blog");
var user_search_term = "%"+Request.Form["Search"]+"%";
var selectQueryString = @"SELECT * FROM blog_container c ,
blog_content d ,
blog_image e
WHERE
(
c.blog_title LIKE @0 OR d.content_text LIKE @0)
AND c.blog_status = 'L'
AND c.blog_id = d.blog_id
AND d.line_no in
(select min(chk.line_no) from blog_content chk where c.blog_id = chk.blog_id )
AND e.blog_image_no = c.blog_image_no";
RRR
Marked as answer by Jamesdontnet on Feb 22, 2013 07:16 AM
How were you previously executing your SQL Statements?
By using SQLCommand (and parameters) you are passing a string containing your SQL query (as you currently have above) along with your existing connection.
//Your query
string yourQuery = "SELECT * ... WHERE YourProperty LIKE @searchTerm ...";
//A SQLCommand that will execute your query that takes in your query and a connection (which you should have)
SqlCommand cmd = new SqlCommand(yourQuery , connection);
So in the code examples above within your string when you have @searchTerm or something similar, using the Parameters.Add() method will appropriately replace those parameters with the value that you set for your value :
//This will replace any instances of @searchTerm within your query with the value in the second parameter
cmd.Parameters.Add("@searchTerm", "%" + user_search_term +"%")
I am sorry. I totally didn't realize that this was WebPages / SQL CE specific.
Something like the following should work for what you need :
if(Request["Action1"] == "Search")
{
//Your Search Term
string user_search_term = Request.Form["Search"];
//Create your Database
var db = Database.Open("Blog");
//Write your SQL Query here
string query = "SELECT * FROM ... WHERE YourProperty LIKE @0 ... ";
//Execute your query
db.Query(query, "%" + user_search_term+ "%");
}
Jamesdontnet
Member
44 Points
68 Posts
SQL " Input string was not in a correct format."
Feb 21, 2013 08:29 PM|LINK
Hi guys me again ^^.
Ok so i have a search form that users can fill in.
On submit I put the contents of the search box into a variable with request.form and then use that variable as a placeholder in my SQL
However when i run the page and hit the search button it displays the error:
" Input string was not in a correct format."
Here is the top section of my code. I think the place holders are put in correctly. Obviously it has somethign to do with the type that i put into the variable :S
Rion William...
All-Star
27374 Points
4542 Posts
Re: SQL " Input string was not in a correct format."
Feb 21, 2013 08:57 PM|LINK
What kind of value are you storing within your user_search_term variable?
I tried to improve the readability of your SQL string a bit, which hopefully might help a little with finding the issue and changed the areas around your LIKE statements to use a parameter rather along with the necessary LIKE syntax :
//Your Search term var user_search_term = Request.Form["Search"]; //Your Query string s = "SELECT * FROM blog_container c ," + "blog_content d ," + "blog_image e " + "WHERE (c.blog_title LIKE @searchTerm OR d.content_text LIKE @searchTerm)" + "AND (c.blog_status = 'L')" + "AND (c.blog_id = d.blog_id)" + "AND (d.line_no IN ( SELECT min(chk.line_no) " + "FROM blog_content chk " + "WHERE c.blog_id = chk.blog_id)" + "AND (e.blog_image_no = c.blog_image_no)"; //Your SQL Command (For example purposes, create as you are already using) SqlCommand cmd = new SqlCommand(s); //Add your parameters using the Parameters.Add() method cmd.Parameters.Add("@searchTerm", "%" + user_search_term +"%")Jamesdontnet
Member
44 Points
68 Posts
Re: SQL " Input string was not in a correct format."
Feb 21, 2013 09:00 PM|LINK
Thanks for that, yeah basically what ever the user inputs into the search box so im guessing string? They can enter anythign as long as its not html mark up or script.
Rion William...
All-Star
27374 Points
4542 Posts
Re: SQL " Input string was not in a correct format."
Feb 21, 2013 09:03 PM|LINK
Revising your SQLCommand to pass the values in using the Parameters.Add() method should clear up some for your issues hopefully. You can also try out this slightly different SQL to see if that makes any difference.
rrrsr7205
Participant
1304 Points
313 Posts
Re: SQL " Input string was not in a correct format."
Feb 21, 2013 09:35 PM|LINK
if(Request["Action1"] == "Search") { var db = Database.Open("Blog"); var user_search_term = "%"+Request.Form["Search"]+"%"; var selectQueryString = @"SELECT * FROM blog_container c , blog_content d , blog_image e WHERE ( c.blog_title LIKE @0 OR d.content_text LIKE @0) AND c.blog_status = 'L' AND c.blog_id = d.blog_id AND d.line_no in (select min(chk.line_no) from blog_content chk where c.blog_id = chk.blog_id ) AND e.blog_image_no = c.blog_image_no";Jamesdontnet
Member
44 Points
68 Posts
Re: SQL " Input string was not in a correct format."
Feb 21, 2013 10:00 PM|LINK
Mmmm im having a lot of trouble understanding sqlcommand and cmd.
GmGregori
Contributor
5448 Points
736 Posts
Re: SQL " Input string was not in a correct format."
Feb 21, 2013 10:20 PM|LINK
The SqlCommand class is a class from the System.Data.SqlClient namespace.
You could use it in WebMatrix, but it's far more tricky than using the usual Database class of the Webmatrix.Data namespace.
Look at the rrrsr7205's solution: it should work.
Rion William...
All-Star
27374 Points
4542 Posts
Re: SQL " Input string was not in a correct format."
Feb 21, 2013 11:20 PM|LINK
@jamesdotnet
How were you previously executing your SQL Statements?
By using SQLCommand (and parameters) you are passing a string containing your SQL query (as you currently have above) along with your existing connection.
So in the code examples above within your string when you have @searchTerm or something similar, using the Parameters.Add() method will appropriately replace those parameters with the value that you set for your value :
//This will replace any instances of @searchTerm within your query with the value in the second parameter cmd.Parameters.Add("@searchTerm", "%" + user_search_term +"%")rrrsr7205
Participant
1304 Points
313 Posts
Re: SQL " Input string was not in a correct format."
Feb 21, 2013 11:21 PM|LINK
Rion:
I believe the poster is in the WebPages/SQL CE arena and you are suggesting something beyond that ken
Rion William...
All-Star
27374 Points
4542 Posts
Re: SQL " Input string was not in a correct format."
Feb 21, 2013 11:25 PM|LINK
Oh!
I am sorry. I totally didn't realize that this was WebPages / SQL CE specific.
Something like the following should work for what you need :
if(Request["Action1"] == "Search") { //Your Search Term string user_search_term = Request.Form["Search"]; //Create your Database var db = Database.Open("Blog"); //Write your SQL Query here string query = "SELECT * FROM ... WHERE YourProperty LIKE @0 ... "; //Execute your query db.Query(query, "%" + user_search_term+ "%"); }