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 |
| 4000 | |
converted table should be like
lname | fname | account_num |
|
| 0 | 0 | 1 |
|
| 0 | 1 | 0 |
|
//the code
using
using
using
using
namespace
{
System;System.Data;System.Data.SqlClient;System.IO;update sqltable class OrdinalIndexer{
{
static void Main(string[] args)
// connection string
// connection string
database = Adventure;
integrated security = sspi"
string connString =@" server = (local);;
// query
select [lname] ,[fname] ,[account_num]
from
customer
"
string sql = @";
// create connection
SqlConnection conn = new SqlConnection(connString);try{
// Open connectionconn.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)
//================== the problem is in the following loop ==================================
{
theDataRow.clear();
for (int row = 0; row < tuplesOnRows->Count; row++)//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;
{
w1.WriteLine(row);
}
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);// close readerrdr.Close();
// flush the filew1.Flush();
// close the filew1.Close();
}
{
}
Console.Read();catch (Exception e)Console.WriteLine("Error Occurred: " + e);finally{
// close connection
conn.Close();
}
}
}
}
Console.Read();
Nony KPosted Mar 17, 2011, 12:47 AM
Thank you for your response.
I know that SQL can perform this task but i am not sure if SQL can perform the following task. As my project requires that system gradually and randomly changes the 0 to 1 based on percentage and put the outcome in SQL table.
For example, i could use the sql statement that you provide to change the normal table (t1) to 0 and 1. But because i need to randomly and gradually change the 0 values to 1 by 5%,10%,..., 90% and put each one in tables (t2),(t3),...,(t10). Based on limited knowledge, i think SQL has limitation to perform this task and i really do not know if sql can do so.
Nony KPosted Mar 17, 2011, 12:47 AM
Thank you for your response.
I know that SQL can perform this task but i am not sure if SQL can perform the following task. As my project requires that system gradually and randomly changes the 0 to 1 based on percentage and put the outcome in SQL table.
For example, i could use the sql statement that you provide to change the normal table (t1) to 0 and 1. But because i need to randomly and gradually change the 0 values to 1 by 5%,10%,..., 90% and put each one in tables (t2),(t3),...,(t10). Based on limited knowledge, i think SQL has limitation to perform this task and i really do not know if sql can do so.
Suthish NairPosted Mar 16, 2011, 11:50 PM
select
case when lname is null then '0' else '1' end lname,
case when fname is null then '0' else '1' end fname,
case when account_num is null then '0' else '1' end account_num
from table1
like this.. try it and lemme know.
Nony KPosted Mar 16, 2011, 10:59 PM
What i am trying to do is to convert all values (numeric and text) on the SQL table to (0,1) based on this condition IF there is value on field then convert it to 0 else convert it to (1). The problem is that i am not familiar in using C# but i have to write my code using this language. My code works well when access to SQL and write the outcome to text file but the problem is that located on the loop section as i loop section. There i would be grateful if you could assist me in designing this code in C#. I have attached the code
Regards
Suthish NairPosted Mar 16, 2011, 1:43 PM
Nony KPosted Mar 14, 2011, 1:49 AM
Hi Sam,
I am planning to use C# but some time i get confuse with C++ syntax. My code works properly when i just read SQL and write outcome to text file. but when i want change table values it give me some errors. Thank you very much for your advice.
Regards
Sam HobbsPosted Mar 14, 2011, 1:18 AM