why There is diffreant between using sqlcommand object and datasource object RRS feed

  • Question

  • Hi EveryBody:

    Whey when I use the following code trying to inser my data to database it did not work:

    Dim mydatasource As New SqlDataSource
    mydatasource.ConnectionString = ConfigurationManager.ConnectionStrings("LocalSqlServer").ToString
    mydatasource.InsertCommandType = SqlDataSourceCommandType.Text
    mydatasource.InsertCommand = "INSERT INTO  husam_Tab(user_Name,user_Password,user_Email,user_Squestion,user_Sanswer) Values(@user_Name,@user_Password,@user_Email,@user_Squestion,@user_Sanswer)"
    mydatasource.InsertParameters.Add("user_Name", TextBox1.Text)
    mydatasource.InsertParameters.Add("user_Password", TextBox2.Text)
    mydatasource.InsertParameters.Add("user_Email", TextBox4.Text)
    mydatasource.InsertParameters.Add("user_Squestion", TextBox5.Text)
    mydatasource.InsertParameters.Add("user_Sanswer", TextBox6.Text)

    While Whe I use the foolowing code to insert the same data to my database Its Work:

     Dim cmd As SqlCommand
     Dim scon As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("LocalSqlServer").ConnectionString)
    Dim sql As String
    sql = "INSERT INTO husam_Tab(user_Name,user_Password,user_Email,user_Squestion,user_Sanswer)" + "Values('" & TextBox1.Text & "','" & TextBox2.Text & "','" & TextBox4.Text & "','" & TextBox5.Text & "','" & TextBox6.Text & "')"
    cmd = New SqlCommand(sql, scon)


    I ma using SQL server 2005 Not Express edition so my database file it is not attached to my solution.

    Any help will be completlly appreciated


    Saturday, August 18, 2007 6:27 AM

All replies

  • Looking at the SqlDataSource i get the feeling that the class is not intended to be used by a developer himself to issue sql commands, but that it should be left to the infrastructure... Inserting real parameter values (thus not setting the default values which you have demonstrated) seems to happen through the framework... Also, the ExecuteInsert method is protected, thus it doesn't even give you a public method to make it work.

    The second approach,  concatenating values into the query, is a big NO-NO (too much sql injection potential) too.In this case you still have to use parameter binding:

    Code Snippet

    SqlCommand cmd = sqlConnection.CreateCommand();
    cmd.CommandText = "INSERT INTO xxx ( column1 ) VALUES ( @value1 )";
    cmd.Parameters.Add("@value1", SqlDbType.String).Value = TextBox1.Text;

    Saturday, August 18, 2007 8:21 AM