Solved

use @strAll in SQL/vb.net

Posted on 2014-10-13
3
340 Views
Last Modified: 2014-10-14
In the code below, what I am trying to do is to determine what the value of str_All should be. And then trying to set @strAll = str_All so that I could use it in my SQL.

Question: How can I do something like this?
Dim str_All As String
If chkCustomersWithOrders.Checked Then
         str_All = "(have orders)"
Else
         str_All = "(some have orders)"
End If

 @strAll =str_All
 SQL.ExecQuery("SELECT CustomerID, substring(FirstName + ' ' + LastName + '                                                     ',1,50) +" & " dbo.fnHasOrders (CustomerID) as CustName From tblCustomers Union" & _
  " SELECT '99999', '<All>' + @strAll  From tblCustomers")

Open in new window

0
Comment
Question by:Mike Eghtebas
3 Comments
 
LVL 15

Assisted Solution

by:Haris Djulic
Haris Djulic earned 200 total points
ID: 40379273
Hi,

do you really need to pass the value strAll to @strAll, you can use it like this..

Dim str_All As String
If chkCustomersWithOrders.Checked Then
         str_All = "(have orders)"
Else
         str_All = "(some have orders)"
End If

 @strAll =str_All
 SQL.ExecQuery("SELECT CustomerID, substring(FirstName + ' ' + LastName + '                                                     ',1,50) +" & " dbo.fnHasOrders (CustomerID) as CustName From tblCustomers Union" & _
  " SELECT '99999', '<All>' + " & strAll & "  From tblCustomers")

Open in new window

0
 
LVL 33

Accepted Solution

by:
ste5an earned 300 total points
ID: 40379313
Use a parameterized query.

btw, please post concise and complete samples. What kind of type is SQL?
0
 
LVL 33

Author Comment

by:Mike Eghtebas
ID: 40380396
samo4fun,

Just because I wanted to see how it is done via parameter, I was trying to use @strAll.


ste5an,

Yes, I should have mentioned SQLEXPRESS12

Mike
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

831 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