Inserting into SQL Server database ASP.net

Question

What I'm trying to do is to insert a username, and their monthly hour limit to my SQL Server database. I've used the automatically generated statements for updating and deleting. I just need to add in new users now. The code below, should work as far as I know, but it doesn't. I think it's the way I've written it.

The part in comments is what the Userdata.aspx file automatically generated, so I'm trying to convert it to use my 2 text boxes.

Thanks a lot.

protected void Button1_Click1(object sender, EventArgs e)
{
     string sql = "INSERT INTO [UserData]([UserName], [MonthlyHourLimit]) VALUES ("+ TextBox1.Text + "," + TextBox2.Text + ")";

     //INSERT INTO [UserData] ([UserName], [MonthlyHourLimit]) VALUES (@UserName, @MonthlyHourLimit)" 
     SqlDataSource1.InsertCommand = sql;
     GridView1.DataBind();
}

THe first thing you should take care about is SQL injection. You'd better created a SqlCommand class isntance with parameters for every value, read from user. Though it's not probably your current problem — Sasha
– Sasha, Commented Mar 15, 2013 at 15:15
I'm not getting an error, just nothing happens when my button is clicked. — Rhys Drury
– Rhys Drury, Commented Mar 15, 2013 at 16:11
The generated code is much better than yours - it uses parameters to avoid concatenating together SQL statements and thus opening the door to SQL injection attacks - just don't do it! - never. — marc_s
– marc_s, Commented Mar 15, 2013 at 16:16
But the generated code doesn't help me because I can't get the data from the boxes and insert on button1_click's event — Rhys Drury
– Rhys Drury, Commented Mar 15, 2013 at 16:39

vittore · Accepted Answer · 2013-03-15 18:00:09Z

5

You need to configure your data source to use parameters.

 <asp:sqlDataSource ID="EmployeeDetailsSqlDataSource" 
   SelectCommand="select [UserName], [MonthlyHourLimit] from [UserData] where UserName= @UserName"

   InsertCommand="IINSERT INTO [UserData] ([UserName], [MonthlyHourLimit]) VALUES (@UserName, @MonthlyHourLimit);"

   ConnectionString="<%$ ConnectionStrings:MyConnection %>"
   RunAt="server">

   <SelectParameters>
      <asp:Parameter Name="UserName" Type="Int32" DefaultValue="0" />
   </SelectParameters>

   <InsertParameters>
      <asp:Parameter Name="UserName" Direction="Input" Type="String" />
      <asp:Parameter Name="MonthlyHourLimit" Direction="Input" Type="String" />
   </InsertParameters>

 </asp:sqlDataSource>

UPDATE:I've forgot to mention, you would like to use ControlParameter and not simple Parameter. Take a look at following snippet:

  <asp:СontrolParameter Name="UserName" ControlId="ddlUserNames" PropertyName="SelectedValue"/>

  ...

  <asp:DropdownList
      ID="ddlUserNames"
      runat="server"
      Autopostback="True">
      <asp:Listitem Selected="True">Users</asp:Listitem>
      <asp:Listitem Value="Peter">Peter</asp:Listitem>
      <asp:Listitem Value="Jessica">Jessica</asp:Listitem>
  </asp:Dropdownlist>

Take a look at corresponding MSDN page describing usage of SqlDataSource in details.

UPDATED 2: complete example in order to avoid confusion

 <asp:sqlDataSource ID="EmployeeDetailsSqlDataSource" 
                    SelectCommand="select [UserName], [MonthlyHourLimit] from [UserData] where UserName= @UserName"
                    InsertCommand="IINSERT INTO [UserData] ([UserName], [MonthlyHourLimit]) VALUES (@UserName, @MonthlyHourLimit);"

                    ConnectionString="<%$ ConnectionStrings:MyConnection %>"
                    RunAt="server">
      <SelectParameters>
          <asp:Parameter Name="UserName" Type="Int32" DefaultValue="0" />
      </SelectParameters>
      <InsertParameters>
          <asp:ControlParameter Name="UserName" ControlId="txtUserName" Direction="Input" Type="String" />
          <asp:ControlParameter Name="MonthlyHourLimit" ControlId="txtMonthlyHourLimit" Direction="Input" Type="String" />
      </InsertParameters>
 </asp:sqlDataSource>

 <asp:TextBox runat="server" ID="txtUserName" /> 
 <asp:TextBox runat="server" ID="txtMonthlyHourLimit" />

edited Mar 15, 2013 at 18:00

answered Mar 15, 2013 at 15:21

vittore

17.6k6 gold badges46 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

tgolisch Over a year ago

#Vittore, this approach is MUCH better and safer than trying to build a clean/safe dynamic SQL statement.

vittore Over a year ago

@tgolisch belive me, I know!

Rhys Drury Over a year ago

So if you make it use parameters in the aspx. How do you then defined them and use them in the .cs file?

vittore Over a year ago

You can define ControlParameter so you don't need code or you can define general parameter so you can set it from code behind, there is details explanation in second link I gave in my answer. Most likely though that you don't need to change it in code-behind for such case and instead going to use combination of ControlParameter, SessionParameter etc.

Rhys Drury Over a year ago

Sorry, I still don't understand how I can get whatever is on those boxes into the database by clicking my button

|

Chains · Accepted Answer · 2013-03-15 15:17:28Z

0

Datasource.InsertCommand is a property.
Datasource.Insert() is a method.

You should also use parameters.

datasource.insertparameters("username").defaultvalue = TextBox1.Text + "," + TextBox2.Text

answered Mar 15, 2013 at 15:17

Chains

13.2k8 gold badges48 silver badges62 bronze badges

1 Comment

Rhys Drury Over a year ago

But insertparameters isn't a method either?

Collectives™ on Stack Overflow

Inserting into SQL Server database ASP.net

2 Answers 2

9 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related