read datareader stream and read output parameters at the same time

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
    - Cascading issues while updating multiple tables with SqlDataAdapter.Update method All, I broke my head over this for 18 hours now and tried out all the suggestions posted here but of no use. Here is what I'm trying to accomplish I have two tables, 1) Criteria 2) Regulator Criteria is a master table and Regulator is a child table. ie, the primary key of Criteria table (CriteriaID, identity column with autoincrement set to 1) is a foreign key in the Regulator table. pretty classic stuff. I wrote a console app in C# to update these two tables using SqlDataAdapter.Update method. The only slight difference from other postings is that I get the XML data file from an external application. I have to import the data from that XML file into these two tables. I tried to follow David Sceppa's suggestions but No matter what I do, the identity value I receive from the Criteria table DOES NOT GET CASCADED to the Regulator table. As a result records are not inserted into the child (Regulator) table. Here is my code Any thoughts would be much appreciated { ...... SelectQuery = "select * from Criteria"; SqlDataAdapter CriteriaDataAdapter = new SqlDataAdapter(); SqlConnection CriteriaSQLConnection = new SqlConnection(ConnectionString); SqlCommand CriteriaSQLSelectCommand = new SqlCommand(SelectQuery, CriteriaSQLConnection); CriteriaDataAdapter.SelectCommand = CriteriaSQLSelectCommand; cmdGetIdentity = new SqlCommand("SELECT @@IDENTITY as CriteriaID", CriteriaDataAdapter.SelectCommand.Connection); CriteriaDataAdapter.RowUpdated +=new SqlRowUpdatedEventHandler(CriteriaDataAdapter_RowUpdated); SqlCommandBuilder Criteriacmdb = new SqlCommandBuilder(CriteriaDataAdapter); SelectQuery = "select * from Regulator"; SqlDataAdapter RegulatorDataAdapter = new SqlDataAdapter(); //attempted to use the same connection object..didn't help //SqlConnection RegulatorSQLConnection = new SqlConnection(ConnectionString); //SqlCommand RegulatorSQLSelectCommand = new SqlCommand(SelectQuery, RegulatorSQLConnection); SqlCommand RegulatorSQLSelectCommand = new SqlCommand(SelectQuery, CriteriaSQLConnection); RegulatorDataAdapter.SelectCommand = RegulatorSQLSelectCommand; SqlCommandBuilder Regulatorcmdb = new SqlCommandBuilder(RegulatorDataAdapter); DataSet NewDataSet = new DataSet(); NewDataSet.ReadXmlSchema("PostApprovalsReport.xsd"); DataColumn NewColumn = NewDataSet.Tables["Criteria"].Columns.Add("CriteriaID", typeof(Int32)); NewColumn.Unique = true; NewColumn.AutoIncrement = true; NewColumn.AutoIncrementSeed = -1; NewColumn.AutoIncrementStep = -1; DataColumn NewFColumn = NewDataSet.Tables["Regulator"].Columns.Add("CriteriaID", typeof(Int32)); NewDataSet.Relations.Add("CriReg", NewDataSet.Tables["Criteria"].Columns["CriteriaID"], NewDataSet.Tables["Regulator"].Columns["CriteriaID"]); Reading the data from a xml file that came from another application NewDataSet.ReadXml("5393_PostApprovalsReport_2004-12-01.xml"); CriteriaDataAdapter.Update(NewDataSet, "Criteria"); RegulatorDataAdapter.Update(NewDataSet, "Regulator"); } private void CriteriaDataAdapter_RowUpdated(object sender, SqlRowUpdatedEventArgs e) { if ((e.Status == UpdateStatus.Continue) && ((e.StatementType == StatementType.Insert))) { Console.WriteLine (cmdGetIdentity.ExecuteScalar().ToString()); e.Row["CriteriaID"] = Int32.Parse(cmdGetIdentity.ExecuteScalar().ToString()); e.Row.AcceptChanges(); Console.WriteLine (e.Row["CriteriaID"]); } } Tag: read datareader stream and read output parameters at the same time Tag: 101858
  - 2
    - Simple task, can't do in ADO.NET Switching from ADO to ADO.net and I am totally lost and and frustrated. What I'm trying to do is so dirt simple: At RUNTIME, load an arbitrary database table into a datagrid for editing. Simple: create a SqlDataAdapter, dynamically constructing the SQL command string based on the the name of the table supplied at runtime; create a CommandBuilder; call Fill, supplying a new blank DataSet; call Update when complete. So far, so good. The table that I'm editing has lookups that the user needs to be able to see as they are editing. However, there's not updatable view concept in ADO.NET. If I do a JOIN to any lookup tables, CommandBuilder flatlines and can't generate the necessary update statements. Is the only alternative here to generate the update statement by hand? That just seems incredibly tedious and unnecessary. Another weird thing is that it's a mystery as to how CommandBuilder works. If you trace through the code you'll see that it doesn't actually modify the UpdateCommand property in the DataAdapter. Somehow it's doing it's magic in the background. This prevented me from kludging up update statements from a non-lookup version of the SelectCommand and using that in the lookup version. Another problem is now to specify a primary key for the DataSet. Supposedly the SqlDataAdapter uses a DataReader in the background to generate schema for the DataSet. But the IsKey fields are all null. So somewhere between there and the DataSet it somehow decides what the primary key is, but it's not accessible to the programmer. I guess what I'm wonder is can I specify a primary key directly to the DataSet after it has been filled? Tag: read datareader stream and read output parameters at the same time Tag: 101841
  - 3
    - Is TDS used only if server is local? In order to use TDS via ADO.NET, does the SQL Server need to be on the same machine? If not, this will have to involve TCP/IP or named pipes correct? How does TDS help in that case? Thanks, Brett Tag: read datareader stream and read output parameters at the same time Tag: 101840
  - 4
    - Testing for the existence of a table All: Is there some standard way I can test for the existence of a database table? I am using ODBC with an ingres database; and I don't really want to open a query like "select * ...." because I may end up reading the whole thing in, when all I want to know is if the table exists. I'm just wondering if there is a standard way in ado with ODBC. TIA ... Greg Shannan Tag: read datareader stream and read output parameters at the same time Tag: 101835
  - 5
    - how to create a master detail display on the datagrid When I read an xml file, the read data will be converted into different tables with the relationship. But how can I display the data into a relational table like the data grid? Tag: read datareader stream and read output parameters at the same time Tag: 101834
  - 6
    - "Method '~' of object '~' failed" error Hi, I'm using ADO.NET command to execute a stored procedure which will do a bounch of deletes and inserts on database side. If the data is not too big, it's fine, but if there is a lot of data needs to be proceeded, sometime I got error of "Method '~' of object '~' failed", sometime I got "Import of MSDTC transaction failed: Result Code = 0x8004d00e." as well. When I ran the stored procedure manually with same parameters, no any problems. What could be the problem? Thanks. william Tag: read datareader stream and read output parameters at the same time Tag: 101832
  - 7
    - system.Data.OracleClient.dll 1.0.5 and TNSNAME longer than 16 char There is a known issue with system.Data.OracleClient.dll 1.0.50000.0 that came with NET 1.1 framework: It cannot handle TNSNAME entries that are longer than 16 chars and produce the error below: ORA-00162: external dbid length 17 is greater than maximum (16) Is there a work around or bug fix for this problem? Thanks Tag: read datareader stream and read output parameters at the same time Tag: 101828
  - 8
    - non-labor intensive solution to Nulls in legacy DB I don't want to enter the religious wars about NULLS and whether they should or should not be allowed in Databases. I have a legacy database and I can't change it. NULLS are a fact of life for me. My question is whether anyone has a less labor intensive solution. In the auto-generated class definition code for the datarow of eacch table is code like the following... Public Property Birthday As Date Get Try Return CType(Me(Me.tableo_Person.BirthdayColumn),Date) Catch e As InvalidCastException Throw New StrongTypingException("Cannot get value because it is DBNull.", e) End Try End Get Set Me(Me.tableo_Person.BirthdayColumn) = value End Set End Property I have been modifying this code to... Public Property Birthday As Date Get Try Return CType(Me(Me.tableo_Person.BirthdayColumn),Date) Catch e As InvalidCastException return nothing End Try End Get Set if value = nothing then Me(Me.tableo_Person.BirthdayColumn) = system.convert.dbnull else Me(Me.tableo_Person.BirthdayColumn) = value endif End Set End Property This code gets generated anytime I have to redrag a datatable into a dataset. Then I have to go thru each field and modify as above again. Is there a smarter way to do this? (I'm normally a foxpro programmer, but this one is going against a SQL server database, if that has any bearing on this question) -- T Ralya, new .NET developer, HMSA Tag: read datareader stream and read output parameters at the same time Tag: 101825
  - 9
    - Re: ADO.net Schem... Hi all gurus, I have question to clarify.. If get back a dataset. Will i get back the schema information and user rigths on the objects? Thanks Srree Tag: read datareader stream and read output parameters at the same time Tag: 101821
  - 10
    - Checking to see if SQL Service is running I have users who, as soon as they boot into XP, immediately try to run my app without waiting for all of the services to start. My app uses MSDE. Is there something I can do in code to check to see if the service is running to prevent them from running the app and therefore seeing any kind of errors? Thanks. Norton Tag: read datareader stream and read output parameters at the same time Tag: 101816
  - 11
    - Slow reads over VPN Have an Access db. When multiple user hit it on the LAN, performance is fine (response time <= 1 sec). Application is in C# using ADO.NET. When a remote user tries to access the db (via a VPN), their response time is fine also. As soon as a user on the LAN access the db, response time goes into multi second (almost a minute). All of these accesses are reads with through a dataset (not datareader). Using a packet sniffer, we find that the file system (client is XP Pro - db is on a W2K Server) is placing/clearing many lock requests on the .mdb file. Any thoughts? TIA Don Tag: read datareader stream and read output parameters at the same time Tag: 101813
  - 12
    - Still stuck on DataRelation names Hi, I've created an xml instance document named "test.xml" and from that created an associated schema document named "test.xsd". I have used the schema designer to add three primary keys and two relations. I have named the relations "RelationDocumentItems" and "RelationItemsItem". When I create a DataSet in code using ReadXml("test.xml") and then read the names of the relations in DataSet.Relations I get "TestDocument_TestItems" and "TestItems_TestItem" rather than the expected "RelationDocumentItems" and "RelationItemsItem" ( my expectation). Can someone please help me understand where my names went? Thanks Jeff Higgins Tag: read datareader stream and read output parameters at the same time Tag: 101811
  - 13
    - Syncronizing Read/Writes Using OleDB There is an issue with sync-ing read/writes from different connections when using the OleDB provider for ADO. The solution that Microsoft gives for it works using the Jet Replication Object to refresh the cache. This is all good and works fine. However, the same issue exists in ADO.NET. What is the correct method of refreshing the cache in ADO.NET, as an ADODB.Connection does not exist to pass to the JRO ActiveX DLL? Not to mention that I would like a managed method to handle this instead of using COM Interop. Thanks -- David Williams Tag: read datareader stream and read output parameters at the same time Tag: 101810
  - 14
    - Several Questions on ADO.NET Hi,there I get some problems when I am struggling in ADO.NET programming. I should really preciate your kindly advice. 1.How could I perform a data paging on master-detail datasets,especially the detail one? As I thought, I retrieve the master data and detail data seperately and then associate them through dataRelation. So it seems that I can paging the master data readily but how about the detail data? 2.Suppose I use DataSet only in my asp.net application. How could I re-position to the row after I insert a new row into the dataset? 3.I am editing a datarow. When I modify the value in a column, I hope value in another column should change correspondingly ASAP. For instance, the "Cost" column should change with "Price" column and "Quantity" column correspondingly. In what event should I place the code? Thanks. Tag: read datareader stream and read output parameters at the same time Tag: 101797
  - 15
    - Using ADO.NET DataSet in Unmanaged C++ COM Code Hello, I am accessing the database using ADO.NET/Managed C++. I would like to use the returned data in form of cursors in Unmanaged C++ (standard C++ with COM). Through COM Callable Wrappers & Interop the Stored Procs will be called. The in-out data volume handled through this module will be large, so the high performance is required. Is there any standard way to write marshaling to use ADO.NET DataSets in COM/C++ world? How to leverage XML? Thanks. -AJ Tag: read datareader stream and read output parameters at the same time Tag: 101786
  - 16
    - where can I download the ADO.Net object? Dear All, I am newbies in .Net. I would like to ask do i need to download the ADO.Net objects for the database connection? just like need to download the ODBC driver for Java? If yes, where can I find the ADO.Net object? Thank you very much. Victor Tag: read datareader stream and read output parameters at the same time Tag: 101785
  - 17
    - Left join, cross-joined view, running forever Hi All, I am using VB.Net on a Pervasive.SQL database. I am trying to retrieve all Customers and Items, and only the related Sales for these Customers/Items. Because there isn't a link between the customer and items, I've created a cross joined view of the customers and items, which returns all the customers and items. I'm trying to left join specific customer and items to Sales for a specific period; however this query is taking forever to return my recordset. Any ideas, for a simpler and/or more efficient solution. Regards, Viren Tag: read datareader stream and read output parameters at the same time Tag: 101784
  - 18
    - Problem with running a SP that has input & a output parameter Hi folks I have a SQL Server SP that is eventually called from a command button on a form. The SP has one input parameter and one output parameter. The problem is that when I call the SP from the command button click event and assign it to a variable, it returns the value "Nothing". The SP code is as follows ====================================== CREATE PROCEDURE [DBO].[sp_GetStoredPwd] @UserName varchar(15), @Result char(10) OUTPUT AS DECLARE @StoredPwd char(10) PRINT 'Msg from DB - @UserName = ' + @UserName SET @Result = (SELECT chPWD FROM Clients WHERE vchClientUserName = @UserName) PRINT 'Msg from DB - @UserName = ' + @UserName + ' - @Result = ' + @Result RETURN GO ============================================ The code for the command button click event is as follows: =========================================== Private Sub btnCommit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCommit.Click Dim strInfo As String Dim cn As SqlConnection Dim cmd As SqlCommand Dim chrPwd As Char If Len(txtUserName.Text) = 0 Or Len(txtPassword.Text) = 0 Then MessageBox.Show("Please enter both a UserName AND Password", "Missing Required Info", MessageBoxButtons.OKCancel, MessageBoxIcon.Error) Exit Sub End If Try strInfo = "server=USER-LMZWW8DZJO\DSK_TOP_ENG;Trusted_Connection=true;database=Int_Expl_Proj" cn = New SqlConnection cn.ConnectionString = strInfo cn.Open() cmd = New SqlCommand With cmd .CommandType = CommandType.StoredProcedure .CommandText = "sp_GetStoredPwd" .Connection = cn End With Dim pmTemp As SqlParameter pmTemp = cmd.Parameters.Add(New SqlParameter("@UserName", SqlDbType.VarChar, 15)) pmTemp.Direction = ParameterDirection.Input pmTemp.Value = txtUserName.Text pmTemp = cmd.Parameters.Add(New SqlParameter("@Result", SqlDbType.Char, 10)) pmTemp.Direction = ParameterDirection.Output chrPwd = cmd.ExecuteScalar If chrPwd = txtPassword.Text Then Process.Start("IEXPLORE.EXE") 'Me.Visible = False Else MessageBox.Show("Incorrect UserName or Password") End If Catch InvalidOpEx As InvalidOperationException If cn.State = ConnectionState.Open Then cn.Close() End If Catch SQLEx As SqlException Dim errorMessages As String Dim i As Integer For i = 0 To SQLEx.Errors.Count - 1 errorMessages += "Index #" & i.ToString() & ControlChars.NewLine _ & "Message: " & SQLEx.Errors(i).Message & ControlChars.NewLine _ & "LineNumber: " & SQLEx.Errors(i).LineNumber & ControlChars.NewLine _ & "Source: " & SQLEx.Errors(i).Source & ControlChars.NewLine _ & "Procedure: " & SQLEx.Errors(i).Procedure & ControlChars.NewLine Next i Dim log As System.Diagnostics.EventLog = New System.Diagnostics.EventLog log.Source = "Int Expl" log.WriteEntry(errorMessages) Debug.Write(errorMessages) Console.WriteLine("An exception occurred. Please contact your system administrator.") Finally If cn.State = ConnectionState.Open Then cn.Close() End If End Try End Sub =========================================== I hope somebody can help Kind regards Ross Petersen Tag: read datareader stream and read output parameters at the same time Tag: 101776
  - 19
    - ObjectSpaces, object persistence I'm looking for a good persistence framework to use with a new project. Gentle.NET looks promising but doesn't support SQL Server stored procedures as far as I can tell. Any tips on that? Is ObjectSpaces still scheduled to be released with Whidbey or has it been pushed off? Thanks, amit Tag: read datareader stream and read output parameters at the same time Tag: 101764
  - 20
    - Metadata about Foreign Keys ADO.NET 2.0 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 Tag: read datareader stream and read output parameters at the same time Tag: 101763
  - 21
    - 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: read datareader stream and read output parameters at the same time Tag: 101762
  - 22
    - 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: read datareader stream and read output parameters at the same time Tag: 101760
  - 23
    - 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: read datareader stream and read output parameters at the same time Tag: 101758
  - 24
    - 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: read datareader stream and read output parameters at the same time Tag: 101749
  - 25
    - 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: read datareader stream and read output parameters at the same time Tag: 101746
- Next
  - 1
    - 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: read datareader stream and read output parameters at the same time Tag: 101736
  - 2
    - 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: read datareader stream and read output parameters at the same time Tag: 101732
  - 3
    - 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: read datareader stream and read output parameters at the same time Tag: 101726
  - 4
    - 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: read datareader stream and read output parameters at the same time Tag: 101719
  - 5
    - 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: read datareader stream and read output parameters at the same time Tag: 101716
  - 6
    - 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: read datareader stream and read output parameters at the same time Tag: 101715
  - 7
    - 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: read datareader stream and read output parameters at the same time Tag: 101712
  - 8
    - 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: read datareader stream and read output parameters at the same time Tag: 101707
  - 9
    - 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: read datareader stream and read output parameters at the same time Tag: 101706
  - 10
    - 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: read datareader stream and read output parameters at the same time Tag: 101701
  - 11
    - 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: read datareader stream and read output parameters at the same time Tag: 101699
  - 12
    - 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: read datareader stream and read output parameters at the same time Tag: 101696
  - 13
    - 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: read datareader stream and read output parameters at the same time Tag: 101694
  - 14
    - 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: read datareader stream and read output parameters at the same time Tag: 101692
  - 15
    - 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: read datareader stream and read output parameters at the same time Tag: 101684
  - 16
    - 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: read datareader stream and read output parameters at the same time Tag: 101674
  - 17
    - 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: read datareader stream and read output parameters at the same time Tag: 101671
  - 18
    - 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: read datareader stream and read output parameters at the same time Tag: 101661
  - 19
    - 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: read datareader stream and read output parameters at the same time Tag: 101659
  - 20
    - 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: read datareader stream and read output parameters at the same time Tag: 101653
  - 21
    - 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: read datareader stream and read output parameters at the same time Tag: 101644
  - 22
    - 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: read datareader stream and read output parameters at the same time Tag: 101642
  - 23
    - 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: read datareader stream and read output parameters at the same time Tag: 101641
  - 24
    - 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: read datareader stream and read output parameters at the same time Tag: 101640
  - 25
    - 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: read datareader stream and read output parameters at the same time Tag: 101638

Been working through a few scenario's where I have returned a stream of data
to the datareader and also returned some output parameters from a stored
procedure. The question is in order to get the parameters returned from the
stored procedure you have to close the datareader. Then you are unable to
step through the stream return ie in a list format. can anyone advise the
best way to do this at the minute i am making to calls one to get the output
params and the second to then step through the datareaders stream.

The only way I can think is to pass the result to a dataset and then close
the reader to expose the output params ????!!!!!!???????

Regards Ben

Top

Re: read datareader stream and read output parameters at the same time by Miha

Miha
Tue Jan 04 07:31:54 CST 2005

Hi ben,

I think that you'll have to read entire stream returned by datareader to get
to the output parameters.
(Close does read all the stream).
You might call two stored procedures - one that returns parameters and the
other returns stream.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
SLODUG - Slovene Developer Users Group
www.rthand.com

"ben" <ben@discussions.microsoft.com> wrote in message
news:1BFE7C27-640C-4276-B87D-8B95EC7C85BC@microsoft.com...
> Been working through a few scenario's where I have returned a stream of
> data
> to the datareader and also returned some output parameters from a stored
> procedure. The question is in order to get the parameters returned from
> the
> stored procedure you have to close the datareader. Then you are unable to
> step through the stream return ie in a list format. can anyone advise the
> best way to do this at the minute i am making to calls one to get the
> output
> params and the second to then step through the datareaders stream.
>
> The only way I can think is to pass the result to a dataset and then close
> the reader to expose the output params ????!!!!!!???????
>
> Regards Ben

Top