Solved

vb .net pass value to sql parameter

Posted on 2009-05-06
14
435 Views
Last Modified: 2012-05-06
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
Comment
Question by:kyriakos70
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
14 Comments
 
LVL 48

Expert Comment

by:jpaulino
ID: 24312574
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
 

Author Comment

by:kyriakos70
ID: 24312794
Hello,
The same needs value. I want to use this version of solution.
0
 
LVL 48

Expert Comment

by:jpaulino
ID: 24312902
The same needs value. I want to use this version of solution.
 Don't understand!!!
0
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 

Author Comment

by:kyriakos70
ID: 24312928
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
 
LVL 48

Accepted Solution

by:
jpaulino earned 500 total points
ID: 24313017
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
 

Author Comment

by:kyriakos70
ID: 24313024
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
 
LVL 48

Expert Comment

by:jpaulino
ID: 24313936
Are you using stored procedures ?
0
 
LVL 48

Expert Comment

by:jpaulino
ID: 24314399
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
 

Author Comment

by:kyriakos70
ID: 24315748
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
 
LVL 48

Expert Comment

by:jpaulino
ID: 24315842
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
 

Author Comment

by:kyriakos70
ID: 24315971
No it is not working, has the or operator it must work as is the or tells sql one or the other parameter?
0
 

Author Comment

by:kyriakos70
ID: 24316144
It is like I can't have 2 parameters, something is not good in the configuration of vs? sql server??
0
 

Author Comment

by:kyriakos70
ID: 24316669
aa is an integer type
0
 

Author Comment

by:kyriakos70
ID: 24327004
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question