Hi, i have an asp.net page running vb.net, connecting to an access database. I am using an inner join to connect to two tables
Table1
CityID
CityName
Table2
DateID
CityID
DateText
Status
I am running a dataGrid which displays the CityName, DateText and Status.
I would like the Status to be a DropDownList which will display is the Location is either "", "Filling" or Full. I would like to have these values as static values for my DropDownList. If the Status field in the database has either "Filling" or "Full" I would like this to be the selected value for the DropDownList (but still have the static values for the list). When a selection is made, a SelectedIndexChanged is triggered and this writes the updated value to the database.
So far i have all this working except when i view the url in a new browser, the Status DropDownList is not gathering the values from the database. They default to first blank static list item. Here is my Code, i hope someone can make sense of it. Thanks for any help received.
Imports System.Data
Imports System.Data.OleDb
Imports System.Net.Mail
Partial Class _Default
Inherits System.Web.UI.Page
Dim ddlDataSet as DataSet = New DataSet()
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Not Page.IsPostBack Then
BindData_1()
End If
End Sub
Sub BindData_1()
'Create a connection
Dim objConn as new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;data source=" & server.mappath("../location.mdb"))
'2. Create the command object, passing in the SQL string
Dim strSQL as String = "SELECT DateID, F.Date, F.CityID, F.Day, F.DateText, F.Status, FC.CityName AS CityNameCat_1 FROM tbl_officerTraining_date F INNER JOIN tbl_officerTraining_city FC ON F.CityID = FC.CityID"
'Set the datagrids datasource to the datareader and databind
Dim resultsDataSet_1 as New DataSet()
Dim myDataAdapter_1 as OleDbDataAdapter = New OleDbDataAdapter(strSQL, objConn)
myDataAdapter_1.Fill(resultsDataSet_1, "CityNameCat_1")
dataGrid_1.DataSource = resultsDataSet_1
dataGrid_1.DataBind()
End Sub
'**********************************************************************************************************************************************
'**********************************************************************************************************************************************
Protected Sub addSpace(ByVal sender As Object, ByVal e As System.EventArgs)
'add blank to user id ddlist
Dim update_status As DropDownList = CType(sender, DropDownList)
If update_Status.Items.Count = 0 Then
update_Status.Items.Insert(0, "")
update_Status.Items.Insert(1, "Filling")
update_Status.Items.Insert(2, "Full")
Else
BindData_1()
End If
End Sub
'**********************************************************************************************************************************************
Public Sub DropDown_1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)
'this is where I am trying to get the ID value of the row
'that the ddl index was changed
'once I get the ID, I can then write the real code forthe update
Try
Dim update_status As DropDownList = CType(sender, DropDownList)
Dim dataGrid_1 As DataGridItem = CType(update_Status.Parent.Parent, DataGridItem)
Dim strDateID As String = dataGrid_1.Cells(0).Text
Dim strStatus As String = update_Status.SelectedItem.Text
'Create the appropriate SQL statement
Dim strSQL as String = "UPDATE [tbl_officerTraining_date] SET [Status] = @Status WHERE [DateID] = @DateID"
Dim objCmd As OleDbCommand
Dim objConn as new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;data source=" & server.mappath("../location.mdb"))
objCmd = New OleDbCommand(strSQL, objConn)
'Add the parameters
Dim parameterStatus as OleDbParameter = new OleDbParameter("@Status", OleDbType.VarWChar)
parameterStatus.Value = strStatus
objCmd.Parameters.Add(parameterStatus)
Dim parameterDateID as OleDbParameter = new OleDbParameter("@DateID", OleDbType.Integer)
parameterDateID.Value = strDateID
objCmd.Parameters.Add(parameterDateID)
objConn.Open()
objCmd.ExecuteNonQuery()
objConn.Close()
'lblUpdateResults.Text = "Record has been successfully updated!"
Catch ex As Exception
Response.Write(ex.Message)
closeAccessConnection()
End Try
dataGrid_1.EditItemIndex = -1
'BindData()
End Sub
'**********************************************************************************************************************************************
Function GetSelectedIndex(CID as String) as Integer
Dim iLoop as Integer
Dim dt as DataTable = ddlDataSet.Tables("CityNameCat")
For iLoop = 0 to dt.Rows.Count - 1
If Int32.Parse(CID) = Int32.Parse(dt.Rows(iLoop)("DateID")) then
Return iLoop
End If
Next iLoop
End Function
'*********************************************************************************
'*********************************************************************************
Public Sub closeAccessConnection()
' If condition to check the access database connection state
' If it is open then close it.
Dim objConn as new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;data source=" & server.mappath("../location.mdb"))
If objConn.State = ConnectionState.Open Then
objConn.Close()
End If
End Sub
End Class<%@ Page Language="VB" CodeFile="index1.aspx.vb" debug="True" Inherits="_Default" %>
<%@ Import Namespace="System.Data.OleDb" %>
<html>
<head></head>
<body>
<form runat="server">
<asp:label id="lblUpdateResults" Font-Name="Verdana" Font-Size="10pt" runat="server"></asp:label>
<asp:datagrid id="dataGrid_1" runat="server"
AllowSorting="True"
AutoGenerateColumns="False"
HeaderStyle-HorizontalAlign="Center"
HeaderStyle-BackColor="#c8e3f4"
HeaderStyle-ForeColor="Black"
HeaderStyle-Font-Bold="True"
HeaderStyle-Font-Name="Verdana"
AlternatingItemStyle-BackColor="#ededed"
ItemStyle-Font-Name="Verdana"
ItemStyle-VerticalAlign="Top"
EditStyle-Font-Size="8pt"
DataKeyField="DateID"
ShowFooter="True"
Cellpadding="5"
Cellspacing="0"
Font-Size="8pt">
<Columns>
<asp:BoundColumn DataField="DateID" Visible="False" HeaderText="ID"/>
<asp:BoundColumn DataField="CityNameCat_1" Visible="True" ReadOnly="True" ItemStyle-HorizontalAlign="Left" HeaderText="City"/>
<asp:BoundColumn DataField="Day" ItemStyle-Width="100" Visible="True" ReadOnly="True" ItemStyle-HorizontalAlign="Left" HeaderText="Day"/>
<asp:BoundColumn DataField="DateText" ItemStyle-Width="100" Visible="True" ReadOnly="True" ItemStyle-HorizontalAlign="Left" HeaderText="Date"/>
<asp:TemplateColumn HeaderText="Status">
<ItemTemplate>
<asp:DropDownList id="update_Status" AutoPostBack="true" OnDataBound="addSpace" DataValueField="DateID" DataTextField="Status" OnSelectedIndexChanged="DropDown_1_SelectedIndexChanged" runat="server" size="1">
</asp:DropDownList>
</ItemTemplate>
</asp:TemplateColumn>
</Columns>
</asp:datagrid>
</form>
</body>
</html>Thanks...