How to use INSERT statement in SQL server compact

Question

3.50/5 (2 votes)

See more:

Please can someone help me with the correct syntax and command to INSERT each line of a RichTextBox into a database column called valid_reg. Also how to copy the contents of the column of the database into an array.

Posted 19-May-11 0:08am

Emejulu JVT

Add a Solution

3 solutions

Solution 3

The best way to insert such kind of data is to use datatable that hold all the record at a time and commit into the database at once.I am assuming the database table name is Registration.

/// <summary>
/// Insert Registration
/// </summary>
public void InsertRegistration()
{
    DataTable registration = new DataTable("Registration");
    using (SqlConnection connection = new SqlConnection("connectionString"))
    {
        connection.Open();
        SqlDataAdapter adapter = new SqlDataAdapter();
        // Just for initializing the datatable
        SqlCommand selectCommand = new SqlCommand("SELECT TOP 1 valid_reg FROM Registration", connection);
        adapter.SelectCommand = selectCommand;
        adapter.Fill(registration);
        // Insert each line of RichTextBox to a DataTable
        foreach (string line in richTextBox1.Lines)
        {
            DataRow newRegistration = registration.NewRow();
            newRegistration["valid_reg"] = line;
            registration.Rows.Add(newRegistration);
        }
        SqlCommand insertCommand = new SqlCommand("INSERT INTO Registration(valid_reg) VALUES(@valid_reg)", connection);
        SqlParameter parameter = new SqlParameter("@valid_reg", SqlDbType.NVarChar);
        insertCommand.Parameters.Add(parameter);
        adapter.InsertCommand = insertCommand;
        adapter.Update(registration); // Commit registration
    }
}

I hope this helps you well.

Posted 22-May-11 5:35am

Wonde Tadesse

Comments

Kim Togo 22-May-11 14:04pm

Good answer to, my 5. But I think you will get the same result regarding speed.
Perhaps a better way may be to begin a transaction, do all inserts and commit transaction.

Wonde Tadesse 6-Dec-11 10:49am

Thanks

Maazatron 7-Dec-11 9:31am

Hello wonde... good answer 5/5... i have another problem... could you pls come to http://www.codeproject.com/Questions/296397/Registration-of-login and check it out... thanks...

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Kim Togo · Accepted Answer · 2011-05-19T00:34:00

Solution 2

Get each line in the RichTextBox.Lines[^] and do a INSERT to SQL.

C#

sqlCon = new SqlConnection("ConnectionString");
sqlCon.Open();

foreach (string line in RichTextBox.Lines)
{
  sql = "INSERT INTO tbl (valid_reg) VALUES(@inputText)";
  sqlCmd = new SqlCommand(sql, sqlCon);
  sqlCmd.Parameters.AddWithValue("@inputText", line);
  sqlCmd.ExecuteNonQuery();
}

Posted 19-May-11 0:34am

Kim Togo

Updated 21-May-11 20:04pm

v2

Comments

Sandeep Mewara 19-May-11 7:00am

5 again!

Kim Togo 19-May-11 7:01am

Thanks

Wonde Tadesse 21-May-11 23:58pm

Can you explain how possible this works. :( Nahh

Kim Togo 22-May-11 2:03am

See updated answer.

Wonde Tadesse 22-May-11 11:35am

Now it makes sense.But as a suggestion this is not efficient way of adding such kind of records.It just commits for every RichTextBox line.

See my answer.

Kim Togo 22-May-11 14:10pm

Regarding speed, I do not think you gain any more speed on your answer. But it depends on how all the INSERT is committed to the database on call to adapter.Update(..)
A better way, can be to use transaction or use of multi values insert.

"INSERT INTO tbl (valid_reg) ('Data1'), ('Data2'), ('Data3'), ('Data4')"

parmar_punit · Accepted Answer · 2011-05-19T00:31:00

Solution 1

This is most common syntax of insert command...

INSERT INTO tableName (field1,field2,field3...fieldN) VALUES (value1,value2,value3....valueN);

if you can write your field name and textbox Name, i can give you exact syntax of Insert command....

Thanks & Regards,
Punit Parmar

Posted 19-May-11 0:31am

parmar_punit