DAAB + Master/Detail Tables with SQL Identity field on master

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
    - Inserting Datarow into a named excel range. My code is starting to stadle a few diffrent groups, so I am not sure if my problem is with the ADO.NET or the interop methods I am using to define the Named range. When I used this sample code below in an Excel file i created by hand and gave a named range it worked. Now that I create the file with interop and define the named region programaticly this code gives me an exception. "{"Syntax error in INSERT INTO statement."}" I have confirmed by that I naming the Region MyTable, so I have no ideal this is not working. string test ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\Working\\OutPut.xls;Extended Properties=\"Excel 8.0;\""; System.Data.OleDb.OleDbConnection objConn = new System.Data.OleDb.OleDbConnection(test); objConn.Open(); // Add two records to the table named 'MyTable'. System.Data.OleDb.OleDbCommand objCmd = new System.Data.OleDb.OleDbCommand(); objCmd.Connection = objConn; for (i = 0; i < dsAudit.Tables[0].Rows.Count; i++) { dr = dsAudit.Tables[0].Rows[i]; objCmd.CommandText = "Insert into MyTable (EventName, ComponentName, Status, Affiliation, LastActivityTS, Count)" + " values ('" + dr[0] + "', '" + dr[1] + "', '" + dr[2] + "', '" + dr[3] + "', '" + dr[4] + "', '" + dr[5] + "' )"; objCmd.ExecuteNonQuery(); } // Close the connection. objConn.Close(); In case your intrested here is how I am defining the named region. m_objExcel = new Excel.Application(); m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks; m_objBook = (Excel._Workbook)(m_objBooks.Add(m_objOpt)); m_objBook.Names.Add("MyTable",@"=Sheet1!R1C1:R1C6" , true, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt, m_objOpt); Thanks, Tag: DAAB + Master/Detail Tables with SQL Identity field on master Tag: 131019
  - 2
    - Leer Varchar con DataReader Hola, he usado los DataReaders para bajar informacion de la base de datos y me ha funcionado muy bien pero con los campos de tipo Varchar no he podido, puedo bajar string, enteros, DateTime pero como se puede hacer con Varchar ???? Ejemplo si el campor es tipo string uso esto -> variable = ret_val.GetString(0); Gracias por anticipado. Tag: DAAB + Master/Detail Tables with SQL Identity field on master Tag: 131018
  - 3
    - Connection Pooling randomly throwing exceptions We randomly get the following exceptions: [COMException (0x80070006): The handle is invalid. (Exception from HRESULT: 0x80070006 (E_HANDLE))] System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32 errorCode, IntPtr errorInfo) +0 System.Runtime.InteropServices.Marshal.ThrowExceptionForHR(Int32 errorCode) +34 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +636 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105 System.Data.SqlClient.SqlConnection.Open() +111 System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +121 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +86 and [UnauthorizedAccessException: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))] System.Runtime.InteropServices.Marshal.ThrowExceptionForHRInternal(Int32 errorCode, IntPtr errorInfo) +0 System.Runtime.InteropServices.Marshal.ThrowExceptionForHR(Int32 errorCode) +34 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +636 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105 System.Data.SqlClient.SqlConnection.Open() +111 System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +121 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +86 Before the Access Denied exception is thrown, the user is prompted for credentials - none work, not even admin. Also, occasionally, pooling will throw a SemaphoreFullException: [SemaphoreFullException: Adding the given count to the semaphore would cause it to exceed its maximum count.] System.Threading.Semaphore.Release(Int32 releaseCount) +1853063 System.Data.ProviderBase.DbConnectionPool.PutNewObject(DbConnectionInternal obj) +54 System.Data.ProviderBase.DbConnectionPool.DeactivateObject(DbConnectionInternal obj) +228 System.Data.ProviderBase.DbConnectionPool.PutObject(DbConnectionInternal obj, Object owningObject) +265 System.Data.ProviderBase.DbConnectionInternal.CloseConnection(DbConnection owningObject, DbConnectionFactory connectionFactory) +97 System.Data.SqlClient.SqlConnection.Close() +117 System.Data.Common.DbDataAdapter.QuietClose(IDbConnection connection, ConnectionState originalState) +13 System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +285 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +86 Obviously, if I turn off pooling, I don't get these exceptions - but performance suffers noticeably. These exceptions don't always happen. The web app will run fine for hours before an exception is thrown. It could also be as short as the next request. . It randomly occurs and in random places in the code. Once thrown, every request afterwards throws the "Access Denied" exception. Using basic connection string: "server=xxx.xxx;database=xxx;uid=xxx;pwd=xxx;app=xxx". I have no idea how to go about troubleshooting this. Any help or ideas would be greatly appreciated. Tag: DAAB + Master/Detail Tables with SQL Identity field on master Tag: 131017
  - 4
    - Object Oriented Databases Just in the past few weeks, I have been looking at a OODBMS called Cache from http://www.intersystems.com/cache/ . At first glance ,this looks like a promising technology since it allows one to design tables in an object oriented manner and has automatic class generation technology that will generate classes for .NET, Java, etc. It also understands ANSI SQL. It seems like the perfect tool to solve the "impedance mismatch" problem. I just read this article on MSDN http://msdn.microsoft.com/data/default.aspx?pull=/library/en-us/dnvs05/html/nxtgenda.asp which is an add on layer for ADO.NET to solve the relational to object mapping dilemma, but it is just one more thing to learn on top of so many others things we need to learn as developers. Has anyone had any real experience with an OODBMS? In trying to search for actual usage experience, I haven't found much. If OODBMSs are that good, why do I not see a whole lot of material out there on them? Thoughts? Comments? Tag: DAAB + Master/Detail Tables with SQL Identity field on master Tag: 131016
  - 5
    - AllowDBNull & Performance? Hi, We have a C# programm running on .NET 1.1 I have found that on some PC's the performance decreases when using AllowDBNull=false on a DataColumn. By looping extensively through many DataRows in the DataTables, this becomes evident: the application is twice as fast without those codelines. Strangely, on other systems I detect no differences. Has anyone else seen this behaviour, or, even better, an explanation? Thanks Geert-Jan Tag: DAAB + Master/Detail Tables with SQL Identity field on master Tag: 131013
  - 6
    - Reading xml containing datetime attributes in dataset I need to to the fallowing: get the data from a dataset as an xml (using GetXml() ) and the load it latr in another dataset (using LoadXml) My problem it that the datetime columns throw an exception when the xml is loaded into the 2nd dataset(string was not ...). My steps are: store dataset1.getXml Create the 2nd dataset (columns of the correct type, MappingType, etc) dataset2.ReadXml(memorystream) the actual schema exported by the 2 datasets is identical Why exactly isn't this able to load my datetime columns correctly? (and how exactly should fix this? Tag: DAAB + Master/Detail Tables with SQL Identity field on master Tag: 131012
  - 7
    - ORA-01036 Error from GridView Hi, I have Oracle 8i database and i am trying to connect to it through .Net framework Data Provider for Oracle in visual studio 2005 GridView every thing goes well until I try to update or insert a record i get error ORA-01036 my update query is: UPDATE CUST_RIG SET FNAME = :FNAME WHERE CUST_SRL = :SRL i tried this in test machine with oracle 9i it works fine but it wont work in 8i Help Please :( Tag: DAAB + Master/Detail Tables with SQL Identity field on master Tag: 131010
  - 8
    - HELP! Why is SqlException being caught in this code if it can't connect to SQL Server? I have a function that simply returns TRUE if it can connect to a particular Sql Server 2005 express, or FALSE if it cannot. I am getting some strange error codes returned when the computer that sql server resides on is not reachable. The error is different depending on the connection string that I use. If I use the following connection string: "server=192.1.1.1; Initial Catalog=master; uid=The_User; password=The_Password; Connect Timeout = 10" then the error number I get for the sqlexception caught is 53. If I add 'tcp:' in front of the IP address in the connection string like this: "server=tcp:192.1.1.1; Initial Catalog=master; uid=The_User; password=The_Password; Connect Timeout = 10" then the error number I get for the sqlexception is 10060. If I add an instance to the server in the connection string like this: "server=192.1.1.1\SQLEXPRESS; Initial Catalog=master; uid=The_User; password=The_Password; Connect Timeout = 10" then I get the error number -1 for the SqlException. I cannot find any reference to any of these error codes. Why is this even being caught as a SqlException if it cannot even connect to the Sql Server in the first place? Aren't the error numbers for SqlException supposed to come from the master..sys.messages table??? Also, the timeout value seems to have no effect for the first two connection strings above, with it taking usually around 30 seconds for the exception to be caught. I am confused...! Here is the function I am using: Public Function TestConnection() As Boolean Dim myConnStr as string = "server=192.1.1.1; Initial Catalog=master; uid=The_User; password=The_Password; Connect Timeout = 10" Dim mySqlConn As new SqlConnection(myConnStr ) Try mConn.Open() Return True Catch ex As SqlException Debug.writeline(ex.tostring) Debug.writeline("---------------- The error code was " ex.number.tostring) Return False Catch ex As Exception Return False End Try End Function Thanks for any enlightenment. Cheers, Marcus Tag: DAAB + Master/Detail Tables with SQL Identity field on master Tag: 130995
  - 9
    - ADO.NET in VB6 May ADO.NET be used in VB6 ? I'm not asking about using it in DLL created in VB.NET or C# and then DLL is used in VB6. I'm asking about direct using. I need some of ADO.NET features, which are not available in a traditional ADO, and I need them in VB6 project. Thank you Al Tag: DAAB + Master/Detail Tables with SQL Identity field on master Tag: 130994
  - 10
    - Concurrency Violation w/ Access Database during Update() Hi All, I have a DataTable that needs an Update, but I am relying on the CommandBuilder to create my Insert, Update, and Delete statements for me. I have to - since my DataTable is free-formed (realtime), and I therefore need to get my SQL elements from the currently defined Columns (for my Select, Insert, Update, and Delete statements). I have had no problems making Updates to MS-SQL Server databases using this technique. It is only failing in Access, and only when ANY of the fields in the database row has an 'empty' value (empty string or DBNull). For example - if I have values in all of the fields in my row and I change one fields' value - the Update works fine (in Access & MS-SQL Server). If I delete a value in one of the fields in the row (say, set a string to "", <empty> for a field of 'Text' datatype) - that works too. But now - since I don't have a value in one of my fields in this row - the next Update (let's say I'm putting the value back into the empty field) fails w/ a Concurrency Violation. Please Note: This does not happen MS-SQL server (it works fine with this), only Access. Please Help, Steve Tag: DAAB + Master/Detail Tables with SQL Identity field on master Tag: 130991
  - 11
    - Invalid cast on SqlConnection.open I am seeing this on some of my Windows 2000 servers. This is a web application and I have to restart IIS admin to allow the user to get connections again. I am assuming this is clearing up the connection pool. This is not the error I would expect for a connection leak. Anybody know what would cause this? System.invalidcastexception: Specified cast is not valid. at System.data.sqlclient.counter.modify(int64 add) at system.data.sqlclient.connectionpool.getconnection(Boolean& isInTransaction) at system.data.sqlclient.sqlconnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) at system.data.sqlclient.sqlconnection.open() Tag: DAAB + Master/Detail Tables with SQL Identity field on master Tag: 130988
  - 12
    - tell VB when new row inserted in SQL server I am running SQL server 2005 and want to be notified in VB when a new row is inserted on a particular table. Can I link to the table in visual express 2005 and have an event fired when there is an insert on a particular table? thanks j Tag: DAAB + Master/Detail Tables with SQL Identity field on master Tag: 130983
  - 13
    - Get related rows for a typed datarow Hi, How do I get the related datarows for a typed datarow? Thanks Henrik Tag: DAAB + Master/Detail Tables with SQL Identity field on master Tag: 130980
  - 14
    - converting system.datatype to DbType Is there any dotnet function that will convert an object of type System.DataType to a IDataParameter.DbType enumeration? When I read off a table schema (IDataReader.GetSchemaTable) the data types for each column in the table are returned as System.DataType. I would like to write a function to create IDbDataParameters for sql queries generically according to the type stored in the table schema. or am i missing something in GetSchemaTable that's already storing the DbType and so I dont have to do the conversion. thanks Tag: DAAB + Master/Detail Tables with SQL Identity field on master Tag: 130978
  - 15
    - Connection Pooling Hi, I have an application, which opens many connection and after completing work never closes connection. This is becoming a problem on sql server side. I am thinking to write a tool which will close all the sleeping connection at one run. Is this possible ?. how will I get a info about connection from sql server? Kishor Tag: DAAB + Master/Detail Tables with SQL Identity field on master Tag: 130974
  - 16
    - How to export data from SQL Server to Excel in .net Hello All, I want to do bulk export using ado.net from SQL Server database to MS Excel document. I would highly appreciate small examle showing how to do this. Best Regards, Neo Tag: DAAB + Master/Detail Tables with SQL Identity field on master Tag: 130972
  - 17
    - 2 DataGrids with using same SQL Server table I have a windows form with two datagrid objects. The goal is to display rows from the same SQL Server table, using different SELECT commands. Here's a sample of the code: Data Grid 1: ---------------------------------------------------------------------------------------------------------------------------------- strCMD = @"SELECT ID, ClassID, ClassCode, StartDate, EndDate" + @" FROM sessions" + @" WHERE StartDate < GetDate()" + @" ORDER BY StartDate, ClassCode"; myCN = new System.Data.SqlClient.SqlConnection(); myCN.ConnectionString = frmMain.strConn; myCN.Open(); myDA1 = new System.Data.SqlClient.SqlDataAdapter(strCMD, myCN); myDS1 = new DataSet(); myDA1.Fill(myDS1, "sessions"); this.myDA1.TableMappings.AddRange(new System.Data.Common.DataTableMapping[] { new System.Data.Common.DataTableMapping("Table", "sessions", new System.Data.Common.DataColumnMapping[] { new System.Data.Common.DataColumnMapping("ID", "ID"), new System.Data.Common.DataColumnMapping("ClassID", "ClassID"), new System.Data.Common.DataColumnMapping("ClassCode", "ClassCode"), new System.Data.Common.DataColumnMapping("StartDate", "StartDate"), new System.Data.Common.DataColumnMapping("EndDate", "EndDate")})}); dataOrgs1.DataSource = myDS1; dataOrgs1.DataMember = "sessions"; dataOrgs1.SetDataBinding(myDS1, "sessions"); ---------------------------------------------------------------------------------------------------------------------------------- DataGrid 2: ---------------------------------------------------------------------------------------------------------------------------------- strCMD = @"SELECT ID, ClassID, ClassCode, StartDate, EndDate" + @" FROM sessions" + @" WHERE Status = 'Open Reg'" + @" ORDER BY StartDate, ClassCode"; myCN = new System.Data.SqlClient.SqlConnection(); myCN.ConnectionString = frmMain.strConn; myCN.Open(); myDA = new System.Data.SqlClient.SqlDataAdapter(strCMD, myCN); myDS = new DataSet(); myDA.Fill(myDS, "sessions"); this.myDA.TableMappings.AddRange(new System.Data.Common.DataTableMapping[] { new System.Data.Common.DataTableMapping("Table", "sessions", new System.Data.Common.DataColumnMapping[] { new System.Data.Common.DataColumnMapping("ID", "ID"), new System.Data.Common.DataColumnMapping("ClassID", "ClassID"), new System.Data.Common.DataColumnMapping("ClassCode", "ClassCode"), new System.Data.Common.DataColumnMapping("StartDate", "StartDate"), new System.Data.Common.DataColumnMapping("EndDate", "EndDate")})}); grdOrgs.DataSource = myDS; grdOrgs.DataMember = "sessions"; grdOrgs.SetDataBinding(myDS, "sessions"); ---------------------------------------------------------------------------------------------------------------------------------- If I comment out one, the active datagrid displays the right rows. If I make both active, the first data grid displays the same rows as the second datagrid. The only difference is the number of rows displayed. Any ideas what I'm missing here? Tag: DAAB + Master/Detail Tables with SQL Identity field on master Tag: 130971
  - 18
    - ADO.NET forces unload ODBC Driver Come across a problem with VB.net application using an ODBC Driver. On application shutdown, it forces unloading the ODBC Driver without giving enough time for the driver to handle DLL_PROCESS_DETACH (or CWinApp::ExitInstance) We have our own ODBC Driver and found a problem with lengthy driver unloading process. The ODBC Driver takes more than 3-5 seconds to clean up during DLL unloading. And it looks like ADO.NET/.NET interrupts normal unloading process, if it takes more than 2 seconds (or about). We tried to emulate lengthy unloading process with normal DLL (not ODBC driver) and DllImport directive in C#, but it seems to work fine. We tried to use others (SQLite) ODBC driver source code and we get the same behaviour from Ado.net Thanks! Tag: DAAB + Master/Detail Tables with SQL Identity field on master Tag: 130970
  - 19
    - Change in Typed Datasets between 1.x and 2.x I know/gather that the changes between typed datasets between 1.x and 2.x are pretty significant, but there's one specific thing I could do in 1.x and I can't figure out how to do it in 2.x, or if it is still possible. There is a stored procedure that returns multiple result sets (4 in particular). In 1.x, I could drag and drop the stored procedure from Server Explorer and it would create 4 'tables' for me, and then I'd make my edits, etc. In 2.x, it only shows the first table. There are many ways that I know will give me workarounds to get to the end result, and I've taken care of this specific case already, I guess I'd just like to know if there's a quick way to get the 1.x functionality for future cases. TIA, jdn Tag: DAAB + Master/Detail Tables with SQL Identity field on master Tag: 130967
  - 20
    - how to lock the counter table if using OLE DB database Hi, all, I'm having problem on multi user environment, currently I have over 20 concurrent users creating records on Access database table, because my database have a Counter table for generating Record ID. My problem is using ADO.net, i need to generate Record ID in 2 steps: Step 1) use dataset or data reader to get the next record ID Step 2) use dataAdapter to update next record ID In multi user environment, anyway to lock this Counter table such that I can ensure the above steps perform as I expected?? I know if I lock the table, two users may have change to read the same Record ID. Tag: DAAB + Master/Detail Tables with SQL Identity field on master Tag: 130964
  - 21
    - SQL Parameter bug I am having a function which accepts Database parameter array, and it inserts to the database inside a loop. Note: inside the loop i am changing the values of the parameter array . it works fine if the loop executes for only one time, but if the loop executes more than one time i am getting the error : "field xyz is already contained by another SqlParameterCollection" Function abc(Parameter_Array) begin loop alter Parameter_Array' values Create command object Add parameters to the command object //i am getting error at this point,if this is executed for the second time . Execute command.parameters.close(); set connection,command etc.. to null end Tag: DAAB + Master/Detail Tables with SQL Identity field on master Tag: 130958
  - 22
    - Oracle behaving strangely with multiple commands executing in a tight loop We've run into a very strange issue when running against Oracle using System.Data.OracleClient. As part of an import process that we're running we're doing a number of validations on data that has already been inserted into our database. For each validation within our import process we're logging errors by doing something along the lines of: INSERT INTO ImportMessages (ErrorContext, ErrorType, Timestamp, RowId) SELECT Col1, 1, CurrentDate, RowIdColumn FROM TableWithImportedRecords WHERE {validation condition} We subsequently update each invalid record in our bulk import table with a simple UPDATE statement. UPDATE BulkImport SET IsValid = 0 WHERE {validation condition} We have approximately 15 validations that are run against the database in sequence. foreach(ImportValidation validation in validations) { validation.Validate(); // this executes the two SQL statements above errorCount = validation.ErrorCount; warningCount = validation.WarningCount; } When we run our imports we're getting extemely inconsistent (random) behavior from Oracle. When we execute the process against Sql Server everything works reliably and consistently. As part of our debugging efforts we tracked things down to the foreach() loop shown above and found that there is some sort of timing issue when we execute our commands against Oracle. When we run in the debugger with a breakpoint set on the errorCount line everything works perfectly, but, as soon as we take the breakpoint off the code results in extemely inconsistent and incorrect results. As an experiment we placed a Thread.Sleep(500) statement inside the loop which resulted in everything working properly. What could be happening when those statements are executed against Oracle that would cause them to return unexpected results when run in a tight foreach loop? Are there any Oracle specific settings that I should look into (auto-commit?)? Any ideas? I'm fresh out Tag: DAAB + Master/Detail Tables with SQL Identity field on master Tag: 130954
  - 23
    - How to increase the width of Crystal report. Hi all, how to increase the width of crystal report of an application. while displaying the columns. thanks in advance, sai.t Tag: DAAB + Master/Detail Tables with SQL Identity field on master Tag: 130921
  - 24
    - Parameters and CrystalReports. I am new to CrystalReports and I just want to use the print functionality. I am currently passing a data set and that works just fine: Dim rpt As New QuoteDetail rpt.SetDataSource(ds) rpt.PrintToPrinter(1, False, 0, 0) rpt.Dispose() I have added some parameter fields because the existing printout needs to be modified. How do I add a parameter to Crystal Reports? I have already added the parameters to the report and now when I execute the above lines I get an exception indicating that there are no parameters. I have parameters such as Terms QuoteFile PreparerName Which are all strings. If I just want to set the parameter 'Terms' to something like "NET 30" how do I do that? I have tried rpt.Parameter_Terms.CurrentValues.AddValue("Net 30") But that does not seem to add the parameter. Any suggestions? Kevin Tag: DAAB + Master/Detail Tables with SQL Identity field on master Tag: 130914
  - 25
    - Command Timeout on Queries I'm reading mixed opinions on the effect of a command timeout, and wanted to asked for clarification. From what I understood (and from what my testing seems to indicate), CommandTimeout affect non-queries executes only. Therefore if ADO was calling an SP, and the Command object was set to 1 second timeout, and a Command.ExecuteReader was then called, and the SP required 20 seconds to pull the data, there would be no timeout and the data would be returned. However, if an SP required 20 seconds to perform (an update or whatever), and a Command.ExecuteNonReader was called, then it would timeout. Again, I'm not 100% sure, especially after reading so many differing opinions on this. Can someone clarify? THe reason I ask... we are running into "Timeout" issues sometimes with SqlDataReaders and some suggest bumping up the CommandTimeout. I'm thinking its a different problem. Tag: DAAB + Master/Detail Tables with SQL Identity field on master Tag: 130912
- Next
  - 1
    - Efficient Filling Multiple DataTables Hi all, I am transitioning from ADO.NET 1.1 to 2.0. One of my 1.1 practices when filling a dataset with several related datatables was to explicitly open the connection, call fill for each dataAdapter involved, explicitly close the connection. This saved the overhead of the connection opening and closing for every dataAdapter.Fill. In 2.0, if I fill via TableAdapters, this approach could get pretty convoluted. Is it worth pursuing? Any known examples of an efficient approach to this situation? Thanks. -- John Tag: DAAB + Master/Detail Tables with SQL Identity field on master Tag: 130908
  - 2
    - Polling a database - Performance question I created a couple of custom performance monitor counters. Basically I have a service that runs and updates the counter on an interval as often as once per second. So the question is, for performance on the SQL Server and the app itself, so I establish the connection once and maintain it until the service is stopped, or do I reconnect every time the counters get updated? (It's a small query, just reading blocks and database names from a couple of the master tables.) Thanks. Jerry Tag: DAAB + Master/Detail Tables with SQL Identity field on master Tag: 130905
  - 3
    - Can anyone See where I'm going wrong? Hi Everyone, Can anyone see where I'm going wrong? Win [C#] OleDbConnection thisConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\HOST 017.MDB"); thisConnection.Open(); OleDbDataAdapter thisAdapter = new OleDbDataAdapter("SELECT * from TableTemplate", thisConnection); OleDbCommandBuilder thisBuilder = new OleDbCommandBuilder(thisAdapter); DataSet thisDataSet = new DataSet(); thisAdapter.Fill(thisDataSet,"TableTemplate"); MessageBox.Show("Before Change:" + thisDataSet.Tables["TableTemplate"].Rows[1]["Catagory"]); thisDataSet.Tables["TableTemplate"].Rows[1]["Catagory"] = "100"; thisAdapter.Update(thisDataSet, "TableTemplate"); MessageBox.Show("After Change: " + thisDataSet.Tables["TableTemplate"].Rows[1]["Catagory"]); Error being generated is : Syntax error(missing operator) in query expression "((ID = ?) and ((? IS NULL AND Nam IS NULL) or (NAME = ?) AND.... It keeps ong on from there. But any and all help would be appreciated. Thanks MikeY Tag: DAAB + Master/Detail Tables with SQL Identity field on master Tag: 130903
  - 4
    - 2100 sqlparameter Hallo Ich möchte einen bulk insert mittels eines SqlCommands und dazugehörigen SqlParametern machen. Dazu habe ich innerhalb einer for schleife einen Stringbuilder mit insert statements gefüllt. Ebenfalls habe ich innerhalb der forschleife die Sqlparameter erzeugt. Danach den CommandText mit dem Inhalt des Stringbuilders gefüllt und das ganze ausgeführt. Leider hat es den Anschein das ich nicht mehr als 2100 SqlParameter benutzen kann. Es gibt immer eine Exception;) Kennt jemand das Problem und gibt es einen workaround? Ich benutze Dotnet1.1 und SqlServer 2000 thx Björn Beispiel Code: for (int i=1;i<526;i++) { sql.Append("insert into tabelle (name,text,nummer,num2) values (@name"+i+",@text"+i+",@num"+i+",@numm"+i+"); "); sqlCom.Parameters.Add("@name"+i,name); sqlCom.Parameters.Add("@text"+i,text); sqlCom.Parameters.Add("@num"+i,num); sqlCom.Parameters.Add("@numm"+i,num2); } sqlCom.CommandText = sql.ToString(); sqlCom.ExecuteNonQuery(); Tag: DAAB + Master/Detail Tables with SQL Identity field on master Tag: 130899
  - 5
    - Ado.Net and Access Is there any easy way of creating a new Access file and adding a table to this new Access file from within a c# application? Thanks Ferdinand Tag: DAAB + Master/Detail Tables with SQL Identity field on master Tag: 130898
  - 6
    - DataReader, Update the same reading row ? Hi everyone, I have a small problem, I am reading the data using the datareader. and I want to update the same row later using a different connection and a transaction. When I update the same row which is currently loaded to the datareader it blocks the whole thing and waits.. ? how do i update a data row which is currently read by the datareader ? thanks, -Aruna Tag: DAAB + Master/Detail Tables with SQL Identity field on master Tag: 130891
  - 7
    - Remote IDbConnections, opening/closing and errors Hi Im newish with databases so I'd thought I'd ask a general question. Im using a remote MySQL database accessed across the internet with dotnet provider components. The code I've written creates a new connection for each transaction on the database then closes it again afterwards. So there are many Connection.open/close during the lifetime of my application. I'm wondering if I should be doing this or if I should only open the connection when my database wrapper object is instantiated and close it when it is destroyed. The reason I did it this way was because I was worried about loss of network after the connection was opened. I've no idea about what possible errors this would cause and also how to write nice error handling for a constantly opened connection that dies during the objects lifetime. I also don't know what performance hits that constant opening and closing would cause. I would appreciate that someone with experience please advise me about how to handle this. thanks Claire Tag: DAAB + Master/Detail Tables with SQL Identity field on master Tag: 130890
  - 8
    - Dataset to excel Hi and TIA! I have a recordset retrieved into a datgrid in my web app, from an SQL database that I want to save as an excel formatted file. I would like the user to click a button, get a prompt to save/name the file. Is this possible and if so where can I find information on accomplishing this task. Any advice is appreciated. Using (.Net 1.1 / VS2003 / SQL Server 2000 / WinXP Pro). I posted to several groups cause not sure the best place to ask the question. Thanks for your time. -- ****************** Reggie Tag: DAAB + Master/Detail Tables with SQL Identity field on master Tag: 130886
  - 9
    - Dataset to excel Hi and TIA! I have a recordset retrieved into a datgrid in my web app, from an SQL database that I want to save as an excel formatted file. I would like the user to click a button, get a prompt to save/name the file. Is this possible and if so where can I find information on accomplishing this task. Any advice is appreciated. Using (.Net 1.1 / VS2003 / SQL Server 2000 / WinXP Pro). I posted to several groups cause not sure the best place to ask the question. Thanks for your time. -- ****************** Reggie Tag: DAAB + Master/Detail Tables with SQL Identity field on master Tag: 130885
  - 10
    - Help Updating Northwind Syntax Win[C#] OLEDB Hi Everyone, Even though this is a OLE question, hopefully someone can help me. I'm having problems with my syntax on the updating part for the Northwind.MDB. All other code seems fine. It has to do with the Order Details on the thisAdapter.Update( ). Or I believe the problem lies with with space between Order and Details and not knowing how to get around that problem. I would like to keep with the space being left in-between the two names. The error I'm getting is The error that it is triggering is "Update unable to find TableMapping['[Order Details]'] or Data Table '[Order Details]'. Order Details is just one table. Is There a way to use a name space name without a hassle? My Code is as follows: Yes & sorry I have posted on another forum, but to no avail. So any and all help is appreciated. Mike private void DataUpdate() { try { OleDbConnection thisConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Northwind.MDB"); thisConnection.Open(); OleDbDataAdapter thisAdapter = new OleDbDataAdapter("SELECT * FROM [Order Details]", thisConnection); OleDbCommandBuilder thisBuilder = new OleDbCommandBuilder(thisAdapter); DataSet thisDataSet = new DataSet(); thisAdapter.Fill(thisDataSet, "Order Details"); thisDataSet.Tables["Order Details"].Rows[0]["Quantity"] = "99"; thisAdapter.Update(thisDataSet, "[Order Details]"); thisConnection.Close(); } catch(Exception myExcept) { MessageBox.Show(myExcept.Message); } } Tag: DAAB + Master/Detail Tables with SQL Identity field on master Tag: 130884
  - 11
    - =?utf-8?B?UmU6IERldGVybWluaW5nIGZpZWxkIHR5cGUgQ0hBUiB2cyBWQVJDSEFS?= As an alternative to being specific to SQL Server, you might want to look at DbDataReader.GetSchemaTable(). It provides a mechanism to get the schema of a SqlDataReader (which might be a good way of getting this data). No way that I know of to get this from a DataAdapter/DataSet/DataTable, but does work across managed providers. Thanks, Shawn Wildermuth Wildermuth Consulting Services, LLC http://adoguy.com C# MVP, MCSD.NET, Author and Speaker > -----Original Message----- > From: Jared [mailto:google@tripletreesoftware.com] > Posted At: Thursday, July 13, 2006 6:35 PM > Posted To: microsoft.public.dotnet.framework.adonet > Conversation: Determining field type CHAR vs VARCHAR > Subject: Re: Determining field type CHAR vs VARCHAR > > > Hi Marina, > > I was hoping this would be exposed in DataColumn, but > querying the system tables should work, too. Thanks for your > quick response! > > Jared > > Marina Levit [MVP] wrote: > > You can query the sql server system tables to find out. Try > looking at > > syscolumns. > > > > > Tag: DAAB + Master/Detail Tables with SQL Identity field on master Tag: 130880
  - 12
    - =?utf-8?B?UmU6IFNRTCBTZXJ2ZXIgQXV0aGVudGljYXRpb24gdmVyc3VzIFdpbmRvd3MgQXV0aGVudGljYXRpb24=?= Its more complicated than that unfortunately. Using SQL Server authentication has some security implications so it is generally better (IMHO) to use Windows Authentication to SQL Server, though by using a Integrated Security you are using the current principal running the ASP.NET page. In general this is either a very limited user (e.g. Network Service under W2K3) or a specific windows principal. If you are using Windows Authentication on your ASP.NET (e.g. Intranet projects), then integrated security means that your actual users are authenticating to the SQL Server. This is good and bad. Bad in that you need to make sure they have access, but good if you need to audit database changes as it will be annotated with who made what changed in audit logs. But this is a usually not the case. I think the best practice with ASP.NET is to use a user account to authenticate with instead of SQL Server security. Whether you use integrated security to pass the credentials to the SQL Server is up to you, but in either case the user should probably be somewhat limited in access to the database to prevent intrusions into the website causing havok. Does this make any sense? Thanks, Shawn Wildermuth Wildermuth Consulting Services, LLC http://adoguy.com C# MVP, MCSD.NET, Author and Speaker > -----Original Message----- > From: supvine@gmail.com > Posted At: Thursday, July 13, 2006 5:14 PM > Posted To: microsoft.public.dotnet.framework.adonet > Conversation: SQL Server Authentication versus Windows Authentication > Subject: SQL Server Authentication versus Windows Authentication > > > Hello, > > I am hearing conflicting point of views regarding ASP.Net > development - some folks say that when connecting to a SQL > Server via ASP.Net it is better to connect using Integrated > Windows Authentication (trusted > connection) while others say that it is better to connect > using SQL Server Authentication. > > The two modes mean the following to me - > > Windows Authentication Mode - meaning a domain account given > permission to a particular SQL Server database. > > SQL Server Authentication Mode - meaning an user created in > SQL Server itself and given specific permissions to a database. > > Could someone provide me with some direction regarding these > two options and what are the advantages and disadvantages of > each of these? > > Thanks for your help regarding this. > > > Tag: DAAB + Master/Detail Tables with SQL Identity field on master Tag: 130876
  - 13
    - SQL Server Authentication versus Windows Authentication Hello, I am hearing conflicting point of views regarding ASP.Net development - some folks say that when connecting to a SQL Server via ASP.Net it is better to connect using Integrated Windows Authentication (trusted connection) while others say that it is better to connect using SQL Server Authentication. The two modes mean the following to me - Windows Authentication Mode - meaning a domain account given permission to a particular SQL Server database. SQL Server Authentication Mode - meaning an user created in SQL Server itself and given specific permissions to a database. Could someone provide me with some direction regarding these two options and what are the advantages and disadvantages of each of these? Thanks for your help regarding this. Tag: DAAB + Master/Detail Tables with SQL Identity field on master Tag: 130875
  - 14
    - Determining field type CHAR vs VARCHAR Is there a way to programmatically determine if a SQL Server field is of type CHAR vs. VARCHAR? I'm able to use the DataColumn.DataType property to determine that a field is a string, and to retrieve its max length. As far as I can tell, however, both CHAR and VARCHAR fields look the same when using the DataColumn object. This for a stored procedure code generator that is complete except for this one feature. TIA Jared Tag: DAAB + Master/Detail Tables with SQL Identity field on master Tag: 130873
  - 15
    - How to execute SP with type of Table.. Guru's, I have oracle sp with the below types defined, and my sp uses these types for outparameter, i am using DAAB. Need to know how to execute this kind of SP. highly appreciate if you can get me solution for this.. Thanks in advance.. ------- TYPE SECTTABTYPE IS TABLE OF VARCHAR2(5) INDEX BY BINARY_INTEGER; TYPE MCHTABTYPE IS TABLE OF VARCHAR2(7) INDEX BY BINARY_INTEGER; TYPE NUMTABTYPE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; ------------ PROCEDURE GET_CAP( P_SECTIONS IN VARCHAR2, P_NUM_DAYS IN NUMBER, P_PROC_ALL_MCHS IN VARCHAR2, P_SECTS OUT SECTTABTYPE, P_MCHS OUT MCHTABTYPE, P_RQDS OUT NUMTABTYPE, P_PLANCAPS OUT NUMTABTYPE) IS --some code ******** // .Net code for executing SP using DAAB.. DBCommandWrapper dbCommandWrapper = db.GetStoredProcCommandWrapper("dcp_PKG.get_cap"); dbCommandWrapper.AddInParameter("P_SECTIONS", DbType.String, section); dbCommandWrapper.AddInParameter("P_NUM_DAYS", DbType.Int32,NumDays); dbCommandWrapper.AddInParameter("P_PROC_ALL_MCHS", DbType.String, Mac); dbCommandWrapper.AddOutParameter("P_SECTS", DbType.String,20); dbCommandWrapper.AddOutParameter("P_MCHS", DbType.String,24); dbCommandWrapper.AddOutParameter("P_RQDS", DbType.Int32,4); dbCommandWrapper.AddOutParameter("P_PLANCAPS", DbType.Int32,4 ); //Execute the stored procedure db.ExecuteDataSet(dbCommandWrapper); Tag: DAAB + Master/Detail Tables with SQL Identity field on master Tag: 130870
  - 16
    - Any way to avoid using SP_EXECUTESQL in ADO.NET? Hi, Our development team has encountered some performance problems when using ADO.NET (using the SqlCommand and SqlDataAdapter objects) to perform queries on large data tables. We've drilled into the issue considerably, and the problem seems to be related to how ADO.NET uses the SP_EXECUTESQL when running text queries. I say this because when we run our query in Query Analyzer by itself the performance is less than a second, but when running it with the SP_EXECUTESQL command (which is what Profiler is telling us is being used), it takes nearly half a minute. >From what I know about SP_EXECUTESQL, it uses dynamic cursors to execute the command it is given. Our query involves User Defined Functions (UDFs) that call UDFs, Group By statements, and Aggregate functions that are not necessarily good for dynamic cursors (or at least that is what I have heard), so the issue seems to make sense. Anyways, what I was wondering was whether or not there is a way to have ADO.NET run our query without using SP_EXECUTESQL. If this is not possible, then I am interested in knowing whether or not it is a bad practice to have UDFs calling other UDFs, as opposed to having a larger query that does all the work but is not as reusable. Thanks for your time Tag: DAAB + Master/Detail Tables with SQL Identity field on master Tag: 130869
  - 17
    - DataAdapter not updating underlying SQL Database! Help! Thanks in advance! I'm using Visual studio.net 2005 I can't get the underlying table (incoming) to update. I modify the datarows, checking the database before and after running (the code gets several hits while iterating the records), and it doesn't update it. What am I doing wrong? Sub scrubTrackedExclusions() 'Dim contactInfo As New contactStruct Dim sqlFindTracked As New SqlCommand 'Dim sqlUPDATE As New SqlCommand Dim intRec As Integer = 1 Dim dtIncoming As New DataTable Dim dtTracked As New DataTable Dim dtExclusions As New DataTable Dim dtCurrad As New DataTable Dim drvCurrad As System.Data.DataRowView() Dim drvRow As System.Data.DataRow Dim dsCurrent As DataSet = New DataSet Dim dsChanges As DataSet = New DataSet Dim drIncoming As DataRow Dim drTracked As DataRow Dim drExclusions As DataRow Dim dcPK1(0), dcPK2(0), dcPK3(0) As DataColumn Dim i As Integer = 0 Dim strSQL As String = "SELECT * FROM Tradoc.dbo.Incoming WHERE op = 'INSERT' or op = 'UPDATE'; " _ & "SELECT * FROM TRACKED Where Status = 'ACTIVE'; SELECT * FROM EXCLUSIONS; SELECT * FROM CURRAD;" Dim strSqlUpdate As String = "UPDATE [Tradoc].[dbo].[Incoming] SET [cn] = @cn, " _ & "[ou] = @ou, [dn] = @dn, [op] = @op, [adsPath] = @adsPath, [reasonCode] = @reasoncode " _ & "WHERE [targetAddress] = @targetAddress" opensql(1) Dim sqlCMD As SqlCommand = New SqlCommand(strSQL, sqlConnection1) sqlCMD.CommandTimeout = 30 Dim sqlDA As SqlDataAdapter = New SqlDataAdapter sqlDA.SelectCommand = sqlCMD Dim sqlUpdate As SqlCommand = New SqlCommand(strSqlUpdate, sqlConnection1) sqlUpdate.CommandType = CommandType.Text sqlDA.UpdateCommand = sqlUpdate sqlDA.UpdateCommand.Parameters.Add(New SqlParameter("@cn", SqlDbType.VarChar, 100, "CN")) sqlDA.UpdateCommand.Parameters.Add(New SqlParameter("@ou", SqlDbType.VarChar, 400, "ou")) sqlDA.UpdateCommand.Parameters.Add(New SqlParameter("@dn", SqlDbType.VarChar, 400, "dn")) sqlDA.UpdateCommand.Parameters.Add(New SqlParameter("@op", SqlDbType.VarChar, 10, "op")) sqlDA.UpdateCommand.Parameters.Add(New SqlParameter("@adsPath", SqlDbType.VarChar, 400, "adsPath")) sqlDA.UpdateCommand.Parameters.Add(New SqlParameter("@reasonCode", SqlDbType.VarChar, 50, "reasonCode")) sqlDA.UpdateCommand.Parameters.Add(New SqlParameter("@targetAddress", SqlDbType.VarChar, 400, "targetAddress")) sqlDA.Fill(dsCurrent, "Table") dsCurrent.Tables(0).TableName = "Incoming" dsCurrent.Tables(1).TableName = "Tracked" dsCurrent.Tables(2).TableName = "Exclusions" dsCurrent.Tables(3).TableName = "CurrAD" dtIncoming = dsCurrent.Tables("Incoming") dtTracked = dsCurrent.Tables("Tracked") dtExclusions = dsCurrent.Tables("Exclusions") dtCurrad = dsCurrent.Tables("currAD") dcPK1(0) = dsCurrent.Tables("Incoming").Columns("targetAddress") dcPK2(0) = dsCurrent.Tables("Tracked").Columns("targetAddress") dcPK3(0) = dsCurrent.Tables("Exclusions").Columns("targetAddress") 'dcPK4(0) = dsCurrent.Tables("Currad").Columns("targetAddress") dtIncoming.PrimaryKey = New DataColumn() {dtIncoming.Columns("TargetAddress")} dsCurrent.Tables("Incoming").PrimaryKey = dcPK1 dtTracked.PrimaryKey = New DataColumn() {dtTracked.Columns("TargetAddress")} dsCurrent.Tables("Tracked").PrimaryKey = dcPK2 dtExclusions.PrimaryKey = New DataColumn() {dtExclusions.Columns("TargetAddress")} dsCurrent.Tables("Exclusions").PrimaryKey = dcPK3 'dtCurrad.PrimaryKey = New DataColumn() {dtCurrad.Columns("TargetAddress")} 'dsCurrent.Tables("Currad").PrimaryKey = dcPK4 Dim dvCurrad As DataView = New DataView(dsCurrent.Tables("Currad"), "", "targetAddress", DataViewRowState.CurrentRows) 'Example find... ' drFIND = dtTracked.Rows.Find("SMTP:brett.mack@usaac.army.mil") Dim iIndex As Integer = 1 For Each drIncoming In dtIncoming.Rows lblGeneral.Text = "Scrubbing record #: " & intRec & " for Tracking & Exclusions." 'Check & modify info if it's in the tracked database... drTracked = dtTracked.Rows.Find(drIncoming.ItemArray(8).ToString) 'Dim intIndex As Integer = dvAllad.Find(strcn) If Not drTracked Is Nothing Then 'If we get a hit, then check the current contacts db 'for a target address match... drvCurrad = dvCurrad.FindRows(drIncoming.ItemArray(8).ToString) 'Okay, if we get a hit on the targetAddress on the Current 'contacts, check to see if the objectGuid matches. If it matches, 'Go ahead and modify the data and set the op to Update... 'Check if it's empty... If drvCurrad.Length >= 1 Then For i = 0 To drvCurrad.Length 'If not, check for a Guid match.... If drTracked.Item(6).ToString = drvCurrad(i).Row(6).ToString Then 'if it matches, reset the appropriate incoming columns drIncoming.Item(1) = drTracked.Item(1) drIncoming.Item(25) = drTracked.Item(25) drIncoming.Item(26) = drTracked.Item(26) drIncoming.Item(27) = "UPDATE" drIncoming.Item(28) = drTracked.Item(28) drIncoming.Item(29) = "TRACKED" Else 'Not yet implemented End If Next End If drExclusions = dtExclusions.Rows.Find(drIncoming.ItemArray(8).ToString) 'Check & see if the contact is in the Excluded database. If so, then 'go ahead and mark it as excluded; this way it won't be touched. If Not drExclusions Is Nothing Then 'drIncoming.Delete() drIncoming.Item(27) = "EXCLUDED" drIncoming.Item(29) = "EXCLUDED" drIncoming.Item(33) = "EXCLUDED" End If iIndex += 1 intRec += 1 End If Next 'This is where I'm pretty sure it should update everything! dsChanges = dsCurrent.GetChanges If Not dsChanges Is Nothing Then sqlDA.Update(dsChanges, "Incoming") dsCurrent.Merge(dsChanges, True) End If sqlCMD.Dispose() sqlDA.Dispose() 'contactInfo = Nothing lblRejects.Text = "REJECTED " & intRejects & " Records." 'sqlReader.Close() closesql(1) End Sub Tag: DAAB + Master/Detail Tables with SQL Identity field on master Tag: 130860
  - 18
    - Help - DataAdapter.Update is not updating Access database Hi all, I'm trying to update a table in an Access database with the records in a table from another Access database that both tables have exact schema. Table one has about 10,000 records and table two usually has 10-20 records. Here is the sample code that works for the most part, but not for the place that changes need to be sent back to the database: 'Table one Dim daGetRecords As New OleDbDataAdapter Dim dsGetRecords As New DataSet daGetRecords.SelectCommand = New OleDbCommand(strSQL, cn) daGetRecords.Fill(dsGetRecords) 'Table 2 from different database, using the same sql statement different conection Dim daRecords As New OleDbDataAdapter Dim dsRecords As New DataSet daRecords.SelectCommand = New OleDbCommand(strSQL, cn2) daRecords.Fill(dsRecords) 'Here I assign primary keys of two tables ' ' 'And merge rows Dim dr As DataRow For Each dr In dsRecords.Tables(0).Rows Dim drow(0) As DataRow drow(0) = dr dsGetRecords.Merge(drow) Next ' I've checked and dsGetRecords has been updated correctly. ' I'm ready to send changes back to the database. ' And this is the place that I'm having problem daGetRecords.Update(dsGetRecords) Please comment: 1. Why update method of dataadapter is not updating database? 2. Is there a better way to do the whole process? Thanks in advance, Roy Tag: DAAB + Master/Detail Tables with SQL Identity field on master Tag: 130859
  - 19
    - Adding column description to Access table, HOW? Hi I'm trying to add a column and it's description to an Access table using the following code: objTable.Columns.Append("RefId", adInteger) objTable.Columns("RefId").Properties("Description").Value = "Reference Id" however I get the following exception: "Item cannot be found in the collection corresponding to the requested name or ordinal." Looking at the Column using a debugger, I can see that the column doesn't contain anything in the Properties collection... But how do I add a Description to this collection ?? TIA Søren Tag: DAAB + Master/Detail Tables with SQL Identity field on master Tag: 130856
  - 20
    - Async Database methods with a Oracle or Postgres The ADO.NET 2.0 SqlDataProvider provides a nice set of async methods to perform database access. Unfortunatly this is the only database provider that I can find that supports these. Is anyone aware of an Oracle implementation that provides Begin/End versions of the Execute Methods? I need a real implementation of this, something similar to the SqlClient approach - so that callbacks happen on IOCP threads and threadpool threads aren't sitting blocked in a Wait the entire time a db operation is pending. It's somewhat surprising that Microsoft didn't provide this generically in the ADO.Net infrastructure, but rather grafted it directly into the SqlDataProvider. -- Chris Mullins Tag: DAAB + Master/Detail Tables with SQL Identity field on master Tag: 130851
  - 21
    - How to Send XML to client I am using VS2005 to develop Asp.Net program. I use the codes below to create a XML stream: How can I set the Stream to client? //----------------------------------------------------------------------------- MemoryStream sm= new MemoryStream(); dsHealth.WriteXml(sm); Tag: DAAB + Master/Detail Tables with SQL Identity field on master Tag: 130849
  - 22
    - Foreign key constraints from DB at runtime I'm trying to figure out how to discover foreign key constraints at runtime by asking the database. All the code samples I see in .NET have you manually create the foreign key constraints. I'm trying to detect them from the database's schema. I'm working with MySQL4.1. When I query the database, I get my UniqueConstraints but I don't get my ForeignKeyConstraints, and wondered if anyone knew how to do it. In my program, the business objects are associated in the database with a main table, and collections within this object are associated by foreign key constraints to other tables. So say I have an object, User. It's associated with the table "user", which is identified by a unique integer key, ObjectId. It contains no foreign key constraints. A User can belong to any number of Groups, and a Group can contain any number of Users, so I have a "groups" table keyed by its own integer ObjectId. It also contains no foreign key constraints. Connecting the two is a "usergroups" table, also keyed by integer ObjectId, and all it contains is two foreign keys. "UserId" refers to "user.ObjectId" and "GroupId" refers to "groups.ObjectId". My code looks like this: StringBuilder sb = new StringBuilder(); sb.Append( "SELECT * FROM " ); sb.Append( strTableName ); using ( OdbcCommand cmd = new OdbcCommand( sb.ToString(), connection ) ) { using ( OdbcDataAdapter da = new OdbcDataAdapter() ) { da.SelectCommand = cmd; // Get the schema for this table and add // the schema for any foreign key tables to the // dataset DataTable table = new DataTable( strTableName ); da.FillSchema( table, SchemaType.Source ); ds.Tables.Add( table ); foreach ( Constraint constraint in table.Constraints ) { if ( constraint is ForeignKeyConstraint ) { ... do stuff... } } } } When you run this code on the "usergroups" table, which has two foreign key constraints, no ForeignKeyConstraints exist. I have my UniqueConstraint on UserId but that's it. If I look in the debugger, table.Constraints.Non-Public members.fLoadForeignKeyConstraint is false, which looks like the culpret. Do I need to set this flag to give me the foreign key constraint? If I do, how do I do it? I see there's the DataTable.ParentRelations and ChildRelations properties but I haven't tried using them because I think they just access the ForeignKeyConstraints. I figured if there are no ForeignKeyConstraints in the collection, I'm going to get the same results if I rip out my code and rewrite it to use DataRelations. Does anyone know how to do this? Thanking you for in advance for your time and thoughts. Shelah Tag: DAAB + Master/Detail Tables with SQL Identity field on master Tag: 130846
  - 23
    - Driver's SQLAllocHandle on SQL_HANDLE_ENV failed I'm trying to connect to an R:Base database on my machine. I created a system DSN using the R:Base provider which connects fine. In my project, I can browse the database by adding this connection in the server explorer so I know it connects just fine. However, when trying to use an ODBCConnection in my app, I get the following error when using the open() method on the connection: Driver's SQLAllocHandle on SQL_HANDLE_ENV failed I was thinking that this was some sort of security error, but I gave the ASPNET account security access to the *.RBx files and even the *.dll provider files. Can anybody shed some light? Tag: DAAB + Master/Detail Tables with SQL Identity field on master Tag: 130843
  - 24
    - Connection timeout - Sleeping Processes - Connection Pools? Why oh Why Hi Everyone, I have a problem with a system in production. Basically we are getting timeouts on getting a connection from the connection pool... I will give the background now... We have a Web Server and a Database Server with the below configurations. Web Server Win2k3 IIS6 Database Win2k3 Sql Server 2000 (8.00.2039 - SP4) We use MSMQ on the Web server to do message processing... So we have one HTTP Application sending messages to another HTTP Application. Lets call the first one MessageSender HTTP Application and the other the MessageProcessor HTTP Application. So we have asynchronous Queue Listeners in the MessageProcessor Application, and to make sure we never get overwhelm with messages I have implemented a thread counting approach to make sure we never have too many threads running at one... At the moment I have set the maximum to 5. Each thread calls a stored procedure on the Database... Anyway for some freak reason... the queue listener stopped listening... and we had a backlog of 8000 messages on one queue... When we restarted the MessageProcessor the queue listener was throttles so no more than 5 threads could run and after about 300 or so messages basically it took up the whole connection pool and then started throwing exceptions... I managed to stop the QueueListener before it brought down the whole application... And I had these observations ... There were similar amounts of "sleeping" processes which only ever disappeared when the Application Pool in IIS was recycled or after a considerable amount of time (I am assuming Garbage Collection Kicked in). So the simple answer is that we are not closing the connections... well we went through the whole application and all of the connections are closed... we tried a close and a dispose call on the connections...Which started throwing the below error (even though we do not reuse connection objects in each thread). ExecuteReader requires an open and available Connection. The connection's current state is Closed So we do not know where to look to next... What could be causing the connections to be held and not reused in the connection pool, is it linked with the sleeping processes? Any Help would be muchly appreciated... Regards Mark Tag: DAAB + Master/Detail Tables with SQL Identity field on master Tag: 130840
  - 25
    - Proper resource deallocation through Dispose() Hi All, Trying to get a grip on the proper deallocation of unmanaged resources. I asked some general questions in another group, here's my ADO question: If I call Dispose on a DbDataAdapter, for example, am I correct in understanding that it will dispose of the DbConnection that it uses? This statement in the .Net 2.0 documentation for DbDataAdapter.Dispose() seems to imply that it does: "Releases all resources used by the Component". It would appear that I'm going to run into problems in this scenario: I have a single DbConnection as an instance variable in a class. Within that class, I have a method that creates, and then disposes of, a DbDataAdapter that uses this connection. The second time I call that method, it will fail because the disposal of the adapter also disposed of the connection (as opposed to just closing it). Or am I totally misinterpreting the documentation? Tag: DAAB + Master/Detail Tables with SQL Identity field on master Tag: 130836

Hi,

Assume the following two tables ...
Order_Header
OH_ID Numeric Identity
OH_Cust varchar(20) not null

Order_Lines
OH_ID Numeric (FK on Order_Header)
OL_Line int not null (incremental)
OL_Qty decimal(9,2) not null

These tables are mapped to a typed dataset where the properties for
Order_Header.OH_ID are set to AutoIncrement=True, AutoIncrementSeed=-1 and
AutoIncrementStep=-1.

These datatables are populated using grids and all works well. When I'm
ready to update the DB I call the UpdateDataset(myOrdersDS, "Order_Header",
insert_stmt, update_stmt, delete_stmt, UpdateBehaviour.Standard).

Now my problems start ... I've updated an identity field in the DB and need
to use this to maintain my relationship with the Order_Lines table. How is
this achieved?? If I re-read the updated data back into my Order_Header
DataTable then I will break the relationship, on the other hand, if I don't
know what the new value of OH_ID is how can I update the Order_Lines table?

Perhaps I've gone down the wrong route with this and I would be grateful for
any solutions or suggestions from the group.

Thanks,

Paul

Top