How to insert and append characters to elements in an array

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

cs2dataAsked:
Who is Participating?
 
rstomarConnect With a Mentor Commented:
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
 
AUmidhCommented:
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
 
cs2dataAuthor Commented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
cs2dataAuthor Commented:
Please note: Previous reply was to AUmidh's posting.
0
 
cs2dataAuthor Commented:
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
 
rstomarCommented:
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
 
cs2dataAuthor Commented:
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
 
cs2dataAuthor Commented:
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
 
AUmidhCommented:
>> 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
 
AUmidhCommented:
replace this line string str=strArray[i]; with the following
string str=Convert.ToString(arrayList[i]);
0
 
rstomarCommented:
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
 
cs2dataAuthor Commented:
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
All Courses

From novice to tech pro — start learning today.