Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to insert and append characters to elements in an array

Posted on 2008-06-09
12
Medium Priority
?
2,556 Views
Last Modified: 2013-12-17
In C# (VS 2005), I have an array of VendorNumbers.  I am outputting those to a single string variable: strVendorNo.  I am using this variable later in a sub-query.  Currently, strVendorNo outputs as such:

(123456, 987654, 111222, 444555, 678989)

1.  I need it to do this instead:
('123456', '987654', '111222', '444555', '678989')

Please see code snippet below.

--OR--

2. When strVendorNo is passed as vendorNumberString,  I need my SQL statement to behave and not give a data mismatch:

[string strSQL = "SELECT Company, VendorNumber, EmailAddress, 'Send Mail' AS SendMail FROM Contacts Where VendorNumber IN (" + vendorNumberString + ")";]

Any ideas?  Thanks!


foreach (DictionaryEntry diS in secondString)
                {
                    ArrayList arr = (ArrayList)diS.Value;
                    for (int q = 0; q < arr.Count; q++)
                    {
                        if (!(arr[q].ToString().StartsWith("**")))
                        {
                            lstSecondString.Items.Add(diS.Key + " -- " + arr[q]);
                       
 
                            ///***Here is the issue...***
                            strVendorNo += ", " + arr[q];
 
                        }
                    }
 
                }

Open in new window

0
Comment
Question by:cs2data
[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
  • 6
  • 3
  • 3
12 Comments
 
LVL 7

Expert Comment

by:AUmidh
ID: 21748284
1. strArray ----> (123456, 987654, 111222, 444555, 678989)

for(int i=0;i<strArray.Length;i++)
{
  string str=strArray[i];
  str= "''" + str + "''";
  strArray[i]=str;
}

2.

if vendorNumberString is single value then do like the following
[string strSQL = "SELECT Company, VendorNumber, EmailAddress, 'Send Mail' AS SendMail FROM Contacts Where VendorNumber IN ('" + vendorNumberString + "')";]

0
 
LVL 5

Accepted Solution

by:
rstomar earned 400 total points
ID: 21748322
foreach (DictionaryEntry diS in secondString)
                {
                    ArrayList arr = (ArrayList)diS.Value;
                    for (int q = 0; q < arr.Count; q++)
                    {
                        if (!(arr[q].ToString().StartsWith("**")))
                        {
                            lstSecondString.Items.Add(diS.Key + " -- " + arr[q]);
                       
 
                            ///***Here is the issue...***
                            if(strVendorNo.Length > 0)
                            strVendorNo += ", "

                             strVendorNo += "'" + arr[q] + "'";  // it is <double quotes><single quote><double quotes>
 
                        }
                    }
 
                }


What is the data type of vendorNumber column.....anyways if you want to CAST it you can do
Where CAST(VendorNumber as nvarchar(12)) IN ('" + vendorNumberString + "')
0
 

Author Comment

by:cs2data
ID: 21748346
I implemented the code as suggested, but

1.  Only one value was returned,
2.  No comma is printed.

I need this to be the output:

'123456', '987654, '111222', '444555', '678989'


(I should note that I am using an ArrayList and not an Array).
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:cs2data
ID: 21748355
Please note: Previous reply was to AUmidh's posting.
0
 

Author Comment

by:cs2data
ID: 21748426
Rstomar,

I am reading an Excel file and writing to XML.  In Excel, it in 'general format' as a 'number stored as text.'  a few of the VendorNumbers have leading zeroes which I need to read.
0
 
LVL 5

Expert Comment

by:rstomar
ID: 21748452
Can you pls. explain your problem with Data conversion......If you convert VendorNumber values to numbers first, it will take away the leading zeroes.....

From your question it was not clear about the need for conversion......I assumed that you are running the query to get data from the database.
0
 

Author Comment

by:cs2data
ID: 21748502
I am reading an excel file (uploaded) to see if a company has an email address.  The client originally sent the VendorNumber as numeric.  Everything was fine but then they sent the VendorNumber as text (to account for some leading zeroes).  Since then I have had problems.  

It is now bombing when I call the fill method of my data adapter...
TestExcel.xls
0
 

Author Comment

by:cs2data
ID: 21748610
I am sorry I was not clear.  I am taking the values from the list array, making them one string, and then using that in my where clause to see if there is an email address for one of the companies (in the excel file).
0
 
LVL 7

Expert Comment

by:AUmidh
ID: 21748818
>> I am taking the values from the list array, making them one string

Sample ArrayList ----> (123456, 987654, 111222, 444555, 678989)

string strSingleString="";
for(int i=0;i<arrayList.Length;i++)
{
  string str=strArray[i];
  str= "''" + str + "''" + ",";       [Replace]
  strSingleString=strSingleString + str;
//   two single quotation for single quotation to use in query
}

at the end strSingleString contains your desire resulted single string.
like '123456', '987654', '111222', '444555', '678989'
but with two single quototaion if you need single then replace the above line with the
str= "'" + str + "'" + ",";
0
 
LVL 7

Expert Comment

by:AUmidh
ID: 21748826
replace this line string str=strArray[i]; with the following
string str=Convert.ToString(arrayList[i]);
0
 
LVL 5

Expert Comment

by:rstomar
ID: 21748845
Ok...to convert values in Excelsheet use CDbl, CInt, CStr etc. for Double , Integer or String Conversions

Try this

Where CInt(VendorNumber) IN ('" + vendorNumberString + "')
0
 

Author Comment

by:cs2data
ID: 21757042
The accepted solution was what I needed.  I didn't realize it until tonight but there were separate problems with the user's Excel sheet (when I ran the SQL query).  Anyway, after importing into Access and exporting back out as Excel, everything worked fine.  Thanks everyone.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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…
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

722 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