In another post of mine in this forum, I was able to resolve a search issue when using a data adapter.
SqlDataAdapter da = new SqlDataAdapter("SELECT BusinessID, UserName, Password FROM Business WHERE UserName = '" + txtUserName.Text + "' AND Password = '" + txtPassword.Text + "'", con);
This worked great until an error was encountered when a user's name (txtUserName.text) contained an apostrophe (ex: O'Smith). The error generated read "Incorrect syntax near 'Smith'. Unclosed quotation mark after character string".
I could request that users not use apostrophes, but that's not to realistic (given 800+ users).
SqlDataAdapter da = new SqlDataAdapter("SELECT BusinessID, UserName, Password FROM Business WHERE UserName = '" + txtUserName.Text.Replace("'","''") + "' AND Password = '" + txtPassword.Text + "'", con);
"Marking as Answer" on right answers helps others
RkChaudary
blog
Both "replace" functions worked just fine (I'm assuming that the one with the apostrophe, replaces just that punctuation and the back slash refers to all punctuations.. I'll to research this a bit futher to get a better understanding).
Need now to figure out how to do the parameterized query to run this.
I hope these links help: http://www.functionx.com/aspnet_csharp/articles/parameterized.htm & http://www.informit.com/articles/article.aspx?p=22676. Let me just add that there are many ways & overloads to use when adding parameters. If you google/bing some more,
in sure you'll find other examples. Or even search these forums or MSDN.
Need now to figure out how to do the parameterized query to run this.
using(SqlDataAdapter da = new SqlDataAdapter("SELECT BusinessID, UserName, Password FROM Business WHERE UserName =@username", con))
{
da.SelectCommand.Parameters.AddWithValue("@username",yourvalue.Replace("'","''"));
da.Fill(DataTable);
}
Rick Knight
Member
4 Points
19 Posts
Handling "Apostrophe's" in dataset search
Feb 15, 2012 11:06 AM|LINK
In another post of mine in this forum, I was able to resolve a search issue when using a data adapter.
SqlDataAdapter da = new SqlDataAdapter("SELECT BusinessID, UserName, Password FROM Business WHERE UserName = '" + txtUserName.Text + "' AND Password = '" + txtPassword.Text + "'", con);
This worked great until an error was encountered when a user's name (txtUserName.text) contained an apostrophe (ex: O'Smith). The error generated read "Incorrect syntax near 'Smith'. Unclosed quotation mark after character string".
I could request that users not use apostrophes, but that's not to realistic (given 800+ users).
What would be the best method to handle this?
Rick
rkchaudary
Contributor
2524 Points
545 Posts
Re: Handling "Apostrophe's" in dataset search
Feb 15, 2012 11:08 AM|LINK
add the Replace like following
SqlDataAdapter da = new SqlDataAdapter("SELECT BusinessID, UserName, Password FROM Business WHERE UserName = '" + txtUserName.Text.Replace("'","''") + "' AND Password = '" + txtPassword.Text + "'", con);
RkChaudary
blog
MetalAsp.Net
All-Star
112699 Points
18359 Posts
Moderator
Re: Handling "Apostrophe's" in dataset search
Feb 15, 2012 11:10 AM|LINK
Use parameterized queries and this problem won't occur. Or double any single quotes, as suggested by RkChaudary. I prefer to use parameters.
avinash_bhud...
Contributor
2881 Points
517 Posts
Re: Handling "Apostrophe's" in dataset search
Feb 15, 2012 11:14 AM|LINK
Escape them with backslashes.
txtUserName.Text.Replace("'","\'");
Hope this helps.
R Knight
Member
31 Points
46 Posts
Re: Handling "Apostrophe's" in dataset search
Feb 15, 2012 03:57 PM|LINK
Thanks to all.
Both "replace" functions worked just fine (I'm assuming that the one with the apostrophe, replaces just that punctuation and the back slash refers to all punctuations.. I'll to research this a bit futher to get a better understanding).
Need now to figure out how to do the parameterized query to run this.
Rick
MetalAsp.Net
All-Star
112699 Points
18359 Posts
Moderator
Re: Handling "Apostrophe's" in dataset search
Feb 15, 2012 04:39 PM|LINK
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Handling "Apostrophe's" in dataset search
Feb 17, 2012 01:24 AM|LINK
using(SqlDataAdapter da = new SqlDataAdapter("SELECT BusinessID, UserName, Password FROM Business WHERE UserName =@username", con)) { da.SelectCommand.Parameters.AddWithValue("@username",yourvalue.Replace("'","''")); da.Fill(DataTable); }