Simple Databinding from db (ASP.NET etc…)

Topic

New Reply

I have been advised that the VS IDE doesn’t ‘close the circle’ when you drop an object on the webform grid and assign a dataset to the object. However, the intellisense in the codebehind page doesn’t seem to give the expected options. Any ideas?

form the book ASP.NET Developer’s Cookbook, the following code is supposed to be placed in the Page_Load block in the codebehind page or in a runat server code block in the .aspx page:

Dim sqlConnection as SqlConnection
Dim sqlAdaptor as SqlDataAdaptor

etc…

when I try this in the codebehind .vb page, intellisense only shows SqlClient{} as the given option in that part of the alphabet-driven choices.

FWIW, my .aspx page has the following entities:
SqlDataAdaptor_SelectAU <– this is the wizard-generated SqlDataAdaptor
SqlConnection_SelectAU <– this is the wizard-generated SqlConnection using wizard-generated Stored Proc to run a Select * statement on the Authors table in pubs
LoadAuthors1 <– this is the wizard-generated DataSet that runs off the SqlConnection

I am presuming I need to insert the correct code in the Page_Load section of the code behind (vb) page but, again, I am not getting very far.

Also, FWIW, the drop-down is assigned to the dataset and is set to display au_lname from the data for display. When I run the project I get a webpage with a drop-down box that does not contain any data.

So, I feel like I am on the right track, but am stuck on this page load() business. I'll continue researching, but any advise is certainly appreciated!

TIA

Reply | Quote

Replies

.NET 101
You’ll have to use the Imports statement (or using for C#) to allow intellisense and the compiler to know that you want to reference those specific SQL objects.

The FULL reference to those objects would be System.Data.SqlClient.SqlConnection and System.Data.SqlClient.SqlDataAdapter. In order to reference those objects without the full path, you’ll need to place Imports System.Data.SqlClient at the top of the code page.

Reply | Quote

.NET 101?? Of Course!!

SO, what I get when I use VS.NET is an .aspx page with a reference to the codebehind .vb file. when I open this, I notice the Web Form Designer Generated Code block (collapsed, but when opened all the connect detail goodies generated using the wizard) and then the Private Sub Page_Load code block. If I start typing my declare statements to grab the dataadaptor,sqlconnect string, etc. generated already, intellisense doesn’t see these objects, even tho they are in the same file. At the top there’s this stuff:

Public Class WebForm1
Inherits System.Web.UI.Page

Are you saying I need to add to the Imports declarations to get the intellisense to understand I am trying to hook into the previously generated stuff? If I try there’s build error. I do recall this stuff about imports to add these references from the SDK code, so I’ll look at that again.

I can get a datagrid to load if I do this on the same aspx page (not the code-behind), so there’s progress of a sort.

BUT — is it good or bad to load all the connect stuff and databinding declare in the code behind page rather than in the header/top of the main .aspx page? See, this is one of the things that’s confusing about using VS — the constellation of files produced makes me wonder where I need to put what. Well, as i have time to muck about I should begin to grok things eventually.

Thanks for your patience in dealing with these newbie questions.

Reply | Quote

The Imports statement should be placed at the VERY top of the codebehind file (assuming that’s where you’re writing your code).

If you look closely at the declarations of the objects on the page, you’ll see that the references are fully-qualified (System.Data.SqlClient.SqlConnection) as opposed to abbreviated (SqlConnection). Fully-qualified object references do not require any kind of import statement. HOWEVER, if you plan to use Intellisense or save on some typing, you need to provide the Imports statement so the Intellisense and compiler know to assume the “System.Data.SqlClient” part of the reference.

Check the helpfiles on “Imports” – it should explain a lot of this for you.

Reply | Quote

Ok, that works. With the Imports at the top of the codebehind page, wapping out this stuff:

Dim DS As DataSet
Dim myConnection As SqlConnection
Dim myCommand As SqlDataAdapter

myConnection = New SqlConnection(“workstation id=XXXXXXXX;packet size=4096;integrated security=SSPI;data source=XXXXX;persist security info=False;initial catalog=Audit_Determ”)
myCommand = New SqlDataAdapter(“Select * From Entity_Information WHERE SA_ID <1000", myConnection)

DS = New DataSet
myCommand.Fill(DS, "Entity_Information")

DataGrid1.DataSource = DS.Tables("Entity_Information").DefaultView
DataGrid1.DataBind()

in the page_load section of the code behind cruft produces a not-terribly-exciting datagrid. Your advice on the Imports lines was spot-on, as you certainly should have expected.

But, what gives with the wizard controls on the datagrid object, which allows you to attach a datasource, identify the PK (if desired), set the columns and paging etc.? What I've done is hand-code this sort of thing, and without the benefit of calling the stored proc I created when setting up the data adaptor. Is there something I need to do to get the actual DataGrid wizard to perform as promised?

BTW, what I am trying to do right now is evaluate the efficiency of .NET paging thru a recordset using stored procs vs my already-implemented paging routine in ASP Classic. I don't yet have a method for db-side paging — is ASP.NET capable of doing this or ?? — I mean, with less trouble than getting into a stored proc on SQL server to set up paging?

thanks again for your help!

Reply | Quote

Steve,

Let me make a suggestion from experience. Do NOT store connection strings and other reusable pieces of information (like the SELECT string in your exapmle below) in a code-behind. Store it in a Web.config file. Check your help topics on Web.configs to learn how to use these. The applcation will be MUCH more configurable and you will not have to recompile anytime your data connection string or SQL Statement changes. You can change values in a Web.config file at ANY time without having to recompile your app (although it will loose session state and restart the application).

You’re on your own with using wizards. My best advice is to avoid them and just learn how to produce the code on your own. The wizards typically generate a lot of additional code that’s not as efficient as it can be with a little knowledge, patience, and research on your part.

Have you tried searching Google for a server-side paging solution? I usually start this kind of search with “ASP.NET” then my search terms. You’ll get a lot of good hits, although it may take some patience to find exactly the one you need. Also, you’ll probably find more info with examples in C# than VB. The C# community is a little larger and more prolific than the VB community at this point…

Reply | Quote

thanks for the advice on security re. connect strings. in ASP Classic, I store them elsewhere and invoke them with an include, which, I am given to understand, is fairly secure.

As far as using VS.NET wizards, I gather you are saying what I would expect to hear re. MS Wizards — not a valid substitute to thinking! No suprise there . In this case, I was thinking the wizards would give me a place to go, code-wise. if they don’t they don’t…I thought it nifty that the SQLDataAdaptor wizard does generate stored procs in SQL Server that I can hack around in to try and set up db-side paging.

and, yes, I do have a lot of articles and tutes, etc. on the subject of paging but I haven’t gotten too far into them yet. I can visualize a way to do it in ASP classic: get an array using a recordcount to find the size of the dataset and set up a client-side method for generating ad-hoc SQL statements to page thru it via some kind of modal-based frame for fetching a block of data per page being requested. Having spent a lot of time doing ASP classic, it’s just too familiar to me to not think about first. However, the method I outlined presupposes an ordered table and that’s not a valid presumption for SQL Server, so I’m not too enthusiastic about it. Then I start thinking about an indexed table and the old brain starts going ‘just do it all in a stored proc, fool’.

Oh well, I’ll figure it out. I’m not immediately in love with ASP.NET as the amount of stuff to worry about is a lot larger than good ol’ ASP classic. However, one must continue to learn.

BTW, I just ran a little experiment: comment out all the code in the page_load except the call to databind to the datagrid, assign a datasource etc. in properties on the datagrid, run and I get — huh! the header row. So, it seems something is going on now that the Imports files are in place.

OK! onward thru the fog…

Reply | Quote