VB: Datagrid Update Query - Data Dissapering

[DJ1UK]

Hi All,

I have a datagrid that filters data from a database table by two fields (UserID and ScenarioID).

It displays the data correctly, but when I go and click the edit button - no data is displayed.

Now, if I take off the WHERE statement for the ScenarioID it works fine.

I think the problem is in the UPDATE SQL statment but not sure.

Heres the code:

Code:

Code: Select all

<%@ Page Language="VB" Debug="True" %>
<%@ Register Tagprefix="DSS" TagName="Header" Src="header.ascx" %>
<%@ Register Tagprefix="DSS" TagName="Menu" Src="menu.ascx" %>

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<script runat="server">
    Sub Page_Load(sender as Object, e as EventArgs)
        If Not Page.IsPostBack
            BindData()
        End If	   
    End Sub
    
    Sub BindData()
		dim objConn as new OleDbConnection ("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\DSS\DSS.mdb")
		objConn.Open()
		Dim ScenID As String = Request.QueryString("ScenID")
		Dim ProjID As String = Request.QueryString("ProjID")
        Dim UserID as Integer = CStr(Request.Params("UserID"))
		Dim strSQL as String = "SELECT tblScenCrit.CriteriaID, tblScenCrit.WeightID, tblScenCrit.UserID, tblScenCrit.ScenarioID, tblWeighting.Weighting, tblCriteria.Name, tblCriteria.Description FROM tblCriteria INNER JOIN (tblWeighting INNER JOIN tblScenCrit ON tblWeighting.WeightID = tblScenCrit.WeightID) ON tblCriteria.CriteriaID = tblScenCrit.CriteriaID WHERE UserID =" & Session(UserID)
		Dim objCmd as New OleDbCommand(strSQL, objConn)
		dgCrit.DataSource = objCmd.ExecuteReader(CommandBehavior.CloseConnecti  on)
		dgCrit.DataBind()
		objConn.Close()	
    End Sub
    
    Sub dgCrit_Edit(sender As Object, e As DataGridCommandEventArgs)
        dgCrit.EditItemIndex = e.Item.ItemIndex
        BindData() 
    End Sub

    Sub dgCrit_Cancel(sender As Object, e As DataGridCommandEventArgs)
        dgCrit.EditItemIndex = -1
        BindData() 
    End Sub

    Sub dgCrit_Update(sender As Object, e As DataGridCommandEventArgs)
		Dim ScenID As String = Request.QueryString("ScenID")
		Dim UserID As Integer = CStr(Request.Params("UserID"))
		
		Dim strCriteriaID as String = e.Item.Cells(1).Text
		Dim strName as String = e.Item.Cells(2).Text
		Dim strDescription as String = e.Item.Cells(3).Text
		Dim strScenarioID as String = e.Item.Cells(4).Text
		Dim strUserID as String = e.Item.Cells(5).Text
		Dim strWeightID as String = CType(e.Item.FindControl("ddlWeight"), DropDownList).SelectedItem.Value
      
		Dim strSQL as String =	"UPDATE [tblScenCrit] SET [WeightID] = @WeightID WHERE [CriteriaID] = @CriteriaID"
		'Dim strSQL as String = " UPDATE tblScenCrit SET tblScenCrit.WeightID = [@Weight] WHERE tblScenCrit.ScenarioID = [@ScenarioID] AND tblScenCrit.UserID = [@UserID] AND tblScenCrit.CriteriaID = [@CriteriaID]"
		
	

		dim objConn as new OleDbConnection ("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\DSS\DSS.mdb")		
		objConn.Open()
		Dim myCommand as OleDbCommand = new OleDbCommand(strSQL, objConn)
		myCommand.CommandType = CommandType.Text
	
		Dim parameterWeightID as OleDbParameter = new OleDbParameter("@WeightID", OleDbType.Char)
		parameterWeightID.Value = strWeightID
		myCommand.Parameters.Add(parameterWeightID)
		
		Dim parameterCriteriaID as OleDbParameter = new OleDbParameter("@CriteriaID", OleDbType.Char)
		parameterCriteriaID.Value = strCriteriaID
		myCommand.Parameters.Add(parameterCriteriaID)
		
		Dim parameterName as OleDbParameter = new OleDbParameter("@Name", OleDbType.Char)
		parameterName.Value = strName
		myCommand.Parameters.Add(parameterName)
		
		Dim parameterDescription as OleDbParameter = new OleDbParameter("@Description", OleDbType.Char)
		parameterDescription.Value = strDescription
		myCommand.Parameters.Add(parameterDescription)
		
		Dim parameterScenarioID as OleDbParameter = new OleDbParameter("@ScenarioID", OleDbType.Char)
		parameterScenarioID.Value = strScenarioID
		myCommand.Parameters.Add(parameterScenarioID)
		
		Dim parameterUserID as OleDbParameter = new OleDbParameter("@UserID", OleDbType.Char)
		parameterUserID.Value = strUserID
		myCommand.Parameters.Add(parameterUserID)
		
		

		myCommand.ExecuteNonQuery()
		objConn.Close()
		
		dgCrit.EditItemIndex = -1
		BindData()       
    End Sub
    
    Function GetWeight() as DataSet
		Dim objConn as New OleDbConnection ("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\DSS\DSS.mdb")
		Dim objCmd as New OleDbDataAdapter("SELECT WeightID, Weighting from tblWeighting ORDER BY Weighting", objConn)
		Dim ddlDataSet as DataSet = New DataSet()
		objCmd.Fill(ddlDataSet, "ddlWeight")
		Return ddlDataSet
	End Function
	
</script>

<html>
<Title>Decision Support System</Title>
<body>
<DSS:Header runat="server" />
<table>
<tr>
<td>
	<DSS:Menu runat="server" />
</td>
<td width="100%" valign="top">

	<center>
		 <form runat="server">
        <asp:DataGrid id="dgCrit" runat="server"
            AutoGenerateColumns="False" CellPadding="4"
            HeaderStyle-BackColor="#0000bf"
            HeaderStyle-ForeColor="White"
            HeaderStyle-HorizontalAlign="Center"
            HeaderStyle-Font-Bold="True"
            EditItemStyle-BackColor="#ccccee"
            OnEditCommand="dgCrit_Edit"
            OnUpdateCommand="dgCrit_Update"
            OnCancelCommand="dgCrit_Cancel"
            DataKeyField="CriteriaID">
        
            <Columns>
                <asp:EditCommandColumn EditText="Add Weights" ButtonType="PushButton"
                UpdateText="Update" CancelText="Cancel" />                   
                <asp:BoundColumn HeaderText="Criteria ID" DataField="CriteriaID" ReadOnly="True" />                                            
                <asp:BoundColumn HeaderText="Name" DataField="Name" ReadOnly="True" /> 
                <asp:BoundColumn HeaderText="Description" DataField="Description" ReadOnly="True" /> 
                <asp:BoundColumn HeaderText="ScenarioID" DataField="ScenarioID" ReadOnly="True" /> 
                <asp:BoundColumn HeaderText="UserID" DataField="UserID" ReadOnly="True" /> 
                <asp:TemplateColumn HeaderText="Weight">
					<ItemTemplate>
						<%# DataBinder.Eval(Container.DataItem, "Weighting") %>
					</ItemTemplate>
					<EditItemTemplate>
						<asp:DropDownList runat="server" id="ddlWeight" DataValueField="WeightID" DataTextField="Weighting" DataSource="<%# GetWeight() %>" />
					</EditItemTemplate>
				</asp:TemplateColumn>
             </columns>      
        </asp:DataGrid>
    </form>
	</center>
</td>
</tr>
</table>
</body>
</html>

Thanks for your suggestions.

DJ

[Smokie]

Perhaps your parameters are not correctly populated?

What do you mean "no data is displayed"? does the datagrid not re-bind?

[DJ1UK]

Yes the data is initially displayed, but when I click the update button the data does not bind to the datagrid.

I think its a problem with the update statement, but still working on it.

Thanks

DJ