Importing existing data

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
    - IDataReader Read() method question Hello All! I am going to crazy and feeling myself so stupid but I don't understand such behavior. I have code: public int getNextAgentId() { Int32 agent_id = 0; IDataReader dr = dbap.DBDataReader("SELECT MAX(agent_id) FROM Agents"); if(dr.Read()) { agent_id = dr.GetInt32(0); } dr.Close(); return agent_id + 1; } where dbap is an instance of DB abstraction class, hence using IDataReader interface instead of concrete DB DataReader such as SqlDataReader. SELECT returns null due to database table is empty and I have error message "Data is Null. This method or property cannot be called on Null values." on line agent_id = dr.GetInt32(0); As I understand in the beginning datareader positioned before first record and when I call Read method it should point to first record but why it returns true in conditions when now rows returned from DB? Any ideas? Thanks in advance to all. Below my code of abstraction class (dbap is instance of DBAbstractionProcessor class): public class DBAbstractionProcessor { // implemented DB provider types public enum DbType { SQLServer, OLEDb, ODBC }; private DbType dbtype; private AbstractDBFactory dbf; public DBAbstractionProcessor(DbType db_type) { this.dbtype = db_type; } private void selectDbType() { switch(this.dbtype) { case DbType.SQLServer: this.dbf = new SQLServerDb(); break; case DbType.ODBC: this.dbf = new OdbcDb(); break; case DbType.OLEDb: this.dbf = new OleDb(); break; } } public void init(string dburl) { this.selectDbType(); this.dbf.init(dburl); } public void init(string dburl, string user, string passwd) { this.selectDbType(); this.dbf.init(dburl,user, passwd); } public void connect() { dbf.connect(); } public void disconnect() { dbf.disconnect(); } public DataSet DBAPSelectQuery(string cmd) { return this.dbf.DBSelectQuery(cmd); } public void DBAPInsertQuery(string cmd) { this.dbf.DBInsertQuery(cmd); } public IDataReader DBDataReader(string query) { return this.dbf.DBDataReader(query); } } Factory class which implements factory design pattern: /// <summary> /// Factory Design Patterns implementation. /// At the present time abstraction if MS SQL Server, ODBC and OLEDB /// interfaces implemented. In case of neccessety, any other interfaces /// can be easily implemented. /// </summary> public abstract class AbstractDBFactory { // database url string protected string url = null; // database username protected string username = null; // database password protected string password = null; // simple initialize by url public void init(string dburl) { if(dburl == null) { throw(new ArgumentException("Illigal agruments")); } this.url = dburl; } // full initialize public void init(string dburl, string user, string pass) { this.init(dburl); this.username = user; this.password = pass; } // clear all settings public void destroy() { this.url = null; this.username = null; this.password = null; } // abstract methods which implemented by SQLServerDb, OdbcDb and OleDb classes abstract public void connect(); abstract public void disconnect(); abstract public DataSet DBSelectQuery(string cmd); abstract public void DBInsertQuery(string cmd); abstract public IDataReader DBDataReader(string cmd); } And SQL Server implementation: /// <summary> /// MS SQL Server interface implementation. /// </summary> public class SQLServerDb : AbstractDBFactory { // connector private SqlConnection sqlCon; public override void connect() { // if base class which stores url, username and passwords not initilized // throw exception if(base.url == null) { throw(new ApplicationException("Class is not initialized. Please run init() first.")); } else { //Disable pooling for sql server if(base.url.IndexOf("Pooling") == -1) { base.url += ";Pooling=false"; } // if username and password properties exists initilize by them otherwise by url if(base.username != null && base.password != null) { this.sqlCon = new SqlConnection(base.url + ";uid=" + base.username + ";pwd=" + base.password); LoggingProcessor.Info("url uid pwd init"); } else { this.sqlCon = new SqlConnection(base.url); LoggingProcessor.Info("url - " + base.url); } try { sqlCon.Open(); } catch (Exception e) { throw new Exception("SQL Server error: " + e.ToString()); } LoggingProcessor.Info("sql server connection established"); } } // simple select query // returns DataSet public override DataSet DBSelectQuery(string cmd) { DataSet ds = new DataSet(); try { if(this.sqlCon.State == ConnectionState.Closed) { this.connect(); } SqlDataAdapter sqlDA = new SqlDataAdapter(); sqlDA.SelectCommand = new SqlCommand(cmd, sqlCon); sqlDA.Fill(ds); } catch (Exception e) { throw new Exception("SQL Server error: " + e.ToString()); } finally { sqlCon.Close(); } return ds; } // simple insert query public override void DBInsertQuery(string cmd) { try { if(this.sqlCon.State == ConnectionState.Closed) { this.connect(); } SqlCommand sqlCmd = new SqlCommand(cmd, this.sqlCon); sqlCmd.Prepare(); sqlCmd.ExecuteNonQuery(); } catch (Exception e) { throw new Exception("SQL Server error: " + e.ToString()); } } // DBDataReader - provides selection and returns SqlDataReader public override IDataReader DBDataReader(string cmd) { try { if(this.sqlCon.State == ConnectionState.Closed) { this.connect(); LoggingProcessor.Info("connection is not initialized. initializing..."); } SqlCommand sqlCmd = new SqlCommand(cmd, this.sqlCon); LoggingProcessor.Info(cmd); //sqlCmd.Prepare(); return sqlCmd.ExecuteReader(); } catch (Exception e) { throw new Exception("SQL Server error: " + e.ToString()); } } // disconnect public override void disconnect() { if(sqlCon.State != ConnectionState.Closed) { try { this.sqlCon.Close(); } catch (Exception e) { throw new Exception("SQL Server error: " + e.ToString()); } } } } Best regards, /Gelios Tag: Importing existing data Tag: 109719
  - 2
    - Opening from Excel into a dataset, issue with mixed datatypes I have an excel sheet with a column of mixed data types, i.e., numeric & text. ADO.NET is scanning the first few rows and deciding on numeric so when we come across text, the data becomes blank. That being the case I set the extended properties setting to include IMEX=1 but this seems to be of no help. Below is the stripped out code of what I am attempting to do. Thank you for your help. string sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Server.MapPath("~/Output/myData.xls") + ";" + "Extended Properties=\"Excel 8.0;IMEX=1;HDR=NO;\""; OleDbConnection oleCon = new OleDbConnection(); oleCon.ConnectionString = sConnectionString; oleCon.Open(); OleDbCommand objCmdSelect =new OleDbCommand("SELECT * FROM [2005$A5:A133]", oleCon); OleDbDataAdapter objAdapter1 = new OleDbDataAdapter(); objAdapter1.SelectCommand = objCmdSelect; DataSet objDataset1 = new DataSet(); objAdapter1.Fill(objDataset1); DataGrid1.DataSource = objDataset1.Tables[0]; DataGrid1.DataBind(); oleCon.Close(); oleCon.Dispose(); Tag: Importing existing data Tag: 109710
  - 3
    - How the frick do you call a stored procedure when using OdbcDataAdapter?!? And why the frick is it sooo damn hard to find information on this crap, anyway?!? Help! -Skipward Tag: Importing existing data Tag: 109699
  - 4
    - Scrolling and retrieving rows similar like ado recordset Hi, I working on implementing a scrolling and record navigation with ADO.NET. In ado recordset having builtin caching/paging and record navigation methods. but i could find a similar conecpts in ADO.NET classes.. Am i missing something?? In dataset class i cannot set pagesize/cachesize, so if i execute a query with a large resultsets with dataset all the rows loaded in the memory. so iam not getting any cursor navigation conecpt benefits in ado.net classes??? Hari Tag: Importing existing data Tag: 109693
  - 5
    - Needing Help Hi, i am trying to make a window application that create graphs (plot data as in excel) for a given set of points, how can i do this? thanx -- Message posted via http://www.dotnetmonster.com Tag: Importing existing data Tag: 109691
  - 6
    - Bidirectional cursor navigation Hi, Is there any class supports bidirectional cursor in the ADO.NEt framework? I was looking some of the samples and only talks about SqlDataReader (forward only cursor). Or is there any way i can make use of database cursors in my dotnet application to navigate the records from a table.? Hari Tag: Importing existing data Tag: 109687
  - 7
    - using a dataReader with StoredProc 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 Tag: Importing existing data Tag: 109682
  - 8
    - 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: Importing existing data Tag: 109679
  - 9
    - 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: Importing existing data Tag: 109673
  - 10
    - 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: Importing existing data Tag: 109669
  - 11
    - 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: Importing existing data Tag: 109668
  - 12
    - 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: Importing existing data Tag: 109667
  - 13
    - 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: Importing existing data Tag: 109666
  - 14
    - 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: Importing existing data Tag: 109657
  - 15
    - 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: Importing existing data Tag: 109653
  - 16
    - 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: Importing existing data Tag: 109652
  - 17
    - 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: Importing existing data Tag: 109650
  - 18
    - Using ms access how do I set column that can be of zero length TIA Tag: Importing existing data Tag: 109644
  - 19
    - 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: Importing existing data Tag: 109643
  - 20
    - 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: Importing existing data Tag: 109642
  - 21
    - 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: Importing existing data Tag: 109639
  - 22
    - 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: Importing existing data Tag: 109638
  - 23
    - 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: Importing existing data Tag: 109636
  - 24
    - 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: Importing existing data Tag: 109630
  - 25
    - 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: Importing existing data Tag: 109628
- Next
  - 1
    - 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: Importing existing data Tag: 109627
  - 2
    - 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: Importing existing data Tag: 109624
  - 3
    - 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: Importing existing data Tag: 109623
  - 4
    - 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: Importing existing data Tag: 109617
  - 5
    - 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: Importing existing data Tag: 109615
  - 6
    - 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: Importing existing data Tag: 109613
  - 7
    - 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: Importing existing data Tag: 109609
  - 8
    - 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: Importing existing data Tag: 109608
  - 9
    - 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: Importing existing data Tag: 109607
  - 10
    - 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: Importing existing data Tag: 109602
  - 11
    - 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: Importing existing data Tag: 109593
  - 12
    - 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: Importing existing data Tag: 109589
  - 13
    - 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: Importing existing data Tag: 109584
  - 14
    - 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: Importing existing data Tag: 109583
  - 15
    - 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: Importing existing data Tag: 109581
  - 16
    - 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: Importing existing data Tag: 109573
  - 17
    - 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: Importing existing data Tag: 109572
  - 18
    - 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: Importing existing data Tag: 109570
  - 19
    - 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: Importing existing data Tag: 109563
  - 20
    - 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: Importing existing data Tag: 109562
  - 21
    - 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: Importing existing data Tag: 109560
  - 22
    - 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: Importing existing data Tag: 109552
  - 23
    - 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: Importing existing data Tag: 109551
  - 24
    - 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: Importing existing data Tag: 109549
  - 25
    - 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: Importing existing data Tag: 109541

Hi

vs2005 beta2. I have created a sql server data file from within vs. Is there
any way to import existing access tables & data into the sql server
datafile? I have tried installed sql server 2004 developer from msdn but did
not see any enterprise manager type tools that come with sql server 2000.
What am I missing?

Thanks

Regards

Top

Re: Importing existing data by Val

Val
Sat Apr 30 20:05:39 CDT 2005

Hi,

SQL Server 2005 comes with a new management tool that calls SQL Server
Management Studio. It should be installed during installation of the SQL
Server 2005

--
Val Mazur
Microsoft MVP

http://xport.mvps.org

"John" <John@nospam.infovis.co.uk> wrote in message
news:ekZGEGYTFHA.3636@TK2MSFTNGP14.phx.gbl...
> Hi
>
> vs2005 beta2. I have created a sql server data file from within vs. Is
> there any way to import existing access tables & data into the sql server
> datafile? I have tried installed sql server 2004 developer from msdn but
> did not see any enterprise manager type tools that come with sql server
> 2000. What am I missing?
>
> Thanks
>
> Regards
>
>

Top