ExecuteNonReader hangs

TheMSsForum.com: The Microsoft Software Forum

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

- Previous
  - 1
    - How to you page and sort large dataset result in datagrid? In ASPNET 1.1 Is there a good way to page/sort VERY large Result set in datagrid > > > > > > P. Wilson blog wrote: " Here's a stored procedure that I use for sorting and paging large recordsets in SQL Server, as opposed to using the more common and terribly inefficient entire dataset approach in .NET. It certainly doesn't matter much in the little classroom examples of a few hundred, or even thousands, of records, but working with larger recordsets with datasets is just bad. This is even more true with ASP.NET, since the entire dataset is usually saved in viewstate, and since the web server is doing the processing for many users. This stored procedure is certainly not the only way to do sorting and paging in SQL Server, but it is probably the most efficient, although proper indexing is still required to avoid table scans. Note that this does not use temporary tables, which are convenient but not as optimal -- this also means you could modify this to be dynamic SQL for Access or other databases! The parameters are the name of the table, the name of the primary key (necessary for the little bit of optimization included), the name of the sort field (or sort fields, with or without ASC/DESC), and the page size (number of records). It also allows optional parameters for the page index (defaults to 1st page) and a query filter (defaults to null) so you can sort and page through filtered records only! Note that it returns two recordsets -- the first is the results you expect, and the second recordset is a scalar with the number of total pages so you can better define the GUI pager for a grid. This is relatively easy to integrate with the ASP.NET datagrid if you use custom paging and sorting, and it will minimize both your load on the server and the amount of data sent to the client's browser! By the way, this code is just modified from some I found on the net, and there are certainly some minor optimizations that can be done, like using different sql for the first page. " CREATE PROCEDURE GetSortedPage( @TableName VARCHAR(50), @PrimaryKey VARCHAR(25), @SortField VARCHAR(100), @PageSize INT, @PageIndex INT = 1, @QueryFilter VARCHAR(100) = NULL ) AS SET NOCOUNT ON DECLARE @SizeString AS VARCHAR(5) DECLARE @PrevString AS VARCHAR(5) SET @SizeString = CONVERT(VARCHAR, @PageSize) SET @PrevString = CONVERT(VARCHAR, @PageSize * (@PageIndex - 1)) IF @QueryFilter IS NULL OR @QueryFilter = '' BEGIN EXEC( 'SELECT * FROM ' + @TableName + ' WHERE ' + @PrimaryKey + ' IN (SELECT TOP ' + @SizeString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' WHERE ' + @PrimaryKey + ' NOT IN (SELECT TOP ' + @PrevString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' ORDER BY ' + @SortField + ') ORDER BY ' + @SortField + ') ORDER BY ' + @SortField ) EXEC('SELECT (COUNT(*) - 1)/' + @SizeString + ' + 1 AS PageCount FROM ' + @TableName) END ELSE BEGIN EXEC( 'SELECT * FROM ' + @TableName + ' WHERE ' + @PrimaryKey + ' IN (SELECT TOP ' + @SizeString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' WHERE ' + @QueryFilter + ' AND ' + @PrimaryKey + ' NOT IN (SELECT TOP ' + @PrevString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' WHERE ' + @QueryFilter + ' ORDER BY ' + @SortField + ') ORDER BY ' + @SortField + ') ORDER BY ' + @SortField ) EXEC('SELECT (COUNT(*) - 1)/' + @SizeString + ' + 1 AS PageCount FROM ' + @TableName + ' WHERE ' + @QueryFilter) END RETURN 0 GO posted on Friday, October 10, 2003 9:04 AM Comments >>>> COMMENTS # re: Sorting and Paging Recordsets in SQL Server Darrell Posted @ 10/10/2003 10:15 AM * Just taking a quick look, you could change the "SELECT Count(*)" to "SELECT Count(@PrimaryKey)" and that should give you a speed boost. Reducing the number of columns in the select statement always speeds things up, even for aggregate operations like Count. Very interesting though; I will have to see how well it works for complex queries. # re: Sorting and Paging Recordsets in SQL Server Torbjörn Axelsson Posted @ 10/27/2003 11:14 AM Very interesting! Right now I'm using a solution based on creating a temporary table in the stored procedure for selecting rows from a table containing over a million rows. I will test to implement this solution and see if it boosts performance, which would be very welcome :) In my project I will have to make an inner join to retrieve User names from another table, but since I only have to do this on the outermost SELECT-statement (after TableName), and I have a good indexed structure, that shouldn't have a big impact on performance. Any thoughts? * # Recordset sorting in SQL Server Jamie Jones Posted @ 11/16/2003 11:05 AM * # O/R Mappers: Simple Database Features ? Paul Wilson's .NET Blog Posted @ 1/9/2004 3:54 PM * # re: Sorting and Paging Recordsets in SQL Server David Portas Posted @ 1/23/2004 5:42 PM See also Aaron Bertrand's comprehensive survey of techniques for paging a result set in SQLServer: http://www.aspfaq.com/show.asp?id=2120 * # re: Sorting and Paging Recordsets in SQL Server HELP! Posted @ 2/11/2004 7:34 PM what's the asp supposed to look like? * # re: Sorting and Paging Recordsets in SQL Server Mark Posted @ 3/6/2004 8:49 AM I was wondering if you had an example of using this code with an asp.net datagrid, complete with paging and sorting. email: mwpat@rogers.com * # re: Sorting and Paging Recordsets in SQL Server Paul Wilson Posted @ 3/6/2004 11:28 AM I use it internally at work on a project, but that's not an example I can share. I also use this, but wrapped up in my WilsonORMapper, on my site, but there I use repeaters since I don't like datagrids. There are lots of other examples that show you the asp.net code that you want, so I haven't bothered to recreate one yet again -- the only thing I'm pointing out is how to set up the stored procedure in a better way. * # re: Sorting and Paging Recordsets in SQL Server babu Posted @ 4/2/2004 5:26 AM quite helpful - babu * # re: Sorting and Paging Recordsets in SQL Server Bob Posted @ 4/5/2004 5:08 AM Sorry, I'm new to sql. Why the extra "SELECT * FROM ~ IN ~" statement? Why not just do a "SELECT TOP ' + @SizeString + ' * FROM ..." and make the second select the top select (if you see what I mean). Wouldn't this have the same affect? * # re: Sorting and Paging Recordsets in SQL Server SANDESH Posted @ 4/6/2004 3:25 AM GOOD ONE * # re: Sorting and Paging Recordsets in SQL Server theintrepidfox@hotmail.com Posted @ 4/17/2004 7:47 PM Hi! Excellent fantastic work! You've made my day as I was experiencing with paging scripts but the only solution I came up with supporting DESC and filtering doesn't perform very well. I probably would have spend the next days coming up with something like yours. Thanks very much! * # re: Sorting and Paging Recordsets in SQL Server AcidJazz Posted @ 4/29/2004 8:11 PM Great stuff! I spent last couple of hours looking for something similar and I could see this proc working very well in my project. Thanks! * # re: Sorting and Paging Recordsets in SQL Server senkwe Posted @ 7/8/2004 9:34 AM Has anybody tried this for tables with a few million rows yet? Thanks. * # re: Sorting and Paging Recordsets in SQL Server Paul Wilson Posted @ 7/8/2004 9:42 AM I actually just got done reading http://codeproject.com/aspnet/paginglarge.asp -- and I may switch what I use. I've never heard any complaints, but it does look like there are better solutions. * # re: Sorting and Paging Recordsets in SQL Server Jonny Posted @ 7/21/2004 7:29 AM I believe I read somewhere the sp_executesql will compile the dynamic query and create an execution plan for it. This may speed this up a bit if the same exact query is executed. * # Taking the value of PageCount Resa Posted @ 7/27/2004 1:59 AM Excuse me but i have never used a stored procedure before, and although your code works great (thanks a lot by the way :)) i want to ask how can i get the PageCount value from the second table generated by ur stored procedure? Thank you very much in advance :) * # re: Sorting and Paging Recordsets in SQL Server Paul Wilson Posted @ 7/27/2004 5:59 AM Assuming you're using .NET and a DataReader then the NextResult method is what you want. If you are using a .NET DataSet then you will look at the second table, i.e. .Tables[1]. * # re: Sorting and Paging Recordsets in SQL Server Resa Posted @ 8/2/2004 2:06 AM I'm using sql server, and the NextResult method doesn't work, is there another way, i mean using sql server? thanks Tag: ExecuteNonReader hangs Tag: 97709
  - 2
    - Linebreaks in SQLCommand.CommandText (C#) Hi, From a C# application I read the SQL text from a text file. The text file contains line breaks that need to be conserved. The text file is read by a streamreader and added to a StringBuilder object. Each line in the text file is read, one at the time. After reading a line, I add a carriage return/linefeed by System.Enviroment.NewLine. But carriage returns / linefeeds are added to the StringBuilder as "/r/n". When I add the content of the StringBuilder object to the SqlCommand.CommandText property and run the query, the SQL fails. I have done a similar thing in VB. In VB I added carriage returns/linefeed by the "vbCrLf" constant. This worked fine. Line breaks were added and maintained when passing the SQL to the SqlCommand.CommandText property - no codes were "visible". I hope someone knows how to handle this in C#. Thanks, Dorte Tag: ExecuteNonReader hangs Tag: 97705
  - 3
    - insert an image to sql server avi_livni@hotmail.com in msn messenger hi i try to insert an image to sqlserver i do like this first i wrote a stored procedure in my sql server in database="new_home" (not in master db) : CREATE PROCEDURE sp_textcopy ( @srvname varchar (30), @login varchar (30), @password varchar (30), @dbname varchar (30), @tbname varchar (30), @colname varchar (30), @filename varchar (30), @whereclause varchar (40), @direction char(1)) AS DECLARE @exec_str varchar (255) SELECT @exec_str = 'textcopy /S ' + @srvname + ' /U ' + @login + ' /P ' + @password + ' /D ' + @dbname + ' /T ' + @tbname + ' /C ' + @colname + ' /W "' + @whereclause + '" /F ' + @filename + ' /' + @direction EXEC master..xp_cmdshell @exec_str this stored procedure i add to : db="new_home" table="image" now i only try to give parmeters in asp.net in oledb , i saw the example : sp_textcopy@srvname = 'ServerName', @login = 'Login', @password = 'Password', @dbname = 'pubs', @tbname = 'pub_info', @colname = 'logo', @filename = 'c:\picture.bmp', @whereclause = " WHERE pub_id='0736' ", @direction = 'I' but i do not now where i write this code and how i write it asp.net instead i write this, but i didnot see any new image in my database : <%@ Import Namespace="System.Data.SQLClient" %> <%@ Import Namespace="System.Data.OLEDB" %> <%@ Page Language="VB" Debug="true" %> <script language="VB" runat="server"> sub Page_Load(obj as Object, e as EventArgs) 'set up connection dim Conn as new OleDbConnection _ ("Provider=SQLOLEDB;" & _ "Data Source=D:\Program Files\Microsoft SQL Server\MSSQL$LOVE\Data\new_home_Data.MDF") dim obdCmd as OleDbCommand = new OleDbCommand("sp_textcopy",Conn) obdCmd.CommandType = CommandType.StoredProcedure dim objParam as OleDbParameter objParam =obdCmd.Parameters.Add("@srvname", OleDbType.Char) objParam.Direction=ParameterDirection.Input objParam.Value="AAA\LOVE" objParam =obdCmd.Parameters.Add("@login", OleDbType.Char) objParam.Direction=ParameterDirection.Input objParam.Value="" objParam =obdCmd.Parameters.Add("@password", OleDbType.Char) objParam.Direction=ParameterDirection.Input objParam.Value="" objParam =obdCmd.Parameters.Add("@login", OleDbType.Char) objParam.Direction=ParameterDirection.Input objParam.Value="" objParam =obdCmd.Parameters.Add("@dbname", OleDbType.Char) objParam.Direction=ParameterDirection.Input objParam.Value="new_home" objParam =obdCmd.Parameters.Add("@ltbname", OleDbType.Char) objParam.Direction=ParameterDirection.Input objParam.Value="image" objParam =obdCmd.Parameters.Add("@colname", OleDbType.Char) objParam.Direction=ParameterDirection.Input objParam.Value="image" objParam =obdCmd.Parameters.Add("@filename", OleDbType.Char) objParam.Direction=ParameterDirection.Input objParam.Value="c\10.jpg" objParam =obdCmd.Parameters.Add("@whereclause", OleDbType.Char) objParam.Direction=ParameterDirection.Input objParam.Value="where id=1" objParam =obdCmd.Parameters.Add("@direction", OleDbType.Char) objParam.Direction=ParameterDirection.Input objParam.Value="i" try obdCmd.Connection.Open() dim objReader as OleDbDataReader objReader = obdCmd.ExecuteReader catch ex as OleDbException response.write("Error retriving Data") obdCmd.Connection.Close() end try end sub </script> *-----------------------* Posted at: www.GroupSrv.com *-----------------------* Tag: ExecuteNonReader hangs Tag: 97704
  - 4
    - How to clear and refill a dataset dragged from the toolbox How to clear and refill a dataset dragged from the toolbox If I generate a dataset from a dataadapter I can ds.clear da.fill(ds, "Table") How do I do it otherwise? Thanks, Dennis Tag: ExecuteNonReader hangs Tag: 97697
  - 5
    - Can't create a new dataset from the toolbox Can't create a new dataset from the toolbox. In the past, on the advice of MVPs, I've created datasets by dragging the dataset component from the datatab of the toolbox onto the form. The first time it created dataset1, a dataset that did not exist before. The second time it created a dataset2. Today I dragged a dataset on a new form. It only offered current datasets, not a default of a new dataset. When I typed in tfsNet2.dataset3 and clicked OK, I got an error message Visual Studio cannot create an instance of TFSMet2.Dataset3. Confirm the class name is a valid dataset class. What happened? Please help. Dennis Tag: ExecuteNonReader hangs Tag: 97695
  - 6
    - Q: Copying a table from one database to another Hi Can anybody tell me the easiest way in ADO.NET to copy a table from one Access database to another Access Database? Some example code would be useful. Thanks in advance Geoff Tag: ExecuteNonReader hangs Tag: 97694
  - 7
    - SQAL question Sorry, but I didn't find a group for SQL questions. May be someone can answer here. In my app I want to reflect a hirarchy in a tree view. Therefore in the database I have a table like this: HirachyId (primary key) ParentHirarchyId (key to reflect the arent entry) CategoryId (a foreign key into a category table) Content may be like the following (0 is root hirarchy) 1 0 10 2 0 11 3 1 16 4 1 8 5 3 27 6 3 28 0 +-- 1 | +-- 3 | | +-- 5 | | +-- 6 | | | +-- 4 | +-- 2 Now I want a SQL-select statement which "recursive" gives me all records for a given node, i.e., when the node is "1" I want to get the records 1 0 10 3 1 16 4 1 8 5 3 27 6 3 28 Can this be done with a SQL-statement? -- Sleepless in Berlin Wernfried Tag: ExecuteNonReader hangs Tag: 97693
  - 8
    - finding records and displaying it in grid Hi, Earlier i asked a question regarding finding a record through ado.net. I found a solution for it through dataview. I have another question now. Supposing i have a table, 'contacts' whose fileds are like this : Contactid(autoname), customerid(FK from another table), contactname(char)... Here customerID is a foreignkey from tables 'Customers'. Since names might repeat in this table, how do i list the names when a search is made on this name. I want to display the fields displayed in row. I tried with findrowview, but could not succeed. Would someone help me pls. K E Senthil Kumar Tag: ExecuteNonReader hangs Tag: 97689
  - 9
    - How te reference to a datagrid in a class? Hi I've a DLL project with a form in it. At that form i want to add a datagrid, but datagrid is not recognized if i type dim db as new Datagrid. In a windows forms project it works correct, but not in the DLL project. What do i wrong????? Peter Tag: ExecuteNonReader hangs Tag: 97686
  - 10
    - Convert <table> from HTML text file to datatable I'm just wondering if anyone have come across a code that reads an html file,search for <table> tag and convert its contents into a datatable and save it into a dataset.If there are few html tables,then each will be converted into a datatable and added into the same dataset. thanks. Tag: ExecuteNonReader hangs Tag: 97678
  - 11
    - Passing Parameters through a linked server? Hello I have a stored proc: CREATE PROCEDURE dbo.sp_test @IN int, @ID int OUTPUT AS begin SET NOCOUNT ON Set @ID=12345 + @IN end GO I'm executing this through a linked server. The exception returned is: Could not execute procedure 'sp_test' on remote server 'QAPS2Remote'.\r\n[OLE/DB provider returned message: Parameter type cannot be determined for at least one variant parameter.] The literal query being run is: declare @P1 int set @P1=NULL exec QAPS2Remote.HRXMLCollector_prod.dbo.sp_test @IN = 1, @ID = @P1 output select @P1 How do I declare the parameters using C#? The code I'm using is: dbCommand.Connection = conn; dbCommand.CommandType = CommandType.StoredProcedure; dbCommand.CommandText = "QAPS2Remote.HRXMLCollector_prod.dbo.sp_test"; SqlParameter sqlParameterIn = dbCommand.Parameters.Add("@IN", SqlDbType.Int); sqlParameterIn.Direction = ParameterDirection.Input; sqlParameterIn.Value = 1; SqlParameter sqlParameterOut = dbCommand.Parameters.Add("@ID",SqlDbType.Int); sqlParameterOut.Direction = ParameterDirection.Output; dbCommand.ExecuteNonQuery(); Thanks in advance. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! Tag: ExecuteNonReader hangs Tag: 97670
  - 12
    - Paging a DataGrid or similar control...? I have a windows application that returns 10s of thousands of rows to a DataGrid (for all intents and purposes you can assume that it's a DataGrid, I'm actually using the Infragistics UltraWinGrid though). Due to the ever growing amount of data I want to implement some sort of record paging. There are a lot of examples for ASP.Net but not many for windows applications. I am planning to use some of the techniques used for ASP.Net, but I was hoping someone out there may have a few suggestions specifically for a windows application. Also, I am using MSDE as my database backend. Thanks Tag: ExecuteNonReader hangs Tag: 97661
  - 13
    - Hello? Is anyone reading these? I'm waiting for an answer, and I'm not the only one... There are quite a few unanswered posts in this NG. What do you say you get busy answering these posts before we choose another development platform! I'll keep watching... Tag: ExecuteNonReader hangs Tag: 97659
  - 14
    - Parameters in .NET to OPENROWSET Hello. Given the stored proc: CREATE PROCEDURE dbo.sp_test @IN int, @ID int OUTPUT AS begin SET NOCOUNT ON Set @ID=12345 + @IN end GO What I can't figure out is how to add an incoming parameter in .NET. My attempts thus far, which produces an exception on the @1 parameter: SqlConnection conn = null; SqlCommand dbCommand = null; SqlDataReader dbReader = null; conn = new SqlConnection(settings["LocalDBConnect"]); conn.Open(); dbCommand = new SqlCommand(); dbCommand.Connection = conn; dbCommand.CommandText = "SELECT outparam FROM OPENROWSET('MSDASQL','DRIVER={SQL Server};SERVER=PS_SQL2000;UID=collector;PWD=co11ection$$', 'declare @outparam int exec HRXML_DEVIII.dbo.sp_test @1,@outparam OUTPUT select @outparam as outparam')"; dbCommand.Parameters.Add("@1", SqlDbType.Int).Value = 1; dbReader = dbCommand.ExecuteReader();//general exception happens here int n; dbReader.Read(); n = dbReader.GetInt32(0); *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! Tag: ExecuteNonReader hangs Tag: 97655
  - 15
    - Multi threading and database connections Hi, What is the best approach for creating database connections while using threading to execute multiple queries in .NET? For eg: I need to run 15 queries using threading from the same database with minimal number of database connections. Any suggestion is appreciated. Thanks, Marshal Antony Tag: ExecuteNonReader hangs Tag: 97649
  - 16
    - Can't save a record again Hi All, I add a new row to a datatable and refresh the dataset. The data is stored. When i edit the row again and refresh the dataset again i get an error telling me the record was edited. probably the database is setting defaultvalues but i have no default values set (i think)! Could someone tell me how to set the properties for the fields in a Jet (access) database? By the way: The checkbox 'Refresh the dataset' in the data adaptor advanced settings is disabled. If i uncheck 'use optimistic concurrency' the error does not occur but i don't want to do that. with kind regards, rob Tag: ExecuteNonReader hangs Tag: 97646
  - 17
    - Filling typed dataset programmatically? I've created a strongly typed dataset (to be used to populate a crystal report) which I need to fill programmatically. How do I do this? Regards, Chris Tag: ExecuteNonReader hangs Tag: 97641
  - 18
    - ADO.NET 'HasMoreRows' and 'HasMoreResults' I am writing an ADO.NET C# com control wrapper for the old VBSql control used in an large application we have. The wrapper emulates the VBSql controls interface and is intended to allow us to move to SQL2000 from 6.5 without resecting the application and re-writing all the logic within the application to ADO. ************************************* The call semantics for VBSql are as follows set sql command call exec for command call results to check for data arrival. Call One while call results != noMoreResults Call Two+ while call nextRow != noMoreRows loop nextRow loop results ************************************* The problem is that because we have to call results once initially for each call to exec, I need to skip the initial call in the .NET code to DataReader.NextResults(). Then the second call comes in and I need to skip it again as I am not ready at this point to move to the next result set. Then the third call comes in and now, I am ready to forward the results set and return nomoreresults or success if there is a second result set. In order for me to do this, I need to keep a counter on the call to DataReader.NextResults() and only actually call NextResults on the third call. But this causes problems when I use a SQL insert command as I just want to know if the command was successful on the first call, and when I want to skip any remaining records in the batch and move onto the next batch. It would solve my problem to be able to know if I have rows or results remaining without calling NextResults() or Read() as they always move the cursors(or whatever) on the sql side. There are two undocumented, private Boolean properties on the Reader I would like access to. They are 'HasMoreRows and 'HasMoreResults'. I have not been able to find a way to get around the 'private' access modifer on these properties as my old trick of "#define private public" (crazy I know, but sometimes it is the only way to move forward in a timely fashion) is not available in .NET to my knowledge. I am sure someone else has had issues regarding the inability to check state of the reader without moving record or result pointers in the process. To check if I have more rows, I have to call read, or the hasRows. But hasRows returns true if I have another result set pending and thus need to call NextResults. So, I have to mangle the NextRow method to include a call to NextResults when the Read method returns false so the next call to the vbsqls results method will return the correct state without actually calling NextResults in the vbsql results stub in the wrapper. Thanks, and hopefully that is legible! Tag: ExecuteNonReader hangs Tag: 97640
  - 19
    - RowUpdatingEventArgs support in CF? According to Oct 2004 MSDN, all RowUpdatingEventArgs properties are supported in the Compact Framework but when I tried to access RowUpdatingEventArgs.StatementType I got a MissingMethodException! The same code works fine on the desktop. Also none of the overloaded versions of DataRow.Item are supported in the Compact Framework. There is therefore no way to access different version of a row under the compact framework. How would one access the original version of a deleted row? Thanks, Philippe Tag: ExecuteNonReader hangs Tag: 97639
  - 20
    - Binding Dropdown to dataset. Need help filtering data. ASP.NET web form application. I am currently binding a datatext field to a dropdown list with the following code: Sub FillFilterLists(ByVal ApplicantActivityDataset As DataSet) ApplicantActivityFilterPanel.ddAppActivityCompanyFilter.DataSource = ApplicantActivityDataset ApplicantActivityFilterPanel.ddAppActivityCompanyFilter.DataTextField = "Company" ApplicantActivityFilterPanel.ddAppActivityCompanyFilter.DataBind() End Sub This is working fine for simply populating the control. Currently my dataset has redundant data. (I'd like to stay away from a data normalization conversation for right now). I am filling the dataset in an earlier routine, and I cannot issue a "select distinct" at the SQL level. Is there anyway I can perform an equivilent function in .NET before the dropdown list is bound? Thanks, Aaron Tag: ExecuteNonReader hangs Tag: 97637
  - 21
    - SqlException I've got a script that sets up a publisher on a msde database. Runs fine from Query Analyzer. I run it from my program and get two warnings (class 10 sqlExceptions). The second warning causes problems. When the script is executed from the application the second warning occurs and the sql process gets hung in a networkio wait type. When I step through the application, I see the second warning occur, continue to step through and everything works fine (no networkio wait type). I've tried inserting pauses, disposing of the sqlCommand object and recreating it, closing and re-opening the sqlConnection when these warnings occur but nothing seems to change the networkio wait type when run from the application (not stepping through). Anyone seen this before? Have any ideas what else I can try? TIA, Dave Tag: ExecuteNonReader hangs Tag: 97635
  - 22
    - Visual FoxPro driver question Hello all, I have written an application that accesses and writes to an old dBase file via a FoxPro driver (vfpoledb.1). It works fine when I try to access a file that exists on my local computer. However, if I try to access a file that exists on a network, I always get OleDbException "Invalid path or file name". Here is my connection string: oleConnection = new OleDbConnection( "Provider=vfpoledb.1;Data Source=" + configuration.BCSHAREDDir); where configuration.BCSHAREDDir is a user configured folder. There is no username or password to be used for this table. If I use the folder "C:\bcshared.dbf" for instance, it works fine. However if I try to a network folder such as "\\rcfs01\rcfs01data\Jason\Software\bcshared.dbf", it craps out. Any ideas as to what I am doing wrong? Thank you! Jason Tag: ExecuteNonReader hangs Tag: 97633
  - 23
    - DataSet - DataColumn MaxLength If I create a DataSet in the Visual Studio IDE, how do I specify the MaxLength of a DataColumn? Once I specify the MaxLength, I want to bind the DataSet to controls on my Windows Form (TextBox controls). Will the MaxLength of the bound TextBox automatically sync with the bound DataColumn? If not, how do I sync the two? Thanks Tag: ExecuteNonReader hangs Tag: 97626
  - 24
    - Access database, network access problems I've recently run into a problem when creating a web service. I have a webserivce that returns data from a small access database; I created and tested the service on my local machine with the database local in a sub-directory. I verified the results on a test server remotely with the database stored on the local disk. The production server is a web farm, a SAN provides all data storage for this farm; This is where I am having problems, I'm no longer able to open the database, the permissions have been validated on the remote share and the user account has access? My connection string "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=\\server\share\folder\folder\folder\database.mdb;" I have also tried using the extended property Mode="Share Deny None" The mappath function is used to get the actual file path. It points to the correct location, but, I get errors that the database cannot be opened because you don't have permissions or the database is being opened exclusively, or something to that nature. I don't have recordlocking enabled and a .ldb file is never created. Any suggestions? If you need more information please let me know. Tag: ExecuteNonReader hangs Tag: 97621
  - 25
    - how to correctly map multiple datasource tables to typeddataset ta I foolishly thought that if.. 1. I have a typed dataset built from my data source schema (that is, all the table names and all their column names, and types, match), and 2. I fill an instance of the dataset using a dataadapter, then the table names in the dataset would be correct (i.e. matching data source table names). I found that they are named Table, Table1, Table2, etc, and that to override this I need to set up table mappings for these default table names (which are dependent on the order of tables in the query text!). Is there a correct way to do this, or is the proper technique simply to map the default table names!? Tag: ExecuteNonReader hangs Tag: 97607
- Next
  - 1
    - Inheritance VB.NET Hi guys, Wonder if anyone can help a newbie in VB.net. I've done a substantial amount of work in VB6 and to promote code use, I lent some methodology from some other OOP RAD tools - but I'm having a probem figuring out an approach in VB.Net... In VB6, I would, for each database table, create a 'class module', which contained all of the necessary database functions I required. So, e.g. I would have a class module called 'tcProvinceState', which would handle all of my database IO for the ProvinceState table. In each class module, I would have functions such as 'DoSelect', 'DoSelectRow', 'InsertRow', 'UpdateRow' and 'DeleteRow'. Here are some examples of the type of thing I currently do; Public Function Do_Select(pSearchString As String) As ADODB.Recordset ' Select all records from the table sSQL = Do_Select_Body(pSearchString) 'Get the body sSQL = sSQL & " ORDER BY PS.PROVINCE_STATE_NAME" Set Do_Select = clsSQL.Perform_SQL(sSQL) End Function Private Function Do_Select_Body(ByRef pSearchString As String) As String ' Get the select body sSQL = "SELECT" sSQL = sSQL & " PS.ID" sSQL = sSQL & ", PS.CODE" sSQL = sSQL & ", PS.PROVINCE_STATE_NAME" sSQL = sSQL & ", PS.COUNTRY" sSQL = sSQL & ", PS.GMT_ZONE" sSQL = sSQL & ", PS.TAX_GROUP_ID" sSQL = sSQL & ", TG.CODE AS TAX_GROUP_CODE" sSQL = sSQL & ", TG.DESCRIPTION AS TAX_GROUP_DESCRIPTION" sSQL = sSQL & ", PS.FREIGHT_ADJUSTMENT" sSQL = sSQL & ", PS.RECORD_CREATED" sSQL = sSQL & ", PS.RECORD_UPDATED" sSQL = sSQL & ", PS.USER_ID" sSQL = sSQL & ", EL.USER_NAME AS EMPLOYEE_USER_NAME" sSQL = sSQL & ", EL.FIRST_NAME AS EMPLOYEE_FIRST_NAME" sSQL = sSQL & ", EL.LAST_NAME AS EMPLOYEE_LAST_NAME" sSQL = sSQL & " FROM ((" sSQL = sSQL & "PROVINCE_STATE AS PS" sSQL = sSQL & " LEFT JOIN EMPLOYEE_LOGIN AS EL ON PS.USER_ID = EL.ID)" sSQL = sSQL & " LEFT JOIN TAX_GROUP AS TG ON PS.TAX_GROUP_ID = TG.ID)" sSQL = sSQL & " WHERE 1=1" Build_Search pSearchString Do_Select_Body = sSQL End Function Public Function Insert_Row(ByRef rsRow As ADODB.Recordset, pID As Long) As Boolean ' Insert row into the table; Dim sErrors As String Dim sDate As Variant sDate = Format(Now(), "d-mmm-yyyy") ' Use Stored Procedure (SQL Server 2000) ' Initialize the input & output parameters; Set cStoredProcedure = New ADODB.Command Set cParam = New ADODB.Parameter With cStoredProcedure Set cParam = .CreateParameter("@CODE", adVarChar, adParamInput, 4, rsRow.Fields("CODE")) .Parameters.Append cParam Set cParam = .CreateParameter("@PROVINCE_STATE_NAME", adVarChar, adParamInput, 30, rsRow.Fields("PROVINCE_STATE_NAME")) .Parameters.Append cParam Set cParam = .CreateParameter("@COUNTRY", adVarChar, adParamInput, 20, rsRow.Fields("COUNTRY")) .Parameters.Append cParam Set cParam = .CreateParameter("@GMT_ZONE", adSmallInt, adParamInput, 5, rsRow.Fields("GMT_ZONE")) .Parameters.Append cParam Set cParam = .CreateParameter("@TAX_GROUP_ID", adInteger, adParamInput, 8, rsRow.Fields("TAX_GROUP_ID")) .Parameters.Append cParam Set cParam = .CreateParameter("@FREIGHT_ADJUSTMENT", adCurrency, adParamInput, 8, rsRow.Fields("FREIGHT_ADJUSTMENT")) .Parameters.Append cParam Set cParam = .CreateParameter("@USER_ID", adInteger, adParamInput, 8, glngUserID) .Parameters.Append cParam Set cParam = .CreateParameter("@ID", adInteger, adParamOutput, 8, pID) .Parameters.Append cParam sSQL = "SP_PROVINCE_STATE_INSERT" clsSQL.Perform_StoredProcedure sSQL, cStoredProcedure, sErrors End With ' Check Errors If Len(sErrors) Then Display_Errors "Insert Failed", sErrors Insert_Row = False Exit Function End If ' Read any output parameters pID = cStoredProcedure.Parameters("@ID").Value Insert_Row = True End Function With this method, creating a new table and its related 'class' is quite simple. Now, although this methodology doesn't have true inheritance, I get some code re-use and it provided a very good platform for my DB applications. However, I am now somewhat confused with regards to VB.Net. .Net would appear to give me some native functionality for things such as selects, inserts and deletes. I can see how inheritance may truly help me by allowing me to create a 'master class', but I'm not sure how I would implement this? - Should I be looking at writing similar code? or does .Net offer me another option? Ideally I would like to create a visual class such as a dataset - but from what I can see - this has to be copied from form to form as required - not inherited. Anyway, if anyone has any suggestioms, or can point me in the right direction, I'd very much appreciate it. All the best, Graham Tag: ExecuteNonReader hangs Tag: 97605
  - 2
    - Recommended OleDB driver for MySQL? I am accessing MySQL from VS 2003, C# and ADO.NET, using the OleDb interfaces (not ODBC) and have been using (successfully) an OLE DB driver on: http://www.mysql.com/downloads/download.php?file=Downloads/Win32/MyOLEDB3.exe&pick=mirror This driver is called "My OLEDB 3.0, OLE Provider for MySQL, February 20, 2001". I am actually amazed that anything written in 2001 still works with the recent MySQL 4.1 ... amazed - and worried that some of the issues I am working through may be caused by some incompatibilies ... Should I be using a more recent driver? I see mysql-connector-net-1.0.0-beta.zip on www.MySQL.com but this package doesn't contain any EXE (and I don't whether one can be built from the included file or how). I am not even sure this is an OLE DB provider, it may be an ODBC provider. In fact, considering how new I am to MySQL, I am not even sure that *any* MySQL OLE DB provider is required: the Windows System Requirements page (http://dev.mysql.com/doc/mysql/en/Windows_system_requirements.html) does not even mention the need to install anything before proceeding with the MySQL installation (except for those planning to connect via ODBC). Thanks! Patrick Tag: ExecuteNonReader hangs Tag: 97598
  - 3
    - DataGrid boundcolumn problem I want to use a datagrid with my display, but I don't want to use 1 row per record. What I have is a record with up to 6 values - (answer1,answer2,answer3,answer4,answer5,answer6). I want to display them with alternating styles. One record may only have the first 3 fields with data and another with 4 and still another with 6. I want it to show as: Answer1 Answer2 Answer3 or Answer1 Answer2 Answer3 Answer4 Answer5 Can I set up a DataGrid this way? Thanks, Tom Tag: ExecuteNonReader hangs Tag: 97597
  - 4
    - Select datagrid rows with key-up and key-down? I have an application that uses a windows forms datagrid. How do I select rows using the up-arrow and down-arrow keys? Mervin Williams Tag: ExecuteNonReader hangs Tag: 97592
  - 5
    - DataTable.Add(row) and fired events I have a situation where a method listens to the RowChanged event for a datatable. The problem I having is this.. The RowChanged event handler calls another method, passing in the e.Row["ID_VALUE"] of the row that fired the event. In the case where this is a .Add(dr) that has caused the RowChanged event to fire.... the row that is referenced in e.Row (event arg for the RowChanged event handler) is not actually in the table yet. If I do a dt.Select() or a dt.Rows.Count,,, it is obvious that the row firing the event has not actually been added to table yet. Why is this happening? Is this a bug? Why would the row fire the table event and not be in the Rows collection for the table??? Tag: ExecuteNonReader hangs Tag: 97584
  - 6
    - Opening an acess database I have been programming in years in VVB-5-6 but can anyone help in the syntax in how to open an access database with a password and how to get a recordset from it as I am only used to the DAO method and this does not seem to be used in .net -- Clifford Sutton Tag: ExecuteNonReader hangs Tag: 97575
  - 7
    - Newbie: Detecting type of field contents in datareader This is a multi-part message in MIME format. ------=_NextPart_000_0059_01C4B5CD.2DB31250 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, I have a few examples and things are quite clear with using a datareader = (reading data, naming column headers etc.), and populating a listview. However: (!!!) In all of the examples we know beforehand the number of = fields and therefore the data type (Int, Date, String etc) since the = SELECT string is constant. My problem is with variable strings that get constructed at runtime. = Then, since i do not know beforehand what exactly is my first,second, = etc. field, I CANNOT use : myDataReader.GetString(0) or myData.GetInt32(0),etc. since io do not = know the type beforehand. Is there a way around this? I really dont mind getting them all as text = so is the an CType analogous method I could use . Something like casting = BEFORE reading ?? ANY help would be appreciated, Thanx -steve ------=_NextPart_000_0059_01C4B5CD.2DB31250 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META http-equiv=3DContent-Type content=3D"text/html; = charset=3Diso-8859-1"> <META content=3D"MSHTML 6.00.2800.1476" name=3DGENERATOR> <STYLE></STYLE> </HEAD> <BODY> <DIV><FONT face=3DArial size=3D2>Hi,</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>I have a few examples and things are = quite clear=20 with using a datareader (reading data, naming column headers = etc.), and=20 populating a listview.</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>However: (!!!) In all of the examples = we know=20 beforehand the number of fields and therefore the data type (Int, Date, = String=20 etc) since the SELECT string is constant.</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>My problem is with variable strings = that get=20 constructed at runtime. Then, since i do not know beforehand what = exactly is my=20 first,second, etc. field, I CANNOT use :</FONT></DIV> <DIV> <P><FONT face=3DArial size=3D2>myDataReader.GetString(0) or = myData.GetInt32(0),etc.=20   since io do not know the type beforehand.</FONT></P> <P><FONT face=3DArial size=3D2>Is there a way around this? I really dont = mind=20 getting them all as text so is the an CType analogous method I could use = .=20 Something like casting BEFORE reading ??</FONT></P> <P><FONT face=3DArial size=3D2>ANY help would be appreciated,</FONT></P> <P><FONT face=3DArial size=3D2>Thanx</FONT></P> <P><FONT face=3DArial size=3D2>-steve</FONT></P> <P><FONT face=3DArial size=3D2></FONT> </P></DIV></BODY></HTML> ------=_NextPart_000_0059_01C4B5CD.2DB31250-- Tag: ExecuteNonReader hangs Tag: 97570
  - 8
    - Syntax error in UPDATE statement - field name "Upper", or "Lower" I am getting "Syntax error in UPDATE statement" if one of the fields in a table of Access database (2000, or 97) has the name "Upper" or "Lower". As soon as I change the name of the field to something else, the code works fine. I looked through the list of reserved names, and I could not find the words "Upper" and "Lower". In order to reproduce this behavior, please create a database "Lookup.mdb" with one table "Test" which contains 2 fields of type "Text". The name of one of the fields should be "Upper", or "Lower". Populate the table with any entries you want. After that create a Windows Forms app and add a Windows Form. Use the code of the Windows Forms class Update1.vb below. Change the constant LOOKUP_DBPATH appropriately. Run the app. Click the button "Get Lookup Test". The datagrid should be populated with values from table "Test". Edit the content of a cell under the filed "Upper" or "Lower". Click the button "Update Lookup Test". An exception should be raised. You may want to set breakpoints in the routines "OnRowUpdating" and "OnRowUpdated". The environment is: Visual Studio 2003; Windows XP Prof; Windows Forms App; Microsoft Access 2000 database. ================================= The code: #Region " Imports " Imports System Imports System.Data Imports System.Data.OleDb #End Region Public Class Update1 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 grdLookup As System.Windows.Forms.DataGrid Friend WithEvents btnGetLookup As System.Windows.Forms.Button Friend WithEvents btnUpdateLookup As System.Windows.Forms.Button <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent() Me.grdLookup = New System.Windows.Forms.DataGrid Me.btnGetLookup = New System.Windows.Forms.Button Me.btnUpdateLookup = New System.Windows.Forms.Button CType(Me.grdLookup, System.ComponentModel.ISupportInitialize).BeginInit() Me.SuspendLayout() ' 'grdLookup ' Me.grdLookup.DataMember = "" Me.grdLookup.HeaderForeColor = System.Drawing.SystemColors.ControlText Me.grdLookup.Location = New System.Drawing.Point(6, 8) Me.grdLookup.Name = "grdLookup" Me.grdLookup.Size = New System.Drawing.Size(346, 166) Me.grdLookup.TabIndex = 0 ' 'btnGetLookup ' Me.btnGetLookup.Location = New System.Drawing.Point(370, 10) Me.btnGetLookup.Name = "btnGetLookup" Me.btnGetLookup.Size = New System.Drawing.Size(114, 22) Me.btnGetLookup.TabIndex = 1 Me.btnGetLookup.Text = "Get Lookup Test" ' 'btnUpdateLookup ' Me.btnUpdateLookup.Location = New System.Drawing.Point(370, 40) Me.btnUpdateLookup.Name = "btnUpdateLookup" Me.btnUpdateLookup.Size = New System.Drawing.Size(114, 22) Me.btnUpdateLookup.TabIndex = 2 Me.btnUpdateLookup.Text = "Update Lookup Test" ' 'Update1 ' Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13) Me.ClientSize = New System.Drawing.Size(492, 181) Me.Controls.Add(Me.btnUpdateLookup) Me.Controls.Add(Me.btnGetLookup) Me.Controls.Add(Me.grdLookup) Me.Name = "Update1" Me.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen Me.Text = "Update1" CType(Me.grdLookup, System.ComponentModel.ISupportInitialize).EndInit() Me.ResumeLayout(False) End Sub #End Region <STAThread()> Public Shared Sub Main() Application.Run(New Update1) End Sub Private m_Conn As OleDbConnection Private m_openedConnection As Boolean Private m_sql As String Private m_adap As OleDbDataAdapter Private m_build As OleDbCommandBuilder Private m_dt As DataTable Const conAccess As String = "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;" Const conAccess1 As String = "Data Source=" Const LOCALDB_PATH = "E:\SMW\TestUpdates\Lookup.mdb" Private Sub Update1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load Try m_openedConnection = OpenConnection(m_Conn) Catch ex As Exception Debug.WriteLine(ex.Message) End Try End Sub Private Sub Update1_Closing(ByVal sender As Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles MyBase.Closing Try Call CloseConnection(m_Conn) Catch ex As Exception Debug.WriteLine(ex.Message) End Try End Sub Private Sub btnGetLookup_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGetLookup.Click Dim gotLookup As Boolean Try m_sql = "SELECT * FROM Test" m_dt = New DataTable m_dt.TableName = "Test" gotLookup = GetDataTableForEditAndUpdate(m_sql, m_Conn, m_adap, m_dt) grdLookup.Text = "Test" grdLookup.DataSource = m_dt AddHandler m_adap.RowUpdating, _ New OleDbRowUpdatingEventHandler(AddressOf OnRowUpdating) AddHandler m_adap.RowUpdated, _ New OleDbRowUpdatedEventHandler(AddressOf OnRowUpdated) Catch ex As Exception Debug.WriteLine(ex.Message) End Try End Sub Private Sub btnUpdateLookup_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdateLookup.Click Dim updated As Boolean Try updated = UpdateLookupTableBackToLocalDB() Catch ex As Exception Debug.WriteLine(ex.Message) End Try End Sub Public Function OpenConnection(ByRef conn As OleDbConnection) As Boolean Try If conn Is Nothing Then conn = New OleDbConnection conn.ConnectionString = conAccess & conAccess1 & LOCALDB_PATH End If Catch ex As Exception OpenConnection = False Debug.WriteLine(ex.Message) Exit Function End Try Try If conn.State = ConnectionState.Closed Then conn.Open() End If OpenConnection = True Catch ex As OleDbException Debug.WriteLine(ex.Message) OpenConnection = False End Try End Function Public Sub CloseConnection(ByRef conn As OleDbConnection) Try If Not conn Is Nothing Then If conn.State = ConnectionState.Open Then conn.Close() conn.Dispose() End If End If Catch ex As Exception Debug.WriteLine(ex.Message) Finally conn = Nothing End Try End Sub Public Function GetDataTableForEditAndUpdate(ByVal sql As String, _ ByVal conn As OleDbConnection, _ ByRef adap As OleDbDataAdapter, _ ByRef dt As DataTable) As Boolean Try adap = New OleDbDataAdapter(sql, conn) m_build = New OleDbCommandBuilder(adap) adap.FillSchema(dt, SchemaType.Source) adap.Fill(dt) GetDataTableForEditAndUpdate = True Catch ex As Exception Debug.WriteLine(ex.Message) GetDataTableForEditAndUpdate = False End Try End Function Private Function UpdateLookupTableBackToLocalDB() As Boolean Try Me.Cursor = Cursors.WaitCursor With m_adap .Update(m_dt.Select(Nothing, Nothing, DataViewRowState.Deleted)) .Update(m_dt.Select(Nothing, Nothing, DataViewRowState.ModifiedCurrent)) .Update(m_dt.Select(Nothing, Nothing, DataViewRowState.Added)) End With UpdateLookupTableBackToLocalDB = True Catch ex As Exception Debug.WriteLine(ex.Message) UpdateLookupTableBackToLocalDB = False Finally Me.Cursor = Cursors.Default End Try End Function Protected Sub OnRowUpdating(ByVal sender As Object, _ ByVal args As OleDbRowUpdatingEventArgs) Debug.WriteLine(args.Command.CommandText) If args.Errors Is Nothing Then ' Else Debug.WriteLine(args.Errors.Message) Debug.WriteLine(args.Errors.Source) End If Debug.WriteLine(args.Status.ToString) End Sub Protected Sub OnRowUpdated(ByVal sender As Object, _ ByVal args As OleDbRowUpdatedEventArgs) Debug.WriteLine(args.Command.CommandText) If args.Errors Is Nothing Then ' Else Debug.WriteLine(args.Errors.Message) Debug.WriteLine(args.Errors.Source) End If Debug.WriteLine(args.Status.ToString) End Sub End Class -- Igor Gegusin Tag: ExecuteNonReader hangs Tag: 97567
  - 9
    - OracleClient exception... Hey guys, I'm stumped on this... Today I was asked to look into coverting our ODBC access to a Oracle database to the client that microsoft wrote and have come up against the lovely "ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'MSG_DATAREAD' " message... All I'm doing is converting the code from the stuff that's using OdbcConnection/Command etc to OracleConnection/Command etc so I know the stored procedure's all work fine. Here's the code that's failing: ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ' get a reader for the record set with this msg number Dim dbcommand As OracleCommand = New OracleCommand dbcommand.Connection = dbData dbcommand.CommandType = CommandType.StoredProcedure dbcommand.CommandText = "MSG_DATAREAD" ' get a cursor out Dim param As OracleParameter = New OracleParameter param = dbCommand.Parameters.Add("RC1", OracleClient.OracleType.Cursor) param.Direction = ParameterDirection.Output ' pass in the msg number Dim msgnumParam As OracleParameter = New OracleParameter msgnumParam = dbcommand.Parameters.Add("MsgNumber", OracleType.VarChar, 50) msgnumParam.Direction = ParameterDirection.Input msgnumParam.Value = NullToBlank(m_szCurrentMsgNumber) Dim dbReader As OracleDataReader = dbcommand.ExecuteReader While dbreader.Read ' do some stuff with the data I get back End While dbreader.Close() ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Here's the Stored Procedure it's calling: /* Stored procedure MSG_DATAREAD ------------------------------------------- */ ( MsgNumber IN VARCHAR2 DEFAULT NULL, RC1 IN OUT Omwb_emulation.globalPkg.RCT1 ) AS MsgNumber_ VARCHAR2(50) := MsgNumber; StoO_selcnt INTEGER; StoO_error INTEGER; StoO_rowcnt INTEGER; StoO_crowcnt INTEGER := 0; StoO_fetchstatus INTEGER := 0; StoO_errmsg VARCHAR2(255); StoO_sqlstatus INTEGER; BEGIN NULL; /*[SPCONV-ERR(6)]:(set DATEFORMAT) Manual conversion required*/ OPEN RC1 FOR SELECT MsgNumber , MsgItem , Section , Field , Data FROM MSG_Data WHERE MsgNumber = MSG_DATAREAD.MsgNumber_ ORDER BY MsgItem ; END MSG_DATAREAD; Tag: ExecuteNonReader hangs Tag: 97559
  - 10
    - problem with the dataset update Hi, I have notice something with the datagrid update i have an application which is updated from multiple sources when one user deletes a row on the datagrid, the delete propgates back to the database. I see it is gone, from the database. but, when other users app calls it's fill command to refresh it's datatable, It sees the already deleted row. what am i doing wrong? Tag: ExecuteNonReader hangs Tag: 97556
  - 11
    - Setting Textbox to Null for DateTime I have a textbox on a windows form which is bound to a smalldatetime field in a Dataset. The problem is that sometimes this date should be deleted so that a null value gets written back to the database. However, if I delete this date and leave the Textbox the previous entry gets retrieved. Is there a way to prevent this from happening and leaving the textbox empty so that a null value gets written back to the database when update is called? Tag: ExecuteNonReader hangs Tag: 97551
  - 12
    - Date/Time in Access I'm having trouble storing the value of Now in an Access table. I'm using VB.NET 2002. I need to store a time value, but no matter what I do I end up getting just a date value in Access. I'm doing something like this: Dim NewRow as DataRow = MyTable.NewRow NewRow("InTime") = Now MyTable.Rows.Add(NewRow) DataAdaptor.Update(MyTable) The value of CDate(NewRow("InTime")) is correct until I refresh the dataset with data from the database. All I get is the correct date and a time of 12:00:00. I've tried using every possible format of Date/Time in Access, including my own custom format. Thanks for the help Tag: ExecuteNonReader hangs Tag: 97550
  - 13
    - Delete datarowview objects from a dataview Hi all, I have a problem when attempting to delete datarowview obecjts from a data view Dim mydv As New DataView Dim thisDRV As DataRowView mydv.Table = myDS.Tables("CountedItem") mydv.RowFilter = "ilID = '" & lID & "'" For Each thisDRV In mydv thisDRV.Delete() Next When I execute this code I get an IndexOutOfRangeException exception. However, if I print the first column of the datarowviews they all exist. Dim mydv As New DataView Dim thisDRV As DataRowView mydv.Table = myDS.Tables("CountedItem") mydv.RowFilter = "ilID = '" & lID & "'" For Each thisDRV In mydv debug.writeline(thisDRV(0)) Next Is there something funny happeneing with the deleting of a datarowview from a dataview? Is this the correct way to delete a subset of rows from a datatable? Many thanks, Jason. Tag: ExecuteNonReader hangs Tag: 97546
  - 14
    - Convert string to sqldbtype This is a multi-part message in MIME format. ------=_NextPart_000_0008_01C4B5C4.ECD8DFB0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Is there another easier way to convert a string to a sqldbtype than = using a select case, as in: Namespace ADOHelper Public Class sqldbtype Public Shared Function Convert(ByVal type As String) As = System.Data.SqlDbType Select Case type Case "int" Return System.Data.SqlDbType.Int Case "NVarChar" Return System.Data.SqlDbType.NVarChar Case "VarChar" Return System.Data.SqlDbType.VarChar Etc ......... ------=_NextPart_000_0008_01C4B5C4.ECD8DFB0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META http-equiv=3DContent-Type content=3D"text/html; = charset=3Diso-8859-1"> <META content=3D"MSHTML 6.00.2800.1458" name=3DGENERATOR> <STYLE></STYLE> </HEAD> <BODY> <DIV><FONT face=3DArial size=3D2>Is there another easier way to convert = a string to=20 a sqldbtype than using a select case, as in:</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV> <P><FONT face=3DArial size=3D2>Namespace ADOHelper<BR>    = </FONT><FONT face=3DArial size=3D2>Public Class = sqldbtype<BR>   =20     </FONT><FONT face=3DArial size=3D2>Public Shared = Function=20 Convert(ByVal type As String) As = System.Data.SqlDbType<BR>   =20         </FONT><FONT face=3DArial = size=3D2>Select Case=20 type<BR>           =20     </FONT><FONT face=3DArial size=3D2>Case=20 "int"<BR>           =20         </FONT><FONT face=3DArial = size=3D2>Return=20 System.Data.SqlDbType.Int<BR>       =20         </FONT><FONT face=3DArial = size=3D2>Case=20 "NVarChar"<BR>           =20         </FONT><FONT face=3DArial = size=3D2>Return=20 System.Data.SqlDbType.NVarChar<BR>       =20         </FONT><FONT face=3DArial = size=3D2>Case=20 "VarChar"<BR>           =20         </FONT><FONT face=3DArial = size=3D2>Return=20 System.Data.SqlDbType.VarChar<BR>      &nbs= p;         Etc=20 .........</FONT></P></DIV></BODY></HTML> ------=_NextPart_000_0008_01C4B5C4.ECD8DFB0-- Tag: ExecuteNonReader hangs Tag: 97544
  - 15
    - Populate SQL table with a dataset all in one It's possibile to populate a SQL table with a dataset without throw all rows in datatable but in one query? Thanks -- Filippo Macchi www.filippomacchi.it Tag: ExecuteNonReader hangs Tag: 97542
  - 16
    - How to use DACW with Scope_Identity for a usable DataSet? How do you use 'Data Adapter Configuration Wizard' (DACW) with an Insert stored procedure that uses 'Scope_Identity' and get a usable DataSet? When my Insert stored procedure looks like this: Note the [@@IDENTITY] =================================================== CREATE PROCEDURE dbo.CustomerInsert ( @FName char(10), @LName char(10) ) AS SET NOCOUNT OFF; INSERT INTO Customer(FName, LName) VALUES (@FName, @LName); SELECT ID, FName, LName FROM Customer WHERE (CustID = @@IDENTITY) GO =================================================== Then the Dataset created by the DACW results in the following properties for CustID: AllowDBNull = FALSE AutoIncrement = TRUE AutoIncrementSeed = 0 AutoIncrementStep = 1 ReadOnly = TRUE Unique = TRUE This results in a dataset that works for successfully adding records to the table. However? when my Insert stored procedure looks like this: Note the [SCOPE_IDENTITY] =================================================== CREATE PROCEDURE dbo.CustomerInsert ( @FName char(10), @LName char(10) ) AS SET NOCOUNT OFF; INSERT INTO Customer(FName, LName) VALUES (@FName, @LName); SELECT ID, FName, LName FROM Customer WHERE (CustID = SCOPE_IDENTITY()) GO =================================================== Then the Dataset created by the DACW results in the following properties for CustID: AllowDBNull = FALSE AutoIncrement = FALSE AutoIncrementSeed = 0 AutoIncrementStep = 1 ReadOnly = TRUE Unique = TRUE This results in a DataSet that CANNOT successfully add new records to the table. I get the following error: =========================================== Error when committing the row to the original data source ------------------------------------------------------- Column 'CustID' does not allow nulls. Do you want to correct the value? [Yes][No] =========================================== Tag: ExecuteNonReader hangs Tag: 97537
  - 17
    - A bug in Data Adapter Configuration Wizard (VB.Net for SQLS2k) == Description == VB.Net will unfaithfully represent primary keys when they are identified as an IDENTITY field for SQL Server tables. I will make these mistakes when producing Insert Stored Procedures and when producing DataSets. It will NOT make these mistakes when the field is 'ID', but it WILL create these mistakes when the field is named other than 'ID' for example 'CustID'. The mistakes in the Insert Stored procedure are: ? The id will not be recognized as an IDENTITY ? The id will be included as a parameter to be assigned a value by the user ? The id will be included in the insert into statement and the Values statement (as if a value will be assigned to it by the user) ? The id will be included in the select statement of the insert stored procedure ? The WHERE statement of the Insert stored procedure will have a parameter for the id equal to its parameter (CustID = @CustID) not (CustID = @@IDENTITY) The mistake in the DataSet is: ? The id field's AutoIncrement property is set to FALSE This mistake will be seen in the DataGrid: ? The ID will shown as NULL ? You will not be able to enter data into the table I can provide instruction on how to duplicate this error. Tag: ExecuteNonReader hangs Tag: 97536
  - 18
    - Datagrid column labels are clipped. Has anyone else had this problem? Right-justified column labels have the rightmost 1-2 pixels clipped off. So a label like "Ship Date" only shows half of the 'e'. Left-justified labels are displayed just fine. Thanks in advance. John John@nospamViridianTech.com Tag: ExecuteNonReader hangs Tag: 97535
  - 19
    - OracleClient and OCI.dll I'm getting this error when I run, but I'm able to connect and get data from adapter in design mode. What's my problem please. [DllNotFoundException: Unable to load DLL (oci.dll).] System.Data.OracleClient.DBObjectPool.GetObject(Object owningObject, Boolean& isInTransaction) System.Data.OracleClient.OracleConnectionPoolManager.GetPooledConnection(String encryptedConnectionString, OracleConnectionString options, OracleConnection owningObject, Boolean& isInTransaction) System.Data.OracleClient.OracleConnection.OpenInternal(OracleConnectionString parsedConnectionString, Object transact) System.Data.OracleClient.OracleConnection.Open() Filter.WebForm1.Page_Load(Object sender, EventArgs e) in c:\inetpub\wwwroot\aspnet_client\filter\webform1.aspx.cs:29 System.Web.UI.Control.OnLoad(EventArgs e) System.Web.UI.Control.LoadRecursive() System.Web.UI.Page.ProcessRequestMain() Tag: ExecuteNonReader hangs Tag: 97531
  - 20
    - MDAC -- error enumerating data servers I'm having a problem using the Database wizard in Visual Studio 2003 (Academic version) to connect in a very basic way (not even any code yet) to SQLEXPRESS, the only available version of SQL Server 2005 out so far. I was running MDAC 2.7 XP SP 1 on Windows XP Pro and thought that maybe pulling down a recent refresh for 2.7 might help (it didn't). So I went ahead and upgraded to 2.8 and still have the error message 'error enumerating data servers'. I know my databases are up and running because my management console tells me so. The whole environment is local to my machine and I don't think it's a permission problem. I'm wondering if maybe the fact that Studio runs on .NET framework 1.1 and SQL Server 2005 express is running on framework 2.x might be part of the problem but have no information so far that confirms for me that trying to run Visual Studio on framework 2.x will improve the situation. Component Checker was showing me just a few MDAC mismatches on 2.7 but 2.8 is showing heavy mismatches ... too many to mention and I couldn't say which ones are relevant anyway (but no change to the error message so if something else is broken I'm not suffering from it yet). When I search for oledb32.dll in the registry, I can't find it (though it is in my file system) so I'm having trouble figuring out how to register it if it isn't there now ... Many thanks for any help. -- thank you, Stephanie Tag: ExecuteNonReader hangs Tag: 97527
  - 21
    - problems with datatable update Hi, I have a datatable which is bound to a database table. the table has a primary key on 1 field and i am using the machine generated statements from the sql data adapter. here is the scenario two users are using different instances of the application if I use the dataset.fill command, i can propogate changes made by one user, to another user. however, if a user deletes one of the primary keys, and inserts another in it's place, the other users datatable has a phantom row. has anyone noticed this problem and what is a good workaround thanks Tag: ExecuteNonReader hangs Tag: 97525
  - 22
    - Bad variable type exception I have made this function to create a command object to be used to call a Stored Procedure: Public Overrides Function GetStoredProcedureCommandObject(ByVal cn As ADODB.Connection) As ADODB.Command '* Create a command objekt to call SP Dim cmd As New ADODB.Command() Dim prm As ADODB.Parameter cmd.ActiveConnection = cn cmd.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc cmd.CommandTimeout = 15 cmd.CommandText = "LEVE_Lev" prm = cmd.CreateParameter("nLev", ADODB.DataTypeEnum.adInteger, ADODB.ParameterDirectionEnum.adParamInput) MsgBox("Me.Lev=" & Me.Lev) prm.Value = Me.Lev cmd.Parameters.Append(prm) Return cmd End Function The parameter is declared as int, and Me.lev contains the value 812 I don't understand why I get the following exception: ************** Exception Text ************** System.Runtime.InteropServices.COMException (0x80020008): Bad variable type. at ADODB._Parameter.set_Value(Object pvar) at ADODB.InternalParameter.set_Value(Object value) at SugroBR.LeverandÃ¸r.GetStoredProcedureCommandObject(Connection cn) at SugroData.SugroDataObjekt.GetData(Boolean bClose) Tag: ExecuteNonReader hangs Tag: 97517
  - 23
    - The way ADO retreives parameters from SQL Server I have a question to which I hope someone has the answer. We have developed an application that queries a database a great deal and speed is crucial. We do all database operations utilizing stored procedures and return the information in parameters since there would be only one row anyway. We have noticed some interesting timing that is causing us problems in the overlying process this applciation supports. By using Windows high performance timers to measure the performance we have learned that sometimes we'll have long delays (When I say long delay I mean between 150 to 600 mS.) waiting for the ExecuteNonQuery to return. Other times (a little more rare) we would have a long delay getting the parameters back after the ExecuteNonQuery returned. Of course we try to retrieve the SQLParameters as soon as the ExecuteNonQuery returns. That delay is measured from the time the ExecuteNonQuery returns and the first SQLParameter is read out of ADO. The time needs to stay below 100 mS. The delay doesn't happen very often about once in every 100 or so attempts. We are putting performance timers in SQL Server to determine the times the stored procedures uses for processing and examining when the server recompiles its stored procedures. So now for the question. We are concerned with the delay ADO is giving us when we attempt to call the SQLParameters collection. Do the parameters return to ADO on a background thread that is spawned when the ExecuteNonQuery returns complete from the stored procedure? If not does anyone know what might account for these infrequent delays that we are seeing? We are using Framework 1.1 SP 1. Any help will is greatly appreciated. Rob Tag: ExecuteNonReader hangs Tag: 97514
  - 24
    - Problems with DATASET.WriteXml: I get two files (XML and XSD) Hi Newsgroup, I have a really suspicious problem: I want to write the data of a Dataset to a XML-File, wich should include then schema of the data. For this I'm using the method DATASET.WriteXml("FILENAME" + ".xml" , System.Data.XmlWriteMode.WriteSchema). As result I get two files: a) XML-File : FILENAME.xml, of course containing the data and a reference to b) b) XSD-File : FILENAME_app1.xsd , containing the schema. When I'm changing the overloaded WriteXml-method to try a work around over a XMLTextWriter I get the same result. What can be the reason for this behavior ? How can I solve this problem ? How can I get an XML-File with an inline schema ? Many thanks in advance Jan -- Jan Schoenrock, Hamburg, Germany Tag: ExecuteNonReader hangs Tag: 97510
  - 25
    - Reading timestamp value form Sybase DB I'm trying to read the timestamp value from the DB into a template column within a datagrid. The issue I'm having is that the value displayed is "System.Byte []". Do I have to do any operations to get it in this format -> "0x000100000139d595" which is the format it's in the DB. Thanks, Tag: ExecuteNonReader hangs Tag: 97507

Hello everyone,
my webservice read from (and write to) Oracle queues. It uses the
Oracle Data Provider downloaded from Oracle site (Oracle10g Data
Provider for .NET 10.1.0.2.0). Webservice calls a stored procedure
that executes the read operation. Sometimes the ExecuteNonReader()
hangs (I'd like to set a timeout on command, but OracleCommand does
not support this option). Consequently the webservice fails for
timeout.
I tested the same class without using webservice and it doesn't hangs.

Do anyone know what is the problem? Is it a bug? I'am using .NET 1.1
on Windows 2003.

Thanks, Gionni

Top

Re: ExecuteNonReader hangs by David

David
Thu Oct 21 09:15:30 CDT 2004

"Gionni" <ehigionni@katamail.com> wrote in message
news:d96ed82b.0410210607.7d9bb75e@posting.google.com...
> Hello everyone,
> my webservice read from (and write to) Oracle queues. It uses the
> Oracle Data Provider downloaded from Oracle site (Oracle10g Data
> Provider for .NET 10.1.0.2.0). Webservice calls a stored procedure
> that executes the read operation. Sometimes the ExecuteNonReader()
> hangs (I'd like to set a timeout on command, but OracleCommand does
> not support this option). Consequently the webservice fails for
> timeout.
> I tested the same class without using webservice and it doesn't hangs.
>
> Do anyone know what is the problem? Is it a bug? I'am using .NET 1.1
> on Windows 2003.

Reading from a queue is supposed to "hang" until a message becomes
available.

Are you using DBMS_AQ.DEQUEUE? There is a timeout you can set there.

David

Top

Re: ExecuteNonReader hangs by ehigionni

ehigionni
Fri Oct 22 04:46:04 CDT 2004

"David Browne" <davidbaxterbrowne no potted meat@hotmail.com> wrote in message news:<OavKth3tEHA.2624@TK2MSFTNGP11.phx.gbl>...
> Reading from a queue is supposed to "hang" until a message becomes
> available.
>
> Are you using DBMS_AQ.DEQUEUE? There is a timeout you can set there.

That's my problem. In my test I use the DBMS_AQ.DEQUEUE with a low
timeout (5 seconds), then I poll the queue. The queue is always empty.
So I would expect after 5 seconds, the ExecuteNonReader returns for
timeout. Sometimes the ExecuteNonReader does not exit and hangs.
Consequently the web service times out and raises the error.
The very strange thing is that If I do not use a web service, but a
console application (same code), the ExecuteNonReader never hangs.

> David

Thanks,
Gionni

Top

Re: ExecuteNonReader hangs by ehigionni

ehigionni
Mon Oct 25 08:44:09 CDT 2004

ehigionni@katamail.com (Gionni) wrote in message news:<d96ed82b.0410220146.462ab6a5@posting.google.com>...
> Consequently the web service times out and raises the error.

I solved setting "Pooling = false" in the connection string. However I
am still ignoring why now it is working.

Cheers,
Gionni

Top