Solved

How to populate an edititem dropdownlist in a datagrid with data from a database

Posted on 2008-06-14
21
543 Views
Last Modified: 2010-04-21
Having searched both experts exchange and the web I have found a number of possible solutions but each one keeps giving the same error 'Object reference not set to an instance of an object.'
Most solutions seem to keep the data connection open from page load onwards, but my pages close the connection after each section.  Please see the code snippet consisting of part of the datagrid and part of the itemdatbound routine.
Thanks in advance.
<asp:DataGrid

  ID="dgrdTitles"

   onItemDataBound="GridDataBound"

   DataKeyField="ID"

   AutoGenerateColumns="False"

    OnEditCommand = "dgrdTitles_EditCommand"

   OnUpdateCommand = "dgrdTitles_UpdateCommand"

   OnCancelCommand = "dgrdTitles_CancelCommand"

     CellPadding="4"

  HeaderStyle-Font-Name="Arial"

  HeaderStyle-Font-Size="9pt"

  HeaderStyle-Font-Bold="True"

  HeaderStyle-BackColor="LightBlue"

  ShowFooter="True"

  FooterStyle-Font-Name="Arial"

  FooterStyle-Font-Size="9pt"

  FooterStyle-Font-Bold="True"

  FooterStyle-BackColor="LightBlue"

  BorderColor="Gray"

  ItemStyle-Font-Name="Arial"

  ItemStyle-Font-Size="9pt"

  ItemStyle-Font-Bold="False"

  EditItemStyle-Width = "100%"

  EditItemStyle-Backcolor = "LightSteelBlue"

  Runat="Server">

<Columns>	

<asp:TemplateColumn

HeaderText="PI"

FooterText = "PI">

<ItemTemplate>

<asp:Label 

id="lbl1" 

runat = "server" 

text = '<%# DataBinder.Eval(Container.DataItem, "PI")%>'>

</asp:Label>

</ItemTemplate>
 

<EditItemTemplate>

<asp:DropDownList

ID="ddlPI"

AutopostBack = "False"

DataValueField = "ID"

DataTextField = "PI"

Runat="server"

SelectedValue = '<%# DataBinder.Eval(Container.DataItem, "PI")%>' >

</asp:DropdownList>

</EditItemTemplate>

</asp:TemplateColumn>
 

**********************

Private Sub GridDataBound(ByVal sender As Object, ByVal e As DataGridItemEventArgs) 

Dim nUnits,nActual,nAllocate As Integer

dim nDiff as double

Dim dtrStart,dtrFinish,dtrScheduleFinish,dtrTest as date

Dim strRec as string

If e.Item.ItemType = ListItemType.Item Or e.Item.ItemType = ListItemType.AlternatingItem Then

'Note ddlPI is the edit item dropdownlist ID

''// Populate the drop down lists
 
 

    sMDBFile = MapPath("~/Audit/Audit.mdb")

	SConn = "Provider=Microsoft.Jet.OLEDB.4.0;"

	Sconn = Sconn & "Data Source=" & sMDBFile

	dim cnn  as OLeDbConnection

	dim cmd as OLeDbCommand

	cnn = New OLeDbConnection(sConn)

	Dim ddlPI as Dropdownlist

	ddlPI = cType(e.item.Findcontrol("ddlPI"),DropDownList)

   	cnn.Open()

	 cmdSelect = New OLeDBCommand( "Select * From tblPSP" , cnn )

	ddlPI.DataSource = cmdSelect.ExecuteReader() 	

	ddlPI.DataBind()

  

   cnn.Close()
 

end if

end sub

Open in new window

0
Comment
Question by:dolbs
  • 11
  • 10
21 Comments
 
LVL 6

Expert Comment

by:rafayali
ID: 21787464
The reason for that error message could be that you are handling the itemdatabound event of the datagrid, but there is no datasource attached to this grid. Even though you are connecting to Access in code, but you are only populating the dropdownlist here. However, itemdatabound is NEVER INVOKED because datagrid (of which itemdatabound is part) DOES NOT HAVE A DATSOURCE assigned, either via datasource property (which is set when using ado.net classes in codebehind) or datsourceid of a datasource control

Try adding a Accessdatasource control and link it to datagrid. Then see what error is raised.
0
 

Author Comment

by:dolbs
ID: 21788056
rafayali

I have another function called BindDatagrid which is run on page load.  This populates the datagrid with the original data .  As I understand it the onItemdatabound runs every time the grid is loaded or changed.  I will try your suggestions now and get back to you.  The code is on my other computer.
0
 

Author Comment

by:dolbs
ID: 21788088
Rafayali

Still have the same error System.NullReferenceException: Object reference not set to an instance of an object. See code snippet


    sMDBFile = MapPath("~/Audit/Audit.mdb")

	SConn = "Provider=Microsoft.Jet.OLEDB.4.0;"

	Sconn = Sconn & "Data Source=" & sMDBFile

	dim cnn  as OLeDbConnection

	dim cmd as OLeDbCommand

	cnn = New OLeDbConnection(sConn)

	Dim ddlPI as Dropdownlist

	ddlPI = cType(e.item.Findcontrol("ddlPI"),DropDownList)

   	cnn.Open()

	 cmdSelect = New OLeDBCommand( "Select * From tblPSP" , cnn )

	    dgrdTitles.DataSource = cmdSelect.ExecuteReader()

     cnn.close()

	 cnn.open()

	

	 cmdSelect = New OLeDBCommand( "Select * From tblPSP" , cnn )

	 

	 ' *** still gives same error.

	ddlPI.DataSource = cmdSelect.ExecuteReader() 	

	ddlPI.DataBind()

    

	  

   cnn.Close()

Open in new window

0
 
LVL 6

Expert Comment

by:rafayali
ID: 21789053
I have two suggestions.

first, try to bind a dataset rather than a datareader to dropdownlist. See if that works.

second, add a new test web form, add the dropdownlist there and try to populat eyour code to see if that works. This is just to isolate any problems with the code itself.

Another thing is the event you are using. Normally, onitemdatabound is executed for each
row that is added to your datagrid. So, in this case you dont want to do that.
You can pretty much call this function from Page_Load event itself after the datagrid is
databound, call this function to see if that works.
0
 
LVL 6

Expert Comment

by:rafayali
ID: 21789061
Also I notice in your code that ddlPI in the method has not been declared. It should be declared of type dropdownlist. May be that could be the error.

I am assuming the error is probably resulting from an incorrect way to find dropdownlist in datagrid container. That's why these problems.
0
 
LVL 6

Expert Comment

by:rafayali
ID: 21789293
Do not call your method in onitemdatabound.

Instead, just try changing your databinder syntax for dropdownlist to

DataSource='<%# GridDataBound((int)DataBinder.Eval(Container.DataItem, "PI")) %>'

PI is int right?

See, you shouldnt be calling this onitemdatabound even if the above suggestion does not work. I havent had the chance to actually test this code on my end even though I''d love to because your problem is very simple. And, VS is installed on another machine to which I dont have access right now. That is why I keep turning up with more and more suggestions hoping that something would work. But, as I said it is simply a logical issue and I had created something of this nature for a project a while back. Therefore, I will paste the code as soon as i get the other machine back. But for now try the above.

Onitemdatabound is rasied for each row as it is created in the datagrid. You dont want that because you want your code to execute only when datagrid is in editmode. So, onitemdatabound is not the place to do this.

Try the above suggestion and let me know if it worked. Otherwise, I will try to whip up something quickly on my other machine and paste the code here.

0
 

Author Comment

by:dolbs
ID: 21790371
Rafayali

tried it out but te "PI" is a list of names (string) and the aurgument does not accept string or text. Calls text an ambiguouios with System.Drawing, system but I need the drawing name space.

Sorry for the delay in getting backtoyou as I am in Perth Australia, probably about 12 hours time difference.  ButI am checking as often as Ican.
0
 
LVL 6

Expert Comment

by:rafayali
ID: 21790440
Alright, from what I understand about the problem, you need a dropdownlist populated in the edit item template right?

I will whip up some code and paste it here shortly. Let me know if I have the problem description incorrect.
0
 

Author Comment

by:dolbs
ID: 21790450
Rafayali

These are the namespaces I am using.  The whole page is quite complex in that it is a plan of action with a number of people assigned to do the task.  PI is one of 4 drop down lists that I want to incorporate and each list will hold 146 names.  Some fields are color coded and that is why I need the drawing namespace.
<%@ Page Language="vb" Debug = "True" ValidateRequest="False" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.OleDb" %>
<%@ import Namespace="System.XML" %>
<%@ Import Namespace="System.Globalization" %>
<%@ Import Namespace="System.Drawing" %>
<%@ Import Namespace="System.Drawing.Imaging" %>
<%@ Import Namespace="System.Web.UI.WebControls"%>
<%@ Import Namespace="System.Web.UI"%>
<%@ Import Namespace="System.ComponentModel"%>
<%@ Import Namespace="System.Web.Mail"%>
<%@ Register TagPrefix="FTB" Namespace="FreeTextBoxControls" Assembly="FreeTextBox" %>
0
 

Author Comment

by:dolbs
ID: 21790457
Rafayali

Yep that is excatly what I need.  We just crossed paths on the message above.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 6

Accepted Solution

by:
rafayali earned 500 total points
ID: 21795766
Ok i have figured it out. Try this code in gridview


 <asp:TemplateField>
               <EditItemTemplate>
               <asp:DropDownList ID="ddl2" runat="server" DataTextField="username" DataValueField="id" DataSource='<%#bindDDL2() %>' />
               
               
               </EditItemTemplate>
               </asp:TemplateField>

And here is the DDL2 method

Public Function bindDDL2() As DataSet
        Dim objAdapter As New OleDb.OleDbDataAdapter("SELECT * FROM users", "provider=microsoft.jet.oledb.4.0;data source=|DataDirectory|\members.mdb")
        Dim ds As New DataSet()
        objAdapter.Fill(ds)
        Return ds

    End Function

Replace the tables, columns, etc. etc. with whatever is appropriate for your environment. Hopefully this will work.
0
 
LVL 6

Expert Comment

by:rafayali
ID: 21795810
Since your original code uses a datareader, here is an example of another function that returns datareader instead of a dataset

  Public Function bindDDL3() As OleDbDataReader
        Dim objConn As New OleDbConnection("provider=microsoft.jet.oledb.4.0;data source=|DataDirectory|\members.mdb")
        Dim objCommand As New OleDbCommand("SELECT * FROM USERS", objConn)
        Dim objReader As OleDbDataReader = Nothing

        objConn.Open()
        objReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection)

        Return objReader

Make sure to import System.Data and System.Data.Oledb
0
 

Author Comment

by:dolbs
ID: 21800010
Rafayali
I used the datadapter codefrom you first example and it worked great, but that has now left me with the problem of preselecting the existing database value.  The list of names comes from a query called QRYNames (extraced from a table called tblNames).  The existing data name for the dropdownlist comes from a table called TBLpsp.  I used this originally to populate the existing selection
SelectedValue = '<%# DataBinder.Eval(Container.DataItem, "PI")%>' > but that no longer works.  Next I tried to put in some code into the BindDDL2() function but that retrned a DDL2 not declared error.  Can you help me further with this?  How do I get the lblPI value to be the selected value in te dropdownlist now that I have all ofthe names? The points are your for sure.  See code snippett

'' This works

Public Function BindDDL2() as DataSet

   sMDBFile = MapPath("~/GAOG/Audit.mdb")

	SConn = "Provider=Microsoft.Jet.OLEDB.4.0;"

	Sconn = Sconn & "Data Source=" & sMDBFile

	dim cnn  as OLeDbConnection

	cnn = New OLeDbConnection(sConn)

	dim objAdapter as New OLeDB.OLEdbDataadapter("Select * from qryNames",cnn)

    dim DS as new DataSet

	objAdapter.Fill(DS)

	Return DS
 

end function
 

''This is the edit item template column

	

<asp:TemplateColumn

HeaderText="PI"

FooterText = "PI">

<ItemTemplate>

<asp:Label 

id="lblPI" 

runat = "server" 

text = '<%# DataBinder.Eval(Container.DataItem, "PI")%>'>

</asp:Label>

</ItemTemplate>
 

	<EditItemTemplate>

<asp:DropDownList

ID="DDLPI"

AutopostBack = "False"

Runat="server"

DataTextField = "NameInitial"

DataValueField = "ID"

Datasource='<%# BindDDL2()%>'/>

</asp:DropdownList>

</EditItemTemplate>

</asp:TemplateColumn>

Open in new window

0
 
LVL 6

Expert Comment

by:rafayali
ID: 21807488
Not a problem. Let me have a look at your recent post more closely - this post is just to confirm that I read it. I will post another one shortly with my recommendation.
0
 
LVL 6

Expert Comment

by:rafayali
ID: 21807600
Ok, so if I am not mistaken, you have a label in the itemtemplate which has a value from column named PI right? You want this label value to be selected once the dropdownlist in edititemtemplate loads?  Correct me if iam wrong.

Or, this sounds more logical and I think you mean this: Once a user selects a value in the dropdownlist (in edititemtemplate), you want this value to be displayed in the label (in itemtemplate)? For example, a user enters edititemtemplate and selects the first value in dropdownlist. Now, this first value should be displayed in the label?

0
 

Author Comment

by:dolbs
ID: 21808313
Rafayali

No.  The user has already selected a name in a previous aspx page and this page is a review and approve page.  The approver must have the ability to change the person assigned to the task, hence the existing name in the label needs to be preselected as most times the name will not be changed, but I have to have that ability.  The idea is that the name preloaded into the label will be selected when the user entrers the edit mode.  As it will turn out there will be 4 dropdownlists in   this page with only the first prepoulated with a name, but that name must be able to be changed if for some reason that person cannot do the task.

Sorry for the cnfusion.
0
 

Author Comment

by:dolbs
ID: 21808340
Raf

Sorry.  More to my reply above.  You are correct in that I have a label with the value of "PI" in it.  When the edit item opens I want the dropdownlist to select the value of the label from the dropdownlist.
0
 

Author Comment

by:dolbs
ID: 21808448
Rafayali

Actually both you points are valid.
Yoour first option is correct and IF the name is changed then it needs to be saved to the data base and then redisplayed in the grid after the edit function closes.  I alread have code for saving to the database as I have another dropdown list where I have manually added items and saved the value back to the database.  This problem is the one of getting the data from the database (done thank you) and then preselecting the existsing value as the selected value (not none yet) I tried this by adding it to the edittemplate but it doesn;t work.
SelectedValue =  '<%# DataBinder.Eval(Container.DataItem, "PI")%>'>

0
 

Author Comment

by:dolbs
ID: 21821134
Rafayali

I haven't heard from you for awhile but you did answer my primary question although I stll don't know how to select the existing database value from the dropdown list, but as you have amnswered my primary question I am allocating th epoints to you.  I'll ask another question to sort the issue remaining if I can't find it elsewhere.
0
 

Author Closing Comment

by:dolbs
ID: 31467278
Answered the question posed but I need further hellp on making the selectedvalue of the dropdownlist match the existing database value.
0
 
LVL 6

Expert Comment

by:rafayali
ID: 21824902
Hi,
Sorry about that man I got caught up in something.

I am still not clear on the requirement thjat you have? Can you explain as clearly as possible what needs to be achieved next? Mean when you say that "I still dont know how to select the existing database value from dropdownlist", what is existing database value? And, where does it exist?

Please let me know. I will try to find asolution for it
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
This video discusses moving either the default database or any database to a new volume.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now