Solved

How to insert and append characters to elements in an array

Posted on 2008-06-09
12
2,548 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
  • 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 100 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
 

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

758 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now