none
I have been at this one issue for hours and can't really get past this last hump. The program code is RRS feed

  • Question

  • using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using System.Data.SqlClient;

    namespace simpledot
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }

            private void btnsubmit_Click(object sender, EventArgs e)
            {
                //ata Source=MAGI-PC;Initial Catalog=simple;Integrated Security=True
                SqlConnection sq = new SqlConnection("data Source=MAGI-PC;Initial Catalog=simple;Integrated Security=True");
                SqlDataAdapter sda = new SqlDataAdapter();
                sda.InsertCommand =new SqlCommand("insert into magi values (@name,@age)"+sq);
                sda.InsertCommand.Parameters.Add("@name", SqlDbType.VarChar).Value = txtname.Text;
                sda.InsertCommand.Parameters.Add("@age", SqlDbType.Int).Value = txtage.Text;
                sq.Open();

            

                sda.InsertCommand.ExecuteNonQuery();
                sq.Close();

            }
        }
    }
    Thursday, February 7, 2013 5:07 PM

Answers

  • Try this, it works..

    Just because it works, doesn't make it right.  One should never (never ever) build SQL statements using user-supplied input.  This has serious security issues.

    The parameterized query method Magidon is using is the proper approach.  Not only is this more secure, it can provide better performance, make the code easier to read and avoid issues like date formatting and decimal separators.

    Just a couple of minor mistakes in the code.  A comma instead of a plus sign needs to be specified on the SqlCommand constructor and the txtage string needs to be converted to int to match the target type:

                SqlConnection sq = new SqlConnection("Data Source=MAGI-PC;Initial Catalog=simple;Integrated Security=True");
                SqlDataAdapter sda = new SqlDataAdapter();
                sda.InsertCommand = new SqlCommand("insert into magi values (@name,@age)", sq);
                sda.InsertCommand.Parameters.Add("@name", SqlDbType.VarChar).Value = txtname.Text;
                sda.InsertCommand.Parameters.Add("@age", SqlDbType.Int).Value = int.Parse(txtage.Text);
                sq.Open();
                sda.InsertCommand.ExecuteNonQuery();
                sq.Close();

    Also, a data adapter isn't needed for just the insert.  If the only purpose is to insert a row, only the SqlCommand is needed:

                SqlConnection sq = new SqlConnection("Data Source=MAGI-PC;Initial Catalog=simple;Integrated Security=True");
                var command = new SqlCommand("insert into magi values (@name,@age)", sq);
                command.Parameters.Add("@name", SqlDbType.VarChar).Value = txtname.Text;
                command.Parameters.Add("@age", SqlDbType.Int).Value = int.Parse(txtage.Text);
                sq.Open();
                command.ExecuteNonQuery();
                sq.Close();
    
    
    


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Marked as answer by Alexander Sun Thursday, February 21, 2013 8:35 AM
    Sunday, February 10, 2013 2:35 PM

All replies

  • using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using System.Data.SqlClient;

    namespace simpledot
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }

            private void btnsubmit_Click(object sender, EventArgs e)
            {
                //ata Source=MAGI-PC;Initial Catalog=simple;Integrated Security=True
                SqlConnection sq = new SqlConnection("data Source=MAGI-PC;Initial Catalog=simple;Integrated Security=True");
                SqlDataAdapter sda = new SqlDataAdapter();
                sda.InsertCommand =new SqlCommand("insert into magi values (@name,@age)"+sq);
                sda.InsertCommand.Parameters.Add("@name", SqlDbType.VarChar).Value = txtname.Text;
                sda.InsertCommand.Parameters.Add("@age", SqlDbType.Int).Value = txtage.Text;
                sq.Open();

            

                sda.InsertCommand.ExecuteNonQuery();// error comes in this line
                sq.Close();

            }
        }
    }

    Thursday, February 7, 2013 5:08 PM
  • Try this, it works..

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using System.Data.SqlClient;
    
    namespace simpledot
    {
        public partial class WindowsFormsApplication14 : Form
        {
            public WindowsFormsApplication14()
            {
                InitializeComponent();
            }
    
            private void btnsubmit_Click(object sender, EventArgs e)
            {
                //ata Source=MAGI-PC;Initial Catalog=simple;Integrated Security=True
                SqlConnection sq = new SqlConnection("Server=MAGI-PC;Database=YOURDATABASE;Trusted_Connection=True");
                SqlDataAdapter sda = new SqlDataAdapter();
                sda.SelectCommand.CommandText = "insert into magi values ('"+txtname.Text"','"+txtname.Text+"')");
                sda.SelectCommand.Connection=sq;
                sda.SelectCommand.ExecuteNonQuery();
                sq.Close();
            }
        }
    }

    >> Tell code name of your database
    >> Use Select Command to insert command default.



    .NET


    Sunday, February 10, 2013 12:06 PM
  • Try this, it works..

    Just because it works, doesn't make it right.  One should never (never ever) build SQL statements using user-supplied input.  This has serious security issues.

    The parameterized query method Magidon is using is the proper approach.  Not only is this more secure, it can provide better performance, make the code easier to read and avoid issues like date formatting and decimal separators.

    Just a couple of minor mistakes in the code.  A comma instead of a plus sign needs to be specified on the SqlCommand constructor and the txtage string needs to be converted to int to match the target type:

                SqlConnection sq = new SqlConnection("Data Source=MAGI-PC;Initial Catalog=simple;Integrated Security=True");
                SqlDataAdapter sda = new SqlDataAdapter();
                sda.InsertCommand = new SqlCommand("insert into magi values (@name,@age)", sq);
                sda.InsertCommand.Parameters.Add("@name", SqlDbType.VarChar).Value = txtname.Text;
                sda.InsertCommand.Parameters.Add("@age", SqlDbType.Int).Value = int.Parse(txtage.Text);
                sq.Open();
                sda.InsertCommand.ExecuteNonQuery();
                sq.Close();

    Also, a data adapter isn't needed for just the insert.  If the only purpose is to insert a row, only the SqlCommand is needed:

                SqlConnection sq = new SqlConnection("Data Source=MAGI-PC;Initial Catalog=simple;Integrated Security=True");
                var command = new SqlCommand("insert into magi values (@name,@age)", sq);
                command.Parameters.Add("@name", SqlDbType.VarChar).Value = txtname.Text;
                command.Parameters.Add("@age", SqlDbType.Int).Value = int.Parse(txtage.Text);
                sq.Open();
                command.ExecuteNonQuery();
                sq.Close();
    
    
    


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    • Marked as answer by Alexander Sun Thursday, February 21, 2013 8:35 AM
    Sunday, February 10, 2013 2:35 PM