• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 452
  • 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
 
Jorge PaulinoIT Pro/DeveloperCommented:
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
 
Jorge PaulinoIT Pro/DeveloperCommented:
The same needs value. I want to use this version of solution.
 Don't understand!!!
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
Jorge PaulinoIT Pro/DeveloperCommented:
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
 
Jorge PaulinoIT Pro/DeveloperCommented:
Are you using stored procedures ?
0
 
Jorge PaulinoIT Pro/DeveloperCommented:
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
 
Jorge PaulinoIT Pro/DeveloperCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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