Hand coding data access code

Wilfredo Rosado · December 27th, 2013, 03:56 AM

Hello Imar its been a long time.

I had to stop my programming for a while and now I'm back to it and ran into a wall.

On page 554 of the Beginning ASP.NET 4 in C# and VB book, you discuss hand coding to get my forms more to the way I would want them to look and feel. I understood what you said to do and it works great as long as I only find one item in the database. When my search returns more then one item I have a problem. I'm not sure what you would need to understand my point. Example: Assume I have a table in a database that stores the person's ID and phone numbers. If one person has multiple phone numbers in that table how would the hand coding work if three records matched the userID? The following code which is out of the book works Ok if one record is return.

Code:

    Using myEntities7 As New iGradesEntities5()
        Dim MyPhone = (From p In myEntities7.Phones
                               Where p.UserID = _UID
                               Select p)
        GridView2.DataSource = MyPhone
        GridView2.DataBind()

I need to be able to look at all return records, so that I can modify any part of all records, if necessary and update them. I've tried a few things but I can't get it to work.

Can you help me out or is there an advanced version of ASP.NET that may describe what I need? Also can I do hand coding without Entity framework, just using SQL data source? I find it difficult to learn two things at the same time because I'm never sure if it is an EF or ASP issue when I get an error message.

Thanks in advance for anything you can help me with.

Imar · December 28th, 2013, 09:24 AM

Hi Wilfred,

The code you posted works fine for multiple items (and not really for just 1). You're binding to a GridView which is designed to handle multiple items.

I see a few options:

1. Keep using the GridView for editing. It supports inline editing which seems a good solution for simple data such as phone numbers.

2. Use the GridView to display the data, and provide a details link to the Edit page that in turn deals with a single record. This is how I did it in my book: the GridView lists all Reviews and the details page is used to edit a single instance.

Quote:

Also can I do hand coding without Entity framework, just using SQL data source?

Not really, as you don't get enough control over the data that gets sent to SQL Server unless you manually modify a lot of the code which defeats the whole purpose. As an alternative, you could use direct ADO.NET code but that means learning yet another technology.

Quote:

I find it difficult to learn two things at the same time because I'm never sure if it is an EF or ASP issue when I get an error message.

When an error occurs, pay attention to the call stack. It'll show you where the error occurred which helps understanding its root cause.

Hope this helps,

Imar

Wilfredo Rosado · December 29th, 2013, 07:16 PM

Hi Imar,

Thanks for the quick reply, you are the best!

In reponse to your item 1) I cannot get the edit to work in GridView. While I use the wizard to set it up I am able to select edit, delete, etc. But when the wizard is finished edit nor delete are available. It does show paging, but thats all. And when I am able to get the edit and delete to show, then it doesn't show any of my database data.

In reponse to your item 2) I am not sure how to provide a details link to the edit page.

In response to the suggestion of using ADO.NET, I am willing to try anything that will allow me to do what I need. Do you know any good books I can purchase?

However, what I really need is a way to take the information placed into MyPhone (see initial thread post) and use it as I need. For example: if the information in MyPhone table looks like this:

Code:

PHID  UserID  AreaCode  PhoneNum  PhoneType
1     54545   203       3334444   Business
2     54545   860       4445555   Home
3     87352   212       1112222   School
4     54545   203       6664455   Mobile

Then how can I manually get the phone number for record 2 using hand code and not the Microsoft controls? I need to take record 2, PhoneNum (4445555) and assign it to a textbox variable name. Is there a mechanism that will allow me to pick and choose which column of what record?

If the search criteria is UserID="87352", then only one record is returned, record 3, then I can simply say:

txtPhone.text = MyPhone.PhoneNUm

After executing, txtPhone.text will have 1112222 in it.

But how do I specify record numbers when more than one record is returned. It will return three records if the search criteria is UserID = "54545"? How can I get to the second record information when MyPhone now has all three records in it. I hope you understand what I am trying to explain as this is really difficult for me to explain. If what I am trying to do is not possible, then I understand, but I cannot believe that Microsoft would force anyone to use only their controls.

I thought that Hand Coding Data Access Pages (page 555), would surely show me how to do what I need, but your description in the book, though very precise and useful, fell short in explaining multiple records returned.

Thanks for you ear and attention. You have helped me tremendously in the past and for this I am grateful. Hopefully, you can help me once more.

Sincerely,
Wilfredo Rosado

Imar · January 4th, 2014, 08:58 AM

Hi Wilfredo,

Quote:

while I use the wizard to set it up I am able to select edit, delete, etc. But when the wizard is finished edit nor delete are available. It does show paging, but thats all.

Make sure your database tables have a primary key. Also, after enabling editing at the data source control you also need to enable it on the Grid View (using its Smart Tasks panel).

Quote:

I am not sure how to provide a details link to the edit page.

Check out Chapter 13, page 453, TIO: Customizing GridView Columns. It shows how to add the link from each item in the grid to a details page called AddEditReview.aspx

Quote:

Then how can I manually get the phone number for record 2 using hand code and not the Microsoft controls? I need to take record 2, PhoneNum (4445555) and assign it to a textbox variable name. Is there a mechanism that will allow me to pick and choose which column of what record?

Again, for something as simple as a phone number, I would recommend using the inline editing features of the GridView as it does exactly what you describe here.

Quote:

But how do I specify record numbers when more than one record is returned. It will return three records if the search criteria is UserID = "54545"? How can I get to the second record information when MyPhone now has all three records in it.

Your Phone record needs a unique ID that you use in the link to the details page. Again, I would recommend checking out page 453 and further as you'll find lots of details on this topic.

Quote:

but I cannot believe that Microsoft would force anyone to use only their controls.

They don't. You can plug in any third party control library you like.

Quote:

but your description in the book, though very precise and useful, fell short in explaining multiple records returned.

It doesn't. It shows you how to display multiple records (reviews within a genre in my case, the the same would apply to phone numbers from some person) and provide links to edit them.
If you mean you want to edit multiple records at the same time, then yes, I am not covering that. This is complicated stuff, and you may be best off using a third party control as none of the built-in controls support multi-editing. It can be done but it requires a lot of tricky code.

Cheers,

Imar

Wilfredo Rosado · January 4th, 2014, 09:16 PM

Hi Imar and thanks for your reply,

I have been an instructor for a good portion of my life and we have a great proverb: "Communication is the responsibility of the sender, not the receiver!" :-)

Well, I have failed in communicating, mostly due because I am not sure about the entities' mechanism for obtaining data from a database and also, how that data is place into the object, which I am calling MyPhone. I am also not sure how I can dissemniate or parse out the information in MyPhone mainly because I donot know how it is formatted within MyPhone.

Code:

Using myEntities7 As New iGradesEntities5()
    Dim MyPhone = (From p In myEntities7.Phones Where p.UserID = _UID Select p)

You see, I have a particular look and feel to the interfaces I am working on, which require me to place the entities' data, into different parts of the screen. Not just from one table, but from multiple tables and place certain information from each onto the same screen.

I want to be able to take the data inside of MyPhone, (or any table), pull the necessary records and fields without binding to GridView or DetailsView. Neither GridView nor DataView allow me to place the data how I want to see it on the screen, I can only place the information in the format these controls dictate.

I am using phone numbers as an example being it is the simplest of the data I need, but there are other data in different tables that I will need to do the same placement on the screen. I need the freedom to place these data wherever I need. So taking the data from MyPhone, parsing it, and assigning them to textboxes, I can place them wherever I need .

There are going to be places where I need to use GridView or DetailsView and I will do so for those occurences, but for these others I need the flexibilityof placing data where I need it on the screen.

Well Imar, I'm sorry for taking up so much of your time. This is the best I can explain with the knowledge of entities that I have. I hope I was able to explain in better terms. I know you do your best with the explanations given to you, but sometimes us beginners are not sure how to explain things based on the minimum knowledge we have of the subject matter: "Entities".

I have successfully programmed similar websites back in the days of classic ASP (before .NET), using similar screen placement of database data. I was able to run querries off of the database and assign the info to the necessary textboxes for screen placement. When .NET came along, it provided nice tools to work with databases, but it took this hand coding away that I relied on.

Thanks,
WIlfredo Rosado

Imar · January 5th, 2014, 07:39 AM

Hi Wilfredo,

I think you need to make a distinction between single records and collection of records. For a collection, you can use the data controls such as GridView and ListView. A ListView gives you the best flexibility. However, since you're still displaying multiple records, you're somewhat limited to columns and rows when displaying them on the screen.

If myPhone is actually a single phone number, you need to change your query so you only get back a single record. Currently, this query:

Code:

Dim MyPhone = (From p In myEntities7.Phones Where p.UserID = _UID Select p)

returns a collection so it can only be used with a data control such as ListView or GridView. However, if you change it to something like this:

Code:

Dim myPhone = (From p In myEntities7.Phones Where p.ID = phoneId Select p).Single()

you get back a single record which you can then use to fill, say, a TextBox:

Code:

myTextBox.Text = myPhone.Number

(This assumes your Phone entity has a Number property).

I am not sure if this is what you need, as I am still unsure as to what you're trying to accomplish. Do you want to display multiple records? Then you don't have "the freedom to place these data wherever I need" when using one of the data controls. However, if your Phone entity has some property to determines what to do with it, you can query the collection and fill in other textboxes. Again, just assuming a lot of stuff as I don't know exactly what you're doing:

Code:

Dim myPhoneNumbers = (From p In myEntities7.Phones Where p.UserID = _UID  Select p)

Dim privatePhone As Phone = (From p in myPhoneNumbers Where p.Type = PhoneType.Private).SingleOrDefault()
If privatePhone IsNot Nothing Then
  PrivatePhoneTextBox.Text = privatePhone.Text
End If

Dim businessPhone As Phone = (From p in myPhoneNumbers Where p.Type = PhoneType.Business.SingleOrDefault()
If businessPhone IsNot Nothing Then
  BusinessPhoneTextBox.Text = privatePhone.Text
End If

This assumes your Phone entity has a Type property that marks the number as private or business. Even though the initial query returns a collection, you can then further query that collection to return single instances which you can then display in text box controls that you can display where you want.

Does this help? If not, can you describe, in much more detail, what you're trying to accomplish?

Cheers,

Imar

Wilfredo Rosado · January 8th, 2014, 04:15 AM

Hello Imar,

BTW: Happy New Year!

I am taking a side step to my original problem and now I am trying to use the DetailsView, which gives me a similar view of data that I would like to see. When I run the codes below I get data displayed. However, the pager shows there are 3 records found, the third record is displayed, and if I hit any of the action links such as pager links, "cancel" or "update", I get the following error message:

"System.Web.HttpException: The DetailsView 'DetailsView1' fired event PageIndexChanging which wasn't handled"

Also, I cannot page through to the other two records only the last; the Default mode is set for edit.

Code:

ASP CODE
<asp:DetailsView ID="DetailsView1" runat="server" AllowPaging="True"
    DefaultMode="Edit" Height="50px"
    Width="125px" Backcolor="#ffebd7" GridLines="None">
    <Fields>
        <asp:CommandField ShowEditButton="True" />
    </Fields>
    PagerSettings Mode="NumericFirstLast" />
</asp:DetailsView>

Code:

VB CODE
Using myEntities As New iGradesEntities5()
    Dim MyPhone = (From p In myEntities.Phones
    Where (p.UserID = _UID) Select p)
    DetailsView1.DataSource = MyPhone
    DetailsView.DataBind()
End Using

If I use SQLDataSource1 instead of Entity, I can see all records for all UserIDs and I can page through all of them. However, I have no way of limiting it to just one UserID since I am not using entities, I'm dealing directly with Database. Or so I believe!

Any ideas?
Wilfredo Rosado

Imar · January 8th, 2014, 05:56 AM

When data binding against EF like this, you need to manually implement paging by implementing the PageIndexChanging method as suggested by the error message: https://www.google.com/#q=pageindexchanging+detailsview

With SQL you can still query using the user ID. Just set up a SelectParameter in the SqlDataSource control and provide it the value it needs. Here's an example of how you can supply that parameter: http://imar.spaanjaars.com/571/letti...t-45-web-forms

Hope this helps,

Imar

Wilfredo Rosado · January 9th, 2014, 07:31 PM

Hello again Imar,

Please forgive my persistence. However, I am unfamiliar as to how I can implement your suggestion:

Code:

manually implement paging by implementing the PageIndexChanging method as suggested by the error message

I went to the link you posted, but I could not find an example of how to do this. Is it possible you can give me an example?

I would be most appreciative,
Wilfredo Rosado

Imar · January 10th, 2014, 04:29 PM

Hi Wilfredo,

No, I don't have any direct link. You may have luck search for paging gridview entity framework instead as the GridView uses similar concepts.

Since you haven't really described what you're trying to accomplish, I can't recommend much else.

Cheers,

Imar