Metadata about Foreign Keys ADO.NET 2.0

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
    - Help with DataRelationCollection indexer In the code below, when I use the integer indexer I get the correct output. DataRelation myRelation = ds.Tables["TestDocument"].ChildRelations[0]; Output: No.1TestItems No.2TestItems But when I use the string indexer the output is nil. DataRelation myRelation = ds.Tables["TestDocument"].ChildRelations["RelationDocumentItems"]; Output: Will someone help me understand what I'm doing wrong? Thanks, Jeff Higgins using System; using System.Data; using System.Xml; using System.Text; using System.Windows.Forms; namespace xmltest { class Class1 { [STAThread] static void Main(string[] args) { StringBuilder s = new StringBuilder(); DataSet ds = new DataSet(); ds.ReadXml("test.xml", XmlReadMode.Auto); DataRelation myRelation = ds.Tables["TestDocument"].ChildRelations["RelationDocumentItems"]; DataTable myTable = ds.Tables["TestDocument"]; DataRow[] arrRows; foreach(DataRow myRow in myTable.Rows) { arrRows = myRow.GetChildRows(myRelation, DataRowVersion.Current); for(int i = 0; i < arrRows.Length; i++) { s.Append(arrRows[i]["name"] + "\n"); } } MessageBox.Show(s.ToString(), "Menu Data", MessageBoxButtons.OK); } } } <?xml version="1.0" encoding="utf-8"?> <TestDocument xmlns="http://tempuri.org/test.xsd" me="{E292E134-BEC8-49f7-A1E8-C3580C76C5B8}" name="TestDocument"> <TestItems me="{3738B467-C142-4ece-88B6-666327FA37B0}" owner="{E292E134-BEC8-49f7-A1E8-C3580C76C5B8}" name="No.1TestItems"> <TestItem me="{72C372F3-4E96-4a82-892B-B441E42D77B6}" owner="{3738B467-C142-4ece-88B6-666327FA37B0}" name="No.1FirstTestItem" /> <TestItem me="{BEB920DF-C2BA-46cf-8E59-EB060E2715DD}" owner="{3738B467-C142-4ece-88B6-666327FA37B0}" name="No.1SecondTestItem" /> <TestItem me="{3776230C-0669-4be4-BBFB-1D50789320D4}" owner="{3738B467-C142-4ece-88B6-666327FA37B0}" name="No.1ThirdTestItem" /> </TestItems> <TestItems me="{560223A6-0938-4a3b-89CD-9507F1F26682}" owner="{E292E134-BEC8-49f7-A1E8-C3580C76C5B8}" name="No.2TestItems"> <TestItem me="{2AA34205-3695-4f2c-9DF0-C3D0EA9E1AF2}" owner="{560223A6-0938-4a3b-89CD-9507F1F26682}" name="No.2FirstTestItem" /> <TestItem me="{3EC22224-B0BE-4364-A631-F455AD2E6F61}" owner="{560223A6-0938-4a3b-89CD-9507F1F26682}" name="No.2SecondTestItem" /> <TestItem me="{8FC33219-4104-458e-A08A-8A5DB549DD50}" owner="{560223A6-0938-4a3b-89CD-9507F1F26682}" name="No.2ThirdTestItem" /> </TestItems> </TestDocument> <?xml version="1.0" ?> <xs:schema id="NewDataSet" targetNamespace="http://tempuri.org/test.xsd" xmlns:mstns="http://tempuri.org/test.xsd" xmlns="http://tempuri.org/test.xsd" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" attributeFormDefault="qualified" elementFormDefault="qualified"> <xs:element name="TestDocument"> <xs:complexType> <xs:sequence> <xs:element name="TestItems" minOccurs="0" maxOccurs="unbounded"> <xs:complexType> <xs:sequence> <xs:element name="TestItem" minOccurs="0" maxOccurs="unbounded"> <xs:complexType> <xs:attribute name="me" form="unqualified" type="xs:string" /> <xs:attribute name="owner" form="unqualified" type="xs:string" /> <xs:attribute name="name" form="unqualified" type="xs:string" /> </xs:complexType> </xs:element> </xs:sequence> <xs:attribute name="me" form="unqualified" type="xs:string" /> <xs:attribute name="owner" form="unqualified" type="xs:string" /> <xs:attribute name="name" form="unqualified" type="xs:string" /> </xs:complexType> </xs:element> </xs:sequence> <xs:attribute name="me" form="unqualified" type="xs:string" /> <xs:attribute name="name" form="unqualified" type="xs:string" /> </xs:complexType> <xs:key name="KeyPrimaryTestDocument" msdata:PrimaryKey="true"> <xs:selector xpath="." /> <xs:field xpath="@me" /> </xs:key> <xs:key name="KeyPrimaryTestItems" msdata:PrimaryKey="true"> <xs:selector xpath=".//mstns:TestItems" /> <xs:field xpath="@me" /> </xs:key> <xs:key name="KeyPrimaryTestItem" msdata:PrimaryKey="true"> <xs:selector xpath=".//mstns:TestItem" /> <xs:field xpath="@me" /> </xs:key> <xs:keyref name="RelationDocumentItems" refer="mstns:KeyPrimaryTestDocument"> <xs:selector xpath=".//mstns:TestItems" /> <xs:field xpath="@owner" /> </xs:keyref> <xs:keyref name="RelationItemsItem" refer="mstns:KeyPrimaryTestItems"> <xs:selector xpath=".//mstns:TestItem" /> <xs:field xpath="@owner" /> </xs:keyref> </xs:element> <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:EnforceConstraints="False"> <xs:complexType> <xs:choice maxOccurs="unbounded"> <xs:element ref="TestDocument" /> </xs:choice> </xs:complexType> </xs:element> </xs:schema> Tag: Metadata about Foreign Keys ADO.NET 2.0 Tag: 101762
  - 2
    - Disable Datagrid scrolling with mouse wheel Hi, In my datagrid I add the control (numeric updown) in very streightforward way: I add this control to the datagrid controls collection and when I click the particular cell the numeric control apper instead of cell (at the place of cell, to be exact) The problem is: when I scroll the mouse wheel the datagrid is scrolling TOGETHER with my cell with numeric updown. The mouse wheel movement affects also the numeric up down, but as far as the whole datagrid is also scrolling I "lose" the cell as it goes away of visibility. The question is: How can I restrict the datagrid scrolling when I move mouse wheel and make the wheel scrolling affect only the numeric up down. Thanks beforehand, hope that I explained clear enough the problem and my question. -- Message posted via http://www.dotnetmonster.com Tag: Metadata about Foreign Keys ADO.NET 2.0 Tag: 101760
  - 3
    - DataRelation with 3 tables Hi, I'm trying to setup two DataRelations for the 3 tables in my application. The first table is the master, the 2nd relates to the master based on the selected record and the third relates to the second based on the selected record. The problem I'm having is the third table filters based on the selected record of the main table and not the child dataset. Does anyone know how to implement a master/detail with 3 levels with DataRelation objects or suggest a better way to do it. Below is the code I'm using. Thanks for your help. Public Sub LoadDataset() Dim SqlServer As New SqlServer Dim TableName() As String = {"JobSummary"} Dim DBConnectionString As String = System.Configuration.ConfigurationSettings.AppSettings.Get("ConnectionString") TableName(0) = "Job" SqlServer.FillDataset(DBConnectionString, "GetJobsNotCompleted", FDataset, TableName, FMachineName) TableName(0) = "WorkOrder" SqlServer.FillDataset(DBConnectionString, CommandType.Text, _ "SELECT * from fl_Work_Order_Summary " & _ "WHERE MachineID = " & FMachineName, FDataset, TableName) TableName(0) = "Item" SqlServer.FillDataset(DBConnectionString, CommandType.Text, _ "Select * from fl_Item_Summary " & _ "WHERE MachineID = " & FMachineName, FDataset, TableName) FDataset.Relations.Add("JobWorkOrders", FDataset.Tables("Job").Columns("JobID"), FDataset.Tables("WorkOrder").Columns("JobID"), False) FDataset.Relations.Add("WorkOrderItems", FDataset.Tables("WorkOrder").Columns("OrderID"), FDataset.Tables("Item").Columns("OrderID"), False) End Sub Private Sub MainForm_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load LoadDataset() DataGrid1.DataSource = FDataset DataGrid1.DataMember = "Job" DataGrid2.DataSource = FDataset DataGrid2.DataMember = "Job.JobWorkOrders" DataGrid3.DataSource = FDataset DataGrid3.DataMember = "WorkOrder.WorkOrderItems" End Sub Tag: Metadata about Foreign Keys ADO.NET 2.0 Tag: 101758
  - 4
    - binding a dropdown list in web app guys, i'm binding the results of a sql query to a drop down list in a web app in the usual way. dropdownlist.datasource= ds.tables(0) dropdownlist.datatextfield ="org1" dropdownlist.databind() now what if i wanted to concatenate other fields and combine them. specifially org1,org2,org3 & org4 together normally in sql this is easy. just select org1||org2||org3||org4 from org_security; and the result would be a long string. and works fine in toad or sql+ but if i want to bind that whole string to the dropdown list, just like i did with the single value, how would i do that? i've tried playing with the datatextfield property all sorts of ways, and nothing seems to work. any ideas would be appreciated. thanks again. ********************************************************************** Sent via Fuzzy Software @ http://www.fuzzysoftware.com/ Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources... Tag: Metadata about Foreign Keys ADO.NET 2.0 Tag: 101749
  - 5
    - Table relations across datasets Hi all, May be this is an illogical question. But I will try anyway. Is there a way to create a relationship between two tables across datasets without violating the constraints? in other words, the primary table (with a foreign key) is in dataset1 and it's lookup table is in dataset2. I will explain why I need this setup. I have a Web application. I want to store all the lookup tables at the Application level and the business logic dataset at the Session level. The Session level dataset tables have foreign keys that need to map to these lookup tables. I don't want to populate these lookup tables at session level since it could choke the system resources if the number of sessions are huge. Any idea anybody? Babu. Tag: Metadata about Foreign Keys ADO.NET 2.0 Tag: 101746
  - 6
    - Is Whidbey the beginning of the end for the developer ? Hi Everyone I have just been getting up to speed on the forthcoming ASP.Net 2.0 (Whidbey) that Microsoft is releasing some time this year. It all looks great but I was a bit concerned that it aims to reduce the amount of code that needs to be written by the developer by 70%. The new GridView control will be "far simpler to use, requires less code, and provides far more power than the DataGrid Control". After working extremely hard to get my Computer Science degree and investing my own hard earnt money heavily in MSDN subscriptions and programming books etc just to keep my skills up to date, I began to wonder if it had all been worthwhile now that the forthcoming ASP.Net 2.0 (Whidbey) seems to be abstracting much of the techniques that I had learnt to "hand code". For instance, I was particularly pleased with my implementation of Roles Based Forms Authentication that I had mastered in C# but now I understand that it will all be wasted now that this functionality will be covered by the new Membership classes. I fully understand that by definition, IT is a constantly evolving industry but it does make me worry that the very systems that we use to improve business efficiency could one day make the role of the qualified developer redundant. The way things are going, the office junior will be able to do all the stuff that the highly qualified and trained developer once did and the developers will be ignored because the higher wages that they rightly attract will no longer be justified. Maybe this is what Microsoft are working towards or maybe I'm just a Luddite. Please accept my appologies for the cross-posting but it's just something that I feel very stongly about and is of equal importance to each group. Tag: Metadata about Foreign Keys ADO.NET 2.0 Tag: 101736
  - 7
    - Cann't get my only record with OracleDatareader object I a newbie to VB.net (only about 3 three weeks). I wrote a small module to upload file to server and send out a e-mail with the upload files. the codes basics as: Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Dim num As Integer cart_id = Request.QueryString("cart_id") num = Request.QueryString("num") .. End Sub Sub SubmitButton_Click(ByVal Source As Object, ByVal e As EventArgs) Dim filepath As String = "D:\Emallupload" ' "D:\Emallupload" Dim uploadedFiles As HttpFileCollection = Request.Files Dim strFilenamesCollection As String = "" Dim i As Integer = 0 Dim arrPostedFile As New ArrayList Dim fullFileName As String Do Until i = uploadedFiles.Count Dim userPostedFile As HttpPostedFile = uploadedFiles(i) Try If (userPostedFile.ContentLength > 0) Then userPostedFile.SaveAs(filepath & "\" & cart_id & "~" & _ Path.GetFileName(userPostedFile.FileName)) strFilenamesCollection = strFilenamesCollection + cart_id + "~" + Path.GetFileName(userPostedFile.FileName) + ";" fullFileName = cart_id & "~" & Path.GetFileName(userPostedFile.FileName) arrPostedFile.Add(fullFileName) End If Catch ex As Exception Span1.InnerHtml += "Error:<br>" & ex.Message End Try i += 1 Loop Span1.InnerHtml += strFilenamesCollection & " Has been saved.<p>" StorePostedFile(cart_id, strFilenamesCollection, arrPostedFile) End Sub Private Sub StorePostedFile(ByRef cart_id As String, ByRef strFilenamesCollection As String, ByRef af As ArrayList) OracleConnection(ConfigurationSettings.AppSettings("strCon")) Dim sTrans As OracleTransaction oCon.Open() sTrans = oCon.BeginTransaction() Dim oCmd As New OracleCommand("set_order_attachments", oCon, sTrans) oCmd.CommandType = CommandType.StoredProcedure oCmd.Parameters.Add("p_cart_id", OracleType.VarChar, 15).Value = cart_id oCmd.Parameters.Add("p_attachment", OracleType.VarChar, 255).Value = strFilenamesCollection Try oCmd.ExecuteNonQuery() sTrans.Commit() EmailPostedFile(cart_id, af, oCon) Catch e As Exception ... Finally .... End Try End Sub Private Sub EmailPostedFile(ByRef cart_id As String, ByRef fn As ArrayList, ByRef oCon As OracleConnection) Dim .. Dim sTrans As OracleTransaction sTrans = oCon.BeginTransaction() Dim eCmd As New OracleCommand("get_vendor_email_info", oCon, sTrans) eCmd.CommandType = CommandType.StoredProcedure eCmd.Parameters.Add("p_cart_id", OracleType.VarChar, 15).Value = cart_id eCmd.Parameters.Add("o_FirstName", OracleType.VarChar, 40).Direction = ParameterDirection.Output eCmd.Parameters.Add("o_LastName", OracleType.VarChar, 40).Direction = ParameterDirection.Output Try eCmd.ExecuteReader() Dim myReader As OracleDataReader = eCmd.ExecuteReader() If myReader.Read Then FirstName = myReader.GetString(0) LastName = myReader.GetString(1) Else ... End If Catch e As Exception .... Finally .. End Try If noEmail Then .. rest of email codes End If End Sub Although the first Sub work (an Insert), I had dificult to run the second Sub, which selected one row back. When I used the OracleDataReader object, the myReader.read was always false even I have tested outside that there was definetely had a record return. Since the myreader.read is a standard way of syntax. I could not figure out what went wrong or missing. Can anyone help me out ? Thanks. C Chang Tag: Metadata about Foreign Keys ADO.NET 2.0 Tag: 101732
  - 8
    - I want help with the jet 4.0 provider.. I try to make a connection with an access database using jet 4.0 OLE DB provider and i receive the following messages: "Test connection failed because of an error in initializing provider.catastrophic failure" and/or "An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll Additional information: No error information available: REGDB_E_CLASSNOTREG(0x80040154)." I have made the following steps until now -I updated my jet 4.0 db engine with the last version (4.00.8618.0) -I re-installed visual studio.net....and nothing change!! I use windows xp and office 2003. Can anyone Help me with this problem? Tag: Metadata about Foreign Keys ADO.NET 2.0 Tag: 101726
  - 9
    - ADO and ADO.net in the same transaction Our old COM data access components use ADO. As we are now begining to move to C#.net, we would like any new data access components to use ADO.net. Therefore we will end up having two seperate connections: One ADO connection and one ADO.net connecation. Is there any way of making it so that updates made through either of these connections will be part of the same transaction? We use Oracle but do not currently have any preference as to which ADO.net data provider we use. Thanks, Jonathan Tag: Metadata about Foreign Keys ADO.NET 2.0 Tag: 101719
  - 10
    - REGDB_E_CLASSNOTREG(0x80040154) -> What else to do? Hi, It seems I ran out of clues. This is the situation: Win98se client with Windows.Net Application (framework 1.1, MDAC2.8, Jet 4.0 Service Pack for Win 98) and the following connection string: "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\archive\db\app.MDB;User Id=admin;Password=;" When I try to open the connection: Dim conn As New OleDbConnection(ConnectionString) conn.Open() the error message in the subject is shown... Who can help me out on this?! Thank you in advance & Happe NewYear! Tag: Metadata about Foreign Keys ADO.NET 2.0 Tag: 101716
  - 11
    - error in executing SELECT statement I get this message when executing SELECT statement in SQL 2000: Location: recbase.cpp:1374 Expression: m_nVars>0 S PID: 51 Process ID: 2480 Why? Any help? Tag: Metadata about Foreign Keys ADO.NET 2.0 Tag: 101715
  - 12
    - DataAdapter call ExecuteReader instead of ExecuteNonQuery I'm working on my own implementation of an ADO.NET Data Provider derived from IDbXXXX classes and MSDN sample (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconImplementingNETDataProvider.asp). All is fine when I execute simple Command and DataReader but I got an error with DataAdapter. Running of SelectCmd and InsertCmd work fine but each UpdateCmd or DeleteCmd thrown a DBConcurrencyException. It seems that problem is that DataAdapter call ExecuteReader instead of ExecuteNonQuery on InsertCmd, UpdateCmd and DeleteCmd. So, number of row affected is probably incorrect. Do I miss something or is it an usual way of working for a DataAdapter ? Why ExecuteReader is called for an Update and a Delete without any result expected ? Thanks in advance for your help or advices. Lionel. Tag: Metadata about Foreign Keys ADO.NET 2.0 Tag: 101712
  - 13
    - Add Connection to ADO Database Hi, Im trying to create a connection to A database in Visual Studio.Net. I select Tools->Connect to Database. Nothing happens. Im trying to connect a data source to my datagrid, but not even able to connect to the database I want to. Any suggestions will be appreciated. Thanks. Tag: Metadata about Foreign Keys ADO.NET 2.0 Tag: 101707
  - 14
    - Returning error from stored proc This is probably a stupid question but its been so long since I've had to do it with ADO, I can't decide which way to do this in ADO.Net. I have basic stored procedures (SQL 2000/MSDE) that do inserts, updates and deletes. I call these from an SQLDataAdapter Update/Insert/Delete -> Update command. So, I can put a try/catch around the adapter Update call, but should I just be trying to catch a Sqlexception? How should I throw the error within the stored proc? For example, I can do: DELETE FROM tblMine WHERE myID = @iID IF @@ERROR <> 0 BEGIN ROLLBACK TRAN A RETURN ? END Should I return a value? I suppose a basic sample might be all I need. Thanks. STom Tag: Metadata about Foreign Keys ADO.NET 2.0 Tag: 101706
  - 15
    - DataSet/field alias Can a DataSet be used to update a database if the query that originally populated it had table and field alias names in it? Tag: Metadata about Foreign Keys ADO.NET 2.0 Tag: 101701
  - 16
    - ADO unable to get values This may not be in the correct place, but I am getting desperate. I am trying to get the field @FileName using vbscript. The store procedure looks like this, and I have already tried putting @FILENAME OUTPUT below the declarations and I received an error. if exists (select * from sysobjects where id = object_id('dbo.wu_ProcessQueue') and sysstat & 0xf = 4) drop procedure dbo.wu_ProcessQueue GO CREATE PROCEDURE wu_ProcessQueue AS declare @FCN int declare @FileName varchar(8) declare @myCommand varchar(175) IF (SELECT count(*) from wu_Queue where FCN = 0) = 0 RETURN exec @FCN = wu_CreateNewFCN 'I' , @FILENAME OUTPUT select * from pfa.dbo.myTable select @FileName update wu_Queue SET FCN = @FCN where FCN = 0 GO GRANT EXECUTE ON dbo.wu_ProcessQueue TO myName GO I am using VBScript and this is what the ADO looks like: Dim myNewFilex Set objCmd = CreateObject("ADODB.command") Set objCmd.ActiveConnection = cnDB objCmd.CommandText = "pfa.dbo.myTable " objCmd.CommandType = 4 Set paraFileName = objCmd.CreateParameter("@FileName") paraFileName.Type = 200 paraFileName.Value = vbNULL paraFileName.Direction = 2 objCmd.Execute MsgBox myNewFilex myNewFilex = parafileName.Value MsgBox myNewFilex On MessageBox, all I am getting is an integer of 1 on both instances. Thanks in advance for any insight. Tag: Metadata about Foreign Keys ADO.NET 2.0 Tag: 101699
  - 17
    - Filtering a DataTable I'm trying to filter a DataTable... first I do a SELECT on my DataTable... and assign the result to a DataRow array... then I CLEAR the DataTable... and try to import the DataRows from the DataRow array... I end up with an empty DataTable... what am I doing wrong? And... is there a better way to do this? Tag: Metadata about Foreign Keys ADO.NET 2.0 Tag: 101696
  - 18
    - DataSet binding/updates If I base my DataSet on a schema (an .xsd file), and then bind the controls on my form to the necessary fields in a DataTable in the DataSet, how then would I use the SqlDataAdapter to put all this information into the database? Loading the DataSet from a schema is easier than basing it on a query since there are several tables involved. Tag: Metadata about Foreign Keys ADO.NET 2.0 Tag: 101694
  - 19
    - Datasets and merging Hi! I want to extract a single row from one table in a typed dataset (dsUser) containing multiple tables, and then send it on to a data access layer component for create/read/update/delete operations. I found two ways: 1. Create a standalone typed dataset (dsPerson) with the same structure as the one table I'm interested in, and use the Merge function to get the content of the datatable. dsPerson.Merge(dsUser.COR_Persons) 2. Extract the row by using the ItemArray property of the dsPerson.COR_Persons and dsUser.COR_Persons datatables respectively. Dim dsPerson As New COR_Persons Dim personRow As COR_Persons.COR_PersonsRow personRow = dsPerson.COR_Persons.NewCOR_PersonsRow personRow.ItemArray = dsUser.COR_Persons(0).ItemArray The first approach is the easiest implement, but I get a bulkier object to send on the data access layer. Does someone now anything about the performance differences for these two approaches? TIA Jonas Tag: Metadata about Foreign Keys ADO.NET 2.0 Tag: 101692
  - 20
    - ASP.NET vs VB.NET guys i have a very simple question concerning the differences between the two. for instance, radio buttons and the checkchanged event. it really doesnt seem to respond in asp.net if i set an if statemtent in that control, where if checked = true, then one thing happens, or if checked = false another thing happens. but, in VB.NET it responds perfectly. is there something extra i have to do. i am using the radio button as a server control. thanks guys. i know this is a pretty simple one for probably everyone..........but, it's kinda got me curious thanks rik ********************************************************************** Sent via Fuzzy Software @ http://www.fuzzysoftware.com/ Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources... Tag: Metadata about Foreign Keys ADO.NET 2.0 Tag: 101684
  - 21
    - Named tables/Sql Server/Can't do TableMappings I have a stored proc that returns me multiple tables. Is there a way I could set their names to be something other than TABLE TABLE1 TABLE2 ... ?? Sorry TableMappings won't do !! I can't use that for certain reasons. I have to be able to do something in the T-Sql that after SqlDataAdapter.Fill, my tables are named properly. DM Tag: Metadata about Foreign Keys ADO.NET 2.0 Tag: 101674
  - 22
    - Update to Visual FoxPro 6 Table is failing Everything I've read says the below code should update the foxpro table, but it doesn't. The dataview and datagrid are being updated, but the actual foxpro table isn't. The table has 3 fields ( idnum, fname and lname). The select statement gets the one matching record just fine. The record in the table has the number 2 in the idnum field, and I'm trying to change it to the number 90. I also have downloaded and installed the latest Microsoft VFPOLEDB provider from http://www.microsoft.com/downloads/details.aspx?familyid=0f43eb58-7a94-4ae1-a59e-965869cb3bc9&displaylang=en#filelist ***** code in button **** Dim ConnFoxTest As OleDbConnection = New OleDbConnection Dim SqlCmdFoxTest As OleDbCommand = New OleDbCommand Dim DaFoxTest As OleDbDataAdapter = New OleDbDataAdapter Dim DsFoxTest As DataSet = New DataSet Dim DvFoxTest As DataView = New DataView Dim idNum As Integer Dim StrSqlCmdFoxTest As String StrSqlCmdFoxTest = "Select * From FoxTest where fname='Jeff'" ConnFoxTest.ConnectionString = "Provider=VFPOLEDB.1;Data Source=c:\temp\foxtest.dbf;Persist Security Info=False" ConnFoxTest.Open() SqlCmdFoxTest.Connection = ConnFoxTest DaFoxTest.SelectCommand = SqlCmdFoxTest DaFoxTest.SelectCommand.CommandText = StrSqlCmdFoxTest DaFoxTest.Fill(DsFoxTest) DvFoxTest.Table = DsFoxTest.Tables.Item(0) idNum = DvFoxTest.Item(0).Item("idnum") ' Messagebox shows '2' MsgBox(Str(idNum)) DvFoxTest(0).BeginEdit() DvFoxTest(0)("idnum") = 90 DvFoxTest(0).EndEdit() DsFoxTest.AcceptChanges() DaFoxTest.Update(DsFoxTest) idNum = DvFoxTest.Item(0).Item("idnum") ' Messagebox shows '90' MsgBox(Str(idNum)) ' datagrid also shows '90' in the idnum field DataGrid1.SetDataBinding(DsFoxTest, DsFoxTest.Tables.Item(0).TableName) DvFoxTest.Dispose() DsFoxTest.Dispose() DaFoxTest.Dispose() SqlCmdFoxTest.Dispose() ConnFoxTest.Close() **** end code ******* Tag: Metadata about Foreign Keys ADO.NET 2.0 Tag: 101671
  - 23
    - Child rows Hi guys, I need to know how to create child rows for a newly created parent row. I've been doing it one way, but it's obviously not the right way to do it (finally starting to cause me some problems!). Here's what I've been doing ("ParentID" is the primary key): \\ Dim NewParent as DataRow = tblParentTable.NewRow '<Initialize NewParent values> tblParentTable.Rows.Add(NewParent) Dim NewChild as DataRow = tblChildTable.NewRow NewChild.Item("ParentID") = CInt(NewParent.Item("ParentID")) '<Initialize other NewChild values> tblChildTable.Rows.Add(NewChild) // What happens is, the dataset creates a "temporary" value for ParentID, and that value is assigned to the child rows. So the relationship works okay until the database is updated and then the tables refilled. On update, the database sometimes gives a different value for ParentID than the database had, but that value change is not carried through to the child rows. What is the proper way to do this? Thanks, Nathan Tag: Metadata about Foreign Keys ADO.NET 2.0 Tag: 101661
  - 24
    - How to INSERT pure <NULL> Into SmallDataTime Field (MS SQL Server) I need to insert NULL into the field with SmallDataTime datatype. In .NET when I tried to use "" or DBNull.Value it insert "" what will be automatically converted into '1/1/1900' by SQL Server... I need to have the Insert statement like this: INSERT tblTest (Name,smDate) Values ('John',NULL) But using pure NULL without double quotes ("") is not allowed in .NET. When I use double quotes ("NULL"), .NET interpreted it as character string and I got the SQL converting error. Thank you for the help. Eugene Tag: Metadata about Foreign Keys ADO.NET 2.0 Tag: 101659
  - 25
    - CommandBuilder InsertCommand Update Hi All!!! Seasons Greetings.... I have read a lot of the post regarding CommandBuilder, InsertCommand and Update. The code below works.... 'This is a foxpro table that matches my MS SQL Table fpDA = New OleDbDataAdapter("select * from table", GetConnection) flat = New System.Data.DataSet() fpDA.Fill(flat, "table") Try Dim cn As New Data.SqlClient.SqlConnection(ConnSqlClient) cn.Open() da = New Data.SqlClient.SqlDataAdapter("select * from table", cn) cb = New Data.SqlClient.SqlCommandBuilder(da) cb.QuotePrefix = "[" cb.QuoteSuffix = "]" da.InsertCommand = cb.GetInsertCommand() Dim p As Data.SqlClient.SqlParameter Dim row As Data.DataRow Dim col As Data.DataColumn For Each row In flat.Tables("table").Rows For Each p In da.InsertCommand.Parameters p.Value = row.Item(p.SourceColumn) Next affectedRows += da.InsertCommand.ExecuteNonQuery Next Return affectedRows Catch ex As Exception MsgBox(ex.Source & vbCrLf & ex.Message) End Try There should be more documentation for the "Update" command from Microsoft. It seems that if you just wanted to insert an unmodified table directly into SQL server using CommandBuilder is a no go. Once again...there should be documentation showing how to insert unmodified data (rowstate is unchanged) into an SQL server database with out getting a headache and eye strain. Markus McGee Tag: Metadata about Foreign Keys ADO.NET 2.0 Tag: 101653
- Next
  - 1
    - Data Access Application Block v3? Hi Guys, Have just downloaded v2 of the Data Access Application Block but see that v3 is available on the GotDotNet website. Does anyone know: if v3 is stable enough for use in real systems and when Microsoft are likely to release it officially. Cheers, Peter Tag: Metadata about Foreign Keys ADO.NET 2.0 Tag: 101644
  - 2
    - Different results with the same query?!? Hi all, I want to check if a record exists in the DB. I use two different ways to create the sql query. One way is by using SqlCommand and SqlParameter. The other way by building the query myself (without SqlParameter(s)) and using the SqlCommand to execute it. In one I get false in the other i get true. Can anyone help me? //////////////////////////////////////////////// //Build the query using SqlParameters //////////////////////////////////////////////// SqlCommand m_MessageSelectCommand = new SqlCommand(); m_MessageSelectCommand.CommandText = "SELECT MessageNumber FROM Messages WHERE CommunityNumber=@CommunityNumber and ForumParent=@ForumParent and ForumNumber=@ForumNumber and WrittenBy=@WrittenBy and MessageDate=@MessageDate"; m_MessageSelectCommand.Connection = m_Connection; m_MessageSelectCommand.CommandTimeout = 0; m_MessageSelectCommand.Parameters.Add(new SqlParameter("@CommunityNumber", System.Data.SqlDbType.Int, 4, "CommunityNumber")); m_MessageSelectCommand.Parameters.Add(new SqlParameter("@ForumNumber", System.Data.SqlDbType.Int, 4, "ForumNumber")); m_MessageSelectCommand.Parameters.Add(new SqlParameter("@ForumParent", System.Data.SqlDbType.Int, 4, "ForumParent")); m_MessageSelectCommand.Parameters.Add(new SqlParameter("@WrittenBy", System.Data.SqlDbType.NVarChar, 100, "WrittenBy")); m_MessageSelectCommand.Parameters.Add(new SqlParameter("@MessageDate", System.Data.SqlDbType.DateTime, 8, "MessageDate")); // later in the code m_MessageSelectCommand.Parameters["@CommunityNumber"].Value = dlMessage.m_CommunityNumber; m_MessageSelectCommand.Parameters["@ForumParent"].Value = dlMessage.m_ForumNumber; m_MessageSelectCommand.Parameters["@ForumNumber"].Value = dlMessage.m_TopicNumber; m_MessageSelectCommand.Parameters["@WrittenBy"].Value = dlMessage.m_MessageAuthor; m_MessageSelectCommand.Parameters["@MessageDate"].Value = dlMessage.m_MessageDate; SqlDataReader reader = m_MessageSelectCommand.ExecuteReader(); bool b = reader.HasRows; // B IS FALSE!!! //////////////////////////////////////////////// //The other way - just build the query by myself //////////////////////////////////////////////// System.Text.StringBuilder sql = new System.Text.StringBuilder(256); sql.Append("SELECT MessageNumber FROM Messages WHERE CommunityNumber="); sql.Append(dlMessage.m_CommunityNumber); sql.Append(" and ForumParent="); sql.Append(dlMessage.m_ForumNumber); sql.Append(" and ForumNumber="); sql.Append(dlMessage.m_TopicNumber); sql.Append(" and WrittenBy='"); sql.Append(dlMessage.m_MessageAuthor); sql.Append("' and MessageDate='"); sql.Append(dlMessage.m_MessageDate); sql.Append("'"); SqlCommand c = new SqlCommand(sql.ToString(), m_Connection); SqlDataReader reader = c.ExecuteReader(); bool b = reader.HasRows; // I get that b is true. This happens durring the same program execution How can this be??? Tag: Metadata about Foreign Keys ADO.NET 2.0 Tag: 101642
  - 3
    - Different results with the same query?!? Hi all, I want to check if a record exists in the DB. I use two different ways to create the sql query. One way is by using SqlCommand and SqlParameter. The other way by building the query myself (without SqlParameter(s)) and using the SqlCommand to execute it. In one I get false in the other i get true. Can anyone help me? //////////////////////////////////////////////// //Build the query using SqlParameters //////////////////////////////////////////////// SqlCommand m_MessageSelectCommand = new SqlCommand(); m_MessageSelectCommand.CommandText = "SELECT MessageNumber FROM Messages WHERE CommunityNumber=@CommunityNumber and ForumParent=@ForumParent and ForumNumber=@ForumNumber and WrittenBy=@WrittenBy and MessageDate=@MessageDate"; m_MessageSelectCommand.Connection = m_Connection; m_MessageSelectCommand.CommandTimeout = 0; m_MessageSelectCommand.Parameters.Add(new SqlParameter("@CommunityNumber", System.Data.SqlDbType.Int, 4, "CommunityNumber")); m_MessageSelectCommand.Parameters.Add(new SqlParameter("@ForumNumber", System.Data.SqlDbType.Int, 4, "ForumNumber")); m_MessageSelectCommand.Parameters.Add(new SqlParameter("@ForumParent", System.Data.SqlDbType.Int, 4, "ForumParent")); m_MessageSelectCommand.Parameters.Add(new SqlParameter("@WrittenBy", System.Data.SqlDbType.NVarChar, 100, "WrittenBy")); m_MessageSelectCommand.Parameters.Add(new SqlParameter("@MessageDate", System.Data.SqlDbType.DateTime, 8, "MessageDate")); // later in the code m_MessageSelectCommand.Parameters["@CommunityNumber"].Value = dlMessage.m_CommunityNumber; m_MessageSelectCommand.Parameters["@ForumParent"].Value = dlMessage.m_ForumNumber; m_MessageSelectCommand.Parameters["@ForumNumber"].Value = dlMessage.m_TopicNumber; m_MessageSelectCommand.Parameters["@WrittenBy"].Value = dlMessage.m_MessageAuthor; m_MessageSelectCommand.Parameters["@MessageDate"].Value = dlMessage.m_MessageDate; SqlDataReader reader = m_MessageSelectCommand.ExecuteReader(); bool b = reader.HasRows; // B IS FALSE!!! //////////////////////////////////////////////// //The other way - just build the query by myself //////////////////////////////////////////////// System.Text.StringBuilder sql = new System.Text.StringBuilder(256); sql.Append("SELECT MessageNumber FROM Messages WHERE CommunityNumber="); sql.Append(dlMessage.m_CommunityNumber); sql.Append(" and ForumParent="); sql.Append(dlMessage.m_ForumNumber); sql.Append(" and ForumNumber="); sql.Append(dlMessage.m_TopicNumber); sql.Append(" and WrittenBy='"); sql.Append(dlMessage.m_MessageAuthor); sql.Append("' and MessageDate='"); sql.Append(dlMessage.m_MessageDate); sql.Append("'"); SqlCommand c = new SqlCommand(sql.ToString(), m_Connection); SqlDataReader reader = c.ExecuteReader(); bool b = reader.HasRows; // I get that b is true. This happens durring the same program execution How can this be??? Tag: Metadata about Foreign Keys ADO.NET 2.0 Tag: 101641
  - 4
    - Question about relationships - "cycles or multiple cascade paths" error I have 2 tables: Table: UserTable Field: Id (pk) Field: FullName Table: MarriageTable Field: MarriageId (pk) Field: Husband Field: Wife The MarriageTable.Husband and MarriageTable.Wife fields BOTH have to be valid users from the UserTable. In SQL Server I'm creating a relationship diagram. I (1) dragged Id from UserTable to MarriageTable.Husband and (2) dragged Id from UserTable to MarriageTable.Wife. But, the Diagram has trouble saving the Wife relationship. Any ideas? Am I doing this correctly? Here's the error: 'UserTable' table saved successfully 'MarriageTable' table - Unable to create relationship 'FK_MarriageTable_UserTable1'. ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Introducing FOREIGN KEY constraint 'FK_MarriageTable_UserTable1' on table 'MarriageTable' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. [Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint. See previous errors. Thanks. Tag: Metadata about Foreign Keys ADO.NET 2.0 Tag: 101640
  - 5
    - VB.NET: How to apply a DataView Can somebody please help. I'm coming from VB6 and this is my first VB.NET applciation. Below is my code that fills two tables contained in a DataSet, creates a relation between them, and finally, uses them as DataSources for two grid controls on the form. This code works prefectly fine. Now, my question is how do I apply a DataView in order to sort and filter my data without destroying my relation? Ever snippet of code I've looked at involves setting up a DataView and then setting it to the DataSource of my DataGrid. This, however, destroys my relation. Thanks for any help. m_Ds = New DataSet m_Da1 = New OleDbDataAdapter("SELECT * FROM Groups", Conn) m_Da2 = New OleDbDataAdapter("SELECT * FROM Clients", Conn) m_Da1.FillSchema(m_Ds, SchemaType.Source, "Groups") m_DgGroupsCount = m_Da1.Fill(m_Ds, "Groups") m_Da2.FillSchema(m_Ds, SchemaType.Source, "Clients") m_Da2.Fill(m_Ds, "Clients") 'Create a relation and add it to the collection. Dim rel1 As New DataRelation("LinkToClients", _ m_Ds.Tables("Groups").Columns("GroupID"), _ m_Ds.Tables("Clients").Columns("GroupID")) m_Ds.Relations.Add(rel1) 'Master/detail setup. Me.dgGroups.DataSource = m_Ds Me.dgGroups.DataMember = "Groups" Me.dgGroups.AllowNavigation = False Me.dgClients.DataSource = m_Ds Me.dgClients.DataMember = "Groups.LinkToClients" Tag: Metadata about Foreign Keys ADO.NET 2.0 Tag: 101638
  - 6
    - comparing two lists hey guys, i need some help on an approach. i need to compare, two different lists. one will be pulled from a dataset and could have as much as 2500 varchar employee numbers in it. the other will have the same data, but might be missing some of those same numbers. what's the best way to compare the two lists, or group or array and find who's missing from the second list? thanks rik ********************************************************************** Sent via Fuzzy Software @ http://www.fuzzysoftware.com/ Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources... Tag: Metadata about Foreign Keys ADO.NET 2.0 Tag: 101635
  - 7
    - Validating data. Hi everybody, this is my first post in this group. I'm working with an Access DB and i have the following code to validate fields with unique indexes (no duplicates): 'Field is a DataColumn If Field.Unique Then If Field.Table.Rows.Contains(NewData) Then Return "Error: This field can't have duplicate values." End If End If The problem that i found is that apparently the property 'Unique' works only for Primary Keys fields. So, how do i make a similar validation but with fields with unique indexes only (no Primary Key)? The other doubt that i have is that the property 'DataColumn.DefaultValue' doesn't set itself to anything when i get the values from a field wich has a default value. Note: I fill a DataSet with Fill and FillSchema methods in order to do the tests. Thanks. Tag: Metadata about Foreign Keys ADO.NET 2.0 Tag: 101633
  - 8
    - OT: Database Design I am trying to make sure I'm using best practice for an Access table. If I have a table that has 100 discrete colums. Is it best to just keep the table with so many columns or to split it into a few other tables. The 100 columns are indeed discrete so splitting them isn't going to reduce any duplication of data. Tag: Metadata about Foreign Keys ADO.NET 2.0 Tag: 101622
  - 9
    - how to merge 5 tables into one table I have a dataset that reads from an xml file. When the file was read, 5 tables where created.having the relationship between the table. how can i merge those five tables into one table or better yet how can i make that table relational? thanks Tag: Metadata about Foreign Keys ADO.NET 2.0 Tag: 101620
  - 10
    - bound textbox text change won't save to dataset BlankHi, I select a value in a bound combobox on tab 1, then I programically assign related text to a bound textbox's text property on tab 2. I see the text in the textbox when I view tab 2 and the textchanged event fired. However, after I save the changes, close the form, reopen the form, the previous data appears in the textbox, not the new data. Here's the code from the combobox's selectedIndexChanged event: With txtSignalOffset .Focus() .Text = "10" End With CType(sender, ComboBox).Focus() CType(cmTerminal.Current, DataRowView).EndEdit() Both the combobox and textbox are bound to the same datatable. If I type data directly into the textbox there is no problem. However I want the textbox to be read only and assigned a value by code only. I tried editing the CType(cmTerminal.Current, DataRowView) datarow directly but then got screwy behavior from the combobox such that it wouldn't change its selected value - it kept bouncing back to the original value. Same thing happened when I instead tried directly editing the datatable. This is driving me crazy and wasting a lot of time. Thanks for any insights you may have, NJ Tag: Metadata about Foreign Keys ADO.NET 2.0 Tag: 101615
  - 11
    - return value from stored procedure using SqlHelper.UpdateDataset Hi, I'm trying to update the Database (sql2000) using the DAAB v2.0 SqlHelper.UpdateDataset method. The problem is that my insert stored procedure returns the primary keys of my new inserted rows as a returned values parameters. But with the above method there seems to be no way to get back those parameteres. What woul be the best way to overcome this problem? Thanks, Gwenda Tag: Metadata about Foreign Keys ADO.NET 2.0 Tag: 101613
  - 12
    - UPDATING database using datagrid : error on UPDATE button click I am using a datagrid to write data to the database. I am using the walkthrough by msdn "Using a datagrid to Read & write data to Database". But when i hit the update button I get the following error: : Object reference not set to an instance of an object. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.NullReferenceException: Object reference not set to an instance of an object. Source Error: Line 348: Line 349: // Updates the dataset table. Line 350: r.CompanyName = track; Line 351: r.Region =esn; Line 352: Source File: c:\inetpub\wwwroot\demo\boflog\viewrec.aspx.cs Line: 350 Stack Trace: [NullReferenceException: Object reference not set to an instance of an object.] BofLog.ViewRec.DataGrid1_UpdateCommand_1(Object source, DataGridCommandEventArgs e) in c:\inetpub\wwwroot\demo\boflog\viewrec.aspx.cs:350 System.Web.UI.WebControls.DataGrid.OnUpdateCommand(DataGridCommandEventArgs e) +109 System.Web.UI.WebControls.DataGrid.OnBubbleEvent(Object source, EventArgs e) +507 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +26 System.Web.UI.WebControls.DataGridItem.OnBubbleEvent(Object source, EventArgs e) +100 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +26 System.Web.UI.WebControls.LinkButton.OnCommand(CommandEventArgs e) +120 System.Web.UI.WebControls.LinkButton.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) +138 System.Web.UI.Page.ProcessRequestMain() +1292 -------------------------------------------------------------------------------- Version Information: Microsoft .NET Framework Version:1.1.4322.2032; ASP.NET Version:1.1.4322.2032 MY CODE for UPDATE button click is:: private void DataGrid1_UpdateCommand_1(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e) { string track, esn; // Gets the value of the key field of the row being updated string key = DataGrid1.DataKeys[e.Item.ItemIndex].ToString(); // Gets get the value of the controls (textboxes) that the user // updated. The DataGrid columns are exposed as the Cells collection. // Each cell has a collection of controls. In this case, there is only one // control in each cell -- a TextBox control. To get its value, // you copy the TextBox to a local instance (which requires casting) // and extract its Text property. // // The first column -- Cells(0) -- contains the Update and Cancel buttons. TextBox tb; // Gets the value the TextBox control in the third column tb = (TextBox)(e.Item.Cells[1].Controls[0]); track = tb.Text; // Gets the value the TextBox control in the fourth column tb = (TextBox)(e.Item.Cells[3].Controls[0]); esn = tb.Text; // Finds the row in the dataset table that matches the // one the user updated in the grid. This example uses a // special Find method defined for the typed dataset, which // returns a reference to the row. dsCompany.FormFieldsRow r; r = dsCompany1.FormFields.FindBy_AsiOrder_(key); // Updates the dataset table. r.CompanyName = track; r.Region =esn; // Calls a SQL statement to update the database from the dataset sqlDataAdapter1.Update(dsCompany1); // Takes the DataGrid row out of editing mode DataGrid1.EditItemIndex = -1; // Refreshes the grid DataGrid1.DataBind(); } I think the problem is bcoz " key" is a string & r maybe is an int. But when I tried doing r = dsCompany1.FormFields.FindBy_AsiOrder_(INT.PARSE(key)); it showed error! Please help. thanks -- pmud Tag: Metadata about Foreign Keys ADO.NET 2.0 Tag: 101612
  - 13
    - Read Description of Field Any way in ADO to get to the Description of the field - for example, in Access you can provide a logical description for each field in the table. Thanks, Scott Tag: Metadata about Foreign Keys ADO.NET 2.0 Tag: 101611
  - 14
    - INSERT into Access Database using C# from ASP page The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data. Is the error message I get when it gets to dbCmd.ExecuteNonQuery();. I have 15 fields to insert, therefore I did one field at the time and found that the 13th field is the one causing the problem, this field has size 20 and data I am entering has size is 1. I took the INSERT command and entered it from Access Query and it worked, but not from ASP page using C#. Please help I thank you for your assistance. I found the similar error in http://www.error-bank.com/microsoft.public.dotnet.languages.vb.1/196223_Thread.aspx. But there were no solutions posted. Tag: Metadata about Foreign Keys ADO.NET 2.0 Tag: 101606
  - 15
    - Recordsets and Ref Cursors I am passing an ADODB.Recordset (rs) into a .Net C# dll. I need to fill a dataset using Oracle.DataAccess.Client.DataAdaptor. OracleDataAdapter da = new OracleDataAdapter(); DataSet ds = new DataSet(); DataTable dt = new DataTable(); da.Fill(dt, rs); This does not work. I need to use: da.Fill(dt, ref cursor); How do I convert the recordset (rs) to a ref cursor? Thanks.... -- Robert Hill Senior Programmer/Analyst Wake Forest Univ Baptist Med Ctr Tag: Metadata about Foreign Keys ADO.NET 2.0 Tag: 101595
  - 16
    - Database Insert with IDENTITY ON I have a situation where I have two datasets each equal with the data from the same table. I want to update one dataset with the records from the next dataset IDs in tact. Here's the code I have: ' Set AcceptChangesDuringFill = False objDataAdapterSource.AcceptChangesDuringFill = False ' Fill the data adapter with some data from the source "myTable" objDataAdapterSource.Fill(objDataSetSource, "myTable") ' This is a function I wrote that just calls the "SET ' IDENTITY_INSERT myTableON" SetIdentityInsert(True, "myTable") ' Select everything from myTable on the database toupdate and ' create a data adapter strSQL = "SELECT * FROM myTable" objDataAdapterDest= New SqlClient.SqlDataAdapter(strSQL, strConn) ' create a commandbuilder object objCB = New SqlClient.SqlCommandBuilder(objDataAdapterDest)' 'Update the destination data set objDataAdapterDest.Update(objDataSetSource, "myTable") Let's say the Source object objDataSetSource had a "myTable" with two columns" "TableID" and "Description" where "TableID" was the key. Let's say it had one row, where the TableID = 1 and Description = "TEST". I am expecting the same table in the destination database to be inserted with the exact same data. Instead, the TableID ends up being generated by the database as if Identity_Insert isn't turned on. Any suggestions? Tag: Metadata about Foreign Keys ADO.NET 2.0 Tag: 101586
  - 17
    - Adding Columns to DataSet-DataTable out of Position Is it possible to add a column to an existing DataTable (in a DataSet) with the new column being some place other than the end of the column collection? I looked at Columns.Add and there is no position parameter and there is no InsertAt method like some other collections. Is this possible through some other means? Maybe create a new Table and copy data??? Thanks, Ashton Tag: Metadata about Foreign Keys ADO.NET 2.0 Tag: 101580
  - 18
    - PRIMARY KEY required for WRITING data to SQL DATABASE using DATA G I am posting this question here too coz I was not sure whether I had posted this earlier in the right group. I need an editable datagrid. But for editing a datagrid as shown in the walkthrough: Using a DataGrid Web Control to Read and Write Data, the table in the SQL database should have a primary key. But my table doesnt have one. So what is the best way I can create primary key in SQL database? Is UNIQUE IDENTIFIER a good option? Is it something like Auro Number of MS Access which is automatically filled by the database for each row? If not , how can i make a primary key for that SQL table? -- pmud Tag: Metadata about Foreign Keys ADO.NET 2.0 Tag: 101578
  - 19
    - MEMBER_UNIQUE_NAME property NULL Hi, I use OleDb (.NET) to execute MDX queries. An simple example query: WITH MEMBER [Promotion Media].[CalcMember] AS '[Promotion Media].[All Media].[Bulk Mail]'SELECT {[Measures].[Unit Sales]} ON COLUMNS,{[Promotion Media].[All Media].[Bulk Mail], [Promotion Media].[CalcMember]} PROPERTIES MEMBER_UNIQUE_NAME ON ROWS FROM [Sales] When I use OleDb to execute this query and populate a DataTable object, the MEMBER_UNIQUE_NAME column value for the [CalcMember] row comes up NULL instead of "[Promotion Media].[CalcMember]". [Promotion Media].[Media Type].[Member_Unique_Name] [Measures].[Unit Sales] ========================================= ================ [Promotion Media].[All Media].[Bulk Mail] 4320 (null) 4320 Can anyone tell me why that is and how to fix it? Thanks in advance. Tag: Metadata about Foreign Keys ADO.NET 2.0 Tag: 101572
  - 20
    - How to kow the Lenght of the Text in the Current DataGridTextBoxColumn (on MouseMove) Hi, I need to know the Lenght of the Text in a DataGridTextBoxColumn when I move with the mouse over it, and show it in a ToolTip. I have something that works, but it works only when the DataGridextBoxColumn is not selected. Once I select it (type some additional text in it), I don't see the ToolTip anymore... I gues that's because it isn't anymore the DataGrid that the Mouse is moving over, but the Cell. Does anybody knows a solution for this? Thanks a lot in advance, Pieter You can see my DataGrid_MouseMove here: Private Sub dbgGrid_MouseMove(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles dbgGrid.MouseMove Dim p1 As New Point p1 = dbgGrid.PointToClient(Cursor.Position) Dim intCol As Integer = dbgGrid.HitTest(p1).Column Dim intRow As Integer = dbgGrid.HitTest(p1).Row If (intCol = 3) And (intRow > -1) Then Dim strT As String Try strT = dbgGrid.Item(intRow, intCol).ToString Catch ex As Exception strT = "" End Try strT = strT & vbCrLf & "Length: " & strT.Length totImport.SetToolTip(dbgGrid, strT) totImport.Active = True Else totImport.SetToolTip(dbgGrid, "") totImport.Active = False End If End Sub Tag: Metadata about Foreign Keys ADO.NET 2.0 Tag: 101569
  - 21
    - How to fill multiple datatables in a dataset in a single fill Anyone who knows how to fill more than one datatable in a single fill using a dataadapter? I have 4 joined tables in a select-statement that I want to fill into their datatables. Now I have to make 4 selects to do that, and that's no good. Alternatively make a datatable representing all 4 tables alltogether. Tag: Metadata about Foreign Keys ADO.NET 2.0 Tag: 101568
  - 22
    - Identifying stored procedure output columns In VS.Net Server Explorer, you can drill down to a SQL Server store procedure and finally, it diplays not only parameter of the procedure but also the result columns. How can I replicate this behaviour in my application? Tag: Metadata about Foreign Keys ADO.NET 2.0 Tag: 101558
  - 23
    - Getting RowCounts for Multiple Statements I am trying to execute several DML statements in a single call to an ADO.NET SqlCommand object, ie. update table1 set column1 = x update table2 set column2 = y I am wanting to get the rows affected for each update statement, ie. 10 rows for table1, 20 rows for table2, but the RowsAffected only gives me the total for the statement. Is there any way to get the records affected broken out by statement. I tried capturing the InfoMessages from the SqlConnection and turning set nocount off, but it doesn't provide any messages. Any ideas? Thanks, Ashton Hobbs Tag: Metadata about Foreign Keys ADO.NET 2.0 Tag: 101553
  - 24
    - DataReader and record locking problem Hi, I am using the ExecuteReader method from the Microsoft DAAB to return a DataReader which I am iterating through to mainly read a binary value from one of the returned columns. I am using this value as the raw data to render a PDF document. After this is successful, I want to update the same record with a timestamp to indicate the render to PDF was successful. However,using a totally separate method in my code (using ExecuteNonQuery of the DAAB) to do this whilst the reader is still open (using the pk of the row I want to update), throws an exception - the call times out and if I look at the processes in the database, the update of the timestamp is being locked by the datareader process. My code is the only code accessing this table and the column I am updating is not one of the ones returned by the datareader. So now on to the questions: 1) Should I theoretically be able to read a row with a datareader and update a column in the same row (using a separate connection/call) before moving on to the next record? 2) If I cannot do this, what is the best and most efficient practice to perform this kind of operation (I may have to do this for up to 30,000 rows)? Do I get a list of all the rows I want to operate on (in an array or datatable) and then iterate through each one, pulling the relevant data back for one row at a time in a disconnected fashion for each row and then perform the update or should i just pull back all the records in a disconnected fashion (this seems like it would work when there are a small amount of rows but then if I have 30,000, that's going to be one very big DataSet/DataTable? Any help would be greatly appreciated Thanks in advance Mike Tag: Metadata about Foreign Keys ADO.NET 2.0 Tag: 101548
  - 25
    - Connection pooling... I'm using connection pooling (0 min/100 max) in an asp.net web application. All users log in using the same connectionstring. Is it prudent, for instance, to close and dispose of my connection after a database update takes place which I normally do when I'm not using connection pooling? Tag: Metadata about Foreign Keys ADO.NET 2.0 Tag: 101534

Hi,

I can use "...Connection.GetSchema("ForeignKeys")" to get the name of an
foreign key defined for a table.

But how to get related/additional infos like the referenced table(name) and
the corresponding fields ?

Thx
Robert Holler

Top

Re: Metadata about Foreign Keys ADO.NET 2.0 by Sahil

Sahil
Sun Jan 02 15:54:58 CST 2005

You'll need to query the master db.

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik

"rob" <rob@discussions.microsoft.com> wrote in message
news:0A755805-8F04-4F19-9046-DD9994B75295@microsoft.com...
> Hi,
>
> I can use "...Connection.GetSchema("ForeignKeys")" to get the name of an
> foreign key defined for a table.
>
> But how to get related/additional infos like the referenced table(name)
> and
> the corresponding fields ?
>
> Thx
> Robert Holler

Top