Porting a data layer from SQL to OleDb/Access

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
    - Create New DataTable From DataView I have a DataView but now I need a DataTable to pass into several routines I've created. Can I make a DataTable from a DataView? The DataView is now sorted and cleaned up and I don't want to reference the original table the DataView came from. Axe Tag: Porting a data layer from SQL to OleDb/Access Tag: 69708
  - 2
    - Can't add data to DataSet from non-creating thread? Hi, My app receives data via an async socket. I need to put this data into a DataSet and display it via a DataGrid. If I add the data to the dataset when it arrives using the async created thread, my app crashes with a NullException inside Application.Run(). If I disconnect the DataSet from the DataGrid (dataGrid.DataSouce = null), then the app does NOT crash. It only crashes when I'm trying to display the data that I'm adding to the DataGrid. So what's the best way to solve this problem? How do you get data into a DataSet via the non-creating thread? I built a demo app below that demonstrates the problem which is listed below. THANKS! John using System; using System.Drawing; using System.Collections; using System.ComponentModel; using System.Windows.Forms; using System.Data; using System.Threading; namespace DataGrid { /// <summary> /// Summary description for Form1. /// </summary> public class DG : System.Windows.Forms.Form { Random random = new Random(); bool running = true; private System.Windows.Forms.DataGrid dataGrid; private DataGrid.MyDataset myDataset; private System.Windows.Forms.Timer timer; private System.Windows.Forms.CheckBox checkBox; private System.ComponentModel.IContainer components; public DG() { // // Required for Windows Form Designer support // InitializeComponent(); dataGrid.DataSource = myDataset; ThreadStart threadStart = new ThreadStart(listenThread); Thread thread = new Thread(threadStart); thread.IsBackground = true; thread.Start(); } private void listenThread() { while ( running ) { // How do I add data to myDataset from this thread? // If I add data to myDataset from this thread, while // myDataset is "connected" to the datagrid, then the // app will crash with a NullException // How do I use the "main" thread to add data to myDataset? // myDataset.MyTable.AddMyTableRow(DateTime.Now, random.Next()); Thread.Sleep(200); } } /// <summary> /// Clean up any resources being used. /// </summary> protected override void Dispose( bool disposing ) { if( disposing ) { if (components != null) { components.Dispose(); } } base.Dispose( disposing ); } #region Windows Form Designer generated code /// <summary> /// Required method for Designer support - do not modify /// the contents of this method with the code editor. /// </summary> private void InitializeComponent() { this.components = new System.ComponentModel.Container(); this.dataGrid = new System.Windows.Forms.DataGrid(); this.myDataset = new DataGrid.MyDataset(); this.timer = new System.Windows.Forms.Timer(this.components); this.checkBox = new System.Windows.Forms.CheckBox(); ((System.ComponentModel.ISupportInitialize)(this.dataGrid)).BeginInit(); ((System.ComponentModel.ISupportInitialize)(this.myDataset)).BeginInit(); this.SuspendLayout(); // // dataGrid // this.dataGrid.Anchor = ((System.Windows.Forms.AnchorStyles)((((System.Windows.Forms.AnchorStyles.To p | System.Windows.Forms.AnchorStyles.Bottom) | System.Windows.Forms.AnchorStyles.Left) | System.Windows.Forms.AnchorStyles.Right))); this.dataGrid.DataMember = ""; this.dataGrid.HeaderForeColor = System.Drawing.SystemColors.ControlText; this.dataGrid.Location = new System.Drawing.Point(8, 48); this.dataGrid.Name = "dataGrid"; this.dataGrid.Size = new System.Drawing.Size(280, 216); this.dataGrid.TabIndex = 0; // // myDataset // this.myDataset.DataSetName = "MyDataset"; this.myDataset.Locale = new System.Globalization.CultureInfo("en-US"); // // timer // this.timer.Tick += new System.EventHandler(this.timer_Tick); // // checkBox // this.checkBox.Location = new System.Drawing.Point(8, 8); this.checkBox.Name = "checkBox"; this.checkBox.TabIndex = 1; this.checkBox.Text = "Timer Enabled"; this.checkBox.CheckedChanged += new System.EventHandler(this.checkBox_CheckedChanged); // // DG // this.AutoScaleBaseSize = new System.Drawing.Size(5, 13); this.ClientSize = new System.Drawing.Size(296, 271); this.Controls.Add(this.checkBox); this.Controls.Add(this.dataGrid); this.Name = "DG"; this.Text = "DataGrid Test"; ((System.ComponentModel.ISupportInitialize)(this.dataGrid)).EndInit(); ((System.ComponentModel.ISupportInitialize)(this.myDataset)).EndInit(); this.ResumeLayout(false); } #endregion /// <summary> /// The main entry point for the application. /// </summary> [STAThread] static void Main() { Application.Run(new DG()); } private void checkBox_CheckedChanged(object sender, System.EventArgs e) { timer.Enabled = checkBox.Checked; } private void timer_Tick(object sender, System.EventArgs e) { // NOTE: THIS WORKS // Console.WriteLine("Fire"); // myDataset.MyTable.AddMyTableRow(DateTime.Now, random.Next()); } } } Tag: Porting a data layer from SQL to OleDb/Access Tag: 69701
  - 3
    - Datagrid with hyperlink columns, need to pass or hold multiple variables. I have a datagrid with hyperlink columns on my page Fill.aspx. I allow users to click on any column in a row. This triggers a lookup using the order#(SubON) which fills in various text boxes with further information. Right now my code looks like this: <asp:datagrid id="dgdList" style="Z-INDEX: 107; LEFT: 0px; POSITION: absolute; TOP: 0px" runat="server"Font-Names="Arial" Font-Size="Smaller" Width="480px" Height="136px" AutoGenerateColumns="False"> <SelectedItemStyle BackColor="PowderBlue"></SelectedItemStyle> <AlternatingItemStyle BackColor="#FFE0C0"></AlternatingItemStyle> <HeaderStyle Font-Size="X-Small" Font-Names="Arial" Font-Bold="True" BackColor="Orange"></HeaderStyle> <Columns> <asp:HyperLinkColumnDataNavigateUrlField="SubON"DataNavigateUrlFormatString="Fill.aspx?SubON={0}"DataTextField="SubON"HeaderText="Order #"> </asp:HyperLinkColumn> <asp:HyperLinkColumnDataNavigateUrlField="SubON"DataNavigateUrlFormatString="Fill.aspx?SubON={0}" DataTextField="Block"HeaderText="Block"></asp:HyperLinkColumn> <asp:HyperLinkColumnDataNavigateUrlField="SubON"DataNavigateUrlFormatString="Fill.aspx?SubON={0}" DataTextField="Quantity"HeaderText="Quantity"> </asp:HyperLinkColumn> <asp:HyperLinkColumnDataNavigateUrlField="SubON"DataNavigateUrlFormatString="Fill.aspx?SubON={0}" DataTextField="NPA"HeaderText="NPA"></asp:HyperLinkColumn> <asp:HyperLinkColumnDataNavigateUrlField="SubON"DataNavigateUrlFormatString="Fill.aspx?SubON={0}" DataTextField="RC"HeaderText="RC"></asp:HyperLinkColumn> <asp:HyperLinkColumnDataNavigateUrlField="SubON"DataNavigateUrlFormatString="Fill.aspx?SubON={0}" DataTextField="RDate"HeaderText="Date"></asp:HyperLinkColumn> </Columns> </asp:datagrid> What I need to do is carry another variable as well. Since I'm reloading the page: DataNavigateUrlFormatString="Fill.aspx?SubON={0}" I loose the variable on the page where I'm holding it in an invisible text box. So, I need a variable which I've been carrying by url string for several pages as well as the SubOn variable. I've tried adding to the UrlFormatString, but I don't know how to do so properly if it can be done. Another option would be to use postback instead of loading the page again (I think) but I can't figure out how to do that either. Can anyone help? Tag: Porting a data layer from SQL to OleDb/Access Tag: 69698
  - 4
    - newbie transaction question Hello All: I am attempting to execute two statements as part of a single transaction using the Microsoft data access application block. The code, which is similar to what follows, returns this message when it attempts to execute the second "SqlHelper.ExecuteReader" statement: "The SqlCommand is currently busy Open, Fetching" I would greatly appreciate any insight! Thanks, Chris ---------------------------------------------------------------------------- - SqlConnection sqlConnection = new SqlConnection(DB("SPMS")); sqlConnection.Open(); SqlTransaction sqlTransaction = sqlConnection.BeginTransaction(IsolationLevel.Serializable); try { // Set parameters // Insert consumer SqlDataReader sqlDataReader = SqlHelper.ExecuteReader(sqlTransaction, CommandType.StoredProcedure, "ConsumerAdd", consumerParameters)); safeDataReader.Close(); // Insert consumer history SqlDataReader sqlDataReaderHistory = SqlHelper.ExecuteReader(sqlTransaction,CommandType.StoredProcedure,"Consumer HistoryAdd",consumerParameters)); sqlTransaction.Commit(); } catch(Exception Exception) { sqlTransaction.Rollback(); throw Exception; } finally { sqlConnection.Close(); } Tag: Porting a data layer from SQL to OleDb/Access Tag: 69694
  - 5
    - Dataset Record Size Restriction Hi All, I was wondering.. do we have any restriction on 1. No of columns in a DataSet-Table 2. Record size restriction per row 3. Record size restriction Data Ste level I am asking this coz i am facing a funny problem. I execute a querry and it returns me some data. Now, some time the query gets executed but no records are returned and no exception is thrown. Now, when i reduce the record size, by removing some columns or reducing the text per column, i am able to get the results. Any pointers? I am using System.Data.Oracle client with Oracle 9i, 8.0 and i am using OracleDataAdapter to fill my DataSet. Tag: Porting a data layer from SQL to OleDb/Access Tag: 69687
  - 6
    - DataBinding Question Hi, Right now in my app I am using databinding for some of my combo boxes. I only uses databinding to display the data to the user. Here is my question. Since, databinding keeps the dataset in memory is it better just to loop through the dataset table and add each item? Or should I create an array list from a datareader object to display the data? I would use the datareader object however I would have to pass the datareader from the database to the business component then to the UI. Any suggestions? Thanks Tag: Porting a data layer from SQL to OleDb/Access Tag: 69686
  - 7
    - HOWTO: Insert New Row I want to write a funciton that has 2 parameters(string tableName and IDbDataParameter[] params). I can't seem to figure out how to create a new record using a combination of a Dataset/DataTable/DataRow. Or is there a better way to insert rows? Tag: Porting a data layer from SQL to OleDb/Access Tag: 69685
  - 8
    - Binding data from DataReader to DataGrid Hi I have used SQLDataReader for fetching data(due to performance issue) and now I want to bind this data to DataGrid, How should I do this? Sample code is most welcomed :) Makarand Tag: Porting a data layer from SQL to OleDb/Access Tag: 69677
  - 9
    - MDAC and .NET framework Hello all. I have a set up question. Here it is: I wrote ASP.NET application that uses ADO.NET (everything is managed code). Now I am going to deploy this app to my web server. I installed .NET Framework 1.1 Question: Do I need to install MDAC2.7 on my web server for ADO.NET to work? Thanks. Tag: Porting a data layer from SQL to OleDb/Access Tag: 69673
  - 10
    - Calling AddNew on BindingContect (CurrencyManager) Calling AddNew on BindingContext creates a New DataRow. What is that DataRow's state and how do I retrieve that DataRow prior to a call to EndCurrentEdit? I want to pass that DataRow as a parameter. Tag: Porting a data layer from SQL to OleDb/Access Tag: 69672
  - 11
    - How is this CurrencyManager persisting???? Hi, I have a DataGrid on a form (WinForms App) that has an event that opens another form containing a user control . The CurrencyManager from the form that contains the Grid is referenced in the form that is opened as well as in the user control . Everything works fine until I close the form, and click on another row in the DataGrid. The CurrencyManager_Position_Changed event is raised in the UserControl contained on the form that was just Disposed of! Is the reference of the CurrencyManager in the original form keeping it alive? When the form that opens from the DataGrid is closed, I want the CurrencyManager in that form and containing UserControl to be destroyed. How can I do this? Thanks! Dave Tag: Porting a data layer from SQL to OleDb/Access Tag: 69665
  - 12
    - Access Related Query Hi, I am using Access as a backend to my project. From my front end I am issuing the queries, however I am finding it is too much slow. I wanted to execute multiple sql statement. My question here is how to execute these multiple statement?. Is it possible in acess 2000. can I have your suggestions?. TIA Kishor Tag: Porting a data layer from SQL to OleDb/Access Tag: 69658
  - 13
    - Does WinForm UserControl inherit BindingContext of owner? Hi, Does a usercontrol placed on a form automatically inherit or acquire the parent forms' BindingContext? Thanks, Dave Tag: Porting a data layer from SQL to OleDb/Access Tag: 69650
  - 14
    - Extract images from sql server Hi, I've got a column in a table which contains images (bmp and jpg, i guess sql server don't convent the jpg to bmp??). How can I extract these images from the table. I've tried something with a MemoryStream, but I don't know how to save it to a file because this stream is a byte array and I can't check the file type and other stuff like that. Is there anybody with the same problem? Thanks a lot Filip Tag: Porting a data layer from SQL to OleDb/Access Tag: 69645
  - 15
    - HLP: Output Display Issues I'm slowly making my way through my project (this project being the first time I've ever tried working with a DB - Access file in this case using VB.net Standad). But now I've come to the part where I want to work on my 'visual' output format to the end user. The Access file is fixed and I can't modify it (which is too bad). But here is what I want to (hope to) have: The DB stores a Users weekly time sheet information. Each row represent a single project, date of one day of the week, and the number of hours for that project for that day. So a person could work on the same project each day and have 5 rows of time sheet entries. Then etc. for any other projects they work on that week. The DB has 14 columns overall. I want to only display a couple of them. And I want to add 7 additional columns (Saturday to Friday) to display calculated project hour values (each day for that week). Just like a typical time sheet would display... the Project + week days on ONE row. I've the DataAdaptors, DataSets and Filters in place and working fine bounded to a test DataGrid (just to see the raw data for now). The number of rows can be anywhere from none... to (say) 30+... depending upon the number of project the user worked on. A graphic display is thus: Typical Raw DB data Record row I would use (uneccessary columns omitted here): Project; WeekDate; DayHours; UserName; UserChargeOut Project; WeekDate; DayHours; UserName; UserChargeOut Project; WeekDate; DayHours; UserName; UserChargeOut The typical Final output row would look something like: Project; SatHrs; SunHrs; MonHrs; TuesHrs; WedHrs; ThurHrs; FriHrs Project; SatHrs; SunHrs; MonHrs; TuesHrs; WedHrs; ThurHrs; FriHrs Project; SatHrs; SunHrs; MonHrs; TuesHrs; WedHrs; ThurHrs; FriHrs Then I'll need to know how to retrieve the five days original data lines when a User clicks on one of the Final Output rows. This is so a user can edit/delete a record set. The two choices I believe I have is either a DataGrid or a ListView. Or... should I have a blank Access file of all the columns I want... bind the DataSet to it and work out on displaying my data? I'm probably asking for too much here. I don't know. But I really MISS a lot of the display methods that VB6 use to have :( Any help is muchly appreciated. Hopefully I've explained it clearly enough. Regards, Bruce Tag: Porting a data layer from SQL to OleDb/Access Tag: 69637
  - 16
    - get record field value in ASP.NET In ASP 3.0, rs("field_name") is able to get the value of field_name in a recordset. In ASP.NET using VB.NET, I think SqlDataReader is the substitute of recordset? I don't quit understand how to use it. Here's my code attempts, but it yields run-time error "Invalid attempt to read when no data is present"on dr(i).ToString Dim cn As New SqlConnection(cs) Dim sql As String = "select * from [Admin] where UserName = '" & TextBox1.Text & "'" & "AND Password = '" & TextBox2.Text & "'" & ";" Dim cmd As New SqlCommand(sql, cn) cmd.Connection.Open() Dim dr As SqlDataReader dr = cmd.ExecuteReader() For i = 0 To dr.FieldCount - 1 strLine = strLine & dr.GetName(i).ToString & Chr(9) & dr(i).ToString Next Please advise! Thanks! Tag: Porting a data layer from SQL to OleDb/Access Tag: 69636
  - 17
    - Active Directory Related Question. Hi, I like to know if there is any way we can use SQL Query syntax for getting values out of ADS. If there is any method pls let me know with example. Thanks Regards ARvind Rangan Tag: Porting a data layer from SQL to OleDb/Access Tag: 69626
  - 18
    - ADS Related Query Hi, I like to know if there is any way we can use SQL Query syntax for getting values out of Active Directory Schema. If there is any method pls let me know with example. Thanks Regards ARvind Rangan Tag: Porting a data layer from SQL to OleDb/Access Tag: 69625
  - 19
    - What's about the " PInvoke Meta data" error? I'm coding the test for testing my project ( which is combined with 2 dll files ) . When I call the function from the dll file , it's occur error display "PInvoke Meta data" error and the function cann't run in the right way. Aspite it can run smoothly when compile by step. Please suggest me how can I test ( or trace ) my program by simulate the working function and call inside code. Thanks. Tag: Porting a data layer from SQL to OleDb/Access Tag: 69623
  - 20
    - ASP.NET SQL Server Please pardon my ignorance on this one, but here's what I'm trying to do: I have a table in a SQL Server 2000 DB called distributors. I've got an ASPX which has a text box, a button, and two labels. In the textbox, the user is to enter a "distributor id", then click the button. The button should query the distributors table for a matching distributor id, and return two prices, which is what the labels should reflect. Right now, though, I'm not even trying to match up the distributor id. The table has only one record in it at this time. This is what I'm doing right now: sqlConnection1.ConnectionString = blah blah blah; sqlSelectCommand1.CommandText = "SELECT distid, colorprice, blackprice FROM dbo.distributors"; sqlSelectCommand1.Connection = sqlConnection1; private void Button1_Click(object sender, System.EventArgs e) { sqlConnection1.Open(); sqlDataReader MyReader = sqlSelectCommand1.ExecuteReader(); Label1.Text = MyReader.getSqlMoney(1).ToString(); Label2.Text = MyReader.getSqlMoney(2).ToString(); sqlConnection1.Close; } As you can see, right now, I'm not even trying to match up the distributor id. I'm just trying to get the prices for the one record that's there. After I get that working I'll want to start matching up the distributor id. The error I'm getting is: Invalid attempt to read when no data is present. Any ideas? Thanks! Scott Tag: Porting a data layer from SQL to OleDb/Access Tag: 69618
  - 21
    - Sql Joins Anyone have any experience working with middle tiers and Stored Procedures with INNER Joins and OUTER JOINS. If so, I need some suggestion on how you handled tables aliases. Thanks, - Rob Tag: Porting a data layer from SQL to OleDb/Access Tag: 69613
  - 22
    - comparison error Hi I am getting the 'Operator is not valid for type 'DBNull' and string "".' error on the following line; If (mydatatable.Rows.Item(I).Item("Forenames") Is System.DBNull.Value) Then ' 'I' is the loop variable from 0 To mydatatable.Rows.Count - 1. Any ideas on what is the reason for this error? Thanks Regards Tag: Porting a data layer from SQL to OleDb/Access Tag: 69600
  - 23
    - simple postback to a database In a .Net Asp application I am able to retrieve data from an Access db and display in on a web form. How ever during the update function after a page update, the update command does not throw an exception but it does not save out the changes either. It seems that upon the postback no data exists in the dataset. I must be missing something. How you post the data from an edited user page back to the database? if (!(Page.IsPostBack)) { this.oleDbDAUser.Fill(this.dsMain1,"Users"); this.lblUserID.DataBind(); this.txtFirstName.DataBind(); this.txtMiddleName.DataBind(); ......... } No error here, but no data is saved either: private void btnSave_Click(object sender, System.EventArgs e) { try { this.oleDbDAUser.Update(this.dsMain1); } catch (Exception oops) { this.txtUserMessage.Text = "Error occured!<BR>" + oops.ToString() ; } } } Thanks, Ray Tag: Porting a data layer from SQL to OleDb/Access Tag: 69597
  - 24
    - DataAdapter.Fill method problem When I run following code it creates two connections to Sql Server database. I observed this from SQL Profiler. Can someone explain this behavior? SqlDataAdapter objAdpater = new SqlDataAdapter("SELECT * FROM TABLE", "Server=xyz;User ID=sa;Password="); DataSet objDS = new DataSet(); objAdpater.Fill(objDS); I am running windows 2000 SP3, SQL Server 2000 SP3. -Thanks -Vivek. Tag: Porting a data layer from SQL to OleDb/Access Tag: 69596
  - 25
    - Overloading Update method I want to overload a userdefined method named update so that I pass in different typed datasets and through a case statement it would then update using the appropriate dataadapter. Is this possible and if so how would the check go to look for the particular typed dataset? Also how do I define the method? Tag: Porting a data layer from SQL to OleDb/Access Tag: 69595
- Next
  - 1
    - 3RD POST- IS MICROSOFT READING THESE HELP! I am trying to produce a the column that contains an error after an sqldataadapter update. Just connect to a sql db get a dataset, set the column to a string greater than the field and try da.update. then catch it a sqlexception and pass it to this ms sample code: Private Sub GetAllErrs(ByVal myRow As DataRow) ' Declare an array variable for DataColumn objects. Dim colArr() As DataColumn ' If the Row has errors, check use GetColumnsInError. Dim i As Integer If myRow.HasErrors Then ' Get the array of columns in error. colArr = myRow.GetColumnsInError() For i = 0 to colArr.GetUpperBound(0) ' Insert code to fix errors on each column. Console.WriteLine(colArr(i).ColumnName) Next i ' Clear errors after reconciling. myRow.ClearErrors() End If End Sub here is the ms link http://msdn.microsoft.com/library/default.asp? url=/library/en- us/cpref/html/frlrfsystemdatadatarowclassgetcolumnsinerrort opic.asp the column array is 0 with no elements! Please help and thanks in advance . Tag: Porting a data layer from SQL to OleDb/Access Tag: 69593
  - 2
    - performance ADO.NET vs ADO Do u know articles about performance benchmark comparing ADO.NET vs ADO? Thanks Tag: Porting a data layer from SQL to OleDb/Access Tag: 69592
  - 3
    - 2 simple questions about ado/xml hi... 1. what is the quickest/most efficient way to get read-only records from an access table into an xml string? i have the following which works fine. is this the most efficient way? OleDbDataAdapter da = new OleDbDataAdapter(cmd); DataSet ds = new DataSet("Records"); da.Fill (ds, "Record"); return ds.GetXml(); 2. after i add/update/delete access record(s) i want the information of the record(s) in xml. how can i do this? thanks a lot. :) Tag: Porting a data layer from SQL to OleDb/Access Tag: 69588
  - 4
    - Connection String vs DSN, what to use and why? Which is better to use a connection string or DSN? Why? It seems like using a connection string is recommended but what exactly are the reasons for this? Also, one of the nice things about DSN's is the ability to quickly change what database your app is pointing to. How is this done with connection strings? Registry entries? I thought I read somewhere connections strings being held in the registry should be encrypted, how should this be accomplished? Is there a semi-standard way of doing all this? Thanks for any help you may provide, Dan Tag: Porting a data layer from SQL to OleDb/Access Tag: 69585
  - 5
    - Query Group Dataset I have a dataset / datatable in memory and i would like to run a query against that to create another in memory datatable or dataview. In this case I have a bunch of data... and i want to run a 'grouping' select query where i just get all items grouped together for example: Existing in memory datatable (2 fields, qty and desc): Record 1: Qty: 23 Desc: Bananas Record 2: Qty 10 Desc: Strawberries Record 3: Qty 23 Desc: Bananas Record 4: Qty 18 Desc: Grapes I want to run a group query on field 'Desc' to give me: Record 1: Bananas Record 2: Strawberries Record 3: Grapes Any ideas? I know how to do this if the data is in an sql database.. but how do i run a query against an in memory dataset? Thanks in advance! Chris cthunell@pierceassociates.com Tag: Porting a data layer from SQL to OleDb/Access Tag: 69582
  - 6
    - DataAdapter.Fill very slow I am currently having a problem with a DataAdapter.Fill performing very slow or timing out. Our code is written in C#, platform Windows 2003 and the database is SQL Server 2000. However after reviewing other postings this seems to be an ado.net problem and not tied to SQL Server. groups.google.com search: DataAdapter Fill Slow We use Typed DataSets and stored procedures in our code. The query is very simple. It queries three tables that are left joined and has a where clause for an ID, and two dates. The tables have been indexed appropriately. I have tried multiple things to include: Changing from Typed DataSets to the regular DataSets. Creating performance counters to see what is happening on the machines. Running the query in Query Analyzer and Enterprise Manager runs in .5 seconds or faster. Changed databases and machines. Rebooted machines, refreshed SQL Server services, restart SQL Server. When I debug in DotNet it always points to the Fill of the DataAdapter. If anyone has any suggestions it would be appreciated. Tag: Porting a data layer from SQL to OleDb/Access Tag: 69575
  - 7
    - newbie vb6 ADO to vb.net ado.net, paging Hi, I am converting an existing VB6 webclass app to VB.NET ADO.NET and I'm having a problem coming up with solution for paging the records returned from my stored procedures. As I understand it from the help I can use a dataadapters fill() method to specify a page size and tha page I want. However this will only return 1 page worth of data. Currently in my web pages I provide next/back links and a <SELECT> list of page numbers. If I use the dataadapters fill() method to do the paging how will my code be able to workout how many pages there are all together? I looked at: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconpagingthroughqueryresult.asp ... but that doesn't seem to fit what I want to do. In ADO it was simple now it seems to be a lot harder. I realise that ADO.NET is built for performance and so doesn't come with as many built in features but some examples of ADO to ADO.NET in the area of paging would of been handy. Anybody done the same leap from ADO --> ADO.NET and done paging? Regards, Peter Tag: Porting a data layer from SQL to OleDb/Access Tag: 69573
  - 8
    - Simple form postback to update a database In a .Net Asp application I am able to retrieve data from an Access db and display in on a web form. How ever during the update function after a page update, the update command does not throw an exception but it does not save out the changes either. It seems that upon the postback no data exists in the dataset. I must be missing something. How you post the data from an edited user page back to the database? if (!(Page.IsPostBack)) { this.oleDbDAUser.Fill(this.dsMain1,"Users"); this.lblUserID.DataBind(); this.txtFirstName.DataBind(); this.txtMiddleName.DataBind(); ......... } No error here, but no data is saved either: private void btnSave_Click(object sender, System.EventArgs e) { try { this.oleDbDAUser.Update(this.dsMain1); } catch (Exception oops) { this.txtUserMessage.Text = "Error occured!<BR>" + oops.ToString() ; } } } Thanks, Ray -- -- /\_Ray Valenti ____ http://www.quixor.com ______ \/ Quixor Flex Systems, Ltd. Tag: Porting a data layer from SQL to OleDb/Access Tag: 69571
  - 9
    - Simple form postback to update a database In a .Net Asp application I am able to retrieve data from an Access db and display in on a web form. How ever during the update function after a page update, the update command does not throw an exception but it does not save out the changes either. It seems that upon the postback no data exists in the dataset. I must be missing something. How you post the data from an edited user page back to the database? if (!(Page.IsPostBack)) { this.oleDbDAUser.Fill(this.dsMain1,"Users"); this.lblUserID.DataBind(); this.txtFirstName.DataBind(); this.txtMiddleName.DataBind(); ......... } No error here, but no data is saved either: private void btnSave_Click(object sender, System.EventArgs e) { try { this.oleDbDAUser.Update(this.dsMain1); } catch (Exception oops) { this.txtUserMessage.Text = "Error occured!<BR>" + oops.ToString() ; } } } Thanks, Ray -- Tag: Porting a data layer from SQL to OleDb/Access Tag: 69570
  - 10
    - Access Passwords Why am I having trouble getting the visual studio designer to accept an access database with a password? It keeps giving me an error that the database is being used by me but I am not. Tag: Porting a data layer from SQL to OleDb/Access Tag: 69564
  - 11
    - Combobox vertical scroll position Does anyone know how to determine the slider position (i.e. relative to the min and max values) of a combobox's vertical scroll bar (i.e. maybe a win api call?). I am trying to feedback the list's value as the user's scrolls the combobox (the list is too large to fill the combobox). I can't find any way to get this information. I seem to recall that in VB 6.0 this was available in one of the scrolling functions. If there is no way to get this info in vb.net, I would like to build my own combobox with textbox, listbox and vertical scroll bar. However, the problem then becomes how to make the vertical scroll bar look like the combobox scroll bar (I like the appearance of the latter when running on XP). Tag: Porting a data layer from SQL to OleDb/Access Tag: 69562
  - 12
    - Adding rows to dataset (a.k.a. can an MVP help me please!) I'm totally lost here.... I have a blank typed dataset designed to hold invoice data, OK? The dataset has two tables, InvoiceHeaders and InvoiceLines. The invoice data is generated by a system/36 emulator and therefore comes in as a comma-delimited text file. My task is to get the text into the dataset so I can bind it to a crystal report and print the invoices. SO: I open the header text file, and go into this loop to attempt to fill the header table: Dim Ivc as New IvcDataSet Do While Not EOF(1) Dim x As IvcDataSet.InvHeadersRow Input(1, x.InsuredNo) Input(1, x.Item("EntryMonth")) Input(1, x.Item("KeyerInit")).... Ivc.Tables("InvHeaders").Rows.Add(x) x = Nothing Loop When I run the code, I get an error on the first input line because x is not set to an instance of an object. Being familiar with this type error I change the Dim line to read: Dim x As New IvcDataSet.InvHeadersRow Now, I have a blue squigly under the x that says "Argument not specified for parameter 'rb'..." or something like that. I find out that the rb is a System.Data.DataRowBuilder object. So I turn to the MSDN library to find out about the DataRowBuilder class. My research reveals that, according to MSDN, "This type supports the .NET Framework infrastructure and is not intended to be used directly from your code." So how the hell am I supposed to add rows to my datasets???? Tag: Porting a data layer from SQL to OleDb/Access Tag: 69559
  - 13
    - .NET Clr Data performance counters Hi. I have one asp.net application running on my server. I am playing with the .NET Clr DATA performance counters in order to control the connections created by the application. When I select the counter "sqlclient: current # connection pools", the instances listbox shows me several instances of the same asp.net application, each one with a different number. What are these numbers? TIA, Erik Cruz Tag: Porting a data layer from SQL to OleDb/Access Tag: 69558
  - 14
    - DB Locking Hi, I have an application framework which consists of several webservices, webpages and application all of which use a couple of MS Access database. We are currently in the testing phase of the project so we don't want to have to upgrade to SQL Server yet. The problem is that when the webservices or applications open the connections to the databases they open it in single user mode. No matter what connection strings I use they still refuse to open in multi-user mode. This is the connection string I'm using: "Provider=Microsoft.Jet.OLEDB.4.0;Password='';User ID=Admin;Data Source=E:\BORG\TypeProcessor\TypeProcessor.mdb;Mode=Share Deny None;Jet OLEDB:System database='';Jet OLEDB:Registry Path='';Jet OLEDB:Database Password='';Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password='';Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False" Can you suggest anything? Thanks, Simon Stockdale Tag: Porting a data layer from SQL to OleDb/Access Tag: 69557
  - 15
    - Assigning Hot Keys in Web Applications Hello, Can anybody help me to add a hot key to a web application button and have the text on the button underscored. I'm using C# within .NET e.g. assign Alt-C to a button with text "Click Me" where the "C" is underscored. Many Thanks Garfield Tag: Porting a data layer from SQL to OleDb/Access Tag: 69553
  - 16
    - connection pooling problems with sqlconnection object Hi, I have the following code that gets run in my database wrapper class. If Not (m_conn Is Nothing) Then If m_conn.state = ConnectionState.Open Then m_conn.Close() End IF m_conn = Nothing End If I get the following error on the m_conn.Close() line: An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dll Additional information: A connection pooling error has occurred. The IF has already checked that the connection is open so why the hell is it saying the operation is invalid? It typically seems to produce this error after doing a couple of debug runs of the app. Regards, Peter Tag: Porting a data layer from SQL to OleDb/Access Tag: 69551
  - 17
    - DataSet V/s Data Objects Hi Friends I have few questions about using DataSets as data storage and using User defined classes (Data Objects) as data storage. Suppose I have plain functionality of retrieving data from data source and updating data into data source and no other complex functionality then how good is idea of using Data Objects instead of DataSets (for better performance)? Can I measure the performance in both the cases if yes how can I do this. Any help on this will be appreciated Regards Makarand Tag: Porting a data layer from SQL to OleDb/Access Tag: 69550
  - 18
    - CAN ANYBODY HELP?! Could anyone help me with the following problem! I open an SqlConnection. Then: 1. execute a reader on SqlCommand and get data through that reader; 2. in a certain time I call Cancel() method on SqlCommand and SqlDataReader.Close(). If I perform steps 1 and 2 several times (sometimes it happens even from the first time) I get the error which says that SqlConnection is closed. And it is. So, seems that SqlCommand.Cancel() in combination with SqlDataReader.Close() forces the connection to close. I can't determine the reason why it happens. Can I control somehow this behaviour, because I need the connection to be opened. Thank you in advance, Sergey. Tag: Porting a data layer from SQL to OleDb/Access Tag: 69549
  - 19
    - ADO.NET/SQL Mystery In using two different SQL Database server (one for development and one for production) a call to a stored procedure is storing incorrect data. We use ADO.NET SQL Data Adpaters and have set the stored procedure parameters. Tracing through on the debugger shows the correct data in the dataset but the SQL Profiler shows that the second parameters is coming in as a -1 (which is not the data in the dataset). To make matters worse this only occurs on the Development sever with the production server executing without error. What could be the difference in the two servers that parses the ADO.NET interface parameters? Tag: Porting a data layer from SQL to OleDb/Access Tag: 69548
  - 20
    - DB2 Connection I am trying to connect DB2. But, I can't not find DB2 in oledb provider list in vs.net 2003. Is there anying additionally required software to connect DB2? Tag: Porting a data layer from SQL to OleDb/Access Tag: 69546
  - 21
    - Nullvalued doubles in a strong typed dataset I have a strong typed dataset with some double values. In my database, from where I fill the dataset, I have numbers for these doubles, but they can also bee empty (NULL). I need this to know if the user has typed in 0 or hase typed in nothing. If I set the nullvalue for the doubles in my dataset to (NULL), I get an exception. How can I pass the System.DBNULL value from my database to my doubles? Thanks Thomas Tag: Porting a data layer from SQL to OleDb/Access Tag: 69545
  - 22
    - Validations inside datagrid Pleas advise me how to do validations in side datagrid before updating the details into database. I am using VB.NET and updating the datagrid using sqlcommandbuilder. I want to validate the data that user changhes inside the grid. How can I do this? Pls help... Regards, Omav Tag: Porting a data layer from SQL to OleDb/Access Tag: 69536
  - 23
    - ASP.NET using ADO.NET connection runtime error I created a simple ASP.NET using ADO.NET application to connect to SQL Server. However, it produces the following run-time error. I think ASPNET is the ASP.NET machine account created by VS.NET, and I already added ASPNET in IIS Manager in directory security tab, and disable the checkbox of anonymous access. Unfortunately, it still not able to solve the problem. Any good suggestions?? Thanks! Exception Details: System.Data.SqlClient.SqlException: User 'MYComputerName\ASPNET' Registered Failure. Source Error on line 37: Line 35: Dim cs As String = "Data Source=MyComputerName;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=autoattendant;Trusted_Connection=yes" Line 36: Dim cn As New SqlConnection(cs) Line 37: cn.Open() Line 38: Response.Write("string = " & cn.ConnectionString) Line 39: End Sub Source File: E:\web\WebApplication2\WebForm1.aspx.vb Line: 37 Stack Trace: [SqlException: User 'TRUSTGOD\ASPNET' Registered Failed] System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnec tionString options, Boolean& isInTransaction) System.Data.SqlClient.SqlConnection.Open() WebApplication2.WebForm1.Submit1_ServerClick(Object sender, EventArgs e) in E:\web\WebApplication2\WebForm1.aspx.vb:43 System.Web.UI.HtmlControls.HtmlInputButton.OnServerClick(EventArgs e) System.Web.UI.HtmlControls.HtmlInputButton.System.Web.UI.IPostBackEventHandl er.RaisePostBackEvent(String eventArgument) System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) System.Web.UI.Page.ProcessRequestMain() Tag: Porting a data layer from SQL to OleDb/Access Tag: 69532
  - 24
    - NewRow method changes values in a reference to another row?! Using .NET Framework 1.1. I do a select for a row, assign a strongly typed var to reference/change the row thuought the rest of the code(OrigRow). After various responses from the user that change some column values of OrigRow, I begin a transaction and select(By PK) for the row again to bring it into the transaction. I use the strongly typed NewRow method of the datatable and assign it to another variable(NewRow) to add a new row based on some of the user responses. Right after the newrow method is executed, the column values of both OrigRow and NewRow change to the original db values of OrigRow!!!??? DA.Fill(DS.DT) 'select * OrigRow as DTRow = DT.rows(0) 'here, OrigRow.Qty = 10 .... do some work, OrigRow.Qty += 10 'proposed value = 20 ... 'now work is done, start a transaction... conn.open conn.BeginTransaction 'change cmd obj for PK DA.Fill(DS.DT) 'select where ID = OrigRow.ID, assigned to Txn 'In retrospect, shouldn't this reset values of OrigRow? Another thread! 'now create a new row for another reason NewRow as DTRow = DT.NewRow 'RIGHT HERE, OrigRow Values AND NewRow Values change to pre-work values of OrigRow!!!!???? e.g., OrigRow.Qty = 10 AND NewRow.Qty = 10, NOT NULL!!?? Anybody else see this or able to explain what I'm seeing, I'm going CRAZY! Thanks for any help, Dwaine Tag: Porting a data layer from SQL to OleDb/Access Tag: 69531
  - 25
    - Need help with data relations I need to establish a relationship that looks like this: One Payment can have many PaymentLine One PaymentLine can have one Payer One PaymentLine can have many PaymentLine Details The relationships that I create between Payment and PaymentLine work, and the one between PaymentLine and PaymentLine detail work. Whenever, I bind the dataset to a grid, it will never pick up the auto-identity for a payment and payer on a payment line. How can I make this work? Any help or insight would greatly be appreciated. Here is some of the code that I built into a winform testing application: Imports System.Data.SqlClient Public Class Form1 Inherits System.Windows.Forms.Form #Region " Windows Form Designer generated code " Public Sub New() MyBase.New() 'This call is required by the Windows Form Designer. InitializeComponent() 'Add any initialization after the InitializeComponent() call End Sub 'Form overrides dispose to clean up the component list. Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean) If disposing Then If Not (components Is Nothing) Then components.Dispose() End If End If MyBase.Dispose(disposing) End Sub 'Required by the Windows Form Designer Private components As System.ComponentModel.IContainer 'NOTE: The following procedure is required by the Windows Form Designer 'It can be modified using the Windows Form Designer. 'Do not modify it using the code editor. Friend WithEvents Button1 As System.Windows.Forms.Button Friend WithEvents DataGrid1 As System.Windows.Forms.DataGrid <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent() Me.Button1 = New System.Windows.Forms.Button Me.DataGrid1 = New System.Windows.Forms.DataGrid CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).BeginInit() Me.SuspendLayout() ' 'Button1 ' Me.Button1.Location = New System.Drawing.Point (16, 16) Me.Button1.Name = "Button1" Me.Button1.TabIndex = 0 Me.Button1.Text = "Button1" ' 'DataGrid1 ' Me.DataGrid1.DataMember = "" Me.DataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText Me.DataGrid1.Location = New System.Drawing.Point (24, 56) Me.DataGrid1.Name = "DataGrid1" Me.DataGrid1.Size = New System.Drawing.Size(680, 240) Me.DataGrid1.TabIndex = 1 ' 'Form1 ' Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13) Me.ClientSize = New System.Drawing.Size(712, 318) Me.Controls.Add(Me.DataGrid1) Me.Controls.Add(Me.Button1) Me.Name = "Form1" Me.Text = "Form1" CType(Me.DataGrid1, System.ComponentModel.ISupportInitialize).EndInit() Me.ResumeLayout(False) End Sub #End Region Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim myPmtTable As New DataTable("Pmt") Dim myColumn As New DataColumn Dim ds As New DataSet myPmtTable.Columns.Add(AddColumn ("Pmt_id", "System.Int32", True)) 'Add table to dataset ds.Tables.Add(myPmtTable) Dim myPmtLnTable As New DataTable("PmtLn") myPmtLnTable.Columns.Add(AddColumn ("PmtLn_id", "System.Int32", True)) myPmtLnTable.Columns.Add(AddColumn ("Pmt_id", "System.Int32", False)) myPmtLnTable.Columns.Add(AddColumn ("Payer_id", "System.Int32", False)) 'Add table to dataset ds.Tables.Add(myPmtLnTable) Dim myPmtLnDetTable As New DataTable("PmtLnDet") myPmtLnDetTable.Columns.Add(AddColumn ("PmtLnDet_id", "System.Int32", True)) myPmtLnDetTable.Columns.Add(AddColumn ("PmtLn_id", "System.Int32", False)) myPmtLnDetTable.Columns.Add(AddColumn ("PmtLnDet_amount", "System.Double", False)) 'Add table to dataset ds.Tables.Add(myPmtLnDetTable) Dim myPayerTable As New DataTable("Payer") myPayerTable.Columns.Add(AddColumn ("Payer_id", "System.Int32", True)) myPayerTable.Columns.Add(AddColumn ("Payer_name", "System.String", False)) ds.Tables.Add(myPayerTable) 'Create another dataset, just to see what happens Dim myDatasetWithRelations As DataSet = BuildPaymentDataRelations(ds) DataGrid1.DataSource = myDatasetWithRelations End Sub Public Function AddColumn(ByVal ColumnName As String, ByVal DataType As String, ByVal autoIncrement As Boolean) As DataColumn Dim myColumn As New DataColumn myColumn.ColumnName = ColumnName myColumn.DataType = Type.GetType(DataType) myColumn.AutoIncrement = autoIncrement If autoIncrement Then myColumn.AutoIncrementSeed = -1 End If Return myColumn End Function Private Function BuildPaymentDataRelations(ByVal dataset As DataSet) As DataSet 'blnRelationsCreated = True Dim parentCol, childCol As DataColumn parentCol = dataset.Tables("Pmt").Columns ("pmt_id") childCol = dataset.Tables("PmtLn").Columns ("pmt_id") dataset.Relations.Add("Pmt_PmtLn", parentCol, childCol) parentCol = dataset.Tables("Payer").Columns ("payer_id") childCol = dataset.Tables("PmtLn").Columns ("payer_id") dataset.Relations.Add("Payer_PmtLn", parentCol, childCol) parentCol = dataset.Tables("PmtLn").Columns ("pmtln_id") childCol = dataset.Tables("PmtLnDet").Columns ("pmtln_id") dataset.Relations.Add("PmtLn_PmtLnDet", parentCol, childCol) Return dataset End Function End Class Tag: Porting a data layer from SQL to OleDb/Access Tag: 69530

I'm quaking at the thought of doing this (it's against my will!), but
are there any significant "gotchas" porting a data layer from the
SqlClient to the OleDb classes for use with Access? I've got
straight-forward SQL for all of the commandtext, generally with
parameters for every query. Does Access speak the same language?

Top

Re: Porting a data layer from SQL to OleDb/Access by Thomas

Thomas
Tue Oct 28 11:14:22 CST 2003

1: parameters are numberd, not named.
2: if you use [] as delimiter for field names, and misspell a field name,
OleDb will complain about a parameter not initialised.
3: the join syntax for ANSI joins is different - radically, so to say. Took
me half adozen saple someone wrote me to get it right.
4: GUID encoding is different.
5: as are dates.
6: Datetime parameters - are buggy. They dont accept a DateTime object, but
accept object.ToString () - seems like a formatting issue.

So, this is abot US english and Indian english, if you want it in languages.

Thank heaven I only have to step into this occasionally as all our SQL is
now auto-generated from an abstract form :-)

Thomas Tomiczek
THONA Software & Consulting Ltd.
(Microsoft MVP C#/.NET)

"Jeff 'Jones' Putz" <jeffyjones@hotmail.com> wrote in message
news:66c1aab0.0310280907.4f8cfc41@posting.google.com...
> I'm quaking at the thought of doing this (it's against my will!), but
> are there any significant "gotchas" porting a data layer from the
> SqlClient to the OleDb classes for use with Access? I've got
> straight-forward SQL for all of the commandtext, generally with
> parameters for every query. Does Access speak the same language?

Top