Help with reading SqlDataReader

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
    - DataBind Multiple Form Controls to ArrayList In attempting to use an arraylist of objects as the datasource for a form's controls (in the same way I would ordinarily use a DataTable of DataRows), I have discovered numerous issues. One is that the form doesn't seem to update properly when I delete a "record" (which in this case is an object). The CurrencyManager shows the correct new Position and Count, but the bound controls continue to show the data for the deleted item. This remains true even if I change the Position property (to the next, previous, last, or first member of the arraylist), then restore it to where I want it (the arraylist member one index number higher in the arraylist from the deleted member). Anyone have an explanation or workaround for that? Another set of issues has to keeping up with what objects have been deleted and inserted. Unlike with a DataTable, there's no RowState property to keep up with that. I could add an ObjectState property to the object, but I'm not sure how to update it for a deletion or insertion. Suggestions? Thanks! -- j steele Tag: Help with reading SqlDataReader Tag: 88910
  - 2
    - Alias stored proc in dataset? I have a stored procedure that does some math for me. I've aliased the calculations and was wondering why I could not use those in a dataset? The sp runs correctly and returns the alias names, but if I fill a dataset with the result, I get no columns in the datatable. Is there another technique to do this? Tag: Help with reading SqlDataReader Tag: 88907
  - 3
    - Tricky process question I'm trying to read records from a main table, along with data records from dependant tables into a SQLAdapter. I will then need to make some changes to certain field data. I will then need to insert these records into another database (with same structures). Can some one sling me some example code as how they would do this? The rudimentary way would be to loop through each record, inserting into the destination DB and then inserting dependant records as well. But what would be snazzy would be to get all the data into a SQLAdapter, with structure in place, disconnect from the original DB, make mods as nessecary, then connect up to the destination DB and do a one-method insert/update to the destination. Can this be done? Tag: Help with reading SqlDataReader Tag: 88906
  - 4
    - Merging DataSet with Schema Hi there, how can I merge a dataset with a dataschema? The dataset I have was serialized before and all data fits the schema specifications. Then I read the serialized dataset with readxml. How can I merge the schema now? Thx BenLeino Tag: Help with reading SqlDataReader Tag: 88905
  - 5
    - Setting Up Data-Adapter Hi, I am trying to update a row in my data-adapter after inserting to a parent table. Scenario: TableParent --- PID (NOT NULL) --- Description (accept NULL) TableChild --- CID (NOT NULL) --- PID (accept NULL) --- Column1 (accept NULL) --- Column2 (accept NULL) --- etc... when the dataadpter is created and updated how do I update the TableChild upon inserting a new column to TableParent? Thanks, Yama Tag: Help with reading SqlDataReader Tag: 88893
  - 6
    - remote Access 2000 connection string Hi How looks remote Access 2000 connection string. Database is placed on another computer, WWW serwer and i would like to connect to it from client machine. Thanks Konrad Tag: Help with reading SqlDataReader Tag: 88892
  - 7
    - Retrieving Data Hi, I am having problem designing my business objects to retrieve data from a SQL Server. The data structure is very similar to Northwind (Order-Order Details...) however much more complex. I was looking at using inner joins in a complex SQL Select statement however there are too many parent-child relationships to handle. So, I decided to break up it up into multiple select statements. I have a few questions: 1) I allow the user to search by Order Number, Serial Number and Customer Name. I display the results in a datagrid. My question is it a good design to select only the order details info (which I display in the datagrid) and wait to retrieve the rest of the data when the user selects that info? 2) Any other suggestions on how to retrieve data would be appreciated! Thanks Tag: Help with reading SqlDataReader Tag: 88887
  - 8
    - Changing alignment of column in datagrid I thought I had done this before, but I'm apparently overlooking something. All I want to do is set the alignment of the first column to the right. The dataset gives me 5 columns and will auto-format the grid, but I really want to align just one column. Thus the code below. However, the app blows right past my attempt to impose a new table style and auto-formats the grid with everything aligned to the left (I have deleted all table styles from the Datagrid properties, so there is no "default"). Is it possible to set alignment for one column or do ALL need to be manually aligned? Anything else I'm overlooking? DataGrid1.TableStyles.Clear() Dim tblStyle As New DataGridTableStyle tblStyle.MappingName = "History" Dim dgColumn1 As New DataGridTextBoxColumn Dim dgColStyleRight As DataGridColumnStyle dgColumn1 = New DataGridTextBoxColumn dgColumn1.Alignment = System.Windows.Forms.HorizontalAlignment.Center tblStyle.GridColumnStyles.Add(dgColumn1) DataGrid1.CaptionText = "History" DataGrid1.ReadOnly = True DataGrid1.TableStyles.Add(tblStyle) Tag: Help with reading SqlDataReader Tag: 88886
  - 9
    - Only "View" specific rows in DataView Howdy, Here is what I have, I'm working on a search engine, where the results = can be sorted, and paged through. (This is for a Web app btw) My results of the search from the database are stored in a DataTable. From this DataTable I create a DataView, that sorts the datatable. Now I need to bind certain rows of this view to the DataSource, and here = is where I'm running into problems. =20 Say I'm on page 4 of my results, and at 10 results per page, I need to = bind rows 31-40 of this dataview to my control (a asp.net repeater). Is = there some way I can filter this DataView based on the index of the = items, not anything specific to the data itself. Or perhaps there is a = way to copy these rows to another view (Didn't look possible) I know I = can create another datatable and add rows manually from the view, but = that just seems like too much overhead when I'm so close. Any Ideas? --Michael Tag: Help with reading SqlDataReader Tag: 88879
  - 10
    - ADO Quick Question Hi, I have a quick question - I have a table where the primary key is an autogenerated GUID via SQL Server 2k. The table has just 2 fields, the PK and the ChangeDate. When I do an insert into the table w/ the date, the PK is generated, but I need to have that PK value returned to the application for use elsewhere. Is there a way to have this value returned to my application w/o having a routine that goes back and gets it? I thought there was something, but I can't remember it. Thanks. Doug Tag: Help with reading SqlDataReader Tag: 88877
  - 11
    - Creating a DataSet and DataTable then adding a record. Help! I am using the code below to create a new dataset, add a new table to it called "Inventory" and then add a new row to the table with some data in it. I am using this to create a temporary dataset so that I can bind a datagrid to it. All goes well until I try to update it. When it gets to the last line and tries to update, I get the error Invalid object name 'Inventory'. Can someone please tell me what I am doing wrong? Thanks in advance! Dim MyConnection As SqlConnection Dim MySQLDataAdapter As SqlDataAdapter Dim bAdd As Boolean Dim aryParts As Array Dim aryQuantities As Array Dim i As Integer Dim PrimaryLocationName, SecondaryLocationName As String Dim PrimaryLocationCode, SecondaryLocationCode As String aryParts = Parts.Split(",") aryQuantities = Quantities.Split(",") PrimaryLocationCode = Location1 SecondaryLocationCode = Location2 PrimaryLocationName = GetLocation(Location1).Trim SecondaryLocationName = GetLocation(Location2).Trim MyConnection = CartFunctions.GetConnection MyConnection.Open() 'Create a new dataset Dim MyDataSet As New DataSet 'Add a new datatable to the dataset to store our records in Dim dtInventory As New DataTable("Inventory") Dim dc As New DataColumn dc = New DataColumn("PartNumber", System.Type.GetType("System.String")) dtInventory.Columns.Add(dc) dc = New DataColumn("OrderQuantity", System.Type.GetType("System.String")) dtInventory.Columns.Add(dc) dc = New DataColumn(PrimaryLocationName, System.Type.GetType("System.String")) dtInventory.Columns.Add(dc) dc = New DataColumn(SecondaryLocationName, System.Type.GetType("System.String")) dtInventory.Columns.Add(dc) dc = New DataColumn("AllLocations", System.Type.GetType("System.String")) dtInventory.Columns.Add(dc) MyDataSet.Tables.Add(dtInventory) Dim MyDataRow As DataRow MySQLDataAdapter = New SqlDataAdapter("SELECT * FROM Inventory", MyConnection) Dim MyDataRowsCommandBuilder As SqlCommandBuilder = New SqlCommandBuilder(MySQLDataAdapter) MySQLDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey 'Now loop through and get the data For i = 0 To UBound(aryParts) 'Get inventory for Primary Location Dim strSQL As String = "SELECT * FROM location_inventory WHERE location_code = '" & PrimaryLocationCode & "' AND part_num = '" & aryParts(i) & "'" Dim cmdReader As SqlCommand Dim dtrList As SqlDataReader cmdReader = New SqlCommand(strSQL, MyConnection) dtrList = cmdReader.ExecuteReader Do While dtrList.Read MyDataRow = MyDataSet.Tables("Inventory").NewRow MyDataRow("PartNumber") = aryParts(i) MyDataRow("OrderQuantity") = aryQuantities(i) MyDataRow(PrimaryLocationName) = dtrList("Quantity") Loop dtrList.Close() MyDataSet.Tables("Inventory").Rows.Add(MyDataRow) MySQLDataAdapter.Update(MyDataSet, "Inventory") Next Tag: Help with reading SqlDataReader Tag: 88869
  - 12
    - convert .NET data types to SQL data types Hello all, the job is the following: given 2 databases I must recreate a table from one DB to another; right now i'm using .getTableSchema, get a datatable with schema, loop through all it's rows and build a "create table..." statement; but the problems apear: converting .NET types to sql types, column length mismatch (sql.text.16 -> .Net.System.String.2147483647) which throws an exception. Is there an elegant way of conversion? Thank you, Daniel Tag: Help with reading SqlDataReader Tag: 88865
  - 13
    - Query an Existing DataSet into a New DataSet I have an existing Dataset, can I create a new Dataset using the existing dataset with a new Query. I want to query the existing dataset into a new dataset. Is this possible? Tag: Help with reading SqlDataReader Tag: 88862
  - 14
    - Why do the commandbuilder classes not derive from an interface or a base class? Why do the commandbuilder classes not derive from an interface or a base class? Every other ado.net object seems to do so. Tag: Help with reading SqlDataReader Tag: 88861
  - 15
    - Getting the value back into the datarow Hi all, I am using the System.Data.OracleClient on an Oracle 8.17 database. The problem I have is that when I define an output parameter I can see the retreived value within the RowUpdated event of the OracleDataAdapter but the value is never actually being put back into the dataRow. For the parameter I define: ParameterName = "EMP_SAL" SourceColumn = "SAL" SourceVersion = DataRowVersion.Current ParameterDirection = ParameterDirection.Output The EMP_ENAME and EMP_EMPNO parameters are defined as InputOutput. And the command that is executed with OracleDataAdapter.Update(row) is: begin insert into EMP (ENAME, EMPNO) values (:EMP_ENAME, :EMP_EMPNO); select SAL, ENAME into :EMP_SAL, :EMP_ENAME from EMP where EMPNO = :EMP_EMPNO; end; The reason I want to use an ouput parameter is because in my example the SAL column in the database has a default value I want to retrieve. In a more realistic example the SAL column could be calculated. The obvious fix is to manually put the value back into the DataRow during the RowUpdated event. Is this a known bug? Any other suggestions? With kind regards, Robert van Poelgeest Tag: Help with reading SqlDataReader Tag: 88848
  - 16
    - Best Practice: Table Primary Key Hi gang, I need some SOLID direction here. I am planning to use DataSets and DataBinding in an app which will have a few users (VB.NET / SQL Server). I need to be able to create an 'offline' mode, where they do not have access to the WebService, and would therfore need to store the data locally as XML, then merge into the DB later. My issue relates to primary keys. As a former Access Guy (current app is SQL Server 2000), I have grown accustomed to the use of Auto-Incrementing primary keys for a table, then making relationships to this integer value. In .NET Datasets, I know (painfully) that I would need to set the AutoincrementStep and AutoincrementSeed of the dataset to -1 and 0, to avoid issues. This doesn't work for me here, since I need the PrimaryKeys to be the same in the 'offline' xml file and the online DB. I am willing to change my ways, and begin to use either 'natural' keys, or GUID fields. I would like some people to share their experience on using either of these is similar situations. Are there performance hits asssociated with relating tables to VARCHAR fields as oposed to INTs? Are there any trade-offs I need to be aware of prior to using this approach? Should I 'never use an AutoIncrement again'? Does databinding have any issues using a GUID as the Value of a combobox, for example? Thanks!! Kevin Tag: Help with reading SqlDataReader Tag: 88847
  - 17
    - Effective use of DataAdapter.Update(DataSet) I have been reading a lot of material on the use of (specifically) the SqlClient classes that are used to retrieve, display, edit, and update database records. The basic approach seems to be: 1. Define a SqlConnection to the database. 2. Define a SqlDataAdapter along with a command for retrieving the data. 3. Set the SqlDataAdapter.MissingSchemaAction to AddWithKey to ensure Primary Key information will be included. 4. Define a DataSet and use SqlDataAdapter.Fill to populate it. 5. Associate the DataSet (or DataSet.Tables[0]) with a DataGrid or some other control(s). 6. Permit the user to edit the contents of the DataSet; rows may be added, deleted or modified. 7. Create a new SqlDataAdapter for the same command used to populate the original data; again set the MissingSchemaAction to be complete. 8. Either define a SqlCommandBuilder or define commands for the Insert, Delete and Update members of the SqlDataAdapter. 9. Invoke the SqlDataAdapter.Update method to commit all changes to the database. 10. Some have suggested then using SqlDataAdapter.Fill on the same DataSet to merge changes made by other clients with the existing information. There seem to be a significant number of flaws with this approach, and it is not easy to find discussions or suggestions for alternatives or methods to avoid these flaws. Of course, I may be missing something completely and not understanding how this process is actually working. I want to make it clear that I develop client and client/server applications. I have never developed IIS, IIS.Net or Web Service 'applications'. Issue #1 -- This mechanism, as with the vast majority of the information I can find on these specific classes, seem to presume single user access to the database and, for the most part, ignore the impact of concurrent updates from multiple client applications into the same database. Presuming the above process is used concurrently by multiple client applications, all making changes to the same table, there will be no way to control which changes are made to a specific record! Consider the case where three (3) clients make changes to the same record, where two of the changes are to different columns and the third change involves all of the columns from the first two. The first client issues the Update and the changes are saved; the second client issues an Update and that change is saved, effectively reversing the change from the first client; when the third client commits the changes then both of the first two changes are overwritten -- and none of the clients are aware of this. Issue #2 -- The documentation indicates that repeating the SqlDataAdapter.Fill method call on an existing DataSet will merge the changes from the table into the DataSet, but this is only partially true -- it does not seem to deal with records that are in the DataSet but which have been deleted from the database table (likely by another client). In order to achieve this it seems one should issue a DatSet.Tables[0].Clear method call before the SqlDataAdapter.Fill call to ensure that this happens, although none of the documentation mentions this. Issue #3 -- I have found no references to how one might effectively approach dealing with the situation where multiple records are modified in different ways by different concurrent users. Considert the case where client A and client B both retrieve the same set of records from the same table. Client A modifies one record and saves the change (the Update succeeds). Client B at that point has no understanding that changes have been made, and the client makes changes to several records, including the one changed by client A. It is not clear how the Update could permit the changes to be made to all of the records except the one modified by Client A (or even how this would be detected), and then permit this information to be reported back to client B. The only option seems to be to fail the entire update from Client B, but there is still no clear way to explain which records are affected. This is compounded by the indication that if the Update method succeeds the AcceptChanges method on the DataSet seems to be called. Issue #4 -- There does not seem to be any automatic way to indicate, say in a DataGrid, which records have been modified and which have not. There also does not seem to be an automatic way to indicate that some record changes were committed to the database and other changes were not. Issue #5 -- These issues are compounded when the code is implemented in a client/server architecture as opposed to a single application (or in a web application, although I have not personally written any of those). Issue #6 -- While there is documentation on the SqlDataAdapter.RowUpdating event, there is no information on how (or if) the code can indicate that the specific record can be ignored or skipped. Ideally it would be nice to be able to compare the original record in the DataSet with the current content of the database table and reject the record accordingly. In order for the client to understand what happened, one would likely want to retain the modified record from the DataSet so it can be compared to the new version from the table and shown to the user (permitting them to make more changes without having to memorize the specific change they made). Issue #7 -- There is no documentation on how the Fill and Update method processes play together when executed against the same table from different client applications concurrently. Consider the scenario from issue #1 and attempt to define the specific data values that each of the three clients will see on their screen. Issues #8 -- The existing classes and controls seem to be focused solely upon multiple-record updates. There seems to be little attention paid to scenarios where the change to each individual record must be processed before the user can make changes to other records. I know that much of .Net is geared toward IIS.Net and Web Server applications, but many client/server applications have traditionally dealt with single record processing, at least in part to avoid some of the problems that are discussed above. I am willing to convert to this 'batch' mode of processing, but I cannot see the degree of control that I need. I apologize of some of the comments here are not crystal clear. -ken Tag: Help with reading SqlDataReader Tag: 88842
  - 18
    - using a SP ?? Hi, when creating an SqlCommand do I use the name of a stored procedure : 'mytable' Now, I've seen (working) code where that SP is preceded by the name of a user : 'user1.mytable' I try it at home ... hence I've created 'user1' in the DB (with dbo rights on myDB) but when executing the command do I get a : Could not find stored procedure 'user1.mytable' How come ? thanks Christian Tag: Help with reading SqlDataReader Tag: 88840
  - 19
    - other ways to use/open a Database Connection? Hi, In a VB.NET-application I use this to open a Database Connection: Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim strConn As String strConn = "Server=BESQL1;DataBase=VocalcomCetelem;User ID=vocalcomcetelem;Password=vocalcomcetelem;Trusted_Connection=False" Dim strSql As String strSql = "SELECT TOP 1 * " & _ "FROM tblCalls " & _ "ORDER BY CallID DESC" Dim dtrSql As SqlDataReader Dim conSql As SqlConnection conSql = New SqlConnection(strConn) Dim cmdSql As SqlCommand Try conSql.Open() cmdSql = New SqlCommand(strSql, conSql) dtrSql = cmdSql.ExecuteReader(CommandBehavior.SingleRow) dtrSql.Read() If dtrSql.HasRows Then MessageBox.Show(dtrSql.Item("tdcaNCartphy").ToString) End If Catch ex As Exception MessageBox.Show(ex.Message & ex.StackTrace) End Try End Sub Sometimes it gives me an error when opening the Connection (on the conSql.Open() ).: Like this with MDAC 2.7 installed: The .Net Data SQL Provider (System.Data.SqlClient) requires Microsoft Data Access Components(MDAC) version 2.6 or later. at System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) at System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnec tionString options, Boolean& isInTransaction) at System.Data.SqlClient.SqlConnection.Open() Or this with MDAC 2.8: Object reference not set to an instance of an object. at System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) at System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnec tionString options, Boolean& isInTransaction) at System.Data.SqlClient.SqlConnection.Open() So it seems I get troubles to get a (pooled) connection. Is there a 'safer' way to get a connection? Using OleDB or Odbc? Or what can I do to get rid of the error and get always my connection? Thanks a lot in advance, Pieter Tag: Help with reading SqlDataReader Tag: 88833
  - 20
    - Data Access Application block and COM+ Hi, all I want to know if I can use Data Access Application block ini COM+. How can I do this? Is as simple as derived the class from EnterpriseServices and asigne it attributes Thanks a lot! Tag: Help with reading SqlDataReader Tag: 88827
  - 21
    - claim your Live Internet training session today. Do you want to attend a LIVE Internet classroom with us? www.velocitystudy.com is offering 100 IT students a free introductory training session. Please visit www.velocitystudy.com to register for you introductory session. Tag: Help with reading SqlDataReader Tag: 88826
  - 22
    - Problems with reading data from excel sheet: Some values are null Hi, I've got an excel sheet in which i define some kind of matrix. This looks this way A B C D E F G ID 12345 FROM/TO 1 2 3 4 5 6 1 0 3 3,5 0 2,3/4 0 2 7,9/3,6 0 1,2 0 1 1 3 0 0 0 4 0 2 4 1 0 1,2/4 0 1 2 5 2 2,4 1 0 0 6 6 1/5 0 0 0 0 0 I'm trying to read that sheet into a dataset via ado.net in a c# windows application as descripted in the MSDN Article http://msdn.microsoft.com/library/en-us/dnadonet/html/odatanet2.asp?frame=true . But when I'm displaying my dataset in a datagrid some values are null: ID 12345 (null) (null) (null) (null) (null) (null) (null) (null) (null) (null) (null) (null) FROM/TO 1 2 3 4 5 6 1 0 3 3,5 0 2,3/4 (null) 2 7,9/3,6 0 1,2 0 1 1 (null) (null) (null) (null) 4 0 2 (null) (null) 0 1,2/4 0 1 (null) (null) (null) (null) 1 0 0 6 (null) (null) (null) 0 (null) 0 0 All cells in the excel sheet are defined as text. I don't see any pattern behind this behaivor. Does anyone here knows something about this issue? Thanks in Advance Markus Tag: Help with reading SqlDataReader Tag: 88825
  - 23
    - MDAC Dependency I understand that ADO.Net has some dependency on MDAC 2.6 or later (from MSDN documentation). Please correct me for the following: "If I am going to use either System.Data.SqlClient OR System.Data.OracleClient namespaces for data access, as they directly communicate with database (without the support of respective OLE-DB drivers), MDAC dependency can be avoided" -- Rao TRN Tag: Help with reading SqlDataReader Tag: 88820
  - 24
    - Updating datasets with parent child relationships - 320301 I have used KB article: 320301 (http://support.microsoft.com/default.aspx?scid=kb;%5BLN%5D;320301&product=vcSnet) as as reference to update datsets with nested relationships. So far it works great for the case of a simple parent-child relationship between two tables in a dataset. I have a couple of questions: -Is this a temporary work-around until a permanent solution is provided? -Is there any negative impact on using this code example to handle relationships of higher depth than parent-child relationship? For example, lets say you have a table named "UnemploymentClaim" that has a child named "worker" that has a child table named "address" that has a child table named "zipCodes". (I know I am reaching here, but hopefully you get the point). The question here is will the same methodology that is used in KB article 320301 work for the relationships that I described? I would really appreciate any input on this matter because I am looking into using this on a larger scale... Thanks, Bill Tag: Help with reading SqlDataReader Tag: 88817
  - 25
    - ado.net not retrieving updated records I have an asp.net application which allows users to search, create and update records in a SQLServer2000 DB. Search results are displayed in a datagrid The problem is after a record is updated or created and the datagrid refreshed by the user, the newly updated record doesn't display in the search results. The datagrid itself is definately refreshing as the record that has been updated disappears completely If I close and open the browser and do a new search the record appears Any db connections I open I have closed in a finally block and the dataset I use to populate the datagrid is NOT held in session Any ideas? Tag: Help with reading SqlDataReader Tag: 88815
- Next
  - 1
    - Why does my Dataview NOT work!!! Me.SqlDataAdapter1.Fill(Me.vdsVehicle) Dim dt As New DataTable dt = Me.vdsVehicle.Tables(0) Dim dv As New DataView(dt) dv.RowFilter = "VehicleYear = '1985'" both the Dataset and the Dataview still have three records. It is not filtered down to ONE record like it should be. Why?? Tag: Help with reading SqlDataReader Tag: 88803
  - 2
    - RowFilter on Time values I have a table in a SqlServer database which contains a couple of DateTime fields, 'StartTime' and 'EndTime'. These fields are only used to store time, not date. Since I don't specify any date information, all the records have '1/1/1900' for their date. Once I've loaded all the records into a DataView, I would like to be able to filter the records by StartTime and EndTime. The time value I want to filter on is stored in a TimeSpan variable. Apparently, you can't compare DateTime values directly to TimeSpan values in a RowFilter, so I wind up doing something like this: DateTime date = new DateTime(time.Ticks); DataView1.RowFilter = "(date > StartTime) AND (date < EndTime)"; Unfortunately, the .NET DateTime class starts at '1/1/0001'. So my filter looks something like this: "('1/1/0001 6:00AM' > '1/1/1900 6:00AM') AND ('1/1/0001 8:00PM' > '1/1/1900 8:00PM)" Which never returns any results, of course. What I want to do is JUST compare two time values. Is there an easy way to do this? TIA, Gabe Tag: Help with reading SqlDataReader Tag: 88802
  - 3
    - Typed DataSet in code... Can a typed DataSet be made in code so that code that comes after the type code will pick up on the dataset's table structure for intellisense? Tag: Help with reading SqlDataReader Tag: 88801
  - 4
    - Typed DataSet creates extra tags in the XML output. Two DataSets loaded with the same XML data and schema write two different XML data files. Bellow youâ??ll find a simple test code and the output. Declare, instantiate and load the DataSets: Dim dsOrig As New DataSet("Request") Dim dsDup As New DataSet("Request") Dim filePath As String filePath = " PVIrequest.xml" dsOrig.ReadXml(filePath) dsOrig.WriteXmlSchema("Test.xsd") dsDup.ReadXmlSchema("Test.xsd") dsDup.ReadXml(filePath) Edit the first DataSet and Write XML: Dim swXML As New System.IO.StringWriter With dsOrig Dim rwEdit As DataRow rwEdit = .Tables("eCommerce").Rows(0) rwEdit.BeginEdit() rwEdit("action") = "Test" rwEdit.EndEdit() .WriteXml(swXML) TextBox1.Text = swXML.ToString â??See bellow <eCommerce version="1.1" action="Test"><Requestor><ID>value</ID><Password>*******</Password></Requestor><Shipment action="GenerateLabel" version="1.0"> Complex Type data </Shipment></eCommerce> Edit the second DataSet and Write XML: Dim swXML As New System.IO.StringWriter With dsDup Dim rwEdit As DataRow rwEdit = .Tables("eCommerce").Rows(0) rwEdit.BeginEdit() rwEdit("action") = "Test" rwEdit.EndEdit() .WriteXml(swXML) TextBox1.Text = swXML.ToString â??See bellow <Request><eCommerce version="1.1" action="Test"><Requestor><ID>PENTA_0608</ID><Password>57ID57C7UH</Password></Requestor><Shipment action="GenerateLabel" version="1.0"> Complex Type data </eCommerce></Request> I need to get rid of the extra tags (the DataSet name). I thank Cowboy for his Xml Transform suggestion, but I would like to know what am I missing with my approach? As I mentioned in my previous cry for help, my way around it was to create doc as an XmlDataDocument(dsDup) and call doc.SelectSingleNode("/Request") function. I hope that this message has clarified my problem. Thank you. Tag: Help with reading SqlDataReader Tag: 88790
  - 5
    - OracleClient stored procedure parameter - can it handle 8 bit VarChars? I'm trying to execute a pair of Oracle stored procedures that encrypt/decrypt a character string using Oracle's obfuscation package. Each stored procedure has an input and an output parameter. One procedure encrypts a string, the other decrypts a string. The problem is that when I encrypt a value the procedure executes without incident, but the return string is losing it's high-order bits. What I get back is a value that looks encrypted, but when I feed the string to the decryption procedure I don't get my original value back. When we compare the string returned by my .Net function to the string returned when executing the sp under SQL*Plus we see that several characters are 80h "smaller" in the .Net functional return than they are when the sp is executed under SQL*Plus. The database is Oracle 9i, running with an eight bit character set, but I don't see any property in the .Net OracleClient that allows me to specify that I expect 8 bit characters back from Oracle. Can anyone shed any light on this for me? How can I get .Net to understand that Oracle is returning 8 bit characters? Thanks much, Brad Tag: Help with reading SqlDataReader Tag: 88786
  - 6
    - RowDeleting, RowChangeing, ColumnChanging exception handling As I figured out, the recomended way to cancel editing of a column, or deletion of a row is to create an event handler for ColumnChanging or RowDeleting events, and to throw your own exception. Well, I did just that, and the problem is that I do not know where to catch this exception. As I understand, it should be done somewhere in the DataGrid which is bound to the DataTable which raised an event, but I don't know where. Can anybody help me? Tag: Help with reading SqlDataReader Tag: 88783
  - 7
    - parent column in child row Hello, I have a dataset with 2 tables and a relation (parent - child). I linked that ds to a datagrid. Shows everything fine (very little coding for such functionality!). But.. in the child row I want to show a column of the parent. Example: orderColumn shows customerId and I want the customerName which is located in the parent. How do I do that? Thanks in advance Frank Tag: Help with reading SqlDataReader Tag: 88778
  - 8
    - DataTable.New() and Rows.Add() not working right I'm using a SqlReader to read results from a query and insert them into a DataTable. The DataTable is from a DataSet which contains an Xml that was read in using ReadXml(). My problem is, only 1 row gets written correctly. The others don't have any attributes and are not in the proper hierarchy in the Xml yet they're all created from the same loop. while (sqlReader.Read() ) { DataRow dr = dtProducts.NewRow(); dr.ItemArray[dr.Table.Columns.IndexOf("Col1")] = sqlReader.GetString(indexPT); ... ... // set more rows dtProducts.Rows.Add(dr); } ds.WriteXml(@"X:\temp\Test.xml"); Tag: Help with reading SqlDataReader Tag: 88777
  - 9
    - SQL Stored Procedure not updating. Hoping someone can provide some insight into what I'm doing wrong here. I have a Win.Form with a bunch of text controls, bound to a Dataset (DsCustomerInfo1), and a ComboBox bound to the ClientID Field of that Dataset (DsCustomerInfo1.cw_clientinfo.ClientID) so that I can select a client ID, and get all the text boxes filled with data for the selected client. That all works beautifully. I edit the address field for instance, and hit my Update Client Button. Private Sub btnUpdateClient_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdateClient.Click Me.connExpress.Open() 'Open the connection Try Debug.Assert(Me.DsCustomerInfo1.HasChanges, "No Changes Present") Me.daCustomerInfo.Update(Me.DsCustomerInfo1, "CW_customerinfo") Catch ex As Exception MsgBox(ex.ToString, MsgBoxStyle.Critical, "SQL Error") End Try Me.connExpress.Close() 'Close the connection End Sub The Assert says that there are no changed Rows. I have a breakpoint on the daCustomerInfo.Update line, and I have a watch on CType(CType(DsCustomerInfo1.cw_customerinfo.rowCollection.Item(0),System.Dat a.DataRow),SupportTool.dsCustomerInfo.CW_customerinfoRow) Which is the row I'm changing. The Address field shows my new edited address, but the Rowstate property is Unchanged. I can select a new client from the Combobox, and come back to this one, and my new edited address is still displayed, but it hasn't been reflected back to the SQL Database. If I log out of the application and come back in, I'm back to my original address as stored in the SQL DB. Tag: Help with reading SqlDataReader Tag: 88773
  - 10
    - [store procedure] + sqldatareader problem my simple store procedure like thiese:- CREATE PROCEDURE dbo.companyinfo_shipper_search @searchcode varchar(10) as select name from companyheader where code = @searchcode GO ------------------------------------------------------------------- my vb.net code cmdSearch.CommandType = "dbo.companyinfo_shipper_search" prmCompany_Code = cmdSearch.Parameters.Add("@searchcode", strCode) prmCompany_Code.Direction = ParameterDirection.Input prmCompany_Code.SqlDbType = SqlDbType.VarChar Try Dim drCompanyReader As SqlClient.SqlDataReader = cmdSearch.ExecuteReader(CommandBehavior.SequentialAccess) Do While drCompanyReader.Read Console.Write(drCompanyReader.GetString(0)) Loop ......etc I use sql analyzer to check and it really return the correct records. However, as I use sqldatareader, it returns error" Invalid attempt to read when no data is present". What's wrong with my code ??? Please help. Tag: Help with reading SqlDataReader Tag: 88771
  - 11
    - Child Table In ado.net cannot have table as child of more than one parent. Is there a way around this? Tag: Help with reading SqlDataReader Tag: 88749
  - 12
    - How to maintain state of the listbox while it is bound to Datasource? Hi I have bound my listbox to a dataset. But when I select multiple choices from the listbox, when I again come back to that list box the selected items are lost I have 2 tabs. One has bounded listbox, so when I select multiple items and then go to the other tab and then came back to the first one the selected items are lost Pl help Tag: Help with reading SqlDataReader Tag: 88738
  - 13
    - Connecting to Excel via ADO.net: ISAM driver error Hi, I've got following problem: I'm trying to open a excel sheet via a ado.net oledb connection. but as soon as the function Open is called I'm getting an exception with following text: "Could not find installable ISAM driver". Looking in the msdn I found several issues to that problem. I checked the registry as described and re-registered the dll but that was effectless. Here's my code: string sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;DataSource=J:\\MyDir\\MyExcelFile.xls" + /*+ ofdExcelSheet.FileName + */ ";Extended Properties=\"Excel 8.0;HDR=Yes\""; System.Diagnostics.Trace.WriteLine( sConnString ); System.Data.OleDb.OleDbConnection cn = new System.Data.OleDb.OleDbConnection( sConnString ); cn.Open(); I'm using VS.NET 2003 and Office 2003 Does anyone have any expierences with that issue Thanks in Advance Markus Tag: Help with reading SqlDataReader Tag: 88737
  - 14
    - DataTable.Select returns no rows on a date filter H I am using the DataTable Select to filter my data on a date column within the data table, as follows dr_DataRow = m_dt_ScheduleDataTable.Select("StartDate=#" & Format(dte_DiaryDate, "MM/dd/yyyy") & "#" This would appear to intermittently return rows, and I can't see why it does sometimes and doesn't on other occasions. The data table I have has 136 rows and the date I am initially testing with matches 22 of these rows. On occasions all 22 are reurned, other times just some of these and at other times no rows I also allow the user to filter on an integer column dr_DataRow = m_dt_ScheduleDataTable.Select("InterpreterID=" & CType(int_Interpreter, String) And this never seems to exhibit any problems so I think it must be a date issue. I am passing the string in in US format as you can see, although I am in UK and everything is in UK format, bit if I pass a date string in UK format it never returns rows and throws an error if I pass in a date which does not comply with US format e.g 16/05/2004, therefore leading me to believe that the Data table holds dates in US format regardless of all my system settings Has anyone seen anthing like this Thank Siobha Tag: Help with reading SqlDataReader Tag: 88734
  - 15
    - How to create COM+ object which use .NET EnterpriceServices on remote machine? How to create COM+ object which use .NET EnterpriceServices on remote machine? I use on remote machine: object comObject; comObject = Activator.CreateInstance(Type.GetTypeFromCLSID(classGiud,remoteMachineName)) ; classGuid - GUID of class which inherits from serviced component. After execution this expression, It shows error like this: "Could not find server.dll assembly." Before I have been installed COM+ proxy. Can I deploy COM+ client as registered type library from server.dll? Thank You, Vasily! Tag: Help with reading SqlDataReader Tag: 88733
  - 16
    - "MDAC required" but it's installed... Hi, I'm having this error in a VB.NET-application at the moment that I attempt to read data from an SQL Server: The .Net Data SQL Provider (System.Data.SqlClient) requires Microsoft Data Access Components(MDAC) version 2.6 or later. at System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) at System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnec tionString options, Boolean& isInTransaction) at System.Data.SqlClient.SqlConnection.Open() Failed to Load Call Data Call Data Failure However the MDAC 2.7 is installed! And when I look into the registry it is indicated as installed too: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\DataAccess\FullInstallVer = "2.71.9040.2" The .NET Framework is installed (1.1), and it is on a Windows NT (Sp 6a) pc. Does anybody know about thisp roblem? And some solution? Could it be because of some restictions (although I do have full Admin rights on the pc). I'ts really urgent and bothering me (I have to do a demo in 20 minutes, hehe). Thanks a lot in advance, any help, hints etc will be really appreciated! Pieter Tag: Help with reading SqlDataReader Tag: 88729
  - 17
    - Getting OCI-22060 error while using Microsoft.Net's Oracle Provider Hi, I am trying to get data back from Oracle database using Microsoft .Net's Oracle Provider and I keep on getting a OCI-22060 error stating that argument [2] has unitialized value. SQL plus returns me the data I need without any problems, and for the same query I get this error on the web. This error only happens when I use a materialized view. If I don't use the MV and get data from all the tables everything is fine. However, since there was no automatic redirection of the queries to the MVs were taking place, I decided to write code to use the MVs directly, and that's when I got this error. Any help would be appreciated. Thanks, and best regards, - Amit. --- Posted using Wimdows.net NntpNews Component - Post Made from http://www.DotNetJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching. Tag: Help with reading SqlDataReader Tag: 88723
  - 18
    - DBnull and checkBox when adding new record Yo, Got a checkbox which I databind at runtime to a numeric column. It works fine but when I add a new record I get the error: -------------------------------- An unhandled exception of type 'System.InvalidCastException' occurred in mscorlib.dll Additional information: Object cannot be cast from DBNull to other types. -------------------------------- I'm databinding just after adding a new record, so the order of events is: 1. Add new record code: private void AddRecord() { // daBuilding is my DataAdapter, dsBuilding is my DataSet. daBuilding.FillSchema(dsBuilding, System.Data.SchemaType.Source, "BUILDING"); this.BindingContext[dsBuilding, "BUILDING"].AddNew(); this.DataBindControls(); } 2. Databinding code: private void DataBindControls() { // Check the global flag to see if we have already databound controls. if (this.dataBound) return; . . // this is the line that craps out: chkHeritage.DataBindings.Add("Checked", this.dsBuilding, "BUILDING.HERITAGE_LISTED_YN"); chkHeritage.DataBindings["Checked"].Format += new ConvertEventHandler(NullToBoolean); . . // Set global flag so we don't have to databind again. this.dataBound = true; } My understanding is that you have to databind AFTER having retrieved a dataset to bind to, hence the FillSchema() call at the begining. But how does this work when you add a new record and have checkBoxes which don't know what to do with a null? Any advice on this greatly appreciated. Cheers, PeterZ Tag: Help with reading SqlDataReader Tag: 88721
  - 19
    - data access for WinForms I am looking for architectural solution for data access from WinForms. For WebForms I am using MS Data Access Application Block with few modifications. DataReader methods help a lot and this was a big boost for my project. For WinForms requirements are quiet different. For a example databinding is must. If you have few windows opened all of them must be in synch. So Dataset objects comes to mind first but it requires so much coding, with any change to database I have to change Adapters, then regenerate dataset. VS has a lot of support for working with datasets but still a lot of hassle. I would prefer to create data access code separately from UI so in order to use VS tools for working with Dataset I use Components instead of classes. I just don't feel that it is considered rapid development. I just wanted to know if everybody works like this or there is some smarter solutions out there(please don't offer me to by $1000 application)? Tag: Help with reading SqlDataReader Tag: 88719
  - 20
    - expression for a instantiation a DataColumnobject Hallo, i not unterstand the expression {oColPk}, see Row 18. What do have the braces with the instantiation a DataColumnobject? >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> 1 Private Sub Test() 2 Dim oDsCustomer As DataSet 3 Dim oTblOrders As DataTable 4 Dim oColPk As DataColumn 5 6 'Eine Tabelle mit 3 Spalten im Arbeitsspeicher anlegen 7 oDsCustomer = New DataSet("CustomerOrders") 8 9 oTblOrders = New DataTable 10 oTblOrders = oDsCustomer.Tables.Add("Orders") 11 12 oColPk = New DataColumn 13 14 oColPk = oTblOrders.Columns.Add("OrderID", Type.GetType("System.Int32")) 15 oTblOrders.Columns.Add("OrderQuantity", Type.GetType("System.Int32")) 16 oTblOrders.Columns.Add("CompanyName", Type.GetType("System.String")) 17 18 oTblOrders.PrimaryKey = New DataColumn() {oColPk} '<--- What is that? 19 end Sub <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< Greetings Wolfram Hubert Tag: Help with reading SqlDataReader Tag: 88716
  - 21
    - DBConcurrencyException (without Concurrency Problems ?) I am experimenting with ADO.NET I use MySQL 3.23 with MyODBC 3.51 At the moment I simply try an insert a select and an update. I use the usual DataSet approach. The select works and retrieves the data from the database. When I try an update I always get a System.Data.DBConcurrencyException I have looked thru some Microsoft documentation and as far as I understood it a DBConcurrencyException is only thrown when the DataSet is out of sync with the database. I am pretty sure that I don't change the database between the filling of the DataSet and testing the Update statement. I have already looked at the DataRow which I try to update immediately before dataAdapter.Update(dataSetChanged); it seems hold the same data as as the database. Does anybody know what could cause this behavior, or give my some hints what to investigate? Many thanks, Hans Tag: Help with reading SqlDataReader Tag: 88713
  - 22
    - Delete Record Question I have a SQLDataAdapter created with the wizard and having all of the commands including the DeleteCommand - I used the defaults. To delete a record, this works just fine: myDA.SelectCommand.Parameters("@mykey").Value = mykey myDS.Clear() myDA.Fill(myDS) If myDS.mytable.Rows.Count > 0 Then myDS.mytable.Rows.Item(0).Delete() myDA.Update(myDS) End If However, it seems that I should be able to do this in a more straightforward manner using the DeleteCommand. How should this be done with a minimum of code? thanks, T Tag: Help with reading SqlDataReader Tag: 88710
  - 23
    - Oracle Connection Pooling Hello, I am trying to figure out connection pooling. I found this article which discussed the various options available on the connection string, unfortunately it was for the "oracle" provider and not the "Microsoft Oracle provider". Where can I find the options which are avialable for the Microsoft Version? How do I control the number of Connection which are in the pool? I placed this in my connection string "Min Pool Size=30" and it did not bark at me, but then again, I am not sure that it really opened 30 connections? How can I see how man connections are open for my application? I think I am opening connections and not closing them because after a while my application begins to have problems making a connection. Someone indicated that I should create "performance counters" to monitor how many connections are open at any one time. I have never set up performance counters. Does someon how a sample? Thanks in advance for your assistance!!!!! Tag: Help with reading SqlDataReader Tag: 88709
  - 24
    - DeleteCommand.executenonquery with ADO.NET - How do I know if a record was deleted? After entering the sql statement da.deletecommand.commandtext = "Delete from Users where username = @username" I set the parameter to the username I wish to delete. This successfully deletes the user. However, even if I enter an non-existant user, I cannot discern whether a user was deleted or not. Is there a way to do this? (I would rather not use a dataset, since I am trying to increase performance). thanks in advance Rodger Dusatko Tag: Help with reading SqlDataReader Tag: 88699
  - 25
    - data access layer function call Hi. I have the following requirement and I don't know which way I should approach it In a 3 tier application, the business layer calls a function in the data access layer; the data access layer function opens an Oracle connection, execute an "ExecuteScalar" instruction, closes the connection and returns the result to the business layer I would like to know which is the best way for handling both database errors and invalid data Right now, I wrapped connection.open and command.executescalar in a try/catch block with connection.close in the finally statment. Should I not do that? Should I move the try/catch block in my business layer wrapped around the data access layer function call? Btw, what is the difference between connection.close and connection.dispose - some MS examples have connection.close while others have both Where should I do my data validation? For example, should I throw an exception if the executescalar returns a null, that I should catch in the business layer, or should I have an IIf(retVal.IsDBNull, -1, retVal) statement? Is the data access layer supposed to return clean data Thank TM Tag: Help with reading SqlDataReader Tag: 88695

Hi

I am new to vb.net using visual studio. I'm having a hard time trying to figure out the new syntax for vb.net

The following is code is what I'm trying to create in vb.net

Set cnGblBatch = New ADODB.Connectio
cnGblBatch.ConnectionString = "Provider=SQLOLEDB;Server=;Database=;UID=;PWD=;
cnGblBatch.Ope

Set rsBatch = cnGblBatch.Execute("select * from person"

Do While Not rsBatch.EO

sFirstName = rsBatch!FirstName
sMI = rsBatch!M
sLastName = rsBatch!LastNam
sAddr1 = rsBatch!Addres
sCity = rsBatch!Cit
sState = rsBatch!Stat
sZip = rsBatch!Zi

rsBatch.MoveNex

Loo

rsBatch.Clos

Any help would be appreciated

Top

Re: Help with reading SqlDataReader by William

William
Wed Jun 02 19:38:53 CDT 2004

Dim cn as New SqlConnection("ConnectionStringHere")
Dim cmd as New SqlCommand("SQLStatement", cn)
Dim dr as SqlDataReader

dr = cmd.ExecuteReader

While dr.Read
sFirstName = dr.GetString(0) ' 0 is the index of the first columname in
your SQL Statement
sLastName = dr.GetString(1)
End While

"beecherw" <william_beecher@irv.wunderman.com> wrote in message
news:CB489F0E-44AD-4DE0-9575-7153C9357FF3@microsoft.com...
> Hi,
>
> I am new to vb.net using visual studio. I'm having a hard time trying to
figure out the new syntax for vb.net.
>
> The following is code is what I'm trying to create in vb.net:
>
>
> Set cnGblBatch = New ADODB.Connection
> cnGblBatch.ConnectionString =
"Provider=SQLOLEDB;Server=;Database=;UID=;PWD=;"
> cnGblBatch.Open
>
> Set rsBatch = cnGblBatch.Execute("select * from person")
>
> Do While Not rsBatch.EOF
>
> sFirstName = rsBatch!FirstName
> sMI = rsBatch!MI
> sLastName = rsBatch!LastName
> sAddr1 = rsBatch!Address
> sCity = rsBatch!City
> sState = rsBatch!State
> sZip = rsBatch!Zip
>
> rsBatch.MoveNext
>
> Loop
>
> rsBatch.Close
>
>
> Any help would be appreciated!
>

--
W.G. Ryan MVP Windows - Embedded

http://forums.devbuzz.com
http://www.knowdotnet.com/dataaccess.html
http://www.msmvps.com/williamryan/

Top

Re: Help with reading SqlDataReader by Cor

Cor
Thu Jun 03 02:57:07 CDT 2004

Hi Beecherew,

I give an alternative to Bills code because I saw you where using the
recordset method which looks more on the dataset method than the datareader.
The method from Bill works as well.

Dim cn as New SqlConnection("ConnectionStringHere")
Dim cmd as New SqlCommand("SQLStatement", cn)
Dim dr as SqlDataReader
dim ds as new dataset
dim da as new SQLDataAdapter(cmd)
da.fill(ds)
dim dr as datarow = ds.tables(0).rows(0)
sFirstName = dr("FirstName")
sMi = dr("MI")
etc.

I hope this helps?

Cor

Top