Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

use @strAll in SQL/vb.net

Posted on 2014-10-13
3
Medium Priority
?
368 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 800 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 36

Accepted Solution

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

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

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Question has a verified solution.

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Simulator games are perfect for generating sample realistic data streams, especially for learning data analysis. It is even useful for demoing offerings such as Azure stream analytics, PowerBI etc.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

916 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