txn isolation level

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
    - VB.NET and SQL Server 2005 - Data locked when they should be. LOOK Hi, I have 2 ways of selecting data from my table "Membre". 1- By permit number (memNoPermis) 2- By lastname, firstname (memNom AND memPrenom) SAMPLE DATA: memNoPermis: 1 memNom: Walter memPrenom: Gregory memNoPermis: 2 memNom: Wick memPrenom: Timothee memNoPermis: 3 memNom: Williams memPrenom: Simon He is my weird scenario. FIRST- I run this query. SELECT M.memNoPermis, M.memNom, M.memPrenom FROM Membre M WITH (UPDLOCK, NOWAIT) WHERE M.memNoPermis = 2 It reads & locks the record ! FINE. ----- After, if I run this query : SELECT M.memNoPermis, M.memNom, M.memPrenom FROM Membre M WITH (UPDLOCK, NOWAIT) WHERE M.memNom = 'Williams' AND M.memPrenom = 'Simon' It tells me its lock ... but that is false. Only record id 2 should be locked. The query above should return unlocked record id 3. BUT If I run the following query instead.. which ask for the same record. SELECT M.memNoPermis, M.memNom, M.memPrenom FROM Membre M WITH (UPDLOCK, NOWAIT) WHERE M.memNoPermis = 3 In this case the record can be selected as if it wasn't locked (which is the case). Can anyone help me on this? memNoPermis is my table PrimaryKey field. Could my problem be related to that? I made many tests. If I only read records with "memNoPermis" in my WHERE clause, everything works perfectly. BUT. More explanation: If I read record id 3 with memNom+memPrenom and no row it locked ! GREAT. READS & LOCKS. While record id 3 is locked: If I read record id 2 (which is before 3 in the table) with memNom+memPrenom ... GREAT. READS & LOCKS. BUT Instead if I read record id 4 (which is after 3 in the table) .. it acts as if I was locked already...which is wrong. Pretty strange. When reading with (memNom+memPrenom) in my WHERE clause, If the record I need is after a locked record in my table, it acts as if it was locked already. When reading with (memNoPermis <-- PK field) in my WHERE clause, In all cases it always works great !! Thanks for helping me resolving this problem. I tried putting memNoPermis+memNom+memPrenom in a UniqueKey. But it didn't help. Tag: txn isolation level Tag: 129537
  - 2
    - DBTransaction question Hi, I have some code like this : DBTransaction = DBConnection.BeginTransaction() Assuming I don't book keep the return from this call , How do I 1) determine if there is a DBTransaction pending on this DBConnection and 2 ) if there is , what is the DBTransaction . thanks -chaz Tag: txn isolation level Tag: 129536
  - 3
    - How To Use Keyword DataDirectory For Database In Same Folder As Executable Hello, With a fairly complete and detailed example, how do I use the DataDirectory keyword? What I ultimately want to do is place my database in the same folder as the executable and have my application find it there. Thank you, Christopher Lusardi Tag: txn isolation level Tag: 129535
  - 4
    - ExecuteNonQuery with error checking (or continue on error equiv) First off I'll explain why I need something similar to ContinueUpdateOnError feature that a data adapter has, and why I can't just use a data adapter. Basically I'm updating a Large amount of data using BCP to copy the data to a temp table in order to quickly move the mass amount of data from a flat file to my database. I Then run updates on the temp table I just loaded that insert AND update the data from the temp table to the production table. The reason i do this is because my data (unfortunately) contains both inserts and updates. The most efficient way I've found to do this involves using BCP to move the data, then running SQL Statements I have generated to process the updates and inserts from the temp table to the production table in the database. I'm using something 'similar' to the BCP code from the PDC 05 Demo's ( http://blogs.msdn.com/dataaccess/archive/2006/01/09/510083.aspx ) Well it appears that when using try/catch if I have errors that yes it will catch the error, however I have no control over it. The entire Update command will then hault not updating any records. Is there some way to have the ability to continue on with my SQL statement if it happens to hit a record that doesn't have a corresponding foreign key or something similar? Thanks -- Matt www.Fiddelke.org Tag: txn isolation level Tag: 129528
  - 5
    - Tabeladapter with row_number() over... Hi Friends, I have created a tableadapter (using wizard of VS2005) with some SQL query statement. But I want to write SQL query such a way that it returns table with one more fields, i.e. Row number. I have tried this SQL query in SQL server 2005: select ordernumber, row_number() over( order by ordernumber) sno from orderhead It's working in SQL server 2005 but not in table adapter (It gives SQL syntax error in 'Add Query' wizard). Please help me to add such a column in SQL. And why should tableadapter give any error? This query is working with dataadapter!! I mean, this code is working Dim ds As New DataSet Dim da As New SqlClient.SqlDataAdapter("select ordernumber, row_number() over( order by ordernumber) sno from orderhead" _ , conStr) da.Fill(ds, "TAB1") Thanks in Advanced, -Yogee Tag: txn isolation level Tag: 129520
  - 6
    - Binding to object datasource I have created a project that contains a typed dataset. I have extended this dataset through partial classes an daab 2.0. I have created a datasource out of this project through the datasources wizard in a new winforms project , I have a dropped a binding source component on the form . When I try to bind the bindingsource to the object datasouce , I get an invalid property error. If i try and expand the node containing this object datasource in the data sources window , the i.d.e shuts down with out warning. Using vs 2005 proefessional vb.net Any help whatsoever would be greatly appreciated Tag: txn isolation level Tag: 129519
  - 7
    - OleDbCommand.Parameters.Add and 'IN' Comparator and MSAccess I am using Visual Studio with a MS Access Database I just can not work out how to create my parameter for the following Select Fld1, Fld2 from myTable where Fld1 in (?) The value I wish to add to the parameter collection for Fld1 is '2, 3, 17, 9, 235' to add a single Parameter I would do the following cmd.parameters.add("",oledbtype.integer).value = 1 There does not seem to be an OleDbType for an integer array But I don't have a single value and I do not know how many values, there will be in the 'IN' query I also have other parameters in the Selection (not shown) and as OleDbParameters, seem to be positional, with no way of changing that, I can't just enter many '?' into the 'IN' part and only enter as many as I need. In any case what would happen if I had multiple 'IN' comparators. Any ideas or suggestions, welcomed Tag: txn isolation level Tag: 129514
  - 8
    - About Load-on-Demand ADO.NET skill. I want to improve my C# ADO.NET skills. I want to study how to handle Load-On-Demand and Paging Skills in C#. Is there any information to study? Tag: txn isolation level Tag: 129511
  - 9
    - Two DataViews of the Same database Table but different columns Hi, I'm new to ADO.Net and I want to DataViews containing different columns of the same database table. Is it possible without creating 2 dataadpaters? Thank you, Scott Tag: txn isolation level Tag: 129509
  - 10
    - sql server 2005 crashes by sqlbulkcopy when the column size does not match dear all, i am having this strange problem. What i have done is i load a datatable to the sql server 2005 using the sqlbulkcopy. when the column size of the destination table is less than the value of the mapped column of the datatable instead of throwing exception the sql server 2005 instead crashes. the only exception that gets caught is the transport level error( TCP Provider, error: 0 .......) what is happening ???? i am so confused. here is the part of the code: ......... SqlConnection oSQLConn = new SqlConnection(); oSQLConn.ConnectionString = Config.GetConnectionString(); oSQLConn.Open(); try{ System.Data.SqlClient.SqlBulkCopy bc= new System.Data.SqlClient.SqlBulkCopy(oSQLConn, SqlBulkCopyOptions.TableLock, null); .......................... ............................// other part of the code the initializes the // DataTable with name dataTable bc.BatchSize = 500; bc.DestinationTableName = "desttable"; bc.WriteToServer(dataTable); bc.close(); } catch(Exception exe){} ..... if anyone knows what is wrong.. or what is going wrong then it would be a great help to me. this is causing a lot of headache.... regards, kamal Tag: txn isolation level Tag: 129504
  - 11
    - How to udpate and alter procedure at once. I want to use SqlCommand.ExecuteNonQuery to execute a Sql which is read from a text file. I want to sql to update a record in myTable, and alter a procedure. The text file is like: -------------------------------------------------------------------------------- UPDATE myTable set Col1='text' ALTER procedure [dbo].[EmptyData] as Delete from Acc; Delete from BodyMind; Delete from Checks; Delete from CheckTemp; -------------------------------------------------------------------------------- when I execute the ExecuteNonQuery, it result into an error: The Alter command must in the first line. But if I put the Update command at last line, it become a part of the ALTER procedure. How can I udpate and alter procedure at once. Tag: txn isolation level Tag: 129491
  - 12
    - Loading data into an Access Table from CSV or XML I have lots of old data files, which I am adding to an Access Database. I read somewhere, that you can select directly on a CSV file or XML by changing the Connection String - Extended Property - or I could have been dreaming. So to save a lot of coding and before I break everything, is it possible to do something like the following COPY CSV into an Access Table INSERT INTO TABLE1 (Fld1, fld2, fld3, fld4) SELECT fld1, fld2, fld3, fld4 from mycsv.csv COPY XML into an Access Table INSERT INTO TABLE1 (Fld1, fld2, fld3, fld4) SELECT fld1, fld2, fld3, fld4 from myxml.xml Using OleDbCommand.executeNonQuery If it is possible, some sample code would be very helpful Thanks for any help - Richard Tag: txn isolation level Tag: 129490
  - 13
    - create *.cub file from DataSet Hi, The environment is .NET 1.1, VS2003. (can also be .NET 2.0 +VS2005) Suppose I have downloaded a DataSet from the server to the client machine. Now I want to create a *.cub file from this DataSet and save it to disk. I tried using ADOMD.NET but dodn't know how to connect to a DataSet. Is it at all doable? Thanks GM P.S. I have already asked this question on sqlserver.olap group, but still didn't get any response. Tag: txn isolation level Tag: 129489
  - 14
    - HELP!!! Error installing SQL Express 2005.... I have VS 2005 Pro installed, but I tried to install SQL Express (as I did not know if the "developer" edition that installs with 2005 Pro was the same as my customers would be using). I have got to the "SQL Server Database Services" line in the setup progress screen, and I get "SQL Server Setup has encountered the following problem: [Microsoft][SQL Native Client]Communication link failure. To continue, correct the problem, and then run SQL Server Setup again." I'd be happy to "Correct the problem..." if the error msg actually told me what it was or how to fix it. When I copied the text I also got this link (which basically says "we don;t know")... "For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=setup.rll&EvtID=60009&EvtType=lib%5codbc_statement.h%40sqlStatement%40OdbcStatement%3a%3aexecute_batch%40HotfixDBEINST%4013%40xea69 " I am leaving everything right where it is (and, yes, I backed up my system before embarking upon this little adventure). Anybody seen this one? Tag: txn isolation level Tag: 129488
  - 15
    - Send Recieve data to insert into tables using Socket Hi, I have to develop a Client Server application. What I want is that the server will listen on a specific port and client will connect to the server and then send a request to retrieve/insert some data from/into the database. Database is placed on server side. What is the best way to send and retrive a large amount of data from a database over the sockets? Thanks in anticipation. Regards, Ahmad Jalil Qarshi Tag: txn isolation level Tag: 129487
  - 16
    - how to set the value of the parameter for the SelectCommand? Hi, The gridview linked to this datasource shows nothing. The problem is the value of the variable 'a' which is not passed, i'm afraid ... The code-behind contains the value to be passed: public a As String Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load a = "phil" End Sub the aspx contains: <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="Provider=....." ProviderName="System.Data.OleDb" SelectCommand="SELECT [name],[city] FROM [mytable] where [name]= @name"> <SelectParameters> <asp:Parameter Name="name" DefaultValue=a /> </SelectParameters> </asp:SqlDataSource> Thanks Phil Tag: txn isolation level Tag: 129486
  - 17
    - SQL Connection still working after KILL the process in SQL I would like to know why is this happening: We do have a multithreading windows application in C#. Each thread establish its own SQL connection to an SQL 2003 server,. Those are worker threads for remote proccess, and each one needs to mantain its own SQL connection and have connection pooling turned off due to company rules. There is also an SQL job in the database, which look for idle connections and KILL them (KILL spid). However, some times the remote "idle" process wasn't "idle", and tries to access again the database. The connection then fails with an expected "General Network Error". However, the conection does *STILL* working, at least from a code perspective: Still doing queries, still getting data, etc, just that error the *first time* it is used since its process ID was KILL in the database. We did some review, and found that a new connection is made each time we use the connection, and immediately disconnected upon completition... The new connection does have the same connection string that the killed one, and connection pooling is disabled ( pooling=false; ) for all the connections . Why is this happening? Is there a way to avoid it ? ************************************************ Here's part of the code. It is not the whole code, nor working, just to show how i am using the connection. All of this is inside a worker thread ---------------------- string scad = ""; scad = string.Format("server={0:s};database={1:s};user id={2:s};pwd={3:s};", gv.SVRTerm, gv.BDTerm, cve_usuario, password); scad += string.Format("pooling=false;"); ------------------------- oConn = new SqlConnection(scad); oConn.Open(); ----------------------- while (true == bActivo){ ----------------- try{ // sql is the process to realize in the server // it's taken from an string array with predefied queries using(SqlCommand ocmd = new SqlCommand(sql, oConn)){ ocmd.CommandType = CommandType.Text; using(SqlDataAdapter oda = new SqlDataAdapter()){ oda.SelectCommand = ocmd; oda.Fill(oDS); oda.Dispose();// } ocmd.Dispose(); } ----------------------- }catch(SQLException ex){ log(ex); } ---------------- } The connection it's established once at the thread start, an closed at thread end. The thread must not crash nor hang over no one circumpstance (and it has not hung nor crash AFAIK ) . In the worst case will log and send a message to the remote server, for that one kill the client process of this thread, and shutdown itself. Tag: txn isolation level Tag: 129480
  - 18
    - InvalidCastException in DataRow Hello, This is a compactframework windows app. Iam trying read from a DataRow. myTextBox.Text=myDataRow("MyColumn") If there is a value for the myDataRow("MyColumn") then the statement executes. But if the column is empty I get invalidCastException, When casting from number etc.. I have tried CTYPE() or even check for "Is Nothing", just accesing the column gives me the exception. How can I prevent the exception from occuring. Underlying DataField is nvarchar(250) from SQLCE databse. Thanks jay Tag: txn isolation level Tag: 129477
  - 19
    - ANN: C# Online.NET Open Soure List FOR IMMEDIATE RELEASE The Open Source Movement has yet to play as critical a role in the development of C# systems as it has in the development of Java systems. Perhaps, it never will. But, for those who cannot afford Microsoft licensing fees or who cannot wait for Microsoft to address a particular domain, open source is a viable alternative. C# Resources - Open Source is a new list of C# open source projects which are both free and open source. The recent sale of the JBoss Application Server to Red Hat for $350 million illustrates vividly that there is a difference. C# Resources - Open Source is here: http://www.csharp-online.net/csow/index.php?title=CSharp_Resources_-_Open_Source If you know of a C# open source project which is not yet listed, please add it under the appropriate category. Visit C# Online.NET for the best online C# resource. C# Online.NET A wiki-based C# and .NET resource featuring articles, code snippets, forums, study guides, and tutorials. http://wiki.csharp-online.net/ http://blog.csharp-online.net/ http://forum.csharp-online.net/ [ ANN: C# Online.NET Open Soure List ] Tag: txn isolation level Tag: 129476
  - 20
    - multiple types of queries and stored procedures I'm using MySql and ADO.net I find myself in a situation where I need to execute many different types of queries. Basically they are all getting the same type of data, but with different WHERE clauses and different levels of required data. For example, one query "GetAllTest" will return ALL the test records in a table and all of the fields. Another query might be "GetRecentTests" which should only get the results from the last 10 days and I don't need to get all the fields, just a subset. I'm using a DataReader for performance reasons and my Dal layer is populating business entities with the results from the DataReader. My questions are: 1) What is the best practice for executing different types of queries with different filter criteria? My Sprocs are pretty long and to make a handful of them that vary only slightly (IE: WHERE _type LIKE 'Single', WHERE type LIKE 'Single' AND ID > 100, etc) and all share the same joins and selects seems like an incredible amount of redundancy. I've thought about using straight SQL rather than sprocs, then I could have a query builder class that could build the queries for me based on filter criteria. I don't want to go away from sprocs if I don't have to, they are so much more maintainable ;0) 2) Business entity population - I currently populate my business entities in the body of my DAL method (IE: GetAllTests) - this poses the same problem as #1, I would be repeating this code over and over for every DAL method that is responsible for getting results with slightly different filters. Seems like a ton of wasted code. I thought about creating an ObjectBuilder class that would take a DataReader and populate my objects for me, I could then use this in my various DAL methods. Is there another way? How do you all do this? These 2 problems must be something that many of you deal with on a regular basis. Do you have any ideas for me? I just though of one more, I could use a VIEW to keep all my joined results, then I would query against the VIEW with my filters, that would save me some redundancy in the sprocs I guess... but that VIEW would get pretty big as time goes on. I'm stuck, I need some hints, pointers, tips, clues, etc ;0) Thanks for any help, Steve Tag: txn isolation level Tag: 129475
  - 21
    - why different syntaxes in Commnad lines? Hi, I saw three different syntaxes in command lines: UpdateCommand="UPDATE [mytable] SET [name] = @name ... UpdateCommand="Update `mytable` set `name`=? ... UpdateCommand="UPDATE [mytable] SET [name] = ? ... Does the three do the same? Why those differences? Thanks Phil Tag: txn isolation level Tag: 129468
  - 22
    - Query builder parameter problem Using VS2005 Pro and dotnet 2.0 I am trying to add parameters in the query builder tool while configuring my data adapter using the data adapter configuration wizard. I right-click somewhere on the builder pane and then select properties. At the bottom of the properties window, there is a line that says 'Query Parameter Identification'. I click the ellipses to the right and I have three items to change - Prefix characters, Suffix characters, Escape characters. I put a Prefix character in the box (In this case I have tried the @ and % symbols), click ok and close the properties box. So far so good. Then in the filter column for the row I want to filter, I put the parameter name I want - in this case @UserID. The builder puts an '=' sign in front. Again, everything is fine. I click the Execute query button at the bottom left and the builder asks me for the parameter data. I input '222' and click ok. I get the results I was expecting. Great! But now the problem starts. I click Ok to exit the builder then click next on the data adapter configuration wizard. I get the following error: Generated Select statement Error in WHERE clause near '@' Unable to parse query text. When I click finish, I get a message box saying "An unexpected error has occurred. Error Message: Object reference not set to an instance of an object." I can't seem to get it to accept my parameters even though when I execute the query within the builder, everything works as expected. Has anyone had this same problem or can you recreate it? Thanks, Jay Tag: txn isolation level Tag: 129466
  - 23
    - how to use a stored procedure of Access in VWD? Hi, I defined a query in Access. I created a AccessDataSource in an aspx page using Visual Web Developer. I specify Access, oledb etc ... and in the window "how do you like to retrieve data from db?" i choose "specify a cusstom sql or stored procedure". I go to next, but here, the radio input of "stored procedure" is not enabled. How can i specify a stored procedure? Thanks for help Phil Tag: txn isolation level Tag: 129465
  - 24
    - A severe error occurred on the current command. sp_AddMergePublication This is a follow up to a posting I made which I can no longer update (http://groups.google.co.uk/group/microsoft.public.dotnet.framework.adonet/browse_thread/thread/272695bcd2e5e375/f2bbe776bafcb1e6#f2bbe776bafcb1e6) Issuing the command "sp_addmergepublication" via ADO.NET against a SQL Server 2000 SP3a instance results in the error "A severe error occurred on the current command. The results, if any, should be discarded". This is an issue with SQL Server SP3a and is resolved with SP4 Thanks Tag: txn isolation level Tag: 129463
  - 25
    - Can't fill dataset whith varchar(350) Why is it that when I try to fill my dataset with a column of type varchar(350) I get a error message "Object reference not set to an instance of an object? I my application bring back only 250 characters in the column and it works. I can query all of the data into Excel using my ODBC, so what do I need to do with the ADO dataset object to get it to play along? Detailed Error Message: Server Error in '/BIC_WEB_TEST' Application. -------------------------------------------------------------------------------- Object reference not set to an instance of an object. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.NullReferenceException: Object reference not set to an instance of an object. Source Error: Line 30: baseconnection.ConnectionString = ConnectString Line 31: baseconnection.Open() Line 32: BICDataAdapter.Fill(BICDataSet, "screening") Line 33: End Sub Line 34: Source File: c:\inetpub\wwwroot\BIC_WEB_Test\ODBCTest.aspx.vb Line: 32 Stack Trace: [NullReferenceException: Object reference not set to an instance of an object.] System.Data.Common.Odbc32.SQLGetData(HandleRef StatementHandle, Int16 ColumnNumber, Int16 TargetType, HandleRef TargetValue, IntPtr BufferLength, IntPtr& StrLen_or_Ind) +0 System.Data.Odbc.OdbcDataReader.GetData(Int32 i, SQL_C sqlctype, Int32 cb) System.Data.Odbc.OdbcDataReader.internalGetString(Int32 i) System.Data.Odbc.OdbcDataReader.GetValue(Int32 i, TypeMap typemap) System.Data.Odbc.OdbcDataReader.GetValue(Int32 i) System.Data.Odbc.OdbcDataReader.GetValues(Object[] values) System.Data.Common.SchemaMapping.LoadDataRow(Boolean clearDataValues, Boolean acceptChanges) System.Data.Common.DbDataAdapter.FillLoadDataRow(SchemaMapping mapping) System.Data.Common.DbDataAdapter.FillFromReader(Object data, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue) System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords) System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) BIC_WEB_TEST.ODBCTest.Page_Load(Object sender, EventArgs e) in c:\inetpub\wwwroot\BIC_WEB_Test\ODBCTest.aspx.vb:32 System.Web.UI.Control.OnLoad(EventArgs e) System.Web.UI.Control.LoadRecursive() System.Web.UI.Page.ProcessRequestMain() -------------------------------------------------------------------------------- Version Information: Microsoft .NET Framework Version:1.1.4322.2032; ASP.NET Version:1.1.4322.2032 Tag: txn isolation level Tag: 129460
- Next
  - 1
    - newbie working with generated code (vb.net & visual studio 2003) Hi, I'm using VB.Net to create a windows form that will insert data from an xml file into an SQL Server Express database. As I'm new to VB.Net, i'm using the drag and drop method in Visual Studio 2003 for datasets, dataadapters etc. I open a file and insert photo details about an employee. The employee will have more than one photo/photo details. I have a function that opens the file, counts the amount of photos related to the employee in the xml file and then inserts into the database. <employee_detail> <lastname>smith</lastname> <firstname>tom</firstname> </employee_detail> <photo_detail> <filename>\my documents\my pictures\hpim0025.jpg</filename> <description>la;ksdfj</description> </photo_detail> <photo_detail> <filename>\my documents\my pictures\hpim0026.jpg</filename> <description>alkjf</description> </photo_detail> I have two tables in the database, employee_detail and photo_detail I'm using autogenerated primary keys in both tables (empid, photoid) I'm posting empid as a foreign key to photoid I use SELECT @@IDENTITY AS empid This works okay if i've just got one photo_detail element but i want to use more than one. I took the code from the "Windows Generate Region" and put it in my own function. After reading in the file into my DataSet, I count the amount of photo_detail elements and want to do that many inserts into the database. Am I allowed do the following: ********************************************************************* 'cut from "Windows Generated" and pasted into Private Sub Open_Click () function Me.SqlDataAdapter7.InsertCommand = Me.SqlInsertCommand7 Me.SqlDataAdapter7.SelectCommand = Me.SqlSelectCommand7 Me.SqlDataAdapter7.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "photo_detail", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("photoid", "photoid"), New System.Data.Common.DataColumnMapping("empid", "empid"), New System.Data.Common.DataColumnMapping("filename", "filename"), New System.Data.Common.DataColumnMapping("description", "description")})}) Me.SqlSelectCommand7.CommandText = "SELECT photoid, empid, filename, description FROM photo_detail" Me.SqlSelectCommand7.Connection = Me.SqlConnection2 Me.SqlInsertCommand7.Parameters.Add(New System.Data.SqlClient.SqlParameter("@empid", System.Data.SqlDbType.Int, 4, "empid")) Me.SqlInsertCommand7.Parameters.Add(New System.Data.SqlClient.SqlParameter("@filename", System.Data.SqlDbType.VarChar, 2147483647, "filename")) Me.SqlInsertCommand7.Parameters.Add(New System.Data.SqlClient.SqlParameter("@description", System.Data.SqlDbType.VarChar, 2147483647, "description")) For st As Integer = 1 To val Step 1 Me.SqlInsertCommand7.CommandText = "INSERT INTO photo_detail(empid, filename, description) VALUES (@empid, " & _ "@filename, @description)" Me.SqlInsertCommand7.Connection = Me.SqlConnection2 Next I then use SqlDataAdapter7.Update(DataSet1) At the moment I'm just getting null in the foreign key empid field of the photo_detail table. Is it possible to use the code from "Windows Generated" elsewhere in the code ? Is this a completely stupid way of doing this ? I wanted to get a handle on .Net and thought that the drag and drop methods would be a quick way to ease into it, for what would seem a simple problem. Apologies if i've phrased this badly and many thanks in advance alan Tag: txn isolation level Tag: 129446
  - 2
    - Connection remain open hi everybody I have an assembly that contain Datasets, this DataSets connect to one SQL server 2000 database and retrive information to be use for a web Service. The problem is that when the Datasets (TableDataAdapter) connect to the database this leave one connection open (for each user), I used the using and Dispose keyword for try to close this connection but nothing. Somebody know how to fix this. I am using Net 2.0 Best Regards Victor Hugo Pozo MCAD Net Tag: txn isolation level Tag: 129432
  - 3
    - Datatable Datetime column remove zeroes I have added calculated column to datatable and expression looks like this: "somecolumn + datetimecolumn1" now, i do not want zeroes from datetime, just date. How to do it? Thanx. my DataTable is filled via adapter from SqlServer. Tag: txn isolation level Tag: 129427
  - 4
    - datagridview Hi, How to put conbobox column or any other column into datagridview. please provide me with code in c#. Thanks in advanc -- mahimam_200 ----------------------------------------------------------------------- mahimam_2004's Profile: http://www.hightechtalks.com/m1 View this thread: http://www.hightechtalks.com/t37752 Tag: txn isolation level Tag: 129425
  - 5
    - Executing MS Access Queries using ADO.NET I'm currently trying to run queries in an Access database using ADO.NET. My code works when executing a query whose name has no spaces but errors out if I try to run a query whose name has spaces in it. Example: When running a query named: "My Query" I receive an error message saying a query with the name "My" cannot be found. Is there any way to get around this problem? Thanks. Tag: txn isolation level Tag: 129423
  - 6
    - Connection I mm developping a winforms application. I use a form do include, alter and delete data. I created a connection using the wizard. I would like that each user uses their own password to log in the SQL Server. I noticed the the connection string is in a file named APPNAME.EXE.CONFIG, and the XXXDataSet.Designer.cs file reads this connectionstring to log into the SQL. I would like to know if there is a way to change this connection string, at runtime, except changing the APPNAME.EXE.CONFIG file. Modifing the APPNAME.EXE.CONFIG works fine. The problem is that the password of the last user remain in the file. Tag: txn isolation level Tag: 129421
  - 7
    - where clause I have an arbitrary number of values in the WHERE clause: select foo from bar where foobar = '1' and foobar = '2' ... and foobar = 'n-1' and foobar = 'n' What's the best way to program this? Thank you. Tag: txn isolation level Tag: 129420
  - 8
    - invalid path error while accessing FOXPRO table from c# HI All, i have a problem accessing a DBF file from c#. this is the code i have specified string strConnection = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\mmyyyy.dbf;Extended Properties=DBASE IV;"; OleDbConnection objConnn = new OleDbConnection(); objConnn.ConnectionString = strConnection; try { objConnn.Open(); } catch(Exception eee) { MessageBox.Show(eee.ToString()); } objConnn.Close(); it says System.Data.OleDb.OleDbException: 'c:\mmyyyy.dbf' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides. at System.Data.OleDb.OleDbConnection.ProcessResults(Int32 hr) at System.Data.OleDb.OleDbConnection.InitializeProvider() at System.Data.OleDb.OleDbConnection.Open() at WindowsApplication2.Form1.button3_Click(Object sender, EventArgs e) in d:\documents and settings\administrator\my documents\visual studio projects\windowsapplication2\form1.cs:line 426 it is strange because i have been connection to the local computer not a networkd one and + i have been accessing this since past few days without a problem i have installed Microsoft's oledb drivers for foxpro + MDAC PN :- i have tries this connection string also "Provider=VFPOLEDB.1;Data Source="C:\mmyyyy.dbf;" thanks, abhay. Tag: txn isolation level Tag: 129413
  - 9
    - ADODC refresh method fails after upgrading from XP SP1 to XP SP2 After upgrading from XP SP1 to XP SP2 get "operation is not allowed when the object is open" error messege. the app in production stop working properly. The refresh method is available, but fails. Here is the code: With Adodc1 .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & App.Path + "\PhoneNumbers.mdb" & "; Mode=Read|Write" .RecordSource = "select * from PhoneNumbers Order By Name" .Refresh End With I need a solution in production,i.e I won't install the IDE in production. Any ideas? Tony Tag: txn isolation level Tag: 129395
  - 10
    - Provider independent data access: creating a data adapter from a I have written a provider independent ado.net utility class for executing queries. One of the routines looked like this in .net 1.1: static public DataSet ExecuteQuery(IDbConnection connection, String sql) { connection.Open(); DataSet result = new DataSet(); IDbDataAdapter adapter = /* magic to create an adapter from a connection*/ // The sybase adapter's implement IDisposable, so wrap with a using. using(adapter as IDisposable) { adapter.SelectCommand.CommandText = sql; adapter.SelectCommand.Connection = conn; adapter.Fill(result); } return result; } The magic code to create the adapter was ugly: it essentially performed a dictionary lookup on the type of connection, and created the resulting adapter type. If IDbConnection had a method called CreateAdapter, analagous to CreateCommand, this would have been cleaner. With the introduction of ADO.NET 2.0 and the DbProviderFactory class, I was hoping that some of the ugliness would go away. Unfortunately, I am running into some problems. Namely 1- DbConnection still does not have a CreateAdapter method 2- DbConnection does not offer a way to get back to the DbProviderFactory that created it. So I still cannot create the correct adapter given only a generic DbConnection. I am looking for suggestions on how to code my routine. So far, I have thought of these 1- Make the user pass in the DbProviderFactory that goes with the connection. CONS: forces users to use DBProviderFactory. We have a lot of code that doesn't. 2- Stick with the dictionary lookup (Dictionary<ConnType, AdapterType>. CONS: DLL dependencies explode. 3- Use a different dictionary lookup (Dictionary<ConnType, DBProviderFactory>. CONS : A little messy, but better than 2. 4- Don't use provider specific adapters at all, just use a generic one. Like this: class GenericAdapter : System.Data.Common.DbDataAdapter { } static public DataSet ExecuteQuery(DbConnection connection, String sql) { connection.Open(); DataSet result = new DataSet(); GenericAdapter adapter = new GenericAdapter(); adapter.SelectCommand = conn.CreateCommand(); adapter.SelectCommand.CommandText = sql; adapter.Fill(result); return result; } I have tested this with two databases, Sybase ASE and ASA, and it seems to work. Are there any serious downfalls to not using the vendor specific adapter type? Is this code safe? H^2 Tag: txn isolation level Tag: 129393
  - 11
    - scope_Identiy() problem vs @@Identity with daAdapter.InsertCommand Hello, I am having a problem using Scope_Identity() when inserting a row to a sql server (sql server 2000 standard Edition) table using a sql DataAdapter.InsertCommand. The row gets inserted but it does not display on the datagridview on my client app (vb2005). I just get a blank row. But if I use @@Identity in the return statement then the new row shows up correctly in the datagridview. The code I am using follows. Note: I used this same code on Sql server 2000 personal edition - loaded on my workstation, and I did not have a problem using Scope_Identity(). But on the Standard Edition - which is server based - I have a problem using Scope_Identity(). The newly inserted row shows up in the datagridview but does not display any data when I use Scope_Identity(). If I clear the dataset (ds) and refill it - then the new row shows up with the new data. If I use @@Identity then the new rows shows up and displays the new data in the datagridview right away. Is there something I need to change when using Scope_Identity()? Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click Dim dr As DataRow, i As Integer dr = ds.Tables("tbl1").NewRow ds.Tables("tbl1").Rows.Add(dr) da.InsertCommand = New SqlCommand da.InsertCommand.Connection = conn '--this insert command returns data correctly 'da.InsertCommand.CommandText = "Insert Into tbl1(tName, fld2) Values(@p1, @p2); Select tID, tName, fld2 from tbl1 Where tID = @@Identity" '--this insert command does not display any return data da.InsertCommand.CommandText = "Insert Into tbl1(tName, fld2) Values(@p1, @p2); Select tID, tName, fld2 from tbl1 Where tID = Scope_Identity()" da.InsertCommand.Parameters.Add(New SqlParameter("@p1", SqlDbType.VarChar, 50)) da.InsertCommand.Parameters.Add(New SqlParameter("@p2", SqlDbType.VarChar, 50)) da.InsertCommand.Parameters("@p1").Value = "test5" da.InsertCommand.Parameters("@p2").Value = "test6" da.Update(ds, "tbl1") '--this will write the new Identity value when using @@Identity but not Scope_Ident Console.WriteLine("*" & dr.Item(0).ToString & "*") End Sub Thanks, Rich Tag: txn isolation level Tag: 129372
  - 12
    - insert parent-child rows with transaction hi, i have a typed dataset containing two data tables and a data relation which links the two tables in a parent-child manner. i use a sqltransaction to control the insertion of records into the database. here is the code for the insertion. foreach (SeminarData.ActivityRow activity in dsSeminar.Activity) { int newActiviyId = InsertActivity(activity); foreach (SeminarData.ActivitySpeakerRow speakerRow in activity.GetChildRows("relActivity_ActivitySpeaker")) { speakerRow.ActivityId = newActiviyId; InsertActivitySpeaker(speakerRow); } } the two Insertxxx() methods each takes a datarow and sets up a datacommand and executes the command. after i have inserted a parent row, i take the returned auto-incremented ID of the parent row, assign it to the child row, and try to insert the child row. however, because this is done in a transaction, the new ID of the parent row is probably not yet inserted into the database, so i keep getting this error: "ForeignKeyConstraint relActivity_ActivitySpeaker requires the child key values (691) to exist in the parent table." 691, is this case, is the new ID of the parent row which is supposed to have been inserted. It seems that I may have two options, 1. commit the transaction before inserting the child row. but that defeats the whole purpose of using transaction as the first place. 2. Somehow update the in-momery parent rows with the new IDs. however, when i try to assign the new IDs to each parent row, i get error message saying that the ID column is read-only, cannot be updated, .understandable. So, how can i insert parent-child rows within a transaction?? I don't want to go for a dataadapter, it would be too much change to the dataaccess code. please enlighten me. Tag: txn isolation level Tag: 129367
  - 13
    - OleDbDataAdapter.Fill throws an error (DB_E_BADROWHANDLE) when .NET 2.0 is installed. When I use the OleDbDataAdapter.Fill to fill a DataSet with a ADODB.Recordset an error occurs ("No error message available, result code: DB_E_BADROWHANDLE(0x80040E04)." If only the .NET Framework 1.1 is installed everything works fine. What's wrong (or changed)? Tag: txn isolation level Tag: 129365
  - 14
    - How can I join 2 bianry files together back to back? How can I join 2 bianry files together back to back? Thanks dee Tag: txn isolation level Tag: 129363
  - 15
    - sorting in parent form Hi I'm trying to sort a parent form(vb.net), can anybody help thanks From http://www.developmentnow.com/g/7_0_0_0_0_0/dotnet-framework-adonet.ht Posted via DevelopmentNow.com Group http://www.developmentnow.com Tag: txn isolation level Tag: 129358
  - 16
    - sorting of parent forms Hi I can't seem to get the code right to sort the parent form, i can get the child form to sort. Can anyone help Thanks A -- Message posted via http://www.dotnetmonster.com Tag: txn isolation level Tag: 129356
  - 17
    - Implement ADO.NET Row Level Pessimistic Locking I am trying to implement record level pessimistic locking using ADO.NET and SQL Server 2000. A reduced structure of the table is shown: CarrierName(varchar) CarrierCode(varchar) RefundStatus(int) ------------------------------------------------------------------------ -------- US Postal Service USPS 1 US Postal Service USPS 2 Canada Postal Service CPC 1 UK Postal Service RMG 1 UK Postal Service RMG 2 I have a win form application(C#), which on a button click selects only the RMG records and then updates these records. The requirement is that, during this operation these RMG records must be pessimistically locked so that no other application can update these records during this time, but the USPS and CPC records should remain updateable. After browsing through different forums, I realised that ADO.NET does not support pessimistic concurrency directly but there is a workaround using transaction. This is how I implemented the solution: private const string DBConnectionString = "Database=BnBCSO; Server=10.20.1.19;UID=sa;PWD=rebaca;" + "Connection Timeout=15;Enlist=False;Max Pool Size=150; Min Pool Size=5;"; private IsolationLevel oIsolationLevel = IsolationLevel.Serializable; private string SelectStatement = "select * from PackageRefund where CarrierCode = 'RMG'"; private void Process() { SqlConnection oConn = null; SqlTransaction oTrxn = null; SqlDataAdapter oDataAdapter = null; DataSet ds = new DataSet(); try { oConn = new SqlConnection( DBConnectionString ); oConn.Open(); oTrxn = oConn.BeginTransaction( oIsolationLevel ); SqlCommand oCommand = new SqlCommand( SelectStatement, oConn, oTrxn ); oDataAdapter = new SqlDataAdapter( oCommand ); oDataAdapter.Fill( ds ); for( int i = 0; i < ds.Tables[0].Rows.Count; i++ ) { Thread.Sleep( 5000 ); ds.Tables[0].Rows[i]["RefundStatus"] = 4; } SqlCommandBuilder oCommandBuilder = new SqlCommandBuilder( oDataAdapter ); oDataAdapter.Update( ds ); oTrxn.Commit(); } catch( Exception exc ) { oTrxn.Rollback(); } finally { if( oDataAdapter != null ) { oDataAdapter.Dispose(); oDataAdapter = null; } if( oTrxn != null ) { oTrxn.Dispose(); oTrxn = null; } if( oConn != null ) { oConn.Close(); oConn.Dispose(); oConn = null; } } } private void cmdStart_Click(object sender, System.EventArgs e) { try { Thread oThread = new Thread( new ThreadStart( Process ) ); oThread.Start(); } catch( Exception exc ) { } } The issue is that after I have started the program, I go to SQL Query Analyzer and execute these sql statements separately: update [TABLE] set RefundStatus = 3 where CarrierCode = 'RMG' update [TABLE] set RefundStatus = 3 where CarrierCode = 'USPS' update [TABLE] set RefundStatus = 3 where CarrierCode = 'CPC' If record level pessimistic locking was properly implemented, then the USPS and CPC updates should have succeded while the RMG one should wait/timeout. But in this case, all the three update statements are waiting and eventually timing out, possibly waiting for the lock to be released. That means that this solution has implemented a table lock rather than a row lock. What do I have to change if I want only the selected records to be locked while the other records in the same table should be updateable. Thanks. -- Sent via .NET Newsgroups http://www.dotnetnewsgroups.com Tag: txn isolation level Tag: 129355
  - 18
    - How to disable a Constrain I am use the new feature of TableAdapter to develop a web application. I use a ObjectDataSouce to link to a TableAdapter The TableAdapter have two constrains. How can I disable one of the contrain when the ObjectDataSouce get data? Tag: txn isolation level Tag: 129353
  - 19
    - <<< State of the art web solution Online>>> <<< State of the art web solution Online>>> WickedInnovations has an extensive working experience in E-commerce solutions, web site design , website development , graphic design , search engine optimization , database integration, multimedia presentations, corporate Identity and a lot more. Wicked Innovations have proven our ability to deliver professional and state of the art web solutions for many e-business verticals. Multimedia Flash Animation Demos/Presentation Flash E-Commerce Video Editing /Encoding Web HTML Websites Flash Websites User Interface Design Web/ Flash Templates Programming Custom Web App Development Script Installation & Revision bsite Administration Production Support WickedInnovations specializes in, from complex integrated solutions for business and advanced multimedia products. Why spend more if you can get the same quality, productivity and performance from an offshore partner? http://wickedinnovations.com/contact.html http://www.wickedinnovations.com/request.html Tag: txn isolation level Tag: 129352
  - 20
    - Silent install for SQL Express? If I develop a SQL Express DB for my app, how do I distribute SQL Express also? Is there a silent install for SQL Express (like for MSDE)? Also, how do I put my DB in the new SQL Express instance and attach to it instead of to my local development copy? Tag: txn isolation level Tag: 129351
  - 21
    - oos! I got an "invalid authorization specification" when testing my connection to Access. What's going on??? I've been working on this app for some time now. Been developing with vs 2005 vb.net and an Access database. Something happened and I can't figure out what caused it or how to correct it. I drop a OleDBAdapter on the form and the data adapter configuration wizard appears. I then click on "New Connection" to choose an Access db other than the one being displayed. The "Add Connection" window appears and the data source box shows "Microsoft Access Database File (OLE DB)". Good so far. I click on browse and select the Access db I want to use. The new file name appears in the database file box. I click on "Test Connection" and the "invalid authorization specification" error pops up and prohibits me from setting up the new coonection. There are no passwords on this db. What did I do to cause this??? Thanks, Hexman P.S. In the Provider property is "SQLNCLI.1", which I can't change. Tag: txn isolation level Tag: 129336
  - 22
    - RE: Update() and stored procedures I want to update for one stored procedure. How? Here the exist stored, .. create procedure abc as select d2.maso,d2.namthang,d2.n1,d2.n2,d2.n3,d1.* from dmns d1 left outer join bangchamcong d2 on (d1.maso=d2.maso and '200607'=d2.namthang) and i want: ... update proc abc set n1='1' go is it right? pls help me! From http://www.developmentnow.com/g/7_2003_7_0_0_124105/Update-and-stored-procedures.ht Posted via DevelopmentNow.com Group http://www.developmentnow.com Tag: txn isolation level Tag: 129328
  - 23
    - Load typed dataset What is the preferred way to read (not write) the records of a typed dataset in C# without use of a form? thanks, Jeff Tag: txn isolation level Tag: 129319
  - 24
    - SQLHelper STored Procedure Output Parameter Hi, All How to get the output parameter's value when you use the SQLHelper (Microsoft Data Access Block)? When I try to access my ourput parm I got the following error. System.NullReferenceException: Object reference not set to an instance of an object. I thought it is because there is a statement cmd.Parameters.Clear() in ExecuteNonQuery function, but even I comment this parameter clear statement, it still caught the same error. Before ExecuteNonQuery return, The output value is still in the output parm. but when return to SqlHelper.ExecuteNonQuery(), the output parameter value got lost while the vlaue of input parameter is still there. Did I miss something? Or SQLHelper can not return output parameter? Help Please ---------------------------------------------------------------------------= =AD------------------ public string TestOutput(int pFlag){ SqlParameter[] parm =3D new SqlParameter[3]; parm[0] =3D new SqlParameter("@flag", SqlDbType.Int,4); parm[0].Value =3D pFlag; parm[1] =3D new SqlParameter("@result_txt", SqlDbType.VarChar,32); parm[1].Direction =3D ParameterDirection.InputOutput; parm[2] =3D new SqlParameter("@result_txt", SqlDbType.Int,4); parm[2].Direction =3D ParameterDirection.InputOutput; // parm[2].Direction =3D ParameterDirection.Output; SqlHelper.ExecuteNonQuery(ConnectionString, "UDSP_OUTPUT_TEST", parm); return parm[1].Value.ToString(); } ---------------------------------------------------------------------------= =AD----------------- CREATE PROCEDURE UDSP_OUTPUT_TEST @flag int, @result_txt varchar (32) output, @result_code int output AS begin set nocount on if @flag=3D1 begin select @result_txt =3D 'ERROR RETURNED FROM SP' select @result_code =3D -1 return end else begin select @result_txt =3D 'SUCCEED RETURNED FROM SP' select @result_code =3D 0=20 end=20 set nocount off=20 end Tag: txn isolation level Tag: 129309
  - 25
    - System.Data.ObjectSpaces - Is this namespace included in framework 2.0 I am trying to figure out if object spaces are included in .net framework 2.0. If so, I am trying to find the dll called System.Data.ObjectSpaces.dll Does anybody know this? Tag: txn isolation level Tag: 129303

Hi,
Should the transaction isolation level persist across the reuse of a
connection from the connection pool?

From my experiment it does persist and I was not expecting it.
I was under the impression that "sp_reset_connection" will tear down all the
connection level settings and go back to the defaults.

I am using the sql .net provder with the 1.1 framework and connecting to a
sql 200 server.

Just wondering if this is a bug or by design ...

--
Vikram Vamshi
Database Engineer
Eclipsys Corporation

Top