DataSet/DataTable to Excel

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
    - Access timeout? Is there something in the connection string that will timeout a MS Access connection if left open? The ldb doesn't ever seem to disappear, even no one is using the file. Thanks! Tag: DataSet/DataTable to Excel Tag: 92596
  - 2
    - Insertion of a nodelist using DataSet.WriteXml method. I have created an XML file using DataSet object, in my application. My Dataset consists of 2 tables. I want to insert a set of nodes in the second table. Which means I want to make the child rows of the table 2 of my dataset. The table will be 2 only , but the second table should have a new rowset in the last column. For this if I use WriteXml object, the insertion is at the last, means the insertion being done after the second table. it makes another table. So, i am not able to solve it properly. Actually , how to write the code to create update the XML document by inserting the node set, by using DataSetName.WriteXml method. Tag: DataSet/DataTable to Excel Tag: 92593
  - 3
    - Optimistic concurrency Hi group. Hope there are some gurus out there that can help me understand this topic. I am about to design a rather large department solution in .NET, utilizing ADO.NET, ASP.NET and XML WebServices. How do ADO.NET handle optimistic concurrency? From what I have read it is built in. But only if you use the CommandBuilder or construct your UPDATE stament/prodcedure so, that every column is checked against, right? I am having trouble finding out on what level the concurrency check is done. This is the "architecture" I plan to use: WebForm->DataBound component->DataSet->DataAdapter->DataSource I understand that if I only use one table, the CommandBuilder can be used to build select, update, delete and insert statements of the DataAdapter. On update all colums are checking against in the WHERE clause, to check if some of them have changed since the DataSet was filled by the DataAdapter. In this scenario it seems like the concurrency is handled at the database level. When not using the CommandBuilder, do I have to provide a "similar mecanism" myself, when writing the code for select, update, delete and insert statements? Sorry for the rather long post...But I hope someone has just a bit of the answers to my quesions.. Thanx in advance Best regards Henrik Tag: DataSet/DataTable to Excel Tag: 92591
  - 4
    - "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done." I'm trying to do a INSERT in a table with this code: protected int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected){ int result=0; rowsAffected = 0; try{ Connection.Open(); OleDbCommand command = BuildIntCommand(storedProcName,parameters); rowsAffected = command.ExecuteNonQuery(); result = (int)command.Parameters["ReturnValue"].Value; Connection.Close(); } catch(OleDbException e){... } private OleDbCommand BuildIntCommand(string storedProcName, IDataParameter[] parameters) { OleDbCommand command = BuildQueryCommand(storedProcName,parameters); command.Parameters.Add(new OleDbParameter("ReturnValue", OleDbType.Numeric, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null)); return command; } private OleDbCommand BuildQueryCommand(string storedProcName, IDataParameter[] parameters) { OleDbCommand command = new OleDbCommand(storedProcName, Connection); command.CommandType = CommandType.StoredProcedure; foreach(OleDbParameter parameter in parameters) { command.Parameters.Add(parameter); } return command; } My DataBase is Access 2000 and the called store is: PARAMETERS [@ID_INTERVENTO] Long; INSERT INTO TABLE_NAME(FIELD1) VALUES ([@PARAM1]); On command.ExecuteNonQuery() i have always this error: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done." Have you got any solutions?? thanks Tag: DataSet/DataTable to Excel Tag: 92587
  - 5
    - Access MSysRelationship Hi How to get access to Access database table MSysRelationship. Thanks Konrad Tag: DataSet/DataTable to Excel Tag: 92581
  - 6
    - Oracle update command and timestamp - how to get it back? To all you Oracle gurus out there, I'm updating an Oracle table with an OleDbDataAdapter (win forms). I'm using a timestamp column rather than comparing all the values to determine concurrency violation. The way I set the timestamp column is within the actual dataAdapter update/insert statement. SYS_TIMESTAMP = SYSDATE ..... equivalent of Now() in SQL I believe? Screenshot of update query builder: http://www.insightgis.com.au/web/stuff/update_qry_builder.gif This works fine, when an update is posted to the db it will write the current SYSDATE to the timestamp column. However, the currently displayed record on my form doesn't know about the new timestamp value, therefore if you make a subsequent edit to that same record and try to post changes to the db you get a concurrency violation error. Question is, when the dataAdapter makes the update to the db, can it refresh the local copy of the timestamp with the new value? Or do I have to fish out the whole record from the db again? One of my tables has 160 fields, so I'd rather not have to fish the whole record out again for obvious reasons :-) How do you Oracle people get around this problem? Cheers, PeterZ Tag: DataSet/DataTable to Excel Tag: 92579
  - 7
    - ComboBox, OleDb's, DataBinding I've got the following code in my form's Load event handler: OleDbConnection oleDbConnection = new OleDbConnection(String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}", databaseFilename)); string sqlstr = @"SELECT * FROM Companies"; companyOleDbDataAdapter = new OleDbDataAdapter(sqlstr, oleDbConnection); companyOleDbDataAdapter.Fill(companyDataSet, "Companies"); DataView dataView = companyDataSet.Tables["Companies"].DefaultView; companyComboBox.DisplayMember = "CompanyName"; companyComboBox.DataSource = dataView; After Fill is executed, the dataset contains the 1 table with 1 row. Setting DisplayMember and DataSource both succeed without errors. This is the end of the method. When the control is displayed, nothing is in the combobox. Am I missing a databinding step? T. Ford Tag: DataSet/DataTable to Excel Tag: 92577
  - 8
    - Oracle Problems I have posted a query before on this particular issue but no one had ever heard of this issue, and I find it difficult to swallow as well, but this is what is happening. When a read an Oracle table and I have a column which has the exact same value in the first row and the second row, the second row part of the row has "garbage" after about 4 characters. So if the first record has the value of "Happy Birthday", the second row will show "Happ\0\0\0". If I look at this same information using the same query and using Toad to execute my query, all is well both the first and the second record say "Happy Birthday". This happens for each and every table that I read and it is repeatable without exception. The column type that I am having problems with is "VarChar2". I am reading the column as follows: String myField = dr.GetString(3). I see there is a method of "GetOracleString(3)" and I have tried this method with the same results. I am using the "System.Data.OracleClient" namespace, which is the Microsoft version of the drivers. I am accessing an Oracle 8I table (or atleast this is what I am told). I am thinking of installing the Oracle version of the drivers or using the standard oledb drivers to get around the problem. Can anyone point me in the right direction on this issue? Tag: DataSet/DataTable to Excel Tag: 92572
  - 9
    - Constraints question I am using VS.net 2003 with SQL Server 2000 as the backend in a desktop application. My question has to do with constraints. The books seem to indicate that the best place to place a constraint is at the table level in SQL Server 2000. Maybe so but if there is no way to get data into SQL Server except through a VS application I wonder if it is really necessary to place a constraint in the SQL Server table if you have record or field validation in your code. What would be the purpose of that? Anyone Tag: DataSet/DataTable to Excel Tag: 92569
  - 10
    - Creating multiple columns in a DataTable based on Relationships I have three tables in a DataSet ds: MainTable, FK1Table, and FK2Table. MainTable has columns Name FK1_ID FK2_ID FK1Table has columns FK1_ID FK1_Name FK2Table has columns FK2_ID FK2_Name I have added a relationship to the dataset to match up MainTable to FK1Table and FK2Table. Now I want to add two new columns to MainTable called FK1_Name and FK2_Name and populate them with the "joined" data from the FK tables. The key lines looks like this: ds.Tables["MainTable"].Columns.Add("FK1_Name", typeof(System.String), "Parent(FK1Relation).FK1_Name"); ds.Tables["MainTable"].AcceptChanges(); ds.Tables["MainTable"].Columns.Add("FK2_Name", typeof(System.String), "Parent(FK2Relation).FK2_Name"); ds.Tables["MainTable"].AcceptChanges(); Now here's my problem. Both columns get added to MainTable, but only the first column ("FK1_Name") gets populated. If I reverse the order (i.e., add FK2_Name before FK1_Name) then FK2_Name gets populated but FK1_Name doesn't. Any ideas? Tag: DataSet/DataTable to Excel Tag: 92565
  - 11
    - Data Access Layer I am writing a fairly large database application for the first time, and am finding it difficult to decide on how to create the data access layer. I have tried reading as much as I can find about them on MSDN, but have found conflicting views, and am now really quite confused. For example, I am using typed datasets to store my data in. I have created a data access layer class, that basically has a set of procedures that accept a dataset, and fill it with some sort of data. These functions are basically static and stateless. Should the data access layer be stateful? Should I instantiate a data access layer in my main() method at the start of my application that basically fills my dataset when I first load my app, and then just pass a reference to the data layer into my various forms? I am finding it hard to know how to split up my database tables into datasets - just for the part I am working on now, I have about 20 tables, all of which are accessed from a single form. Should these just be in a single dataset? Or should I split them into smaller subgroups. The problem I see with this is that any relations between them will no longer exist! Also, if I have other forms that want to use just one or two of these 20 tables, should I create a separate dataset for them, or just use the reference to the data access layer and return the relevant tables? Sorry about the length of this post, but any help with any of the issues here would be greatfully received. Any links to good reference material would be useful too. Pete Tag: DataSet/DataTable to Excel Tag: 92561
  - 12
    - XML Filter I was assigned to a project where the main database is XML files instead of RDBMS. One of my primary responsibilities is to create Crystal Reports using .Net. We are having performance issues when we open and view reports. The reason is because Crystal Report that uses XML as a data source pulls all of the data into the report and I just have a filter inside the report. The XML files are loaded into a strong-typed dataset. Then this dataset is passed to Crystal Report. Is there away to filter XML data before I pass them to the Crystal Report? Thanks for any ideas! Cesar Tag: DataSet/DataTable to Excel Tag: 92559
  - 13
    - ODBC DataSource Dialog Hi, Is it possible to programaticly show and configure odbc drivers by standard ODBC DataSource Dialog. Regards, Olo. Tag: DataSet/DataTable to Excel Tag: 92554
  - 14
    - Error Com+ and ADO.NET Hi, I built a Com+ component that access a stored procedure and returns the data into a datareader. The component return the error below: " An unhandled exception of type 'System.Runtime.Remoting.RemotingException' occurred in mscorlib.dll Additional information: This remoting proxy has no channel sink which means either the server has no registered server channels that are listening, or this application has no suitable client channel to talk to the server. " Any ideas out there? public SqlDataReader ReturnReader() { SqlConnection objConn; objConn = new SqlConnection(); objConn.ConnectionString = "Server=Server1;DataBase=Db1;uid=sa;pwd=;"; SqlDataReader objReader; SqlCommand objCommand; objCommand = new SqlCommand(); objCommand.Parameters.Clear(); objCommand.CommandText = "STO_RETURN_DADOS"; objCommand.CommandType = CommandType.StoredProcedure; ObjConnMovimento.Open(); objCommand.Connection = objConn; objReader = objConn.ExecuteReader(); objConn.Close(); return objReader; } tks. Fabiano Tag: DataSet/DataTable to Excel Tag: 92553
  - 15
    - Update with SQL Data Adapter I have two tables in my sql database with the same structure. One table has 5 records and the other has 1 record. I want to take the 5 records into 1 dataset, and the 1 record into another dataset, merge into the data into one dataset (the first) and update the table with 5 records so now it has the additional 1. Everything works fine with code below with the merging, however when I run the Update method with my sql adapter, the table does not get the additional record...what is going on? ----------------------------------------------------------- Try Dim strConn As String strConn = "data source=" & ServerName & _ "; initial catalog=" & Database & _ "; uid=" & UserName & ";pwd=" & Password Dim conn As SQLConnection = New SQLConnection(strConn) Dim strSQL as String strSQL = "SELECT * FROM PDABookMarks" conn.Open() Dim cmdGetProfile As SqlDataAdapter = New SqlDataAdapter (strSQL, conn) Dim profileCB as SqlCommandBuilder = New SqlCommandBuilder(cmdGetProfile) cmdGetProfile.InsertCommand = profileCB.GetInsertCommand() cmdGetProfile.UpdateCommand = profileCB.GetUpdateCommand() Dim dsProfile As DataSet dsProfile = New DataSet() cmdGetProfile.Fill(dsProfile) conn.Close() strSQL = "SELECT * FROM PDABookMarks2" conn.Open() Dim cmdGetProfile2 As SqlDataAdapter = New SqlDataAdapter (strSQL, conn) Dim myData As DataSet myData = New DataSet() cmdGetProfile2.Fill(myData) dsProfile.Merge(myData) cmdGetProfile.Update(dsProfile) conn.Close() Return (dsProfile) Catch Return (Nothing) End Try Tag: DataSet/DataTable to Excel Tag: 92552
  - 16
    - Schedulers Components. Hi there, Does any body know where can I find a good Scheduler components (for appointment scheduling) for .NET.? I'm developing a scheduling application and I need to either build one from scratch, which I have no idea how to do that, or get a prebuild scheduler component that I can just include it in the application. Any advices, ideas, hints would much appreciated. Manuel. Tag: DataSet/DataTable to Excel Tag: 92550
  - 17
    - How to remove the time portion from a texbox Hi I have textbox in VB.Net that is bound to a Date/Time field in an Access database. The date in Access database only has the date and no time. When you load the dataset into the VB.Net application though, 12:00 time shows up. I realize this is the default but time is meaningless to our application and makes it look bad. Can anyone please let me know if there is a way to format a text box bound to a date field so that is automatically doesn't show the time part of the date. I saw a post or two about formatting using substring, but what event could be used to call this? I know this is a workaround, but is there a property to just display the date?? There must be an easier way!! Any help would be greatly apprecitated!! Tag: DataSet/DataTable to Excel Tag: 92547
  - 18
    - after .doc (word) creation it downloads the file but i want it to be opened On my dev PC the website that i wrote auto creates a word document and it auto-opens in my webbrowser. Whenever i visit the site with another PC the website ask if the user wants to download the .doc document of open it. Why is this and how can i make sure it opens in the browser only. my code: --------------------------------------------- Sub butMaakWord_OnClick(Sender as Object, e as EventArgs) Dim myConnection As OleDBConnection = New OleDBConnection(AppSettings("DigifanDatabaseCon")) myConnection.Open() Dim ds As DataSet Dim sXSL As String ds = New DataSet("klantenkaart") Dim ContactsDA As OleDBDataAdapter = New OleDBDataAdapter("SELECT * FROM Klantenbestand WHERE klantnummer="& Request.QueryString("ID") & "", myConnection) ContactsDA.Fill(ds, "Klantenbestand") ' XSLT to use for transforming this dataset. sXSL = "klantenkaart.xslt" ' Close the connection to the data source. myConnection.Close() ' Transform the dataset by using the appropriate stylesheet. Dim xmlDoc As XmlDataDocument = New XmlDataDocument(ds) Dim xslTran As XslTransform = New XslTransform xslTran.Load(Server.MapPath(sXSL)) ' Stream the results of the transformation to Word. Response.ContentType = "application/msword" Response.Charset = "" Response.ContentEncoding = System.Text.Encoding.Default xslTran.Transform(xmlDoc, Nothing, Response.Output) End Sub ---------------------------------------------------------------- thx in advance Tag: DataSet/DataTable to Excel Tag: 92542
  - 19
    - creating typed dataset Hi How to create typed DataSet with relations from a database. Is it possible with VS.NET 2003 or there is a tool doing this? Thanks Konrad Tag: DataSet/DataTable to Excel Tag: 92540
  - 20
    - executing join query on dataset Hi All I have a dataset having multiple tables. I have following queries. 1. Is there a way to execute join query on dataset 2. One alternative is to create the flat table with all the columns. For creating this table do i have to loop over each column in each table and then add that column to new table or is there any other way? How to take care of foreign key columns? 3. After creating the new flat table how to add the data from each table to the flat table? Any link or code will be a great help for me. Thanks in advance. Regards gagan Tag: DataSet/DataTable to Excel Tag: 92536
  - 21
    - Syntax to retrieve rows by datetime value I have a table in an Access DB with a datetime column, from which I'm trying to retrieve values before/after/between various date values, but I'm getting a syntax error in the SELECT command. I'm trying eg "SELECT * FROM mytable WHERE datetimecol > 29/6/04" The datetime values also have specific time values. Sometimes I'll want all values after a certain day (as the example above tries to do). Other times it will be a certain datetime range, maybe just within a single day. Anyone able to help with the correct syntax. (Maybe I've got the wrong form of the Select command altogether?). JGD Tag: DataSet/DataTable to Excel Tag: 92534
  - 22
    - How to Unload and de-map DataSet from XMLDocument?? I have a dataset that i use to create an XMLDocument. I later want to add a table to thee Dataset and the following error occurs. "Cannot add or remove tables from the DataSet once the DataSet is mapped to a loaded XML document" I have been unsuccessful finding a way around this has anyone else seen this or have a recomendation on how to load a DataTablee into a DataSet after an XMLDocument was instantiated??? Thanks Tag: DataSet/DataTable to Excel Tag: 92533
  - 23
    - Ccan't filter integer column in DataView Hello, I can't filter integer column in DataView I have one TextBox that I retreive the value and one ComboBox where I select column; When column is string (LastName) everything working very well, but when I select integer type (AccountNumber) generate an excpetion: System.Data.EvaluateException: "Cannot perform 'Like' operation on System.Int32 and System.String" Thanks in advance This is my source code: private void SetUpFilter() { this.dvList.RowFilter = null; if (this.txtFilterName.Text.Length != 0) { try { if (this.cmbFilterColumns.text.Length == 0) { MessageBox.Show("Select column"); return; } string filter = "["+ this.cmbFilterColumns.Text + "] LIKE '" + txtFilterName.Text.ToString() + "%'"; this.dvList.RowFilter = filter; } catch (Exception ex) { MessageBox.Show(ex.ToString()); ExceptionManager.Publish(ex); } } } Tag: DataSet/DataTable to Excel Tag: 92530
  - 24
    - Add Row Manually to DataGrid Hi, I have a DataGrid to which I want the program add from time to time a new row, without redrawing the whole DataGrid. So I don't want to add the new Row to the DataSource and than do a Refresh, but I want it kind of directly add it to the DataGrid so it only adds the new row. Does anybody knows how to do this? Or what I'd better use for this? The reason I want it is that repainting the (Custom)DataGrid takes some time, and I don't want to repaint it the whole time when I Add a new Row (every 5-6 seconds a new Row should be added). Thanks a lot in advance, Pieter Tag: DataSet/DataTable to Excel Tag: 92522
  - 25
    - Addnew Hi, I am new to vb.net and have a question. I created a dataform using the .NET wizard. When I click the "Addnew" command a new row is added but the bound textbox fields do not clear to allow user to enter the new fields values. Am I missing something? the form does display and allow user to edit and save changes. Thank you in advance. Tag: DataSet/DataTable to Excel Tag: 92521
- Next
  - 1
    - DataGrid + BeginUpdate/EndUpdate Hi, I found on the net that it is possible to use the BeginUpdate end EndUpdate-methods with a DataGrid: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemwindowsformsdatagridcolumnstyleclassbeginupdatetopic.asp Unfortunately it doesn't work with me: In some way I should make a derived class from the DataGridColumnStyle but I'm not able to do it. Des anybody knows how to do this? Thanks a lot in advance! Pieter Tag: DataSet/DataTable to Excel Tag: 92519
  - 2
    - Creating a Queried DataTable From an Existing DataTable I am manually creating a datatable in memory, creating columns etc for it and then populating it by importing data from files and various databases. Once I have the datatable in memory (which consists of around 10000 records) I need to be able to perform queries on it. I'm not talking about merely filtering or sorting it which I could do with a DataView, but performing real queries on it like grouping and counting. I can obviously do this by saving the datatable to a database and then using the OleDbDataAdapter to perform these kind of queries, but I don't want to have to save the data to a database, firstly because I don't need to store it and secondly because its incredibly slow saving 10000 records across a network. Is there a way I can perform SQL queries on a datatable that exists only in memory? Thanks Lee Tag: DataSet/DataTable to Excel Tag: 92503
  - 3
    - ORA-01036 Error with Microsoft Provider for Oracle Hi, I am using .Net Framework 1.0 with Oracle. I am using the Oracle provided data provider 'Oracle.DataAccess.Client' and I am using the parameterized queries. Now I want to use the Microsoft provided data provider 'System.Data.OracleClient' for Oracle, but when I change the reference in my project I start getting following error: ORA-01036: illegal variable name/number SQL String= SELECT u.opid,user_id,password,first_name,start_date,middle_name,last_name,INITIALS ,email,menu_color,predefined_bg_color,tab_color,body_color,security_type,fon t,max_search_results,max_search_results_per_page,startup_page,url,dept from UADUSR_USER u, UADUSP_USER_SCREEN_PREF p, UADAPP_APPLICATIONS a WHERE u.opid = p.opid AND u.startup_app = a.app_name_pk AND u.enabled_flag = 'E' AND LOWER(user_id) = :USERNAME AND password = :PASSWORD I guess this is a issue related to caramelized query related difference with both the data provider. Can you suggest some thing here, I basically want to make minimum changes in my code. I want my code to work on both Framework 1.0 and 1.1. Thanks Alok Tag: DataSet/DataTable to Excel Tag: 92499
  - 4
    - Oracle Provider or Microsoft Provider Hi, I am using .Net Framework 1.0 with Oracle. I am using the Oracle provided data provider 'Oracle.DataAccess.Client' and I am using the parameterized queries. Now I want to use the Microsoft provided data provider 'System.Data.OracleClient' for Oracle, but when I change the reference in my project I start getting following error: ORA-01036: illegal variable name/number SQL String= SELECT u.opid,user_id,password,first_name,start_date,middle_name,last_name,INITIALS ,email,menu_color,predefined_bg_color,tab_color,body_color,security_type,fon t,max_search_results,max_search_results_per_page,startup_page,url,dept from UADUSR_USER u, UADUSP_USER_SCREEN_PREF p, UADAPP_APPLICATIONS a WHERE u.opid = p.opid AND u.startup_app = a.app_name_pk AND u.enabled_flag = 'E' AND LOWER(user_id) = :USERNAME AND password = :PASSWORD I guess this is a issue related to caramelized query related difference with both the data provider. Can you suggest some thing here, I basically want to make minimum changes in my code. I want my code to work on both Framework 1.0 and 1.1. Thanks Alok Tag: DataSet/DataTable to Excel Tag: 92498
  - 5
    - sqldatareader connection Hi, why is this error exist? there is already an open datareader associated with this connection which must be closed first does the reader running on a server side cursor ? can't it work in a disconnected fashion? what should i do in order to run disconnected or when i want to make update on each iteration of the reader, use data table? why is it implemented this way? TIA, z. Tag: DataSet/DataTable to Excel Tag: 92496
  - 6
    - Check a textbox.text is number how to check a textbox.text is number? 52532590235320953535355 Tag: DataSet/DataTable to Excel Tag: 92491
  - 7
    - ASP DataGrid Sorting Hi all, I have written a simple Web Application that displays a query result onto a page using the ASP DataGrid. To Digress ======= Development information about the page is as follows 1. The database used is SqlServer 2000. 2. A Stored Procedure is dragged onto the page from the Server Explorer creating a connection and command object. 3. An adapter object is dragged onto the page from the Toolbox and the SelectCommand property is assigned the command object generated in step 2 4. A dataset object is created from the adapter object 5. In the page load, the relevent Adapter.Fill(dataset) and Grid.ReBind() functions are called. The above leads to a web page that displays the information retrieved by the Stored Procedure. The Problem ========= I have attempted to add sorting to the Grid. I have selected what is required to receive the Grid_SortCommand event in my page object and the e.SortExpression is returning the name of the field selected but any attempts to sort the data based on the SortExpression fails. The line I am using to sort is Dataset.Tables(0).DefaultView.Sort = e.SortExpression This is run after the dataset has been filled by the adapter but before the grid is rebinded to the dataset. The Question(s) =========== Can a grid be sorted when it is populated by a Stored Procedure? (cringe if this is so) Or; Have I failed to take something fundamental into account? All the examples I have found work with dynamically created dataviews/datatables. Any help would be greatly appreciated. Thanks in advance. Mark Tag: DataSet/DataTable to Excel Tag: 92490
  - 8
    - Dataadapter.Update fails intermittendly when updating large database My Program needs to insert about 6 Mio records into a large table on SQL Server 2000 which already has 2 or 3 Mio records. It uses a DataAdapter to insert 100 records at a time. About every 2 or 3 hours, the DataAdapter.Update fails with a timeout exeption (SqlException "Timeout expried. the timeout period elapsed prior to completion of the operation or the server is not responding". I used SQL profiler to fetch the latest INSERT INTO statement of th dataadapter. If I run that INSERT INTO statement in SQL Query analyzer, I notice that the command actually succeeds, but it takes 2 or 3 minutes to complete. I guess that SQL Server is growing the db size during this time. The problem is - how can I make the dataadapter wait for the command to complete? I used DataAdapter.UpdateCommand.CommandTimeout = 600 right before the DataAdapter.Update command, but it still returns after about 30 seconds with a timeout exception. I also tried to catch the exception, and reissue the update command if the exception occurs. This doesn't help either - the update will fail again and again. Thanks for your help Urs Tag: DataSet/DataTable to Excel Tag: 92487
  - 9
    - Handling empty relation to child object In a Typed Dataset, is there a way on the xs:keyref tag that I can specify how to handle null child objects? For example, if I have an Invoice table, where a ClientID key is used to create a reference to a Clients Table by it's ID Column. On the Clients table I have a Name element. In the calling code I do : x = myInvoice.Reference x = myInvoice.Amount x = myInvoice.Rate x = myInvoice.Client.Name this will throw an error if, for whatever reason, the Client does not exist. How can I get it to return an empty string instead of having to do : If Not myInvoice.Client = Nothing Then x = myInvoice.Client.Name else x = "" End If Tag: DataSet/DataTable to Excel Tag: 92481
  - 10
    - How to get row count of a DataRelation? If you have two dataTables and a dataRelation how would you get the row count of the dataRelation? Lets use the obligatory simpleton example - A strongly typed dataset with an Authors and Books table and a dataRelation between the two. ie: myDataSet.Authors myDataSet.Books myDataSet.relBooksByAuthor Now you bind a listbox to the Authors and a dataGrid to the dataRelation. The grid now shows only the books by the currently selected author. Mindblowing stuff I know, but just try to stay with me on this one :-) Ok, now how would you get the row count of the books by the currently selected author? Surely this must be a property of the dataRelation object? I mean it must know how many rows its filtering at any given time? I tried looking for a Rows.Count property in a dataRelation but there isn't any, ie: myDataSet.relations["relBooksByAuthor"].Rows.Count? not there! If I get the row count of myDataSet.Books.Rows.Count I always get the total number in the whole table, not the filtered count. Surely this must be a nobrainer? Can anyone shed light? Thanks, PeterZ Tag: DataSet/DataTable to Excel Tag: 92472
  - 11
    - DataAdapter Fill method I was wondering, does the dataadapter populates the whole DataSet objects with all the rows retrieved from the Select Command (eg.SELECT * FROM Customer) . Means, will this slow down the process if there is 10,000 records or more. Tag: DataSet/DataTable to Excel Tag: 92471
  - 12
    - how to change a integer of 0 into null when insert db how to change a integer of 0 into null when insert db Tag: DataSet/DataTable to Excel Tag: 92469
  - 13
    - how to change a integer of 0 into null when insert db how to change a integer of 0 into null when insert db joe Tag: DataSet/DataTable to Excel Tag: 92468
  - 14
    - How to insert a integer = 0 into database to be null when i passed a integer = 0 to a sqlparameter, how to change to null into db Tag: DataSet/DataTable to Excel Tag: 92467
  - 15
    - Connection Timeout I am trying to run as stored procedure in vb.net. When I try to execute the sp I keep getting a Connection timeout error. I have increased my timeout property but it continues to timeout. This sp may take quite a long time to run. Can someone help? Tag: DataSet/DataTable to Excel Tag: 92459
  - 16
    - OleDB Select Scope_Identity() after insert ERROR It gives an error saying there is 2 much text after the SQL command. with MSSQL i just placed, "; SELECT SCOPE_IDENTITY()" after the insert statement and i would get the current ID, but when i use OleDB it gives me the error each time, anyone know if there is someother syntax for an MS Access DB? thx in advance Richard Tag: DataSet/DataTable to Excel Tag: 92449
  - 17
    - How do I release a DataAdapter. Hi All, After I call a data adapter for a straight insert on the DB, if I call the same data adapter for an update on the same record, it throws an concurency error saying that the update command updated 0 rows. Now, how do I release and initialize it again so I can update that record right after the insert command? Thanks very much for you input on this one. Manuel Tag: DataSet/DataTable to Excel Tag: 92448
  - 18
    - Fun and excitement updating related data Ideally, what I would like to do is to populate a datagrid with the information from 2 tables. Man, does that sound simple ... if so, please join me on this expedition ... Begin by using two dataAdapters, filling one from the main database table and another from the foreign-keyed table and set up a data relation (thanks to Kevin on pointing me in the general direction). Now use the expressions to add columns that we want displayed in the grid. But this is where things get crazy. Indeed, I can now bind this related data to the datagrid. Everything "looks" great. I can even do a delete with CurrencyManager. But when I try to do an Insert to the main table, I get a constraint exception, "ForeignKeyConstraint requires the child key values to exist in the parent table" Do who, huh, what? The child (Supplier) key values must exist in the parent (Zip) table. My update logic calls only for the Supplier table (and worked perfect before I went down this road). And I most certainly do not want to be inserting any data into the Zip table (it is a lookup table for all zip, city, state names in the U.S.). Just because I enjoy banging my head against the wall, I set the relation constraint to False. Now we actually add a record to the Supplier table, but I still get an exception: "Cannot change ReadOnly property for the Expression column" Here is the relevant snippet. If anyone can see clearly through this, advice would be appreciated. Note that what should be the Parent table is set as the Child because the silly syntax for the Expression apparently does not allow you to associate a column from the Child. ******************************************** Dim strSQLServer As New SqlConnection(strConnString) Dim cmdSuppliers As New SqlCommand("GetAllSuppliers", strSQLServer) 'a join query that matches up all supplier zip IDs Dim cmdZipMatches As New SqlCommand("GetAllSupplierZips", strSQLServer) cmdSuppliers.CommandType = CommandType.StoredProcedure cmdZipMatches.CommandType = CommandType.StoredProcedure cmdSuppliers.Connection = strSQLServer cmdZipMatches.Connection = strSQLServer strSQLServer.Open() Dim da As New SqlDataAdapter(cmdSuppliers) Dim daZip As New SqlDataAdapter(cmdZipMatches) da.Fill(dsx, "dtSuppliers") daZip.Fill(ds, "dtSupplierZips") ds.Tables("dtSuppliers").Columns("SupplierID").ColumnMapping = MappingType.Hidden ds.Tables("dtSuppliers").Columns("Tstamp").ColumnMapping = MappingType.Hidden ds.Tables("dtSuppliers").Columns("SupplierZipCityID").ColumnMapping = MappingType.Hidden ds.Tables("dtSupplierZips").Columns("ZipCityStateID").ColumnMapping = MappingType.Hidden Dim rel As DataRelation rel = New DataRelation("CityStateZipSupplier", _ ds.Tables("dtSupplierZips").Columns("ZipCityStateID"), _ ds.Tables("dtSuppliers").Columns("SupplierZipCityID"), False) ds.Relations.Add(rel) ds("dtSuppliers").Columns.Add("City", GetType(String), "Parent(CityStateZipSupplier).City") ds.Tables("dtSuppliers").Columns.Add("State", GetType(String), "Parent(CityStateZipSupplier).State") ds.Tables("dtSuppliers").Columns.Add("Zip", GetType(String), "Parent(CityStateZipSupplier).Zip") dtSuppliers = ds.Tables("dtSuppliers") DataGrid1.DataSource = dtSuppliers Tag: DataSet/DataTable to Excel Tag: 92443
  - 19
    - Please Help: DB2 C# Weird Error "ExecuteReader requires an open and available Connection" Hi, I'm building an Intranet for my client. The computer is running Windows 2000 Server ASP.NET Version:1.1.4322.573 and DB2 UDB 8.1 DB2 has 10 databases each one with 10-12 tables. In one ASPX page I need to access the 10 tables for each DB. When I used to have 4 DB it worked fine, but one there are 5 it crashes with this error: "ExecuteReader requires an open and available Connection. The connection's current state is Closed. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.InvalidOperationException: ExecuteReader requires an open and available Connection. The connection's current state is Closed." Here is the code I use to open the connection and retrieve the information: In a file named module.cs I have this // Module 1 namespace Combalia.Conexiones.ADONET { using System; using System.Data.OleDb; using System.Data; using System.IO; using System.Web; public class adoConn { OleDbConnection mySqlConnection; public static void Main() { adoConn myadoConn = new adoConn(); myadoConn.Run("null"); } public OleDbDataReader dbSQLSelect(string mySelectQuery) { OleDbCommand myCommand = new OleDbCommand(mySelectQuery,mySqlConnection); OleDbDataReader myReader = myCommand.ExecuteReader(); return myReader; } public bool dbSQLDIU(string myDIUQuery) { OleDbCommand myCommand = new OleDbCommand(myDIUQuery,mySqlConnection); if(myCommand.ExecuteNonQuery() > 0) { return true; } else { return false; } } public void Run(string sDB) { mySqlConnection = new OleDbConnection("Provider=IBMDADB2.1;Data Source='" + sDB + "';User ID=administrator;Password=xxxxxxx;Network Address=xxx.xxx.xxx.xxx; Max Pool Size=100;Connection Lifetime=0"); try { mySqlConnection.Open(); if(mySqlConnection.State != ConnectionState.Open) { mySqlConnection.Open(); } } catch { Console.WriteLine("No se pudo conectar con {0}", mySqlConnection.ConnectionString); } } public string Close() { mySqlConnection.Close(); OleDbConnection.ReleaseObjectPool(); mySqlConnection.Dispose(); return "ok"; } } } // End Module 1 Then I use this code in the ASPX page: if(ShowDb1) { adoConn adoConn = new adoConn(); adoConn.Run("DBNAME"); OleDbDataReader myReader = adoConn.dbSQLSelect("Select Query"); try { while (myReader.Read()) { iTotal += myReader.GetInt32(0); } } finally { myReader.Close(); adoConn.Close(); } myReader = adoConn.dbSQLSelect("Select Query 2"); try { while (myReader.Read()) { iSuplidos += myReader.GetInt32(0); } } finally { myReader.Close(); } } ...And so on for each table and DB Any ideas what might be the problem? Thank you Tag: DataSet/DataTable to Excel Tag: 92442
  - 20
    - Microsoft Data Access Block for .NET and the "like" statement Is there a way to use a like statement with a SqlParameter and the Microsoft Data Access Block? I would like to be able to do this: int CustomerID = (int) SqlHelper.ExecuteScalar( ConfigurationSettings.AppSettings["connectionString"], CommandType.Text, @"select CustomerId from Northwind..Customers where ContactName like '%@customer_name%'", new SqlParameter("@customer_name",CustomerName) ); Since the DAB already quotes my SqlParameter, SqlServer ends up seeing extra quote signs. Something like: where ContactName like '%'John Doe'%' Any thoughts? -Paul Microsoft Data Access Block: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/daab-rm.asp Tag: DataSet/DataTable to Excel Tag: 92441
  - 21
    - can't isolate changes to bound datatable I have a dataview on a datatable as part of a dataset. I have bound the controls on my form to the columns in the dataview and when i modify the fields in the form the datatable gets updated. I know that this is true because i can repopulate the form from the datatable and i get the new values. But when i check if the underlying dataset .haschanges it returns false and the databletable.getchanges returns nothing and changing the rowstatefilter for the dataview yields nothing. what am i missing? Thanks John Tag: DataSet/DataTable to Excel Tag: 92439
  - 22
    - DataRowView Question Hi, I am binding to a DataRowView object which contains approximately one row. However, one of the columns of the row contains an array of author names. I bind to it like this: Me.lblAuthors.Text = row("Authors") 'row being the DataRowView object When I run the code, I get no errors but the authors are not shown as expected. The output is System.String[] for the label's text property. I'd like the author names to be listed and separated by commas. How do I properly bind to the array of authors using the DataRowView object? Must I do some type of string conversion or something? Thanks, Roshawn Tag: DataSet/DataTable to Excel Tag: 92437
  - 23
    - time and dates? Hi! Is there a sample or some classes to deal with time and date calculations? I have records that i need to calculate, the difference between the time the clocked in and out and the time they have worked on a order through out the day. Please...give me some advice? regards, Jake. Tag: DataSet/DataTable to Excel Tag: 92424
  - 24
    - Best Method for DataSet Hi all, I have used a DataGrid in my ASP.Net, C# WebPage and Binded DataSet to DataGrid in "not IsPostBack". I have Three DropDownList OutSide DataGrid Which act as Filters For Data in DataGrid, When user Changes the selected item in DropDownList the data in grid should change. example : One DropDownList Contains all countries of records are shown in grid, now when you select suppose "USA" only records of country "USA" are shown. and 3 such DropDowns are given. now my question is how shall i put these filters to DataSet. 1> shall i Query DB for each filter and bind new DataSet to grid each time. 2> shall i use a Static DataSet and just Put Filter to DataSet. 3> shall i write DataSet to XML File and refer it each time. give me the best solution which is not very heavy. Thanx in advanca... Vivek Kumbhojkar Tag: DataSet/DataTable to Excel Tag: 92418
  - 25
    - Debuging Scalar-Valued Function in Visual Studio with date parameters Hello, I couldn't find a better newsgroup to put this post in. If there is one, feel free to respond with that group. I'm running VS .NET 2003 Ent Arch., tapping into a MS SQL 2000 database. I am trying to debug a function that I wrote on the SQL Server. The function has three parameters including two smalldatetime parameters. CREATE FUNCTION dbo.DaysBetweenByAccrual (@Day1 smalldatetime, @Day2 smalldatetime, @AccrualType varchar(8)) RETURNS int ... My issue is when trying to run or step through the function using VS, I cannot enter (or do not know the proper format) the date parameters in the parameter input dialog. I've tried entering the following formats and I also show the resulting value in the locals window: Date Format Result mm/dd/yyyy 1/1/1900 12:00:00 AM mm/dd/yyyy 12:00:00 AM Does not run 'mm/dd/yyyy' Error: @Day1: this input parameter cannot be converted #mm/dd/yyyy# Error: @Day1: this input parameter cannot be converted The database output is the following: If I placed 01/01/2004 and 02/01/2004 for the dates. Running dbo."DaysBetweenByAccrual" ( @Day1 = 01/01/2004, @Day2 = 02/01/2004, @AccrualType = 30/360 ). I can get around this issue by editing the value of the dates in the locals window but there's got to be a way to pass these parameters through the dialog so that it works and not always pass them in as 1/1/1900. Any suggestions? Thanks in advance. Tag: DataSet/DataTable to Excel Tag: 92417

Hi,
Is there any similiar way to transfer data from DataSet/DataTable to Excel Worksheet providing there is not need to copy each row/column cell individually from DataTable/DataSet into individual cells on excel worksheet.
Ideally ADO function CopyFromRecordset would be suitable but my data is in the DataSet/DataTable in .NET app.
Thank you,

Nataliya

Top