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();
}

• goric: What doesn't work about this? Are you getting an error?
• Sasha: 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
• Rhys Drury: I'm not getting an error, just nothing happens when my button is clicked.
• marc_s: 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.
• Rhys Drury: 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

Answer 1

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" />

• tgolisch: #Vittore, this approach is MUCH better and safer than trying to build a clean/safe dynamic SQL statement.
• vittore: @tgolisch belive me, I know!
• Rhys Drury: So if you make it use parameters in the aspx. How do you then defined them and use them in the .cs file?
• vittore: 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: Sorry, I still don't understand how I can get whatever is on those boxes into the database by clicking my button

Answer 2

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

You should also use parameters.

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

• Rhys Drury: But insertparameters isn't a method either?