Insert row to SQL Server using Visual Studio 2010 and ASP.NET

Question

Alright I have a question that seems simple but I haven't been able to figure it out.

Just some quick details: I am using Visual Studio 2010 with ASP.NET, VB, and using SQL Server as my database

I am creating a website using ASP.NET and I have a mailing list database, I have a add to mailing list page that takes in certain fields (Name, e-mail, phone number) that are TextBox and a sumbit button. When the submit button is clicked how to do I take the current values of those text fields and add them to the database.

I have done it using the DetailedView which works at adding to the database but I also want to redirect to a different page after inserting.

So my question basically is how to take the values from the TextBox and insert them into the database, or how to I redirect to a different page after inserting a new row with DetailedView.

Here is my code with for the .aspx file, it has both the TextBox's and the detailed view

 <p class=whiteMail>
    First Name: <asp:TextBox ID="FirstName" runat="server"></asp:TextBox> <br />
    Last Name: <asp:TextBox ID="LastName" runat="server"></asp:TextBox> <br />
    E-Mail: <asp:TextBox ID="Email" runat="server"></asp:TextBox> <br />
    Phone Number: <asp:TextBox ID="PhoneNumber" runat="server"></asp:TextBox> <br />

    <asp:Button ID="Submit" runat="server" Text="Button" />
    <br />

    <asp:DetailsView ID="DetailsView2" runat="server" Height="50px" Width="125px" 
        DataSourceID="SqlDataSource1" AutoGenerateRows="False" CellPadding="4" 
        DataKeyNames="FirstName,PhoneNum,LastName" DefaultMode="Insert" 
        ForeColor="#333333" GridLines="None">
        <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
        <CommandRowStyle BackColor="#E2DED6" Font-Bold="True" />
        <EditRowStyle BackColor="#999999" />
        <FieldHeaderStyle BackColor="#E9ECF1" Font-Bold="True" />
        <Fields>
            <asp:BoundField DataField="FirstName" HeaderText="First Name" ReadOnly="True" 
                SortExpression="FirstName" />
            <asp:BoundField DataField="LastName" HeaderText="Last Name" ReadOnly="True" 
                SortExpression="LastName" />
            <asp:BoundField DataField="PhoneNum" HeaderText="Phone Number" ReadOnly="True" 
                SortExpression="PhoneNum" />
            <asp:BoundField DataField="Email" HeaderText="Email" SortExpression="Email" />
            <asp:CommandField ShowCancelButton="False" ShowInsertButton="True" />
        </Fields>
        <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
        <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
        <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
        <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
    </asp:DetailsView>

    <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
        ConnectionString="<%$ ConnectionStrings:Database1ConnectionString2 %>" 
        DeleteCommand="DELETE FROM [MailList] WHERE [FirstName] = @FirstName AND [PhoneNum] = @PhoneNum AND [LastName] = @LastName" 
        InsertCommand="INSERT INTO [MailList] ([FirstName], [PhoneNum], [Email], [LastName]) VALUES (@FirstName, @PhoneNum, @Email, @LastName)" 
        SelectCommand="SELECT [FirstName], [PhoneNum], [Email], [LastName] FROM [MailList]" 
        UpdateCommand="UPDATE [MailList] SET [Email] = @Email WHERE [FirstName] = @FirstName AND [PhoneNum] = @PhoneNum AND [LastName] = @LastName">
        <DeleteParameters>
            <asp:Parameter Name="FirstName" Type="String" />
            <asp:Parameter Name="PhoneNum" Type="String" />
            <asp:Parameter Name="LastName" Type="String" />
        </DeleteParameters>
        <InsertParameters>
            <asp:Parameter Name="FirstName" Type="String" />
            <asp:Parameter Name="PhoneNum" Type="String" />
            <asp:Parameter Name="Email" Type="String" />
            <asp:Parameter Name="LastName" Type="String" />
        </InsertParameters>
        <UpdateParameters>
            <asp:Parameter Name="Email" Type="String" />
            <asp:Parameter Name="FirstName" Type="String" />
            <asp:Parameter Name="PhoneNum" Type="String" />
            <asp:Parameter Name="LastName" Type="String" />
        </UpdateParameters>
    </asp:SqlDataSource>

    <br />
</p>

And here is the .vb code

Public Class add
Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

End Sub


Protected Sub Submit_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Submit.Click
    Dim strURL As String = "message.aspx?firstname=" & FirstName.Text & "&lastname=" & LastName.Text()

    Response.Redirect(strURL)
End Sub

Any help or suggestions would be great

Answer 1

I am not sure if I understood well, but I believe that your approach is not correct. Why you are using the textboxes in the beginning of the code?

You can achieve nicely what you want by using the full capability of the DetailsView. For example you can do something like this:

<asp:DetailsView ID="DetailsView2" runat="server" Height="50px" Width="125px" 
    DataSourceID="SqlDataSource1" AutoGenerateRows="False" CellPadding="4" 
    DataKeyNames="FirstName,PhoneNum,LastName" DefaultMode="Insert" 
    ForeColor="#333333" GridLines="None">
    <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
    <CommandRowStyle BackColor="#E2DED6" Font-Bold="True" />
    <EditRowStyle BackColor="#999999" />
    <FieldHeaderStyle BackColor="#E9ECF1" Font-Bold="True" />
    <Fields>
        <asp:TemplateField HeaderText="FirstName" SortExpression="FirstName">
        <ItemTemplate>
            <asp:Label ID="Label1" runat="server" 
                    Text='<%# Bind("FirstName") %>'></asp:Label>
        </ItemTemplate>
        <EditItemTemplate>
            <asp:TextBox ID="TextBox1" runat="server" 
                    Text='<%# Bind("FirstName") %>'></asp:TextBox>
        </EditItemTemplate>
        <InsertItemTemplate>
            <asp:TextBox ID="TextBox1" runat="server" 
                    Text='<%# Bind("FirstName") %>'></asp:TextBox>
        </InsertItemTemplate>
     </asp:TemplateField>
         <asp:TemplateField HeaderText="LastName" SortExpression="LastName">
        <ItemTemplate>
            <asp:Label ID="Label2" runat="server" 
                    Text='<%# Bind("LastName") %>'></asp:Label>
        </ItemTemplate>
        <EditItemTemplate>
            <asp:TextBox ID="TextBox2" runat="server" 
                    Text='<%# Bind("LastName") %>'></asp:TextBox>
        </EditItemTemplate>
        <InsertItemTemplate>
            <asp:TextBox ID="TextBox2" runat="server" 
                    Text='<%# Bind("LastName") %>'></asp:TextBox>
        </InsertItemTemplate>
     </asp:TemplateField>
         <asp:TemplateField HeaderText="PhoneNum" SortExpression="PhoneNum">
        <ItemTemplate>
            <asp:Label ID="Label3" runat="server" 
                    Text='<%# Bind("PhoneNum") %>'></asp:Label>
        </ItemTemplate>
        <EditItemTemplate>
            <asp:TextBox ID="TextBox3" runat="server" 
                    Text='<%# Bind("PhoneNum") %>'></asp:TextBox>
        </EditItemTemplate>
        <InsertItemTemplate>
            <asp:TextBox ID="TextBox3" runat="server" 
                    Text='<%# Bind("PhoneNum") %>'></asp:TextBox>
        </InsertItemTemplate>
      </asp:TemplateField>
          <asp:TemplateField HeaderText="Email" SortExpression="Email">
        <ItemTemplate>
            <asp:Label ID="Label4" runat="server" 
                    Text='<%# Bind("Email") %>'></asp:Label>
        </ItemTemplate>
        <EditItemTemplate>
            <asp:TextBox ID="TextBox4" runat="server" 
                    Text='<%# Bind("Email") %>'></asp:TextBox>
        </EditItemTemplate>
        <InsertItemTemplate>
            <asp:TextBox ID="TextBox4" runat="server" 
                    Text='<%# Bind("Email") %>'></asp:TextBox>
        </InsertItemTemplate>
      </asp:TemplateField>
       <asp:CommandField ShowCancelButton="False" ShowEditButton="True" ShowInsertButton="True" />
    </Fields>
    <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
    <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
    <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
    <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
</asp:DetailsView>

And then in you code behind you can use the events of the DetailsView to do the redirect like this:

Protected Sub DetailsView1_ItemUpdated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewUpdatedEventArgs) Handles DetailsView1.ItemUpdated
    EndEditing()
End Sub

Protected Sub DetailsView1_ItemInserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewInsertedEventArgs) Handles DetailsView1.ItemInserted
    EndEditing()
End Sub

Private Sub EndEditing()
    Response.Redirect("Default.aspx")
End Sub

I just used a the default.aspx for the redirect, but you can use any other page to redirect.

I hope that this can help you.