using a dataReader with StoredProc

TheMSsForum.com: The Microsoft Software Forum

The MSS Forum ‹ Ado
- Archive
  - Biz
  - MCSE
  - CRM
  - Drivers
  - Framework
  - ADO
  - ASP
  - Compact
  - Forms
  - Dotnet
  - C#
  - VB
  - FontpageGen
  - Excel
  - WorkSheet
  - Exchange
  - Setup
  - Fox
  - Fontpage
  - ASP
  - IIS
  - Entourage
  - Money
  - Messanger
  - PocketPC
  - Powerpoint
  - Project
  - Publisher
  - Excel
  - VB
  - Security
  - Portal
  - Services
  - SQLServerDev
  - SVCS
  - SQLServer
  - VB
  - VC
  - MFC
  - ExcelGen

- Previous
  - 1
    - How to provide a feedback in accessing the BLOB type data? How to let the user know the progress while accessing BLOB type data? The feedback such as the number of bytes readed/written to database. Regards, ZhangZQ Tag: using a dataReader with StoredProc Tag: 109679
  - 2
    - Comparing 2 datasets (or datatables) I want to compare two datasets (of 2 datatables). I just need to know whether the 2 datasets are same or different. I do not want to know how it differs. (I would be nice to have though).. Is there any easy way to accompilsh this. Thanks Tag: using a dataReader with StoredProc Tag: 109673
  - 3
    - stored proc /w Oracle and Ado.Net Can someone help me with a very simple task I am trying to execute a stored procedure in an Oracle database using Ado.Net. The following is a code snippet conn = New OracleConnection(mOracleConnectionString) cmd = New OracleCommand cmd.Connection = conn conn.Open() cmd.CommandType = CommandType.StoredProcedure cmd.CommandText = "TW_USER_TEST3" cmd.ExecuteReader(CommandBehavior.Default) The stored proc does not have any input or output parameters. I created it for testing purposes. I had a stored proc that did have input and output but I was getting errors so I thought to create the simplest store proc. The proc doesn't even do anything. I am getting the following error: >> An unhandled exception of type 'System.Data.OracleClient.OracleException' occurred in system.data.oracleclient.dll Additional information: ORA-06550: line 1, column 7: PLS-00201: identifier 'BRAVO_CL21.TW_USER_TEST3' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored >> The following is the stored proc: >> CREATE OR REPLACE procedure tw_users_test3 as begin null; end; / >> I would appreciate any help thank you John Tag: using a dataReader with StoredProc Tag: 109669
  - 4
    - ODBCConnection and ASP.NET I have an ODBC connection (to Perforce) using a System DSN. In a console app I can open the connection, in an ASP.Net webform I can't, and get the error... [OdbcException: ERROR [HY000] [ ERROR [HY000] [] I assume it must be permissions? I have tried a few things, firstly making ASPNET (I'm running IIS5) andAdmin user, then giving it permissions to the System DSN registry key, but to no avail. I'm using the 1.1 Framework and the System.Data.Odbc.OdbcConnection object. Anbody got any ideas? Tag: using a dataReader with StoredProc Tag: 109668
  - 5
    - Continious DataTable.Select without AcceptChanges on every table leaks memory Hi everybody, we had very strange memory usage in our application when continiously updating a dataset. We were able to exactly localize the problem in the datatable.Select method. Each time we check the datatables for changes with Select, there is an internal index build. These indexes are later deleted when you pass the datatable to the dataadapter. But in our code we have a lot of tables in the dataset where we check all for changes and only update the ones with changes. When the changes only occur in the same tables for a long time, the other tables get more and more indexes consuming a lot of memory. The following piece of code demonstrate this behaviour. It generates more than 400Meg on my machine, and it's the Select method doing this! Only a call of the AcceptChanges method clears the memory. If your machine doesn't have that much memory, be careful! We have not examined the problem on .NET SP1, perhaps it is fixed. For me, creating a static index without reusing it anytime looks like a bug. Are there any designguides saying that this is not the right way to use/update datasets? Doesn't anybody work with datasets like that? Any comments on that? Helge ---------------------------------------------------- class Class1 { [STAThread] static void Main(string[] args) { // build a very simple DataSet System.Data.DataSet ds = new DataSet(); System.Data.DataTable table = ds.Tables.Add("Table"); table.Columns.Add(new System.Data.DataColumn("ID", typeof(Guid))); // add some Data to your one table to make the effect more dramatic ;-) for (int i = 0; i< 10000; ++i) { System.Data.DataRow row = table.NewRow(); row["ID"] = Guid.NewGuid(); table.Rows.Add(row); } table.AcceptChanges(); // dump memory before Class1.DumpMemory(); for(int i = 0; i <10000; ++i) { // this simple select (doing nothing) executed 10000 times generates over 400Meg of memory! // And you don't get rid of it with GC.Collect()! table.Select(null,null,DataViewRowState.ModifiedCurrent); } // dump memory after! Suprise! Class1.DumpMemory(); // only if you call AcceptChanges the memory is cleared! ds.AcceptChanges(); Class1.DumpMemory(); } static void DumpMemory() { System.GC.Collect(); System.Console.WriteLine("TotalMemory: " + GC.GetTotalMemory(true).ToString()); } } Tag: using a dataReader with StoredProc Tag: 109667
  - 6
    - OnRowUpdated, Rollback in DB, ConcurrencyException and RowState question. Please give your opinions. Hello. If you think this post is too long to read please scroll to the bottom and read the last two sentences. Thank you :) I have a problem which I cannot find a good solution to and hopefully I can get some ideas from the readers of this group. I will first describe my environment for you; I have a database without constraints. It has a lot of tables ofcourse and among all of them are two specific tables. Lets call them Customer and CustomerDetails. In the Customer table there are some columns ofcourse and the last column is DetailsId that works as reference to the CustomerDetails table. The column datatype is an int and points out the Id column in the CustomerDetails table. The Id column in CustomerDetails has autoincrement turned on and is the primary key column. In the dataset in the C# application there is a relation between Customer and CustomerDetails (DetailsId in Customer refers to Id in CustomerDetails). Before a Customer has been "opened" for the first time in the Application the CustomerDetails table does not have any entries which is refered to from the Customer row. The DetailsId value of the specific customer is DBNULL. Now, imagine the following scenario: Person A opens up a customer in the application. Automatically a row in CustomerDetails is added and it's Id value is automatically assigned. This value is then, due to the relation between the tables also inserted into the customer row's DetailsId column. Lets say the value is 20 (because there are 19 other entries in CustomerDetails already). Then Person B opens up the same customer in the application. Same thing happens with the CustomerDetails table in the dataset. It gets a new row with Id 20. Person A changes something in the customer row and then saves all changes back to the DB. Everything works fine so far. Person B changes the same value in the customer row and then also tries to save. What happens now is that first the CustomerDetails table in the dataset is saved to the db. When this happens the RowUpdated event triggers and we get a hint that the actual value of Id has changed in the DB so it is now 21 (since 20 was already taken). With the help of the relation between the tables also the DetailsId value in the Customer table is changed to 21. Then some other updates follow, but finally there is a concurrency in the Customer table since both person A and B have tried to changed the very same rowvalues. A rollback is performed in the database. Person B is informed that a concurrenyexception has been thrown and is now given two options. First option is to loose all of his changes due to the concurrency. Second option is to override the data in the database with his specific changes. The person B chooses the second option since he thinks his changes are more important. What happens now is that data is collected from the database into a new dataset. Then this dataset is merged into the first dataset (in which all of person B's changes are). It is merged with the "preserve changes" flag so that all of the changes that person B has made are still there. Since the DetailsId value has been changed in the RowUpdated event to 21 this value is still 21 in both Customer and CustomerDetails. Though, when the new dataset was filled from the database there was already a row in CustomerDetails (with Id value 20). So after the merge the CustomerDetails table now holds two rows. One with Id 20 and one new with Id 21. In the Customer table the row's value is 21 (which is correct). Now, person B saves for the second time to the DB. Now there is no concurrency and everything seemd to work fine. But everything is certainly not fine. The first problem is that no row in the Customer table refers to the row in CustomerDetails which has Id 20. The second problem is that the row in CustomerDetails with Id value 21 is never saved to the DB so the row in the Customer table now refers to a row that does not exist in the DB. The first problem you probably understand why it occured. The second problem is a little more tricky to understand. Atleast it was for me. The thing is that when person B saved to the DB the very first time all of the rows that were saved before the concurrency occured had it's rowstate property changed from added/modified to unchanged. Then the rollback was done in the DB, but the dataset were still "changed". That is why the row in CustomerDetails was not saved when person B saved to the DB the second time. To fix the second problem I have this solution in mind; If I first make a copy of the changes (before person B is saving for the first time) in the dataset with dataset.GetChanges() and use this "copy" when I update the DB. If everything goes fine with the DB update I can merge any changes (that could have been done due to RowUpdated event activity) from the "copy" dataset back to the original dataset. If something goes wrong with the DB update (i.e. if a concurrency exception is being thrown), I will still have the original dataset unchanged with all rowstates. I can then still give person B the option to "override" the concurrency, which means I refresh a new dataset from the DB and merge the changes into it. Then person B tries to save once again. Question now is, what will happen if I implement my solution? What if the DetailsId in person B's dataset is something different from what's already in the DB (say, 22). Will the existing row (21) that person A once saved have it's Id value changed to 22 du to the merge between the newly refreshed dataset and person B's dataset? Or will there just be a new row added (22) to CustomerDetails so that problem 1 occurs (row 21 will be left on it's own with reference from the Customer table) ? I know that this was a quite big post but I felt I needed to explain everything good enough to get good help. I am VERY thankful if you find a working solution for me. Regards, David. Tag: using a dataReader with StoredProc Tag: 109666
  - 7
    - Require Help Regarding Deployment...... This is a multi-part message in MIME format. ------=_NextPart_000_000A_01C54CB2.5B42EED0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi Every Body, I build an application using vb.net. Now i m deploying on the client system. I know that the deployment automatically check the .net framework on the = client system at startup. I include the .net framework in the deployment setup. I want that if the framework is not installed on the client system then = my deployment wizared automatically install the framework which i = include in the deployment package. How can i do this. What are the setttings are needed regarding this. Regards, Muhammad Jamil Nawaz ------=_NextPart_000_000A_01C54CB2.5B42EED0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META http-equiv=3DContent-Type content=3D"text/html; = charset=3Diso-8859-1"> <META content=3D"MSHTML 6.00.2900.2604" name=3DGENERATOR> <STYLE></STYLE> </HEAD><FONT face=3DArial><FONT size=3D2> <BODY> <DIV>Hi Every Body,</DIV> <DIV> </DIV> <DIV>I build an application using vb.net.</DIV> <DIV>Now i m deploying on the client system.</DIV> <DIV>I know that the deployment automatically check the .net framework = on the=20 client system at startup.</DIV> <DIV>I include the .net framework in the deployment setup.</DIV> <DIV>I want that if the framework is not installed on the client system = then my=20 deployment wizared automatically install the framework which i include = in the=20 deployment package.</DIV> <DIV> </DIV> <DIV>How can i do this. What are the setttings are needed regarding = this.</DIV> <DIV> </DIV> <DIV>Regards,</DIV> <DIV> </DIV> <DIV>Muhammad Jamil Nawaz</DIV></BODY></HTML></FONT></FONT> ------=_NextPart_000_000A_01C54CB2.5B42EED0-- Tag: using a dataReader with StoredProc Tag: 109657
  - 8
    - Large jumps in number connections to SQL DB and Connection Pool Not really sure if I have a problem or not...but, I want to try and understand what is going on. With that said, I haven't ruled out that it's a code problem, but I want to post to see if anyone has seen similar behavior. Basically, throughout the day, we may see "large" jump in the number of connections to our SQL DB and a corresponding jump in the connection pool within a short period of time, i.e., 100+ connection increase in 2 to 3 seconds. The number of users during these jumps are steady...that is, we don't have a large increase of end users (it's an internal call center app). Like I said, I haven't ruled out there being some kind of problem in the application that generates the connections, but I just want to try and persue all avenues. Thanks in advance. Tag: using a dataReader with StoredProc Tag: 109653
  - 9
    - Table Mapping with Typed Dataset I've created a strongly typed DataSet, with a 2 related tables. a couple of the columns in the parent are computed with expressions. One of which is a Sum of a Child.column (Sum(Child.UnitCost). Eitherway, since adding these expressions, I'm getting a InvalidOperationException when I execute the Update method of corresponding DataAdapter on the parent. The column names haven't changed from the underlying data source, a table on sql server. Admittedly, at this point in the execution, the child table has not been filled yet, but that's fine, the expression columns can just stay null at this point. What am I doing wrong? Is it case that all expression columns are not meant to be mapped back, but instead are just for use within the dataset? :O( Tag: using a dataReader with StoredProc Tag: 109652
  - 10
    - Using gcroot template class and understanding memory utilization. Hi Guys, Iam using a managed dll in my unmanaged MFC application to use ADO.NET classes. so i have a class in the manged dll declared as follows : class __declspec ( dllexport ) CADOInterface { public: //////////// //---- ////////////// int GetCacheRecordIndex(int recordId); public: ///--- /// other declarations /// gcroot<DataTable*> m_pDataTable; }; In one of the method implementation, i have used a local DataRowCollection class. Since the CADOInterface is created in the unmanaged appllication, do i need to use the gcroot template class with the local DataRowCollection class??? if don't use with gcroot class, is there any memory leak??? int CADOInterface ::GetCacheRecordIndex(int recordId) { /// assume m_pDataTable table has some rows gcroot<DataRowCollection*> rows = m_pDataTable->Rows; if(rows) { gcroot<DataRow*> row; int recId; for(int i=0; i < rows->Count; i++) { row = rows->Item[i]; recId = *dynamic_cast<__box Int32*>(row->get_Item(S"RecordId")); if(recId == recordId) return i; } } return 0; } Also do i need to always use gcroot template class with managed classes, when iam using in a unmanaged class?? Hari Tag: using a dataReader with StoredProc Tag: 109650
  - 11
    - Using ms access how do I set column that can be of zero length TIA Tag: using a dataReader with StoredProc Tag: 109644
  - 12
    - Finding out ADO.NET version How do i find out what version of ADO.NET is being used in my .NET application. Iam building vc++ application in visual studio.net 2003 (7.1 version) and .NET framework version 1.1. I would like use the new feature of ADO.NET 2.0 and need to know i have the correct ado dll's on my system.? The dlls i have on C:\Program Files\Common Files\System\ado folder is msado15.dll like that, is it 1.x or 2.0 version? Hari Tag: using a dataReader with StoredProc Tag: 109643
  - 13
    - Repost:Adding rows to DataTable and DataView issue Hi, I thought I had my problem with the dataview and InsertAt fixed, but nope. It looks like it works at first, but then the dataview starts going nuts. And I experience the issue from my other recent post about the dataview containing datarowviews that don't point to an actual row. In fact, if I check the source table, my table can have say 4 rows. But the DataView will have 5, one of which is this phantom one that doesn't point to a real DataRow. I am experiencing something described here: http://groups-beta.google.com/group/microsoft.public.dotnet.framework.windowsforms/browse_thread/thread/5b274676ce51dc99/dfb4d5d987d4b371?q=DataViewRowState.CurrentRows+insertat&rnum=1&hl=en#dfb4d5d987d4b371 Seems there is no real solution other then to make a fake column that keeps track of row order. Except then, every time you insert one not at the end, you have to renumber all the rows after it. Help, someone, anyone? Tag: using a dataReader with StoredProc Tag: 109642
  - 14
    - Filtering on a Hierarchical recordset Hello, I have a dataset with multiple tables linked through parent child relationships. I want the user to be able to filter on a child table and I'm using the DataViewManager to set the rowfilter for all the tables in the dataset. This does not seem to work. Only the filter on the parent table works. This is an examle of what I'm currently doing: //Create DataView settings for each Table using the DataViewManager m_oppDVM = new DataViewManager(myDataset); // Loop through the DataViewSettings and set Rowfilter for each Table individually String oppTable; foreach (DataViewSetting DVS in m_oppDVM.DataViewSettings) { // Set Default Sort Order = Primary Key for all Tables DVS.ApplyDefaultSort = true; // Set individual Sort and Rowfilter ... oppTable = DVS.Table.ToString(); switch (oppTable) { case "Table1": { DVS.RowFilter = "filter"; } case "Table2": { DVS.RowFilter = "filter"; } case "Table2": { DVS.RowFilter = "filter"; } } } To give you an example, if the user sets a filter on table2, I want to be able to show the user all rows (both parent and child) which match the filter. I am using the wrong approach?? Thanks Tag: using a dataReader with StoredProc Tag: 109639
  - 15
    - DataView mystery Hi, For some reasons, these lines of code sometimes differ in results: myDataView.Item(1).row("MyColumn") myDataView.Item(1)("MyColumn") Sometimes the second version throws a NullReferenceException in the System.Data.DataView.IsOriginalVersion(Int32 index) method. So the DataRowView object being indexed to exists. And it definitely exists, because the first version of the code works fine. Can anyone tell me why going through the underlying row is OK, but not through the indexer in datarowview directly? Tag: using a dataReader with StoredProc Tag: 109638
  - 16
    - N-tier applications with VB.NET: Where to put everything practically? Hi, I'm developping VB.NET-applications for 2 years now, and I'm now moving on to bigger porjects, so I'm examining the use of Visio for enterprise architects, and 'the design' we should use. I'm my whole carreer already using the concept of the 3 tiers (presentation-, business- and datalayer), but I don't now 'the exact way' to do it (shame on me, hehe). Does anybody know some good articles about this subject? So not the theoretical way, but really more practical. What do I have to put actually in which layer? Les't take for exemple a simple application that let the user administer the Northwind-database. - Do I create one big Datalayer-class with different SqlDataAdapters and 1 DataSet? Or one class with one SqlDataAdapter and its own DataSet that I inherit for every object/table? - The logic behind for exemple a "Save"-button on a Form with a DataGrid: Do I put this in the Business Layer, or in the Presentation Layer? - The same with for exemple a button that will open a Form that shows all the Orders of a Customer? Where do I search for its Orders etc? - Do I create for each Form its own Business-class? I know I'm asking some really dumb questions, but I'm getting confused, and I really want to know the 'right' way :-) Thanks a lot for any hints, documents, example Tag: using a dataReader with StoredProc Tag: 109636
  - 17
    - DataView.RowFilter and DateTime column I'm trying to set the RowFilter = "MyDateTimeColumn in ('2005-02-21')" but it returns 0 rows. I know the row is there. If change the RowFilter to RowFilter = "MyDateTimeColumn not in (all the other dates but 2005-02-21) it returns the rows. Any suggestions? Tag: using a dataReader with StoredProc Tag: 109630
  - 18
    - Adding rows to DataTable and DataView issue Hi, Let's say I have an existing view on a table which has 4 rows. If I add a row to the table at position 2, the view treats it as position 5, instead of it's physical location. Note: there is no sort on the view. So, the order in the datatable, should be exactly the same as the order in the view. Am I missing something obvious? How to make the view look at the row order correctly? Thanks Tag: using a dataReader with StoredProc Tag: 109628
  - 19
    - retrieve default values Hi, I am trying to export info about a SQL server database in vb.net. I need to retrieve the following info about the fields in the tables: names, data type, length and default values. I have used the following code to retrieve the first three properies: Dim strSQL As String = "SELECT * FROM " + CStr(DBDS.Tables(0).Rows(count)("TABLE_NAME")) Dim aReader As OleDbDataReader Dim oledbCmd As OleDbCommand = New OleDbCommand(strSQL, oleDBConn) aReader = oledbCmd.ExecuteReader() Dim schemaTable As DataTable = aReader.GetSchemaTable() Dim ColumnName As String = schemaTable.Rows(0)("ColumnName").ToString() Dim Length As String = schemaTable.Rows(0)("ColumnSize").ToString() Dim DataType As String = schemaTable.Rows(0)("DataType").ToString() aReader.Close() However, I cannot find a way to find the default value for each field. Does anyone know how to do this? I have searched the newsgroups and can't find an answer - some people talk about using ADOX, but apparently this doesn't work with .NET? Any help, even just to be told it's not possible, would be appreciated!! Thanks, Kath Tag: using a dataReader with StoredProc Tag: 109627
  - 20
    - connection object and GC Hi, I would like to concrete my understanding about my custom object using an ADO.NET connection object and the behavior of garbage collector. I have a simple .NET object public class DBACCESS { public void OpenConnection() { SqlConnection cn = new SqlConnection(connection_string); // some code here } } Here is my client code; DBACCESS db1 = new DBACCESS(); db1.OpenConnection(); db1 = null; Now, I would like to confirm that when the GC collected my db1 object, will it also close/release the connection object used by the object instantiated by my client code (which is the db1)? Thanks! Tag: using a dataReader with StoredProc Tag: 109624
  - 21
    - Prevent row access until transaction committed We have a database that is accessed by the web to display the data. We also have programs that insert or update to that database. What I want to be able to do is insert data using a transaction but I don't want the web page to display any new rows being inserted until the transaction is committed. So, for example, the program begins a transaction to insert an order header and a number of order details In the meantime, someone requests data using the web page. I don't want my new rows being returned to the web page until the transaction that is inserting (or updating) them is committed. I tried playing around with the isolation levels but that did not seem to have any effect. Thanks for the help. Tag: using a dataReader with StoredProc Tag: 109623
  - 22
    - data access layer In VS 2003 we used Data access layers intensively. Is the same pattern valid for VS 2005? Or new data source features ceases its existence... Tag: using a dataReader with StoredProc Tag: 109617
  - 23
    - Two nested relations, but one is to itself Hi. I have an XSD which describes a set of objects which one is both contained in a parent, as well as referencing itself again (recursively). XSD.exe doesn't like it very much and complains about: "The same table cannot be the child table in two nested relations." I have read all over the web about this, which seems to be a limitation in .Net, but everywhere I read it is regarding an item which is referenced by two different parents. My problem is a little different and I was wondering if anyone has a solution. I have full control over the XSD, but it must be standard (not MS specific) and compliant. My XML looks something like this: <Root> <Main> <Name>Service</Name> <pg> <pg_name>One</pg_name> <pg> <pg_name>One_1</pg_name> </pg> </pg> <pg> <pg_name>Two</pg_name> <pg> <pg_name>Two_1</pg_name> </pg> </pg> </Main> </Root> Both Main and pg are the parents of pg. What XSD can describe this and be .Net friendly? Thanks Tag: using a dataReader with StoredProc Tag: 109615
  - 24
    - Connecting to the IdX I need information on how to program to retreive information in the indexing service. Can anybody show me when there examples. Thanks in advance Tag: using a dataReader with StoredProc Tag: 109613
  - 25
    - problem wuth oracle 9i unable to load oramts.dll when i am trying to open the connection it is show the above problem. get me the solution. From http://www.developmentnow.com/g/7_2004_6_0_11_0/dotnet-framework-adonet.htm Posted via DevelopmentNow.com Groups http://www.developmentnow.com Tag: using a dataReader with StoredProc Tag: 109609
- Next
  - 1
    - Could not determine which columns uniquely identify the rows for ..... Ive read about similar problems here. Im using a SQL 2000 db. Im trying to use the VS .NET designer to drag 'n' drop tables from Server explorer to my component and receive this error message. My table is uniquely identified by a primary key, so I dont understand the problem. Do you have any ideas? *** Sent via Developersdex http://www.developersdex.com *** Tag: using a dataReader with StoredProc Tag: 109608
  - 2
    - oracleparameter returns empty resultset these two parameter field are varchar type in DB. when I pass an empty string in one of the paramters the result set is empty. but if I one parameter is something other than empty string, and the other is existing value in the db, the result is correct. Any idea ? string SQL = "Select * from GAESTEST where MEMBERNR = :MemberNr or NAME = :Name"; OracleCommand Cmd = new OracleCommand(SQL, OraConn); Cmd.CommandType = CommandType.Text; Cmd.Parameters.Add("MemberNr", txtMemberId.Text); Cmd.Parameters.Add("Name", txtName.Text); OraConn.Open(); Tag: using a dataReader with StoredProc Tag: 109607
  - 3
    - Unable to display child rows I created a dropdown list and a datagrid in a webform. When a customer's name is selected in the dropdown list, a "SelectedIndexChanged" event is raised and the orders for that customer would be displayed in the datagrid. The following code was written but the orders were not displayed after a customer is selected from the dropdown list. Anyone knows what's wrong ? ************************************************ Private Sub lstCustomer_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles lstCustomer.SelectedIndexChanged Dim drNew As New DataRelation("CustOrders", ds.Tables("Customers").Columns("CustomerID"), ds.Tables("Orders").Columns("CustomerID")) Dim strCurrent As String = lstCustomer.SelectedItem.Value Dim dvCustOrders As New DataView(ds.Tables("Customers")) Dim drView As DataRowView dvCustOrders = drView.CreateChildView(drNew) dvCustOrders.RowFilter = "CustomerID='" & strCurrent & "'" dgCustOrders.DataSource = dvCustOrders dgCustOrders.DataBind() End Sub Tag: using a dataReader with StoredProc Tag: 109602
  - 4
    - time keeps converting itself to date! I am accessing an Access db. I generated the adapter and ds thru the wizard. I have a datetime field in the database (simply called Time) that I am posting to from a textbox. I had to convert it to dattime format, which seems to go fine, but when I execute the update, it strips the time portion and just leaves the date. Here is my conversion code: dr("Time") = DateTime.Parse("9:32 PM") Thanks in advance! Tag: using a dataReader with StoredProc Tag: 109593
  - 5
    - BCP Invalid collation name I'm trying to load data to an sqlserver table from a comma delimeter file. I'm trying to use the FORMATFILE property. I've bcp out the format of the table so it can be used as the load format. However, when I used the file to import I get error "invalid collation name for source column 3. Here's what the data file looks like. Intelis,LOCK.E23100,3.00000,3.00000,3.00000,3.00000,3.00000,3.00000,3.00000 And here's the bcp out formatfile. I have tried various names for the collation column, but nothing seems to work. 8.0 14 1 SQLCHAR 0 20 "" 1 Entity SQL_Latin1_General_CP1_CI_AS 2 SQLCHAR 0 15 "" 2 Account SQL_Latin1_General_CP1_CI_AS 3 SQLDECIMAL 0 9 "" 3 Period 1 "" 4 SQLNUMERIC 0 9 "" 4 Period 2 "" 5 SQLDECIMAL 0 9 "" 5 Period 3 "" 6 SQLDECIMAL 0 9 "" 6 Period 4 "" 7 SQLDECIMAL 0 9 "" 7 Period 5 "" 8 SQLDECIMAL 0 9 "" 8 Period 6 "" 9 SQLNUMERIC 0 9 "" 9 Period 7 "" 10 SQLDECIMAL 0 9 "" 10 Period 8 "" 11 SQLDECIMAL 0 9 "" 11 Period 9 "" 12 SQLDECIMAL 0 9 "" 12 Period 10 "" 13 SQLDECIMAL 0 9 "" 13 Period 11 "" 14 SQLDECIMAL 0 9 "" 14 Period 12 "" "" -- Hutty Tag: using a dataReader with StoredProc Tag: 109589
  - 6
    - Filter on child row column Hi I know that to perform a DataView.RowFilter based on a child relation, an aggregation must be performed. But I have a datagrid bound to a DataView (filtered by various columns) of my parent table on which Insert/Update/Delete operations are performed. The parent table has a relationship with it's child on a one to many basis. One of my required filter columns is however in the child table. My tables are in a strongly typed dataset and trips back to the sql server are not permitted. What would be the recommended approach to solving this problem? Any help would be most appreciated. -- Many Thanks Terry Tag: using a dataReader with StoredProc Tag: 109584
  - 7
    - trouble reading excel with ado.net Whenever I try to read an excel sheet using ado.net, I keep getting this error "Could not find installable ISAM. " I tried what microsoft had to say on this link http://support.microsoft.com/?scid=kb;EN-US;209805 but the problem still persists. Does anyone have any idea why? Tag: using a dataReader with StoredProc Tag: 109583
  - 8
    - Explicit locking Does SqlDataAdaptor do an explicit table lock when calling Update? We have an importer that connect to SQL Server 2000 and anytime imports are being written to a table and a query is ran against that table at the same time we get a timeout exception. Not locking the table is fine for us because the imports don't effect any user and is only done by one person. Tag: using a dataReader with StoredProc Tag: 109581
  - 9
    - Error - Deleted row information cannot be accessed through row I am using a dataset containing Parent/Child tables. When I attempt to delete more than a single row, I get the DeletedRowInaccessibleException error on the second row to be deleted. I cannot figure out why. I have looked at the data just before the failed call (identified by ==>). I have verified that the tblDetail.Select method creates a datarow array with the correct number of rows deleted and the data in those rows represent the rows which I deleted (in a grid). The Insert and Update sql commands work as expected. The snippet follows. I left in the code which helped me verify that the deleted data was correct. The method creating the DataAdapter's DeleteCommand is at the bottom. Thanks for any help, Lars ==================================================== Dim cn As New SqlConnection(ConnectionSettings.cnString) Dim daMaster As New SqlDataAdapter("usp_Master_Sel_All", cn) Dim daDetail As New SqlDataAdapter("usp_MasterDetail_Sel_All", cn) Dim tblMaster As DataTable = ds.Tables(0) Dim tblDetail As DataTable = ds.Tables(1) daMaster.InsertCommand = Me.CreateInsertMaster(cn) daMaster.UpdateCommand = Me.CreateUpdateMaster(cn) daMaster.DeleteCommand = Me.CreateDeleteMaster(cn) daDetail.InsertCommand = Me.CreateInsertDetail(cn) daDetail.UpdateCommand = Me.CreateUpdateDetail(cn) daDetail.DeleteCommand = Me.CreateDeleteDetail(cn) cn.Open() Try ' Submit the only new Master/Detail rows daMaster.Update(tblMaster.Select("", "", DataViewRowState.Added)) daDetail.Update(tblDetail.Select("", "", DataViewRowState.Added)) ' Submit the only modified Master/Detail rows daMaster.Update(tblMaster.Select("", "", DataViewRowState.ModifiedCurrent)) daDetail.Update(tblDetail.Select("", "", DataViewRowState.ModifiedCurrent)) ' Submit only deleted Detail then Master rows Dim strx As String Dim dr() As DataRow = tblDetail.Select("", "", DataViewRowState.Deleted) Dim num As Integer = dr.Length() For Each rw As DataRow In dr For Each col As DataColumn In tblDetail.Columns If Not rw.IsNull(col, DataRowVersion.Original) Then strx = rw.Item(col, DataRowVersion.Original) End If Next Next ==>daDetail.Update(tblDetail.Select("", "", DataViewRowState.Deleted)) daMaster.Update(tblMaster.Select("", "", DataViewRowState.Deleted)) Catch ex As SqlException MessageBox.Show(Exceptions.HandleError(ex)) End Try cn.Close() Private Function CreateDeleteDetail(ByVal cn As SqlConnection) As SqlCommand Dim cmd As New SqlCommand("usp_MC_Del", cn) cmd.CommandType = CommandType.StoredProcedure Dim parm As SqlParameter Dim pc As SqlParameterCollection = cmd.Parameters parm = pc.Add("@MCID", SqlDbType.Int, 0, "MCID") parm.SourceVersion = DataRowVersion.Original Return cmd End Function Tag: using a dataReader with StoredProc Tag: 109573
  - 10
    - Catching Errors with try catch Hi all, I'm trying to catch errors and when running in VS.net 2003 my try catch doesn't seam to catch the error? this error comes up: why does this not catch it? Catch ex As System.Data.SqlClient.SqlException "A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in system.data.dll Additional information: System error." my code is this: Public Function RunSQLcmd(ByVal SQLquery As String, ByVal Msgtxt As String) Dim result As String = String.Empty Dim MessageFrm As StatMess = New StatMess MessageFrm.StatusMessage(Msgtxt) Try ' Reset the cursor to the hourglass postion. Cursor.Current = System.Windows.Forms.Cursors.WaitCursor SQLConnection(loginDatabase, loginIPaddress, "Standard", loginuser, loginpass) Dim MyCmd2 As New SqlCommand(SQLquery) MyCmd2.CommandTimeout = 600 MyCmd2.Connection = SQLConn SQLConn.Open() MyCmd2.ExecuteNonQuery() SQLConn.Close() Application.DoEvents() Catch ex As System.Data.SqlClient.SqlException result = "There was an SqlException.." & vbCrLf & "Details..." & vbCrLf & ex.ToString() Catch ex As InvalidCastException result = "There was an InvalidCastException..." & vbCrLf & "Details..." & vbCrLf & ex.ToString() Catch ex As Exception result = "There was an Unhandled Exception..." & vbCrLf & "Details..." & vbCrLf & ex.ToString() Finally If IsNothing(MessageFrm) = False Then MessageFrm.Dispose() If IsNothing(SQLConn) = False Then If SQLConn.State <> Data.ConnectionState.Closed Then SQLConn.Close() : SQLConn.Dispose() End If MsgBox(result) Cursor.Current = System.Windows.Forms.Cursors.Default MessageFrm.close() End Try ' Reset the cursor to the default for all controls. Cursor.Current = System.Windows.Forms.Cursors.Default SQLConn.Close() MessageFrm.close() End Function thanks Gerry Tag: using a dataReader with StoredProc Tag: 109572
  - 11
    - Refresh labels in program Hello: I have a Progress Bar in my program and I also wrote some code that will show the Percent completed. I know that the percent complete is actually being calculated but when i run the program it does not show up. Instead when the process is complete it just shows the 100%. Is there a way that the label can get refreshed so it actually shows the percentage change. Should i be using a text box instead? Thank You Prasun Tag: using a dataReader with StoredProc Tag: 109570
  - 12
    - OracleDataAdapter returns gibberish I'm using Microsoft OracleDataAdapter 1.0.5 with Oracle client 9.2 and Oracle DB 8.1.7 with NLS_LANGUAGE=AMERICAN and NLS_CHARACTERSET, NLS_NCHAR_CHARACTERSET=IW8ISO8859P8 Sometimes, when the result exceeds several hundred rows, I'm receiving gibberish in the lower part of the result. Any suggestions? Thanks in advance, Eli Tag: using a dataReader with StoredProc Tag: 109563
  - 13
    - SQL Access and COM+ I need a component to reside in COM+ (as it'll be component load balanced). My code is written in VB.Net and the class inherits ServicedComponent. To avoid multiple calls, I have a simple procedure that does all I need in 1 go, ie 1) Gets the connection string from the registry 2) Uses a SQLdataAdapter to populate a dataset. 3) Disposes the command & connection I then use a simple web service to test the procedure, and automate it using Application Test Centre. The throughput doesn;t seem to get above 3 requests per second. This seems pretty poor considering the query runs incredibly quick, and all components (IIS / SQL etc are on my local pc). Any idea how I could identify where the bottleneck is, or perhaps someone could confirm my structure? Tag: using a dataReader with StoredProc Tag: 109562
  - 14
    - Important -> MDAC and XmlDataDocument Hi! I have a windows forms application and , what I basically do is: 1.- Instanciate a DataSet. 2.- Instanciate a XMlDataDocument attaching the DataSet to it. 3.- Load XmlDataDocument with a local XML document (C:\mydoc.xml) 2.- Bind it to a Datagrid. That is all! My app doesn't need to access SQL Server, or any other ODBC, not even to MS Access.... Is MDAC still required on the target machine in order for this to work? I have not tried it on a machine that does not have MDAC installed so I don't know if it works.... Thanks in advance. -- I love .NET! Tag: using a dataReader with StoredProc Tag: 109560
  - 15
    - kind of memory leak in dataset/dataadapter update? Hi, we had strange memory usage / memory leak of our web application which we could strip down to a (at least for us) unexpected behaviour of a dataset. Scenario: With application start we open a dataset keeping a reference of some datatables as long as the app runs. During this time we do regular updates of these tables by changing the dataset and then passing it to the dataadapter for update. During update the dataadapter seems to build some help tables and/or structures which are held as long as the dataset exists. Because this is done for every update again and again, the allocated memory sums up to an unacceptable amount. Our first workaround is to regularly copy the dataset to free the old dataset with all related memory. Is this a known behaviour/bug? We use framework 1.1. Is this maybe fixed with SP1? Are there other workarounds for this? best regards Helge Tag: using a dataReader with StoredProc Tag: 109552
  - 16
    - typed datasets and maintaining object relations Hi, We are implementing Data Transfer Objects (DTO) as Typed DataSets. This means that we would have multiple typed DataSet classes that overlap slightly, sharing tables between them. The issue with typed datasets is that it inherits from the DataSet class and hence a typed dataset class cannot inherit from any other class. For example consider two datasets MemberDS and AgentDS both sharing a Person table between them. By default each generated dataset would contain a schema defining the Person table, meaning that if the table changes, you'll have to change the schema in two different places. Ideally, you need a way to define each type once; no matter how many times your application uses or extends that type. That way, when the shared Person table changes, you'll only have to modify your code in one place. We are planning to write a tool that will take of the issues mentioned above. Your help is welcome. Thanks. Tag: using a dataReader with StoredProc Tag: 109551
  - 17
    - Help in getting the output parameter from sp This is a multi-part message in MIME format. ------=_NextPart_000_0010_01C54B4F.FDAE8620 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable hi all, i have storedprocedure who takes one input/output parameter the sp also return 2 tables. i m using sqldataadapter. and read the contents of sp in dataset in dataset 2 tables are automatically added by the sp which it returns but how can i get the value of output parameter. please help me thanks Jamil Nawaz ------=_NextPart_000_0010_01C54B4F.FDAE8620 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META http-equiv=3DContent-Type content=3D"text/html; = charset=3Diso-8859-1"> <META content=3D"MSHTML 6.00.2900.2604" name=3DGENERATOR> <STYLE></STYLE> </HEAD><FONT face=3DArial><FONT size=3D2> <BODY> <DIV>hi all,</DIV> <DIV>i have storedprocedure who takes one input/output parameter</DIV> <DIV>the sp also return 2 tables.</DIV> <DIV>i m using sqldataadapter.</DIV> <DIV>and read the contents of sp in dataset</DIV> <DIV>in dataset 2 tables are automatically added by the sp which it=20 returns</DIV> <DIV>but how can i get the value of output parameter.</DIV> <DIV> </DIV> <DIV>please help me</DIV> <DIV> </DIV> <DIV>thanks</DIV> <DIV>Jamil Nawaz</DIV></BODY></HTML></FONT></FONT> ------=_NextPart_000_0010_01C54B4F.FDAE8620-- Tag: using a dataReader with StoredProc Tag: 109549
  - 18
    - Is it possible to access an ACCESS DB with password protected? Is it possible to access an ACCESS DB with password protected? I have the user/password information of an access db, how can I access it by System.Data.OleDb ? Another question is, is it possible to compact a access db by a System.Data.OleDb ? Thank you very much! ZhangZQ Tag: using a dataReader with StoredProc Tag: 109541
  - 19
    - ADO.Net 1.1 I had large database about 2GB size and consist of 1 million+ records. I use ADO.Net 1.1 in ASP.Net 1.1 for connection pooling this database and it always prompt "Stack Memory Error". The information request is about 2MB if I running at SQL Query Analyzer. Please advise. Many thanks. Coding ------- Dim okcon As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString")) Dim okcmd As New SqlCommand() okcmd.CommandText = "Select gbaid,account_num,acc_desc,amount from f0902 where gbmcu = " & _ TextBox_Account.Text & " and gblt = '" & DropDownlist_LedgerType.SelectedItem.Value & _ "' and amount <> 0 and dateyear like '%" & DateYear & "%' order by account_num" okcmd.Connection = okcon Try okcon.Open() Dim okrd As SqlDataReader = okcmd.ExecuteReader() myDataGrid.DataSource = okrd myDataGrid.DataBind() okrd.Close() Finally okcon.Close() okcon.Dispose() myDataGrid.DataSource = Nothing myDataGrid.Dispose() End Try Tag: using a dataReader with StoredProc Tag: 109533
  - 20
    - Parsing DataTable Columns Hello All In my DataTable the following data is returned Object_IDX Object_FullName 48984232 |MS_Net|EUR|Server02 48986409 |MS_Net|EUR|Server21 48990195 |MS_Net|EUR|WKS23 48995964 |MS_Net|EUR|WKS2 48996647 |MS_Net|EUR|Test01 I want to parse out the Object_FullName and create 3 Columns in the DataTable Object_IDX Object_FullName Network Domain Host oDataTable = oDataSet.Tables[0]; oDataTable.Columns.Add("Network",typeof(string)); oDataTable.Columns.Add("Domain",typeof(string)); oDataTable.Columns.Add("Host",typeof(string)); How do populate these Cols? Results Object_IDX Object_FullName Network Domain Host 48984232 |MS_Net|EUR|Server02 MS_Net EUR Server02 Thanks Stuart Tag: using a dataReader with StoredProc Tag: 109529
  - 21
    - Best Practice for Uploading to SQL Server? Hi there, I have an ArrayList containing approximately 20,000 database INSERT command objects. Each of these commands needs to be executed. The commands do not need to be executed in any particular order, nor do they need to be part of a transaction. I'm currently accomplishing this task by iterating through the ArrayList via the foreach construct and Executing each command. This works fine but I'm wondering if there's a way to get the upload to execute faster. Here's why I ask: During the upload: - The CPU useage on the uploader machine is very low. - The CPU useage on the database machine is very low. - The network bandwidth between machines isn't saturated. Since neither computer is even breathing hard during this operation, it leads me to believe that there is room for optimization. Here's one approach that I'm considering: Store the commands in a Queue instead of an ArrayList. Spawn multiple threads. Each thread attempts to pop a command off the Queue and execute it using its own database connection. The threads will keep working until the Queue is empty. Am I on the right track? If so, how many threads should I spawn? I feel like there should be a way to speed up my upload but I'm not sure how to proceed. I know that there is a program called BCC designed for this sort of thing, but in my case, I want everything to be done in my C# app instead of shelling out to another app. Thanks, The Oracle Tag: using a dataReader with StoredProc Tag: 109521
  - 22
    - Trying to re-sort a datatable My program grabs a single datatable (dt) from an Excel file, loads it into a dataset (ds) and displays it in a datagrid (dg). The datatable has a couple of "calculated" columns that start out blank. After loading the datattable, the program fills in these blank columns using the values in other columns. Next, I want to re-sort the datatable using the "calculated" columns. I use the following code: Ds.dt.DefaultView.Sort = "calculatedcol1, calculatedcol2" Dg.DataSource() = ds.dt This causes the datagrid to sort the datarows correctly. HOWEVER, I also want to declare a loop that goes through each datarow in the datatable and does something with that datarow. It's important that the datarows are sorted as shown in the code. However, when I do the loop, it's clear that the underlying datatable has not actually been re-sorted. My question is this: Is it possible either to re-sort the actual underlying datatable, OR is it possible to somehow loop through each value of the DefaultView instead of the table itself? Thanks!!!! Tag: using a dataReader with StoredProc Tag: 109520
  - 23
    - General Network error I am currently getting a General Network Error were I was not getting one before. The batty thing is the error happens about every other connection to the database. I have looked at the SQLException that gets generated and the error number is 20. Tag: using a dataReader with StoredProc Tag: 109514
  - 24
    - Inserting data from multiple related tables - need help I'm new to .NET.. I have multiple related tables that I need to insert data into. Lets say for example: Customers and Orders. Orders has a foreign key Customer_Number column in it. I want to use the dataadapter update method to do the inserts for me. Ideally I'd use a stored procedure that will generate a unique id for the Customer Number column on insert. But I can't figure out how will I fill the Customer_Number in the orders table? I really have no clue here, can someone link me to an example of code that does something like this? Thanks! Tom Tag: using a dataReader with StoredProc Tag: 109513
  - 25
    - single to double quotes to input parameter of a stored procedure...does not work Hi all, I'm using VB.NET for this one, and it is straight forward. I have a sp that takes an input parameter (a string) for querying the db. Here is the sp: CREATE PROCEDURE [dbo].[web_SearchByName] @LastName varchar(80) AS SELECT Parcel.[Parcel ID],[Parcel Number], RTRIM([address Number]) + ' ' + RTRIM([Address Street Name]) As [Address], [Last Name] FROM Parcel INNER JOIN [Ownership] on [Ownership].[Parcel Id]= [Parcel].[Parcel Id] INNER JOIN [Entity] ON [Entity].[Entity ID]=[Ownership].[Entity ID] WHERE [TO DATE] IS NULL and [Primary owner]=1 and [Last Name] LIKE @LastName ORDER BY [Last Name] --REPLACE(@LastName, CHAR(39), CHAR(39) + CHAR(39)) GO Note, I even used the REPLACE statement after LIKE instead of the plain @LastName, but still does not work. In my code, I format the input string to have doubel quotes if there is a single quote: myString = Replace(mysring, "'", "''") and I do verify that the input parameter contains double quotes when running from source code ......but I still do not get results. Any ideas why? I also tried replacing each sinque quote by 4 consecutive single quotes, and nothing. Please your quick will be appreciated. Tag: using a dataReader with StoredProc Tag: 109511

I have an oracle storedProc that takes one input parameter and 15
output parameters. Can I get the results of the output parameters in a
datareader or must I add 15 output parameters?

thanks

john

Top

Re: using a dataReader with StoredProc by Sahil

Sahil
Fri Apr 29 11:30:50 CDT 2005

You can get a datareader. Just use a Ref Cursor output parameter.

- Sahil Malik [MVP]
http://codebetter.com/blogs/sahil.malik/

"coltrane" <tendengarci@yahoo.com> wrote in message
news:1114791224.426306.95160@g14g2000cwa.googlegroups.com...
> I have an oracle storedProc that takes one input parameter and 15
> output parameters. Can I get the results of the output parameters in a
> datareader or must I add 15 output parameters?
>
> thanks
>
>
> john
>

Top

Re: using a dataReader with StoredProc by coltrane

coltrane
Tue May 03 09:15:16 CDT 2005

thanks for the reference
john

Top