DataSet and Table join

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
    - Having problem with SQLConnection.ChangeDatabase() method. I have multiple databases in which I want to traverse. Each database is secured using Applicaiton Roles. I use the SQLConnection object to connect to the first database. I run the sp_setapprole procedure and I can access the tables with no problem. However, when I try to call the ChangeDatabase method on my connection object, I get an exception thrown stating that my application role user is not a valid user in the database. If I connect to the same database using Open, it works fine. I don't quite understand what is going on here. Can anyone help? -- ----------------------------------- Ken Varn Senior Software Engineer Diebold Inc. EmailID = varnk Domain = Diebold.com ----------------------------------- Tag: DataSet and Table join Tag: 107873
  - 2
    - DataTable Select method I want to know what kind of queries can i execute on datatable select method. The one i am interested in is between query for eg:- this is my sample query Select max(resultan) from TblseasResult Where SeasId='15.3.13.3302005' And Countno between 4801 and 4806 where Countno is numerical field now this is what i want to do DataTable1.select("Countno between 4801 and 4806") so it should return all the rows as Datarow array Thanks -------------------------------- From: Mandar Patankar MCP ----------------------- Posted by a user from .NET 247 (http://www.dotnet247.com/) <Id>yuriN1lzc0ydMcoRYJ+RYw==</Id> Tag: DataSet and Table join Tag: 107871
  - 3
    - Problems inserting data into Access database Hi, I'm trying to insert new rows into a table in an Access database. Here's my code: Dim cn As OleDbConnection Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load cn = New OleDbConnection(ConfigurationSettings.AppSettings("cnstr")) Dim TStart, TEnd As Date Dim duration As TimeSpan TStart = Now() Dim table as DataTable = GetData() TEnd = Now() duration = TEnd.Subtract(TStart) Label1.Text = tbl.Rows.Count Label2.Text = "Operation took " & Round(duration.TotalSeconds, 0) & " seconds to execute." End Sub Private Function GetData() As DataTable Dim AWS As New EEC.BookStore.Books() Dim tbl As DataTable = MakeTable() Dim books As EEC.BookResponse Dim i As Integer For i = 1 To 10 'would be to the max number of pages available 'Call the web service and format data as desired; this works Call Me.AddToTable(books)'custom function that adds rows to the table Next cn.Open() Call Me.SubmitInsertViaAdapter() cn.Close() AWS = Nothing Return tbl End Function Private Function MakeTable() As DataTable Dim tbl As New DataTable("Books") Dim col As DataColumn With tbl col = .Columns.Add("ISBN", GetType(String)) col = .Columns.Add("Title", GetType(String)) col = .Columns.Add("Category", GetType(String)) col = .Columns.Add("Authors", GetType(String)) col = .Columns.Add("SImg", GetType(String)) col = .Columns.Add("MImg", GetType(String)) col = .Columns.Add("LImg", GetType(String)) col = .Columns.Add("Pub", GetType(String)) col = .Columns.Add("PubDate", GetType(String)) col = .Columns.Add("Binding", GetType(String)) col = .Columns.Add("NumItems", GetType(String)) col = .Columns.Add("NumPages", GetType(String)) col = .Columns.Add("EAN", GetType(String)) col = .Columns.Add("Keywords", GetType(String)) End With Return tbl End Function Private Function UpdateTable() As OleDbCommand Dim str As String str = "INSERT INTO Books (ISBN, Title, Category, Authors, SImg, MImg, LImg, Pub, PubDate, Binding, NumItems, NumPages, EAN, Keywords) VALUES ([?], [?], [?], [?], [?], [?], [?], [?], [?], [?], [?], [?], [?],[?])" Dim cmd As New OleDbCommand(str, cn) cmd.CommandType = CommandType.StoredProcedure Dim col As DataColumn For Each col In tbl.Columns Dim param As New OleDbParameter(col.ColumnName, OleDbType.VarChar, 255, col.ColumnName) cmd.Parameters.Add(param) Next Return cmd End Function Private Sub SubmitInsertViaAdapter() Dim daBooks As New OleDbDataAdapter() daBooks.ContinueUpdateOnError = True 'daBooks.SelectCommand = Nothing 'daBooks.UpdateCommand = Nothing 'daBooks.DeleteCommand = Nothing daBooks.InsertCommand = UpdateTable() Label3.Text = CStr(daBooks.Update(tbl)) End Sub When all is done, there are rows in the datatable but none of them were submitted to the database. What am I doing wrong? Please help, I have only a few hairs left on my head!! Thanks, Roshawn Tag: DataSet and Table join Tag: 107866
  - 4
    - Can't find stored procedure On Sql Server 2000, my program can't seem to find the Stored Procedure: AddNewResumeTemplate. The error it shows is: Could not find stored procedure 'AddNewResumeTemplate '1234',1001,'This is a test','This is our resume',5'. The problem is that it is there. I even have Sql Profiler running which shows it trying to execute: exec AddNewResumeTemplate '1234',1001,'This is a test','This is our resume',5 Which is correct and when I copy and paste it into Sql Query Analyser, it works. My code is the same as I have been running for a long time, so I can't seem to see what is missing. Here is what it looks like: ********************************************************************************* Dim ConnectionString as String =System.Configuration.ConfigurationSettings.AppSettings("MM_CONNECTION_STRING_ftsolutions") Dim objConn as New SqlConnection (ConnectionString) Dim CommandText as String = "AddNewResumeTemplate '1234',1001,'This is a test','This is our resume',5" Dim objCmd as New SqlCommand(CommandText,objConn) objCmd.CommandType = 4 trace.warn("objConn.ConnectionString = " & objConn.ConnectionString) objConn.Open() objCmd.ExecuteNonQuery ************************************************************************************* The trace.warn shows that it is accessing the correct database on the server, so I don't know why it can't find it. Tom Tag: DataSet and Table join Tag: 107862
  - 5
    - using ReadXML with disparate XML data I want to load data from two related but different XML files into a two different DataTables in a single DataSet, then create relations, and finally filter and select specific data. I can't figure out how to load the two different sets of data into one DataSet. For example, the following results in just one table, and no detail data: XmlDataDocument datadoc = new XmlDataDocument(); datadoc.DataSet.ReadXml(new StreamReader(@"C:\temp\header.xml"), XmlReadMode.Auto); datadoc.DataSet.ReadXml(new StreamReader(@"C:\temp\detail.xml"), XmlReadMode.Auto); Trying a few variations of this code didn't get me any further. Tag: DataSet and Table join Tag: 107854
  - 6
    - A question about closing the SqlDataReader Hi all, I have code like: SqlDataReader reader = cmd.ExecuteReader(); if (reader.Read()) { // do something here } else { reader.Close(); // this is the line I'm not sure about Response.Redirect("to_some_other_page.aspx"); } reader.Close(); My question is: Is reader.Close() in the else part really needed? If reader.Read() returns nothing, then is the reader actually open or not? The code above does not crash, but maybe the Close() is redundant... Thanks. Tag: DataSet and Table join Tag: 107853
  - 7
    - List SQL Servers instaled on the system and the ODBC drivers Hi! I am trying to find a way for listing the SQL servers installed on a machine, for example MS SQL, Oracle, My SQL, Transact SQL. And also the ODBC drivers that exists on a system, including ODBC drivers for sql servers previosly listed, and the ODBC drivers for flat files (for example foxPro dbf files, dBase files, Microsft Office Excel, other ISAMs) In a word, all the posible information existing on a machine for creating datasources . Very much need for this! Tag: DataSet and Table join Tag: 107845
  - 8
    - Can I execute sql drop database cmd w/a SqlParameter Hi, I'm wondering if it is possible to execute a sql command to drop a database with a SqlParameter containing the name of the db to drop. I've tried the following and I get an error SqlCommand dropDbSqlCmd = _masterDbConn.CreateCommand (); dropDbSqlCmd.CommandType = CommandType.Text; dropDbSqlCmd.CommandText = "DROP DATABASE @dbToDrop"; SqlParameter dbNameParam = dropDbSqlCmd.Parameters.Add ( @dbToDrop, SqlDbType.NVarChar, 128 ); dbNameParam.Value = dbName; Error is System.Data.SqlClient.SqlException : Line 1: Incorrect syntax near '@dbToDrop'. Thanx jra Tag: DataSet and Table join Tag: 107844
  - 9
    - Connecting to non SQL database When i try to connect to an old UniVerse database (using their ODBC drivers) visual studio (vc# standard) gives me an error saying something to the effect that this version can only connect to Access/SQL databases. What version of visual c# or visual studio do I need to connect to any ODBC complient database? Tag: DataSet and Table join Tag: 107843
  - 10
    - DataReader problem I hope someone can help. I'm using a SqlDataReader to pull some data from a Sql 2000 database. Most of the columns are varchar's of varing lengths and one of the columns is defined as a char(2). Using the datareader I can get the values of the varchar columns, but the char column returns empty. Example: myObj.prop1 = Convert.ToString(dr["VARCHAR_COL_1"]); myObj.prop2 = Convert.ToString(dr["VARCHAR_COL_2"]); myObj.prop3 = Convert.ToString(dr["CHAR_COL_1"]); I know there's a value in the char column on the db, the first two properties get set, but the third does not. I haven't check to see if it's null or just an empty string, My property being assigned to is a string. David Tag: DataSet and Table join Tag: 107828
  - 11
    - Altering Access Database Using ADO.NET Hi Since the last version of my released software I've had to make some modifications to the back end Access database such as adding an extra field to a table and adding new queries. Does anyone know the easiest way to modify a tables structure and add queries using purely VB.NET or is it necessary to use COM objects to do so? Thanks Lee Tag: DataSet and Table join Tag: 107826
  - 12
    - DataAdapter Query Access Hi people, i have a question about this: I need to know how can i obtain the sql queries (Insert, Update, Delete) that are executed by DataAdapter.Update on a DataSet. I know that i can see that queries on SQLProfiler but i want to obtain that sentences on my code...It's possible??? I hope you can help me... Regards Johny Tag: DataSet and Table join Tag: 107823
  - 13
    - how store the images in ORACLE database using asp.net (Type your message here) -------------------------------- From: viji g hi i need help .... how store the images in ORACLE database using asp.net pls give me resulet i m wating pls help me urgent ----------------------- Posted by a user from .NET 247 (http://www.dotnet247.com/) <Id>kyzm318fGEuYs1Q2e5VjAA==</Id> Tag: DataSet and Table join Tag: 107814
  - 14
    - Replacing MSDE Hello, I have made a Windows Application in C#, with a database running under MSDE. The application is quite simple, and there is only 3 tables, and only one has maybe 200-250 records maximum (the 2 others have only 10-20 records). I wanted to use XML files for datasets, but i am not sure if it is possible to use SQL queries to sort my data (like SELECT * FROM myTable WHERE ..... ORDER BY ...). Is there an other way to use database file without installing anything, like the old DBF files for Foxpro ? Because i think MSDE is a little bit to heavy for my purpose. Thanks for your answers. Michael Tag: DataSet and Table join Tag: 107811
  - 15
    - Excel file permissions problem I have an Excel file, which I use to store some data. When reading from file, everiting is ok. When i try to insert some row, an OleDbExcetpion is thrown. I'va tracked it to a permission problem. The sheats of my Excel file as protected with password. Any way I can supply this password to the connection string, or other way? thanks Tag: DataSet and Table join Tag: 107809
  - 16
    - A simple insert problem Hi all, I know I am going to kick myself but I have run into a bit of a brick wall and I need some help before I loose the rest of my hair. All I want to do is insert a new row of data into a access database from a vb.net app I'm writing. I have read so many forums today and I think I must be missing something really basic. I have a database with a table in it called Flights. The columns in the database are: CellInfo type=string Flightdate type=dateTime FlightTime type=int Key type=int recordNum autonumber, primarykey WhyComment type=string I have created a OleDbDataAdapter1, and Dataset1 to this database. With my code all I am doing at this stage is trying to insert the word "Test" into the column "WhyComment" when I click a button. Heres my code: *************************************** Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click 'Fill Dataset DataSet11.Clear() OleDbDataAdapter1.Fill(DataSet11) 'Assign variables Dim myNewRow As DataSet1.FlightsRow Dim myDataset1 As DataSet1 Dim FlightsTable As DataSet1.FlightsDataTable 'Insert the word test into the colum "WhyComment" in table "Flights 'in the database. 'WhyComment is a string table myNewRow = myDataset1.Flights.NewRow 'The above row is where it errors out with this error: 'An unhandled exception of type 'System.NullReferenceException' occurred 'in DatabaseInserts.exe ' 'Additional information: Object reference not set to an instance of an object myNewRow("WhyComment") = "Test" myDataset1.Tables("WhyComment").Rows.Add(myNewRow) 'update Database OleDbDataAdapter1.Update(DataSet11) MessageBox.Show("Database updated!") End Sub **************************************** Anyones help would be appreciated. Cheers Dave ----------------------- Posted by a user from .NET 247 (http://www.dotnet247.com/) <Id>FdFbRYaMk0mqARLDPpAkqQ==</Id> Tag: DataSet and Table join Tag: 107807
  - 17
    - Using Enterprise Library caching in Data Layer Can anyone point me in the direction of, or provide, some best practice (and poss examples) for using the Enterprise Library caching block within a COM+ Data Access Layer (which also uses the DA block)? I would like to cache data for a set period of time. The data is returned from SQL as Xml and then transformed with Xslt, it is the transformed xml that is to be cached. A few different applications use this data and I believe caching will vastly improve the performance. Any help on this subject area woul dbe much appreciated. Thanks, -------------------------- Shaun Venus emailid: sunevnuahs domain: hotmail.com -------------------------- Tag: DataSet and Table join Tag: 107804
  - 18
    - Simply adding a table What is the best way to add a new table to a database? If possible I don't want to use SQL, but ADO.Net directly. I'm a Ado-Newbie and tried the following: dataSet.Tables.Add("xyz"); adapter.Update(dataSet); but this does not work. I believe that Adapter.Update() only works for Tables that already exist in the Database. But what is the alternative if I don't like the SQL solution (CREATE TABLE ...)? I cannot create the tables directly. I have to make that at runtime. Thanks, Thomas Krause Tag: DataSet and Table join Tag: 107794
  - 19
    - Handling paging in the UI without using datagrid/dataview Hi Please give me some inputs on the following : Iam planning to use a custom control to list a large number of records from sql database through ado.net. so in the following code, if i give a select * query, does the dataset holds the complete records in the memory? or is there anyway i can set options in the dataset? or the paging logic need to be done in UI side? Hari //Open Connection. SqlConnection conn = new SqlConnection("Server=server;uid=login;pwd=pwd;database=northwind"); //Set the DataAdapter's query. da = new SqlDataAdapter("select * from customers", conn); ds = new DataSet(); //Fill the DataSet. da.Fill(ds, "customers"); Tag: DataSet and Table join Tag: 107792
  - 20
    - Excel and ADO.NET Hello: I am building an App that takes an Excel file and populates 3 DataTable's in a Dataset. I then was to create a relationship between these 3 DataTables. After doing so I want to run some code to process / modify the data in the tables and then return the result back into an Excel file. I am a beginner at VB and i chose this as a starter project. I get the following exception when i try to create the DataRelation: An unhandled exception of type 'System.ArgumentNullException' occurred in system.data.dll Additional information: 'column' argument cannot be null. Listed below is the code I have. All help would be appreciated Thank You Private Sub cbConvert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbConvert.Click 'Convert button click event Dim filename As String filename = tbInputFileBrowser.Text() Dim XLFileConn As New OleDbConnection XLFileConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & filename & _ ";Extended Properties=""Excel 8.0;HDR=YES""" Try XLFileConn.Open() Dim XLFileCmd1 As New OleDbCommand XLFileCmd1.Connection = XLFileConn Dim XLFileDA As OleDbDataAdapter = New OleDbDataAdapter XLFileDA.SelectCommand = XLFileCmd1 Dim XLFileMainDS = New DataSet("MainDataSet") Dim TableMain As DataTable = XLFileMainDS.Tables.Add("TableWithMain") XLFileCmd1.CommandText = "SELECT * FROM [Sheet1$] WHERE Modify <> 'TC' OR '26'" XLFileDA.Fill(XLFileMainDS, "Sheet1") Dim TableTC As DataTable = XLFileMainDS.Tables.Add("TableWithModTC") XLFileCmd1.CommandText = "SELECT * FROM [Sheet1$] WHERE Modify = 'TC'" XLFileDA.Fill(XLFileMainDS, "Sheet1") Dim Table26 As DataTable = XLFileMainDS.Tables.Add("TableWithMod26") XLFileCmd1.CommandText = "SELECT * FROM [Sheet1$] WHERE Modify = '26'" XLFileDA.Fill(XLFileMainDS, "Sheet1") 'XLFileDA.FillSchema(XLFileMainDS, SchemaType.Source, "Sheet1$") Dim ParentCol As DataColumn Dim Child1Col As DataColumn Dim Child2Col As DataColumn ParentCol = XLFileMainDS.Tables("TableWithMain").Columns("Proc Code") Child1Col = XLFileMainDS.Tables("TableWithModTC").Columns("Proc Code") Child2Col = XLFileMainDS.Tables("TableWithMod26").Columns("Proc Code") Dim RelProcCode As DataRelation Dim RelProcCode1 As DataRelation RelProcCode = New DataRelation("ProcCodeTC", ParentCol, Child1Col) 'Get an Exception Here RelProcCode1 = New DataRelation("ProcCode26", ParentCol, Child2Col) 'Add the relation to the DataSet. XLFileMainDS.Relations.Add(RelProcCode) XLFileMainDS.Relations.Add(RelProcCode1) Finally XLFileConn.Close() End Try Tag: DataSet and Table join Tag: 107788
  - 21
    - Datagrid Navigate question If a datatable, bound to a datagrid, has multiple child tables, is there any way to determine which relationship is being used to generate the child rows when the user navigates using one of the relationship links? Thanks. Tag: DataSet and Table join Tag: 107780
  - 22
    - ADO.Net DataSet to ADO Recordset I have two applications, one on ASP.Net and the other on ASP (both have different explicit databases). It so happens that the ASP application now wants to use the dataset created in ASP.Net for further information processing. How do i make this happen? Tag: DataSet and Table join Tag: 107778
  - 23
    - Messages in ADO.NET I would like to be able to execute a long-running stored procedure from a Windows Forms application. The procedure loops through about 7,000 items, calling various other procedures along the way, and progress is displayed in SQL Query Analyzer using the Print command. It appears that Print events are handled automatically within the application (I assume through the InfoMessage event) and displayed as Message windows, which quickly causes an error. Is there a way to suppress this automatic handling, or do I just have to create an even handler of my own which does nothing? Tag: DataSet and Table join Tag: 107776
  - 24
    - return OleDbDataReader from static function Hi all I want to return OleDbDataReader from a static function, So I will have DB interface for my Entire project, Is it possible? How do I close the connection ?????? Thank you Sharon ///////////////////////////////////////////////////////////// //Example of use : ///////////////////////////////////////////////////////////// OleDbDataReader reader = myDbObj.Select("SELECT CustomerID, ContactName, FROM Customers") While (reader.Read()){ System.diagnostics.Trace.WriteLine(reader("CustomerID").ToString() + " : " + reader("ContactName").ToString() + "<br>") } ///////////////////////////////////////////////////////////// //select function : ///////////////////////////////////////////////////////////// Public static OleDbDataReader Select (string selectString) { 'Use a string variable to hold the ConnectionString property. String connectString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\\Program Files\\Microsoft Visual Studio\\VB98\\NWIND.MDB" OleDbConnection cn = New OleDbConnection(connectString) 'Open the connection. cn.Open() 'Use a variable to hold the SQL statement. String As String = OleDbCommand cmd = New OleDbCommand(selectString, cn) Return cmd.ExecuteReader() 'Close the reader and the related connection. // reader.Close() //?????????????????????????????????????????????????? cn.Close() } Tag: DataSet and Table join Tag: 107774
  - 25
    - How to override (null)? Sorry, this can be trivial question, but somehow I cannot find the answer to it. When I bind a data grid to a table, then some cells show a text value "(null)". This is happening for fields that have a database null value. When I manually read the database I can intercept this and override the value with a blank string, and this is the behaviour that I really want. How can I do it when I automatically bind the grid to a source? Thanks Tag: DataSet and Table join Tag: 107772
- Next
  - 1
    - ado.net create a table Hi , I want to copy a dbf file to the the access database.I thought of two solutions 1. Copy the dbf to the access db by the query SELECT * into dest_table from [source.dbf] in 'source folder' this does not work out, it gave me the error that u have no permission 2.create a table with the same structure of dbf file and then insert the rows using oledbcommandbuilder update commands i tried to create the table by the following query create table temp (test1 int,test2 varchar(40)) it gave a error message "you donot have the necessary permission to use the 'temp' object." I believe there is something to do with the permission or something other than that could some one tell me what is the solution.. Thanks in advance, Viki Tag: DataSet and Table join Tag: 107766
  - 2
    - LABEL ON not part of sql server? in sql server, how do I specify the column heading of a column? That is a column heading other than the column name. in the little sql I know, in the SQL on the IBM AS400, the following is permitted: Example 1: Enter a label on the DEPTNO column of table DEPARTMENT. LABEL ON COLUMN DEPARTMENT.DEPTNO IS 'DEPARTMENT NUMBER' thanks, -Steve Tag: DataSet and Table join Tag: 107754
  - 3
    - DataReader works even if the SQLServer is shutdown. Hi, I was thinking that the SQLDataReader should not work if there is= no connection to the SQLServer=2E We experimented the scenario but looks like after we execute the= ExecuteReader method, in the debub mode on VS=2ENET , we then= shutdown the SQLServer 2000=2E Then we tried to iterate through= the DataReader and what I expected was an exception=2E But what= actually happened was that it gave me the results one after the= other=2E Ideally the DataReader should read one row at a time from= the stream that gets connected to the DB server and which should= throw an exception if we shutdown the DB server right ? What is it that I am missing here=2E Is it that the stream is opened with another class on the= DB-server side which caches the rows when the Stored Procedure= is executed ? And so the SQLDatatReader actually makes a= connection to that class and not the server per se ? Please clarify this=2E Thanks in advance=2E Have a nice day=2E Regards, Kris -------------------------------- From: Nimmagadda Ramakrishna ----------------------- Posted by a user from =2ENET 247 (http://www=2Edotnet247=2Ecom/) <Id>QfGT0yT8/ESiPJ/G5laCDw=3D=3D</Id> Tag: DataSet and Table join Tag: 107753
  - 4
    - Patterns and Practices - SQL Database Question One of the major pitfalls of the new Patterns and Practices Enterprise Library, compared to the old version of the SQL Helper libraries, is that the code gets blown up. Before, using the SQLHelper library, I could write code as follows, while the new library I must do stuff following the first example TO DO the same thing: Please note, these are not complete examples...not worrying about exception handling or anything else...just to get the point across and my question at the end. SQL Helper Example: Public Sub Update(ByVal Row As MyTypedDataSet) SQLHelper.ExecuteNonQueryTypedParams( _ Common.ConnectionString, _ "MyTable_Update", Row, New String() { "tblMyTable" }, New Object() { } ) End Sub Patterns and Practices Enterprise Library Example: Public Sub Update(ByVal Row As MyTypedDataSet) Dim db As Database = DatabaseFactory.CreateDatabase() Dim cmd As DBCommandWrapper = db.GetStoredProcCommandWrapper("MyTable_Update") ' Manually retrieve and set the values for each sp parameter. cmd.AddInParameter("@MyId", DbType.Int32, Row.MyId) cmd.AddInParameter("@Name", DbType.String, Row.Name) cmd.AddInParameter("@Address", DbType.String, Row.Address) cmd.AddInParameter("@City", DbType.String, Row.City) cmd.AddInParameter("@State", DbType.String, Row.State) cmd.AddInParameter("@Zip", DbType.String, Row.Zip) cmd.AddInParameter("@Region", DbType.String, Row.Region) cmd.AddInParameter("@Phone", DbType.String, Row.Phone) cmd.AddInParameter("@DateOfBirth", DbType.DateTime, Row.DateOfBirth) ' Execute the stored procedure. db.ExecuteNonQuery(cmd) End Sub So, see the difference? The latter example, we have to manually set each parameter for the stored procedure. Why didn't they add the ability to "sniff" out the sp to retrieve and match stored procedure parameters (or any parameters for any type of query)? Thanks in advance, Mythran Tag: DataSet and Table join Tag: 107747
  - 5
    - DataAdapter Update If a DataAdapter.Update fails mid-way through the dataset, are earlier updates rolled back or how is this handled? For example, let's say I am a dummy and I create a dataset without setting a unique key constraint. Then I try to do an update on the underlying database. It would fail on the records with duplicates in the unique key field. What is the state of my database at that time? I Hope that question makes sense. TIA John Tag: DataSet and Table join Tag: 107744
  - 6
    - Filtering Dataviews The DataView RowFilter property rather annoyingly doesn't allow for wildcards in the middle of strings. So, for example: col LIKE 'A*B' doesn't work, i.e. it doesn't return all rows where col starts with A and ends with B and has anything inbetween. To get around this I derived my own class from DataView and parsed the RowFilter string like this: // Look for wildcards that DO NOT occur at the start or end of the string Regex reg = new Regex(@"(?<=.)\*(?=.)"); string strReplace = "*' AND " + col + " LIKE '*"; RowFilter = reg.Replace(strWild,strReplace); so col LIKE 'A*B' becomes col LIKE 'A*' AND col LIKE '*B' this works quite well except in this case: col LIKE '*A*B*' which becomes: col LIKE '*A*' AND col LIKE '*B*' this will work, it'll give you rows that contain and A followed by a B anywhere in the string, but it also returns strings that contain B followed by A. For example, given these rows: xxxAxxxBxxx xxABxxxxxxx xxxBxxxAxxx xxxAxxxxxxx The first 3 rows would be returned, but row 3 should not have been returned since A and B are in the wrong order. Does anybody have any ideas on how I could handle this? Or is there an easier way to deal with wildcards? And why doesn't DataView support wildcards in the middle of strings in the first place? Thanks Tag: DataSet and Table join Tag: 107741
  - 7
    - Easy question, need help on how to find the position of a record I have a basic customer data entry screen where the sort order is customer name. Given a customer's ID#, I need to find the position of that record in the list that is sorted by name, i.e. in the form's currency manager. In order to move the record on the screen to the record I want, I would need to something like myCurrencyManager.Position = 3. As an example, suppose I want to find customer #1 (Amy) in the following customer records: ID# Name 3 Bill 4 Bob 1 Amy 2 Susy I know how to find a record based on the id#. Just create a dataview, sort it by id#, and find the given value. The problem is, when the dataview is sorted by id#, finding id=1 will return the position in the dataview, which when sorted by id# will be position #1. When the list is sorted by name, the record I want is really in position #3. Given the above data, what code can I use to tell me that the record with id #1 is in position #3 when the list is sorted by name? Am I missing something really obvious here? TIA, Mike Rodriguez Tag: DataSet and Table join Tag: 107740
  - 8
    - Bulk insert to sql server Hi. We have an object model that return a list of about 2500 lins and i want to add them to sql as fast as i. How can I do a bulk insert to sql Using ADO.net? Thanks Tag: DataSet and Table join Tag: 107733
  - 9
    - is it possible hi all is it possible to use 2 datareaders at the same time with the same connection ???? how ??? if not ? how can i do a clone from a connection ???? thks JSB Tag: DataSet and Table join Tag: 107732
  - 10
    - How can I do a comum DAL to access SQL, Oracle, Access, etc Hi. I want to develop a class (my Data Access Layer) to access all database what am I want (like Oracle, SQL Server, Access, MySQL) Someone did anything like that? Or know how can I do that? I hear something about Pet Shop 3 model access each Oracle than Sql Server. The model they are utilized were correct? Thanks. Fernando Lopes Tag: DataSet and Table join Tag: 107730
  - 11
    - how is SQLConenction associated to a Database Hello When we open a connection using some connectionstring the connection is associated to a database which we specify in the ConnectionString. Now my question is 1) When i do a changedatabase does the connection object closes the connection to the server and opens the connection again for a new Database or does it somehow keep the connection open and somehow just changes the database it is pointing to. 2) When we do a changedatabase on the connection what happens to the Transaction context in which the Connction was. Does it provide us with a new Transaction Context or does it continue with the previous transaction context. Any comments would really help Thanks Abhishek Tag: DataSet and Table join Tag: 107728
  - 12
    - Determining the Better Approach of Transaction in n-tier Application Model and where Transactions are maintained This is a multi-part message in MIME format. ------=_NextPart_000_0045_01C53474.E25B6BA0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello, I have the scenario. I m building an application either in asp.net or = window application. This application is base on n-tier application = model. Let us take example of Northwind Database in SQL SERVER. i build an asp.net page. On that page i place the grid with one template = column. In that column i put HTML Table with two rows. In First row = there are one combobox and two textboxes. In the second row there is = another DataGrid means Inner DataGrid. I select the data from the = database and bind it with page. the data is shown in my required = formate. When i click on the edit button then that row comes into edit = mode. At that time i insert some rows in ther inner datagrid and delete = some rows and update some rows. Then i click on update button. At behind = i design the 3-tier application model. Now i update my database. The = changes i made must be in a transaction.=20 for example the tables are Order, OrderDetail in Northwind Database The Outer DataGrid show the data from Order Table (eg, columns shown are = OrderID,CustomerName,CompanyName) The Inner DataGrid show the data from OrderDetail on the basis of = OrderID from the Order Table. I have two classes in business layer one is BusinessLayer.Order, second is BusinessLayer.OrderDetail At DAL there are some methods which insert, update, and delete the = records.=20 there are two classes OrderDAL and OrderDetailDAL. from Page i call the update() method of BusinessLayer.Order Class. This method call the Update() method of OrderDAL, and second call is = Update() of BusinessLayer.OrderDetail class The Update() method of BusinessLayer.OrderDetail call the Update() = method of OrderDetailDAL. My Problem is i want to do all this things in one transaction. How Can I = do that? The second question is Where i start the transaction (I think In = business layer bcoz i call all methods here). Then next Question is What is the better approach of Transaction in this = scenario, I mean to ask whether .Net Transaction is Better or MTS DTC = (COM+) Transaction If Some One Answers that Manual transaction (.Net Transaction) is = better. Then please explain how can i make that Business Layer Method to = maintain a transaction. I m not using Business Layer CRUD Behaviour. Thanks Muhammad Jamil Nawaz ------=_NextPart_000_0045_01C53474.E25B6BA0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META http-equiv=3DContent-Type content=3D"text/html; = charset=3Diso-8859-1"> <META content=3D"MSHTML 6.00.2900.2604" name=3DGENERATOR> <STYLE></STYLE> </HEAD><FONT face=3DArial><FONT size=3D2> <BODY> <DIV>Hello,</DIV> <DIV> </DIV> <DIV>I have the scenario. I m building an application either in = asp.net or=20 window application. This application is base on n-tier application = model.</DIV> <DIV>Let us take example of Northwind Database in SQL SERVER.</DIV> <DIV>i build an asp.net page. On that page i place the grid with one = template=20 column. In that column i put HTML Table with two rows. In First row = there are=20 one combobox and two textboxes. In the second row there is another = DataGrid=20 means Inner DataGrid. I select the data from the database and bind it = with page.=20 the data is shown in my required formate. When i click on the edit = button then=20 that row comes into edit mode. At that time i insert some rows in ther = inner=20 datagrid and delete some rows and update some rows. Then i click on = update=20 button. At behind i design the 3-tier application model. Now i update my = database. The changes i made must be in a transaction. </DIV> <DIV> </DIV> <DIV>for example the tables are Order, OrderDetail in Northwind = Database</DIV> <DIV>The Outer DataGrid show the data from Order Table (eg, columns = shown are=20 OrderID,CustomerName,CompanyName)</DIV> <DIV>The Inner DataGrid show the data from OrderDetail on the basis of = OrderID=20 from the Order Table.</DIV> <DIV> </DIV> <DIV>I have two classes in business layer</DIV> <DIV>one is <STRONG>BusinessLayer.Order</STRONG>, second is=20 <STRONG>BusinessLayer.OrderDetail</STRONG></DIV> <DIV> </DIV> <DIV>At DAL there are some methods which insert, update, and delete the = records.=20 </DIV> <DIV>there are two classes <STRONG>OrderDAL </STRONG>and=20 <STRONG>OrderDetailDAL</STRONG>.</DIV> <DIV> </DIV> <DIV>from <STRONG>Page</STRONG> i call the <STRONG>update()</STRONG> = method of=20 <STRONG>BusinessLayer.Order Class</STRONG>.</DIV> <DIV>This method call the <STRONG>Update()</STRONG> method of=20 <STRONG>OrderDAL</STRONG>, and second call is <STRONG>Update()</STRONG> = of=20 <STRONG>BusinessLayer.OrderDetail</STRONG> class</DIV> <DIV>The <STRONG>Update()</STRONG> method of=20 <STRONG>BusinessLayer.OrderDetail</STRONG> call the = <STRONG>Update()</STRONG>=20 method of <STRONG>OrderDetailDAL</STRONG>.</DIV> <DIV> </DIV> <DIV>My Problem is i want to do all this things in one transaction. How = Can I do=20 that?</DIV> <DIV>The second question is Where i start the transaction (I think In = business=20 layer bcoz i call all methods here).</DIV> <DIV>Then next Question is What is the better approach of Transaction in = this=20 scenario, I mean to ask whether .Net Transaction is Better or MTS DTC = (COM+)=20 Transaction</DIV> <DIV>If Some One Answers that Manual transaction (.Net Transaction) is=20 better. Then please explain how can i make that Business Layer = Method to=20 maintain a transaction.</DIV> <DIV>I m not using Business Layer CRUD Behaviour.</DIV> <DIV> </DIV> <DIV>Thanks</DIV> <DIV> </DIV> <DIV>Muhammad Jamil Nawaz</DIV> <DIV> </DIV></BODY></HTML></FONT></FONT> ------=_NextPart_000_0045_01C53474.E25B6BA0-- Tag: DataSet and Table join Tag: 107722
  - 13
    - Excel ADO question I use Excel file as a data source for my Web Service. I want to know a few things. 1. When reading from the Excel file, does it remain locked for the time of reading the data? Must I implement some logic, to check if the file is locked? 2. May I use SQL statements to Insert, Update and Delete information in the Excel file? 3. Because I am using and OleDB connection to fill a DataSet, can I use the DataAdapter to update the data to the Excel file, like the way when a SQL Server connection is used? Thanks Tag: DataSet and Table join Tag: 107706
  - 14
    - News: VistaDB 2.0.14 embedded database for .NET released VistaDB 2.0.14 embedded database engine update for .NET has been released. Features better .NET Provider performance, new VistaDBDataSet component that provides live data cursors that are fully compatible with databound controls, world's first 3rd party managed Membership Provider for ASP.NET 2.0, improved In-Memory databases and tables, Visual Studio .NET 2005 ("Whidbey") support and more. http://www.vistadb.net/news_050323_vistadb2014_released.asp?ref=0305msdn Tag: DataSet and Table join Tag: 107703
  - 15
    - retrieving a large amount of data across the network from SQL2000 We need to retrieve approx 200,000 records containing about 100 megabytes of data across the network from a SQL 2000 DB server to a VB.NET app that then cursors through this data, does some processing and outputs it to several destination tables. What is the optimal way to handle this large amount of data? Should we retrieve this from SQL Server using an ADO.NET server-side cursor, which takes care of buffering etc? I'm concerned about the 2 hours or so that the db connection would have to be open. We could add error handling to reexecute if the query and figure out how to continue where we left off, but this sounds kludgy. We could retrieve all 100 megabytes of data into a clientside recordset and keep it in RAM. However, this would not scale should this data increase dramatically in size in the future. We could retrieve this data to the client server and save it to disk and then retrieve from it there. Ideas? Tag: DataSet and Table join Tag: 107701
  - 16
    - Yukon UDT Help Hi all, What is the syntax for adding a UDT that has a namespace? When I try CREATE ASEMBLY Foo FROM "C:\Foo.dll" WITH PERMISSION_SET=SAFE CREATE TYPE Bar EXTERNAL NAME Foo.SomeSpace.Bar I receive the error Incorrect syntax new '.'. If I create the same UDT with no namespace, everything works fine. Cheers, Rod O. Tag: DataSet and Table join Tag: 107695
  - 17
    - disconnected experiment I get An unhandled exception of type 'System.ArgumentException' occurred in system.data.dll Additional information: Format of the initialization string does not conform to specification starting at index 175. from this line: cn.ConnectionString = bs listing (more or less out of page 712, MS's "Programming VB.net") Dim bs As String bs = "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=C:\backup\laptop\debt\benifits.mdb;Mode=ReadWrite|Share Deny None;Extended Properties="";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False " Dim cn As New OleDbConnection cn.ConnectionString = bs cn.Open() Tag: DataSet and Table join Tag: 107689
  - 18
    - Cascading updates not working I have been using this feature for a long time and it's worked like a champ, but I've come across a time when it doesn't seem to work. Typically (when it works) there is a parent table that has it's Primary Key set with the AutoIncrement. There is then a Child table that has a DataRelation (with constraints) to this PrimaryKey via a column in the Child table of a column of the same datatype (for many, the same columnname also). Example: Parent "Bill" PK BillID int Child "BillPayment" PK BillPaymentID int BillID int If you add rows to the parent and get the PK and add rows to the child with that ID, the Cascading seems to work. In my case, I'm doing this: Add rows to the parent. Now instead of add rows to the Child, I Populate the Child table from the database with all columns except BillID. Then manually set BillID with the value from the Parent (the autoincrement value). The difference is that the rows are Modified instead of Added. Now when the Parent is saved to the database, the Child table's BillID column does not update!!! What gives? Thanks, -Dave Tag: DataSet and Table join Tag: 107688
  - 19
    - [ANN] March 29, 2005, "ADO.NET with Visual Basic .NET" chat Every application uses data. Join members of the Visual Studio and SQL teams (Steve Lasker, Eric Gruber, Kris Langohr, Steve Stein, Riaz Khan, Eddie Goziker, and John Chen) for a discussion of the best approaches for working with data in your applications. Date: March 29, 2005 1:00 - 2:00 P.M. Pacific time 4:00 - 5:00 P.M. Eastern time 21:00 - 22:00 GMT (For a list of the start time in several local time zones, please see http://www.timeanddate.com/worldclock/fixedtime.html?month=11&day=2&hour=13&min=0&sec=0&p1=234) Outlook Reminder: http://msdn.microsoft.com/chats/outlook_reminders/050329_DN_ADONET.ics Location (click the name of the chat to enter the chat room) http://www.microsoft.com/communities/chats/default.mspx#050329_DN_ADONET For more information about Visual Basic .NET, see http://msdn.microsoft.com/vbasic/ To see a list of upcoming chats or set a reminder for this chat, see http://msdn.microsoft.com/chats. For archives of previous chats, see http://msdn.microsoft.com/chats/recent.asp. Thanks! Jason Cooke Visual Basic Team ======== This posting is provided "AS IS" with no warranties, and confers no rights. You assume all risk for your use. (c) 2005 Microsoft Corporation. All rights reserved. Tag: DataSet and Table join Tag: 107687
  - 20
    - Updating a Dataset 2nd part: Accepted/Rejected rows ? Hello, This is the second part of my question. Forgive me if this post is too long but I like to formulate a complete question. I am currently building the DALC for our company application. When updating a Dataset I thought it could be interesting to keep track of the rows that were succesfully updated to the data source. I CAN achieve this, but it does not look the slickest solution when working with MULTIPLE TABLES. Here is what I do (quite simple): 1. Registering my adapter to handle the RowUpdated event: myDataAdapter.RowUpdated +=new OleDbRowUpdatedEventHandler(myDataAdapter_RowUpdated); 2. Handling the RowUpdated event to store the error message and skip the AcceptChanges call: private static void myDataAdapter_RowUpdated(object sender, OleDbRowUpdatedEventArgs e) { if (e.Status == UpdateStatus.ErrorsOccurred) { e.Row.RowError = e.Errors.Message; e.Status = UpdateStatus.SkipCurrentRow; } } 3. And last, simply updating the data set: myDataAdapter.Update(myDataSet); Since "SkipCurrentRow" will skip the call to DataRow.AcceptChanges(), the resulting dataset (after it has been updated) will keep the RowState for those rows where an error ocurred, the rest of the rows will have a "Unchanged" state. This is all great! (BTW, question on the side: Why do some examples appeal to calling DataSet.AcceptChanges() after an Update .... isn't this done automatically by the DataAdapter?) Now, my problem with this solution is that my DAL is not using the DataSet.GetChanges() method before calling DataAdapter.Update. I know in Tag: DataSet and Table join Tag: 107683
  - 21
    - Announcement::DataAccess Web Log Hi All, If you haven't noticed yet, the Microsoft-DataWorks team now has its own blog site at: http://blogs.msdn.com/DataAccess. You may not recognize the team, since its an internal team in Microsoft. In a nutshell, we are group that deal with developing Data Access technologies at Microsoft. To know more about us, look at the blog message posted by our Product Unit Manager - Alyssa Henry at http://blogs.msdn.com/dataaccess/archive/2005/03/20/399386.aspx. If you have a topic/content, that you would see us blog. Send it as feedback at http://blogs.msdn.com/dataaccess/archive/2005/03/21/399867.aspx Thanks, Sushil. Tag: DataSet and Table join Tag: 107682
  - 22
    - refresh schema in designer I create a dataadapter by dragging onto the design surface and create the dataset. If I make changes to the SQL table, how do I get the dataadapter to see the new table changes? Do I have to make the changes manually, or is there a refresh method? thanks norm Tag: DataSet and Table join Tag: 107681
  - 23
    - Updating a Dataset: How the Adapter works inside ? Hello, I am currently writing a DAL. When calling the DataAdapter.Update function for a Dataset, does the DataAdapter calls DataSet.GetChanges() internally before looping through rows? Or should my DAL be responsible to call GetChanges before updating? Thank you (I am talking about a single table dataset, disregarding the cases where the GetChanges must be called for multiple tables datasets) Tag: DataSet and Table join Tag: 107679
  - 24
    - connecting to the server (Type your message here) this is the second time this happend. my pop3 server .and my smip is not responing.i get a message :time out has occured and nothing happens. -------------------------------- From: jack cutler ----------------------- Posted by a user from .NET 247 (http://www.dotnet247.com/) <Id>muXQNn0K+0K2RTZlvice1A==</Id> Tag: DataSet and Table join Tag: 107676
  - 25
    - Copy a table between databases If you have a DataTable full of data, what's the easiest way to save the DataTable as a new table in a Microsoft Access database? This is a C# application. What the application needs to do is: 1) Run a select query on an Access database and fill a DataTable with the result. 2) Create a new Access database. 3) Save the DataTable created in step 1 as a new table in the database created in step 2. So far I've been able to accomplish step 1 (using ADO.NET) and step 2 (using ADOX), but I'm stumped on step 3. FYI, here's the code I use to accomplish steps 1 and 2: // Step 1 public static DataTable SelectQuery(string databaseFilePath, string sql) { string connection = "User Id=Admin;" + "Password=;Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=\"" + databaseFilePath + "\""; OleDbConnection oleDbConnection = new OleDbConnection(connection); OleDbDataAdapter oleDbDataAdapter = new OleDbDataAdapter(sql, oleDbConnection); DataTable dataTable = new DataTable(); oleDbDataAdapter.Fill(dataTable); return dataTable; } // Step 2 // requires reference to Microsoft ADO Ext. 2.7 public static void CreateAccessDatabase(string filePath) { ADOX.CatalogClass cat = new ADOX.CatalogClass(); cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filePath + ";" + "Jet OLEDB:Engine Type=5"); } // Step 3 public static void SaveDataTableIntoAccess (string databaseFilePath, DataTable dataTable) { // how the heck do I code this? } // Example of putting it all together [STAThread] public static void Main(string[] args) { string filePath = "Orders.mdb"; string newFilePath = "NewOrders.mdb"; if (File.Exists(newFilePath )) { File.Delete(newFilePath ); } string sql = "select * from orders where OrderDate > #1/1/2005#"; // Step 1 DataTable dataTable = SelectQuery(filePath, sql); // Step 2 CreateAccessDatabase(newFilePath); // Step 3 SaveDataTableIntoAccess(newFilePath, dataTable); } Tag: DataSet and Table join Tag: 107672

Is there a way to join DataTables, kind of like how you do join in SQL?

DataSet and Table join by Elton

Elton
Wed Mar 30 18:34:11 CST 2005

If you mean join query, I suppose not.

Elton Wang

>-----Original Message-----
>Is there a way to join DataTables, kind of like how you
do join in SQL?
>
>.
>

Top

RE: DataSet and Table join by AdrianMoore

AdrianMoore
Sat May 14 11:41:03 CDT 2005

You might also want to check out this web-site that promises to provide a
.NET component to allow full SQL syntax queries against a DataSet.

http://queryadataset.com

Hope this helps
Ad.

"dmsy" wrote:

> Is there a way to join DataTables, kind of like how you do join in SQL?
>
>

Top