DB not updating using gridview with MS access vb.net

Question

User1249916507 posted

Hey guys i'm having an issue with my code here. Basically, would like to put in a gridview the rows from the DB that have the same state as selected on a DropDownlist. Also, i would like to be able to modify them. The select command works fine but the update is not working. When i click, it refresh the page(Autopostback) but nothing happens on the gridview. I really tried everything but it's not making any sense to me. This is the markup:

<form runat="server" autocomplete="off">
<br>
<asp:dropdownlist runat="server" id="Ddl1" OnSelectedIndexChanged="admvis"  Autopostback="True" >
    <asp:listitem>-</asp:listitem>
    <asp:listitem>In Configuration</asp:listitem>
    <asp:listitem>Configured</asp:listitem>
    <asp:listitem>Ordered</asp:listitem>
    <asp:listitem>Shipped</asp:listitem>
</asp:dropdownlist>

<asp:sqlDataSource id="SqlDataSource2"
               SelectCommand="SELECT Distinct State From PCRequests"
               runat="server"
               ConnectionString="<%$ connectionStrings:CyberTailorsDB%>"
               ProviderName="<%$ connectionStrings:CyberTailorsDB.providerName %>">
</asp:sqlDataSource> 


<asp:sqlDataSource id="SqlDataSource1"
               runat="server"
               SelectCommand=""
               UpdateCommand=""
               ConnectionString = "<%$ connectionStrings:CyberTailorsDB%>"
               ProviderName = "<%$ connectionStrings:CyberTailorsDB.providerName %>">

               <UpdateParameters>
                    <asp:Parameter Name="PCUse" Type="String"/>
                    <asp:Parameter Name="OS" Type="String"/>
                    <asp:Parameter Name="ExtDev" Type="String"/>
                    <asp:Parameter Name="Requests" Type="String"/>
                    <asp:Parameter Name="Antivirus" Type="String"/>
                    <asp:Parameter Name="Budget" Type="String"/>
               </UpdateParameters>

</asp:sqlDataSource>



<asp:gridview id="admgridview" runat="server" OnRowUpdating="Rowup" AutoGenerateColumns="False" DataSourceID="SqlDataSource1" ForeColor="white" width="100%">
    <HeaderStyle ForeColor="#00bbff"/>
    <Columns>
    <asp:CommandField ShowEditButton="True" EditText="Modify" UpdateText="Update" CancelText="Cancel" />

        <asp:BoundField DataField="ID" HeaderText="ID" ReadOnly="true"/>
        <asp:BoundField DataField="PCUse" HeaderText="PCUse" ItemStyle-Width="18%"/>
        <asp:BoundField DataField="OS" HeaderText="Operating System" ItemStyle-Width="18%"/>
        <asp:BoundField DataField="ExtDev" HeaderText="External Devices" ItemStyle-Width="18%"/>
        <asp:BoundField DataField="Requests" HeaderText="Particular Requests" ItemStyle-Width="18%" />
        <asp:BoundField DataField="Antivirus" HeaderText="Antivirus" ItemStyle-Width="5%"/>
        <asp:BoundField DataField="Budget" HeaderText="Budget" ItemStyle-Width="5%" />
        <asp:BoundField DataField="State" HeaderText="State" ItemStyle-Width="13%" readonly="true"/>


    </Columns>
</asp:gridview>
</form>

Here is behind code:

Dim strSQL As String
Dim index As Integer

Sub Page_Load(ByVal sender as Object, Byval e As EventArgs)

  If Session("log") = 1 Then
   login.innerHTML = "You are logged in as " & Session("user")
   change.href="./gtetrue.aspx"
   lout.innerHTML = "Log out"
  End If

  If Session("user") <> "Adm"
    Response.Redirect("./index.aspx")
  End if
End Sub

Sub sbout(ByVal sender as Object, Byval e As EventArgs)
    login.innerHTML = "You are not logged in at the moment"
    Session("log") = 0
    lout.innerHTML = "Sign in"
    Response.Redirect("./gtefalse.aspx")
End Sub

Sub admvis(Byval sender as Object, Byval e As EventArgs) 
    SqlDataSource1.SelectParameters.Add("status", Ddl1.SelectedValue.ToString())
    strSQL = "SELECT * FROM PCRequests WHERE State = [@status] Order By ID"
    SqlDataSource1.SelectCommand = strSQL
    admgridview.DataSourceID = "SqlDataSource1"
End Sub

Sub Rowup(Byval sender as Object, Byval e As EventArgs)
    Dim strSQL As String
    index = Convert.ToInt32(admgridview.Rows(admgridview.EditIndex).Cells(1).Text)
    strSQL = "UPDATE PCRequests SET PCUse = @PCUse, OS = @OS, ExtDev = @ExtDev, Requests = @Requests, Antivirus = @Antivirus, Budget = @Budget Where ID = " & index.ToString()
    SqlDataSource1.UpdateCommand = strSQL
End Sub

The weird thing is that i have a page similar to this one but that one doesnt' have the dropdownlist to select the "State". That one works.

This one also works if i put the selectcommand straight into "Sqldatasource1" and remove the dropdownlist to select the rows in the database based on the State.

Thank you in Advance.

Answer 1

User-359936451 posted

You don't have any code in your...

<asp:sqlDataSource id="SqlDataSource1"
               runat="server"
               SelectCommand=""
               UpdateCommand=""

 

So when you perform the Postback on changing the DROPDOWNLIST nothing executes when you call admvis, this needs to be an UPDATE SQL statement.

In short, your SQLDATASource is trying to do the update but there is no code for it execute.

You are calling in your rowup function...

Sub Rowup(Byval sender as Object, Byval e As EventArgs)
    Dim strSQL As String
    index = Convert.ToInt32(admgridview.Rows(admgridview.EditIndex).Cells(1).Text)
    strSQL = "UPDATE PCRequests SET PCUse = @PCUse, OS = @OS, ExtDev = @ExtDev, Requests = @Requests, Antivirus = @Antivirus, Budget = @Budget Where ID = " & index.ToString()
    SqlDataSource1.UpdateCommand = strSQL

this last line calls it but the UPDATE COMMAND has no SQL in it.

Answer 2

User61956409 posted

Hi emanuele1996,

Firstly, please debug the code to make sure if “Rowup” event could fire.

Secondly, if you’d like to implement CRUD operations, you could refer to this tutorial.

http://www.aspsnippets.com/Articles/ASPNet-MySQL-CRUD-Select-Insert-Edit-Update-and-Delete-in-GridView-using-MySQL-database-in-ASPNet.aspx

Best Regards,

Fei Han