Update and Select in the same statement?

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
    - Problem with ImageButton.OnClientClick when DataBinding I have the following tag in one of my DataList's ItemTemplates: <asp:ImageButton ID="btnViewQuestion" runat="server" BorderWidth="0" ImageUrl="images/qmark.gif" Height="27px" Width="26px" OnClientClick="window.open(<%# DataBinder.Eval(Container.DataItem,"questionid","'viewquestion.aspx?questionid={0}'") %>,'question','scrollbars=yes,width=630,height=480,location=no,menubar=no,status=no,toolbar=no');"/> As you can see, my goal is to use the value from the DataBinding Expression as the first parameter of a JavaScript window.open() method in the OnClientClick property of an ImageButton. However, when I run the application using this, I recieve the following error: Server Error in '/' Application. -------------------------------------------------------------------------------- Parser Error Description: An error occurred during the parsing of a resource required to service this request. Please review the following specific parse error details and modify your source file appropriately. Parser Error Message: The server tag is not well formed. I tried to be careful using the single and double quotes, but I think everything is correct. Did I do something wrong in this server tag? All my other DataBinding Expressions work fine when I remove this tag, and the error says the problem is in this line, so what's the problem? Thanks. -- Nathan Sokalski njsokalski@hotmail.com http://www.nathansokalski.com/ Tag: Update and Select in the same statement? Tag: 140723
  - 2
    - Error: data truncated. Hello, When writing a text to a SQL database nvarchar(6) field, I get following error when the size of the input is larger than the available length of the textfield (larger than 6). String or binary data would be truncated. The statement has been terminated. How can I make that at least the first 6 characters will be saved and the input is indeed truncated without generating an error or at least to catch the error silently? Many thanks and greetings, Michel Tag: Update and Select in the same statement? Tag: 140719
  - 3
    - Typed Dataset: Add expression-based column? Hi, I'm creating a typed dataset based off of a SQL Server 2005 DB. There are 2 fields from one table that I'd like to bind to on an ASP.NET dropdownlist. Since I can't specify two fields in the "Datatextfield" proprety of the control, I'm trying to create an expression-based column. One thing that compilicates things a bit is that one of the fields is actually a number which points to a friendly display value in a different table. There's a foreign-key relationship that the dataset designer (and SQL Server 2005) are both aware of. So I can modify the query that my method uses to include the proper text value from the other table. However, when I try to, in the query, create a single field that combines that text field and the other field (a datetime field), I get an error at runtime complaining of a problem converting between a datetime and a character string: "Conversion failed when converting datetime from character string" Line 1329: this.Adapter.SelectCommand.Parameters[0].Value = ((int)(OwnerOfShoes)); Line 1330: DataSet1.ShoeInstancesDataTable dataTable = new DataSet1.ShoeInstancesDataTable(); Line 1331: this.Adapter.Fill(dataTable); Line 1332: return dataTable; Line 1333: } Should I be trying to architect a solution to this in the SQL query? I'd really like to try to modify the datatable after the SQL query is complete, but it gets a bit more complicated because one of the values needs to be looked up in a related table. I'm not sure where I would add the code. Would I use a partial class to add a new method? Any thoughts on this? Any suggestions for the best way to do this? Thanks. -Ben Tag: Update and Select in the same statement? Tag: 140715
  - 4
    - Using a .net Custom Data Provider and SQL Server 2005 Management Studio Hi, MS have exposed the functionality to use custom data providers in Visual Studio 2005, I've used this and it works fine. I would like to know if it's possible to use a custom data provider from SSMS? I'm not even sure if SSMS uses a .net data provider under the covers, - does anyone know? If it does then does anyone know how SSMS gets a list of databases from the connected instance? Background: We have a specialised solution which manages lots (1000's) of simple databases (for example a questionaire) for clients built on top of SQL 2005. Each of these databases is a virtual database - they arn't real databases in SQL 2005. The data for all the databases is stored vertically in an EAV format in various tables. For convenience I use my custom data provider to execute SQL against the flattened schema, and it internally converts the sql using the schema for each "virtual" database (stored as xml in SQL 2005) to the rather complicated SQL required to access the real underlying tables. Best Regards, Daniel Tag: Update and Select in the same statement? Tag: 140706
  - 5
    - what's the best approach to follow for sql execute Hi There, What's the better way to follow when we use sql string sql = "Inset into table(f1,f2) values (@f1, @f2)"; then add and passing parameters or in the first place itselef assign the values to the string string sql = "Inset into table(f1,f2) values (" + tbName.text + ", " + tbCity.Text +");"; Thanks Ganesh Tag: Update and Select in the same statement? Tag: 140698
  - 6
    - Change connectionstring Typed dataset Hi, I've noticed that if I change my connectionstring in the app.config file, this isn't enough, a second change in the Global.System.Configuration is necesary!!! This means that after deployment and if the SQL server is changed, changing the code is necesary, ..............?.?? can I prevent this?? HOW??? Thx -- Best regards Luc N Tag: Update and Select in the same statement? Tag: 140697
  - 7
    - What does DataSet.AcceptChanges do? If I delete rows in a DataTable and then do a DataSet.AcceptChanges, and then pass that DataSet to the client, will the client have any idea of rows that previously existed? I'm hoping not. If the client would still see those rows (but as deleted), I guess I will have to create a new DataTable and just add new rows that I want the client to see and pass that instead? Thanks, Ron Tag: Update and Select in the same statement? Tag: 140691
  - 8
    - Compare two DataSets? Is there a way I can compare two DataSets to see if they are identical? Thanks, Ron Tag: Update and Select in the same statement? Tag: 140689
  - 9
    - bind variables oracle visual basic I have a query that looks up the highest value for column a and then returns column a and column b. It uses a nested select in the from clause. I want to use bind variables for my parameters but have found out i can't use bind variables in the from clause. It keeps returning no records when i can run this same query in toad and it returns records. My question is can anyone help me wiht how to do this? Here is my code: Dim con As New OracleConnection(cnCICString) Dim cmd As New OracleCommand Dim da As OracleDataAdapter = New OracleDataAdapter(cmd) con.Open() cmd.Connection = con cmd.CommandType = CommandType.Text cmd.CommandText = "select ec.ec_export_number,ec_date from event_campaign ec, " & _ " (select max(ec_export_number) as maxexport from event_campaign" & _ " where EC_PURPOSE = :event_purpose AND EC_ITEM_SENT = :item_sent" & _ " and EC_SQL = :sql_totalcount ) maxresults where " & _ " ec.ec_export_number = maxresults.maxexport and EC_PURPOSE = :event_purpose AND EC_ITEM_SENT = :item_sent " & _ " and EC_SQL = :sql_totalcount" cmd.Parameters.Add(":sql_totalcount", OracleDbType.Varchar2).Value = "select cli_rid from dual" cmd.Parameters.Add(":event_purpose", OracleDbType.Varchar2).Value = "20070511TXAM" cmd.Parameters.Add(":item_sent", OracleDbType.Varchar2).Value = "POSTCARD" Tag: Update and Select in the same statement? Tag: 140687
  - 10
    - It's back - the timeout problem Hi; One of our testers is getting this now - and he is not running under a debugger. The error is: Exception(s):DataSourceException: executeQuery select=select CategoryName from Categories Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. It occurs randomly. And it returns this in under a second so it's unlikely to be an actual timeout. Also this is hitting Sql Server 2005 Express on the user's computer so it can't be a network issue. Any idea what this can be? This code is called when clicking a button in our program which then runs the exact same code each time. This happens occasionally and when it does sometimes it happens again the next 2 or 3 times we click the button and then it's gone and everything is working. -- thanks - dave david_at_windward_dot_net http://www.windwardreports.com Cubicle Wars - http://www.windwardreports.com/film.htm Tag: Update and Select in the same statement? Tag: 140685
  - 11
    - Updated Primary Key (Identity) Value To start, I'm using VB 2005 and SQL Server Express 2005. This almost appears to be a bug, but I've yet to find an actual bug and generally narrow my issues down to user-error on my part. I have the following code snippet (LogFile is a class I've created simply to output to a textfile): --- Dim ta As New mattersTableAdapter Dim dt As New mattersDataTable Dim dr As mattersRow LogFile.WriteLine("Adding new matter...") 'Fill data table ta.Fill(dt) 'Add new data row and update dr = dt.NewmattersRow dr.Item("file_no") = "New Matter" dr.Item("date_created") = Now dr.Item("date_modified") = Now dt.AddmattersRow(dr) ta.Update(dt) dt.AcceptChanges() LogFile.WriteLine("New matter added. Id: {0}.", dr.Item("m_id").ToString) --- As you can probably already tell I'm using a Strongly Typed Dataset. The table schema includes "m_id" being the Primary Key and has the property "Identity Specification" set to Yes and increments by 1. My issue is that if the DataTable is empty after being filled from the database (ie: the database table is empty) I get 0 (zero) returned to "m_id" in the DataRow. If the DataTable already contains existing records and I run this code, I get the appropriate, incremented value. Is there something I'm missing? Thanks in advance for any help... Luke Tag: Update and Select in the same statement? Tag: 140681
  - 12
    - Help - why does SqlException not list Procedure name on timeout? When I have a timeout on invoking a stored procedure against SQL Server, I have an exception returned that looks like the following: {System.Data.SqlClient.SqlException} System.SystemException: {"Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."} _errors: {System.Data.SqlClient.SqlErrorCollection} Class: 10 Errors: {System.Data.SqlClient.SqlErrorCollection} LineNumber: 0 Message: "Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding." Number: -2 Procedure: "ConnectionRead (WrapperRead())." Server: "" Source: ".Net SqlClient Data Provider" State: 0 There no additional information in the Errors collection. The description of SqlException.Procedure property is as follows: Gets the name of the stored procedure or remote procedure call (RPC) that generated the error. Is there something I'm missing? I'm trying to put together some generic logging capabilities, and I had expected that the SqlException would always provide the name of the stored procedure in the Procedure property. Thanks in advance. Tag: Update and Select in the same statement? Tag: 140679
  - 13
    - LDAP: User associate to computer Hi, How to search the user (or users) associate to computer by ldap. thanks -- Jean-Yves BURLOT SI - EVEN Lait Tag: Update and Select in the same statement? Tag: 140678
  - 14
    - Command parameters, DBNull and SQL Server image field Hi! This code (using the System.Data.SqlClient namespace)... SqlCommand c = myConnection.CreateCommand(); c.CommandText = "INSERT INTO myTable (myField) VALUES (@myParameter)"; c.Parameters.Add("@myParameter", myValue); c.ExecuteNonQuery(); ...usually works perfectly fine for all kinds of myFields and all kinds of myValues, UNLESS - myField is of (SQL Server) data type "image" AND - myValue is DBNull.Value In that case I get an SQL Server error message stating that nvarchar is incompatible with image ("Operandentypkollision: nvarchar ist inkompatibel mit image"). I think I understand what is happening behind the scenes: ADO.NET cannot infer a useful data type from DBNull.Value so it assumes that it's an nvarchar, which supports implicit conversion into a lot of other data types, excluding (unfortunately) image. Is there an easy solution to this problem? I need this for a library function, i.e. the data type of myField is not known at run-time. Of course, I could string-replace @myParameter with NULL if myValue is DBNull.Value but that seems like a rather ugly workaround to me... Greetings, Heinzi PS: I'm using .net 1.0/1.1. Tag: Update and Select in the same statement? Tag: 140667
  - 15
    - Responding to DataGrid selection With a .Net 2's WinForms which contain 1) a DataGrid which is assigned to a DataSet's DataTable (some columns are "hidden" on screen) 2) a few TextBox which are aligned to different fields within the same DataTable What event do I code for the .Net DataGrid such that the TextBoxes's value are updated as different rows are selected? Tag: Update and Select in the same statement? Tag: 140666
  - 16
    - update db ? I am having an issue with getting my ds to update my db. I am pulling info from the NW db on my Default.aspx page. I then click the edit btn and pass the data to my Details.aspx page. That works fine. The issue is getting the ds to actually update the db. I know this is simple, but I can't seem to figure out what I am doing wrong. Here is the code... please don't laugh to hard, as this is the first time that I have tried to do this with code-behind, I normally just use a SqlDS on the page, but want to make the page look cleaner... Protected Sub dvDetails_ItemUpdating(ByVal sender As Object, ByVal e As DetailsViewUpdateEventArgs) Handles dvDetails.ItemUpdating Dim connStr As String = ConfigurationManager.ConnectionStrings("NWConnectionString").ToString() Dim conn As New SqlConnection(connStr) 'Dim strSql As String = "SELECT * FROM Employees WHERE EmployeeID=@EmployeeID" Dim upSql As String = "UPDATE Employees SET LastName=@LastName, FirstName=@FirstName, Title=@Title, Address=@Address," & _ "City=@City, Region=@Region, PostalCode=@PostalCode, HireDate=@HireDate WHERE EmployeeID=@EmployeeID" Dim da As New SqlDataAdapter Dim ds As New DataSet Dim ds1 As New DataSet Dim upCmd As New SqlCommand(upSql, conn) Dim idParam As SqlParameter = upCmd.Parameters.Add("@EmployeeID", SqlDbType.Int, 4, "EmployeeID") Try ' da.SelectCommand = New SqlCommand(strSql, conn) 'da.Fill(ds, "Employees") upCmd.Parameters.Add("@LastName", SqlDbType.VarChar, 20, "LastName") upCmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 10, "FirstName") upCmd.Parameters.Add("@Title", SqlDbType.VarChar, 30, "Title") upCmd.Parameters.Add("@Address", SqlDbType.VarChar, 60, "Address") upCmd.Parameters.Add("@City", SqlDbType.VarChar, 15, "City") upCmd.Parameters.Add("@Region", SqlDbType.VarChar, 15, "Region") upCmd.Parameters.Add("@PostalCode", SqlDbType.VarChar, 10, "PostalCode") upCmd.Parameters.Add("@HireDate", SqlDbType.DateTime, 8, "HireDate") idParam.SourceVersion = DataRowVersion.Original conn.Open() 'Update Employees Table ds1 = ds.GetChanges(DataRowState.Modified) da.Update(ds1) ds.Merge(ds1, False, MissingSchemaAction.Add) ds.AcceptChanges() dvDetails.DataBind() Catch ex As Exception 'Display Error Console.WriteLine("Error: " & ex.ToString()) Finally 'Close Connection conn.Close() 'Redirect to Home Response.Redirect("~/Default.aspx") End Try End Sub As this is for learning purposes only, if you could please let me know what needs to be changed and the format of the actual update that would be great. I want to start using this on a normal basis, but really want to understand it before putting it into a live app. Thanks Tag: Update and Select in the same statement? Tag: 140651
  - 17
    - Indexing usefull or not?? Maybe a stupid one,...... is it still usefull to make indexes on the SQL server since in ADO.NET we work with complete datasets which contain complete or parts of tables??? -- Best regards Luc N Tag: Update and Select in the same statement? Tag: 140650
  - 18
    - Problems in loading data from an access database into an array Please i'm new in VB.net and i'm designing a Cinema booking and purchase system..I need to load the seat ID from the database into an array.please how do i go about it.. or can anyone give me an idea of how i can show seats are availabele,booked or bought..i'll really appreciate input.... regards wale fabiyi Kuala Lumpur,Malaysia Tag: Update and Select in the same statement? Tag: 140644
  - 19
    - Requesting some help Hello community I am scincerley asking for some help. I'm 31 years old and live in the UK, I'm married with two children and have worked all my life as has my wife. I have been given an opportunity to emigrate to Canada and start a life long ambition in my chosen career I have a problem though. We cannot raise the money to move and have no deposit for a home, without this we cannot go. Its a long shot but all I ask is if someone out there would be kind enough to give my family a £1 donation. I don't know if what I'm doing is allowed but if nobody wants to give then there is no harm in asking If your happy to help then email at d2801@yahoo.com and 'll send you my details Kind regards Dave Tag: Update and Select in the same statement? Tag: 140643
  - 20
    - Merge DataRow Items? I have a DataTable with one row and I did a Merge() from another DataTable and now I have two rows. How can I get the rows to be a single row (assuming row 0 is the "dominant" one). Thanks. Tag: Update and Select in the same statement? Tag: 140642
  - 21
    - Null value Check in DataTable? I have a DataRow in a DataTable. I want to know if there is a value. I do this if ( this.Rows[0]["Col_A"] == null ) { throw new Exception ( "no" ); } However this condition is never hit. Why? Thanks. Tag: Update and Select in the same statement? Tag: 140640
  - 22
    - DataAdapter.Update() returns error. Hi, I am populating a dataset from a stored procedure, which returns multiple resultsets. One of the resultset is formed with a join condition from two tables. I am updating the fields of the respective datatable. Then update the first table in the database using DataAdapter.Update(). This succeeds. Subsequently, I update the fields of the second table in the datatable and update the respective(second) table in the database using DataAdapter.Update(). This throws the error, "Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.". Dataset.HasChanges() returns true before each update. I give below the code. // First Table DataRow dr = dtOrderInfo.Rows[iSelectedRow]; dr.BeginEdit(); // Data Row changes. ... ... dr.EndEdit(); // connect to database and execute as follows. cnDatabaseOpen(strdbconnect); SqlDataAdapter da = new SqlDataAdapter("Select top 0 * from OrderDetail", cnDatabase); SqlCommandBuilder scb = new SqlCommandBuilder(da); da.Update(dtOrderInfo); da.Dispose(); cnDatabaseClose(); // Second Table dr = null; dr = dtOrderInfo.Rows[iSelectedRow]; dr.BeginEdit(); // Data Row changes. ... ... dr.EndEdit(); // connect to database and execute as follows. cnDatabaseOpen(strdbconnect); SqlDataAdapter da = new SqlDataAdapter("Select top 0 * from Orders", cnDatabase); SqlCommandBuilder scb = new SqlCommandBuilder(da); // Following line throws error. da.Update(dtOrderInfo); da.Dispose(); cnDatabaseClose(); I get the error in da.Update() for the second table. Any help would be greatly appreciated. Thanks Manohar Tag: Update and Select in the same statement? Tag: 140639
  - 23
    - Unexplained entry in Oledb Excel schema table Hi I have an excel workbook with 4 worksheets. When I connect to this workbook using an Oledb connection, I use GetOleDbTableSchema to retrieve the names of the worksheets ("TABLE_NAME"), only the schema table returns 5 rows, not 4. If my worksheet names are Sheet1, Sheet2, Sheet3 and Sheet4, I would expect to get 4 rows in the schema datatable, with "TABLE_NAME" as Sheet1$, Sheet2$, Sheet3$ and Sheet4$, however, I get those four, and an extra one, say Sheet1$_ Has anyone come across this before who could offer either an explanation, or a solution, or preferably both? Thanks in advance for your help. Tag: Update and Select in the same statement? Tag: 140632
  - 24
    - How to get the primary key Hello there, I want to write a function returning the primary key name of a certain table. The following function successfully returns the names of all fields of a table: public string[] GetFieldNames(string tableName) { string[] fieldNames = null; IDbConnection conn = _factory.CreateConnection(true); try { string[] restrictions = { null, tableName, null }; DataTable dataTable = (conn as DbConnection).GetSchema("Columns", restrictions); if (dataTable != null) { fieldNames = new string[dataTable.Rows.Count]; for (int i = 0; i < dataTable.Rows.Count; i++) fieldNames[i] = dataTable.Rows[i] ["COLUMN_NAME"].ToString(); } return fieldNames; } finally { conn.Close(); } } An interesting part in the upper function is, that the table name is at the second position in the restrictions array instead on the third, as I have seen several times in samples found in the internet. In my case I get the field names with the table name at the second position in the array. Now I wrote a function to get the primary key of a table: public string GetPrimaryKeyName(string tableName) { IDbConnection conn = _factory.CreateConnection(true); try { string[] restrictions = { null, tableName, tableName }; DataTable dataTable = (conn as DbConnection).GetSchema("PrimaryKeys", restrictions); if (dataTable != null) { if (dataTable.Rows.Count > 0) return dataTable.Rows[0] ["COLUMN_NAME"].ToString(); } return null; } finally { conn.Close(); } } That function never returns me the primary key, regardless on which position the table name is in the restrictions array. I heard that the upper function does not work for connections based on Oracle. Is that true? Regards, Norbert Tag: Update and Select in the same statement? Tag: 140630
  - 25
    - How to get the last created or modified dataset Hello, is there a possibility to get the ID of the last created or modified dataset of a table via ADO.NET? Is there a database independent way? In SQL Server you can call the scope_identity() function, but this won't work in Oracle, MySQL, Access a.s.o. Thanks in advance, Norbert Tag: Update and Select in the same statement? Tag: 140628
- Next
  - 1
    - TransactionScope promotion & isolation levels Our project is using the System.Transactions.TransactionScope class to provide an ambient transaction which our DAL classes can leverage. I'm looking for some information about the TransactionScope class to answer a few questions about how it behaves when a transaction is promoted to a distributed transaction. Our environment is ASP.NETv2 on Win2003 calling db server running SQL2005. In pseudo-code, we do something like this: Using (scope1 = New TransactionScope()) DalA.IssueInsertAndUpdateStatementsToDb1() Using (scope2 = New TransactionScope(TransactionScopeOptions.Suppress)) DalB.IssueInsertToDb2() scope2.Complete() End Using scope1.Complete() End Using The first TransactionScope is used to create a transaction for a series of DAL inserts and updates to our primary database. Then we start a second, nested scope with the transaction option set to "Suppress" and it is used to insert a record into a second database (on the same db server). Both databases are configured for a default isolation level of ReadCommitted, but we are seeing some transactions come across at the Serializable isolation level. My question is: is the Serializable isolation level being caused by the use of TransactionScope (and its implicit use of DTC)? If we were to provide the appropriate TransactionOption class to the constructor of each scope object, would it provide us with ReadCommitted isolation level, or does the use of DTC always force the use of Serializable? Thanks, Chris Tag: Update and Select in the same statement? Tag: 140627
  - 2
    - DataReader values and .ToString Hello, In order to avoid errors, I have all my DataReader-fields converted with a .ToString. This in case some record could have Null-fields and an error would be thrown. This works fine, except for "money" fields in SQL-Server. When I enter e.g. ? 2,50 and I read it back with a DataReader without .ToString, I get the correct display. However using .ToString, I get no valuta-sign and the value is multiplied by 10 000. When trying to StringFormat the DataReader.ToString, I get a syntax error. Can someone explain this to me an offer some help? Many thanks and greetings, Michel Tag: Update and Select in the same statement? Tag: 140619
  - 3
    - SqlDependency.Start KeyNotFoundException - Possible Bug I'm working heavily with query notifications in SQL Server 2005 and I'm now getting the following exception: System.Collections.Generic.KeyNotFoundException: The given key was not present in the dictionary. This was working for quite a while and then it stopped. At first, the exception was intermittent, but now it's permanent. The exception is happening deep inside of a System.Data.dll in a class called SqlDependencyProcessDispatcher in a method defined as follows: private bool Start(string connectionString, out string server, out DbConnectionPoolIdentity identity, out string user, out string database, ref string queueService, string appDomainKey, SqlDependencyPerAppDomainDispatcher dispatcher, out bool errorOccurred, out bool appDomainStart, bool useDefaults) It looks like the method is checking a dictionary for particular key; then, if it's found, it retrieves the item from the dictionary. Looking at Reflector it appears that this lookup may not be threadsafe. It has a few code blocks inside structured like this: if (!this._sqlDependencyPerAppDomainDispatchers.ContainsKey(appDomainKey)) { lock (this._sqlDependencyPerAppDomainDispatchers) { this._sqlDependencyPerAppDomainDispatchers[appDomainKey] = dispatcher; } } These should probably be using sqlDependencyPerAppDomainDispatchers.TryGetValue instead or should move the lock to the outside of the if statement. No one on the Internet seems to have ever encountered this. Two partial stack traces appear below. They're partial in that I've ommitted a long set of calls from our interal app framework that are orthogonal to the problem. The NT user making these calls is a local administrator on the database server. The calls work a lot of the time, but break and seemingly random moments. We use a preconfigured SQL Server Service Broker Queue named QueryChangeMessages and a service called QueryChangeNotifications. Please help. One stack trace looks like this: [KeyNotFoundException: The given key was not present in the dictionary.] SqlDependencyProcessDispatcher.Start(String connectionString, String queue, String appDomainKey, SqlDependencyPerAppDomainDispatcher dispatcher) +0 System.Data.SqlClient.SqlDependency.Start(String connectionString, String queue, Boolean useDefaults) +672 System.Data.SqlClient.SqlDependency.Start(String connectionString, String queue) +7 Nvidia.PolicyInjection.CallHandlers.SqlDependencyConnection.StartInternal() +103 [DataException: An error occurred while calling ConfigurationManagement.Start for connection string named ConfigurationManagement on SQL Server Service Broker Queue QueryNotificationQueue.] ... Another stack trace looks like this: Server stack trace: at System.ThrowHelper.ThrowKeyNotFoundException() at System.Collections.Generic.Dictionary`2.get_Item(TKey key) at SqlDependencyProcessDispatcher.SqlConnectionContainer.ProcessNotificationResults(SqlDataReader reader) at SqlDependencyProcessDispatcher.SqlConnectionContainer.SynchronouslyQueryServiceBrokerQueue() at SqlDependencyProcessDispatcher.SqlConnectionContainer..ctor(SqlConnectionContainerHashHelper hashHelper, String appDomainKey, Boolean useDefaults) at SqlDependencyProcessDispatcher.Start(String connectionString, String& server, DbConnectionPoolIdentity& identity, String& user, String& database, String& queueService, String appDomainKey, SqlDependencyPerAppDomainDispatcher dispatcher, Boolean& errorOccurred, Boolean& appDomainStart, Boolean useDefaults) at SqlDependencyProcessDispatcher.Start(String connectionString, String queue, String appDomainKey, SqlDependencyPerAppDomainDispatcher dispatcher) at System.Runtime.Remoting.Messaging.StackBuilderSink._PrivateProcessMessage(IntPtr md, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext, Object[]& outArgs) at System.Runtime.Remoting.Messaging.StackBuilderSink.PrivateProcessMessage(RuntimeMethodHandle md, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext, Object[]& outArgs) at System.Runtime.Remoting.Messaging.StackBuilderSink.SyncProcessMessage(IMessage msg, Int32 methodPtr, Boolean fExecuteInContext) Exception rethrown at [0]: at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg) at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type) at SqlDependencyProcessDispatcher.Start(String connectionString, String queue, String appDomainKey, SqlDependencyPerAppDomainDispatcher dispatcher) at System.Data.SqlClient.SqlDependency.Start(String connectionString, String queue, Boolean useDefaults) at System.Data.SqlClient.SqlDependency.Start(String connectionString, String queue) ... Tag: Update and Select in the same statement? Tag: 140613
  - 4
    - SqlDependency.Start - Getting KeyNotFoundException I'm working heavily with query notifications in SQL Server 2005 and I get the following exception: System.Collections.Generic.KeyNotFoundException: The given key was not present in the dictionary. This was working and then it stopped. At first, the exception was intermittent, but now it's permanent. The exception is happening deep inside of a System.Data.dll in a class called SqlDependencyProcessDispatcher in a method defined as follows: private bool Start(string connectionString, out string server, out DbConnectionPoolIdentity identity, out string user, out string database, ref string queueService, string appDomainKey, SqlDependencyPerAppDomainDispatcher dispatcher, out bool errorOccurred, out bool appDomainStart, bool useDefaults) It looks like the method is checking a dictionary for particular key; then, if it's found, it retrieves the item from the dictionary. Looking at Reflector it appears that this lookup may not be threadsafe. It has a few code blocks inside structured like this: if (! this._sqlDependencyPerAppDomainDispatchers.ContainsKey(appDomainKey)) { lock (this._sqlDependencyPerAppDomainDispatchers) { this._sqlDependencyPerAppDomainDispatchers[appDomainKey] = dispatcher; } } These should probably be using sqlDependencyPerAppDomainDispatchers.TryGetValue instead or should move the lock to the outside of the if statement. No one on the Internet seems to have ever encountered this. Two partial stack traces appear below. They're partial in that I've ommitted a long set of calls from our interal app framework that are orthogonal to the problem. The NT user making these calls is a local administrator on the database server. The calls work a lot of the time, but break and seemingly random moments. We use a preconfigured SQL Server Service Broker Queue named QueryChangeMessages and a service called QueryChangeNotifications. Please help. One stack trace looks like this: [KeyNotFoundException: The given key was not present in the dictionary.] SqlDependencyProcessDispatcher.Start(String connectionString, String queue, String appDomainKey, SqlDependencyPerAppDomainDispatcher dispatcher) +0 System.Data.SqlClient.SqlDependency.Start(String connectionString, String queue, Boolean useDefaults) +672 System.Data.SqlClient.SqlDependency.Start(String connectionString, String queue) +7 Nvidia.PolicyInjection.CallHandlers.SqlDependencyConnection.StartInternal() +103 [DataException: An error occurred while calling ConfigurationManagement.Start for connection string named ConfigurationManagement on SQL Server Service Broker Queue QueryNotificationQueue.] ... Another stack trace looks like this: Server stack trace: at System.ThrowHelper.ThrowKeyNotFoundException() at System.Collections.Generic.Dictionary`2.get_Item(TKey key) at SqlDependencyProcessDispatcher.SqlConnectionContainer.ProcessNotificationResults(SqlDataReader reader) at SqlDependencyProcessDispatcher.SqlConnectionContainer.SynchronouslyQueryServiceBrokerQueue() at SqlDependencyProcessDispatcher.SqlConnectionContainer..ctor(SqlConnectionContainerHashHelper hashHelper, String appDomainKey, Boolean useDefaults) at SqlDependencyProcessDispatcher.Start(String connectionString, String& server, DbConnectionPoolIdentity& identity, String& user, String& database, String& queueService, String appDomainKey, SqlDependencyPerAppDomainDispatcher dispatcher, Boolean& errorOccurred, Boolean& appDomainStart, Boolean useDefaults) at SqlDependencyProcessDispatcher.Start(String connectionString, String queue, String appDomainKey, SqlDependencyPerAppDomainDispatcher dispatcher) at System.Runtime.Remoting.Messaging.StackBuilderSink._PrivateProcessMessage(IntPtr md, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext, Object[]& outArgs) at System.Runtime.Remoting.Messaging.StackBuilderSink.PrivateProcessMessage(RuntimeMethodHandle md, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext, Object[]& outArgs) at System.Runtime.Remoting.Messaging.StackBuilderSink.SyncProcessMessage(IMessage msg, Int32 methodPtr, Boolean fExecuteInContext) Exception rethrown at [0]: at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg) at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type) at SqlDependencyProcessDispatcher.Start(String connectionString, String queue, String appDomainKey, SqlDependencyPerAppDomainDispatcher dispatcher) at System.Data.SqlClient.SqlDependency.Start(String connectionString, String queue, Boolean useDefaults) at System.Data.SqlClient.SqlDependency.Start(String connectionString, String queue) ... Tag: Update and Select in the same statement? Tag: 140612
  - 5
    - GridView databinding I have a GridView which is bound to a table. The user has the ability to change some defaults and then run a process from this. This all worked fine until I added in an auto postback to one of the controls to tie in some other functionality. After the post back, and at the end of page load complete, everything is as it should be, but then the GridView does an automatic databind (after load complete) and wipes it all out. Is there anyway to keep this automatic update from occuring, or cancel it or whatever? Thanks. Tag: Update and Select in the same statement? Tag: 140609
  - 6
    - Database Persistance Hi I'm not sure whether I've given this post the correct title, but I'm a bit unsure of where to put it. I have a database that holds information about several of our users. This database is stored on a central server, with an application that periodically performs some processing, which may or may not update the database. It is possible that some times this data will update several times a minute. Here's my problem - I would like to create an application, in a remote location to the main database server that displays the database contents. This applications would need to update its view of the database as soon as the database changes in order to ensure that only the most up to date information is being displayed. I've previously done this by periodically re-reading the database and updating the fields that have changed on screen, however as I add more terminals, this may cause a problem. So ideally I would like a callback or trigger to be called when the database updates that informs the correct client, so that it can update it's screen accordingly. Is this possible, or am I barking up the wrong tree? Is there a better way of doing it? I'd be very grateful if you could give me your thoughts. I am developing the main client application in C#, and the database is currently MySQL. It would be possible to change the database if this made things easier, however it would have to be a free alternative! Many thanks Steve Tag: Update and Select in the same statement? Tag: 140601
  - 7
    - Detach database from SQL Server Hello, When I have closed my appliciation in VB.NET, and I try to detach my database from SQL SERVER, I often get a message that the database cannot be detachted, because it is still in use. So I think there is some code missing in my application (in the MDI_FormClosing?) to make my database is no longer in use. I already put a Connection.Close in the FormClosing also in a Finally-block of that FormClosing, but that does not seem to be enough. Can someone help me with that? Many thanks and greetings, Michel Tag: Update and Select in the same statement? Tag: 140599
  - 8
    - delete row from dataGridView problem Hi All, I have been failing to delete a row from dataGridView. I use typed dataset. Here is my code, please advice what wrong is here? private void MainForm_Load(object sender, EventArgs e) { // TODO: This line of code loads data into the 'lodgersDataSet1.Table_Lodgers' table. You can move, or remove it, as needed. this.table_LodgersTableAdapter1.Fill(this.lodgersDataSet1.Table_Lodgers); } private void dataGridViewLodgers_RowsRemoved(object sender, DataGridViewRowsRemovedEventArgs e) { string strConnString = @"Data Source=XP\SQLEXPRESS;Initial Catalog=Lodgers;Integrated Security=True"; SqlConnection sqlConnection = new SqlConnection(strConnString); SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Table_1",sqlConnection); MyDataSet myDataSet = new MyDataSet(); da.Fill(myDataSet, "Table_1"); // myDataSet is updated, row is deleted properly // DataRow dr = myDataSet.Table_1.Rows[e.RowIndex]; myDataSet.Tables["Table_1"].Rows.Remove(dr); // Update returns 0 !!! // int ret = daLodgers.Update(dsLodgers2, dsLodgers2.Tables[0].TableName); myDataSet.Table_1.AcceptChanges(); myDataSet.AcceptChanges(); } Thank you in advance A. Tag: Update and Select in the same statement? Tag: 140596
  - 9
    - ExecuteNonQuery within the loop of a datareader Hello, How can I use an ExecuteNonQuery within the loop of a datareader? My code is as follows: Dim cmdToProcess As New SqlClient.SqlCommand Dim rdToProcess As SqlClient.SqlDataReader With cmdToProcess .CommandText = " SELECT * FROM tblDetails " .Connection = cn End With rdToProcess = cmdToProcess .ExecuteReader Select Case rdToProcess Case True Do 'here I want to add or update data Dim cmdToChange As New SqlClient.SqlCommand With cmdToChange .CommandText = "INSERT INTO tblNew(AD_Action) VALUES (@Action)" .Parameters.AddWithValue("@Action", rdToProcess("DE_Number")) .Connection = cn End With cmdToChange.ExecuteNonQuery Loop until not rdProcess.Read End Select At "cmdNieuweCafetariaActiedetail.ExecuteNonQuery", I get the message that I have to close the first datareader in order to execute, but then my code cannot proceed because rdToProcess("DE_Number") can no longer be read. How can I solve this? Many thanks and greetings for your help, Michel Tag: Update and Select in the same statement? Tag: 140594
  - 10
    - Console and Files How come line 1 will output garbage to the screen, but line 2 will output a good number to a file? How can I make line 1 out put a good number to the screen String* dayStr = System::DateTime::Now.ToString("dd"); Int32 dayInt = System::Int32::Parse(dayStr); Console::WriteLine("the day is {0}", __box(dayInt) ); // Line 1 . . . String* path = new String("_1D.txt"); // will read the time from a file FileStream * fs = new FileStream(path, FileMode::Open); BinaryReade* br = new BinaryReader(fs); Int32 day = br->ReadInt32(); fs = new FileStream(S"_1k.txt", FileMode::Create); //will write the time to another file BinaryWriter* bw = new BinaryWriter(fs); Br->Write( day); //Line 2 Tag: Update and Select in the same statement? Tag: 140587
  - 11
    - encrypt/decrypt function in asp.net 2 i am looking for code sampe in aso.net 2 for encrypt/decrypt of paswr d withought using any external DLL (judt to use existing net2 tools) thnaks i nadvance peleg Tag: Update and Select in the same statement? Tag: 140586
  - 12
    - SqlDependency.Start - Getting intermittent KeyNotFoundException I'm working heavily with query notifications in SQL Server 2005 and I intermittently get the following exception: System.Collections.Generic.KeyNotFoundException: The given key was not present in the dictionary. No one on the Internet seems to have ever encountered this. A partial stack trace appears below. It's partial in that I've ommitted a long set of calls from our interal app framework that are orthogonal to the problem. The NT user making these calls is a local administrator on the database server. The calls work a lot of the time, but break and seemingly random moments. We use a preconfigured SQL Server Service Broker Queue named QueryChangeMessages and a service called QueryChangeNotifications. Please help. Server stack trace: at System.ThrowHelper.ThrowKeyNotFoundException() at System.Collections.Generic.Dictionary`2.get_Item(TKey key) at SqlDependencyProcessDispatcher.SqlConnectionContainer.ProcessNotificationResults(SqlDataReader reader) at SqlDependencyProcessDispatcher.SqlConnectionContainer.SynchronouslyQueryServiceBrokerQueue() at SqlDependencyProcessDispatcher.SqlConnectionContainer..ctor(SqlConnectionContainerHashHelper hashHelper, String appDomainKey, Boolean useDefaults) at SqlDependencyProcessDispatcher.Start(String connectionString, String& server, DbConnectionPoolIdentity& identity, String& user, String& database, String& queueService, String appDomainKey, SqlDependencyPerAppDomainDispatcher dispatcher, Boolean& errorOccurred, Boolean& appDomainStart, Boolean useDefaults) at SqlDependencyProcessDispatcher.Start(String connectionString, String queue, String appDomainKey, SqlDependencyPerAppDomainDispatcher dispatcher) at System.Runtime.Remoting.Messaging.StackBuilderSink._PrivateProcessMessage(IntPtr md, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext, Object[]& outArgs) at System.Runtime.Remoting.Messaging.StackBuilderSink.PrivateProcessMessage(RuntimeMethodHandle md, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext, Object[]& outArgs) at System.Runtime.Remoting.Messaging.StackBuilderSink.SyncProcessMessage(IMessage msg, Int32 methodPtr, Boolean fExecuteInContext) Exception rethrown at [0]: at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg) at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type) at SqlDependencyProcessDispatcher.Start(String connectionString, String queue, String appDomainKey, SqlDependencyPerAppDomainDispatcher dispatcher) at System.Data.SqlClient.SqlDependency.Start(String connectionString, String queue, Boolean useDefaults) at System.Data.SqlClient.SqlDependency.Start(String connectionString, String queue) Tag: Update and Select in the same statement? Tag: 140578
  - 13
    - Internal .Net Framework Data Provider error 6. Hi I'm getting very occasional exceptions in an application when trying to open a SQLConnection. Does anyone know what this error means, or what I can do about it? The error has only occurred a couple of times, each time with a gap of more than a day, so I cannot replicate or even find a pattern other than it happening at the point where I open the connection. I've pasted the exception details below. Thanks Dan ---------------------- Source: System.Data Message: Internal .Net Framework Data Provider error 6. Stack: at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at.... etc.... Type: System.InvalidOperationException Tag: Update and Select in the same statement? Tag: 140577
  - 14
    - Problem in datagrid paging and checkbox Hi All, I have one issue in using the datagrid in the asp.Net. I have the checkbox column in the datagrid. And also having the paging option.I have selected two items in the page 1 and navigate to page 2.And i selected 1 item. The problem is when i come back to page one ,all the item was unchecked.Cos during the paging i need to bind the grid again from the database .Is there any way to bind the datagrid with one time data fetch from database. Tag: Update and Select in the same statement? Tag: 140571
  - 15
    - BackgroundWorkers and ADO.Net I have an app that allows the user to build a query and then click on a button to execute the query and the results are then used to fill a grid. Can I have the retrieval run in a backgroundworker and have a button that would allow the user to cancel the query if it is running for too long of a period? Is the BackgroundWorker able to populate a grid which is running in the primary thread? Bill Tag: Update and Select in the same statement? Tag: 140569
  - 16
    - Automatic filling fields Hi guys, could you please help me on my problem. >>>> --------------- <<<<<<<<< Datasource: MS Access 2003 Table1: Tbl_Employee Field1: Emp_name Field2: Position Field3: Contact# Field4: Address Table2: Tbl_Salary >>>> --------------- <<<<<<<<< Windows Form of Visual Basic.net i have a field Employee on the form, the problem is, i when i type the name of employee, i want the position, contact# and address to automatically fill my other fields and will save on my database Tbl_Salary. -- Message posted via DotNetMonster.com http://www.dotnetmonster.com/Uwe/Forums.aspx/dotnet-ado-net/200707/1 Tag: Update and Select in the same statement? Tag: 140566
  - 17
    - ObjectDataSource a little help I have something that works, but it only returns one row of data when i create an ObjectDataSource with it and bind it to a gridview so i need a little help with this (thanks) as simple as it gets 1. my class: Imports Microsoft.VisualBasic Public Class radioShowFiles Private _szFileName As String Public Property szFileName() As String Get Return _szFileName End Get Set(ByVal value As String) _szFileName = value End Set End Property End Class 2. the functions that allows it to bind: Public Function fn_getallRadioShows() As DirectoryInfo Dim ri As System.IO.DirectoryInfo = New System.IO.DirectoryInfo("c:\websites\arlnewlook\radioshows") Return ri End Function Function fn_rtn() As radioShowFiles Dim rsf As radioShowFiles = New radioShowFiles Dim ri As DirectoryInfo = Me.fn_getallRadioShows Dim diar1 As IO.FileInfo() = ri.GetFiles() Dim dra As IO.FileInfo For Each dra In diar1 rsf.szFileName = dra.Name Next Return rsf End Function End Class I know why it's only including the last row of data, but i am sorry to say i don't know what to do to change it! I know i'm very close. THANKS! KES -- Share The Knowledge. I need all the help I can get and so do you! Tag: Update and Select in the same statement? Tag: 140565
  - 18
    - combining datasets Hi, I have 2 datasets right now, say with the following structure: DataSet 1 has a row called ItemCode, index 1 DataSet 2 has a row called ItemCode, index 1 both are similar, but i need to merge them to make one dataset, which needs to have everything in DataSet1 plus anything from DataSet2 that's not in DataSet1 already. I'm not too familiar with working with Datasets, i know theres suppose to be a way to join them, can someone help out? Both have a ItemCode which is what needs to be used to make sure no duplicates from DataSet2 get into DataSet1. Thanks. Tag: Update and Select in the same statement? Tag: 140563
  - 19
    - DataAdapter with Parameter problem When I run the following code everything works fine (which proves the = stored procedure and the parameter: Dim cmdSQL As New SqlCommand cmdSQL.CommandType =3D CommandType.StoredProcedure cmdSQL.CommandText =3D "bdl_CustomerOrders_SELECT" cmdSQL.Connection =3D mcnnSQL Dim prm As New SqlParameter() prm.ParameterName =3D "@CustomerID" prm.Value =3D Me.cboCustomerSELECT.SelectedValue prm.Direction =3D ParameterDirection.Input cmdSQL.Parameters.Add(prm) mcnnSQL.Open() Dim dr As SqlDataReader dr =3D cmdSQL.ExecuteReader() While dr.Read Debug.WriteLine(dr.Item(0).ToString) End While dr.Close() However, when I run the following code I get the error message: Incorrect syntax near 'bdl_CustomerOrders_SELECT' at the last line: = sda.Fill(ds, "CustomerOrders") Thoughts anyone ? Dim sda As New SqlDataAdapter("bdl_CustomerOrders_SELECT", mcnnSQ= L) Dim prm As SqlParameter =3D = sda.SelectCommand.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, = "CustomerID") prm.Direction =3D ParameterDirection.Input prm.SqlValue =3D Me.cboCustomerSELECT.SelectedValue sda.TableMappings.Add("Table1", "CustomerOrders") Dim ds As New DataSet() sda.Fill(ds, "CustomerOrders") Des -- = Using Opera's revolutionary e-mail client: http://www.opera.com/mail/ Tag: Update and Select in the same statement? Tag: 140562
  - 20
    - Question about TransactionScope Hi, Does TransactionScope need DTC in situations that the transaction is not distributed? I have a set of non-distributed transactions and I am not sure that TransactionScope is the best option for best practice perspective. Thank you, Max Tag: Update and Select in the same statement? Tag: 140560
  - 21
    - Strongly Typed DataSet -> Preview Data -> How to preview stored procedures with GUID parameter? Hi, In Strongly typed datasets, when we preview a table adapter query, we cannot preview a stored procedure query that has GUID parameter. The error complains that cannot convert a string to GUID. What is the proper format for GUID parameters? Of course I tried {.} format. Thanks, Max Tag: Update and Select in the same statement? Tag: 140556
  - 22
    - Typed DataSets ADO.NET 2.0 For a project I have a typed dataset created ( by dragging tables/ views and configuring the appropriate queries ). If the database schema for these tables is changed (e.g. few columns get added), is it possible to automatically refresh the DataSet. Right now, I see that the Fill query only has the previous columns selected, the new added ones do not appear. This is my first time using datasets, so wondering if the adapter queries can be refreshed somehow. Thanks. Tag: Update and Select in the same statement? Tag: 140553
  - 23
    - Check user input data exist in database: ERROR Dim retVal As Boolean = False Dim cnSQL As SqlConnection Dim cmSQL As SqlCommand Dim strSQL As String strSQL = "Select * From Table_Contacts where EmailAddress = '" & txtEmail.Text & "' OR Name = '" & txtName.Text & "';" cnSQL = New SqlConnection(thisConnection) cnSQL.Open() cmSQL = New SqlCommand(strSQL, cnSQL) Dim reader As SqlDataReader = cmSQL.ExecuteReader() If Not reader.HasRows Then retVal = False MessageBox.Show("No Record") Else MessageBox.Show(" already exists!", " ", MessageBoxButtons.OK, MessageBoxIcon.Error) retVal = True End If cnSQL.Close() cmSQL.Dispose() cnSQL.Dispose() retVal = True Can someone help me with this? I'm keep getting "already exist" even there is no matching record in the database. - Thanks Tag: Update and Select in the same statement? Tag: 140546
  - 24
    - Help with this Argument 'Prompt' cannot be converted to type 'Stri Hi; I am creating a windows app using ado.net 2.0 in the app I am connecting to a database by creating and then using a data provider factory. When I call sqlConn.Open() I get the follwoing message "Argument 'Prompt - cannot be converted to type String". What am I missing in my code ? My code follows: Here are the entries in my app.config file <configuration> <appSettings> <add key="provider" value="System.Data.SqlClient" /> </appSettings> <connectionStrings> <add name ="cnStr" connectionString = "Data Source=aServer;integrated Security=True;initial Catalog=GoodTable" /> </connectionStrings> </configuration> Here is how I call this in my app. : Try Dim sAttr As String = ConfigurationManager.AppSettings("provider") ' Dim dp As DataProvider = CType(Enum].Parse(GetType(DataProvider),sAttr), DataProvider) Dim cnStr As String = ConfigurationManager.ConnectionStrings("cnStr").ConnectionString ' Create the provider factory Dim df As DbProviderFactory = DbProviderFactories.GetFactory(sAttr) 'Connection here Dim sqlConn As DbConnection = df.CreateConnection() sqlConn.ConnectionString = cnStr Catch ex As Exception MsgBox(ex) End Try Try sqlConn.Open() Catch ex1 As Exception MsgBox(ex1) End Try Thanks for any suggestions ! -- Gordon Tag: Update and Select in the same statement? Tag: 140545
  - 25
    - What are the pros and cons in using Enterprise Library? I'm new to .net. It seems that those application blocks in Enterprise Library are very useful especially when one tries to build a new application. I want to know the pros and cons in using those blocks. Is MS going to release new Enterprise Library for future .net version/build? If I use the current version of the blocks, what happen when a new version comes out? Tag: Update and Select in the same statement? Tag: 140544

Is there any way to select a record and update a field in that record in the
same statement? I have a table that several threads will be querying. Once
the record has been selected by a thread, I don't want that record to be
selected by any of the other threads. So my thought was to have a "busy"
column that would be flagged true during the select. However, I don't see a
way to do it.

I'm working with an MDB file so I don't think triggers would be the answer
here. Suggestions anyone?

Top

Re: Update and Select in the same statement? by Petar

Petar
Tue Jul 31 09:48:46 CDT 2007

Terry Olsen wrote:
> Is there any way to select a record and update a field in that record in the
> same statement? I have a table that several threads will be querying. Once
> the record has been selected by a thread, I don't want that record to be
> selected by any of the other threads. So my thought was to have a "busy"
> column that would be flagged true during the select. However, I don't see a
> way to do it.
>
> I'm working with an MDB file so I don't think triggers would be the answer
> here. Suggestions anyone?
>
>

Hi Terry,
Use row-level locking (I suppose you use OLEDB):
http://msdn2.microsoft.com/en-us/library/system.data.common.dbconnectionstringbuilder.connectionstring.aspx

In AD0.NET you can change the locking behaviour to pessimistic through
the IsolationLevel. [http://msdn2.microsoft.com/en-us/library/ms971557.aspx]

In addition you may consider using transactions.

Locks can be set at row, page and database level. Keep in mind that
since Jet 4.0 if row locks reach certain level [as defined in
PagesLockedToTableLock found under the
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Jet 4.0], these
locks will be automatically promoted up to page or table level.

HTH,
Petar Atanasov
http://www.a-wake.net

Top