Subselect, TableAdapter and MS-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
    - Timeout expired connection Ihave an application (.Net 1.1, wi2k3 SP1, SQL 2005) that works fine after severalminutes of browsing then I start to get the following error : Servor Error in '/myVirtualServer' Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. Thismay have occured because all pooled connections were in use and max pool size was reached. I have several connections opened/closed that use the same connection string What might be the issue? Is there a parameter to fine tune the number of connection allowed. Thanks Tag: Subselect, TableAdapter and MS-Access Tag: 133412
  - 2
    - Update Access DB from a Data Grid I have a data grid with three columns Text1, Text2, Bool3. I would like to have the user check/uncheck in Bool3 column and automatically update the Access DB. One other thing I have Text1 and Text2 and readonly and the user can only modify the Bool3 column; is there a way to have the user only in the bool3 column and never in the Text1 or Text2 columns. Thanks, Steve Tag: Subselect, TableAdapter and MS-Access Tag: 133406
  - 3
    - Async Ado.Net Operations (Begin/End OpenConnection) We're building high performance database applications, and are trying to use all the new Async ADO.Net 2.0 features. The problem we're having is that we can't find a SqlConnection.BeginOpen/EndOpen method. We can execute everything using the BeginExecuteCommand, but without the BeginOpen on the connection we end up with lots of deadlocked threads, all waiting for SqlConnection.Open. This means things are more-or-less useless. We looked briefly into MARS for doing this, as that might be an option, but we really prefer the more standard Async stuff as it maps much easier into working with other database providers such as Oracle. What's frustrating is that early Whidbey builds, and many old ADO.Net 2.0 articles, refer to these methods so they existed at one point. These methods were obviously removed during the .NET 2.0 RTM process, which seems really strange. Any ideas how to work around this and actually do highly scalable I/O using the Async infrastructure in ADO.NET 2.0? -- Chris Mullins MCSD.Net, MCPD Enterprise http://www.coversant.net/blogs/cmullins Tag: Subselect, TableAdapter and MS-Access Tag: 133397
  - 4
    - ADO.NET 2.0 Asynchronous Command Execution: Independant of Web Session? I have a small ASP.NET 2.0 application that needs to be able to kick off a long stored procedure (for importing and processing tables ) and then allow the user to continue with the session, perform other tasks, and log out without having to wait for the SP to complete. I read the following article: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvs05/html/async2.asp This looks very promising but I could not figure out what would happen to the thread if the user logged out before it completed. The stored procedure could take several hours depending on the parameters selected so I need the command to be independant of the web Session. Will it keep running or die when the user logs out? If it dies, can anyone suggest a way to set it up so it will run until complete? Thanks for your help! Tag: Subselect, TableAdapter and MS-Access Tag: 133388
  - 5
    - Getting Oracle Error Hi I'm trying to run a queries on a schema, and I keep getting ORA-00911: invalid character error. If I copy/paste it in SQL/Plus it runs fine. This is what I'm doing in code: //Set session here to ZGK037_MDB string sqlSession = string.Format("ALTER SESSION SET CURRENT_SCHEMA={0}", oDB.DatabaseName); //Note I don't close the Connection Object in this call. It just gets an OracleCommand Object cmdOracle = MiscDataUtils.GetCommandObj(sqlSession, oDB.Connection); //Execute the sqlSession query cmdOracle.ExecuteNonQuery(); //This is what it shows in my logfile...so it looks like the above worked with no exceptions [Logging-DEBUG]-9/29/2006 10:22:45 AM [GetCommandObj] ALTER SESSION SET CURRENT_SCHEMA=ZGK037_MDB Next I try to run a query on this command obj cmdOracle.CommandText = sql; //Set to my new SQL string rdr = MiscDataUtils.GetDataReaderFromCommandObj(cmdOracle); This is what the log file says: [Logging-DEBUG]-9/29/2006 10:22:45 AM [GetDataReaderFromCommandObj] Select RoFm.SymCount as Occ, csd.oid as SymbolOID, csd.m_progID as ProgID, csd.m_version as ProgVersion From CORESymbolDefinition csd, (Select Count(*) as SymCount, fm.m_defname as RoFmID FROM CORERelationOrigin RO JOIN COREFlavorMgr FM ON RO.oidTarget = FM.oid JOIN CORESymbol S ON RO.oid = S.oid Group By fm.m_defname ) RoFm Where csd.m_progID=RoFm.RoFmID Order by csd.m_progID; [Exception] ORA-00911: invalid character Any help is really appreciated. Sunit Tag: Subselect, TableAdapter and MS-Access Tag: 133385
  - 6
    - TableAdapter vs DataAdapter Hi, I was wondering when to use TableAdapters (TA) and when to use DataAdapters (DA). If I've got a typed dataset containing multiple related tables, I would say use a DA to fill the dataset. Why should I use TA instead? I can imagine situations where may be just one table from the dataset is needed, in that case you might say, use a TA. I agree on that. What about updating a typed dataset containing multiple related tables. In that case I also would use a DA rather than TA's. I presume I will need multiple TA's when updating multiple tables in this typed dataset (i.e. added orderd and orderdetails in the typed dataset). Conclusion: If I want to use just a few tables I would better fill the tables using TA's and if I've got just one table to update I could also use a TA. But when filling a dataset in one operation and when updating multiple tables in a dataset I would better use a DA. Is this correct? P.S.: I will not use SQL in code, just SPROC's Tag: Subselect, TableAdapter and MS-Access Tag: 133383
  - 7
    - Casting - IDataParameter Is there some way to cast a IDataParameter to a SQLParameter? It's not working and I'm not sure why. Tag: Subselect, TableAdapter and MS-Access Tag: 133381
  - 8
    - xmltype oracleclient Hi - Does anyone know if the xmltype oracle column data type is supported in system.data.oracleclient? ---------------- Sal .NET 2003 Oracle 9i Tag: Subselect, TableAdapter and MS-Access Tag: 133379
  - 9
    - Connection pooling parameters not working for Oracle connection I am using OracleConnection to connect to Oracle 9i Database from a VB.NET project the connection string has the parameters "User ID", "password", and "Data Source". my project import System.Data.OracleClient I tried to add connection pooling parameters like "Connection Timeout", "Incr Pool Size" and "Min Pool Size" to the connection string string but for each one I get the error that this parameters is not supported, when I try to open the connection any clue ? Tag: Subselect, TableAdapter and MS-Access Tag: 133378
  - 10
    - Export Table to dBase 3 (.dbf) Hi! Does anybody know the best way how to export a DataTable to a dBase 3 file? Regards Stephan Tag: Subselect, TableAdapter and MS-Access Tag: 133377
  - 11
    - InvalidOperationException when changing value in XmlDataDocument I'm using a XmlDataDocument to interface a DataSet. When trying to change the value of a node (I'm actually using InnerXML) I get an InvalidOperationException saying "Please set DataSet.EnforceConstraints == false before trying to edit XmlDataDocument using XML operations.". Is it not possible to make changes in the DataSet without setting EnforceConstrains to false? If I set the EnforceConstraints to false, all values that are constrained may be overrided. I only want to be able to set values that are not constrained. Tag: Subselect, TableAdapter and MS-Access Tag: 133375
  - 12
    - Remove association between DataSet and XmlDataDocument I have a DataSet that is not cleared untill the application is closed. But I want to associate a XmlDataDocument to the DataSet several times. If I try this I get an ArgumentException saying that a XmlDataDocument cannot be associated with a DataSet that already has an association. How do I remove this association without clearing the DataSet? Another possibility would be to retrieve the reference to the XmlDataDocument from the DataSet, but such a reference doesn't seem to exist? Tag: Subselect, TableAdapter and MS-Access Tag: 133374
  - 13
    - What happened to SqlConnection.BeginOpen? We're building some very high performance database applications, and are trying to use all of the new Async ADO.Net 2.0 features. The problem we're having is that we can't find a SqlConnection.BeginOpen/EndOpen method. We can execute everything using the BeginExecuteCommand, but without the BeginOpen on the connection we end up with lots of deadlocked threads, all waiting for SqlConnection.Open. We looked briefly into MARS for doing this, as that might be an option, but we really prefer the more standard Async stuff as it maps much easier into working with other database provides such as Oracle. To do this with MARS, we would still need this BeginEnd methodology. What's frustrating is that early Whidbey builds, and many old ADO.Net 2.0 articles, refer to these methods so they existed at one point. These methods were obviously removed during the .NET 2.0 RTM process, which seems really strange. Any ideas how to work around this and actually do highly scalable I/O using the Async infrastructure in ADO.NET 2.0? -- Chris Mullins MCSD.Net, MCPD Enterprise http://www.coversant.net/blogs/cmullins Tag: Subselect, TableAdapter and MS-Access Tag: 133370
  - 14
    - Oracle connection error when called from web service Hi, I'm having a web service which connects to a Oracle database and gets the results. When running the web service individually the database gets connected and the results are downloaded. I'm calling the same web service from another web service, the Oracle connection is not made, it provides me a exception "ORA-12154: TNS:could not resolve service name". The web services used to work perfectly until for some other purpose i changed the tnsnames.ora file, after that i reverted back with the changes earlier (which was working). But still if there is any problem in tnsnames.ora file, then the first web service should also not get connected to database. I don' t know how it gets connected. Following is the code used, string query = GetQuery( ID ); // Create a Oracle connection. OracleConnection dBConn = new OracleConnection(); // Provide the connection string details. dBConn.ConnectionString = "User Id=XXXX;Password=XXX;Data Source = XXX"; // Frame a Oracle command. OracleCommand cmd = new OracleCommand( query ); // Connect the command to connection for the Oracle database. cmd.Connection = dBConn; try { // Open the connection. dBConn.Open(); } catch( Exception ex ) { } Can anyone provide me a pointer about what is happening and how to solve the same?? Regards, Chandra Tag: Subselect, TableAdapter and MS-Access Tag: 133368
  - 15
    - Executing an sql script on a remote database from Visual Studio .NET 2005 I am connecting to a remote SQL Server database from Visual Studio .NET 2005. I have an sql script located on my machine containing the sql code I want to use to create several tables, and want to execute it. How can I do this from Visual Studio .NET 2005? I do not like designing the database using the designer because sometimes the designer uses different words, phrases things differently, and causes me to be unsure exactly what the properties of the fields in my database are. If anybody know how to do this, please let me know. Thanks. -- Nathan Sokalski njsokalski@hotmail.com http://www.nathansokalski.com/ Tag: Subselect, TableAdapter and MS-Access Tag: 133367
  - 16
    - Query works in Query Analizer but not as part of a table adapter I'm trying to run a query to return all of the customers who have not placed an order. This query runs fine in Query Analizer but when I try to run it as part of a table adapter it returns all rows (customers with orders AND customers without orders). It appears that the second select is not being utilized. What am I missing? SELECT CustomerID, Name FROM Customers WHERE CustomerID NOT IN (SELECT CustomerID FROM Orders WHERE (OrderMonth = 9) AND (OrderYear = 2006)) Thanks, Tom Tag: Subselect, TableAdapter and MS-Access Tag: 133364
  - 17
    - DataBindings : wrong data displayed Hello, I'm using databindings to display data from a datatable. When I first fill my datatable, it works perfectly, and displays the current data. If I remove the datatable, and recreate it with new data, it's not working anymore : the displayed data are from the first fill of the Datatable ! (I checked, it's not the same DataTable anymore). Here's my code : SqlDataAdapter sqlda = new SqlDataAdapter("SELECT * From Data",Cnx); if (GlobalDS.Tables.Contains("Data")) { GlobalDS.Tables.Remove("Data"); } sqlda.Fill(GlobalDS, "Data"); txtName.DataBindings.Clear(); txtName.DataBindings.Add("Text", GlobalDS,"Data.Name"); Thanks, Tag: Subselect, TableAdapter and MS-Access Tag: 133354
  - 18
    - Creating dataset.xsd file programmatically as dragging table from Server explorer fails Visual studio 2003, MySQL database, MySQL odbc 3.51 driver Is it possible to create the file dataset.xsd programmatically? I'm trying to create my dataset1.xsd file for using with crystal reports. The database is mySQL and the data connection is sat in my server exporer bar. The tables and fields are enumerated correctly in the server explorer, but when i try to drag and drop any item onto the dataset1.xsd designer surface I receive a sql error - "The wizard detected the following problems when configuring the data adapter for "users". Details: Error in SELECT clause: exression near "" Error in FROM clause: near "" Unable to parse query text." If I press OK, then i receive the message - "The following unexpected error has occurred: There were errors dropping "users" onto the designer. The XML schema could not be interpreted from this object. " Im stuck and relatively inexperienced so I don't know where to go from here. I thought that it might be possible to create a dataset from code then write it to file. How would I go about this please? Tag: Subselect, TableAdapter and MS-Access Tag: 133353
  - 19
    - DTSWizard Are there any tools around similar to MSSQLs DTSWizard for importing data into a DataTable? The DTSWizard has many nice features which I would hate to recreate if it's already been done. Thanks, Joe Tag: Subselect, TableAdapter and MS-Access Tag: 133336
  - 20
    - ado & Active directory I am using ado to connect to active directory in a web application. I create the command: objConnection = new adodb.connection objCommand = New adodb.Command objConnection.Provider = ADSDOObject objCommand.Properties("Page Size").Value = 1000 objCommand.Properties("SearchScope").Value = 2 I have one server, one IIS, 2 directories. All permissions are the same that I can tell on both directories and IIS settings. However, when I run the above on one of the directories, it run fine, on the other I get this error: System.NullReferenceException: Object reference not set to an instance of an object. at System.Enum.InternalGetValue() at System.Enum.System.IConvertible.ToBoolean (IFormatProvider provider) Why would I get this error on one directory and not the other, exact same program, same web.config, everything I can think of is exactly the same. And whatever could be the cause?? Thank you for your help. Tag: Subselect, TableAdapter and MS-Access Tag: 133335
  - 21
    - How do I connect to SQL database using applicaiton roles and ado.net? My MSDE database uses Application Roles. When I run my asp.net application I cannot open the database, I get a Logon Failed exception. I need to be able to open the DB to run the sp_setapprole stored procedure for my role access. Is there some other way that I can do this? So far the only way I can get this to work is to give the ASP.NET process SYSTEM rights. I would prefer to use the ASPNET user rights. My connection string is as follows: "Pooling=false;Server=(local);Integrated Security=SSPI;Database=MyDb" -- ----------------------------------- Ken Varn Senior Software Engineer Diebold Inc. EmailID = varnk Domain = Diebold.com ----------------------------------- Tag: Subselect, TableAdapter and MS-Access Tag: 133334
  - 22
    - Trapping for Connection and Command Timeout FW: 1.1 What is the best method for trapping timeout exceptions? There does not seem to be specific exceptions thrown for connection or command timeout. Should I check the message for "Timeout", or is there some other propery in the exception that better identifies a connection or command timeout? Thanks for your help. Tag: Subselect, TableAdapter and MS-Access Tag: 133333
  - 23
    - SQL Question Hello: I have a SQL question. If this is the improper board to post this question on, please let me know where you would suggest I post it. I have a SALES table with the following schema and structure. ID Status Date -- ------ ---- 1 Open 1-1-2000 2 Open 1-1-2001 3 Closed 1-1-2002 4 Open 1-1-2003 5 Closed 1-1-2004 I want to write a query that returns all the records where it first does a count of all the statuses, identifies where the count status is > 2, then display each record where the status = the status which has been identified as having a count of > 2. So the results I want back are: ID Status Date -- ------ ---- 1 Open 1-1-2000 2 Open 1-1-2001 4 Open 1-1-2003 So I write a query something like this: SELECT count(status), id, date FROM sales GROUP BY status HAVING count(status) > 2 I get a SQL error saying that I have to add "id" and "date" have to be in the GROUP BY clause. But when I put those fields in the GROUP BY, then the count does not calculate right and I get no results back. Could anyone enlighten me as to what I could do to fix this? Tag: Subselect, TableAdapter and MS-Access Tag: 133320
  - 24
    - pStartInfo in C#.Net Hi all, I want to do the foll. I want to get the process running in the name system, with name "abc", and in turn get their arguments, so that i do some processing later Foll. is my code: Process proc = new Process(); proc.StartInfo.FileName = "notepad.exe"; proc.StartInfo.Arguments = "abc.cs"; proc.StartInfo.UseShellExecute = false; proc.Start(); Process[] p = Process.GetProcessesByName("notepad"); string args = p[0].StartInfo.Arguments.ToString(); Console.WriteLine("args: " + a); //this is retning null - shd retn abc.cs string b = p[0].BasePriority.ToString(); Console.WriteLine("BASE PRIORITY: "+ b); //this comes out correctly With the above code, args is returnin a null string. (Also abc is currently running-obvious 'coz i didnt get any InvalidOperationException). when i try to print the parameters other than that of StartInfo, its working fine-only in case of StartInfo params. Tag: Subselect, TableAdapter and MS-Access Tag: 133314
  - 25
    - Using crystal reports with MySQL ADO connector I am a developer with limited database and zero crystal report experience. I am very confused. We wanted the ability to switch database providers easily, so I selected to use ADO components with our application. The database we're developing with is MySQL. I downloaded their MySQL Connector/NET components which are ADO compliant. Using .NET connectors, if we want to switch to another provider, there should be minimal code changes necessary to make it run. It all works fine so far. I connect to the remote database at run time using a connection string read from stored configuration information. Now I need to add reporting capabilities to the application, using Crystal reports, and this is where Im confused. http://support.businessobjects.com/communitycs/technicalpapers/rtm_reportingoffadonetdatasets.pdf This example shows preparing data for use in a web project. Ours is a desktop application. So far I've tried to keep all of this as generic and configurable from config files as possible. The example in the above link uses a wizard to link to OLE DB provider registered on the local pc. This immediately ruins the portability as it's done through a wizard. Is OLE DB the "same" as ADO? Do I need to download and install OLE DB drivers from mySQL's site? I wondered if once you've used this wizard, at later dates you can programmatically change server information. Is that possible? thanks :-) Tag: Subselect, TableAdapter and MS-Access Tag: 133313
- Next
  - 1
    - How to keep ALTER SESSION settings Hello All I'm trying to execute queries on a specific schema using ALTER SESSION as string sqlSession = string.Format("ALTER SESSION SET CURRENT_SCHEMA={0}", oDB.DatabaseName); MiscDataUtils.ExecuteNonQuery(sqlSession, oDB.Connection); //Change Session Here //However this one fails since this is another connection rdr = MiscDataUtils.GetDataReader(sql, oDB.Connection); Is there a better way to do this. ?? thanks Sunit Tag: Subselect, TableAdapter and MS-Access Tag: 133312
  - 2
    - Non English Characters Hi, We are developing a small web interface to a local ERP software, which uses SQL Server 2000 as database. The database uses SQL_Latin1_CP1 collation, and the fields are varchar (not nvarchar), however, the main program inserts and reads non-English (Turkish) characters into these columns. However, when we connect to database with ADO.NET, these characters are not read correctly. (The situation is same when I check tables with Enterprise Manager and Query Analyzer) In a past situation (which was about a Win32 application), I have heard about character conversion behaviour of ADO (and many other DB libraries) and solved that problem using BDE instead of ADO, so that the connection is made via DB-Library instead of OLEDB. But this way cannot be applied to my ASP.NET situation, and there is NO way to change database collation. Must I use a ADO.NET property, or use another provider, or maybe another library? Any advices? Thanks... Tag: Subselect, TableAdapter and MS-Access Tag: 133310
  - 3
    - Corrupted TableAdapter I have this a Data Set. In that data set I have three table adapters, each has there own queries. For some unknown reason, after I added two new fields to the database and mofified the store procedure to retrieve the new fields, in addition too the other fields it already retrieves, and finally modified the tableadapter "GetAssetsById" to retrieve the new fields the TableAdapter Wizard insists on putting a "GetData" as a Return a DataTable Method even though I have uncheck the box. Now my applications fails to execute. I am getting errors everywhere that uses this tableadapter. Sorry I tried but could not supply the code for the tableadapter. Tag: Subselect, TableAdapter and MS-Access Tag: 133306
  - 4
    - db2 commands on .net hi, i'm using db2 v8.2 and .net 2005. i'm trying to execute db2 commands but i can't . forexample, i can't get list of aliases or i can't create a new database. how can i solve this problem? please help me. Tag: Subselect, TableAdapter and MS-Access Tag: 133303
  - 5
    - Problem with proc.kill() Hi, My case is, i am trying to kill a process (SQLPLUS process) in my Win. Service, when either the Service is stopped or when my app exe is killed from the Task Manager. The problem is, when i kill the SQL process, its not getting killed, and its running and running. I find this in both the cases of stopping the service and killing the exe. What can i do to kill the process, because killing SQLPLUS is critical; and because of this problem, two SQLPLUS processes are overlapping with each other, when the next time i start the exe/service. Thus, my output file, where actually i spool data (using SQLPLUS) becomes inconsistent and this is bad. I couldn't find any alternative to proc.kill() [which shd. actually kill the process immediately] Can any of u help me out in this ASAP?? Tag: Subselect, TableAdapter and MS-Access Tag: 133302
  - 6
    - CommandBuilder and Identity Field Hi, I used a commandbuilder for my DataAdapter. i have a dataadapter "Pather" and a dataadapter "Sons" in "Pather" table i have and identity field that i use in "Sons" table to join them. How can i know with identity number i have for my new "Pather" row ? How can set my "sons" rows with this identity value ? I hope my bad english is little clean. Thank's a lots Tag: Subselect, TableAdapter and MS-Access Tag: 133300
  - 7
    - using access as database Hey everyone, I want an advice. I want to build a site as an internet application using database etc. Since I need to use database , I'm interested in something not expensive for my own needs. I thought about using sqlServer, but I know that it is very expensive. Though I thought to use Access. - Did anyone use Access in an Asp.net application ? - Is it comfortable and runs ok ? - Does Microsoft continue to develope and support this tool ? - Which Access do I need for my purpose ? the simple one that comes with office 2003 ? Thanks a lot for any answer !!! Tag: Subselect, TableAdapter and MS-Access Tag: 133298
  - 8
    - haii...adding bunch of records haii... i neeed to know how to add a bunch of records to a database table from c#.net coding.. please sugest me .... Tag: Subselect, TableAdapter and MS-Access Tag: 133293
  - 9
    - Connection String of SQL 2005 We upgrade our database from 2000 to 2005. So I use the new connection string in our newly developed web app. "Provider=SQLNCLI;Server=server_ip;Database=db_name;UID=username;PWD=password;" I got this error: The 'SQLNCLI' provider is not registered on the local machine I want to know, if I use OLEDB in my asp.net, is it necessary to use the Native Client drive? Tag: Subselect, TableAdapter and MS-Access Tag: 133292
  - 10
    - CommandBuilder ConflictOption usage I'd like to use the command builder to create update queries but my database schema uses datetime columns for the last time modified instead of a timestamp. Unfortunately ConflictOption.CompareRowVersion seems to only work if a timestamp exists. The update command seems to be rewritten by the dataadapter update method. I added an event handler for the RowUpdating event and the update command is different inside the handler. I'm guessing the update command is created dynamically for each row. Looks like I'll need to fire up reflector. Tag: Subselect, TableAdapter and MS-Access Tag: 133285
  - 11
    - Possible to Select * Into #temp From tblx and read #temp with ado. Hello, I have a complex query on sqlsrv2000 that runs better if I use an intermediate #temp table. I have tried using a dataAdapter, Command Object to da.SelectCommand.CommandText = "Select * Into #temp From tblx" da.SelectCommand.ExecuteNonQuery But I get an error. The goal is to then use that same #temp table in a subsquent query. I am thinking that since I am still on the same connection - the #temp table would persist. But that does not seem to be the case. Any suggestions appreciated if this is doable and how - or if I am limited to using a stored Proc are table on the disk for something like this. Thanks, Rich Tag: Subselect, TableAdapter and MS-Access Tag: 133284
  - 12
    - Calling Oracle proc from C# - must I pass in a cursor? I want to create a simple procedure in Oracle that returns a resultset (a select query). In all the examples I've seen, you have to pass in a "ref cursor" OUT param into the proc from C#. Is there a simpler way to do this? IWO can I not pass anything in, and just declare the cursor in the proc? Or have no cursor, like with SQL Server? Seems pointless to pass in an empty cursor param each time. Thanks, Burt Tag: Subselect, TableAdapter and MS-Access Tag: 133281
  - 13
    - getting data from exchange server How can I get email data by email address from Exchange Server into a vb.net web application? The exchange server is on the same network as the web server. I basically need to be able to display what emails were sent to/received from based upon an email address, with the date, subject and message. For instance, if there were emails from clienta@clienta.com, I want to be able to get any emails back that were sent to clienta@clienta.com and who sent them, and any emails received from clienta@clienta.com and who received them. Can you help on this? Thanks. Tag: Subselect, TableAdapter and MS-Access Tag: 133280
  - 14
    - OleDbConnection + MySQL hanging. Hi, I'm trying to use a web service on my webhotel that has support for ASP.NET 2.0 and MySQL 4.0.18-nt. However, whenever I try to use OleDbConnection, my SOAP call just hangs for a while, then returns with "WebException unhandled - Operation timed out.". I've pinpointed it all the way to happening when I call conn.Open() on my OleDbConnection object. Now I tried to make an ODBC driver for my db too, but it gave the same result. Everything else seems work fine with my web service, but it's not much fun without the database support.. Web Service Code: [WebMethod] public string CheckDb() { OleDbConnection conn = new OleDbConnection(strConn); try { conn.Open(); OleDbCommand command = new OleDbCommand("SELECT * FROM test", conn); command.ExecuteNonQuery(); conn.Close(); return "okay"; } catch (Exception x) { return x.ToString(); } } Just to add, I've tried with both valid and invalid connection strings, tried excluding my username / password to get it to fail, but the result is always the same. Anyone have any experience with these sorts of hangs - and what the problem could be? Thanks, - Rasmus. Tag: Subselect, TableAdapter and MS-Access Tag: 133275
  - 15
    - MS ACCESS and ADO.NET 2.0 Hi, I try to use new DataSource wizard to create a connection to MS Access 2003 with VB.NET 2003. Unfortunetly after I created the solution when I try to use getdata button I get and error that MS Access DB not found. I cretaed folder in under my project and call it as DB and mdb file name was DDB.mdb. First run was I get above error and second time when I try to cionstruct again it tells me to its going to add the mdb file under my solution and I am still getting error. Does anyone know how to construct this or what should I do to change in my config file to accept new string that point correct folder with correct files? Thank you. Rgds, Niyazi -- Message posted via DotNetMonster.com http://www.dotnetmonster.com/Uwe/Forums.aspx/dotnet-ado-net/200609/1 Tag: Subselect, TableAdapter and MS-Access Tag: 133271
  - 16
    - ConnectionString manual and secure in C# 2.0 Hello, I am a newbie in C#2.0, as I have read, the application automatically generates a configuration files to store the connectionstring in (.XML file). So after compiling, everyone can see this sensitive infomation (username, password). Pls tell me how to make it "hidden" from user and how to manage the connectionstring promatically (set server name, databse name, username) Thanks in advance! -- Contact: Bauman Moscow State Technical University Russia, Moscow 105005 Tel: +7915-426-6272 ICQ: 160-223-078 YM: Chipheo2k Tag: Subselect, TableAdapter and MS-Access Tag: 133269
  - 17
    - DataTable Add Row is slow (especially with empty table) After several frustrating hours, I found a solution to a problem that I've been having with DataTable.Add running very slow when trying to add a bunch of records. I found similar questions posted but no good answers, so I thought I'd post this in hopes of helping someone else. Problem: DataTable.Add is very slow. It may be relatively fast when the DataTable already has some rows in it, but is terribly slow when the DataTable is empty. First, unbind any controls that are bound to the DataTable or dependent DataViews. For example: myDataGrid.SetDataBinding(Nothing, Nothing). Next, before you start your batch Add process, call myDataTable.BeginLoadData(). This turns off notifications that the DataTable normally does automatically when you Add. After you are done adding rows, call myDataTable.EndLoadData(). This turns them back on. myDataGrid.SetDataBinding(Nothing, Nothing) myDataTable.BeginLoadData() ' Add a bunch of rows here... myDataTable.EndLoadData() Good luck. Tag: Subselect, TableAdapter and MS-Access Tag: 133267
  - 18
    - combining fields VS2003 C# Hi, I have a bound text box in which I would like to display a first name/last name concatination. These reside in different fields in the database. Rather than do this from the underlying database, or doing it in code, which would reduce the effectiveness of the binding, is there anyway I can combine them on the dataset/datatable that is being bound to? Thanks for any ideas on this. Tag: Subselect, TableAdapter and MS-Access Tag: 133266
  - 19
    - Passing parameter for Stored Procedure to Data Adapter/Data Set Hey all, I'm trying to pass a parameter into my Stored procedure to be accessed by my data adapter into a dataset db2.AddParameter("@intClientID", cmbClientList.SelectedValue.ToString()); DataSet rsDataset = db2.Fill("spListMappingDetails", CommandType.StoredProcedure); However, when i try to run it, it gives me an error {"Procedure 'spListMappingDetails' expects parameter '@intClientId', which was not supplied."} Any help is appreciated. I used a executescalar and executequery function and it has no problem. thanks in advance Tag: Subselect, TableAdapter and MS-Access Tag: 133265
  - 20
    - Compare datasets with hash values or possibly another method? I have two datasets with the same exact structure that I would like to compare. I would like to know if there is any difference in the data between them. I don't need to know what the differences are, only that there is a difference. From what I have read about hash values, I thought that I could obtain the hash values of the datasets and compare those. If the hash values were different, I would know that there is a difference in the data in the datasets. I tried this approach, but I found that even when I changed a string in one of the fields in one dataset, the hash values calculated were the same. I am not sure if I am missing something here??? This is the code I performed on both datasets to get the hash values... ' Create a new instance of memory stream Dim ms1 As New MemoryStream Dim formatter As New BinaryFormatter 'Serialize dataset to memory stream formatter.Serialize(ms1, dsCurrent) 'Compute hash value for stream Dim hashvalue1 As Byte() = CType(CryptoConfig.CreateFromName("MD5"), HashAlgorithm).ComputeHash(ms1) Then I changed the hash byte array to a hex string... Dim i As Integer Dim sOutput1 As New StringBuilder(hashvalue1.Length) For i = 0 To hashvalue1.Length - 1 sOutput1.Append(hashvalue1(i).ToString("X2")) Next Dim strHashValue1 As String = sOutput1.ToString I then compared the hex strings. Is there another way other than hash values that I could use to compare the data in the datasets? I tried dataset.merge and found that when merging the second dataset into the first, the rowstate of the rows that were different between the two datasets did not change to 'modified', but remained at 'unchanged'. Any help would be greatly appreciated!! Thanks! From http://www.developmentnow.com/g/7_0_0_0_0_0/dotnet-framework-adonet.ht Posted via DevelopmentNow.com Group http://www.developmentnow.com Tag: Subselect, TableAdapter and MS-Access Tag: 133263
  - 21
    - 2.0 Auto Id in typed datasets Hi, I'm still strugling with the following situation. I've got two table which are related by a foreign key contraint. These table reside in a typed dataset. I working on a webbased app, so a disconnected environment. Now, when I add a row to the parent table and add rows to the childtable (think of customer and orders). This means that I have to use the autogen. id of the parenttable and use this Id as de FK for the childtable. Then when updating the database, I have to retrieve the new Id from de customer table from de db and use this id to update the FK in the childtable before I can add the rows to the db. In 1.1 for this I used the RowUpdating and RowUpdated event. The above example is very basic, in my situation I had to update multiple depending tables. This forced me to use the RowUpdating and RowUpdated methods. I als used the dataadapter.update to update the typed dataset. I can not rely on the Id generated by the typed dataset, because an other person can also add a customer and orders at the same time. To uniquely create an Id, I set the prim.key in the parent to a seed of -1 starting from -1. The neg. numbers are replaced with the new db Id as described in the example above. Now, do I have to use the same construction in 2.0 or is it better to use tableadapters instead? If yes, how can I use them in such a way the same functionality is there as in my 1.1 app? Tag: Subselect, TableAdapter and MS-Access Tag: 133261
  - 22
    - objectdatasource questions Can anyone tell me is there an easy way to get a value into an objectdatasource select parameter from Application Variables? Obviously its easy from cookies, session etc... but what about Application? Or is there an easy way to get a value into an objectdatasource parameter from the web.config? Kind of like how we get the connection string from web.config into a datasource object, like: <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:spg_portalsConnectionString %>" Can I do: <SelectParameters> <asp:Parameter Name="dept_id" DefaultValue="<%$ AppSettings:dept %> </SelectParameters> Tag: Subselect, TableAdapter and MS-Access Tag: 133252
  - 23
    - TableAdapter specifing 'IN' in the where clause for list of id's Hi, I have a table that contains an interger primary key. I want to query the table for rows that have values equal to a list of ID's that I specify. The sql that I would use would be something like the following: SELECT FunctionalGroupId, ReportId FROM FunctionalGroupReportXRef WHERE (FunctionalGroupId IN (@Ids)) When I specify this query in the TableAdapter wizard the wizard typecasts @id to an interger. Thus I can only specify one id in my query rather then a list of values. I would have expected @Ids to be a string thus allowing me to specify a list of id's. How can I get around this issue? Any help would be appreciated. Thanks!!! Tag: Subselect, TableAdapter and MS-Access Tag: 133249
  - 24
    - DataGridView or something else from what I read & tried, DataGridView was meant for Window Forms, not for ASP/Web pages... correct? While I can use it to display data, I can't seem to figure out how to edit the cells... I'm not looking to put edit buttons on each row... I'll go back to regular window forms before I create such an interface.. So is it true, in ASP.Net using the DataGridView, I'm not going to be able to use that control to edit data? Is there such a control I can use in ASP.Net? Tag: Subselect, TableAdapter and MS-Access Tag: 133244
  - 25
    - how useful DbCommon namespace? I am working on an application where the end user will select the type of datastorage to use (SQL, Oracle, Access, even Excel for some readonly data, and maybe more providers ) The application reads/writes data and even must be able to create tables in databases (or even create databases) as needed. I am trying to use the new DbCommon namespace in ADO 2.0 to keep the database code as generic as possible. However, as we all know, there are many nuances between the common data providers that make this difficult. For example date formats in queries are different (JET, at least before Access 2003, requires '#' delimiters), cultural settings can work differently with Access/Excel JET (m/d/y or d/m/y). Syntax for creating tables are also different (nvarchar(1000) crashes in JET because text fields in JET are limited to 255 characters so you must use MEMO). So, while I can write a lot of DB independent code, I need specifics in many cases. While working to this end, I can't even find a simple way to query a connection to see who it's driver is!! I've used if (conn is System.Data.SqlClient.SqlConnection) ,is there a better way? I've also written query formatters for date parameters. Who out there has a little more experience to share? Some tricks, pitfalls, links to good provider comparision imformation, anything would be helpful! Tag: Subselect, TableAdapter and MS-Access Tag: 133240

1) I generated a Subselect query in MS-Access which works fine there:
SELECT (SELECT Name FROM Players WHERE Players.PlayerID =
PlannedGames.PlayerID) AS PlayerName, (SELECT Surname FROM Players WHERE
Players.PlayerID = PlannedGames.PlayerID) AS PlayerSurname, *
FROM PlannedGames

This Subselect is recommended in Scott Mitchel's excellent MSDN Article
"Creating a data access layer".

Then I cut&paste this select statement into the TableAdapter Configuration
Wizard.
This generates SQL parsing errors.

Does the ADO.NET 2.0 provider for MS-Access not support Subselects?

2) In general is there a comparison table which SQL-Server 2005 features /
.NET 2.0 classes are not available/working when using ADO.NET 2.0 and
MS-Access 2003 ?

thank you very much
herbert

Top

Re: Subselect, TableAdapter and MS-Access by Paul

Paul
Mon Oct 02 08:16:35 CDT 2006

On Sat, 30 Sep 2006 23:53:01 -0700, herbert <herbert@discussions.microsoft.com> wrote:

¤ 1) I generated a Subselect query in MS-Access which works fine there:
¤ SELECT (SELECT Name FROM Players WHERE Players.PlayerID =
¤ PlannedGames.PlayerID) AS PlayerName, (SELECT Surname FROM Players WHERE
¤ Players.PlayerID = PlannedGames.PlayerID) AS PlayerSurname, *
¤ FROM PlannedGames
¤
¤ This Subselect is recommended in Scott Mitchel's excellent MSDN Article
¤ "Creating a data access layer".
¤
¤ Then I cut&paste this select statement into the TableAdapter Configuration
¤ Wizard.
¤ This generates SQL parsing errors.
¤
¤ Does the ADO.NET 2.0 provider for MS-Access not support Subselects?
¤
¤ 2) In general is there a comparison table which SQL-Server 2005 features /
¤ .NET 2.0 classes are not available/working when using ADO.NET 2.0 and
¤ MS-Access 2003 ?

I would recommend avoiding the configuration wizard in this instance. Just copy the SQL text to the
CommandText property for the Fill method of the TableAdapter or the SelectCommand of TableAdapter
object.

Paul
~~~~
Microsoft MVP (Visual Basic)

Top