Solved

How to insert and append characters to elements in an array

Posted on 2008-06-09
12
2,553 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
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 

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

Industry Leaders: 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

Suggested Solutions

Title # Comments Views Activity
transition to visual .net from vb6 5 59
Upgrade code from VS 2010 to VS 2015 7 32
Consume a webservice via VB in Visual Studio 2015 3 15
Return array 3 19
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.
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

735 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