i have a treeview from which i am capturing values whats happening is when i select more than one value my query does not work ?

Question

0.00/5 (No votes)

See more:

SQL

Dim selected As String = ""
        For Each node As RadTreeNode In treedetails.Nodes
            If node.Checked = True Then
                selected += node.Value + ""
            End If
        Next
        tn &= " and a.zbaid in (" & selected & ")"

when i check though breakpoints this is whats happening

SQL

select distinct a.empno,a.name,a.empdesignation ,to_char(b.dtleavefrom,'dd-Mon-yyyy') as dtleavefrom  ,to_char(b.dtleaveto,'dd-Mon-yyyy') as dtleaveto,c.desp, DECODE(b.dtleaveto -b.dtleavefrom,0,1,b.dtleaveto -b.dtleavefrom) dAYS   from tt.employee a join dilpreet.leavedetails b on a.empno = b.empid join ranu.leavemaster c on b.leaveid = c.leaveid  where (b.status='C')  and b.dtleavefrom LIKE '%JAN-13%' and a.zbaid in (201123208210166212)

the a.zbaid feild shows my id like this when it is supposed to show it like (201,123,208,210,166,212)
please help

Posted 7-Jul-13 23:31pm

a2ulthakur

Updated 7-Jul-13 23:32pm

v2

Add a Solution

2 solutions

Solution 2

VB

Dim selected As String = ""
       Dim sep As String = ""
       For Each node As RadTreeNode In treedetails.Nodes
           If node.Checked = True Then
               selected +=  node.Value & ","
           End If
       Next
       selected=selected.Substring(0, selected.Length - 1)
       tn &= " and a.zbaid in (" & selected & ")"

Posted 8-Jul-13 2:36am

Basmeh Awad

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2013-07-07T23:48:00

Solution 1

IN clause requires a commas separated list: so add your comma in the loop:

VB

Dim selected As String = ""
Dim sep As String = ""
For Each node As RadTreeNode In treedetails.Nodes
    If node.Checked = True Then
        selected += sep + node.Value
        sep = ","
    End If
Next
tn &= " and a.zbaid in (" & selected & ")"

But you would be better off using a StringBuilder, or assembling a List of strings, and using String.Join.

[edit]move separator to before clause...oops - OriginalGriff[/edit]

Posted 7-Jul-13 23:48pm

OriginalGriff

Updated 8-Jul-13 2:03am

v2

Comments

a2ulthakur 8-Jul-13 7:53am

still it produces output like this

select distinct a.desgid,a.empno,a.name,a.empdesignation ,to_char(b.dtleavefrom,'dd-Mon-yyyy') as dtleavefrom ,to_char(b.dtleaveto,'dd-Mon-yyyy') as dtleaveto,c.desp, DECODE(b.dtleaveto -b.dtleavefrom,0,1,b.dtleaveto -b.dtleavefrom) dAYS from tt.employees a join dilpreet.leavedetails b on a.empno = b.empid join ranu.leavemaster c on b.leaveid = c.leaveid where (b.status='P' or b.status='PR' or b.status='AP') and b.dtleavefrom LIKE '%FEB-13%' and a.zbaid in (201123,208,210,)

OriginalGriff 8-Jul-13 8:03am

:doh: I blame lack of coffee this morning - I have changed my code to fix it...