none
Reading from one Datatable and storeing in other datatable ? RRS feed

  • Question

  • hi...

    I'm reading text file and storing those data into dt1.....In text file there is raw data I mean if there is 3 rows firname,lastname,id....In dt1 there can be some values null

    now I have table in database name student . which have id column is NOT NULL....

    when I start reading each row from the dt1 and try to insert it to student table there comes error for column in which there is null value for ID...

    I want to skip that row which is not valid for insertion in student table .

    for this I have write something like this....

     OleDbConnection con = new OleDbConnection(constr);                    
                OleDbCommand cmd = new OleDbCommand("select * from[test.txt]");          
                con.Open();          
                System.Data.DataTable dt = new System.Data.DataTable();
                dt.Load(cmd.ExecuteReader());
                Console.WriteLine("Data Loaded");
                con.Close();
                Console.WriteLine("Connection Obj Closed");
                DataTable test = new DataTable();
    
    
    using(SqlConnection con1= new SqlConnection("Data Source=srv1;Initial Catalog=test;Integrated Security=True"))
                {
    
                    try
                    {
                     
                        using(SqlDataAdapter sda= new SqlDataAdapter("select top 0* from student",con1))
                        {
                            
                                sda.FillSchema(student,SchemaType.Mapped);
                             
                           
                           
                            foreach (DataRow dr in dt.Rows)
                            {
                                fname = dr.ItemArray[0].ToString();
                                lname = dr.ItemArray[1].ToString();
                                val = Convert.ToInt32(dr["val"]);
                                try
                                {
    
                                    test.ImportRow(dr);
                                    
                                }
                                catch(Exception ex)
                                {
                                  
                                    Console.WriteLine(dr.ToString());
                                }                 
                            }
                            foreach (DataRow dr in student.Rows)
                            {
                                Console.WriteLine("from test");
    
                                fname = dr.ItemArray[0].ToString();
                                lname = dr.ItemArray[1].ToString();
                                val = Convert.ToInt32(dr["val"]);
    
                                Console.WriteLine(fname);
                                Console.WriteLine(lname);
                                Console.WriteLine(val);
                            }
                        }
                       
                    }
                    catch(SqlException sex)
                    {
    
                    }
                    finally
                    {
    
                    }
                   
    
                }

    Exception is....NoNullAllowedException 

    and no data inserted in student table ...there is now rows in student in strudent ...when i loop through that table . 

    remarkeble thing is when i debug there is values in each variable fname ,lname and val .

     

     



    • Edited by .netaholic Thursday, August 16, 2012 8:16 AM
    Thursday, August 16, 2012 8:10 AM

Answers

  • I'm confused. You mention 3 columns, firstname, last name and id. But yet your code has a column called "val", nothing called "id". So, I assume the "val" column is the ID that you're referring to, and that contains a null value?

    I think that your exception is probably caused by this line:

    val = Convert.ToInt32(dr["val"]);

    Since you want to skip the row if it has a null value, then what you probably want to do is use int.TryParse() to see if the "val" column is a number and only Import if it is:

    foreach (DataRow dr in dt.Rows)
    {
        try
        {
            if (int.TryParse(dr["val"].ToString(), out val))
                test.ImportRow(dr);
        }
        catch(Exception ex)
        {
            Console.WriteLine(dr.ToString());
        }                 
    }


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Sunday, August 19, 2012 11:04 PM

All replies

  • First try to select not null values from dt1,

    Second If you can't please try to use the check the is null value before execute the insert statement.

    create procedure spTestPro
    @VAL varchar(10)
    as
    begin
    	if @VAL is null
              	print  'NULL'
    	else
    		print 'Not NUll'
    end
    	
    --execute spTestPro @VAL=null

    Third : Insert some static value to the NOt NULL column by using the

    select ISNULL(null,'To Delete') and delete the rows using the satatic value


    With Thanks and Regards
    Sambath Raj.C
    click "Proposed As Answer by" if this post solves your problem or "Vote As Helpful" if a post has been useful to you
    Happy Programming!

    Thursday, August 16, 2012 10:05 AM
  • I'm confused. You mention 3 columns, firstname, last name and id. But yet your code has a column called "val", nothing called "id". So, I assume the "val" column is the ID that you're referring to, and that contains a null value?

    I think that your exception is probably caused by this line:

    val = Convert.ToInt32(dr["val"]);

    Since you want to skip the row if it has a null value, then what you probably want to do is use int.TryParse() to see if the "val" column is a number and only Import if it is:

    foreach (DataRow dr in dt.Rows)
    {
        try
        {
            if (int.TryParse(dr["val"].ToString(), out val))
                test.ImportRow(dr);
        }
        catch(Exception ex)
        {
            Console.WriteLine(dr.ToString());
        }                 
    }


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Sunday, August 19, 2012 11:04 PM
  • If you want to skip that row which is not valid for insertion in student table, you should add a try catch inside foreach when iterate through the student table.

    Besides, I can't see any code may throw the NoNullAllowedException in the student foreach statement. Did you modified the code before you post? Which link throw this exception?

    Best Regards,


    Bob Wu [MSFT]
    MSDN Community Support | Feedback to us

    Tuesday, August 21, 2012 11:34 AM
    Moderator
  • Bob ... just my 2 cents, but a try/catch should not really be used in situations where you know what the problem might be and can test for it. Testing for an invalid value is always preferred to just wrapping it all in a try/catch. Catching exceptions is expensive!

    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Tuesday, August 21, 2012 10:29 PM
  • Hi Bonnie,

    I agree that Testing for an invalid value is always preferred to just wrapping it all in a try/catch. I read the code but not sure which party would throw this exception. I think there may be other unknown exceptions, For example, val is not a column of the student or the value of dr["val"] can't be convert to int.

    Anyway, thank you for the explanation.

    Have a nice day.


    Bob Wu [MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, August 22, 2012 5:20 AM
    Moderator