2

Currently I will clean my code a little bit and VS told me, it is better to use the SqlParameter for the sql commands instead a compound string. So I decided to change my code, unfortunately now I don’t get a result and I don’t know why. Here is the piece of my code:

...    
DataTable dt = new DataTable();
SqlConnection connection = new SqlConnection(GetSQLConnectionString());
SqlDataAdapter sqlSelect = new SqlDataAdapter();
try
{
    connection.Open();
    sqlSelect.SelectCommand = connection.CreateCommand();
    sqlSelect.SelectCommand.CommandText = "SELECT id, @FROM AS \"from\", @TO AS \"to\" FROM Dictionary WHERE @FROM LIKE @SEARCHSTRING";
    sqlSelect.SelectCommand.Parameters.Add(new SqlParameter("@FROM", this.from));
    sqlSelect.SelectCommand.Parameters.Add(new SqlParameter("@TO", this.to));
    sqlSelect.SelectCommand.Parameters.Add(new SqlParameter("@SEARCHSTRING", "'%" + this.SearchField.Text + "%'"));

    sqlSelect.Fill(dt);
    connection.Close();
}
catch(SqlException e)
...

I don’t get any exception. Why is dt empty after the search? (With a compound string, the select works.) What went wrong?

Greetz

Andre Hofmeister
  • 3,185
  • 11
  • 51
  • 74

4 Answers4

2

You can't specify field names using parameters like that. In your where clause WHERE @FROM LIKE @SEARCHSTRING it is comparing the value of the parameter @FROM with the value of the parameter @SEARCHSTRING.

If the where clause evaluates to true you will get every record in the dictionary table, if it evaluates to false you will get no records. It will never treat the contents of @from as a field name in the dictionary table.

Ben Robinson
  • 21,601
  • 5
  • 62
  • 79
  • Okay, I tested it and you are right! The problem is really the where clause. (Parameters at the column position works fine). For a better understanding, the `WHERE @FROM LIKE @SEARCHSTRING` comparing both values, if they are the same, I will get the complete table, right? Is it possible to add a parameter at `@FROM`, or have I just to compound the `CommandText` string at that position? Thanks, greetz. – Andre Hofmeister Oct 27 '11 at 14:03
  • Yes if they are the same you will get the whole table. You will have to dynamically build your Sql string if you want to use parameters to specify field names. The safest way to do this is to use spExecuteSql http://msdn.microsoft.com/en-us/library/ms188001.aspx – Ben Robinson Oct 27 '11 at 15:11
1

As people have said here the issue is that you cant pass field names as parameters.

The approach you are taking is a bad idea for a couple of reasons, firstly when you pass a sql command in this way the server has to recompile it every time you execute that query, this puts extra load on the server and slows down performance. Secondly it is a risk to security transmitting your select statements like this as it gives anyone who intercepts it a look at your table structure. Thirdly using select statements like this means if you ever want to reuse the code you cant without a copy paste.

What I would reccomend is switching to a stored procedure. you can still pass in your parameters etc but it will improve your code as it takes the SQL out of the c# and leaves only what is relevant.

If you REALLY need to pass in fieldnames to be used within the select statement like this you can do this in SQL and build up a query string then execute it using sp_executesql.

Basically what you do is declare a query string like 

DECLARE @queryString VARCHAR(3000)

SET @queryString ='SELECT id, '+@FROM+' AS from, '+@TO+' AS to FROM Dictionary WHERE +'@FROM+' LIKE %'+@SEARCHSTRING+'%'

then just use sp_executesql to execute the @queryString

You may need to cast the parameters as Varchar though if you get any errors whilst building up the querystring

Purplegoldfish
  • 5,268
  • 9
  • 39
  • 59
  • Yes, I really need to pass the fieldnames. The end user could change with a dropdown list the selected column (I knew it is not safe, but I don’t know other way). If I understand you correctly it is better, to create a procedure, add at the procedure the dynamically fields and add the parameters like [here](http://stackoverflow.com/questions/376941/c-sp-executesql-and-incorrect-syntax) in c#. Also for sql updates, deletes and so on? Greetz – Andre Hofmeister Oct 27 '11 at 16:38
  • Okay, I have added a procedure like you said. Now I have the problem that I have to add ‘% and %’ to the search string. In the c# code that isn’t a problem and everything works fine, but how could I add a single quote if I execute the procedure from the sql server? In c# I do: `cmd.Parameters["@SEARCHSTRING"].Value = "'%string%'"; `. At the sql server I executed `exec test_ procedure @FROM = 'FROMVALUE', @TO = 'TOVALUE', @SEARCHSTRING = 'STRING'`. Greetz – Andre Hofmeister Oct 27 '11 at 19:34
  • @Taz I updated the example I posted to show the querystring with the % – Purplegoldfish Oct 28 '11 at 08:06
  • Well, that works, but if I try to select with equal (`=`) the single quotes are missing, if the column is for example a varchar. I would be interesting to know, how I could execute a procedure which expect a sting at the sql server. The actual problem is solved. Greetz – Andre Hofmeister Oct 28 '11 at 08:48
  • @Taz check this out, its fairly simple but it shows how this can be expanded a bit http://pastebin.com/tZqQpzmW – Purplegoldfish Oct 28 '11 at 09:18
  • Mhm, to avoid misunderstanding the procedure works correctly. I would like to know, how I could execute a procedure from the sql server, if a parameter is a string. If I execute for example this: `Procedure_Name @FROM=coumn1, @TO=coumn2, @SEARCHSTRING=SEARCHSTRING`, the sql server tells me, that `SEARCHSTRING` is not a column, that’s correctly. The correct syntax for searching is `['SEARCHSTRING']`. Thanks everyone! Greetz. – Andre Hofmeister Oct 28 '11 at 09:53
  • @taz When you select a string you usually do column= 'stringtext' but when you are using the variables the string you create ends up looking like column = stringtext without the ' ' what you need to do is make sure these characters get included in the param you pass to the server – Purplegoldfish Oct 28 '11 at 10:14
0

This:

sqlSelect.SelectCommand.Parameters.Add(new SqlParameter("@SEARCHSTRING", "'%" + this.SearchField.Text + "%'"));

suppose to be:

sqlSelect.SelectCommand.Parameters.Add(new SqlParameter("@SEARCHSTRING", "%" + this.SearchField.Text + "%"));
Peter Csala
  • 17,736
  • 16
  • 35
  • 75
AndyPL
  • 1
  • Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Mar 04 '22 at 05:42
0

Why you have written query like this?

   "SELECT id, @FROM AS \"from\", @TO AS \"to\" FROM Dictionary WHERE @FROM LIKE @SEARCHSTRING";

you are trying to fetch @FROM from the table and also trying to pass it as a parameter, hows that supposed to work? Also why have you included slashes? they just make things messy, remove them. A Select query takes input parameters only with "WHERE" clause and nowhere else.

Try replacing it with this

"SELECT id, FROM AS 'from', TO AS 'to' FROM Dictionary WHERE FROM LIKE @SEARCHSTRING";

Also remove all but the last occurrences of:

sqlSelect.SelectCommand.Parameters.Add

Also take care that "FROM" is an SQL keyword as well, so make sure its being interpreted the right way by enclosing it in "[]".

Hope this helps...

Aman
  • 548
  • 1
  • 4
  • 11