Retreiving from a database

[DaveRook]

Hi

I have moved to c# from vbscript, and consequently from ASP.old to ASP.Net

A quick question about SQL in C#.

In VBscript, I could use the line

Select * from Cars where Model like '%" + Replace(var_TextSearchedFor,"'","''") + "%'

This would then insert the variable var_TextSearchedFor and look for that under the column Model.

I can't do this in C#. It is driving me crazy as this was one of the easier things in VBscript.

If I enter a normal fixed 'where' clause then it works fine. But if I try to swap it with a variable, it won't work.

My current line reads:

command = new SqlCommand("Select * from Cars where ModelNumber like '%" + Replace(var_TextSearchedFor, "'", "''") + "%'", connection);

I have tried removing the double quote marks (speech marks) but this still didn't help. I get so far and it tells me "System.Data.SqlClient.SqlException: Invalid column name 'var_TextSearchedFor'."

Also, should this type of question be posted here or in the SQL section? I am using SQL Server Management Studio Express.

Thanks

Dave

[Curtis Rutland]

Use this:

command = String.Format("select * from Cars where ModelNumber like '%{0}%'",var_TextSearchedFor.Replace("'","''"));
 

Assuming that var_TextSearchedFor is a String.

String.Format replaces "{0}" with the first parameter, "{1}" with the second parameter, and so forth. So {0} would become the value of var_TextSearchedFor.Replace("'","''")

In C#, the Replace method is a member of the String class, so you have to call it from the string you are using it on.

[DaveRook]

Hi insertAlias

Sadly this doesn't seem to work for me. It throws out a compiler error message: CS0029: Cannot implicitly convert type 'string' to 'System.Data.SqlClient.SqlCommand'

Please find my entire source code incase I missed something.

 
protected void Page_Load(object sender, EventArgs e)
{
string var_MountType = "%";
if (Request.QueryString["t"]!="") 
if (Request.QueryString["t"]!=null)
{
if (Request.QueryString["mt"] == "sm")
{
var_MountType = "Surface Mount";
}
if (Request.QueryString["mt"] == "th")
{
var_MountType = "Through Hole";
}
 
}
SqlConnection connection;
SqlCommand command;
SqlDataReader reader;
string connectionString = ConfigurationManager.ConnectionStrings["1mmBoardToBoard"].ConnectionString;
connection = new SqlConnection(connectionString);
command = String.Format("Select * from tb_1mm where MountType like '%{0}'", var_MountType.Replace("'", "''"));
 
 
try
{
connection.Open();
reader = command.ExecuteReader();
rpBoardToBoard.DataSource = reader;
rpBoardToBoard.DataBind();
reader.Close();
}
finally
{
connection.Close();
}
}
 
The line that starts command = string.format... used to be:
 
command = new SqlCommand("Select * from tb_1mm where MountType = 'Through Hole'", connection); 
 

This worked so I know the connection works correctly. It's just making it read the querystring to assign a variable to allow the correct SQL to be run.

Any ideas what I have done wrong?

Dave

[Curtis Rutland]

Hi insertAlias

Sadly this doesn't seem to work for me. It throws out a compiler error message: CS0029: Cannot implicitly convert type 'string' to 'System.Data.SqlClient.SqlCommand'

Yeah, sorry about that. I should have told you to try this:

string sqlCommandStr = String.Format("select * from Cars where ModelNumber like '%{0}%'",var_TextSearchedFor.Replace("'","''"));
command = new SqlCommand(sqlCommandStr);
 

I forgot that command is a SqlCommand object and not just the command string.

[DaveRook]

Hello insertAlias

Thank you for all your help. One thing was missing (this is for others who may have the same problem)

The final command needed to include the ,connection statement

eg,

command = new SqlCommand(sqlCommandString, connection);

Thank you for all your help. I really appreciate this.

Will this work if there is more than query required?

eg, can I use the code

string sqlCommandStr = String.Format("select * from Cars where ModelNumber like '%{0}%'",var_TextSearchedFor.Replace("'","''") + " and where Colour like '%{0}%'",var_TextColor.Replace("'","''"));

Dave

[Curtis Rutland]

Glad to hear that it is working. There are multiple constructors for SqlCommand; I was assuming that you were assigning the Connection value later.

As to the new question, no that won't work like that. You should look up String.Format(). It replaces the {0} with the first parameter after the string. Let me help you break down what is going on and what you want to do.

string tSearchedFor = var_TextSearchedFor.Replace("'","''");
string tColor = var_TextColor.Replace("'","''");
string sqlCommandStr = String.Format("select * from Cars where ModelNumber like '%{0}%' and where Colour like '%{1}%'", tSearchedFor, tColor);
 

I separated out some of the pieces so you can really see what is happening. String.Format() is removing the {#} and replacing it with the parameters that follow: {0} is the first parameter after the string, {1} is the second, and so forth.

Also, please use code tags in your posts; it will make your code easier to read.

Will this work if there is more than query required?

eg, can I use the code

string sqlCommandStr = String.Format("select * from Cars where ModelNumber like '%{0}%'",var_TextSearchedFor.Replace("'","''") + " and where Colour like '%{0}%'",var_TextColor.Replace("'","''"));


Dave