Using parameters

  • Thread starter Thread starter Neven Klofutar
  • Start date Start date
N

Neven Klofutar

Hi,

I have a problem with creation of SQL query when I try to use parameter (I'm
not using stored procedure).

SELECT * FROM Contact WHERE prefix IN (@strPrefix)

@strPrefix is created:
SqlParameters[] arlSqlParams = new SqlParameters[1];
arlSqlParams[0] = new SqlParameter("@strPrefix", SqlDbType.NVarChar);
arlSqlParams[0].value = strPrefix

strPrefix is created dinamicaly, it's value looks like: '01','021','022' so
when inserted into query, query should look like:
SELECT * FROM Contact WHERE prefix IN ('01','021','022')

When I try to build query manualy, everything works perfectly.
" SELECT * FROM Contact WHERE prefix IN (" + strPrefix + ") "


When using parametriset version Exception is not thrown, I just get empty
DataSet.



Thanks, Neven
 
Or 4 or 2 or 7 if he does not have 3 elements in the IN list?

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Sahil Malik said:
Try replacing @strPrefix with 3 parameters.

--

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
----------------------------------------------------------------------------
---------------

Neven Klofutar said:
Hi,

I have a problem with creation of SQL query when I try to use parameter (I'm
not using stored procedure).

SELECT * FROM Contact WHERE prefix IN (@strPrefix)

@strPrefix is created:
SqlParameters[] arlSqlParams = new SqlParameters[1];
arlSqlParams[0] = new SqlParameter("@strPrefix", SqlDbType.NVarChar);
arlSqlParams[0].value = strPrefix

strPrefix is created dinamicaly, it's value looks like: '01','021','022' so
when inserted into query, query should look like:
SELECT * FROM Contact WHERE prefix IN ('01','021','022')

When I try to build query manualy, everything works perfectly.
" SELECT * FROM Contact WHERE prefix IN (" + strPrefix + ") "


When using parametriset version Exception is not thrown, I just get empty
DataSet.



Thanks, Neven
Click to expand...
Click to expand...
 
Then look at Bill Ryan's solution ;-)

- SM

William (Bill) Vaughn said:
Or 4 or 2 or 7 if he does not have 3 elements in the IN list?

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Sahil Malik said:
Try replacing @strPrefix with 3 parameters.

--

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
--------------------------------------------------------------------------
Click to expand...
--
---------------

Neven Klofutar said:
Hi,

I have a problem with creation of SQL query when I try to use parameter (I'm
not using stored procedure).

SELECT * FROM Contact WHERE prefix IN (@strPrefix)

@strPrefix is created:
SqlParameters[] arlSqlParams = new SqlParameters[1];
arlSqlParams[0] = new SqlParameter("@strPrefix", SqlDbType.NVarChar);
arlSqlParams[0].value = strPrefix

strPrefix is created dinamicaly, it's value looks like:
Click to expand...
'01','021','022'
so
when inserted into query, query should look like:
SELECT * FROM Contact WHERE prefix IN ('01','021','022')

When I try to build query manualy, everything works perfectly.
" SELECT * FROM Contact WHERE prefix IN (" + strPrefix + ") "


When using parametriset version Exception is not thrown, I just get empty
DataSet.



Thanks, Neven
Click to expand...
Click to expand...
Click to expand...
 
Yup, this helps !

Thanx, Neven


W.G. Ryan MVP said:
Neven - try this
http://support.microsoft.com/default.aspx?scid=kb;en-us;555167

If you still have any problems let me know. THe essential problem is that
params escape values so it's treating the param as one value, not a list
of them but the above will show you a work around.
Neven Klofutar said:
Hi,

I have a problem with creation of SQL query when I try to use parameter
(I'm not using stored procedure).

SELECT * FROM Contact WHERE prefix IN (@strPrefix)

@strPrefix is created:
SqlParameters[] arlSqlParams = new SqlParameters[1];
arlSqlParams[0] = new SqlParameter("@strPrefix", SqlDbType.NVarChar);
arlSqlParams[0].value = strPrefix

strPrefix is created dinamicaly, it's value looks like: '01','021','022'
so when inserted into query, query should look like:
SELECT * FROM Contact WHERE prefix IN ('01','021','022')

When I try to build query manualy, everything works perfectly.
" SELECT * FROM Contact WHERE prefix IN (" + strPrefix + ") "


When using parametriset version Exception is not thrown, I just get empty
DataSet.



Thanks, Neven
Click to expand...
Click to expand...
 
Back
Top