Hi All,

I am trying to read the SQL table and update null value with "1" and nonvalue with "0" as you can see in tables. To do this i have design i code that allow me to read the date and write to the text file but it seems there is a problem loop.

I really appricate if you could guys give me a hand on this one.

For example
orginal table

lname |fname |account_num|
--------------------------
John |Smith |
Tony | | 46666000

converted table should be like

lname |fname | account_num
--------------------------
0 | 0 | 1
0 | 1 | 0

using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;


namespace update sql table 
{
    class OrdinalIndexer
    {
        static void Main(string[] args)
        {
            // connection string
            // connection string
            string connString =
          @" server = (local);
               database = adventure;
               integrated security = sspi";

            // query
            string sql = @"
               select [lname] ,[fname] ,[account_num]
      
               from
               customer
               ";
            // create connection
            SqlConnection conn = new SqlConnection(connString);
            try
            {
                // Open connection
                conn.Open();
                // create command
                SqlCommand cmd = new SqlCommand(sql, conn);
                // create data reader
                SqlDataReader rdr = cmd.ExecuteReader();
                // print headings
                Console.WriteLine("\t{0} {1} {2}",
                "lname".PadRight(10),
                "fname".PadRight(10),
                "account_num".PadRight(10)
                );
                Console.WriteLine("\t{0} {1} {2}",
                "============".PadRight(10),
                "============".PadRight(10),
                 "============".PadRight(10)
                );
                //=====loop for changing table values to 0 and 1===== here is the problem
                for (int row = 0; row < tuplesOnRows->Count; row++)
                {
                    theDataRow.clear();

                    //s = s + (tuplesOnRows[row].Members[0].Caption + "\t");
                    if (!(tuplesOnRows->get_Item(row)->get_Members()->get_Item(0)->Caption->Equals("All")))
                    {
                        //Console::Write(tuplesOnRows->get_Item(row)->get_Members()->get_Item(0)->Caption->ToString());
                        //Console::Write("\t");

                        for (int col = 0; col < tuplesOnColumns->Count; col++)
                        {
                            //s = s + (cs.Cells[col, row].FormattedValue + "\t");
                            if (cs->get_Cells()->get_Item(col, row)->get_FormattedValue()->Equals(""))
                                //Console::Write(" 0 ");
                                theDataRow.push_back(0);
                            else
                                //Console::Write(" 1 ");
                                theDataRow.push_back(1);
                        }
                        //Console::WriteLine();
                        //s = s + "\n";
                        theDataSet.push_back(theDataRow);
                        index++;
                    }
                }

                // open writing file 

                StreamWriter w1 = new StreamWriter("c:\\2.txt");

                // loop through result set 

                //string line1;
                while (rdr.Read())
                {
                    string field1 = rdr[0].ToString().PadLeft(10);
                    string field2 = rdr[1].ToString().PadLeft(10);
                    string field3 = rdr[2].ToString().PadLeft(10);

                    string row = String.Format(" {0} | {1} | {2}", field1, field2, field3);
                    Console.WriteLine(row);
                    w1.WriteLine(row);
                }





                // close reader
                rdr.Close();
                // flush the file
                w1.Flush();
                // close the file
                w1.Close();

                Console.Read();
            }
            catch (Exception e)
            {
                Console.WriteLine("Error Occurred: " + e);
            }
            finally
            {
                // close connection
                Console.Read();
                conn.Close();
            }
        }
    }
}

Please can u clarify your problem.... what i understand is that u read a certain table from SQL Database and change its values to 1's and 0's is that right?

Thank you very much.
The problem is that i cannot convert the SQL table to 0,1 format. When i tried to execute the code, the problem occur on loop section line 49 to 75.

ok... u can insert 0 and 1 as a string is tht not working??? you can enter it in the form of string and when want to calculate you can convert in to integer... because the datatype of your columns must be varchar tu you have to enter integer in form of varchar like

put '' arround 0 and 1

You are using C++ syntax to reference members of the class. Did you intend this to be managed C++ or do you want C#?

.

the above is a single quote arroung 0 and 1

you are just writing on console?

You are using C++ syntax to reference members of the class. Did you intend this to be managed C++ or do you want C#?

I would like to use C# but sometime i get confuse because i am new learner to C#.

Ok :) This line is in C++ syntax

for (int row = 0; row < tuplesOnRows->Count; row++)

In C# we write the same thing as

for (int row = 0; row < tuplesOnRows.Count; row++)

The -> is used very very very rarely, and if you are just beginning in C#, you aren't close to where you'll use it :) First thing to do is clean up all your code replacing all those.

Ok :) This line is in C++ syntax

for (int row = 0; row < tuplesOnRows->Count; row++)

In C# we write the same thing as

for (int row = 0; row < tuplesOnRows.Count; row++)

The -> is used very very very rarely, and if you are just beginning in C#, you aren't close to where you'll use it :) First thing to do is clean up all your code replacing all those.

Thank you very much. I really would like to get your advice and some hint this problem.

When i eliminate the for loop section code, the program runs properly. This helps me to read and write table. However, when in include the loop code that converts 0 , 1 , i got these mistake. Can you please help me to sort this out or refer me to good website?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.