[srinivasks33]

Hi,

I'm currently developing a Windows Form Application using C# and SQL server database.. I have a textbox on my form which has data in few lines and I wanted to store the data in SQL server database. Everything is working fine but the thing is I want to store each line of data into a separate row but I'm unable to do that. currently when I run a Program all the data is stored into a single row..

The code which I'm using is below..

Any help is appreciated.

            SqlConnection con;
            SqlCommand cmd;
            SqlDataAdapter sda;

            DataSet ds = new DataSet();
            string qry = "";
            
            qry = "INSERT INTO [Example].[dbo].[SystemConsole1]([time],[Message])VALUES(GETDATE(),'" + rtTerminal.Text + "')";
            con = new SqlConnection("Data Source=YOUR-0Z71CK0XVX\\SQLEXPRESS;Database=Example;Trusted_Connection=True");
            cmd = new SqlCommand(qry, con);
            if (con.State == ConnectionState.Open)
                con.Close();
            else
                con.Open();
            cmd.ExecuteNonQuery();
            con.Close();

rtTerminal is the name of the Textbox where the data is present

MOD EDIT: please use the code tags

This post has been edited by eclipsed4utoo: 26 October 2010 - 04:40 AM

[eclipsed4utoo]

you will need to split the text in the textbox on the carriage return, then do an insert for each line.

string text = rtTerminal.Text;
string[] myArray = text.Split(new string[] { "\r\n" }, StringSplitOptions.None);

foreach (string line in myArray)
{
    // do your insert
}

[srinivasks33]

eclipsed4utoo, on 26 October 2010 - 03:46 AM, said:

you will need to split the text in the textbox on the carriage return, then do an insert for each line.

string text = rtTerminal.Text;
string[] myArray = text.Split(new string[] { "\r\n" }, StringSplitOptions.None);

foreach (string line in myArray)
{
    // do your insert
}

Hi eclipsed4utoo,

I tried with the piece of code which you gave me but still my application reads all the data from the textbox into a same row... Please could you help me to resolve this issue...

[erburrell]

Can you post your new code? Getting eclipsed's code to work will depend on how you changed your code to use his statement...

Ed.

[srinivasks33]

Hi Eclipsed4utoo,
I'm very new to programming could you please suggest me how to split the text in the textbox

[srinivasks33]

erburrell, on 26 October 2010 - 05:04 AM, said:

Can you post your new code? Getting eclipsed's code to work will depend on how you changed your code to use his statement...

Ed.

 SqlConnection con;
            SqlCommand cmd;
            SqlDataAdapter sda;
            string qry = "";

            string text = "+ rtTerminal.Text +";
            string[] split = text.Split(new char[] { '>' });


            //string text = rtTerminal.Text;
            //string[] myArray = text.Split(new string[] { "\n" }, StringSplitOptions.None);

            foreach (string line in split)
            {
                // do your insert
                qry = "INSERT INTO dbo.SystemConsole1 (time, Message) VALUES( GETDATE(),'" + rtTerminal.Text + "')";
            }

            //qry = "INSERT INTO [Example].[dbo].[SystemConsole1]([time],[Message])VALUES(GETDATE(),'" + rtTerminal.Text + "')";
            con = new SqlConnection("Data Source=YOUR-0Z71CK0XVX\\SQLEXPRESS;Database=Example;Trusted_Connection=True");
            cmd = new SqlCommand(qry, con);
            if (con.State == ConnectionState.Open)
                con.Close();
            else
                con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
        }

[eclipsed4utoo]

Can you post the data that is in the TextBox? Is there a ">" character in the text?

[srinivasks33]

eclipsed4utoo, on 26 October 2010 - 05:55 AM, said:

Can you post the data that is in the TextBox? Is there a ">" character in the text?

Hi,

There is a character '<' in the text.. One more thing is this is not a fixed data but it is a data which I receive from a port.

The sample looks like this:

You are connected on COM3 at this time

Your connection was terminated at this time

<You are connected on COM3 at 26/10/2010 15:07:21>



<COM3 connection has terminated at 26/10/2010 15:07:22>
adcsqr
abrds #djdfk

This is the sample data..

[Curtis Rutland]

           foreach (string line in split)
           {
               // do your insert
               qry = "INSERT INTO dbo.SystemConsole1 (time, Message) VALUES( GETDATE(),'" + rtTerminal.Text + "')";
           }

This is your problem. There is no insert in this loop. All you do is recreate the string with different data, but you do nothing with it until after the last iteration. Also, do not use string concatenation for SQL.

EDIT:

           string text = "+ rtTerminal.Text +";
           string[] split = text.Split(new char[] { '>' });

This is also a problem. You're splitting the literal string "+ rtTerminal.Text +". Literally that, not what rtTerminal.Text contains.

Now I'm confused if you want to do this line by line, or on some other character. I'll assume line by line first.

Luckily, we don't even have to split TextBox data. It has a property called Lines.

I'm going to show you an example of how to do this, because it's important to know how to use Parameters instead of string concatenation.

private void button1_Click(object sender, EventArgs e)
{
    string connStr = "Data Source=SERVER;Initial Catalog=DATABASE;Integrated Security=SSPI;";
    string sql = "INSERT INTO Log (Timestamp, Message) VALUES (@timestamp, @message)";
    SqlConnection conn = new SqlConnection(connStr);
    try
    {
        foreach (string line in textBox1.Lines)
        {
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.Parameters.AddWithValue("@timestamp", DateTime.Now);
            cmd.Parameters.AddWithValue("@message", line);
            if(conn.State != ConnectionState.Open)
                conn.Open();
            cmd.ExecuteNonQuery();
        }
    }
    catch (Exception exc)
    {
        MessageBox.Show(exc.Message);
    }
    finally
    {
        if (conn.State != ConnectionState.Closed)
            conn.Close();
    }
    MessageBox.Show("Finished Inserting.");
}

You can't just copy and paste this, because some of the names are different. But you can see how this should be done.


Note that I'm executing the query inside the loop, not outside. I'm not appending text to form the query string, I'm using parameters.

This post has been edited by insertAlias: 26 October 2010 - 09:28 AM

[Curtis Rutland]

Also note that in this example, if there are empty lines in the text box, they will also be inserted into the DB. There are two ways you could handle this.

You could do something simple like this as the first line of the loop:

if (line == string.Empty)
    continue;

and it will jump to the next string without executing the loop for the current one.

Or, you can actually split the text around newlines:

string[] lines = textBox1.Text.Split(new string[] { Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries);
foreach (string line in lines)
...