2 DBs Ref by 1 DataAdapter?

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
    - Can't save new row to table Can't save new row to table I'm trying to save a new row using the following code: It's a schema adapted almost exactly from one given me by Kevin Yu some time ago. One difference is the inclusion of the ID field. If I don't include it I get an error message. I also have an addhandler onCAIrowupdated. The msgbox in that gives the right answer for the table's ID = 0, since it would be the first entry. However, right after the msgbox I get an error. Message: You cannot add or change a record because a related record is required in table 'PointsOfView'. The line number indicated is intModified = daCAI.Update(tblCAI) Of the six fields in ComceptAssignmentInstances, four have 1 to many relationships with with table: TopicID, PointOfVew, ReportType, and ConceptID. This is an Access database. I carefully examined the relationship table, and PointOfView is identical with all the others. So I put in the following message boxes Both showed values 0, because I hadn't changed them, and the error message showed. The second time I changed the comboxes selected items and the save was successful. Two problems. The first is the known but of having to first move the selected value of of comboboxes to 1 and then back to 0. But where and how can I do it once I click the save button? The second problem is that the ID value in the saved table was -1. Since ID is an autoincrementing long integer, why didn't it have value 0? thanks, dennist685 Private Sub btnSaveC1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSaveC1.Click Try daCAI.Fill(DsCAI1, "ConceptAssignmentInstances") Dim tblCAI As New dsCAI.ConceptAssignmentInstancesDataTable Dim rowCAI As dsCAI.ConceptAssignmentInstancesRow rowCAI = tblCAI.NewConceptAssignmentInstancesRow rowCAI.ID = -1 rowCAI.TopicID = iTopicID rowCAI.BeginText = txtConceptText.SelectionStart rowCAI.ConceptID = lstConcepts1.SelectedValue rowCAI.ReportType = Me.cboRptTypeCncpt.SelectedValue rowCAI.PointOfView = Me.cboPOVCncpt.SelectedValue 'rowCAI.ReportType = Me.cboRptTypeCncpt.SelectedValue MsgBox("report type " & rowCAI.ReportType.ToString) MsgBox("point of view " & rowCAI.PointOfView.ToString) tblCAI.AddConceptAssignmentInstancesRow(rowCAI) daCAI.InsertCommand = New OleDb.OleDbCommand("INSERT INTO ConceptAssignmentInstances(ID, TopicID, BeginText,ConceptID,ReportType,PointOfView) values (?,?,?,?,?,?)", cnCAI) 'daCAI.InsertCommand = New OleDb.OleDbCommand("INSERT INTO ConceptAssignmentInstances( TopicID, BeginText,ConceptID,ReportType,PointOfView) values (?,?,?,?,?)", cnCAI) daCAI.InsertCommand.Parameters.Add("@ID", OleDb.OleDbType.Integer, 4, "ID") daCAI.InsertCommand.Parameters.Add("@TopicID", OleDb.OleDbType.Integer, 4, "TopicID") daCAI.InsertCommand.Parameters.Add("@BeginText", OleDb.OleDbType.Integer, 4, "BeginText") daCAI.InsertCommand.Parameters.Add("@ConceptID", OleDb.OleDbType.Integer, 4, "ConceptID") daCAI.InsertCommand.Parameters.Add("@ReportType", OleDb.OleDbType.Integer, 4, "ReportType") daCAI.InsertCommand.Parameters.Add("@PointOfView", OleDb.OleDbType.Integer, 4, "PointOfView") AddHandler daCAI.RowUpdated, AddressOf OnCAIRowUpDated Dim intModified As Integer intModified = daCAI.Update(tblCAI) Dim sOutput As String sOutput = "Modified " & intModified & " ConceptAssignmentInstances" Dim s As String = ControlChars.CrLf Catch ex As OleDbException If ex.Errors(0).SQLState = "3022" Then Else Dim errorMessages As String errorMessages += "Message: " & ex.Errors(0).Message & ControlChars.CrLf _ & "NativeError: " & ex.Errors(0).NativeError & ControlChars.CrLf _ & "Source: " & ex.Errors(0).Source & ControlChars.CrLf _ & "SQLState: " & ex.Errors(0).SQLState & ControlChars.CrLf _ & "StackTrace: " & ex.stacktrace & "The form will be closed" MsgBox(errorMessages) Me.Close() End If Catch ex As DBConcurrencyException MessageBox.Show(ex.Message & "StackTrace: " & ex.stacktrace, "Update failed!", MessageBoxButtons.OK, MessageBoxIcon.Error) Me.Close() Catch ex As Exception MessageBox.Show(ex.Message & "StackTrace: " & ex.stacktrace, "Update failed!", MessageBoxButtons.OK, MessageBoxIcon.Error) Me.Close() End Try Me.cnCAI.Close() End Sub Tag: 2 DBs Ref by 1 DataAdapter? Tag: 100479
  - 2
    - Large Datasets Hello All, Does anyone have any suggestions on working with large datasets (10GB - 100GB)? Best initialization? Transfer from dataset to database? Anything would be helpful, articles, code examples, discussions, etc Thanks In Advance Tag: 2 DBs Ref by 1 DataAdapter? Tag: 100474
  - 3
    - Connection String Machine Independent. Hi there, I've been researching on the diferent newsgroups to find out how to do this, but no luck so far. I'm developing a client/server windows application with VB.NET, MSDE is my backend database. This is sample connection string to connect to my server: data source=MACHINE_NAME\MSDE; initial catalog=DatabaseName; user ID=sa;password=sa Now, when I distribute this application, the Server will have to be installed in one machine in the LAN, now the connection string it's hardcoded right now to look for a specific machine name and the instance name. So, how can I build, if it's possible, the connection string machine independent? Thanks very much, Manny Tag: 2 DBs Ref by 1 DataAdapter? Tag: 100467
  - 4
    - ADO .NET and Excel ... Hi Frankly speaking, what ever one says, Microsoft technologies will always remain problem-matic.. specially with versions... they don have seamless integration. Deployment is always always an issue. But I still love .NET!! Anyway folks, my problem goes like this... I am reading and updating excel sheet using ADO .NET. Everything is working fine on machine, but on server it is all messing up... I connect to sheet using the string called... Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES;' But whenver I try to execute insert command, following error occurs... Operation must use an updateable query.Microsoft JET Database Engine at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr) at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteNonQuery() at MyPledge.ClientPaymentData.Button1_Click(Object sender, EventArgs e) in C:\Inetpub\wwwroot\Web1\Data.aspx.vb:line 74 I will highly appreciate if someone help me... !!! eff_kay ------------------- IMPORTANT: No trees were killed during the sending of this message but a large number of electrons were terribly inconvienced. Tag: 2 DBs Ref by 1 DataAdapter? Tag: 100465
  - 5
    - How to resolve "General Network Error"? I'm getting the following message trying to update a SQL Server table: "General network error. Check your network documentation." I'm using stored procedures via a SQL data connection from within a .Net windows application. The application producing the error, reads a table from the server, then proforms a series of updates, deletions and insertions locally to a DataSet. After all processing is completed, the data is written back to the server using the Update(dataset, "table_name") method on the DataAdapter. The Update method is called 3 times, once for all updated rows, once for all inserted rows and once for all deleted rows. Currently, I read and update 3 tables in seperate datasets. 2 tables work and the 3rd does not. How can I get resolved this issue? Tag: 2 DBs Ref by 1 DataAdapter? Tag: 100464
  - 6
    - Getting SQL Error - "General network error" I'm getting the following message trying to update a SQL Server table: "General network error. Check your network documentation." I'm using stored procedures via a SQL data connection from within a .Net windows application. The application producing the error, reads a table from the server, then proforms a series of updates, deletions and insertions locally to a DataSet. After all processing is completed, the data is written back to the server using the Update(dataset, "table_name") method on the DataAdapter. The Update method is called 3 times, once for all updated rows, once for all inserted rows and once for all deleted rows. Currently, I read and update 3 tables in seperate datasets. 2 tables work and the 3rd does not. How can I get resolved this issue? Tag: 2 DBs Ref by 1 DataAdapter? Tag: 100463
  - 7
    - create table based on existing tables in a dataset, how ? hi all i create a dataset with table a, b and c in it. Is it possible for me to generate extra table (e.g. d) into this dataset based on table a and table b (which already exist in this dataset) ? note that creation of table d, include a LEFT JOIN between table a and b. pls give me some suggestion / example on how to do it. cheers, Tag: 2 DBs Ref by 1 DataAdapter? Tag: 100457
  - 8
    - Cannot open OracleConnection with Oracle 9.2 in ASP.NET Hi I'm using System.Data.OracleClient with VisualStudio.NET 2003 and trying to connect to Oracle 9.2 database. Oracle client software for 9.2 has been installed and I can perfectly make a connection in a Windows Forms application. But on the same machine, I cannot make a connection in an ASP.NET web application. The Exception is raised when I try to open the connection as follows: System.Data.OracleClient requires Oracle client software version 8.1.7 or greater. What is happening? Tag: 2 DBs Ref by 1 DataAdapter? Tag: 100456
  - 9
    - ado.net exception from raiserror In a couple of stored procedures (SQL Server 2000) im doing: raiserror('aaa',16,1) return 1 My understanding was that this should throw an exception in my C# code. But sometimes it doesn't (I have to manually check the return value parameter). Can anyone explain why? Is there a difference in behaviour between SqlCommand.ExecuteNonQuery and ExecuteReader (or SqlDataAdapter.Fill) in this regard? The situation where I've noticed no exception is being thrown is when I'm calling adapter.Fill(), and the sproc has already returned a resultset (but later on in the sproc code it detects an error). Maybe the presence of a resultset prevents an exception being thrown? Thanks, John Tag: 2 DBs Ref by 1 DataAdapter? Tag: 100448
  - 10
    - Better way to build results? (Relative newbie) Hello, Undoubtedly this answer is in front of my nose, but I can't figure it out or find it anywhere. Using VB.NET, Access database. I have successfully built a series of nested queries that return results from three interrelated tables, reading the results into datareaders. I can iterate through the three datareaders and build the results that I want, but I suspect there are more efficient ways to do what I need. The only way I have been able to get this to work is by establishing one connection for the first query, and then in a nested loop keep making connections to satisfy the second query, and within that loop there is another loop that keeps making connections to satisfy the third query. This works fine on my very small test database, but I know users will have much larger ones. I can't use UNION, because the tables have differing numbers of fields. Let's say I have three tables: Neighborhoods, Houses, and Residents (not my real database!). Neighborhood.nID = Houses.nID and Houses.hID = Residents.hID. The trick is that I might not have houses for every neighborhood, and I might not have residents for every house. But I need to get all the neighborhoods, even the ones without houses, and I need to get all of the houses, even the ones without residents. Conversely, every resident has to have a house, every house has to have a neighborhood. So my simplified code is: Establish first connection Get all the neighborhoods Read the first neighborhood Build a node for a TreeView Establish/reestablish second connection Get all the houses in that neighborhood Read the first house Build a node for a TreeView Establish/reestablish third connection Get all the residents in that house Read the first resident Build a node for a TreeView Loop Close third connection when we've found all the residents for that house Loop Close second connection when we've found all the houses for that neighborhood Loop Close first connection when we've found all the neighborhoods Any and all advice gratefully received. Thanks, Chris Tag: 2 DBs Ref by 1 DataAdapter? Tag: 100447
  - 11
    - Error: ArgumentOutOfRangeException on stringbuilder Hi experts, I wrote a function to use stringbuilder for a fix length text delimited file for banking stuff. I received an error on strResult.Insert(141, "".PadLeft(21, " ")) Error: System.ArgumentOutOfRangeException Message: "Index was out of range. Must be non-negative and less than the size of the collection. StackTrace: "at System.Text.StringBuilder.Insert(Int32 index, String value, Int32 count) at System.Text.StringBuilder.Insert(Int32 index, String value) at EPay_Enroller.JenniferFunct.GenerateA(String& strEmpID, String& strClientAcct, String& strAcctStatus, String& strAddressFlag, String& strNameFlag) Any help? ------------------------------------------------------------------------------------------- Public Function GenerateA(ByRef strEmpID As String, ByRef strClientAcct As String, ByRef strAcctStatus As String, ByRef strAddressFlag As String, ByRef strNameFlag As String) Dim strResult As New System.Text.StringBuilder Dim GPConnection As SqlConnection Dim dr As SqlDataReader Dim GPDataset As New DataSet GPConnection = New SqlConnection("...................................................") Dim selectCMD As SqlCommand = New SqlCommand("SELECT DDACTNUM, LASTNAME,FRSTNAME,BRTHDATE FROM UPR00102 where EmployID=' " + strEmpID + "'", GPConnection) Dim GPDataAdapter As SqlDataAdapter = New SqlDataAdapter GPDataAdapter.SelectCommand = selectCMD Dim EfundAcct As String Dim LName As String Dim FName As String Dim MName As String Dim DOB As String Dim Loc As String Try GPConnection.Open() Dim DS As DataSet = New DataSet GPDataAdapter.Fill(DS, "AcctInfoTble") EfundAcct = DS.Tables("AcctInfoTble").Rows(0)("DDACTNUM") LName = DS.Tables("AcctInfoTble").Rows(0)("LASTNAME") FName = DS.Tables("AcctInfoTble").Rows(0)("FASTNAME") 'Position 1 2A A strResult.Insert(0, "A ") 'Position 3 10A Employee Number strResult.Insert(2, strEmpyID.ToString.PadRight(10, " ")) .........codes 'Position 142 21A Filler strResult.Insert(141, "".PadLeft(21, " ")) ......more codes -------------------------------------------------------------------------------------------- *-----------------------* Posted at: www.GroupSrv.com *-----------------------* Tag: 2 DBs Ref by 1 DataAdapter? Tag: 100445
  - 12
    - Adding columns to SQL Server tables dynamically Hi, I'm writing a routine which queries a table in a mySQL database and then adds records from it into a SQL Server 2000 database - pretty easy stuff. Problem is, that the mySQL table may have additional columns every time I query it. I can easily use the Columns collection of both tables to see if there is a new column in the mySQL table which isn't in the SQL Server table, but what's the easiest / most efficient way to add a new column to an existing SQL Server table, given that I know the name and datatype of the column to be added? Would it be to pass some sort of "ALTER TABLE..." SQL into an SQLCommand object, or is there a better way? Any assistance gratefully received. Best regards, Mark Rae Tag: 2 DBs Ref by 1 DataAdapter? Tag: 100440
  - 13
    - Dataset Error:IndexOutofRangeException Hi experts, I have a dataadapter and datasets. I debugged it and gave me an error on LName = DS.Tables("AcctInfoTble").Rows(1)("LASTNAME") error: There is no row at position 1. WHAT DID I DO WRONG at the following code? Thanks. ------------------------------------------------------------------------------------------------ Public Function GenerateA(ByRef strEmpID As String, ByRef strClientAcct As String, ByRef strAcctStatus As String, ByRef strAddressFlag As String, ByRef strNameFlag As String) Dim strResult As New System.Text.StringBuilder Dim GPConnection As SqlConnection Dim dr As SqlDataReader Dim GPDataset As New DataSet GPConnection = New SqlConnection("workstation id=ADMIN;packet size=4096;user id=MaintenanceHD;data source=AMKSQL2;p" & _ "ersist security info=True;initial catalog=HFC;password=maintainme") Dim selectCMD As SqlCommand = New SqlCommand("SELECT DDACTNUM, LASTNAME,FRSTNAME,BRTHDATE FROM UPR00102 where EmployID=' " + strEmpID + "'", GPConnection) Dim GPDataAdapter As SqlDataAdapter = New SqlDataAdapter GPDataAdapter.SelectCommand = selectCMD Dim EfundAcct As String Dim LName As String Dim FName As String Dim MName As String Dim DOB As String Dim Loc As String Try GPConnection.Open() Dim DS As DataSet = New DataSet GPDataAdapter.Fill(DS, "AcctInfoTble") EfundAcct = DS.Tables("AcctInfoTble").Rows(0)("DDACTNUM") LName = DS.Tables("AcctInfoTble").Rows(1)("LASTNAME") FName = DS.Tables("AcctInfoTble").Rows(2)("LASTNAME") ......more codes -------------------------------------------------------------------------------------------- *-----------------------* Posted at: www.GroupSrv.com *-----------------------* Tag: 2 DBs Ref by 1 DataAdapter? Tag: 100437
  - 14
    - Incorrect in passing parameters Hi all, I received an error: System.NullReferenceException: Object reference not set to an instance of an object at GenerateA(String& strEmpID, String& strClientAcct, String& strAcctStatus). When I run the debug, and in the command line I entered ?strEmpID, and it does passed the correct employee ID. However, in the first line of the dataset, EfundAcct = DS.Tables("AcctInfoTble").Rows(0)("DDACTNUM") it generates error message "Object reference not set to an instance of an object" and stacktrace is "at GenerateA(String& strEmpID, String& strClientAcct, String& strAcctStatus)" ---------------------------------------------------- Main.vb Main.VB ----------------- some codes........ Dim strCurEmployId As String strCurEmployId = dtrSelectTransactions("EmployID") smCurBatch.WriteLine(GenerateA(strCurEmployId, boxTopCorp.Text, "Open")) some codes............... ------------------------------------------------ Public Function GenerateA(ByRef strEmpID As String, ByRef strClientAcct As String, ByRef strAcctStatus As String) Dim strResult As New System.Text.StringBuilder Dim GPConnection As SqlConnection Dim dr As SqlDataReader Dim GPDataset As New DataSet GPConnection = New SqlConnection(".............................................") Dim GPDataAdapter As New SqlDataAdapter("SELECT LASTNAME,FRSTNAME,MIDLNAME,SOCSCNUM,ADDRESS1,PHONE1,BRTHDATE FROM UPR00102 where EmployID= " + strEmpID, GPConnection) Dim EfundAcct As String Dim LName (30) As Char Dim FName (30) As Char Dim MName (30) As Char GPConnection.Open() GPDataAdapter.Fill(GPDataset, "AcctInfoTble") Dim DS As DataSet = New DataSet EfundAcct = DS.Tables("AcctInfoTble").Rows(0)("DDACTNUM") LName = DS.Tables("AcctInfoTble").Rows(1).Item("LASTNAME") FName = DS.Tables("AcctInfoTble").Rows(2)("LASTNAME") MName = DS.Tables("AcctInfoTble").Rows(3)("MASTNAME") 'Position 1 2A A strResult.Insert(0, "A ") 'Position 3 10A Employee Number strResult.Insert(9, strEmpID.PadRight(10, " ")) 'Position 13 16N Efund acct number strResult.Insert(12, EfundAcct.PadLeft(16, " ")) GPConnection.Dispose() Return strResult.ToString End Function *-----------------------* Posted at: www.GroupSrv.com *-----------------------* Tag: 2 DBs Ref by 1 DataAdapter? Tag: 100435
  - 15
    - DataSet, Relation, IsNested and WebService Hi all, If I want to provide a typed dataset from a webservice and if that dataset has relations that are nested (isNested=True), then the relations(keyrefs) are not available in the XSD that is offered. If I set IsNested to false or default, then it is included in the XSD. Is this a bug or by design? Corno Tag: 2 DBs Ref by 1 DataAdapter? Tag: 100431
  - 16
    - Can you package a oledb data provider? Hello, Is it possible to package a oledb data provider? I need to be able to access an iSeries machine, but the server that the program is being installed on does not have the ibmda400 provider. The easy way would be to install client express, but unfortunatly that can not happen. Any ideas? Thanks, Brian Tag: 2 DBs Ref by 1 DataAdapter? Tag: 100430
  - 17
    - Converting from DataColumn to OleDbType for Analysis Services 2005?? Its been posted before, but I've not found a satisfactory answer. Environment: Dot-Net 2.0 beta, with Analysis Services 2005 beta. DbDataAdapter produces DataColumns from its FillSchema method which give me all sorts of information about the data type of the column. AMO, the API for Analysis Services 2005, requires me to give an OleDbType when I set up a foriegn key in a fact table and when I make an Attribute. (I'm using metadata as a starting point to scan a data warehouse and build the dimensional model programatically. Think of it as a form of Intellicube, but especially taylored to the structure of our data warehouses feeding off our existing metadata.) The thing is. I've used DbDataAdapter to FillSchema() to get the underlying field types in the tables. It should _know_ what data type is there as its just told me. I suppose the problem is that DataTable class being generic can't know anything about specific databases, but it would be nice if there was a method somewhere to take a DataColumn and get the XXXDbType from it. Analysis Services already knows the data type, as it's got a Data Source View. I've presumably already told it once when I defined the schema by passing in the DataSet that defined it - the DataSet made using FillSchema. Or have I? I'm building up a model that presumably hasn't been committed yet, so presumably the problem is that there's no way (even though I've given it a Connection String) for it to go to the database and find out what it needs, and its not willing to wait until commit time to work it out? Can I assume that the DataColumn doesn't have enough information in itself? Therefore the Data Source View is, at that time, incomplete, and I have to add the information myself from an external source? If thats the case - it's not nice. Also, as an aside, why (I'm following Microsoft's sample code) am I using an OleDbDataType with an SQLServer database? Thanks - Richard Tag: 2 DBs Ref by 1 DataAdapter? Tag: 100428
  - 18
    - Value in output parameter not accessable I am using the Microsoft Data Application Block to access a database and am trying to execute the following stored procedure: CREATE PROCEDURE WhiteboardPostItem @Id int OUTPUT, @Subject varchar(255), @Message varchar(2000), @PostedBy varchar(100) AS INSERT INTO Whiteboard(Subject, Message, PostedBy, DatePosted) VALUES (@Subject, @Message, @PostedBy, getdate()) SELECT @Id = @@IDENTITY GO This procedure is called from the following code: Public Shared Function PostItem(ByVal Subject As String, ByVal Message As String, ByVal PostedBy As String) As Integer Dim arParams() As SqlParameter = New SqlParameter(3) {} arParams(0) = New SqlParameter("@Id", SqlDbType.Int) arParams(0).Direction = ParameterDirection.Output arParams(1) = New SqlParameter("@Subject", SqlDbType.VarChar, 255) arParams(1).Value = Subject arParams(2) = New SqlParameter("@Message", SqlDbType.VarChar, 2000) arParams(2).Value = Message arParams(3) = New SqlParameter("@PostedBy", SqlDbType.VarChar, 100) arParams(3).Value = PostedBy ' Call to Microsoft Data Application Block SqlHelper.ExecuteNonQuery(ConnectionString(), "WhiteboardPostItem", arParams) Return Convert.ToInt32(arParams(0).Value) End Function However, the output parameter (arParams(0)) is never set to the identity value of the inserted row even though the row does get added. I've checked the stored procedure in query analyser and the return value is properly set but it does not work through this code. The code in the above function is now in the format described in a Microsoft example and it still doesn't work. I'm sure I'm missing something obvious, but can anyone tell me how to resolve this as it's driving me mad! Many thanks Alan Tag: 2 DBs Ref by 1 DataAdapter? Tag: 100421
  - 19
    - Viewing large amounts of data We have a data management application writen in Delphi. This app shows all tables in the database, and display all data in a given table in a datagrid. We are using an ODBC connection for this. This functionality is not unlike what is seen in e.g. Enterprise manager. Now even with 400K of rows, this datagrid is "filled" is mere seconds. I am aware that the underlying dataset only retrieves a subset of rows, and dynamically retrieves and unloads rows as needed, thereby giving an illusion of retrieving the entire table. We are now moving to .net and I find myself having a great deal of difficulty getting the same functionality out of ado.net. The ODBC components we are using in our Delphi app is connected whereas ado.net is disconnected. What I basically need is to retrieve say 100 rows at a time, and dynamically unload and retrieve new rows and filling them into a datagrid as the user scrolls back and forth through the datagrid. How do I achieve this? Please dont lecture about filtering and whatnot. This is the functionality we need. -- Thomas Due Posted with XanaNews version 1.16.5.2 "If you can't convince them, confuse them." -- Harry S. Truman Tag: 2 DBs Ref by 1 DataAdapter? Tag: 100419
  - 20
    - include rowstate in dataset.getxml Hi Gurus, how can i include rowstate value in dataset.getxml string <datatable> <row1 state="Added"> <col1>v1</col1> </row1> <row2 state="Modified"> <col1>v2</col1> </row2> </datatable> </dataset> i am passign the above string to stored proc(sql200) to perform DML operations any help on this. regards mak Tag: 2 DBs Ref by 1 DataAdapter? Tag: 100407
  - 21
    - deleting rows in one-to-many relationship Dear Ng, I have a 1(master table) : n(first child table) : m(second child table) relationship in my sql server and strongly typed dataset. Now I locate the corresponding row in the master table. DataAccessControl.DocumentTemplateRow rowTemplate = tblTemplate.FindBydocumentTemplateId(templateId); Then i request all child rows: DataRow[] rowsToDelete = rowTemplate.GetChildRows("DocumentTemplateDocumentField"); Then I want to delete all child rows in the child tables: foreach (DataAccessControl.DocumentFieldRow row in rowsToDelete) { tblFields.RemoveDocumentFieldRow(row); } dac.updateDataAccessLayer(); The delete rules of both relations (from the master to the first child and from the first to the second child) is set to cascade (in the database and in the dataset). But if I execute the code, just the rows in the second child table are deleted. What do I do wrong? If I use additionally the row.delete() command foreach (DataAccessControl.DocumentFieldRow row in rowsToDelete) { row.Delete(); if (row.RowState == DataRowState.Deleted) tblFields.RemoveDocumentFieldRow(row); } dac.updateDataAccessLayer(); I'll get a DBConcurrencyException! What is wrong? What I want to achieve is that the master record is kept and all child records are deleted. Thanks in advance and best regards, Patrick Braunschweig Tag: 2 DBs Ref by 1 DataAdapter? Tag: 100403
  - 22
    - Trouble with OleDbDataAdapter Hi there, I am trying to display information from Active Directory using a DataGrid. Now, I know MS have put out the System.DirectoryServices classes to make this easy. But if you just want to query and display, it seems an easier path is to use the AdsDsoObject provider with OleDb, and get back a DataTable, pour into a DataView, bind to the DataGrid, voila! At least, that's the theory. The main driver is that trying to bind a SearchResultCollection to a DataGrid doesn't yield very satisfactory results, and that's the only useful class in the DirectoryServices namespace that implements ICollection and IEnumerable. The problem is, if I manually construct a DataTable with dummy data, pour into a DataView, bind to DataGrid and display then I see exactly what I would expect. If I setup an OleDbDataAdapter, query the AD, fill a DataSet and serialize to XML I see what I would expect. If I setup an OleDbDataAdapter, query the AD, fill a DataTable, pour into a DataView, bind to the DataGrid and display, I get: System.Web.HttpException: DataGrid with id '' could not automatically generate any columns from the selected data source Here is the source for Page_Load that generates the last result: string ldapString = "<LDAP://DC=acme,DC=com>;(&(objectClass=user)(objectCategory=person));cn,distinguishedName;subtree"; string connstr = "Provider=ADsDSOObject;User ID=acme\\Administrator;Password=notthispassword"; OleDbConnection conn = new OleDbConnection(connstr); OleDbDataAdapter da = new OleDbDataAdapter(ldapString, conn); DataTable dt = new DataTable("Table1"); da.Fill(dt); DataView dv = new DataView(dt); dg1.DataSource = dv; dg1.DataBind(); Now, I'm fairly new to all this, but I've spent a good couple of days butting up against this one. The only checking I haven't really done is using OleDb with a different provider and seeing if it is a general problem. The main reason I haven't done that is that I can see the data there if I use an alternate means of accessing it! So, any ideas? MTIA, Mick Heywood Tag: 2 DBs Ref by 1 DataAdapter? Tag: 100402
  - 23
    - Create Stored procedure Hi, there I want to create a Stored procedure for my Access database using the code below. ' Conn is an opened connection to my Access database Dim cmd As OleDbCommand cmd = Conn.CreateCommand cmd.CommandType = CommandType.Text cmd.CommandText = _ "CREATE PROCEDURE udpGetSampleIDByName" & vbCrLf & _ "@zSampleName VarChar(64)" & vbCrLf & _ "AS " & vbCrLf & _ "Select zSampleID FROM TSamples " & _ "Where zSampleName = @zSampleName " cmd.ExecuteNonQuery() After running, I got a error message: "invalid SQL Syntax£ºneeded symbol AS¡£" It goes well when I create SQL Server stored procedure using SQLCommand . Now I turn to OleDB and the error occurs. Anybody can help me? Peter Tag: 2 DBs Ref by 1 DataAdapter? Tag: 100401
  - 24
    - "Cannot create a child list for field MyTable" I'm having a frustrating problem trying to set up a master-detail relationship between 2 datagrids. I've used DataSets extensively before, but only in code and basically only in a disconnected state. Now as I try to set up this simple master-detail thing, I'm having problems. I have about 6 tables in a SQL Server 2000 database. All tables have single field primary keys, and I've created appropriate relationships betwen the tables in SQL Server. In VS 2003, I've created a SQLDataAdapter for each table, that interacts with the table via stored procedures created by the SQLDataAdapter wizard. I then used the "Generate Dataset..." functionality of one of the data adapters to autogenerate dataset that included all 6 tables (i.e. it was based on all 6 data adapters). So far so good. In the DataSet schema editor, I created relationships between the datatables to mimic the relationships in SQL Server. This seems to go ok, and when I "Preview DataSet" from the schema editor, all seems ok - including the relationships. I also have a WinForm that has 2 DataGrids on it. Both DataGrids have the DataSet I created as their DataSource. When I try to list the DataTables in the DataGrid.DataMember property so I can set up the DataGrids for the master-detail, I the the error, "Cannot create a child list for field MyTable", where 'MyTable' is one of my DataTables in the DataSet. If I remove all of the relations from the DataSet, and save the .xsd schema a few times (moving the datatables on the designer between saves so there's something to save), the error will go away - but then I have no relationships. So, I re-create the relationships in the DataSet schema editor, and I start getting the error again. Sometimes I don't get the error until I have more than one relationship, and sometimes by moving the datatables on the schema designer and re-saving the dataset schema a few times, I don't get the error - even when the dataset has 2 relationships. It seems some code is getting corrupted somewhere. I've tried restarting VS, and I've rebooted my machine. I've also deleted the DataSet and created it again from scratch. The same thing happens. When running my project, I can successfully fill my DataSet from the DataAdapters, but I get the same error when I try to set the DataMember property of one of the DataGrids in code. What's going on? Tag: 2 DBs Ref by 1 DataAdapter? Tag: 100400
  - 25
    - SQLDMO Code Sometimes Works I have this code on two identical machines, named DEVONE and DEVTWO. Both on the network. It works on DEVONE, finding (local) and DEVONE and populates the combo box with them, but does not find any server(s) on DEVTWO, putting <No Servers Found> in the combo box. Can anyone shed any light. Code below: Private Sub GetSQLServers() Dim i As Integer Dim sqlApp As New SQLDMO.ApplicationClass Dim sqlServers As SQLDMO.NameList = sqlApp.ListAvailableSQLServers() For i = 0 To sqlServers.Count - 1 Dim srv As Object = sqlServers.Item(i + 1) If Not IsNothing(srv) Then Me.cboRelevantServer.Items.Add(srv) Me.cboMainServer.Items.Add(srv) End If Next If Me.cboRelevantServer.Items.Count > 0 Then Me.cboRelevantServer.SelectedIndex = 0 Else Me.cboRelevantServer.Text = "<No Servers Found>" End If End Sub Thanks, Michael Tag: 2 DBs Ref by 1 DataAdapter? Tag: 100399
- Next
  - 1
    - DataSet and Stringbuilder? Hi expert, I received a sample from bank for the header record in the output file (attached at the bottom). I want to use same method to have a stringbuilder to arrange the fix length positions, and the fields will be retrieved from database and put into the dataset. But I am familiar at how to use dataset and put into .insert stringbuilder. Here is the code that I am working on (the function should return 1 string line at a time. The loop of reading rows will be controled in main form): Can someone show me how to retrieve a column, say LastName and put into strResult.Insert()??? Thanks. Imports System.Data.SqlClient Module Module1 Public Function GenerateA(ByRef strEmpID As String, ByRef strClientAcct As String, ByRef strAcctStatus As String, ByRef strAddressFlag As String, ByRef strNameFlag As String) Dim strResult As New System.Text.StringBuilder Dim GPConnection As SqlConnection Dim dr As SqlDataReader Dim GPDataset As New DataSet GPConnection = New SqlConnection("............................") Dim GPDataAdapter As New SqlDataAdapter("SELECT LASTNAME,FRSTNAME,MIDLNAME,PHONE1,BRTHDATE FROM UPR00102 where EmployID="+strEmpID,GPConnection) Try GPConnection.Open() GPDataAdapter.Fill(GPDataset, "AcctInfoTble") Dim DS As DataSet = New DataSet 'position 1 20A strResult.Insert ????????????????? Catch ex As Exception MessageBox.Show(ex.ToString) Finally GPConnection.Dispose() End Try Return strResult.ToString End Function End Module ------------------------------------------------------------------------------------- Module GenerateFile Public Function GenerateFH(ByRef strCompanyName As String, ByRef strTopCorp As String, ByRef strBatchNum As String, ByRef dtFileDate As Date) Dim strResult As New System.Text.StringBuilder strResult.Capacity = 731 'Position 1 2A FH strResult.Insert(0, "FH") 'Position 3 10A Header Date strResult.Insert(2, dtFileDate.Date.Year & "/" & dtFileDate.Date.Month.ToString.PadLeft(2, "0") & "/" & dtFileDate.Date.Day.ToString.PadLeft(2, "0")) 'Position 13 8A Header Time strResult.Insert(12, dtFileDate.TimeOfDay.Hours.ToString.PadLeft(2, "0") & ":" & dtFileDate.TimeOfDay.Minutes.ToString.PadLeft(2, "0") & ":" & dtFileDate.TimeOfDay.Seconds.ToString.PadLeft(2, "0")) 'Position 21 10A Transfer File Version strResult.Insert(20, "4.0".PadRight(10, " ")) 'Position 31 10A Product Type strResult.Insert(30, "EFUNDS".PadRight(10, " ")) ............. Return strResult.ToString End Function End Module --------------------------------------------------------------------------- *-----------------------* Posted at: www.GroupSrv.com *-----------------------* Tag: 2 DBs Ref by 1 DataAdapter? Tag: 100394
  - 2
    - Moving solution to another machine, SQL Failure Hi all, I've developed a .NET 2003/SQL Server 2000 application. When the app fires up, it loads combo boxes (Using SQLDMO) with available servers, databases and tables. It works great. However, when I move the solution to another PC and try to run it, it fails saying "Login failed for user DEVONE\Guest'. DEVONE is the name of the machine where I developed the application, which is running SQL Server 2000 Developer. No where in my code does it reference DEVONE, so somewhere a reference to the previous machine name or SQL Server instance name is being held in the solution. Any ideas? Thanks Michael Tag: 2 DBs Ref by 1 DataAdapter? Tag: 100390
  - 3
    - GetChanges false positives, How to avoid? I was curious if anybody know of a way to avoid false positives while using the GetChanges method. In most of my updateable aspx pages I have text/ddl/checkboxes that are populated from the existing data that the user can change, then hit the save/update button. When the save/update button is clicked the dataset is updated from the controls values regaurdless if the values in the controls have changed or not. Such as ds.Table1.Col1 = TextBox1.Text; // set whether text has changed or not. ds.Table1.Col2 = TextBox2.Text; The problem arises when a user hits the save/update button without changing anything on the page. This means that all the values in the dataset get set to exactly what they were before; however, the GetChanges returns rows with no real changes in them. Take the following code. The last GetChanges always returns 1 row even though no values in the row have changed. Dataset1 ds = new Dataset1(); Dataset1.UsersRow r = ds.Users.NewUsersRow(); string s = "asdf"; r.UserName = s; r.UserID = 1; ds.Users.Rows.Add(r); ds.AcceptChanges(); DataTable dt = ds.Users.GetChanges(); if(dt != null) Console.WriteLine("# of changed rows " + dt.Rows.Count); ds.Users[0].UserName = s; // change the value to exactly what it was. dt = ds.Users.GetChanges(); if(dt != null) Console.WriteLine("# of changed rows " + dt.Rows.Count); Any help is appreciated, EntDevGuy Tag: 2 DBs Ref by 1 DataAdapter? Tag: 100388
  - 4
    - SchemaTable I'm using the following code to obtain information about any table in a database. Now _schemaTable contains the info about the table and one of its fields is "IsKey" which as far as I know should identify the columns that are the primary keys in the table. Unfortunately "IsKey" is always false for me even though some columns are the primary keys. Why is that? Is there something I'm doing wrong or something that I'm not doing? Some of the other information in the _schemaTable such as "ColumnName" is correct for each of the columns in the table. The following is the code sample: Thanks for help in advance. Tom. System.Data.IDataReader reader; this._Command.CommandText = "SELECT * FROM [" + tableName + "]"; reader = _Command.ExecuteReader(System.Data.CommandBehavior.SingleRow); _schemaTable = reader.GetSchemaTable(); Tag: 2 DBs Ref by 1 DataAdapter? Tag: 100385
  - 5
    - Row-wise vs columnwise If I need to iterate through all the field values in a dataset, is there any preference for organising the outer iterations by row or by column . ie should I do (in VB.Net): For i as integer = 1 to table(0).columns.count For j as integer = 1 to table(0).rows.count ......... Next j Next i or swap columns or rows, or is it immaterial? TIA JGD Tag: 2 DBs Ref by 1 DataAdapter? Tag: 100383
  - 6
    - SqlException - Help pls Using SQLServer database. UserId defined as numeric and First/LastName fields defined as nvarchar(50). Here is my SQL Statement: SELECT UserId, LastName & ', ' & FirstName AS FullName FROM Users ORDER BY LastName, FirstName; What does this error mean? Server Error in '/Test' Application. ________________________________________ Invalid operator for data type. Operator equals boolean AND, type equals nvarchar. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Invalid operator for data type. Operator equals boolean AND, type equals nvarchar. Source Error: Line 136: End If Line 137: Line 138: Dim dr As SqlDataReader = MySqlHelper.ExecuteReader(CONNECTION_STRING, CommandType.Text, strSql) Line 139: Line 140: Return dr Source File: c:\inetpub\wwwroot\Test\Components\dbUsers.vb Line: 138 Stack Trace: [SqlException: Invalid operator for data type. Operator equals boolean AND, type equals nvarchar.] System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, String commandText, SqlParameter[] commandParameters, SqlConnectionOwnership connectionOwnership) Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteReader(String connectionString, CommandType commandType, String commandText, SqlParameter[] commandParameters) Microsoft.ApplicationBlocks.Data.SqlHelper.ExecuteReader(String connectionString, CommandType commandType, String commandText) Test.dbUsers.GetAllUsers(Int32 UserId) in c:\inetpub\wwwroot\Test\Components\dbUsers.vb:138 Test.ManageUsers.FillUsersDdl() in c:\inetpub\wwwroot\Test\Secured\ManageUsers.aspx.vb:139 Test.ManageUsers.Page_Load(Object sender, EventArgs e) in c:\inetpub\wwwroot\Test\Secured\ManageUsers.aspx.vb:59 System.Web.UI.Control.OnLoad(EventArgs e) +67 System.Web.UI.Control.LoadRecursive() +35 System.Web.UI.Page.ProcessRequestMain() +731 ________________________________________ Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET Version:1.1.4322.573 Tag: 2 DBs Ref by 1 DataAdapter? Tag: 100382
  - 7
    - How to effiently load many, many SQL records I have a need to effiently load millions of fairly small records into SQL Sever 2000. I have some financial data with many, many observations (one set has 37 million records) and I need to get it into a SQL server. I create a comma separated variable (CSV) file using a VB.NET program acting on the raw data. I have tried importing it into SQL Server using DTS import, but it says that it completes after about 18 million records, with no error. Using Visual Studio 2004 VB, I wrote a windows program that uses a SQL dataadapter to the table I want to insert records in, set the field parameters to the generated SQL insert command and then call ExecuteNoQuery to insert each record. This process works, but is seems to take forever to create a lot of records this way. I am fairly well versed in VS 2003 and VB.NET, but I am new to using these tools to load so many records. I must be doing something wrong or not using the appropriate technique to do this. Any help will be appreciated. Thanks! Bill Patterson UW-Madison School of Business Tag: 2 DBs Ref by 1 DataAdapter? Tag: 100378
  - 8
    - Navigating child rows of a relationship Hi there, Here's my problem : I have 2 tables with a one-to-many relationship. I setup the relationship via the dataset component. ParentTable : Parentkey col1 col2 ChildTable : Childkey ParentKey col1 col2 I Have setup the databindings like this : textBox1.DataBindings.Add("Text",ds,"ParentTable.Parentkey"); textBox2.DataBindings.Add("Text",ds,"ParentTable.col1"); textBox3.DataBindings.Add("Text",ds,"ParentTable.col2"); textBox4.DataBindings.Add("Text",ds,"ParentTable.MyRelation.Childkey"); textBox5.DataBindings.Add("Text",ds,"ParentTable.MyRelation.Parentkey"); textBox6.DataBindings.Add("Text",ds,"ParentTable.MyRelation.col1"); textBox7.DataBindings.Add("Text",ds,"ParentTable.MyRelation.col2"); I setup a currency manager to track position and navigate in the dataset like this : myCurrency = (CurrencyManager)this.BindingContext[ds,"ParentTable"]; myCurrency.Position = 0; The events to navigate through the ParentTable rows are as follows : private void navPrevious_Click(object sender, System.EventArgs e) { if(myCurrency.Position != 0) { myCurrency.Position -= 1; } } private void navNext_Click(object sender, System.EventArgs e) { myCurrency.Position += 1; } My question is : since the ParentTable can have one or many chlidren, how can i navigate (Navigate next, previous,etc ) through these rows ? Do i have to setup another currency manager ? I tried this : myCurrency2 = (CurrencyManager)this.BindingContext[ds,"TableParent.MyRelation"]; myCurrency2.Position = 0; This doesn't seem to work if i setup the events like this : private void navPrevious_Click(object sender, System.EventArgs e) { if(myCurrency2.Position != 0) { myCurrency2.Position -= 1; } } private void navNext_Click(object sender, System.EventArgs e) { myCurrency2.Position += 1; } What am i doing wrong ? Is there a solution ? Thanks a lot for the help. Tag: 2 DBs Ref by 1 DataAdapter? Tag: 100375
  - 9
    - Connection Pool Hello, Is it ado provider by default do the connection pooling? or it is depending on the database. I know sqlclient provider does the connection pooling but can I use connection pooling with MYSQL? thanks ap Tag: 2 DBs Ref by 1 DataAdapter? Tag: 100371
  - 10
    - All data is text in ADO Update to Excel... I am updating several excel worksheets using ADO.NET (OLEDB objects). = Everything is working fine except all the data is inserted as text = despite being typed in the DataTable object and when the Excel worksheet = was actually created. If the user-specified Excel Workbook does not exist, one is created and = worksheets are added using "CREATE TABLE" statements with type-specific = data columns. The data type for each column is specified in the = DataTable object as well as the actual "CREATE TABLE" SQL statement. However, if the user-specified workbook already exists, the worksheets = will be updated correctly, but all data will be inserted as text values = regardless of how it is typed in the DataTable. Any help, suggestions, or advice would be greatly appreciated. - Carl Tag: 2 DBs Ref by 1 DataAdapter? Tag: 100367
  - 11
    - Cascade without relation I have two datatables, dtTable1 has an INDENTITY column, dtTable2 has a integer column to have the value of table1 indentity column but it can be null. so I can't create the relation between tables (unless there is some kind of ignore null relation). Now when I add new row to dtTable1, then add new row to dtTable2 with the same indentity value (of cause a temp value, will change in dtTable1 when save to database) from dtTable1, is there any way to cascade value to dtTable2 when I save data into database? Please... Wei Tag: 2 DBs Ref by 1 DataAdapter? Tag: 100363
  - 12
    - ADO.NET text driver not importing data as text I have the following comma delimited file to read: Date,campaign code,count 12/1/2004,DM31,10 12/1/2004,DM32,15 12/1/2004,DM33,20 12/1/2004,DM34,25 12/1/2004,DM35,30 I'm using the ADO.NET, System.Data.Odbc namespace to create and ODBCConnection to this file and read it using the following (simplified) code. Dim cn As New OdbcConnection("Driver={Microsoft Text Driver (*.txt; _ *.csv)};DefaultDir=d:\websites\jpg\dataimport;") Dim cmd As New OdbcCommand ("select * From cfr.csv") Dim myreader As OdbcDataReader cn.open() cmd.connection = cn myreader = cmd.executereader() response.write("<table>") While myreader.read() response.write("<tr><td>" & myreader.getstring(0) & "</td><td>" & _ myreader.getstring(1) & "</td><td>" & _ myreader.getstring(2) & "</td></tr>") End While response.write("</table>") cn.close() It outputs the following: 2004-12-01 00:00:00 31.0000 10 2004-12-01 00:00:00 32.0000 15 2004-12-01 00:00:00 33.0000 20 2004-12-01 00:00:00 34.0000 25 2004-12-01 00:00:00 35.0000 30 It converted the second field to a numeric value. I can get it to treat the field as character data if I put the values inside quotes but I don't have control of this file, it's delivered to me from a third party as is. Is there anyway to get it to read the data as plain character data without modifying the file? --Buddy Tag: 2 DBs Ref by 1 DataAdapter? Tag: 100360
  - 13
    - DateTimePicker problem with .AddNew Hi! My Windows Form contains DateTimePicker to show date-type data from DataTable. Because application need to be able to save "no date" ie. NULL into database table, I solved it like way... ("dtpRepaired" is DateTimePicker-control) dtpRepaired.Checked = Not IsDBNull(dt.Rows(cm.Position)("Repaired")) If dtpRepaired.Checked Then dtpRepaired.Value = Date.Parse(dt.Rows(cm.Position)("Repaired").ToString()) End If Private Sub dtpRepaired_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles dtpRepaired.TextChanged dt.Rows(cm.Position)("Repaired") = IIf(dtpRepaired.Checked, dtpRepaired.Value, DBNull.Value) End Sub ...because I didn't succeeded to do this like this way bounding it directly to the DataTable... 'dtpRepaired.DataBindings.Add(New Binding("Text", dt, "Repaired")) This is almost working fine, but it occurs an error after cm.AddNew when trying to change dtpRepaired-controls value or Checked-state. Error message is: "There is no row at Position 5.". I understand this why, but don't know how I could solve to make it possible to "add new" and make it possible to edit dtpRepaired too without this problem. Any succestions? Is it really impossible to bound DateTimePicker-control to DataTable, and make it possible to change date in DataTable to "NULL" ? Hopefully you understand my problem with my poor english, I'm finnish :) -- Thanks in advance! Mika Tag: 2 DBs Ref by 1 DataAdapter? Tag: 100358
  - 14
    - Problem catching exception from a procedure Hi! I wrote a procedure in my sybase database this procedure is one line only, and attemp to insert a record in a table generating a constraint violation of a primary key and an exception. I do this only for test... then when I call the procedure from .net: com.Connection = conn com.CommandType = CommandType.StoredProcedure com.CommandText = "PRUEBA" Dim valor Try conn.Open() com.ExecuteNonQuery() Catch ex As System.Data.OleDb.OleDbException ms = ex.Message finally conn.Close() End Try there is not excetion catch!!! , I try several code modification whitout result... Tag: 2 DBs Ref by 1 DataAdapter? Tag: 100351
  - 15
    - SqlDataAdapter aand transaction Hello, I'm using SqlDataAdapter to insert data from one table into another with SqlCommand after I start a transaction. If I have convertion error for the insertion of the data, The transaction is no longer valid, I'ts rolled back. Is there a property to leave the transaction open ? my code : SqlTransaction trans = m_Conn.BeginTransaction(); SqlCommand oCmd1 = new SqlCommand("Insert into tbl1(col1) select col1 from tbl2", m_Conn); oCmd1.Transaction = trans; oCmd1.ExecuteNonQuery(); Thanks, Sharon Tag: 2 DBs Ref by 1 DataAdapter? Tag: 100349
  - 16
    - DataSet, new row and default values ? When I add a new row to a DataSet that was filled with info from a database the new row does not get the default values defined in SQL Server, how do I accomplish this seemingly simple task ? I don't want to have to fill in the default values in the row first, I want it to understand the defaults set in the database and fill it automatically as you would expect it to. Thank you PL. Tag: 2 DBs Ref by 1 DataAdapter? Tag: 100348
  - 17
    - Best aproach to update DataAdapters with XML-Data Hi all What's the best aproach to import xml-based data into an oracle 9i database with ado.net DataAdapters, when the data is in an xml-file? The operations should insert new rows and update changed rows. Does ado.net provide some automatisms for this or do i have to do this with functions written myself? Is there anywhere some example code? Thank you Adrian Tag: 2 DBs Ref by 1 DataAdapter? Tag: 100347
  - 18
    - Help ... I need somebody Help I have create such a DB -> look at the end: And I would like to have a 2 comboboxes in which I could select all Prog_Id And Profile_Id and in this way modified datasource in each of Rows in table Progs and aloso 6 textboxes to display and modified datasource in each of rows in Profile_Base. How it can be done in the fastest way ?? It is really urgent, beacuse i sit on it 3 days !! Thanks in advance ----------------------------------------- CREATE DOMAIN For_Prog_ID AS VARCHAR(20); CREATE DOMAIN For_SubProgs As VARCHAR(20); CREATE TABLE Profile_Base( Profile_ID For_SubProgs NOT NULL PRIMARY KEY, Param_1 FLOAT, Param_2 FLOAT, Param_3 FLOAT, Param_4 FLOAT, Param_5 FLOAT, Param_6 FLOAT ); CREATE TABLE Progs ( Prog_ID For_Prog_ID NOT NULL PRIMARY KEY, Profile_S1 For_SubProgs, Profile_S2 For_SubProgs, Tenon_S1_S1 For_SubProgs, Tenon_S1_S2 For_SubProgs, Tenon_S2_S1 For_SubProgs, Tenon_S2_S2 For_SubProgs, FOREIGN KEY (Profile_S1) REFERENCES Profile_Base (Profil_ID) ON UPDATE CASCADE ON DELETE CASCADE, FOREIGN KEY (Profile_S2) REFERENCES Profile_Base (Profil_ID) ON UPDATE CASCADE ON DELETE CASCADE, FOREIGN KEY (Tenon_S1_S1) REFERENCES Profile_Base (Profil_ID) ON UPDATE CASCADE ON DELETE CASCADE, FOREIGN KEY (Tenon_S1_S2) REFERENCES Profile_Base (Profil_ID) ON UPDATE CASCADE ON DELETE CASCADE, FOREIGN KEY (Tenon_S2_S1) REFERENCES Profile_Base (Profil_ID) ON UPDATE CASCADE ON DELETE CASCADE, FOREIGN KEY (Tenon_S1_S2) REFERENCES Profile_Base (Profil_ID) ON UPDATE CASCADE ON DELETE CASCADE ); -------------------------------- *-----------------------* Posted at: www.GroupSrv.com *-----------------------* Tag: 2 DBs Ref by 1 DataAdapter? Tag: 100344
  - 19
    - How To Recursively Search Directories by Using C# .NET I've found this script on microsoft site that searches for file with given extension, its window based. How can i convert it to make it web based? I am looking for a c# script that will search a particular drive with given file extension and email the search result. http://support.microsoft.com/default.aspx?scid=kb;en-us;303974 Please help.. many thanks in advance. Tag: 2 DBs Ref by 1 DataAdapter? Tag: 100340
  - 20
    - Updating table at a different database Hello, I'm using SqlDataAdapter to get records from table at database different from my connection using the databasename at the select statment "Select * from otherdb..myTable" After updating the data I want to update the table, I'm using SqlCommandBuilder to set the UpdateCommand at the SqlDataAdapter. But when I use the SqlDataAdapter.Update I get an error : "Invalid object name 'myTable'" I tried to change the UpdateCommandText to "Update otherdb..myTable ..." but it's not working. I don't want to use another connection. Is there a way of making it work? Thanks, Sharon. Tag: 2 DBs Ref by 1 DataAdapter? Tag: 100336
  - 21
    - Printing excel files Hi. Can anybody help me on how to print excel files that has been read via ADO.NET. Or anyway, what I want to avoid is automation (instantiating an excel application object). Hopefully, there is way aroudn this.. Thanks in advance. Tag: 2 DBs Ref by 1 DataAdapter? Tag: 100324
  - 22
    - higher reads when using ADO.NET thatn when using Query Analyzer I was doing some testing and noticed that when I execute a stored procedure from my ADO.NET (using the Command class of the System.Data.SQLClient namespace) the number of reads on a relatively simple query were significantly higher. As a matter of fact I have not found a case where the reads when executed from ADO.NET are lower than the reads when executing in QA. Sometimes they may be the same but QA is always has equal or fewer reads. Any reason why this is and is there any thing I can do to get ADO.NET to perform like QA? --Buddy Tag: 2 DBs Ref by 1 DataAdapter? Tag: 100317
  - 23
    - OleDbConnection problem with more than 1 connection Hi, I had wrote 2 app in c#.net one Windows Form base and one Web Base and those 2 connect to a access database and some time I got this error in my web base application and I can't figure out what's wrong, I just hit reload and it's work Thanks for your time Sylvain Bissonnette Server Error in '/WebApplication1' Application. Cannot open database ''. It may not be a database that your application recognizes, or the file may be corrupt. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.OleDb.OleDbException: Cannot open database ''. It may not be a database that your application recognizes, or the file may be corrupt. Source Error: Line 77: // Open database connection Line 78: OleDbConnection MyConn = new OleDbConnection(ConnectionString); Line 79: MyConn.Open(); Line 80: Line 81: // Create the data adapter and execute the query Source File: C:\Inetpub\wwwroot\WebApplication1\Light.aspx.cs Line: 79 Stack Trace: [OleDbException (0x80004005): Cannot open database ''. It may not be a database that your application recognizes, or the file may be corrupt.] System.Data.OleDb.OleDbConnection.ProcessResults(Int32 hr) +20 System.Data.OleDb.OleDbConnection.InitializeProvider() +57 System.Data.OleDb.OleDbConnection.Open() +203 WebApplication1.Light.MyDataGrid_ItemCommand(Object source, DataGridCommandEventArgs e) in C:\Inetpub\wwwroot\WebApplication1\Light.aspx.cs:79 System.Web.UI.WebControls.DataGrid.OnItemCommand(DataGridCommandEventArgs e) +110 System.Web.UI.WebControls.DataGrid.OnBubbleEvent(Object source, EventArgs e) +68 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +26 System.Web.UI.WebControls.DataGridItem.OnBubbleEvent(Object source, EventArgs e) +106 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +26 System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +121 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +115 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain() +1277 Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET Version:1.1.4322.573 Tag: 2 DBs Ref by 1 DataAdapter? Tag: 100315
  - 24
    - Command Builder in tables without primary key Hi every one, i'm trying to use a sqlcommandbuilder in an application using a table without a primary key, when i call the DataAdapter.Update Method there comes an Error saying that "the selectcommand does not return information about the primary key" or some thing like that... Does some one knows a way to implement the command builder or similar method to work around on updating tables with out primary key?? Tag: 2 DBs Ref by 1 DataAdapter? Tag: 100306
  - 25
    - SqlHelper.UpdateDataSet with transaction Hi, I'm trying to use the SqlHelper.UpdateDataSet method with transactions. The DAAB 2.0 documentaion only say: "SqlHelper.UpdateDataset does not include transaction support. If you need this, you can implement it yourself by a preceding call to the helper method SqlHelper.ExecuteScalar("begin transaction")." but I didn't find any matching overload in the ExecuteScalar method. What would be the correct way of using the UpdateDataSet with transaction? Thanks, Gwenda Tag: 2 DBs Ref by 1 DataAdapter? Tag: 100293

I have two SQL Server databases and I want to reference tables from both DBs
in the SQL. How can I have a connection object that points to both DBs?
Thanks,
T

Top

Re: 2 DBs Ref by 1 DataAdapter? by Jim

Jim
Sun Dec 05 12:12:54 CST 2004

Assuming that they are on the same server, use the three part naming
convention

databasename.owner.tablename

e.g.

select * from northwind.dbo.customers
select * from pubs.dbo.authors

"Tina" <tinamseaburn@removespamexcite.com> wrote in message
news:ui5arHv2EHA.2112@TK2MSFTNGP15.phx.gbl...
>I have two SQL Server databases and I want to reference tables from both
>DBs in the SQL. How can I have a connection object that points to both
>DBs?
> Thanks,
> T
>

Top

Re: 2 DBs Ref by 1 DataAdapter? by Tina

Tina
Sun Dec 05 13:06:04 CST 2004

When using the data adapter wizard, a specific database connection must be
chosen ie. either northwind or pubs and then only tables from that
connection are available in the querybuilder.

I guess my question was "how can this be done using the DataAdapter wizard
and querybuilder.
T

"Jim Hughes" <NOSPAMJ3033@Hotmail.com> wrote in message
news:eQdCMYv2EHA.1296@TK2MSFTNGP10.phx.gbl...
> Assuming that they are on the same server, use the three part naming
> convention
>
> databasename.owner.tablename
>
> e.g.
>
> select * from northwind.dbo.customers
> select * from pubs.dbo.authors
>
> "Tina" <tinamseaburn@removespamexcite.com> wrote in message
> news:ui5arHv2EHA.2112@TK2MSFTNGP15.phx.gbl...
>>I have two SQL Server databases and I want to reference tables from both
>>DBs in the SQL. How can I have a connection object that points to both
>>DBs?
>> Thanks,
>> T
>>
>
>

Top

Re: 2 DBs Ref by 1 DataAdapter? by Chris

Chris
Sun Dec 05 12:49:07 CST 2004

Hi Tina,

If the database servers are on separate machines you can configure a linked
server or use ad hoc distributed queries.. This can be done from within the
Enterprise Manager or you can use the sp_addlinkedserver procedure, for
ad-hoc distributed queries take a look at OPENROWSET.

In both cases you will be setting up a single connection to one server and
the server will manage the connection and retrieval of data from the other
server. For databases on a single server you merely need to prefix the
object name with the database name to query accross databases.

Hope this helps

--
Chris Taylor
http://dotnetjunkies.com/weblog/chris.taylor

"Tina" <tinamseaburn@removespamexcite.com> wrote in message
news:ui5arHv2EHA.2112@TK2MSFTNGP15.phx.gbl...
> I have two SQL Server databases and I want to reference tables from both
DBs
> in the SQL. How can I have a connection object that points to both DBs?
> Thanks,
> T
>
>

Top