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

kmizle

SOLVED CLOSED ACTIVE ANSWERED
User: kmizle
Posted: on Sep 04, 2017 12:07 AM
Forum: Data Controls
Answer: 1
Views: 11611
Sample Code: Download

I have an asp.net web form page with 2 drop down lists on it. Each drop down list has the same values, they are just lists of all the active users in the database. I want the command to copy all the rows of data from user1 to user2. I have place this sql statement in SSMS and replaced the parameters with users and it ran fine, but I cannot get the command to work in visual studio. It runs fine, but doesn't grab the parameters, therefore nothing changes.

<asp:SqlDataSource ID="AssetSqlDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:meindbConnectionString %>"

        InsertCommand="INSERT INTO Assets (USERNAME, ASSETTAG,
        SERIALNUMBER, DEPT, CATEGORY, ITEMDESC, MODEL, ISSUEDATE, ISSUEDBY,
        TOTALVALUE, ACCESSORIES, ISSUECONDITION, NOTES, CREATEBY, CREATEDATE)
        SELECT @USERNAME2, ASSETTAG, SERIALNUMBER, DEPT, CATEGORY, ITEMDESC, MODEL,
        ISSUEDATE, ISSUEDBY, TOTALVALUE, ACCESSORIES, ISSUECONDITION, NOTES,
        CREATEBY, CREATEDATE FROM ASSETS WHERE USERNAME = @USERNAME">

        <InsertParameters>

            <asp:ControlParameter ControlID="ContentPlaceHolder1$WebGroupBox1$FormView1$UserWebDropDown" Name="USERNAME" PropertyName="SelectedValue" Type="String" />
            <asp:ControlParameter ControlID="ContentPlaceHolder1$WebGroupBox1$FormView1$WebDropDown1" Name="USERNAME2" PropertyName="SelectedValue" Type="String" />
            <asp:Parameter Name="ASSETTAG" Type="String" />
            <asp:Parameter Name="SERIALNUMBER" Type="String" />
            <asp:Parameter Name="DEPT" Type="String" />
            <asp:Parameter Name="ITEMDESC" Type="String" />
            <asp:Parameter Name="MODEL" Type="String" />
            <asp:Parameter Name="ISSUEDATE" Type="DateTime" />
            <asp:Parameter Name="ISSUEDBY" Type="String" />
            <asp:Parameter Name="CATEGORY" Type="String" />
            <asp:Parameter Name="TOTALVALUE" Type="Int32" />
            <asp:Parameter Name="ACCESSORIES" Type="String" />
            <asp:Parameter Name="ISSUECONDITION" Type="String" />
            <asp:Parameter Name="NOTES" Type="String" />
            <asp:Parameter Name="CREATEDATE" Type="DateTime" />
            <asp:ControlParameter ControlID="UsernameHiddenField" Name="CREATEBY" PropertyName="Value" Type="String" />
            <asp:Parameter Direction="Output" Name="ID" Type="Int32" />

        </InsertParameters>

Does anyone have any suggestions on why it is not working? thank you in advance for your responses

kmizle

0 3

Reply
User: kmizle
Replied: on Sep 05, 2017 07:01 AM
Modified: on Sep 05, 2017 08:20 AM
Report

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

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

Company

Explore

Follow us

Contact Us