Datasource to Populate DDL - then choose item

I've got a page with a SQLDataSource control successfully populating a Dropdownlist...

However, now I find I need extended functionality - so I've got another page with links to this page, using querystrings - I need to hit the page, check the querystring, and if it's blank, just continue, but if the querystring is populated, then choose that item.

I've tried the page_load and the page_prerender, but so far, it's not working:
CC = Request.QueryString("center")
If CC <>""Then
' DropDownList1.Items.FindByText(CC).Selected = True
DropDownList1.SelectedIndex = DropDownList1.Items.IndexOf(DropDownList1.Items.FindByText(CC))
End if

I've double checked, and yes, every time CC is populated, that item is definitely in the dropdownlist - it just doesn't get changed to that item.

ideas?

EndIf

Here's an example for you:

ASPX

<asp:dropdownlist id="ddlPets" runat="server" ondatabound="ddlPets_DataBound"></asp:dropdownlist><br /><br /><asp:hyperlink id="hypDog" runat="server" navigateurl="1131233.aspx?pet=Dog" text="Pre-select Dog" /><br /><asp:hyperlink id="hypCat" runat="server" navigateurl="1131233.aspx?pet=Cat" text="Pre-select Cat" /><br /><asp:hyperlink id="hypGoldfish" runat="server" navigateurl="1131233.aspx?pet=Goldfish"text="Pre-select Goldfish" /><br />

CODE-BEHIND

protected void Page_Load(object sender, EventArgs e){if (!this.IsPostBack){string[] pets = {"Dog","Cat","Goldfish" };ddlPets.DataSource = pets;ddlPets.DataBind();}}protected void ddlPets_DataBound(object sender, EventArgs e){if (this.Request["Pet"] ==null) {return; }ddlPets.SelectedIndex = ddlPets.Items.IndexOf(ddlPets.Items.FindByValue(this.Request["Pet"]));}

|||

using the databound even worked

sql

DataReader Source and Column Types

Is there a way to control the types for output columns of a DataReader Source? It appears that any System.String will always come out as DT_WSTR. As I have my own managed provider, and I know what went in, I can say that really it should be DT_STR. The GetSchemaTable call from my provider will always say System.String as it does not have much choice, but GetSchemaTable does contain a ProviderType which is different for my DT_STR vs DT_WSTR, or rather when I want each. I think something like MappingFiles as used by the Wizard would work, but can I do anything today?

Darren,

I am afraid there is no way to influence this mapping. The Data Reader Source adapter uses only the CLR type (the DataType column from the table's schema) to determine which DT_... type to choose. The ProviderType field could not be used as it has different meaning for different providers.

The mapping files would definitely help here, but that infrastructure is not used by this component.

I do not have any good advice, but explicit data conversion to the DT_STR type or building your custom ADO .NET adapter are options I see available at this moment.

Thanks.

|||

Bob,

Thanks for confirming what I already suspected, but I had to ask. I'm thinking a MSDN feedback request for the ability to supply mapping files would be coming your way.

Conversion works, but I'm concerned about the impact of doubling the buffer size each time I do this. Most columns I am working with are DT_WTR, but need to be DT_STR, it is 2 x buffer every time. Is this really twice the size or is there some fancy pointer type work going on? I will probably test when I have time as I had some other ideas about custom components for such conversion, but it depends on what the impact really is.

A custom provider had been considered but currently rejected due to time. It took me long enough to get the managed provider written :)

Thanks

|||

Hi Darren,

I believe you are right about the buffer size. It might impact your package performance, but it is not sure how significant that could be. It may depend on many factors. If you get a chance to measure the impact in your configuration, please share results with us.

If the "power" stays with us, we should be able to provide much better story with managed providers in the next version.

Thanks.

|||

I have done some playing around with this.

For information my theory goes like this. If I use a Data Conversion transform I am increasing the number of columns in my buffer so I get less rows per buffer. This seems inefficient. On the other side we know that copying data between buffers has a cost. So lets test which is more efficient, the larger row size versus the cost of moving between buffers, and keeping a small row size.

I wrote a simple asynchronous component that allowed you to select columns from the input buffer which are then copied directly to the output buffer. The one feature is that any DT_WSTR column is reproduced as DT_STR. So the buffer sizes/structure are the same for input and output except for the change in type, and any associated overheads of each type. One would think that unicode types require twice the space of non-unicode, so this should make the asynchronous component test even faster as this allows even more rows to fit into the output buffer of my component.

For a baseline I used a Script Component -> Union All. The script component generated a variable number of rows, as determined by a package variable. The columns produced are 1 integer column (row count), and 9 x 50 character DT_WSTR columns fully populated.

For testing I used the same script component and two methods of converting the columns -

Script Component -> Data Conversion -> Union All

Script Component -> DeUnicodeAsynchTestComponent -> Union All

Tests showed that the data conversion was 1.5-2.5 times slower than the baseline. The asynchronous component was then 2-2.5 times slower than the data conversion. Times were averaged across 6 executions. The range in times are for different row counts, 100,000 to 1,000,000.

N.B. These ratios are for my local machine, and I would fully expect results to vary on different hardware and with different resource constraints. These are for illustration only. If you want to know how this equates to your environment, test it for yourself, and use real hardware, not a test system.

So, whilst it may not look pretty leaving the buffer alone is the way to go. Trying to remove columns or change columns is a non-starter as this means creating a new buffer, the cost of which far outweighs the benefit of the smaller row size in the buffer. When you do need to work on columns, use a synchronous component such as the Data Conversion or Derived Column transformations, and don’t worry if you end up with more columns that you will use at the end. (Obviously don’t create columns for the sake of it!)

DataItem access in ascx

I am currently building an ecommerce site using aspx, vbscript, & sql.
In my productlist control containing a datalist I am trying to use an ifstatement to determine if products are from a certain supplier... andif they are; display a phone number to call for pricing rather than theprice, since this particular supplier doesnt allow pricing of theirproducts online.
This is what I have now:
<%
Dim objConn
objConn = Server.CreateObject("ADODB.Connection")
Dim strConnect
strConnect = ConfigurationSettings.AppSettings("ConnectionString")
Dim supplier
objConn.Open(strConnect)
supplier = objConn.Execute("Select SupplierID From Product")
If supplier = "C" Then
Response.Write("Call 1-800-POO-STAIN")
Else
Response.Write(" Sug. Retail:")
DataBinder.Eval(Container.DataItem, "Price")
End IF
objConn.Close
objConn = Nothing
%>
This code gives me the following error message:
The component 'ADODB.Connection' cannot be created. Apartment threaded components can only be created on pages with an <%@. Page aspcompat=true %> page directive

Can anybody help me get this working?
I haven't worked with VBScript for acouple years now and I'm a little rusty.
Any help would be greatly appreciated

What you have is Classic ASP code. You should change the extension to ASP, or convert the code to use VB.NET and the ADO.NET components.
Alternately, add this to the top of the page:
<%@. Page aspcompat=true %>
|||

The problem is I am using this in an ascx control. aspcompat is not supported by the 'control' directive.

Any other suggestions?

|||the page that will hold the ascx needs to have the aspcompat attribute.

DataGridView/SQL Express - Updating record using seperate form

Hi,

I am currently using VC++/Cli 2005. In a project, I'm using a DataGridView control to show records from a SQL Express 2005 table. Instead of updating a specific item directly within DataGridView control, I would like to open a new form and allow user to update selected record/item within that form. The reason to update this way is conditionned by the fact that I have 3 levels of detail as following:

Level 0 Level 1 Level 2

1:N 1:N
Furniture --> Component --> Component

You all understand that update form of Level1 will/must include, as Level0 do, another DataGridView control to display/detail all related items issued from next level. This explains why I can't allow user to update Level 1 directly from DataGridView control.

I've searched in MSDN and even bought a few books on subject but unfortunately I found nothing on how to do it this way. All articles on DataGridView control were only showing how to update record directly from control.

My approach, I think, would be to transmit to my level1 updating form, as a single parameter, the selected DataRow object (or a brand new one if currently adding) issued from DataGridView and let user update it's content. When User finally leaves level0 update form, then I presume that DataGridView corresponding table would be automatically updated according to DataGridView's content.

What would be the proper way to do it? I would certainly appreciate to hear you view on this.

Also, what can I do if I want to refresh DataGridView's content when coming back from update form. Is it done automatically? I would certainly be sure that it reflects the reality, not only when I update it myself but also especially when other users could concurrently update same records?

Thanks in advance,

Stphane

Hi Stephane,

It sounds like you're looking for a Master/Detail implementation. There are a number of topics covering this in MSDN, start with the first three hits on this search.

Mike

|||

Hi Mike,

I followed links supplied. For what I could see, they are only showing how to put 2 DataGridView controls, master and detail, on same windows form and have data displayed accordingly. I already had a look at this technique. Unfortunately, this is not exactly what I'm looking for.

I'm definitely looking to be able to get/highlight a record (a row...) from a DataGridView control and then be able to perform any insert/update/delete operation, update its content using a new windows form which will be my "updating" form where user will be able to supply any required infos for that specific record/row instead of doing it directly from within DataGridView control.

Any hint or useful links?

Thanks again for your help,

Stphane

|||

Hi Stephane,

First off, remember this is the SQL Express forum and you're asking about a VS component so you might get a better answer in one of the VS forums. I'd suggest the .NET Data Access forum as a place to start, but language specific forums or Windows Forms forums may also be usefull.

That said, if you start with this topic about using two DataGridViews but split the two DGs between two forms, it should just be a matter of writing some code to react to an action on the Master form. Check out the documentation for this control, there are a number of Properties that allow you to determin the selection as well as some events (i.e. OnClick) that should let you respond to user action in the control.

I'm guessing that if you ask around in the VS forums someone will have already written a framework to do this.

Mike

|||

Hi Mike,

Thanks for clarification. I must admit that I get quite lost when trying to find appropriate forum... Any document or article listing all forums and their purposes? Would certainly help!

I followed your link but anything I can find on MSDN is an example using 2 datagridviews on same form. For my case, I just try to find a way to, first, select a SQL table record from datagridview in form1, open a new form, form2, where I will enable user to update record's content. Scenario I illustrated could also include a second datagridview in form2.

Now browsing through the many forums available, it seems that there is one discussing specific issues regarding datagridview:

MSDN Forums Windows Forms Windows Forms Data Controls and Databinding

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=7&SiteID=1

Hope I will be able to get any hint from there!

Thanks for your help,

Stphane

|||

Hi Stephanie,

You might be confused because many of the VS examples and Walkthroughs, including this one, do not walk you through using the VS UI to create the example, they use a 100% code based solution. The sample that I linked to provides code that programatically creates a form with two DataGridViews on it, attaches those DGVs to BindingSources and then hooks them up to the Northwind Database.

VS doesn't have many help topics that walk through specific tasks using the UI tools. I looked around on http://windowsforms.com and didn't really find anything that discusses how to use the UI to do this either, but you're welcome to take a look yourself as you may find some interesting material there.

There isn't a document listing all the fourms, but this page has them all. Each has a short description which hopefully describes it's use.

Mike

|||

Hi again Mike,

Thanks for links. I will keep looking around. I got my hand on a book which seems to give some hints about DGVs. Hope I will find something...

Stphane

DataGridView/SQL Express - Updating record using seperate form

Hi,

I am currently using VC++/Cli 2005. In a project, I'm using a DataGridView control to show records from a SQL Express 2005 table. Instead of updating a specific item directly within DataGridView control, I would like to open a new form and allow user to update selected record/item within that form. The reason to update this way is conditionned by the fact that I have 3 levels of detail as following:

Level 0 Level 1 Level 2

1:N 1:N
Furniture --> Component --> Component

You all understand that update form of Level1 will/must include, as Level0 do, another DataGridView control to display/detail all related items issued from next level. This explains why I can't allow user to update Level 1 directly from DataGridView control.

I've searched in MSDN and even bought a few books on subject but unfortunately I found nothing on how to do it this way. All articles on DataGridView control were only showing how to update record directly from control.

My approach, I think, would be to transmit to my level1 updating form, as a single parameter, the selected DataRow object (or a brand new one if currently adding) issued from DataGridView and let user update it's content. When User finally leaves level0 update form, then I presume that DataGridView corresponding table would be automatically updated according to DataGridView's content.

What would be the proper way to do it? I would certainly appreciate to hear you view on this.

Also, what can I do if I want to refresh DataGridView's content when coming back from update form. Is it done automatically? I would certainly be sure that it reflects the reality, not only when I update it myself but also especially when other users could concurrently update same records?

Thanks in advance,

Stphane

Hi Stephane,

It sounds like you're looking for a Master/Detail implementation. There are a number of topics covering this in MSDN, start with the first three hits on this search.

Mike

|||

Hi Mike,

I followed links supplied. For what I could see, they are only showing how to put 2 DataGridView controls, master and detail, on same windows form and have data displayed accordingly. I already had a look at this technique. Unfortunately, this is not exactly what I'm looking for.

I'm definitely looking to be able to get/highlight a record (a row...) from a DataGridView control and then be able to perform any insert/update/delete operation, update its content using a new windows form which will be my "updating" form where user will be able to supply any required infos for that specific record/row instead of doing it directly from within DataGridView control.

Any hint or useful links?

Thanks again for your help,

Stphane

|||

Hi Stephane,

First off, remember this is the SQL Express forum and you're asking about a VS component so you might get a better answer in one of the VS forums. I'd suggest the .NET Data Access forum as a place to start, but language specific forums or Windows Forms forums may also be usefull.

That said, if you start with this topic about using two DataGridViews but split the two DGs between two forms, it should just be a matter of writing some code to react to an action on the Master form. Check out the documentation for this control, there are a number of Properties that allow you to determin the selection as well as some events (i.e. OnClick) that should let you respond to user action in the control.

I'm guessing that if you ask around in the VS forums someone will have already written a framework to do this.

Mike

|||

Hi Mike,

Thanks for clarification. I must admit that I get quite lost when trying to find appropriate forum... Any document or article listing all forums and their purposes? Would certainly help!

I followed your link but anything I can find on MSDN is an example using 2 datagridviews on same form. For my case, I just try to find a way to, first, select a SQL table record from datagridview in form1, open a new form, form2, where I will enable user to update record's content. Scenario I illustrated could also include a second datagridview in form2.

Now browsing through the many forums available, it seems that there is one discussing specific issues regarding datagridview:

MSDN Forums Windows Forms Windows Forms Data Controls and Databinding

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=7&SiteID=1

Hope I will be able to get any hint from there!

Thanks for your help,

Stphane

|||

Hi Stephanie,

You might be confused because many of the VS examples and Walkthroughs, including this one, do not walk you through using the VS UI to create the example, they use a 100% code based solution. The sample that I linked to provides code that programatically creates a form with two DataGridViews on it, attaches those DGVs to BindingSources and then hooks them up to the Northwind Database.

VS doesn't have many help topics that walk through specific tasks using the UI tools. I looked around on http://windowsforms.com and didn't really find anything that discusses how to use the UI to do this either, but you're welcome to take a look yourself as you may find some interesting material there.

There isn't a document listing all the fourms, but this page has them all. Each has a short description which hopefully describes it's use.

Mike

|||

Hi again Mike,

Thanks for links. I will keep looking around. I got my hand on a book which seems to give some hints about DGVs. Hope I will find something...

Stphane

Database versioning SQL Server 2000

Where can we find a Database Version Control Tool for all the database objects, not only for stored procedures like Visual SourceSafe.I am not sure exactly what you are after but if you want to be able to capture scripts of all objects in a database into sourcesafe then try:

http://www.sqlservercentral.com/products/bwunder/archiveutility/

for a neat little freeware utility.

P.S. You may need to register on sqlservercentral.com|||There is SQL Source Control 2003 http://www.skilledsoftware.com/sqlsourcecontrol.htm and Embarcadero Change Manager. The first uses integration with MS Visual Source Safe, the second stores scripted versions locally or on the server. I never worked with any of them though.|||check out these links, may be they'll be helpful.

http://www.nigelrivett.net/DMOScriptAllDatabases.html
http://www.nigelrivett.net/SQLServerReleaseControl.htm