How do i perform intersection in 2 datsets??

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
    - Datarelation query If i need to relate two tables using datarelation on a column "col1" out of which,table1 i want to make as parent table contains duplicate entries in "col1" and i want to retrieve all the rows present in table2 having matching rows on "col1" of only ("col1" and "col2") of table2. *** Sent via Developersdex http://www.developersdex.com *** Tag: How do i perform intersection in 2 datsets?? Tag: 126909
  - 2
    - Data Access Layer design question Hi. I have a quick question. I am trying to create a quick data access layer for command CRUD tasks using stored procedures. My question is more of a design point of view. In each of my methods Create, Read, Update, and Delete, it seems I am repeating a lot of code such as: <Code> // Create the connection and Open it SqlConnection conn = new SqlConnection(connectionString); conn.Open(); cmd = new SqlCommand(storedProcedure, conn); // Set the type of command to a stored procedure cmd.CommandType = CommandType.StoredProcedure; // Add the SP parameters to the command cmd.Parameters.AddRange(parameters); </Code> What's the opinion on this? Should I create a connection in a method and pass it back or just repeat this chunk of code in each method? I read some articles that mentioned that when passing connection references around one needs to be careful to close connections, etc. Tag: How do i perform intersection in 2 datsets?? Tag: 126903
  - 3
    - how to INSERT records to an OLEDB target ?? Hi I have a datatable with several field and, particularly, a binary field (byte[]) (taken from some database) using the OleDB connection. Now I want to INSERT all the record of this table into a newly CREATEd table of another database (say mysql or access or sybase or whatever: just assume it be an arbitrary OleDb connection). I am connected via the OleDb connection. My question. What is the best way to insert these records (I would like to be able to do the insert whatever be the target (always OleDb)). Any snippet would be much appreciated. I have already seen several posting on how to use PARAMETERS, @field, BUT my greatest concern is the binary field. If you could elaborate a little on the best way to upload the BINARY stuff, I would be grateful. I have no idea how to deal with it. Is it sufficient that I assign the byte array to the Value property of a parameter? Or would this not make any sense? -Pam Tag: How do i perform intersection in 2 datsets?? Tag: 126902
  - 4
    - TableAdapter vs DataAdapter performance Hi, Thinking of the best way to develop a data acces layer for a new program I have been doing some performance test. My lab pilot is as follows: SqlServer 2000 + VS2005 TS in the same machine. Storage procedure returning 100.000 records from a table ----------------------------------------------- DataTable dt = new DataTable(); ArticulosTableAdapter ta = new ArticulosTableAdapter(); SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(sqlCommand);. Connected Model: ----------------------------- puno = Environment.TickCount; sqlCommand.Connection.Open(); SqlDataReader sqlDim = sqlCommand.ExecuteReader(); dt.L.oad(sqlDim); sqlDim.Close(); sqlCommand.Connection.Close(); time = TimeSpan.FromMilliseconds(Environment.TickCount - puno); Trace.Write("Load From DataReader Time-> "); Trace.WriteLine(time); Running: Load From DataReader Time-> 00:00:02.3440000 Disconnected Model: ------------------------------------ 1) TypedDataSet with DataAdapter puno = Environment.TickCount; da.Fill(_articulosDS); time = TimeSpan.FromMilliseconds(Environment.TickCount - puno); Trace.Write("Typed DataSet With DataAdapter Time-> "); Trace.WriteLine(time); Running: Typed DataSet With DataAdapter Time-> 00:00:00.5620000 2) TypedDataSet with TableAdapter puno = Environment.TickCount; ta.Fill(_articulosDS.Articulos); time = TimeSpan.FromMilliseconds(Environment.TickCount - puno); Trace.Write("Typed DataSet With TableAdapter Time-> "); Trace.WriteLine(time); Running: Typed DataSet With TableAdapter Time-> 00:00:02.6250000 I understand that filling a DataTable with a DataReader is a time consuming process that will take 2,34 secs for 100.000 records, but I do not understand why Filling a TableAdapter I am getting poor performance (3 times less) than Filling a DataAdapter. Any Idea ? Thank you José Ramón Tag: How do i perform intersection in 2 datsets?? Tag: 126896
  - 5
    - Databinding Problem Hi, I am working on a small windows app that pulls data from a SQL Server table and binds the data to the controls for the windows form. One field in the SQL Server table has a data type of bit. I do allow Nulls on the field. This bit field in my dataset was bound to the check state of a checkbox. What was happening, is that if the field value was NULL then the form would not display any data in this control or any other control on the form and never generated an error. Does anyone know if this behavior is documneted anywhere, or if this same type of thing happens with databinding other kinds of controls? Thanks Tag: How do i perform intersection in 2 datsets?? Tag: 126887
  - 6
    - Q311522 Is this issue fixed in VS.NET 2005 release ?? I have VS.NET Beta2 and still have problems with Columns in DataSets (designed with the designer) containing expressions involving relations (child/parent). Indeed when I call ds.GetChanges() it launches an exception saying that the relation is not initialized. Where can I find a list of fixes in VS.NET 2005 release from VS.NET Beta2 ?? Thanks ! Tag: How do i perform intersection in 2 datsets?? Tag: 126886
  - 7
    - Oracle Lob (blob) Read / Write woes VS 2005, VB, .Net Frameword 2.0, Oracle 9i Database, System.Data.OracleClient I am trying to insert documents (word, pdf, images) into a BLOB data column in an Oracle 9i database using the System.Data.OracleClient. I have permissions on the database and the connection works to retrieve records. There appears to be very little help or documentation for using the System.Data.OracleClient in Visual Basic 2005 so I took a C# example from (.NET Framework Developer's Guide / Working with Oracle LOBs) and converted it to VB. I am getting stumped by the CreateTempLob function with an Oracle error (#01036: illegal variable name/number) when trying to execute the blobCmd.ExecuteNonQuery. Ugh. My code is below, again converted from C# to VB. Any suggestions greatly appreciated and eagerly attempted. Thank you. ***** BEGIN CODE CLASS ***** Imports System Imports System.Data Imports System.Data.OracleClient Imports System.Windows.Forms Imports System.IO Imports System.Collections.Generic Public Class BlobImports Public Sub DoBlob(ByVal blobPath As String, ByVal blobFilename As String, ByVal blobID As String) 'create connection Dim cnBlob As New OracleClient.OracleConnection("Data Source=database;Persist Security Info=True;User ID=user;Password=pword;Unicode=True") Try With cnBlob ' open connection .Open() 'create command object Dim cmdBlob As OracleClient.OracleCommand = cnBlob.CreateCommand() 'select text cmdBlob.CommandText = "SELECT BLOB_ID, BLOB_DESC FROM BLOBS WHERE BLOB_DESC = :pBlobDesc" 'add parameter cmdBlob.Parameters.Add(":pBlobDesc", OracleType.VarChar) cmdBlob.Parameters(0).Value = blobFilename 'read records ReadBlobRecords(cmdBlob) 'update command text for update cmdBlob.CommandText = _ "SELECT BLOB_DESC, BLOB FROM BLOBS WHERE BLOB_DESC = :pBlobDesc FOR UPDATE" 'update record with blob item - causes error in createtempblob function WriteBlob(cmdBlob, blobPath, blobFilename) End With Catch ex As Exception MsgBox(ex.ToString & vbCrLf & ex.Message, MsgBoxStyle.OkOnly, "Foo") Finally cnBlob.Close() End Try End Sub Private Sub ReadBlobRecords(ByVal blobCommand As OracleClient.OracleCommand) Dim blobTrans As OracleClient.OracleTransaction = blobCommand.Connection.BeginTransaction blobCommand.Transaction = blobTrans Dim blobReader As OracleClient.OracleDataReader = blobCommand.ExecuteReader() While blobReader.Read lbBlobs.Items.Add(blobReader.GetOracleString(1)) End While blobTrans.Commit() End Sub Private Sub WriteBlob(ByVal blobCommand As OracleClient.OracleCommand, ByVal blobPath As String, ByVal blobName As String) 'read file into binary Dim fi As New FileInfo(blobpath & "\" & blobname) Dim sr As New StreamReader(fi.FullName) Dim tempBuffer As String = sr.ReadToEnd sr.Close() 'begin transaction to post blobs Dim blobTrans As OracleClient.OracleTransaction = blobCommand.Connection.BeginTransaction blobCommand.Transaction = blobTrans Dim blobReader As OracleClient.OracleDataReader = blobCommand.ExecuteReader While blobReader.Read Dim oBlobFile As OracleClient.OracleLob = blobReader.GetOracleLob(1) ' call to create temp blob throws oracle error Dim inBlobFile As OracleClient.OracleLob = CreateTempLob(blobCommand, oBlobFile.LobType) Dim lngBlob As Long = oBlobFile.CopyTo(inBlobFile) blobTrans.Commit() End While End Sub Private Function CreateTempLob(ByVal blobCmd As OracleClient.OracleCommand, ByVal blobType As OracleClient.OracleType) blobCmd.CommandText = _ "DECLARE A " & blobType & "; BEGIN DBMS_LOB.CREATETEMPORARY(A, FALSE); :tmpBlob = A;" Dim pBlobTemp As OracleParameter = blobCmd.Parameters.Add("tmpBlob", blobType) ''pBlobTemp.Value = Nothing pBlobTemp.Direction = ParameterDirection.Output ' here is where the error is occuring blobCmd.ExecuteNonQuery() Return pBlobTemp.Value End Function ***** END CODE CLASS ***** Thank you again for assistance. -- JonSteng ~ Making Magic in Sunny Central Florida ~ Tag: How do i perform intersection in 2 datsets?? Tag: 126883
  - 8
    - Binding of textboxes to a empty table Hi folks, I am looking for a solution of an "simple" problem, but I dont found it. I have a dataset with to tabels on it. On a Form i have a lot of TextBoxes to display the data from two database tables. The databinding of the TextBoxes is linked to two BindingSource-Objects (.NET 2.0). BindingSource1 connect to table1 at my dataset, BindingSource2 connect to table2. That works fine (changed data are updated at the database) if both tables are filled with rows. If table2 have no rows (no address of a person is known) it is possible to fill in data into the textboxes, but the data are not saved into the dataset (no row is created and filled). What is the best way to solve this? Is there any event of BindingSource? Table? (or what?) witch give me the information that a new rows is needed. Is it possible to force the BindingSource to create an new Row? Because of the checks at my dataset i need to set some columns (foreign key ...) by myself. Thanks in advance for any help. Niels Tag: How do i perform intersection in 2 datsets?? Tag: 126881
  - 9
    - Exception message using EnterpriseLibrary.Data I'm getting an exception message using EnterpriseLibrary.Data block. In the profiler I can see that the sql i fired in the sql database. Imports Microsoft.Practices.EnterpriseLibrary.Data Dim db As Database = DatabaseFactory.CreateDatabase Dim cmd As DBCommandWrapper = db.GetSqlStringCommandWrapper(sql) Dim ds As DataSet = db.ExecuteDataSet(cmd) I'm getting the following ex.Message: "The type initializer for "Microsoft.Practices.EnterpriseLibrary.Data.Instrumentation.DataCommandFailedEvent" threw an exception." I'm not sure what this is all about! \M Tag: How do i perform intersection in 2 datsets?? Tag: 126879
  - 10
    - DataReader Can anybody tell me how SqlDataReader and OracleDataReader process the encoding of contents from database when it's GetString(or GetSqlString,GetOracleString) method is called? I have been annoyed on this issue for several days and searched many materials but no help. -- Zhou Peng Tag: How do i perform intersection in 2 datsets?? Tag: 126878
  - 11
    - Encoding about GetString method of DataReader Can anybody tell me how SqlDataReader and OracleDataReader process the encoding of contents from database when it's GetString(or GetSqlString,GetOracleString) method is called? I have been annoyed on this issue for several days and searched many materials but no help. -- Zhou Peng Tag: How do i perform intersection in 2 datsets?? Tag: 126877
  - 12
    - CONNECTING TO EXTERNAL DATABASE Hi, I'm trying to make a new connection to a external database, it's not local, so what is the direction I must write to connect to it? It is the www... or I must write the IP of the server where it is stored? Thanks! Ángel Tag: How do i perform intersection in 2 datsets?? Tag: 126874
  - 13
    - Simple DataRelation question ??? Dear all, Does the way of creating a datarelation between a parent and a child based on more that one column , is going to create a FK first ?? thnaks regards serge Tag: How do i perform intersection in 2 datsets?? Tag: 126871
  - 14
    - Store procedure vs Direct statement ??? Dear all, Is there any restriction or rules when you must you Store procedure call within application code compare to direct SQL statement. For sure the first reason is data security as all querry are executed on the server. But is there some cases where store procedure should be avoid ? For my case if data security is a must, the first thing would be to always use strore procedure Any comments are welcome Regards Serge Tag: How do i perform intersection in 2 datsets?? Tag: 126862
  - 15
    - Columns from two tables in one datagridview row Hi, Im using Visual Studio 2005 and playing around with all the new ADO .Net 2.0 features, I am hoping that what I am chasing is now available. I am wanting to display an updatable row (don't need insert or delete) which displays columns from two tables which have a one to one relationship. The following is the sql query which produces exactly what I am after: SELECT d_client.client_id, d_client.client_status, d_client_members.member_id, d_client_members.phone_mobile FROM d_client LEFT OUTER JOIN d_client_members ON d_client.client_id = d_client_members.client_id WHERE (d_client_members.flag_primary_contact = 1) OR (d_client_members.flag_primary_contact IS NULL) I have run this query in sql server and it returns 1 row per client which is exactly what I want. In vs 2005 im not sure how to use this query correctly so instead I have set the relationship up between the two tables and used seperate select queries in each table in the dataset designer. When I set the datagridview to the d_client binding source, i get a parent row with the d_client information and then a child row with the d_client_members details. I know I am doing something wrong here but all the new features have me a little overwhelmed... Any help will be greatly apprecaited Cheers Paul Tag: How do i perform intersection in 2 datsets?? Tag: 126852
  - 16
    - UniqueIdentifier Sql Data and Parameters Hi everybody, I have a column in one of tables in one of the sql server that has a data type of UniqueIdentifier. Now, I am supplying a value to used this column as filter to get specific record. Note: strCompId is in string datatype. [code] adoOleDbDataAdapter2.SelectCommand.Parameters.Add("@compid", OleDbType.Guid, Me.strCompId.Length, "COGUID").Value = Me.strCompId [/code] The code shown above causes a conversion error. There is no conversion from string to GUID. Is there? [code] adoOleDbDataAdapter2.SelectCommand.Parameters.Add("@compid", OleDbType.VarChar, Me.strCompId.Length, "COGUID").Value = Me.strCompId [/code] The code above returns that "@compid" parameter does not exist. How can I solved this? Thanks. den2005 -- MCP Year 2005, Philippines Tag: How do i perform intersection in 2 datsets?? Tag: 126842
  - 17
    - ExecuteNonQuery returning -1 I have a stored procedure that adds a new record in the database and have another SP that updates a record. I used to call ExecuteNonQuery method on command object and it used to return 1 if method executed sucessfully. This was all fine and dandy in .Net1.1 Now in .Net 2.0 I use the same code and ExecureNonQuery returns -1 for both command objects. Any thing changed between 1.1 and 2.0 that I need to be aware of? This is causing a lot of code to break. Tag: How do i perform intersection in 2 datsets?? Tag: 126840
  - 18
    - Storing wave files in database Hi, in my current project I need to record audio data analyse them and store everything in some sort of database. I have not much experience with databases except for some small tests with ADO and MS Access, MySql and Paradox. The goal is to record sample files for test speakers and store the audio data, name and gender of the speaker and finally some analysis results. All this would happen on a local computer Win2K/XP. Network access, if needed at all, would be limited to a few users. My questions: 1) Can anyone give some advice as to whether it is reasonable to store large amounts of binary data in a database, wave files in my case? Or should I store file paths to external wave files? 2) In case I use an MS Access database: is there a limit on how many data I can store in it? The number of records wouldn't be that large but the total amount of data could be well beyond 2 GB. 3) Are there any performance issues in reading/storing large amounts of binary data from a database compared to file access for external referenced files? How about MS Access in particular? 4) If all of the above is absolutely nonsense, does anyone have any experience what a good solution would be for this task? thanks for any comment, Nicolas Tag: How do i perform intersection in 2 datsets?? Tag: 126839
  - 19
    - Very Strange: field value is not updated if you try to set it to its default value This is completely baffling me and I'm hoping someone can help me understand why this doesn't work. I'm updating existing rows in a MS SQL table by reading them into a datatable, modifying the values then updating the database using the update method of SqlDataAdapter. If I set a value of a field to the same value as the default value for that column the value resorts back to its previous value when I update the table. For example: The definition for Field1 is a varchar where Allow Nulls is false and the default value is ''. I have filled a datatable with the existing rows from my sql table. When I change Field1 to any value other than '' the data is saved correctly when I run the update method on the SqlDataAdapter. If I set the value of Field1 to '' the value is ignored and the previously saved value is saved. This is my select command: select Col_ID, Field1 from MyTable This my update command/parameter: sqlDA.UpdateCommand = new SqlCommand("update MyTable set Field1=@Field1 where Col_ID=@Col_ID", sqlCnn); sqlDA.UpdateCommand.Parameters.Add("@Field1", SqlDbType.VarChar, 50, "Field1"); sqlDA.UpdateCommand.Parameters.Add("@Col_ID", SqlDbType.Int, 4, "Col_ID"); To ensure that I wasn't completely insane, I watched the events in SQL Profiler and sure enough, my values were getting replaced with the original values. Following is the actual statement displayed in SQL Profiler. Notice the value for Field1 is being saved as SALEPR which is the original value: exec sp_executesql N'update MyTable set Field1=@Field1 where Col_ID=@Col_ID', N'@Field1 varchar(50),@Col_ID int', @Field1 = 'SALEPR', @Col_ID = 241 Since I was manually creating my update command I decided to try the same thing using the SqlCommandBuilder. Profiler reflected a far more complicated update command generated by CommandBuilder but the net result was the same; The sql statement was using the previously saved value, not my newly entered value. I should also point out that the RowState for the row was set to modified and I had not run the AcceptChanges method. I'm pretty sure that Profiler would not have displayed my update if either of these two conditions were different. I decided to check one more thing. I made sure that the last row in the datatable was modified to set Field1 to '' then ran the sqlDA.Update() and set a breakpoint on the line following the update then checked the value of the Field1 parameter and it was set to to 'SALEPR'. I also checked the value in the table row and it was still set to a blank string. One final thing to try... I added one more line of code immediately following sqlDA.Update() to update the last row only and it saved the Field1 value correctly as follows: sqlDA.UpdateCommand.Parameters["@Field1"].Value = ""; // no need to set Col_ID because it is already set to the Col_ID of the last row sqlDA.UpdateCommand.ExecuteNonQuery(); This concludes that my logic is sound, but why is the Update method screwing with my data? I'm starting to think there is a bug in the SqlDataAdapter in .NET Framework 1.1. mike Tag: How do i perform intersection in 2 datsets?? Tag: 126838
  - 20
    - Error when trying to create new SQL Server login via ado.Net I get an error when I try to execute the following code using ADO.Net in VB.Net: conn.open sqlString="CREATE LOGIN test WITH PASSWORD = '1qaz2wsx'" command=new SqlCommand(sqlString) command.connection = conn command.ExecuteNonQuery() The error occurs when executing the ExecuteNonQuery line and is: "Line1: Incorrect syntax near 'LOGIN' " The database connection is connected to the master database of SQL 2005 Express using the sa account. This line executes just fine in Sql Server Studio Manger when connected as sa. Am I not allowed to create database logins via ado.net?!?! I need to for my application. Thx, Marcus Tag: How do i perform intersection in 2 datsets?? Tag: 126836
  - 21
    - How next button in search works - Design problem Hi, May be I am a newbie, or may be i dont have that much insight in following systems ..i.e. why i have some confusions as below: In many websites, when search is performed on some keywords (not only including google which perform general search but other sites which perform seach on a particular type of item in their database) then many search results are obtained (for example more than 100 or even more than 1000) but only limited amount of results are displayed in a page (like for example 10 results per page). Thus in order to display the next 10 results, a user has to click on 'Next' button. Similarly, it is possible that a user can click on 'Previous' button to display the previous 10 results. My question is what are different mechanisms to perform this and which one would be most optimal than others? Why I am asking this question because, if we are performing a search by sending a query of keywords to a website then we get following procedure: Browser->Send Query->ASP.NET page(considering we are using ASP.NET although it could be PHP or ASP or JSP etc) Now internally from ASP.NET page, a database connection is opened (say using ADO.NET) and search is performed by sending a query to database and getting the results. So we get: ASP.NET page->Send query to database (using ADO.NET) Now database returns recordset containing more than lets say 100 records (or 1000 records) Database->Send back 1000 records->ASP.NET page And then ASP.NET page send 10 records to client and drop the rest. So: ASP.NET page->Send 10 records->Client Browser. So now when the client press Next button what happens? It seems illogical to send same search query to database and then dropping first 10 records from recordset and only passing recordset from 11th record to 20th records to client browser. So, can anyone please tell me what are different approaches to handle this. Thanks, Arsalan Tag: How do i perform intersection in 2 datsets?? Tag: 126834
  - 22
    - Dataset changed but won't update. I have a pretty simple block of code here that won't work. I've looped through the rows, and reviewed the values and the dataset values change fine but it's not updating to the database and doesn't throw an exception. Has anyone run into this before? sql = "SELECT * FROM INVENTORY WHERE Inventory_ID=" & InvId da = mdi.dt.GenericDA(sql) da.UpdateCommand = mdi.dt.GenericDACommand("UPDATE INVENTORY Set Amount_Paid=@Amount_Paid WHERE Inventory_ID=" & InvId) da.UpdateCommand.Parameters.Add("@Amount_Paid", OleDb.OleDbType.Currency, 8, "Amount_Paid") ds.Tables.Add("Inventory") da.Fill(ds.Tables("Inventory")) da.UpdateCommand.Parameters("@Amount_Paid").Value = 12 da.Update(ds, "Inventory") Tag: How do i perform intersection in 2 datsets?? Tag: 126824
  - 23
    - Perform a LIKE filter on DataView Integer column? Don't know if this can be done, but if I have a DataTable/DataView that has an integer column. Is there a way to do a "LIKE '123%'" kind of a filter on the column, which obviously works for text a text column. Thanks. Tag: How do i perform intersection in 2 datsets?? Tag: 126820
  - 24
    - Dataset, SqlDataAdapter, memory and you in .net 2 hey doods and doodettes. I am having severe memory issues when opening Datasets in .net 2.0. A web-based app will cause the worker process to recycle on a heavy-duty server after just a few client connections, and a windows app will bring the machine to a crawl. My testing code is as follows. It is used as the source for a report that can obviously be run many times. Even if I isolate the code to just opening and closing the data (as shown below), major problems still occur. Have I left anything out? using (SqlDataAdapter TheAdapter = new SqlDataAdapter("select * from customermaster", DoodConnection)) { using (DataSet TheCustomers = new DataSet()) { TheCustomers.Tables.Add("CustomerMaster"); TheAdapter.Fill(TheCustomers, "CustomerMaster"); TheAdapter.Dispose(); TheCustomers.Dispose(); GC.Collect(); GC.WaitForPendingFinalizers(); } } The "using" was added based on a post I found but did not help at all. Adding calls to GC helped a bit, but in the long run this is ridiculous!!! Tag: How do i perform intersection in 2 datsets?? Tag: 126819
  - 25
    - Optimistic Concurrency It could be me, but the check box for optimistic concurrency looks like it should be called pessimistic concurrency and seems completely broken. To me optimistic concurrency say's I'm never going to have a concurrency problem, last save wins (which is the default). When you check the box, it say's I'm only going to update the record if it hasn't changed on you. That being said, it appears that checking the box will only work if you never allow a null value in your tables? It generates an update statement such as the following: UPDATE [Test] SET [test] = @test WHERE [id] = @original_id AND [test] = @original_test The problem is if test or original_test are ever null, this statement will never update data. If you have 40 fields, non of them can be null? Perhaps what they mean by optimistic, is they're optimistic you'll never need a null column? Does anyone have a simple work around for this? How do you tell if the update didn't work? (Seems it would be good to indicate that?) Tag: How do i perform intersection in 2 datsets?? Tag: 126818
- Next
  - 1
    - ComboBox datasource problem VS2005 C#: On a form I have bound textboxes displaying information on companies. On that same form I am displaying the child rows from another table, Contacts, for each company record. It's just a simple form - subform deal. In the company table I have a field that stores the GUID of the primary contact in the child table. In other words, each company has many contacts, one of which is a primary. I have a combobox bound to this 'Primary Contact' field by it's selectedvalue property, and the combobox's datasource is set to a bindingsource that includes all of the contacts for that company. Here's the problem: When the form loads the first record displayed is correct. The combobox correctly shows the primary contact. But when the position changes(through a bindingnavigator) the combobox shows the first contact in the list, regardless of what the selectedvalue prop is. If I change the primary contact, the dataset is correctly updated, but the combobox will always go back to the first item in the list on record change. This does not happen when I use a bindingsource that includes all records on the contacts table, only when I use the bindingsource that displays the child rows. Because it's correctly updating the dataset, I am assuming that the combobox is just rendering before the second bindingsorce is updated. Does anyone have a solution or a workaround for this problem? Tag: How do i perform intersection in 2 datsets?? Tag: 126813
  - 2
    - Cannot open database ''. The full error message is: Cannot open database ''. It may not be a database that your application recognizes, or the file may be corrupt. I'm seeing this error once in a while in a C# windows app that talks to a MS Access database (2000 file format). The connection string is set at startup and never changed. It is in the format: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=(path to my db);Jet OLEDB:Database Password=(the password); All commands executed against the db go through the same method, which ensures that connections are opened, used, closed and released. The app is NOT very data-intensive, so it's difficult for me to believe this is a "number of connections" issue. Anybody have an idea what this error is trying to tell me? TIA Jared Tag: How do i perform intersection in 2 datsets?? Tag: 126812
  - 3
    - Saving Dataset- Order of operations Hi If you save a Dataset, which order must you do the operations in? I think its 1) Update 2) Delete 3) Add Or does the order matter? Vayse Tag: How do i perform intersection in 2 datsets?? Tag: 126811
  - 4
    - GetOpenConnection state exceptions Using Windows Server 2003, SQL Server 2005, and .net 2.0 (C#) I'm intermittently getting exceptions like the following: System.Data.SqlClient.SqlConnection(GetOpenConnection)ExecuteNonQuery requires an open and available Connection. The connection's current state is connecting. System.Data.SqlClient.SqlConnection(GetOpenConnection)ExecuteNonQuery requires an open and available Connection. The connection's current state is open. System.Data.SqlClient.TdsParserStateObject(ExecuteFlush)Operation cancelled by user. These are now occurring in service processes that have been behaving properly for several months. Tag: How do i perform intersection in 2 datsets?? Tag: 126809
  - 5
    - Trouble with DbConnection.GetSchema() Hello, I trying to get some schema info with help of string of code: _conn.GetSchema("Tables", sa) where _conn is type of DbConnection. So - sa must be string[] type. If I define sa like this: string[] sa = new string[3]; sa[0] = "master"; sa[1] = null; sa[2] = "users"; all fine, I get row "master dbo users BASE TABLE". This was SYSTEM database. Now want the same but for my own MSSQL database with name "TestBase". Such database definitely exist on the same server and definitely has table "MyTbl2". So I re-define sa: string[] sa = new string[3]; sa[0] = "TestBase"; sa[1] = null; sa[2] = "MyTbl2"; and get... nothing. Just empty DataTable? :( What's wrong? Tag: How do i perform intersection in 2 datsets?? Tag: 126805
  - 6
    - output param in SP, tableadapter I have a stored procedure with a number of input parameters, and one output parameter (integer type). I call the stored procedure from a table adapter in a typed dataset. The table adapter's method used (I'll call it sp_getdata) is configured to receive a single value from the SP. I understand that behind the scenes, the table adapter code handles the one output parameter as a reference type. Because I get design-time errors that I cannot convert from int to ref int?, I create a new integer variable (int var) , and pass it in as a reference (ref var). Now I get the error that I cannot convert from 'ref int' to ref int?' Can anyone make sense of this? One more thing. I can calling sp_getdata either of the following two ways int retval; int spval; tableadapter.sp_getdata(var1, var2, var3, var4, ref spval); retval = spval; as well as int retval; int spval; retval = tableadapter.sp_getdata(var1, var2, var3, var4, ref spval); Thanks, David Tag: How do i perform intersection in 2 datsets?? Tag: 126804
  - 7
    - Inserting a record to access via asp.net I am trying to add a record in the access97 database to a table that is linked to another. Is there an example of such ? Tag: How do i perform intersection in 2 datsets?? Tag: 126800
  - 8
    - Using DateTime with SQL I am using instances of DateTime within my application and i need their values to be used as selection criteria in an SQL select clause. So i need them to return a literal string representation of themselves in the following format '{ts '1990-01-01 00:00:00'}' Where the date can be any valid date as contained in the DataTime instance. How can i get them to do this ? Tag: How do i perform intersection in 2 datsets?? Tag: 126798
  - 9
    - hosted web server to access db servre on our network We have a hosted server and i have developed an ASP.net applciation that uses this backend i have tested it and its ready for deployment on the hosted server. However i cannot get it to see the server. I have had this working with an ASP application, the .net application has been redeveloped to take advantage of its features, and has had some new features implemented so the old ASP app is no good now. I use a ipaddress and port for the server eg. 192.168.1.2:9800 my connection string looks like New SqlConnection("Data Source=192.168.1.2:9800;Initial Catalog=Database;User Id=username;Password=password;") obviously i have changed it. I have also tried with integrated security but there is no corresponding account so it wont accept it. I have tried impersonate user whihc is what i have to do in the office but it doesn't recognise the domain\username on the webserver so errored when i had that in my web config. I have tried everything i know and have run out of ideas can someone offer some advice please, if you need any more information please let me know cheers Tag: How do i perform intersection in 2 datsets?? Tag: 126790
  - 10
    - [VB] Replacing DataTable with Sorted DataView and DataSet manipulation Q's Some beginner Q's. 1. How do I replace the contents a DataTable with the contents of a sorted DataView? 2. How do I create a copy of a DataSet/DataSet Table, and then when processed, replace the original with the copy 3. How do I address/change the internal index of a DataSet table's row e.g. DataSet1.Tabel1.Rows(0) (I want the index to match my table's autonumber primary key) My utimate goal is to sort a DataSet so each record's index matches the primary keys in the access table. Thanks. -- Aziz Tag: How do i perform intersection in 2 datsets?? Tag: 126788
  - 11
    - How to export DataTable or DataSet to Excel file? How to export DataTable or DataSet to Excel file? Tag: How do i perform intersection in 2 datsets?? Tag: 126786
  - 12
    - Unexpected end-of-file (EOF) encountered in data file. I am letting the end-user select the file to be imported into the SQL via the intranet. I am getting this error displayed. Microsoft OLE DB Provider for ODBC Drivers error '80040e14' [Microsoft][ODBC SQL Server Driver][SQL Server]Bulk Insert: Unexpected end-of-file (EOF) encountered in data file. /loaddata/file.asp, line 15 Here is a copy of the asp file <html><head><title>Loading file</title></head><body> <% x = request.form("filename") response.write "the file is = "&x set myCon = Server.CreateObject("ADODB.Connection") mycon.Open "NIPDB" set myCmd = server.CreateObject("Adodb.command") mycmd.activeconnection = mycon mycmd.commandText = "LDS" mycmd.commandType = 4 Set BudRS5 = mycmd.execute(,array(x)) budrs5.close mycon.close set mycon = nothing %></body></html> Here is copy of stored procedure LDS CREATE procedure LDS(@Filein nvarchar(50)) AS DECLARE @SQLString NVARCHAR(80) /* Build Command*/ SET @SQLString = N'Bulk Insert DailyTbl from ' + N'N''' + @Filein + N'''FIRSTROW = 2, MAXERRORS = 0, FIELDTERMINATOR = ",", ROWTERMINATOR = "\n,\t"' /* Execute */ EXEC (@SQLString) GO Here is a sample of data held within the .csv file Day,Date,Line,Line Description,Registered Sales (£),Sales SUs,Reduced (£),Explained Wastage,Reductions % Reg sales SUN,29/01/2006,28429,CONT CHAR POTS,1072.67,632,58.61,39.38,5.46 SUN,29/01/2006,38439,ORG LGE LSE POTS,279.97,166,0.0,24.98,0.0 Please let me know if more information is required Tag: How do i perform intersection in 2 datsets?? Tag: 126783
  - 13
    - Filtering Column with type TimeSpan I have DataTable with TimeSpan column named Duration. I am trying to use Select statment and it complains that operator = can't compare String and TimeSpan. I tried the following without success: Select("Duration = Convert(0, 'System.TimeSpan'). Error: Cannot perform '=' operation on System.TimeSpan and System.TimeSpan I tried to define new column DurationStr with expression "Convert([Duration], 'System.String')". After I add new record (in memory) I see that DurationStr column contains value "PTOS". That means that my Select("DurationStr = '00:00:00'") never finds anything. Tag: How do i perform intersection in 2 datsets?? Tag: 126782
  - 14
    - ADO.NET Excel Connections & DataType INT32 Hello, Can anybody tell me if it is possible to create a Table with an INT32 field with a ADO.NET Excel type connection? I have done this sucessfully with VARCHAR types but when I create an INT32 field and populate it it becomes a string value. ie In Excel it has looks like '32. This means that when working with pivot tables Excel starts complaining. Any help on this would be GREAT! Cheers Phil Tag: How do i perform intersection in 2 datsets?? Tag: 126780
  - 15
    - The Jet 4.0 Exchange/Outlook IISAM from ADO.NET I am experimenting with using Visual Studio 2005 and .NET 2.0 for the first time (though I have plenty of 1.1 experience and it's not that important in this case). I have written a VC++ Win32 API-only application that acts as an address book converter between applications. So, I'm trying to get away from the difficulties of manually calling MAPI and OLB's and the like. I think if I could use a DataSet for as much of it as possible, it would simplify things a great deal. So, I dug around and found out about this Jet 4.0 Exchange/Outlook IISAM. That looks very promising. I am able to use the connection string like "Provider=Microsoft.Jet.OLEDB.4.0;Exchange 4.0;MAPILEVEL=Personal Folders|;TABLETYPE=1;DATABASE=C:\\Temp" and a select statement like "SELECT * FROM [Address Book]" to get the contents of the WAB, for example. I have two large problems at ths point, however. First is the fact that all examples are in DAO or classic ADO. Could I enumerate the folders from ADO.NET like I could do in VB?: Set db = _ OpenDatabase("c:\temp\", False, False, _ stConnect) For Each tdf In db.TableDefs Debug.Print "FOLDERS: " & tdf.Name Next tdf Second is Unicode. The above example select statement, when linked to a DataSet, converts all non-Latin characters to question marks. It doesn't look like a limitation of the IISAM, but is it a limitation of the Exchange/Outlook drivers or can I get Unicode text somehow? Thanks in advance, Boris Zakharin Tag: How do i perform intersection in 2 datsets?? Tag: 126773
  - 16
    - how to insert a null value to sqlparameter how to insert a null value to sqlparameter Tag: How do i perform intersection in 2 datsets?? Tag: 126769
  - 17
    - Exception of type System.StackOverflowException was thrown. I Exception of type System.StackOverflowException was thrown. I m having this error sometimes and dont know why it comes. it happends only at one table its a biggest one which have 500 records but dot thinking that it was a big number for overflowing the stack. DataRow newrow = table.NewRow(); newrow[0] = rprow.Title; newrow[1] = rprow.AggFunc; rprow.DataRow = newrow; rprow.DataRowIndex = table.Rows.Count; table.Rows.Add(newrow); Tag: How do i perform intersection in 2 datsets?? Tag: 126767
  - 18
    - Parent-Child record and the Parent ID Hi, I'm writing a library of data routines, this of course has to be VERY generic and has to work with both MSSQL and Oracle (and of course, I don't have Oracle here to test on, figures.) In this library when I generate an INSERT command object via my own custom command builder I need it to return the ID of the newly inserted record. This works via SCOPE_IDENTITY() as long as I'm using SQL Server: insertCommand.CommandText = "INSERT INTO someTable (some fields) VALUES (some values);SELECT SCOPE_IDENTITY() AS AutoIncFieldName" That is just so magical I could weep, really, it's awesome. But I digress... Naturally there is no equivalent to SCOPE_IDENTITY() on Oracle (really, is it that hard to come up with some standards people?) So I did some poking around and the suggested course of action is something like the following: SELECT sequence_name.CURVAL FROM something My question is, how do I know what sequence_name is? Since this is a common interface for both MS-SQL and Oracle, it has to be completely transparent. Is there anyway to get this sequence_name based on the primary key and such? And what is 'something' table supposed to be? Dave Sceppa has it as "DUAL" in his 'canned response', but is this just an example table name? Is it the table that I inserted into prior? Will there ever be a day when I don't have to deal with this stupid competition between Microsoft and Oracle? Thanks, Mike Tag: How do i perform intersection in 2 datsets?? Tag: 126765
  - 19
    - **newbie** simple application but struggling with msdn/tutorial vb.net Hi, It would seem I have a simple application but I'm struggling. I'm currently loading an xml file using DataSet1.ReadXml(RStream) I want to load the xml data into an sql server express database The xml file is structured as <?xml version="1.0" encoding="utf-8"?> <outpatient_questions xmlns:xsi="http://www.w3.org/2000/10/XMLSchema-instance"> <outpatient_detail> <lastname>sharpe</lastname> <firstname>lee</firstname> <sex>male</sex> <age>67</age> </outpatient_detail> <habitat_detail> <house_type>hostel</house_type> <entrance_type>front</entrance_type> <equipment_reqd>ramps</equipment_reqd> </habitat_detail> </outpatient_questions> and the database contains two tables (outpatient_detail, habitat_detail) I'm using an auto number to create an id for both tables (pat_id, hab_id) and using pat_id as a foreign key in habitat_detail I have virtually no experience with vb.net/ado.net and have been using the wizards, which from reading this site seems to be the wrong way to go but I needed to act quick!! I'm having problems updaing my second table I'm using two sqlDataAdapters (one for each table) the first works fine but how do i get the generated auto number from the first table to add as a parameter to my second dataAdapter. My code for the second adapter is Me.SqlInsertCommand2.CommandText = "INSERT INTO habitat_detail(hab_id, house_type, entrance_type, equipment_reqd, pat" & _ "_id) VALUES (@hab_id, @house_type, @entrance_type, @equipment_reqd, @pat_id)" Me.SqlInsertCommand2.Connection = Me.SqlConnection2 Me.SqlInsertCommand2.Parameters.Add(New System.Data.SqlClient.SqlParameter("@hab_id", System.Data.SqlDbType.Int, 4, "hab_id")) Me.SqlInsertCommand2.Parameters.Add(New System.Data.SqlClient.SqlParameter("@house_type", System.Data.SqlDbType.VarChar, 50, "house_type")) Me.SqlInsertCommand2.Parameters.Add(New System.Data.SqlClient.SqlParameter("@entrance_type", System.Data.SqlDbType.VarChar, 50, "entrance_type")) Me.SqlInsertCommand2.Parameters.Add(New System.Data.SqlClient.SqlParameter("@equipment_reqd", System.Data.SqlDbType.VarChar, 50, "equipment_reqd")) Me.SqlInsertCommand2.Parameters.Add(New System.Data.SqlClient.SqlParameter("@pat_id", System.Data.SqlDbType.Int, 4, "pat_id")) Is there a relatively easily understood way of setting "@pat_id" and inserting all data I'm currently using SqlDataAdapter1.Update(DataSet1) *** get most recent auto generated id and add to me.SqlInsertCommand2.Parameter SqlDataAdapter2.Update(DataSet1) I've tried creating another dataset to see the id (using select max(pad_id) and i can see it but cant convert to an integer, and even if i could how could i get it into the parameters collection before updating the second dataAdapter I've also tried creating a datarelation between my two tables in the dataset but get a column cant be null error (is this because the id cols are not in the xml file ?) I hope this is clear (and not too rambling!!) and any feedback would be greatly appreciated. thanks in advance alan Tag: How do i perform intersection in 2 datsets?? Tag: 126760
  - 20
    - Performance of OleDB Vs. ADO.NET Hi, I am developing an ASP.NET website using MySQL as a backend database. Database access consists of simple update, select and insert queries however its likely that there will be a number of users simultaneously accessing the database. I can access database using either ADO.NET or using OleDB(OleDB APIs can be used as COM objects can be used in .NET easily). However, which method is preferable in terms of performance? using ADO.NET or using OleDB COM objects? (Overall performance could be separated into performance during connection (which one has less overhead in connection), performance for a select (which one has less over head for select) and performance for update/insert/delete (which one has less overhead). I am particularly interested in performace for MySQL database however for any general database is also appreciable. Also could any one please tell me is OleDbConnection class is part of ADO.NET or separate and how can we compare its performance with OleDB COM APIs (and with ADO.NET if it is separate from ADO.NET)? Are there any performance benchmarks for above? Thanks, Arsalan Tag: How do i perform intersection in 2 datsets?? Tag: 126759
  - 21
    - FS: SQL Programming Database Management Books CHEAP! I am selling these books in a lot. I am not selling them individually. Most of the books are in NEW or LIKE NEW condition. A small minority are older and may have some shelfwear. There may be remainder marks. There is no highlighting or writing in the books. If you are intereseted, please contact me at NOgymarquezHAM@sbcglobal.net. You can get rid of the NO and the HAM in order to reach me at the proper e-mail address. Please include the subject heading so that I will know which books your are interested in as I am selling multiple groupings of books. Understanding the New SQL: A Complete Guide (The Morgan Kaufmann Series in Data Management Systems) (Paperback) by Jim Melton, Alan R. Simon Paperback: 536 pages Publisher: Morgan Kaufmann (October 1, 1992) Language: English ISBN: 1558602453 Product Dimensions: 9.4 x 7.6 x 1.0 inches Shipping Weight: 2.1 pounds List Price: $57.95 Instant SQL Programming (Paperback) by Joe Celko Paperback: 389 pages Publisher: Wrox Press; Bk&CD-Rom edition (August 1995) Language: English ISBN: 1874416508 Product Dimensions: 9.2 x 7.3 x 1.5 inches Shipping Weight: 1.8 pounds List Price: $29.95 Introduction to Database Management: A Practical Approach (Hardcover) by Gerry Litton Hardcover: 532 pages Publisher: William C Brown Pub (February 1987) Language: English ISBN: 0697004511 List Price: $59.95 Database: Step-By-Step (Hardcover) by Mark L. Gillenson (Photographer) Hardcover: 406 pages Publisher: John Wiley & Sons (September 1985) Language: English ISBN: 0471807028 Shipping Weight: 1.8 pounds List Price: $59.95 Understanding data base management systems (Wadsworth series in computer information systems) by Joseph A Vasta HardcoverBinding: 350 pages Publisher: Wadsworth Pub. Co (1985) Language: English ISBN: 0534040292 List Price: $59.95 SQL from the Ground Up (Paperback) by Mary Pyefinch Paperback: 510 pages Publisher: McGraw-Hill Companies (April 1, 1999) Language: English ISBN: 0072119748 Product Dimensions: 9.1 x 7.3 x 1.4 inches Shipping Weight: 2.2 pounds List Price: $34.99 SQL: The Complete Reference (Paperback) by James R. Groff, Paul N. Weinberg Paperback: 1008 pages Publisher: Osborne/McGraw-Hill; Bk&CD Rom edition (March 1, 1999) Language: English ISBN: 0072118458 Product Dimensions: 9.1 x 7.4 x 2.4 inches Shipping Weight: 3.6 pounds List Price: $49.95 Total Number of Books: 7 Total List Price Value: $ 352.69 Total Asking price: $ 88.00 Location: Houston, TX USA, but willing to ship Tag: How do i perform intersection in 2 datsets?? Tag: 126758
  - 22
    - Oracle 10g access from 1.1 Hi, Is it possible to use the MS Oracle Client for connectivity to 10g from the 1.1 platform or must I use the ODP driver? Is recommended? Thanks, Dan Tag: How do i perform intersection in 2 datsets?? Tag: 126757
  - 23
    - FS: Microsoft .NET Framework 1.1 Class Library Reference Microsoft .NET Framework 1.1 Class Library Reference Volumes 1-4 are for sale These books have never been used, but they are not sealed. There is some minor shelf wear due to simply handling the cardboard frame and taking them out of the cardboard frame. Our loss is your gain! You will get: Volumes 1-4 ( 4 books) Class Library Poster Pack ISBN: 0735615551 Paperback: 8000 pages Publisher: Microsoft Press; Bk&Poster edition (February 12, 2003) Language: English ISBN: 0735615551 Product Dimensions: 12.2 x 5.2 x 7.8 inches Shipping Weight: 26.1 pounds. Format: Softcover Publication Year: 2003 Details Editor: Microsoft Corporation List Price: $149.99 Asking Price $79.99 I am in the Houston, TX area in the USA. But I would be willing to ship the books. You can pick them up locally. I am also willing to ship them. Some Reviews: Massive Reviewer: dannomite There is a HUGE amount of content in these volumes. Much more than I expected. I thought perhaps the pages would be wasted with large type and big margins, but they are not! Instead they used a small type face and two columns per page and very small margins so that you get a jam packed verbose reference of practically the entire System namespace. Plenty of sample code as well, which I have already found helpful. Yes, you can get much of this info from the online help. But honestly, having a printed set just makes it so much nicer to skim through, and conveniant to sit down and read anywhere or take with you without having to wear out your eyes staring at a screen. I have already sat down on the couch a few times and enjoyed skimming through page after page, jotting down notes. These references in combo with the online doc is as comprehensive as it gets. A monster, worth every page..., Reviewer: Aaron B. Hockley We got this set of references into our office this week and after a good perusing I can say this is a must-have for any serious .NET developer/shop. For me, I much prefer being able to carry a book or flip through the pages to find something, with the ability to annotate the pages, than to reading the information online. As posted, this is volumes 1-4 of 7. This provides a great starting point for .NET reference materials and it is obvious from the content that Microsoft is concerned with continuing to provide valuable print references for developers. Tag: How do i perform intersection in 2 datsets?? Tag: 126756
  - 24
    - Passing Parameter Collections Two questions 1) Can a Parameter Collection be created without a Command? I've tried Dim paramsCustomer As OleDbParameterCollection paramsCustomer.Add(New OleDbParameter("CustomerBalance", OleDbType.Single, 8, "CustomerBalance")) paramsCustomer.Add(New OleDbParameter("CustomerId", OleDbType.SmallInt, 8, "CustomerId")) But it won't work. I get "Object reference not set to an instance of an object." So for now, I have to add paramsCustomer = cmdTest.Parameters after the Dim statement. Is there any way around this? 2) Passing the Parameters Collection So I have a function like this: Function UpdateTable(ByVal stCommandText As String, ByVal Params As OleDbParameterCollection, ByVal dtOrig As DataTable) As Long Dim cmdUpdate As New OleDbCommand(stCommandText, connData) How do I set cmdUpdate parameters to the Params that I pass? Can't get it to work. Thanks Vayse Tag: How do i perform intersection in 2 datsets?? Tag: 126755
  - 25
    - Paradox Database Hello, I've a paradox database file named master.db. What is the best way to access this using asp.net (VB)? I want to put the data in a gridview. - Thanks Tag: How do i perform intersection in 2 datsets?? Tag: 126754

How do i perform intersection in 2 datasets having a same col.

*** Sent via Developersdex http://www.developersdex.com ***

Top

Re: How do i perform intersection in 2 datsets?? by olrt

olrt
Wed Mar 29 13:52:58 CST 2006

Hello, do you mean JOIN between two datasets ?
It's possible to do JOINs between DataTables in a DataSet,
but it seems to me odd to do JOINs between DataTables from
two DataSets...
Maybe you should expose your design so that one
may give guidance by answer.

Top

Re: How do i perform intersection in 2 datsets?? by H5N1

H5N1
Thu Mar 30 04:09:49 CST 2006

I'd also like to know the answer.

>From my point of view: let's say I want to present JOINed query in
gridview - from what I know, if i bind it with dataset with join query,
update methods want be generated.
So one of resolutions would be to define 2 (or more) single table
DataTables, join them, feed it to gridview, and on update of gridview
somehow modify those singletable datasets.

Is there simpler way to do it?

thanks a lot

Top