How to handle sqlcommand parameter that could be null?

I have this sql sqlcommand:

string sql = "INSERT Zones (ZName, TypeID, Description) VALUES (@dotnet.itags.org.ZName, @dotnet.itags.org.typeID, @dotnet.itags.org.description)";

command.Parameters.Add("@dotnet.itags.org.description", SqlDbType.NVarChar, 100).Value = description;

The column Description is nullable. But if the variable description is null, it seems the paramter @dotnet.itags.org.description can't be passed correctly.

How can I pass a null parameter to sql server?

Thanks!

You can send DBNull.Value as a value, that is it.

how to handle sql with selecting attributes from multiple tables

a sql like: "select t1.a, t2.b from t1, t2 ...",
how does ado.net handle the query result from this kind of sql?
thx.
hongyuyour question is unclear. but, for ado.net doesn't look any difference that
result is coming from one table or 10 tables as far as retrieveal is the
only aim.
say, it gives the same result as query analyzer/sql * plus gives, if you
write this query in that tools.
Rajesh Patel
"Jeff Lu" <hl_107@.yahoo.com> wrote in message
news:eA7LdphkDHA.2676@.TK2MSFTNGP11.phx.gbl...
> a sql like: "select t1.a, t2.b from t1, t2 ...",
> how does ado.net handle the query result from this kind of sql?
> thx.
> hongyu
>
One ResultSet, one DataTable
"Jeff Lu" <hl_107@.yahoo.com> wrote in message
news:eA7LdphkDHA.2676@.TK2MSFTNGP11.phx.gbl...
> a sql like: "select t1.a, t2.b from t1, t2 ...",
> how does ado.net handle the query result from this kind of sql?
> thx.
> hongyu
>
if i use DataReader to read the query results, how to get the meta data
of the columns from different tables?
thx.
Greg wrote:
> One ResultSet, one DataTable
>
> "Jeff Lu" <hl_107@.yahoo.com> wrote in message
> news:eA7LdphkDHA.2676@.TK2MSFTNGP11.phx.gbl...
>>a sql like: "select t1.a, t2.b from t1, t2 ...",
>>how does ado.net handle the query result from this kind of sql?
>>thx.
>>hongyu
>>
>
>
suppose there are 2 tables with 1-m relationship,
Order and Transaction(with orderId as the foreign key).
i want to get some Tx columns as well as some Order columns within
a single sql, which can be "select tx.attr1, tx.attr2, o.attr1 from
Transaction tx, Order o where tx.seq=? and tx.orderId=o.orderId".
with the query result from such a query, i can build a entity(let's
say a ReportingEntity) through some O-R mapping mechanism. The O-R
mapping layer needs meta data of the query result to build/bind
the entity. so, for each column/value of the DataReader, i
need to know its DB table name, DB column name and maybe its type.
DataReader.GetSchemaTable returns a DataTable, i didn't see a API
for getting the meta data across multiple tables mentioned above.
i guess i must have missed sth.
thx for your help.
William Ryan wrote:
> If you are using tables like that, you may want to consider using a
> DataRelation and pulling the tables individually, connecting them after
> you've grabbed them from the DB. If you are joining tables, you are pulling
> over redundant data that eats up bandwidth and depending on size, can really
> hinder performance. That's not to say that you should never join tables
> server side...but make sure that's really the way you want to do it. From
> your later post, you mention the meta data. I'm not sure what platform you
> are referring to, or what specific meta data you are referring to, but
> DataReader.GetSchemaTable will give you basic information about the fields
> pulled over.
> What specifically in the MD are you looking for? Let me know and I can
> probably be of more help.
> Bill
> "Jeff Lu" <hl_107@.yahoo.com> wrote in message
> news:eA7LdphkDHA.2676@.TK2MSFTNGP11.phx.gbl...
>>a sql like: "select t1.a, t2.b from t1, t2 ...",
>>how does ado.net handle the query result from this kind of sql?
>>thx.
>>hongyu
>>
>
>

How to handle postback on gridview without mixing records.

I have a griview to list a list of records, on each record, I have set status/delete button, they have commandname/commandargument, once they are clicked, record will be marked or deleted, those actions will not go to a different page.

The problem is when there is a new record added by others when one user click for example "set status" button, when it postbacks one more record is added on top of the list, and then the command will fire on the record above the record the user clicked on since the whole list shifted down since there is a new one added on top.

Any idea how to handle this?

thanks

looks like you disabled the view state. And also check for IsPostback before databinding the gridview.

like

Page_load()

{

if( ! isPostback )

{

GridView.DataBind();

}

}

thanks

right , I disabled viewstate.

If do like you said, how to refresh the gridvew, also, after user click the button, if they click F5, it seems it will fire the click again

oh, since I disable viewstate, I have to databind in every load, right?

but if you databind on every load you will end up in the same problem you mentioned. I would suggest enable view state and databind once you do update or delete functionality.

I see, you suggested me to turn on viewstate. That for sure is a solution.

Page_load()

{

if( ! isPostback )

{

GridView.DataBind();

}

}

Doesn't look like you read the other replies, how does that work without viewstate?

How to Handle Oracles XMLElement output in VS.NET 2005

Hi,

Oracle 9i, VS.NET 2005

I have a query that uses Oracle's XMLElement function. It returns XML. What I don't know, is how I use VS.NET to handle the outupt. I just want to take this output and write it to a file. Has anyone done this before? How is this accomplished?

I'm trying something like this but I've no idea if this is correct or not:

// connect to oracle

OracleConnection oConn =newOracleConnection("Data Source=databasename;Persist Security Info=True;User ID=userid;Password=pwd");OracleCommand oCommand = oConn.CreateCommand();

oCommand.CommandText =

"SELECT XMLElement( a huge multiline, multi join statement.....";

oCommand.CommandType =

CommandType.Text;

oConn.Open();

// retrieve the output.OracleDataReader oread = oCommand.ExecuteReader();

while (oread.Read())

{

Response.Write(oread.GetString(0) + "<br />" );

}

oread.Close();

oConn.Close();

The query runs in SQLPlus.

However, I'm slightly mystified as to what to use to handle the output from Oracle's XMLElement. Should I use an OracleDataReader?

Thanks in advance,

Paul

Hi,

I think you can return as a string to the c# code in the form XML string.Then use XMLDOM object loadXML(XML string) method so that you get dom object with xml data then if wanted to load it into dataset use DOM object's DOM.dataset() method.

It may help you out

How to handle online event registration

We have a database that keeps track of the people that attend our events. We want to add the ability for people to register online. I'm just looking for any thoughts or suggestions on how the database should/could be setup to handle this. We have a client table and an event fee table in our database now.

I thought I would revisit this post to see if anyone new is out there with any ideas they can share.

How to handle Numeric or Date Null Data or COlumn when creating query to insert into excel sheet

I am creating Excel Sheet using Microsoft.Jet.OLEDB.4.0 Create Table Query. It works well and I can transfer my data to Excel Sheet.

The problem is whenever I have some numeric or date data and it is null then it passes nothing in query so there is error of Insert Into.

Whenever I have stateid is null then it forms query like Insert Into City (CityID,City,StateID) Values (1,'Mumbai',).

How to handle Numeric or Date Null Data or COlumn when creating query to insert into excel sheet?

Excel is not true structured database. In a case if columns inside of the spreadsheet contain mixed data, provider will start to return and store NULL values if it cannot detect type of the cell. For example, assuming spreadsheet has column A1 where some values are strings and other one dates. First provider scans first N rows and detects datatype of the column. If it detects that column contains strings, then it will work with the dates as with NULLs and vice versa. The only thing you could do with Jet 4.0 is to force it to treat all the values as strings, if you add IMEX=1 to the Extended Properties of the connection string. But in this case you will lose data types completely. I faced this issue long time ago and I decided to go my own way of creating component for it. Another way is to use Office Tools from Microsoft, but they still COM based and require a lot of the resources during run-time

how to handle nulls in a RadioButtonList

My webform RadioButtonList is bound to a column from a Sql Server dataSource. The radioButtonList is in a formView which starts up in edit mode. My question is how to handle null values because the only way I could get the page to come up when the columnn value is null is to have an item in the radio list which is setup for nulls such as, "Unknown" in the Text property and -1 in the value property. I understand that the RadioButtonList control is supposed to default to nulls which means that nothing in the radio list is selected - this is the behavior that I want, but I get the error below complaining that null (-1) is not valid because it does not exist as a valid value in the radio list: I have tried -1, '', etc. with no success.

'RadioButtonList2' has a SelectedValue which is invalid because it does not exist in the list of items.
Parameter name: value

How should I handle nulls at page startup?

Thanks.

While setting SelectedValue = null will normally allow no default selection for the RadioButtonList control, trying to set it to null in binding (using either Eval or Bind) doesn't work and throws an exception. Going through a method, however, seems to work as follows:

[ASPX]

<ItemTemplate>

<asp:RadioButtonListID="RadioButtonList1"runat="server"SelectedValue='<%# myFunc(Eval("NumValue").ToString()) %>'>

<asp:ListItemValue="1"Text="Item 001"></asp:ListItem>

<asp:ListItemValue="2"Text="Item 002"></asp:ListItem>

<asp:ListItemValue="3"Text="Item 003"></asp:ListItem>

<asp:ListItemValue="4"Text="Item 004"></asp:ListItem>

<asp:ListItemValue="5"Text="Item 005"></asp:ListItem>

</asp:RadioButtonList>

</ItemTemplate>

[code-behind: C#]

publicstring myFunc(string val)

{

if (val =="")

returnnull;

else

return val;

}

Thank you, the myFunc worked great for handling nulls, But it caused another problem with the update statement. My update statement no longer works because it has lost the identity of the bound variable somehow.

The default SelectedValue works fine:

aspx:

SelectedValue='<%# Bind("myField") %>'

UpdateCommand="update myTable setmyField=@.myField

But with your custom SelectedValue code I get the error at the bottom:

SelectedValue='<%# myFunc(Eval("r1").ToString()) %>'

UpdateCommand="update myTable setmyField=@.myField

here is the error:

Must declare the variable'@.myField'.

[SqlException (0x80131904): Must declare the variable'@.myField'.]

The customization to SelectedValue caused this, any idea why?

Thanks.

I can see why this would be an issue when trying to update since it deviates from the binding expected by the framework. Interesting situation you have here. I'll have to look into this before giving you an answer. I wouldn't be surprised if someone already faced such situation and resolved it.
Thanks. I am appreciative of your help.
I tried to see if there's a workaround for this issue, but I'm afraid there isn't as far as I can see. I thought that there might be a way to bind a NULL value into SelectedValue property (which can be done if a direct null value is assigned), but when binding to a table column, it rejects it for some reason. This problem/behavior may be related to the fact that RadioButtonList isn't meant to provide an option for "no selection" (i.e. once a selection is made, you cannot go back to a no-selection state), although the starting point allows no selection state. Perhaps the DropDownList control serves your purpose better since you can have a default value that represents no selection. Another option might be to add another item for your RadioButtonList that represents no selection and default what would have been null value to that choice. Sorry I don't have a better news for you.

Here's another way to handle this. Create a user control with your RadioButtonList in it, and create a property in it called SelectedValue such as following:

public string SelectedValue
{
get
{
return RadioButtonList1.SelectedValue;
}
set
{
if (value != "")
RadioButtonList1.SelectedVAlue = value;
}
}

This way you conditionally avoid setting the RadioButtonList value when it's null. Then you can happily use the SelectedValue='<%# Bind("FieldName") %>' syntax in your user control tag declaration.

How to Handle Nulls

Nulls continue to kick my rear. It would have been so much easier to just
fill the DB with Zeros. But then people flame me for wasting drive space.
I just cant win! :)
Anyways, below is a snippet of code I'm using (minus the extras vacation and
holiday...). I need to add these fields up (along with other math) but a
lot of times the fields will be null. I cant seem to be able to do anything
with null fields regardless of how I format them. So I used try statements
and new variables to move the field data in to. Since the only result I can
get from adding these fields is an error the try statement is perfect
because then I can continue and fill with "0" since that would be the case.
BUT
Doing it this way takes a LONG time because you have to wait to each to
error out. I'm looping through these calcs many many times.
Try
RegHours = EmployeeRow.Item("Reg Hours")
Catch ex As Exception
RegHours = "0"
End Try
Try
OverHours = EmployeeRow.Item("Over Hours")
Catch ex As Exception
OverHours = "0"
End Try
If RegHours + OverHours < 80 Then...
So how can I format the following where if one field = 8 and the other field
= Null I still wind up with 8 after adding them instead of a system error
stating "anything" cant be converted to null.
EmployeeRow.Item("Reg Hours") + EmployeeRow.Item("Over Hours")
Thanks for any help,
JusitnHi Justin,
Not your code however maybe this helps better..
Using VB it is
datarowfield Is DbValue.Null (means Null in database notation)
Objectreference Is Nothing (means there is no reference)
Value = Nothing (means the value is in the defaultstate what can be "", 0 or
01-01-1900 00:00:00)
I hope this helps?
Cor
Hi
Or you can convert them to 0 while retrieving values from the table. For example in SQL Server you can do something like
select columnA = coalesce(ColumnA,'0'), columnB = coalesce(ColumnB,'0') from TableA
So, if ColumnA and B are null they are replaced with Zeros. This will prevent you from checking for nulls in your api. Hope this helps.
--
Ibrahim
"Justin Emlay" wrote:
> Nulls continue to kick my rear. It would have been so much easier to just
> fill the DB with Zeros. But then people flame me for wasting drive space.
> I just cant win! :)
> Anyways, below is a snippet of code I'm using (minus the extras vacation and
> holiday...). I need to add these fields up (along with other math) but a
> lot of times the fields will be null. I cant seem to be able to do anything
> with null fields regardless of how I format them. So I used try statements
> and new variables to move the field data in to. Since the only result I can
> get from adding these fields is an error the try statement is perfect
> because then I can continue and fill with "0" since that would be the case.
> BUT
> Doing it this way takes a LONG time because you have to wait to each to
> error out. I'm looping through these calcs many many times.
> Try
> RegHours = EmployeeRow.Item("Reg Hours")
> Catch ex As Exception
> RegHours = "0"
> End Try
> Try
> OverHours = EmployeeRow.Item("Over Hours")
> Catch ex As Exception
> OverHours = "0"
> End Try
> If RegHours + OverHours < 80 Then...
>
> So how can I format the following where if one field = 8 and the other field
> = Null I still wind up with 8 after adding them instead of a system error
> stating "anything" cant be converted to null.
> EmployeeRow.Item("Reg Hours") + EmployeeRow.Item("Over Hours")
> Thanks for any help,
> Jusitn
>
>
Is that supposed to be my select statement? I didn't understand it :(
This is one of my statements that I'm currently using:
SELECT TimeSheets.Employee, SUM(TimeSheets.[Hol Hours]) AS [Hol Hours],
TimeSheets.[Job Number], SUM(TimeSheets.[Over Hours]) AS [Over Hours],
SUM(TimeSheets.[Reg Hours]) AS [Reg Hours], SUM(TimeSheets.[Vac Hours]) AS
[Vac Hours], Employee.Type, TimeSheets.Dept, TimeSheets.GL FROM (Employee
INNER JOIN TimeSheets ON Employee.Employee = TimeSheets.Employee) WHERE
[Period Start] = @.PeriodStart GROUP BY TimeSheets.Employee, TimeSheets.[Job
Number], Employee.Type, TimeSheets.Dept, TimeSheets.GL ORDER BY
TimeSheets.Employee, TimeSheets.GL
"Ibrahim Shameeque" <IbrahimShameeque@.discussions.microsoft.com> wrote in
message news:AEAE83CC-FE57-4306-8D46-FC068A0AFFEC@.microsoft.com...
> Hi
> Or you can convert them to 0 while retrieving values from the table. For
example in SQL Server you can do something like
> select columnA = coalesce(ColumnA,'0'), columnB = coalesce(ColumnB,'0')
from TableA
> So, if ColumnA and B are null they are replaced with Zeros. This will
prevent you from checking for nulls in your api. Hope this helps.
> --
> Ibrahim
>
> "Justin Emlay" wrote:
> > Nulls continue to kick my rear. It would have been so much easier to
just
> > fill the DB with Zeros. But then people flame me for wasting drive
space.
> > I just cant win! :)
> >
> > Anyways, below is a snippet of code I'm using (minus the extras vacation
and
> > holiday...). I need to add these fields up (along with other math) but
a
> > lot of times the fields will be null. I cant seem to be able to do
anything
> > with null fields regardless of how I format them. So I used try
statements
> > and new variables to move the field data in to. Since the only result I
can
> > get from adding these fields is an error the try statement is perfect
> > because then I can continue and fill with "0" since that would be the
case.
> >
> > BUT
> >
> > Doing it this way takes a LONG time because you have to wait to each to
> > error out. I'm looping through these calcs many many times.
> >
> > Try
> > RegHours = EmployeeRow.Item("Reg Hours")
> > Catch ex As Exception
> > RegHours = "0"
> > End Try
> > Try
> > OverHours = EmployeeRow.Item("Over Hours")
> > Catch ex As Exception
> > OverHours = "0"
> > End Try
> >
> > If RegHours + OverHours < 80 Then...
> >
> >
> > So how can I format the following where if one field = 8 and the other
field
> > = Null I still wind up with 8 after adding them instead of a system
error
> > stating "anything" cant be converted to null.
> >
> > EmployeeRow.Item("Reg Hours") + EmployeeRow.Item("Over Hours")
> >
> > Thanks for any help,
> > Jusitn
> >
> >
> >
> >
I googled for "Coalesce" and got nothing but unrelated items. The one link
that did look like a match discussed T-SQL.

How to handle NULL values in SQL Server

I'm building an ASP.NET application using VB.NET and SQL Sever 2000.

I'm returning data from a table to my datagrid that contains a datetime field. The date values for some of the records are null in the database table. My strategy (be it good or bad) is to update every field in a record during the datagrid's UpdateCommand event, regardless of whether each field's data has changed.

When I update a record whose date was null and has not been modified, SQL Server places a 1/1/1900 value in the date field. I was expecting the datetime field to remain null. Would someone be able to enlighten me on how best to handle this?

My update code looks like this:

Private Sub dgrdEaTask_UpdateCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles dgrdEaTask.UpdateCommand
Dim Success As Boolean
Dim intEaId As Integer
Dim txtEaTask As TextBox
Dim txtDueDate As TextBox

txtEaTask = e.Item.Cells(2).Controls(0)
txtDueDate = e.Item.Cells(11).Controls(0)
intEaId = dgrdEaTask.DataKeys(e.Item.ItemIndex)

'Call the update method in the oEaTasks class
Success = oEaTasks.Update(txtEaId.Text, txtEaTask.Text, txtDueDate.Text)

If Success Then
dgrdEaTask.EditItemIndex = -1
BindGrid()
Else
'There was a problem updating the data
End If

End Sub

Thanks!use dbnull.value incase of null...

hth
Thanks!

I'll research it and give it try.

how to handle null values for Templatefield, hyperlink etc...

Nulldisplaytext work for asp:boundfields
<asp:BoundFieldDataField="fax"HeaderText="fax"SortExpression="fax"/>

how to make it work for

<asp:HyperLinkFieldDataNavigateUrlFields="supplierID"DataNavigateUrlFormatString="editSupplier.aspx?supplierID={0}"DataTextField="supplierID"HeaderText="SupplierID">
<ItemStyleHorizontalAlign="Left"/>
</asp:HyperLinkField>

<asp:TemplateFieldHeaderText="Supplier Name"SortExpression="SupplierName">
<ItemStyleHorizontalAlign="Left"/>
<ItemTemplate>
<%#Eval("SupplierName")%>
</ItemTemplate>
</asp:TemplateField>

In a TemplateField, you can use a databinding statement like this (in C#):

<asp:TemplateField>
<ItemTemplate>
<asp:Label runat=server Text='<%# Eval("CustomerID") == null ? "nullDisplayText" : Eval("CustomerID") %>' />
</ItemTemplate>
</asp:TemplateField>

There's no easy way to do this with a HyperLinkField, so consider converting your HyperLinkField to a TemplateField to do the method above.

This is what I use to do to handle null,

select isnull(field,'')as field from table. If i do this do you think it will be an performance issue as I have to do for each an every field.

and what's the replacement for your code in VB

Text='<%# Eval("CustomerID") == null ? "nullDisplayText" : Eval("CustomerID") %>'

bhavin78 wrote:

and what's the replacement for your code in VB

Text='<%# Eval("CustomerID") == null ? "nullDisplayText" : Eval("CustomerID") %>'

Try,

Text='<%# IIF(Eval("CustomerID") is nothing,"nullDisplayText",Eval("CustomerID")) %>'

which one would be better to use as far as performance goes

isnull("data",'') in sql query

or

Text='<%# IIF(Eval("CustomerID") is nothing,"nullDisplayText",Eval("CustomerID")) %>'

How to handle null value mapping in SqlDataSource

Hi, guys

If I pass null value to a parameter of SQL automatically generated by SqlDataSource (as WHERE field1=@dotnet.itags.org.field1, where field1 is of type ntext or nvarchar) at design-time, it seems SqlDataSource can not update appricately, throwing an exception: [SqlException (0x80131904): The data types ntext and nvarchar are incompatible in the equal to operator.],

even though the field1 in the db table is null-allowable.

How do I handle the situation where I must pass null value to update any field permitting null value.

Thanks,

Ricky.

What's the type of @.field1, and what are you setting it to? Post the relevant section of your code.

How to Handle Null Value from <%# Bind(...

I am trying to retrieve the values for a selected row in a FormView to allow editing. One of the fields ("photo") can be null and when the following statement gets executed it throws an error that it cannot convert a null to an integer.

<gs:PhotopickerID="Photopicker1"runat="server"ImageId='<%# Bind("photo") %>'/>

I recall that in 1.1 I could replace the olde EVAL with a function that could check for a null and return a zero instead? Can that be done here, and if so, what is the syntax?

Hi Wayne,

Bind is a two-way data operation -- is that what you are trying to use with this?

Otherwise, you could just do an <%# Eval( CallSomeFunction("photo")) %> and define the "CallSomeFunction" (or another name) on your page or control to convert it to an appropriate value.

Alternatively, you could define ImageId on the control to accept null and handle the conversion there.

Hope this helps,

Scott

I am actually trying to modify the Club Starter Kit so most of the original code is from that kit. I did figure out that Bind was a 2 way process and I believe that the way that is written, the 2 way binding is necessary. I guess I may have to re-code that whole section?

Could you expand on defining Imageid on the control? I am not familiar with that approach.

Assuming you are talking about the control defined in photos_formpicker.ascx:

The easiest fix is to add a new property to the control for the ImageID, and have it take object rather than string. You could change the type on the existing property, but you'd then also have to change the code throughout the control. This way you just need to change the property name used to bind to the database from ImageID to PhotoImageID:

constint DEFAULTIMAGEID = 0;

privateint ImageId

{

get

{

EnsureChildControls();

if (viewswitch.ActiveViewIndex == 1)

{

returnConvert.ToInt32(selectedimage.Value);

}

else

{

object id = ViewState["ImageID"];

if (id ==null)

{

ViewState["ImageID"] = DEFAULTIMAGEID;

return DEFAULTIMAGEID;

}

else

{

returnConvert.ToInt32(id);

}

}

}

set { ViewState["ImageID"] =value; }

}

publicobject PhotoImageID

{

get {return ImageId; }

set { ViewState["ImageID"] = (value ==DBNull.Value) ? DEFAULTIMAGEID :Convert.ToInt32(value); }

}

Thank you very much. That is all a little beyond my current understanding so I'll have to do a bit of reading to make sure I impliment it correctly.

I appreciate the very complete response.

How to handle null value

I got trouble with System.Null.ReferenceException. Please guide me how to manipulate it.

Below is my error code line, when the result of my query returns null value.

string FATypeName = SelectCommand.ExecuteScalar().ToString().Trim();

Thanks,

string FATypeName = SelectCommand.ExecuteScalar()
if ( FATypeName!=null )
{
FATypeName=FATypeName.ToString().Trim();
}
else
{
FATypeName=String.Empty;
}

How to handle NULL fields-error with fill method

I have a data adapter that is using a stored procedure and when I get to the fill method to fill a dataset I get the following error
Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.
The results of the procedure has 6 columns by 50 rows with some NULL field values throughout so think the NULLs are causing the problem. Just wondering how to handle this? Thanks Paul.Fixed this problem. Since a change was made to the procedure, reconfiguring the data adapter cleared the problem.

How to handle multiple UserControls that manipulate the same underlying data?

How should I handle the sitaution where two or more UserControls on the same page are using ObjectDataSources that leverage the same business object?

For example, UserControlA and UserControlB are both using BusinessObjectA via their respective ObjectDataSource. However, there does not appear to be a guarentee that UserControlA's events (i.e., DELETing data) fire before UserControlB's events (i.e., SELECTing data.) The result is that UserControlB can (and does) display incorrect data for the page, as it performed its SELECT before UserControlA performed its DELETE.

The situation that I find myself in at the moment is a page made up of GenericWebParts via dragging these UserControls into a WebPartZone. The controls are designed to be independant of one another, hence the reason why they each have their own ObjectDataSource. The problem is arising just as described above ... when I delete data via one UserControl, another UserControl on the page that makes decisions based upon the same data does not see the changes until a forced page refresh.

I did a little digging into this tonight and sure enough, UserControlB is being loaded before the events for UserControlA are firing, resulting in UserControlB read data before UserControlA has a chance to delete it.

I'm able to hack the solution by performing the delete opeartion from another page. So instead of enabling deletes via UserControlA's GridView, I've added a Hyperlink Field to ControlA's GridView which calls a Delete.aspx and then redirects back to the default page. However, this does not feel very clean at all.

how to handle massive amounts of data in a data table

Hi,

I have over 200,000 records in a database and need the user to be able to view the data and edit some records. I have an editable data grid setup but when I query stuff to try to show around 30,000 in a table it just crashes.

Thanks

Why not page it with like 100 per page or do they need to see all 30,000 at once?

200,000 records in 1 page... in one go.. yea that will mess up your computer.

This is due to the raw amounts of data being passed to the clients computer from your webpage, and the viewstate is probably massive as well.

What you need to do is bring in paging for your DataGrid, as well as filtering (Date Filtering would be the easiest if you have date logs for the data your returning).

You also might want to turn EnableViewState on your DataGrid to false. This will force you to REBIND after everypostback, but it will not store the datasource in the viewstate (this is a big issue for large recordsets).

Also, look into paging.

----

If you wanted to play around to get more information on this, change your select statement to only select the top 1,000 rows. Then render it and view the properties of the page (right click->properties). Look at the size of the page.

Then increase it to 5,000 rows, 10,000 rows, 20,000 rows etc. and you will see how much data is being transfered to the client. As well, think about what the browser needs to do in order to render that data.

thanks ... I have decided that it is best to only allow them to see the records week by week.

How to handle linkbutton click?

guys I've met with a problem

I need to create a linkbutton control dynamicaly within a repeater

Dim X As New RepeaterItem(0, ListItemType.Item)

Dim LB As New LinkButton
LB.Text = SomeText
LB.CommandArgument = Value
LB.CausesValidation = True
AddHandler LB.Click, AddressOf PageClick
X.Controls.Add(LB)

Rep.Controls.Add(X)

...

Protected Sub PageClick(ByVal sender As Object, ByVal e As EventArgs)

Search(3, TryCast(sender, LinkButton).CommandArgument)

End Sub

but when I click that linkbutton it doesn't fire. what I do wrong?

thanks

Make sure you are re-creating this LinkButton each and every PostBack of the Page.

Add a command name to your LinkButton something like "MyLinkButton" then in the ItemCommand repeater event check if the args, hang on I will put up a sample.

ProtectedSub rptMyRepeater_ItemCommand(ByVal sourceAsObject,ByVal eAs System.Web.UI.WebControls.RepeaterCommandEventArgs)Handles rptMyRepeater.ItemCommand

If e.CommandName ="MyLinkButton"Then
' the link button was clicked
EndIf

EndSub

Eah, I need to recreate them, it works now. Is there a way to avoid recreation on every postback?

Sure, if you wanted, you could always have some type of flag stored in your Page's ViewState collection which you can use to re-create your controls or not. However, no re-creation, no event handlers.

How to handle large result sets in Windows Forms

A typical szenario in a database-centered application is that a user
(accidentally or on purpose) requests data that would result in a very
large result set, may be millions of rows.
I really like the way how MS Access handles that problem. The first
records are displayed as soon as they are available. When the user
scrolls down he or she experiences the display of the further records
as if they were available locally. Jumping to record number 1,000,000
also as little delay, the same applies for jumping to the end.
Is it possible to achieve the same or a similar behavior in ADO.NET
and Windows Forms? Can I still rely on data binding?
My approach would be:
- Using a background thread for the query to keep the application
responsive and give the user an opportunity to abort the action.
- Using a DataReader to fill a DataTable with the first n records.
- Data bind the DataTable.
- Handle scrolling down or jumping to a specified record by reading
forward from the DataReader into the DataTable. That can't be done
in the background thread, I suppose.
The problem is that I need to keep open the connection all the time
(or until the user scrolles to the end) which has a strong impact on
scalability.
I have a basic idea of how to handle paging in ADO.NET.
How do I support sorting in the DataGrid?
I suppose that is a frequent requirement. There should be some kind of
a design pattern - or even a framework or third-party tool that
supports such an implementation. Does anyone know more?
Thank you in advance.
Daniel FaensenHi Dan,
This is a continual issue. I looked into it extensively about 1 and a half
years ago, and I came up with certain workarounds, but nothing too great.
First, a datareader is not the way to go - it's a fire hose cursor and
doesn't make it possible to do very much very effectively, even searching.
Second, you can always use ado through interop services to bind to a
datasource CONNECTED, not disconnected, as ado .net provides using a
datatable. But you lose a lot of the benefits of ado .net, especially it's
disconnected, non-clashing nature, and it's enormous speed when data is in
memory.
But I think the best solution may be a third party grid, like C1, which I
use (many of the others are probably fine also). C1 also has a data access
class that can do what you described re MS Access, but I haven't chosen to
use it, because my users may want to filter rows while they are in the grid
and that requires gathering the full table and manipulating it, perhaps
again and again during the same session.
Even with the third party grid, I try to get my users to select data
filtered first (both re data and/or re columns to be returned), so that is
all they have in the grid at one time - they usually can do this because
they know what they're after. If they want the whole table - my largest is
about 25 cols wide, 1.5 million rows - they wait for the load - which isn't
terrible (approx 75 seconds to load).
I long ago spoke to MS about adding a connected element to ado .net, and
they said they would, but I don't believe it will happen.
HTH,
Bernie Yaeger
"Daniel Faensen" <faensen@.inf.fu-berlin.de> wrote in message
news:e9099bc4.0411050227.40853353@.posting.google.com...
>A typical szenario in a database-centered application is that a user
> (accidentally or on purpose) requests data that would result in a very
> large result set, may be millions of rows.
> I really like the way how MS Access handles that problem. The first
> records are displayed as soon as they are available. When the user
> scrolls down he or she experiences the display of the further records
> as if they were available locally. Jumping to record number 1,000,000
> also as little delay, the same applies for jumping to the end.
> Is it possible to achieve the same or a similar behavior in ADO.NET
> and Windows Forms? Can I still rely on data binding?
> My approach would be:
> - Using a background thread for the query to keep the application
> responsive and give the user an opportunity to abort the action.
> - Using a DataReader to fill a DataTable with the first n records.
> - Data bind the DataTable.
> - Handle scrolling down or jumping to a specified record by reading
> forward from the DataReader into the DataTable. That can't be done
> in the background thread, I suppose.
> The problem is that I need to keep open the connection all the time
> (or until the user scrolles to the end) which has a strong impact on
> scalability.
> I have a basic idea of how to handle paging in ADO.NET.
> How do I support sorting in the DataGrid?
> I suppose that is a frequent requirement. There should be some kind of
> a design pattern - or even a framework or third-party tool that
> supports such an implementation. Does anyone know more?
> Thank you in advance.
> Daniel Faensen

How to handle large recordsets?

When retrieving a recordset with more than 100.000 records, it needs some time to fill a datagrid.
How can the grid be filled quickly?
I read something about caching, but that doesn't seem to work.
MauriceI think you should use asynchronous query that means you have to open the recordset whith the adSyncExecute and adSyncFetch parameters.
You will get synchronously a first cache of records (so you can populate the visible part of your grid) and then asynchronously the rest of records. To do this you must trap the event FetchProgress on your recordset.

I've got the same problem but I don't know how to detect when the query returns no records. If you have an idea about this, think to me

Pascale
Pascale,

Can you provide me with some source code execute your suggestions?

Maurice.
This method is not very simple to use in C++ but this is a good sample from MSDN

Pascale

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

ADO Events Model Example (VC++)


The Visual C++ section of ADO Event Instantiation by Language gives a general description of how to instantiate the ADO event model. The following is a specific example of instantiating the event model within the environment created by the #import directive.

The general description uses adoint.h as a reference for method signatures. However, a few details in the general description change slightly as a result of using the #import directive:

The #import directive resolves typedef's, and method signature data types and modifiers to their fundamental forms.

The pure virtual methods that must be overwritten are all prefixed by "raw_".
Some of the code simply reflects coding style.

The pointer to IUnknown used by the Advise method is obtained explicitly with a call to QueryInterface.

You don't need to explicitly code a destructor in the class definitions.

You may want to code more robust implementations of QueryInterface, AddRef, and Release.

The __uuidof() directive is used extensively to obtain interface IDs.
Finally, the example contains some working code.

The example is written as a console application.

You should insert your own code under the comment, "// Do some work".

All the event handlers default to doing nothing, and canceling further notifications. You should insert the appropriate code for your application, and allow notifications if required.
// eventmodel.cpp : Defines the entry point for the console application.
//

#import "c:\Program Files\Common Files\System\ADO\msado15.dll" \
no_namespace rename("EOF", "EndOfFile")
#include
#include

//--The Connection events--------------

class CConnEvent : public ConnectionEventsVt
{
private:
ULONG m_cRef;
public:
CConnEvent() { m_cRef = 0; };
~CConnEvent() {};

STDMETHODIMP QueryInterface(REFIID riid, void ** ppv);
STDMETHODIMP_(ULONG) AddRef(void);
STDMETHODIMP_(ULONG) Release(void);

STDMETHODIMP raw_InfoMessage(
struct Error *pError,
EventStatusEnum *adStatus,
struct _Connection *pConnection);

STDMETHODIMP raw_BeginTransComplete(
LONG TransactionLevel,
struct Error *pError,
EventStatusEnum *adStatus,
struct _Connection *pConnection);

STDMETHODIMP raw_CommitTransComplete(
struct Error *pError,
EventStatusEnum *adStatus,
struct _Connection *pConnection);

STDMETHODIMP raw_RollbackTransComplete(
struct Error *pError,
EventStatusEnum *adStatus,
struct _Connection *pConnection);

STDMETHODIMP raw_WillExecute(
BSTR *Source,
CursorTypeEnum *CursorType,
LockTypeEnum *LockType,
long *Options,
EventStatusEnum *adStatus,
struct _Command *pCommand,
struct _Recordset *pRecordset,
struct _Connection *pConnection);

STDMETHODIMP raw_ExecuteComplete(
LONG RecordsAffected,
struct Error *pError,
EventStatusEnum *adStatus,
struct _Command *pCommand,
struct _Recordset *pRecordset,
struct _Connection *pConnection);

STDMETHODIMP raw_WillConnect(
BSTR *ConnectionString,
BSTR *UserID,
BSTR *Password,
long *Options,
EventStatusEnum *adStatus,
struct _Connection *pConnection);

STDMETHODIMP raw_ConnectComplete(
struct Error *pError,
EventStatusEnum *adStatus,
struct _Connection *pConnection);

STDMETHODIMP raw_Disconnect(
EventStatusEnum *adStatus,
struct _Connection *pConnection);
};

//--The Recordset events--------------

class CRstEvent : public RecordsetEventsVt
{
private:
ULONG m_cRef;
public:
CRstEvent() { m_cRef = 0; };
~CRstEvent() {};

STDMETHODIMP QueryInterface(REFIID riid, void ** ppv);
STDMETHODIMP_(ULONG) AddRef(void);
STDMETHODIMP_(ULONG) Release(void);

STDMETHODIMP raw_WillChangeField(
LONG cFields,
VARIANT Fields,
EventStatusEnum *adStatus,
struct _Recordset *pRecordset);

STDMETHODIMP raw_FieldChangeComplete(
LONG cFields,
VARIANT Fields,
struct Error *pError,
EventStatusEnum *adStatus,
struct _Recordset *pRecordset);

STDMETHODIMP raw_WillChangeRecord(
EventReasonEnum adReason,
LONG cRecords,
EventStatusEnum *adStatus,
struct _Recordset *pRecordset);

STDMETHODIMP raw_RecordChangeComplete(
EventReasonEnum adReason,
LONG cRecords,
struct Error *pError,
EventStatusEnum *adStatus,
struct _Recordset *pRecordset);

STDMETHODIMP raw_WillChangeRecordset(
EventReasonEnum adReason,
EventStatusEnum *adStatus,
struct _Recordset *pRecordset);

STDMETHODIMP raw_RecordsetChangeComplete(
EventReasonEnum adReason,
struct Error *pError,
EventStatusEnum *adStatus,
struct _Recordset *pRecordset);

STDMETHODIMP raw_WillMove(
EventReasonEnum adReason,
EventStatusEnum *adStatus,
struct _Recordset *pRecordset);

STDMETHODIMP raw_MoveComplete(
EventReasonEnum adReason,
struct Error *pError,
EventStatusEnum *adStatus,
struct _Recordset *pRecordset);

STDMETHODIMP raw_EndOfRecordset(
VARIANT_BOOL *fMoreData,
EventStatusEnum *adStatus,
struct _Recordset *pRecordset);

STDMETHODIMP raw_FetchProgress(
long Progress,
long MaxProgress,
EventStatusEnum *adStatus,
struct _Recordset *pRecordset);

STDMETHODIMP raw_FetchComplete(
struct Error *pError,
EventStatusEnum *adStatus,
struct _Recordset *pRecordset);
};

//--Implement each connection method-----------

STDMETHODIMP CConnEvent::raw_InfoMessage(
struct Error *pError,
EventStatusEnum *adStatus,
struct _Connection *pConnection)
{
*adStatus = adStatusUnwantedEvent;
return S_OK;
};

STDMETHODIMP CConnEvent::raw_BeginTransComplete(
LONG TransactionLevel,
struct Error *pError,
EventStatusEnum *adStatus,
struct _Connection *pConnection)
{
*adStatus = adStatusUnwantedEvent;
return S_OK;
};

STDMETHODIMP CConnEvent::raw_CommitTransComplete(
struct Error *pError,
EventStatusEnum *adStatus,
struct _Connection *pConnection)
{
*adStatus = adStatusUnwantedEvent;
return S_OK;
};

STDMETHODIMP CConnEvent::raw_RollbackTransComplete(
struct Error *pError,
EventStatusEnum *adStatus,
struct _Connection *pConnection)
{
*adStatus = adStatusUnwantedEvent;
return S_OK;
};

STDMETHODIMP CConnEvent::raw_WillExecute(
BSTR *Source,
CursorTypeEnum *CursorType,
LockTypeEnum *LockType,
long *Options,
EventStatusEnum *adStatus,
struct _Command *pCommand,
struct _Recordset *pRecordset,
struct _Connection *pConnection)
{
*adStatus = adStatusUnwantedEvent;
return S_OK;
};

STDMETHODIMP CConnEvent::raw_ExecuteComplete(
LONG RecordsAffected,
struct Error *pError,
EventStatusEnum *adStatus,
struct _Command *pCommand,
struct _Recordset *pRecordset,
struct _Connection *pConnection)
{
*adStatus = adStatusUnwantedEvent;
return S_OK;
};

STDMETHODIMP CConnEvent::raw_WillConnect(
BSTR *ConnectionString,
BSTR *UserID,
BSTR *Password,
long *Options,
EventStatusEnum *adStatus,
struct _Connection *pConnection)
{
*adStatus = adStatusUnwantedEvent;
return S_OK;
};

STDMETHODIMP CConnEvent::raw_ConnectComplete(
struct Error *pError,
EventStatusEnum *adStatus,
struct _Connection *pConnection)
{
*adStatus = adStatusUnwantedEvent;
return S_OK;
};

STDMETHODIMP CConnEvent::raw_Disconnect(
EventStatusEnum *adStatus,
struct _Connection *pConnection)
{
*adStatus = adStatusUnwantedEvent;
return S_OK;
};

//--Implement each recordset method-----------

STDMETHODIMP CRstEvent::raw_WillChangeField(
LONG cFields,
VARIANT Fields,
EventStatusEnum *adStatus,
struct _Recordset *pRecordset)
{
*adStatus = adStatusUnwantedEvent;
return S_OK;
};

STDMETHODIMP CRstEvent::raw_FieldChangeComplete(
LONG cFields,
VARIANT Fields,
struct Error *pError,
EventStatusEnum *adStatus,
struct _Recordset *pRecordset)
{
*adStatus = adStatusUnwantedEvent;
return S_OK;
};

STDMETHODIMP CRstEvent::raw_WillChangeRecord(
EventReasonEnum adReason,
LONG cRecords,
EventStatusEnum *adStatus,
struct _Recordset *pRecordset)
{
*adStatus = adStatusUnwantedEvent;
return S_OK;
};

STDMETHODIMP CRstEvent::raw_RecordChangeComplete(
EventReasonEnum adReason,
LONG cRecords,
struct Error *pError,
EventStatusEnum *adStatus,
struct _Recordset *pRecordset)
{
*adStatus = adStatusUnwantedEvent;
return S_OK;
};

STDMETHODIMP CRstEvent::raw_WillChangeRecordset(
EventReasonEnum adReason,
EventStatusEnum *adStatus,
struct _Recordset *pRecordset)
{
*adStatus = adStatusUnwantedEvent;
return S_OK;
};

STDMETHODIMP CRstEvent::raw_RecordsetChangeComplete(
EventReasonEnum adReason,
struct Error *pError,
EventStatusEnum *adStatus,
struct _Recordset *pRecordset)
{
*adStatus = adStatusUnwantedEvent;
return S_OK;
};

STDMETHODIMP CRstEvent::raw_WillMove(
EventReasonEnum adReason,
EventStatusEnum *adStatus,
struct _Recordset *pRecordset)
{
*adStatus = adStatusUnwantedEvent;
return S_OK;
};

STDMETHODIMP CRstEvent::raw_MoveComplete(
EventReasonEnum adReason,
struct Error *pError,
EventStatusEnum *adStatus,
struct _Recordset *pRecordset)
{
*adStatus = adStatusUnwantedEvent;
return S_OK;
};

STDMETHODIMP CRstEvent::raw_EndOfRecordset(
VARIANT_BOOL *fMoreData,
EventStatusEnum *adStatus,
struct _Recordset *pRecordset)
{
*adStatus = adStatusUnwantedEvent;
return S_OK;
};

STDMETHODIMP CRstEvent::raw_FetchProgress(
long Progress,
long MaxProgress,
EventStatusEnum *adStatus,
struct _Recordset *pRecordset)
{
*adStatus = adStatusUnwantedEvent;
return S_OK;
};

STDMETHODIMP CRstEvent::raw_FetchComplete(
struct Error *pError,
EventStatusEnum *adStatus,
struct _Recordset *pRecordset)
{
*adStatus = adStatusUnwantedEvent;
return S_OK;
};

//--Implement QueryInterface, AddRef, and Release-------

STDMETHODIMP CRstEvent::QueryInterface(REFIID riid, void ** ppv)
{
*ppv = NULL;
if (riid == __uuidof(IUnknown) ||
riid == __uuidof(RecordsetEventsVt)) *ppv = this;
if (*ppv == NULL)
return ResultFromScode(E_NOINTERFACE);
AddRef();
return NOERROR;
}
STDMETHODIMP_(ULONG) CRstEvent::AddRef(void) { return ++m_cRef; };
STDMETHODIMP_(ULONG) CRstEvent::Release()
{
if (0 != --m_cRef) return m_cRef;
delete this;
return 0;
}

STDMETHODIMP CConnEvent::QueryInterface(REFIID riid, void ** ppv)

{
*ppv = NULL;
if (riid == __uuidof(IUnknown) ||
riid == __uuidof(ConnectionEventsVt)) *ppv = this;
if (*ppv == NULL)
return ResultFromScode(E_NOINTERFACE);
AddRef();
return NOERROR;
}
STDMETHODIMP_(ULONG) CConnEvent::AddRef() { return ++m_cRef; };
STDMETHODIMP_(ULONG) CConnEvent::Release()
{
if (0 != --m_cRef) return m_cRef;
delete this;
return 0;
}

//--Write your main block of code------------

int main(int argc, char* argv[])
{
HRESULT hr;
DWORD dwConnEvt;
DWORD dwRstEvt;
IConnectionPointContainer *pCPC = NULL;
IConnectionPoint *pCP = NULL;
IUnknown *pUnk = NULL;
CRstEvent *pRstEvent = NULL;
CConnEvent *pConnEvent= NULL;
int rc = 0;
_RecordsetPtr pRst;
_ConnectionPtr pConn;

::CoInitialize(NULL);

hr = pConn.CreateInstance(__uuidof(Connection));
if (FAILED(hr)) return rc;

hr = pRst.CreateInstance(__uuidof(Recordset));
if (FAILED(hr)) return rc;

// Start using the Connection events

hr = pConn->QueryInterface(__uuidof(IConnectionPointContainer),
(void **)&pCPC);
if (FAILED(hr)) return rc;
hr = pCPC->FindConnectionPoint(__uuidof(ConnectionEvents), &pCP);
pCPC->Release();
if (FAILED(hr)) return rc;

pConnEvent = new CConnEvent();
hr = pConnEvent->QueryInterface(__uuidof(IUnknown), (void **) &pUnk);
if (FAILED(hr)) return rc;
hr = pCP->Advise(pUnk, &dwConnEvt);
pCP->Release();
if (FAILED(hr)) return rc;

// Start using the Recordset events

hr = pRst->QueryInterface(__uuidof(IConnectionPointContainer),
(void **)&pCPC);
if (FAILED(hr)) return rc;
hr = pCPC->FindConnectionPoint(__uuidof(RecordsetEvents), &pCP);
pCPC->Release();
if (FAILED(hr)) return rc;

pRstEvent = new CRstEvent();
hr = pRstEvent->QueryInterface(__uuidof(IUnknown), (void **) &pUnk);
if (FAILED(hr)) return rc;
hr = pCP->Advise(pUnk, &dwRstEvt);
pCP->Release();
if (FAILED(hr)) return rc;

// Do some work

pConn->Open("dsn=Pubs;", "sa", "", adConnectUnspecified);
pRst->Open("SELECT * FROM authors", (IDispatch *) pConn,
adOpenStatic, adLockReadOnly, adCmdText);
pRst->MoveFirst();
while (pRst->EndOfFile == FALSE)
{
wprintf(L"Name = '%s'\n", (wchar_t*)
((_bstr_t) pRst->Fields->GetItem("au_lname")->Value));
pRst->MoveNext();
}

pRst->Close();
pConn->Close();

// Stop using the Connection events

hr = pConn->QueryInterface(__uuidof(IConnectionPointContainer),
(void **) &pCPC);
if (FAILED(hr)) return rc;
hr = pCPC->FindConnectionPoint(__uuidof(ConnectionEvents), &pCP);
pCPC->Release();
if (FAILED(hr)) return rc;
hr = pCP->Unadvise( dwConnEvt );
pCP->Release();
if (FAILED(hr)) return rc;

// Stop using the Recordset events
hr = pRst->QueryInterface(__uuidof(IConnectionPointContainer),
(void **) &pCPC);
if (FAILED(hr)) return rc;
hr = pCPC->FindConnectionPoint(__uuidof(RecordsetEvents), &pCP);
pCPC->Release();
if (FAILED(hr)) return rc;
hr = pCP->Unadvise( dwRstEvt );
pCP->Release();
if (FAILED(hr)) return rc;

CoUninitialize();
return 1;
}
Pascale,

Is is easier to handle in Visual Basic?

Maurice
It's more easier in VB. here is a sample in VB. In your case you should detect the FetchProgress event to populate the grid before getting the FetchComplete event. Look also for the following property
'rst.Properties("Initial Fetch Size") = 1
I hope it will help you

Pascale

Private WithEvents rst As ADODB.Recordset

Private Sub Form_Load()

Dim strCnn As String

strCnn = "Provider=SQLOLEDB;Data Source=MyServer;Initial Catalog=Pubs;User ID=;Password="

Set rst = New ADODB.Recordset
rst.CursorLocation = adUseClient

'Uncomment the next line to workaround the problem
'rst.Properties("Initial Fetch Size") = 1
' For dual processor machines uncomment the next line also
' rst.properties("Background Fetch Size") = 1

rst.Open "select * from publishers", strCnn, adOpenKeyset, adLockOptimistic, adAsyncFetch
rst.MoveLast

End Sub

Private Sub rst_FetchComplete(ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)

MsgBox "FetchComplete executed"

End Sub
Pascale,

Thanks again for your help.

I still have two questions.

I did the following:

Private WithEvents mrstDummy As ADODB.Recordset

Private Sub Form_Load()

Set mrstDummy = New ADODB.Recordset

mrstDummy.Open "select * from
publishers",dnvJFM.ConJfm, adOpenStatic,
adLockReadOnly, adCmdText + adAsyncFetch

Set DataGrid.DataSource = mrstDummy

End sub

The events fetchprogress and fetchcomplete are not fired. Why not?

Why do I need these events to populate the datagrid?
I assign the datagrid.datasource to the recordset.

Maurice
Try this, it works on my computer (VB6)

Dim WithEvents rstPG As ADODB.Recordset

Dim cnn As New ADODB.Connection
Set cnn = New ADODB.Connection

With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Open "Q:\test.mdb"
End With
Set rstPG = New ADODB.Recordset
rstPG.CursorType = adOpenStatic
rstPG.CursorLocation = adUseClient
rstPG.LockType = adLockBatchOptimistic
rstPG.Properties("Initial Fetch Size") = 1

rstPG.Open "select * from tb3", cnn, adOpenStatic, adLockBatchOptimistic, adCmdText + adAsyncExecute + adAsyncFetch

Private Sub rstPG_FetchProgress(ByVal Progress As Long, ByVal MaxProgress As Long, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)

Debug.Print "fetch progress"
End Sub

Private Sub rstPG_FetchComplete(ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)

Debug.Print "fetch complete"

End Sub
Pascale,

Yes offcourse you are right.
I made a mistake.

But still, do I need to catch those events, because I assign my datagrid.datasource to the recordset? The grid will populate itself.

Maurice

How to handle insert apostrophe from textbox to database?

Hi experts,

Databases reserve the ' sign to start and end strings. So if user enters a ' (e.g., 'McDonald's') in the company field, the database will give me an error and the insert into is failed. I have lots of textboxes in my asp.net application and how can I handle this exception?

First of all, I retrieved the textbox value from a stored session value:
Dim AppCompany As String = CStr(Session("Company"))

Then I have a SQL commandtext for my Insert Into statement:

Dim dbComm As New SqlClient.SqlCommand
dbComm.Connection = conHRISDb
dbComm.CommandText = "Insert Into Crew_Applicant_Table (First_Name, Last_Name,Main_Phone,App_Street,App_City,App_State,App_Zip,App_Company) Values ( '" & FName & "','" & LName & "','" & AppPhone & "','" & AppStreet & "','" & AppCity & "','" & AppState & "','" & AppZip & "','" & AppCompany & "')
dbComm.CommandType = CommandType.Text

Try usingParametrized queries
Yes, use parameterized queries. But to answer your questionregarding apostrophe, simply append another apostrophe next to yourstring. For example MacDonald's = MacDonald''s.
Insert into company(companyName) value 'MacDonald''s'
will work.
Good Luck

I am trying to insert as a parameterize way, but I got another problem:

On the first page, I stored a textbox value into session value : Session("FromDate1") = txtFromDate1.Text

Then on this page, I retrieve the stored value and take care the null value :

Dim AppFromDate1 As Date
If Session("FromDate1") Is DBNull.Value Then
AppFromDate1 = Nothing
Else
AppFromDate1 = CDate(Session("FromDate1"))
End If

The inserting data part:
dbDetail.Parameters.Add(New SqlParameter("@.AppFromDate1", SqlDbType.SmallDateTime))
sqldatenull = SqlDateTime.Null
If AppFromDate1 = "" Then
dbDetail.Parameters("@.AppFromDate1").Value = sqldatenull
Else
dbDetail.Parameters("@.AppFromDate1").Value = DateTime.Parse(AppFromDate1)
End If

But it throws me an error when AppFromDate1 is null: Cast from string "" to type 'Date' is not valid.
(on Else
AppFromDate1 = CDate(Session("FromDate1"))
End If)

any suggestions?

How is AppFromDate1 declared in code?
It looks as though your Session variable isn't getting populated in thefirst form. For now I would just pass the FromDate value as a URLparameter and extract it via a Request.QueryString. If this worksyou will at least know that your SQL is valid.

I would try the following:
1) Load Session variables into String variables
dim fromDate1Str as String = Session("FromDate1")
2) Cast to Date only if String variable is not empty
if fromDate1Str <> String.Empty then
dim AppFromDate1 = CType(Session("FromDate1"),Date)
end if
Good Luck

I have took care of the seesion variable in the first form:

If txtFromDate1.Text.Trim().Length = 0Then

Session("FromDate1") = DBNull.Value

Else

Session("FromDate1") = txtFromDate1.Text

EndIf

In the 2nd form, I retrieve the value:

If Session("FromDate1")Is DBNull.ValueThen

AppFromDate1 =Nothing

Else

AppFromDate1 =CDate(Session("FromDate1"))

EndIf

HOWEVER, I found the null session date value becomes "#12:00:00AM#" even AppFromDate1=Nothing dosen't set the AppFromDate1 to null but to a string value.

Then I take care the parameter value by:

sqldatenull = SqlDateTime.Null

If AppFromDate1 =NothingThen

dbDetail.Parameters("@.AppFromDate1").Value = sqldatenull

Else

dbDetail.Parameters("@.AppFromDate1").Value = DateTime.Parse(AppFromDate1)

EndIf

But dbDetail.Parameters("@.AppFromDate1").Value = sqldatenull doesn't do the job either, it still try to insert "#12:00:00AM #"

Please someone help me!

If I try to load session variable into String variable: dim fromDate1Str as String = Session("FromDate1")
, I will get an error:

Cast from type 'DBNull' to type 'String' is not valid

you can also do an

If fromDate1Str.Trim.Length = 0 then

'pass the sqldatenull

else

'pass the actual value

end if

In the first form, I stored session variable to:
If txtFromDate1.Text.Trim().Length = 0 Then
Session("FromDate1") = DBNull.Value
Else
Session("FromDate1") = txtFromDate1.Text
End If

Then I retrieve the variable in the 2nd form:

Dim AppFromDate1 As Object
Dim sqldatenull As SqlDateTime
sqldatenull = SqlDateTime.Null
If AppFromDate1.Trim.Length = 0 Then
AppFromDate1 = sqldatenull
Else
AppFromDate1 = (Session("FromDate1"))
End If

I received the error:
Object variable or With block variable not set on

If AppFromDate1.Trim.Length=0

Any Suggestions?

>>Dim AppFromDate1 As Object
what kind of object is it ? a string, an int ?
you might want to checkthis article

Here is what I would do:
1) In form 1, initialize Session("FromDate1") to string.empty and setSession("FromDate1") to txtFromDate1.Text.ToString only iftxtFromDate1.text <> string.empty
2) In form 2 replace your parameterized Insert SQL statement witha parameterized Insert store procedure. This will providebenefits in the area of performance, security and flexibility as youwill see.
3) In your store procedure indicate which parameters are optional in TSQL by initializing them to null
4) In form 2 check whether Session("FromDate1") = string.empty. If it does, do not add the FromDate1 parameter to the parameterlist ofyour Command object. If it is not equal to string.empty then addthe FromDate1 parameter to the parameterlist.
I've done this before and it should work - provided you properlyidentified your optional paramaters in your TSQL store procedure. In a nutshell here's what should happen: If the FromDate1 parameter isnot added to the parameterlist, your store procedure will use thedefault value that you defined otherwise it should read the value fromyour Session variable and process normally.
Good Luck

How to handle insert and ListBox from a GRID??

Please help me, Is it possible to do this from a grid?

Functionallity:
------
The grid will be populated from the database table TIME_JOURNAL.

The last line should be an empty line for adding new rows. Should I place this in the grid footer?.

The listbox (dropdown-list) should be populated from the database table TRANSACTION_TYPES.
So the user can change the transaction type. How should I do to populate the listbox?

To edit a cell the user should just click on it with the mouse pointer. How should I solve this?
I dont want an EDIT button on each line. Just click the cell to edit.

Delete button: Will delete current row.

Finish button:
Will take TIME_JOURNAL id for current row and start a stored procedure.
How do I reference the id? After this button is clicked the row should be green.
Can I reference every cell in a grid??

Is there any good examples?

Database tables:
TIME_JOURNAL
# IDNUMBER
# TYPE_OF_TRANS NUMBER (fk to TRANSACTION_TYPES ID)
# AMOUNT NUMBER
# DESCRIPTIONCHAR

TRANSACTION_TYPES
# ID NUMBER
# DESCRIPTION CHAR

Design
---------------------
| Listbox | TextBox | TextBox | Delete button | Finish button |
---------------------
| Listbox | TextBox | TextBox | Delete button | Finish button |
---------------------
| Listbox | TextBox | TextBox | Delete button | Finish button |
---------------------
| Listbox | TextBox | TextBox | Delete button | Finish button |
---------------------
| Listbox | TextBox | TextBox | Add button | (empty line for adding rows)
----------------

Regards,

MartinHi,

DataGrid & DropDownList Live demo :
Specifying a DataBound DropDownList in the EditItemTemplate

Adding a New Record to the DataGrid

To make empty line for adding new rows in the footer :

<asp:TemplateColumn HeaderText="DESCRIPTION"
<ItemTemplate>
<%# DataBinder.Eval(Container.DataItem, "DESCRIPTION") %>
</ItemTemplate
<EditItemTemplate>
 <asp:TextBox ID="DESCRIPTION" Text='<%# Container.DataItem("DESCRIPTION") %>' Runat="server" />
</EditItemTemplate
<FooterTemplate>
 <asp:TextBox ID="add_DESCRIPTION" Runat="Server" />
</FooterTemplate
</asp:TemplateColumn>

HTH
Thank you I think, I get it to work.

/m

How to handle GridView Event Handler of Child GridView

Hi All,

I am developing a website which needs nested gridview control. All things are fine upto binding data to childgrid, but i am not able to handle Event Handler of Edit, Update, Delete, Cancel for Child Grid View.

I am binding the data to child gridview at RowDataBound Event of Parent GridView.

Here is the code for the same :

protected void gridID_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
childgrid = (System.Web.UI.WebControls.GridView)e.Row.Cells[2].FindControl("childgrid");

string comID = gridID.DataKeys[e.Row.DataItemIndex].Value.ToString();
con.Open();

cmd = new SqlCommand("SELECT * FROM Pipeline where CompName ='" + comID.ToString() + "'", con);

childgrid.DataSource = cmd.ExecuteReader();
childgrid.DataBind();
con.Close();
}

}

But when I click Edit Button of Child Grid View, it's not able to open selected row in edit mode. but when we hide child grid and open once again it is opening in edit mode.

Could you please help me with this?

Thanks,

Jay

Hi:

Could you post your aspx? Did you put it in <EditItemTemplate>?

Thanks

How to handle foreign keys when using DataObjectTypeName

If you have a table that has a foreign key, is it good practice to have a property in your class for that foreign key? I ask this because right now I haven't, and so I have declared a method to insert a record like this:

public static Insert(OrderItem orderItem,int orderId) { }

And I was thinking I could hook this up to an ObjectDataSource that looks like this:

<asp:ObjectDataSource ID="OrderitemDataSource" runat="server" TypeName="OrderItemManager" DataObjectTypeName="OrderItem"> <InsertParameters> <asp:QueryStringParameter name="orderId" QueryStringField="orderId" /> </InsertParameters></asp:ObjectDataSource>

But that will not work, because it seems that, as soon as I specify a DataObjectTypeName, the ObjectDataSource will ignore any extra parameters. It will look for:

public static void Insert(OrderItem orderItem) { }

That means that, in order to insert the foreign key value, it need to be a property of OrderItem. Is there another way around this, or is this the way you'd solve it too? The reason I am not sure, is because in OOP, it's more naturla to have a property of type List<OrderItem> on the Order class.

Thanks for your time!

Hi,

If you have a DataObjectTypeName set, the ObjectDataSource will create an object for this type and set property value for each of these parameters.

I think if you're having a reference to another object in this type, it is fine. It works like a foreign key.

Yes, I know. But how do you feel about this from an OOP perspective? Don't you usually have it the other way around (object Order has a List<OrderLine>, object OrderLine does not have an Order). Foreign key is a database term, OOP should not be coupled to the database.

How to handle exception?

Hi,

I have a asp.net 2.0 page which uses objdatasource of which the business object is a TableAdapter object. In the page, all database related operation is done by GridView. Issues come out that when user perform an illegal database insert or update (such as inserting a null value), the exception is thrown out to the page, which is unfriendly.

How can program to catch exception and show friendly in the page?

try

'your code

catch objExeption as Exception (or as )

'the code in case of an exception

'for example response.redirect("friendly page")

end try

hope it helps

How to handle events?

Sirs,

How can I handle events of controls created during runtime?
Let us say if I have nested repeaters how can I handle the events coming from the "child" repeaters? Or if I create a command button from the code how can I handle the events coming out of them? Example:

dim cmdButton as new button
cmdbutton.text="Hello"
page.controls.add(cmdbutton)

Now how to handle the click on the cmdbutton?

Regards,

TeddyCome on, Guys be more creative... I really need the answer...

Thx,
Teddy
I'm actually looking for the samething (in asp.net 2)

I've tried using:

.CommandName = "Method";
.Command += new CommandEventHandler(Method);

I don't get errors, just the event doesn't fire.

.OnCommand throws permisiion error

How to handle events for controls within templates?

I have a FormView control, containing an EditItemTemplate with a dropdown listbox. I want to handle the SelectedIndexChanged event so I can hide or show part of the form...

If I double-click the dropdown to create the event handler, I get the function dropdown1_SelectedIndexChanged()... but without " handles Dropdown1.SelectedIndexChanged"

Obviously the dropdown1 control is inside the EditItemTemplate - but it still generates events? how does one get these?

This is getting frustrating: every time I try to design stuff logically in a componentised way, I get kicked in the teeth with a "gotcha". It was easier using classic ASP and just throwing the html out using Response.Write!!

Hello.

I think that you're using vb.net, right? in c# I don't see any trouble with this. Doesn't something like this work in vb.net:

<asp:DropDownListrunat="server"id="T"OnSelectedIndexChanged="p"AutoPostBack="true">

<asp:ListItemText="1"/>

<asp:ListItemText="2"/>

</asp:DropDownList>

then just define the p method without using the handles (but with the normal parameter list).

Ah, many many hanks: I didn't realise thats how it worked (instead of event-based handling). It so frustrated me last night i turned off the PC and polished off a bottle of wine.

That part works at last!

Now all I need to do is figure out how to control my datasource and formview to tell them to open in edit or insert mode by default (and not select).

How to handle errors when using GridView and DetailsView without writing any code

It is great that no code is neccessory when using GridView and DetailsView controls together with a SqlDataSource control. Since there is no code, "try.. catch.. finally" can not be used (nowhere to place the code block). I am wondering how the errors can be trapped and displayed. We certainly do not want users to see a generic .net error page. Can anyone give me some information on this? thanks,

Tom

I guess no one has noticed this question. Let me rephrase it. I really need some help on this. How can you use "try...catch..finally" to handle errors when working with GridView, DetailsView and SqlDataSource controls without writing any code? Or how and where to add error handling code? Thanks,
This is somewhat of a general answer to your question and it may not be of any help. You could use try-catch blocks that will run when events fired by your aspx page like Page_Load, On_Selecting etc... That way these error catching mechanisms will be in place to handle any abnormal behavior your code might produce during runtime.

Thanks plazma, for the suggestion. In fact, I had thought about the way you suggested. It did not work out (or at least I could not figure out how it works). The problem is this. Noramally, the data retrieval and data binding happen inside try.. catch block.

try{
//contact database and bind data to data controls
}
catch{
//handle error
}

However, for the codeless programming, data retrieval and data binding are done automatically by the controls. If the try..catch block is placed in Page_Load or other locations, it could catch some errors but they would not be the data retrieval and databinding related. If let's say there is a permission issue in database, this try..catch block will not be able to catch it because the data retrieval and databinding do not happen inside the block.

I found out that error can be handled in the events (selected, inserted and updated) of the SqlDataSource control:

protected void MySqlDataSource_Selected(object sender, SqlDataSourceStatusEventArgs e){ if (e.Exception != null) { // Mask theerror with a generic message (for security purposes.) lblError.Text = "An exception occurred performing the query."; // Consider theerror handled. e.ExceptionHandled = true; }}

How to Handle Error Message: "Login failed for user (null). Reason: Not associated with a

I'm running WinXP and using "Visual Studio 2005". To make a long story short, my "Default.aspx" page is using a GridView to display data which is being read from a Windows 2000 DB server. When I run the app under the IDE all works fine.

I copied the 1 file and put it into my folder as, "C:\Inetpub\wwwroot\default.aspx". When it loads I receive the error message, "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection."

I'm frustrated with Studio Express 05 and will not bother with it again until Microsoft fixes this deployment issue. Can any one PLEASE help me???As you surely understand, this is a question of Access Premissions. When you write "Windows 2000 DB server", do you actually mean SQL 2000 Server? Assigning permissions correctly to the '<machinename>\ASPNET' account can be a bit difficult to grasp sometimes. It would help if you described your exact setup (what is installed on which machine). Example:
- Asp.Net 2.0 version 2.xxxxxxxxxx installed on machine YYYY running OS Windows XP Pro with 'IIS X.X'
- SQL Server version XXXX running on machine ZZZZ running OS QQQQ.

swep wrote:

I'm frustrated with Studio Express 05 and will not bother with it again until Microsoft fixes this deployment issue. Can any one PLEASE help me???

Your error SCREAMS "bad/improper connection string", so i wouldn't be so quick to blame a "deployment issue"
Application created on (my machine):
- Asp.Net 2.0 version 2.0.50215.44 running OS Windows XP Pro SP 2 with 'IIS 5.1'

Accessing database on:
- SQL Server 2000 Enterprise Edition running OS Windows Server 2000.

I'm really stuck...Any help you can offer would be greatly appreciated.
You must put in a valid connection string, because that is what the error is telling you
http://www.connectionstrings.com

I'm really lost right now and need some expect advice. You mentioned in the ASP.NET forum,

"You must put in a valid connection string, because that is what the error is telling you"

If my connection string is wrong then why can I connect to the db server within Visual Web 2005 just fine? My web.config file contains my connection as,

add name="WebConnectionString" connectionString="Data Source=NAME OF SQL SERVER;Initial Catalog=NAME OF TABLE;"

Could it be that I created this in .net framework 2.0 and it is querying a database in the .net framework 1.1?

hello.

maybe the problem is related with the fact that the asp.net user hasn't enough permissions to access the database (you might not have faced this problem because when you use the internal server it'll use the credentials of the active user to process the request - this does not happen when you use IIS: in this case, i't use a specific account for accessing the db).

How to handle dynamic control event in repeater?

Hello!

I've met with a problem. I'm building a list of products and use asp.net repeater to display it. each item has to have a LinkButton called "Remove" and I need to handle the click event somehow... :(

Any ideas how to insert a linkbutton into repeater item in a way so I can handle the click event it in the code for all of them?

Put the link button in the repeater

<asp:LinkButton ValidationGroup="Delete" ID="LinkButton1" CommandArgument='<%# Eval("myID") %>">' runat="server" CausesValidation="True"
CommandName="Delete" Text="Delete" OnClick="LinkButton1_Click"></asp:LinkButton>

protected void LinkButton1_Click(object sender, EventArgs e)
{
LinkButton btn = sender as LinkButton;
String idtoDelete = btn.CommandArgument;
// Run Delete
}

How to Handle dynamic Button Click event

Dear sir,

In the above code, I am binding the data such as description of the product, and i have added one button. When I click on the button for the particular row, that row's details has to be added in a separate datatable.

<asp:DataGrid ID="dgGrid" runat="server" AutoGenerateColumns="false">
<Columns>
<asp:TemplateColumn HeaderText="Name">
<ItemTemplate>
<asp:Label ID="Label1" Runat="server" Text='<%# DataBinder.Eval(Container.DataItem, "Description") %>' />
</ItemTemplate>
</asp:TemplateColumn>

<asp:TemplateColumn >
<ItemTemplate>
<asp:Button ID="Button1" runat="server" CommandName="Add To Cart" Text="Add To Cart" />
</ItemTemplate>
</asp:TemplateColumn>
</Columns>
</asp:DataGrid>

Please let me know how to proceed.

Thanks in Advance,

Regards,

Arun.

How about something like this:

protected void dgGrid_ItemCommand(object source, DataGridCommandEventArgs e){Button Button1 = e.CommandSourceas Button;if (Button1 ==null) {return; }DataGridItem dgi = Button1.NamingContaineras DataGridItem;if (dgi ==null) {return; }Label Label1 = dgi.FindControl("Label1")as Label;if (Label1 ==null) {return; }string description = Label1.Text;// Do whatever you want here}

How to handle DBNull DataType for Ms access ??

Hi,

I have problem in using this function to check if the field is empty or null :

Let say , I have connect to a Database and set up Dataset :

Dim Ds as dataset

Dim StrAddr as string

If IsDBNull( DS.tables("tblName").rows(0).item("Address")) then

LbMsg.text =" No address entered."

else

StrAddr = DS.tables("tblName").rows(0).item("Address"))

end if

I have error msg : Can not cast DBNull to string.

All I wanted is to check the field address to see if it is empty or no data entered.

Please help.

How to check ? How to handle if the field has nothing in it at all. How to use IsDBNull() ?

Thanks in advance.

Try this:

StrAddr = DS.tables("tblName").rows(0).item("Address").ToString

If StrAddr ="" then StrAddr = "No Address Entered"

Tim

How to handle DBNull

As i write some functions to return string or dataset from database, there is a chance of return DBNULL.

Even i checked the dr(0) with Nothing, It still get Error
dim dr as sqldatareader = xxx.executeQuery
if dr.Read() then
if dr(0) <> Nothing Then
dim abc as string = dr(0)
end if
end if

How can i solve this?
Thx a lotsThere is ISDBNull function in VB and also isDBNull method in SqldataReader, which you can use to check if field is null. If field is null (DBNull), you should set the member/variable you set based on this field, so that its value clearly indicates it has not been set, for example with string it could be String.Empty.
thx for your quick respone

how to handle datagrid item command in C#?

Can anyone share a code snippet to handle the link button with Update command in C#? The link button is a template column inside a datagrid.

ASPX code:
<asp:TemplateColumn HeaderText="Update">
<ItemTemplate>
<asp:LinkButton Runat="server" Text="Update" CommandName="Update" ID="Linkbutton1" NAME="Linkbutton1"></asp:LinkButton>
</ItemTemplate>
</asp:TemplateColumn
I tried the following C# code:

private void grdOrderItems_ItemCommand(Object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)
{
if (e.CommandName == "Update")
{
...
}
}

But the handler did not get called when the link button was clicked.

Thanks.Hello, i recommend you check this pageAdding Button Columns to a DataGrid Control.

Good Luck.

How to handle data in different tables?

I was wondering if someone could point me to a tutorial that might help me?

I have an application that I'm developing that will display a tabbed form which needs to contain data from different tables. For example, my database has a contact table with typicall information and then it also has a lead table which contains information if the contact is a sales lead. They are linked together by a common contactID column. There are a few other normalized tables also like an AlternateAddress table.

My first problem was that I could only get the binding source to connect to a particular table. This was a problem cause the data that I need to modify is normalized across a few different tables. My solution to that problem was to create a view and join all of my data from the different tables into a 'virtual table'.

My new problem now is I can't generate an update command for a view.

How do I get a binding navigator to use a binding source to navigate through my data for each contact and also be able to update changes to the datasource?

I've read some other posts about this (there's not many) and here's what I think might work. Can someone tell me if there is an easier way?

I would have my dataset with a table for each table in the database (ContactInfo, Lead, and Alternate Address) Each would have a table adapter, but the Lead and Alternate Address tables would have an additional table adapter to fillByContactID. I would have my binding source tied to my contactInfo table, and I would have to catch the position changed event and then re-fill the Lead and Alternate Address tables by the current contactID.

Would it be easier to write my own update function for using a VIEW? Someone please help. Thank you.

create a view in sql server

allow the application to update insert delete from the view

in sql server configure an instead of update,insert and delete trigger

that updates individual table behind the scene

From what I understand, I can't update to a view without writing my own update SQL command correct?

yes you can write your update statement for a view

but inside sql server it can be the instead of triggers

that update the individual base tables behind the scene

most views are not updateable. VS perceive the view as updatable

Adam,

ADO.NET 2.0 is not very apt at dealing with scenarios involving multiple tables. I know it sounds silly, but this is unfortunately the truth. LINQ to SQL and ADO.NET vNext deal with this problem out of the box.

So obviously the question is, "What can you do to deal with this problem today".

The best answer I can give you is to do some further reading into ADO.NET and try and wean yourself away from drag-drop solutions for this problem. A handwritten solution, which is definitely possible in ADO.NET 2.0 (my book covers it well in Chap 10), is your best bet in this scenario.

The "virtual table" scenario you mention - may just be a good shortcut, but you will probably have to hand-specify your own commands.

- Sahil Malik
http://blah.winsmarts.com

I found the answer I was looking for. Basically I followed this example which showed me that the way to have multiple tables on the same form is to have multiple binding sources and then hooking the Data Source of the child tables up the Parent table binding source. Also, hook the Data Member of the child tables up to the Foriegn Key in the parent table's binding source. Anyone else looking for help with this, follow this example and then look at the binding sources.

http://msdn2.microsoft.com/en-us/library/4esb49b4.aspx

how to handle connections on heavely used app

I have a web app which could get 10,000 hits per minute, and relies heavely
on database interaction... we've always been told open and close connections
only when needed... but what happens if two people hit a page at the same
time and one has an open connection and the other is trying to open one,
it'd fail saying the connection is already open... how do you guys handle
connections in a heavy use application?No it would not fail. You can have more then one open connection from a
machine to a database server. In fact, you are only really limited by how
many your database server can handle.
So yes, just create connections as you need them. There is no rule that says
that your database server is only allowed one database connection at a time.
"Smokey Grindel" <nospam@.nospam.com> wrote in message
news:OXVS9Ih8GHA.3960@.TK2MSFTNGP05.phx.gbl...
>I have a web app which could get 10,000 hits per minute, and relies heavely
>on database interaction... we've always been told open and close
>connections only when needed... but what happens if two people hit a page
>at the same time and one has an open connection and the other is trying to
>open one, it'd fail saying the connection is already open... how do you
>guys handle connections in a heavy use application?
>
They why does it fail for us all the time when two people try to access the
database through the same website application? The application itself has
the connection open already, when someone else accesses the same page at the
same time it throws an exception "The database connection is already open
(State = Open)" when the Open method is called... and this is on an SQL 2005
server with a lot of CAL's on it...
"Marina Levit [MVP]" <someone@.nospam.com> wrote in message
news:%23K1ByWh8GHA.4996@.TK2MSFTNGP03.phx.gbl...
> No it would not fail. You can have more then one open connection from a
> machine to a database server. In fact, you are only really limited by how
> many your database server can handle.
> So yes, just create connections as you need them. There is no rule that
> says that your database server is only allowed one database connection at
> a time.
> "Smokey Grindel" <nospam@.nospam.com> wrote in message
> news:OXVS9Ih8GHA.3960@.TK2MSFTNGP05.phx.gbl...
>>I have a web app which could get 10,000 hits per minute, and relies
>>heavely on database interaction... we've always been told open and close
>>connections only when needed... but what happens if two people hit a page
>>at the same time and one has an open connection and the other is trying to
>>open one, it'd fail saying the connection is already open... how do you
>>guys handle connections in a heavy use application?
>
It sounds like you have a globally defined connection object?
You never ever ever ever ever, under any circumstances, have a global
connection object to be shared by all users in a web application. Now, you
have to synchronize access to it, which is going to kill performance and
scalability.
You are supposed to *create* the connection when you need it, open it, do
your work, then close it. By "create" I mean actually make a new connection
object - not use one already hanging out there in shared space.
Whoever was the one that always told you to open and close connections only
when needed, missed the critical detail of not sharing one connection object
for an entire web application.
"Smokey Grindel" <nospam@.nospam.com> wrote in message
news:uQotcah8GHA.1560@.TK2MSFTNGP04.phx.gbl...
> They why does it fail for us all the time when two people try to access
> the database through the same website application? The application itself
> has the connection open already, when someone else accesses the same page
> at the same time it throws an exception "The database connection is
> already open (State = Open)" when the Open method is called... and this is
> on an SQL 2005 server with a lot of CAL's on it...
>
> "Marina Levit [MVP]" <someone@.nospam.com> wrote in message
> news:%23K1ByWh8GHA.4996@.TK2MSFTNGP03.phx.gbl...
>> No it would not fail. You can have more then one open connection from a
>> machine to a database server. In fact, you are only really limited by how
>> many your database server can handle.
>> So yes, just create connections as you need them. There is no rule that
>> says that your database server is only allowed one database connection at
>> a time.
>> "Smokey Grindel" <nospam@.nospam.com> wrote in message
>> news:OXVS9Ih8GHA.3960@.TK2MSFTNGP05.phx.gbl...
>>I have a web app which could get 10,000 hits per minute, and relies
>>heavely on database interaction... we've always been told open and close
>>connections only when needed... but what happens if two people hit a page
>>at the same time and one has an open connection and the other is trying
>>to open one, it'd fail saying the connection is already open... how do
>>you guys handle connections in a heavy use application?
>>
>
the connection object isnt shared globally... this is why I am confused
about this... the object is made locally on each page at page load... which
I thought would create a unique object for each instance of a page... but
I'm still running into problems for some reason... wish I could figure this
one out...
"Marina Levit [MVP]" <someone@.nospam.com> wrote in message
news:exWs6eh8GHA.4116@.TK2MSFTNGP03.phx.gbl...
> It sounds like you have a globally defined connection object?
> You never ever ever ever ever, under any circumstances, have a global
> connection object to be shared by all users in a web application. Now,
> you have to synchronize access to it, which is going to kill performance
> and scalability.
> You are supposed to *create* the connection when you need it, open it, do
> your work, then close it. By "create" I mean actually make a new
> connection object - not use one already hanging out there in shared space.
> Whoever was the one that always told you to open and close connections
> only when needed, missed the critical detail of not sharing one connection
> object for an entire web application.
> "Smokey Grindel" <nospam@.nospam.com> wrote in message
> news:uQotcah8GHA.1560@.TK2MSFTNGP04.phx.gbl...
>> They why does it fail for us all the time when two people try to access
>> the database through the same website application? The application itself
>> has the connection open already, when someone else accesses the same page
>> at the same time it throws an exception "The database connection is
>> already open (State = Open)" when the Open method is called... and this
>> is on an SQL 2005 server with a lot of CAL's on it...
>>
>> "Marina Levit [MVP]" <someone@.nospam.com> wrote in message
>> news:%23K1ByWh8GHA.4996@.TK2MSFTNGP03.phx.gbl...
>> No it would not fail. You can have more then one open connection from a
>> machine to a database server. In fact, you are only really limited by
>> how many your database server can handle.
>> So yes, just create connections as you need them. There is no rule that
>> says that your database server is only allowed one database connection
>> at a time.
>> "Smokey Grindel" <nospam@.nospam.com> wrote in message
>> news:OXVS9Ih8GHA.3960@.TK2MSFTNGP05.phx.gbl...
>>I have a web app which could get 10,000 hits per minute, and relies
>>heavely on database interaction... we've always been told open and close
>>connections only when needed... but what happens if two people hit a
>>page at the same time and one has an open connection and the other is
>>trying to open one, it'd fail saying the connection is already open...
>>how do you guys handle connections in a heavy use application?
>>
>>
>
wierd the server admin made some changes on how IIS is setup and load
balanced and now its not doing that anymore... now I'm really confused
"Smokey Grindel" <nospam@.nospam.com> wrote in message
news:e2XG3Ul8GHA.3916@.TK2MSFTNGP04.phx.gbl...
> the connection object isnt shared globally... this is why I am confused
> about this... the object is made locally on each page at page load...
> which I thought would create a unique object for each instance of a
> page... but I'm still running into problems for some reason... wish I
> could figure this one out...
> "Marina Levit [MVP]" <someone@.nospam.com> wrote in message
> news:exWs6eh8GHA.4116@.TK2MSFTNGP03.phx.gbl...
>> It sounds like you have a globally defined connection object?
>> You never ever ever ever ever, under any circumstances, have a global
>> connection object to be shared by all users in a web application. Now,
>> you have to synchronize access to it, which is going to kill performance
>> and scalability.
>> You are supposed to *create* the connection when you need it, open it, do
>> your work, then close it. By "create" I mean actually make a new
>> connection object - not use one already hanging out there in shared
>> space.
>> Whoever was the one that always told you to open and close connections
>> only when needed, missed the critical detail of not sharing one
>> connection object for an entire web application.
>> "Smokey Grindel" <nospam@.nospam.com> wrote in message
>> news:uQotcah8GHA.1560@.TK2MSFTNGP04.phx.gbl...
>> They why does it fail for us all the time when two people try to access
>> the database through the same website application? The application
>> itself has the connection open already, when someone else accesses the
>> same page at the same time it throws an exception "The database
>> connection is already open (State = Open)" when the Open method is
>> called... and this is on an SQL 2005 server with a lot of CAL's on it...
>>
>> "Marina Levit [MVP]" <someone@.nospam.com> wrote in message
>> news:%23K1ByWh8GHA.4996@.TK2MSFTNGP03.phx.gbl...
>> No it would not fail. You can have more then one open connection from
>> a machine to a database server. In fact, you are only really limited by
>> how many your database server can handle.
>> So yes, just create connections as you need them. There is no rule that
>> says that your database server is only allowed one database connection
>> at a time.
>> "Smokey Grindel" <nospam@.nospam.com> wrote in message
>> news:OXVS9Ih8GHA.3960@.TK2MSFTNGP05.phx.gbl...
>>I have a web app which could get 10,000 hits per minute, and relies
>>heavely on database interaction... we've always been told open and
>>close connections only when needed... but what happens if two people
>>hit a page at the same time and one has an open connection and the
>>other is trying to open one, it'd fail saying the connection is already
>>open... how do you guys handle connections in a heavy use application?
>>
>>
>>
>