How do I...I am getting a data from checklistbox into a string and pass that string into a sql query for in operator

Question

0.00/5 (No votes)

See more:

Hi Everyone...

I am getting a data from checklistbox into a string and pass that string into a sql query for in operator.But My coding is not working.I show you.

SQL

CheckedInvoiceNo = ""
       'Dim ff As New frmSalePerformaReport
       For I = 0 To chklistbx.Items.Count - 1
           If chklistbx.GetItemChecked(I) = True Then
               CheckedInvoiceNo = CheckedInvoiceNo + "," + chklistbx.Items(I).ToString()
           End If
       Next
       sql = "select PFDate,PFNo,CustType,Party,PartyCode,SaleNo,SaleDate,InvHeading,SaleType,Mode,Transporter from sale where PFNo in '" & CheckedInvoiceNo & "'"

------------------------
Edit: Added information that OP originally submitted as a solution.

Quote:
I am getting data into checkinvoiceNo.But My syntax of append a string is not correct

I am getting like that checkinvoice no = NZ-SP/13-14/00002 ,NZ-SP/13-14/00001 ,

But I wanna get like that checkinvoice no = 'NZ-SP/13-14/00002' , 'NZ-SP/13-14/00001'

Posted 31-Dec-13 18:57pm

1989priya

Updated 1-Jan-14 8:55am

TnTinMn

v2

Add a Solution

Comments

TnTinMn 1-Jan-14 14:57pm

I have added the information that your erroneously submitted as a solution to your question. Please delete "Solution 2" that you posted.
Thanks.

2 solutions

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)

Peter Leow · Answer 1 · 2013-12-31T19:28:00

First of all, you should not "inject" value directly into your sql query like what you did

SQL

in '" & CheckedInvoiceNo & "'"

to avoid sql injection, read more about it here SQL Injection Attacks and Some Tips on How to Prevent Them[^].

Secondly, you sql query is not constructed correctly, check these out:
1. SQL Select[^]
2. SQL IN[^]

TnTinMn · Answer 2 · 2014-01-01T09:20:00

Quote:
I am getting data into checkinvoiceNo.But My syntax of append a string is not correct

I am getting like that checkinvoice no = NZ-SP/13-14/00002 ,NZ-SP/13-14/00001 ,

But I wanna get like that checkinvoice no = 'NZ-SP/13-14/00002' , 'NZ-SP/13-14/00001'

I would recommend that you use a StringBuilder to construct "CheckedInvoiceNo". Something like this:

VB

Dim CheckedInvoiceNo As New System.Text.StringBuilder()
For Each CheckedItem As Object In chklistbx.CheckedItems
   If CheckedInvoiceNo.Length > 0 Then ' a prior item was added, so add a comma
      CheckedInvoiceNo.Append(","c)
   End If
   CheckedInvoiceNo.Append("'"c)
   CheckedInvoiceNo.Append(CheckedItem.ToString())
   CheckedInvoiceNo.Append("'"c)
Next

' note: I modified the "In" expresion
sql = "select PFDate,PFNo,CustType,Party,PartyCode,SaleNo,SaleDate,InvHeading,SaleType,Mode,Transporter from sale where PFNo IN (" & CheckedInvoiceNo.ToString() & ")"

Also, please pay heed to Pete Leow's comments about parameterized queries.

How do I...I am getting a data from checklistbox into a string and pass that string into a sql query for in operator

2 solutions

Solution 1

Solution 3

Add your solution here

Preview 0