Solved

How to insert and append characters to elements in an array

Posted on 2008-06-09
12
2,555 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 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
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 

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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

We all know that functional code is the leg that any good program stands on when it comes right down to it, however, if your program lacks a good user interface your product may not have the appeal needed to keep your customers happy. This issue can…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

636 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