Insert record in database with Parameters and display in GridView using SqlDataSource in ASP.Net

Hi kmizle,

I have created sample that full fill your requirement.

HTML

<div>
    <asp:GridView runat="server" ID="gvEmployees" AutoGenerateColumns="False" DataSourceID="SqlDataSource2">
        <Columns>
            <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name" />
            <asp:BoundField DataField="Designation" HeaderText="Designation" SortExpression="Designation" />
            <asp:BoundField DataField="GroupName" HeaderText="GroupName" SortExpression="GroupName" />
        </Columns>
    </asp:GridView>
    <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:meindbConnectionString %>"
        SelectCommand="SELECT Name,Designation,GroupName FROM [Employee]"></asp:SqlDataSource>
    <br />
    <table>
        <tr>
            <td>
                user1
                <asp:DropDownList runat="server" ID="UserWebDropDown">
                    <asp:ListItem Text="John Tom" Value="John Tom" />
                    <asp:ListItem Text="P K Johns" Value="P K Johns" />
                    <asp:ListItem Text="Kevin Ob" Value="Kevin Ob" />
                    <asp:ListItem Text="Rose Marry" Value="Rose Marry" />
                    <asp:ListItem Text="Jony Cater" Value="Jony Cater" />
                </asp:DropDownList>
            </td>
            <td>
                user2
                <asp:DropDownList runat="server" ID="WebDropDown1">
                    <asp:ListItem Text="John Tom" Value="John Tom" />
                    <asp:ListItem Text="P K Johns" Value="P K Johns" />
                    <asp:ListItem Text="Kevin Ob" Value="Kevin Ob" />
                    <asp:ListItem Text="Rose Marry" Value="Rose Marry" />
                    <asp:ListItem Text="Jony Cater" Value="Jony Cater" />
                </asp:DropDownList>
            </td>
            <td>
                <asp:Button ID="btnAdd" runat="server" Text="Add" OnClick="Insert" />
            </td>
        </tr>
    </table>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:meindbConnectionString %>"
        InsertCommand="INSERT INTO Employee (Name,Designation,GroupName) SELECT @Name2,Designation,GroupName FROM Employee WHERE Name = @Name">
        <InsertParameters>
            <asp:ControlParameter ControlID="UserWebDropDown" Name="Name" PropertyName="SelectedValue"
                Type="String" />
            <asp:ControlParameter ControlID="WebDropDown1" Name="Name2" PropertyName="SelectedValue"
                Type="String" />
            <asp:Parameter Name="Designation" Type="String" />
            <asp:Parameter Name="GroupName" Type="String" />
        </InsertParameters>
    </asp:SqlDataSource>
</div>

C#

protected void Insert(object sender, EventArgs e)
{
    SqlDataSource1.Insert();
    gvEmployees.DataBind();
}

VB.Net

Protected Sub Insert(sender As Object, e As EventArgs)
	SqlDataSource1.Insert()
	gvEmployees.DataBind()
End Sub

Screenshot

Thank you dharmendr for such a detailed and clear response, unfortunately my code is still not working. It's telling me that there was rows affected, but there is nothing being inserted into the new user

This is how my drop downs are coded and the code behind that is running when I click on the button.

<ig:WebDropDown ID="UserWebDropDown" EnableAutoCompleteFirstMatch="true" EnableAutoFiltering="Client" EnableCustomValues="False"
     AutoSelectOnMatch="True" EnableRenderingAnchors="False" DropDownContainerWidth="190px" DataSourceID="UserSqlDataSource"
     AppendDataBoundItems="true" runat="server" Width="60%" ValueField="USERNAME" TextField="FULL_NAME" SelectedValue='<%# Bind("USERNAME") %>'>
</ig:WebDropDown>


Protected Sub AssetSqlDataSource_Inserted(sender As Object, e As SqlDataSourceStatusEventArgs) Handles AssetSqlDataSource.Inserted
   Label9.Text = e.AffectedRows & " Asset Inserted"
   Label9.ForeColor = Drawing.Color.Red        
   ScriptManager.RegisterStartupScript(Page, GetType(String), "FakeKey", "ExitFrameRefresh()", True)
   Panel1.Visible = False
End Sub    

Protected Sub AssetSqlDataSource_Inserting(sender As Object, e As SqlDataSourceCommandEventArgs) Handles AssetSqlDataSource.Inserting
   e.Command.Parameters("@CREATEDATE").Value = Now
   e.Command.Parameters("@ISSUEDATE").Value = Now
   e.Command.Parameters("@CREATEBY").Value = Session("USERNAME")
End Sub    

Protected Sub Insert(sender As Object, e As EventArgs)
   AssetSqlDataSource.Insert()
End Sub

Hi kmizle,

As you can see i have provided you the working sample. It is not possible to check your code remotely. So use try catch block the catch the error details like below and check the error details.

Protected Sub Insert(sender As Object, e As EventArgs)
	Try
		AssetSqlDataSource.Insert()
	Catch ex As Exception
		ScriptManager.RegisterStartupScript(Page, Me.[GetType](), "", "alert('" + ex.Message + "')", True)
	End Try
End Sub

I have found that my parameters aren't being passed into the statement.