• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 450
  • Last Modified:

vb .net pass value to sql parameter

Hi,
I want to pass the Null value to an sql parameter.
but I get an error when I run it parameter @aad needs value
code :
Form2.SqlDataAdapter2.SelectCommand.Parameters.Item(0).SqlValue = DBNull.Value
Form2.SqlDataAdapter2.SelectCommand.Parameters.Item(1).Value = TextBox1.Text

sql :
SELECT     *
FROM         Taxinomisi_fortotikon
WHERE     (aa LIKE @aad) OR
                      (date_fortosis LIKE @datef)


Kyriakos
0
kyriakos70
Asked:
kyriakos70
  • 8
  • 6
1 Solution
 
jpaulinoCommented:
Hi,
You should change the parameter to:
Form2.SqlDataAdapter2.SelectCommand.Parameters("@aad", SqlDbType.VarChar).Value = DBNull.Value

and change the T-SQL to:
WHERE     (@aad IS NULL OR aa LIKE @aad) OR
 
 
0
 
kyriakos70Author Commented:
Hello,
The same needs value. I want to use this version of solution.
0
 
jpaulinoCommented:
The same needs value. I want to use this version of solution.
 Don't understand!!!
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
kyriakos70Author Commented:
Because I want to use complex statements.
eg. select * from table where aa like a or bb like @b and dd between @a and @b.
0
 
jpaulinoCommented:
WHERE   (@aad IS NULL OR aa LIKE @aad) OR

I still don't understand your problem. If you use something like this you can pass a null parameter and still show all the results.
0
 
kyriakos70Author Commented:
this doesn't work

You should change the parameter to:
Form2.SqlDataAdapter2.SelectCommand.Parameters("@aad", SqlDbType.VarChar).Value = DBNull.Value
and change the T-SQL to:
WHERE     (@aad IS NULL OR aa LIKE @aad)
it keeps telling me @aad expects parameter.

0
 
jpaulinoCommented:
Are you using stored procedures ?
0
 
jpaulinoCommented:
The T-SQL command should be like this:
WHERE  (@aad IS NULL OR aa LIKE '%' + @aad + '%')
 
If this doesn't work can you show the code you have ?
0
 
kyriakos70Author Commented:
SELECT    * FROM         Taxinomisi_fortotikon
WHERE     (aa LIKE NULL OR aa LIKE @aad) OR
                      (date_fortosis LIKE @datef OR @datef IS NULL)

I use the sql builder of the adapter and when I press the execute query it becomes splitted like this :
SELECT    * FROM         Taxinomisi_fortotikon
WHERE     (aa LIKE NULL) OR
                      (aa LIKE @aad) OR
                      (date_fortosis LIKE @datef) OR
                      (@datef IS NULL)

It is changing the sql.
0
 
jpaulinoCommented:
It should be this (check snippet) but you're using a date and you should define a period like this
 
@datef  BETWEEN @start AND @end

SELECT * FROM  Taxinomisi_fortotikon
WHERE  (@aad IS NULL OR aa LIKE '%' + @aad + '%') OR
       (@datef IS NULL OR date_fortosis = @datef)

Open in new window

0
 
kyriakos70Author Commented:
No it is not working, has the or operator it must work as is the or tells sql one or the other parameter?
0
 
kyriakos70Author Commented:
It is like I can't have 2 parameters, something is not good in the configuration of vs? sql server??
0
 
kyriakos70Author Commented:
aa is an integer type
0
 
kyriakos70Author Commented:
No not working, I just wanted to have a search option, I have tried another thing to change the command text of the sqldataadapter at run time but doesn't work either, any help?
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 8
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now