data access layer

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
    - Two nested relations, but one is to itself Hi. I have an XSD which describes a set of objects which one is both contained in a parent, as well as referencing itself again (recursively). XSD.exe doesn't like it very much and complains about: "The same table cannot be the child table in two nested relations." I have read all over the web about this, which seems to be a limitation in .Net, but everywhere I read it is regarding an item which is referenced by two different parents. My problem is a little different and I was wondering if anyone has a solution. I have full control over the XSD, but it must be standard (not MS specific) and compliant. My XML looks something like this: <Root> <Main> <Name>Service</Name> <pg> <pg_name>One</pg_name> <pg> <pg_name>One_1</pg_name> </pg> </pg> <pg> <pg_name>Two</pg_name> <pg> <pg_name>Two_1</pg_name> </pg> </pg> </Main> </Root> Both Main and pg are the parents of pg. What XSD can describe this and be .Net friendly? Thanks Tag: data access layer Tag: 109615
  - 2
    - Connecting to the IdX I need information on how to program to retreive information in the indexing service. Can anybody show me when there examples. Thanks in advance Tag: data access layer Tag: 109613
  - 3
    - problem wuth oracle 9i unable to load oramts.dll when i am trying to open the connection it is show the above problem. get me the solution. From http://www.developmentnow.com/g/7_2004_6_0_11_0/dotnet-framework-adonet.htm Posted via DevelopmentNow.com Groups http://www.developmentnow.com Tag: data access layer Tag: 109609
  - 4
    - Could not determine which columns uniquely identify the rows for ..... Ive read about similar problems here. Im using a SQL 2000 db. Im trying to use the VS .NET designer to drag 'n' drop tables from Server explorer to my component and receive this error message. My table is uniquely identified by a primary key, so I dont understand the problem. Do you have any ideas? *** Sent via Developersdex http://www.developersdex.com *** Tag: data access layer Tag: 109608
  - 5
    - oracleparameter returns empty resultset these two parameter field are varchar type in DB. when I pass an empty string in one of the paramters the result set is empty. but if I one parameter is something other than empty string, and the other is existing value in the db, the result is correct. Any idea ? string SQL = "Select * from GAESTEST where MEMBERNR = :MemberNr or NAME = :Name"; OracleCommand Cmd = new OracleCommand(SQL, OraConn); Cmd.CommandType = CommandType.Text; Cmd.Parameters.Add("MemberNr", txtMemberId.Text); Cmd.Parameters.Add("Name", txtName.Text); OraConn.Open(); Tag: data access layer Tag: 109607
  - 6
    - Unable to display child rows I created a dropdown list and a datagrid in a webform. When a customer's name is selected in the dropdown list, a "SelectedIndexChanged" event is raised and the orders for that customer would be displayed in the datagrid. The following code was written but the orders were not displayed after a customer is selected from the dropdown list. Anyone knows what's wrong ? ************************************************ Private Sub lstCustomer_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles lstCustomer.SelectedIndexChanged Dim drNew As New DataRelation("CustOrders", ds.Tables("Customers").Columns("CustomerID"), ds.Tables("Orders").Columns("CustomerID")) Dim strCurrent As String = lstCustomer.SelectedItem.Value Dim dvCustOrders As New DataView(ds.Tables("Customers")) Dim drView As DataRowView dvCustOrders = drView.CreateChildView(drNew) dvCustOrders.RowFilter = "CustomerID='" & strCurrent & "'" dgCustOrders.DataSource = dvCustOrders dgCustOrders.DataBind() End Sub Tag: data access layer Tag: 109602
  - 7
    - time keeps converting itself to date! I am accessing an Access db. I generated the adapter and ds thru the wizard. I have a datetime field in the database (simply called Time) that I am posting to from a textbox. I had to convert it to dattime format, which seems to go fine, but when I execute the update, it strips the time portion and just leaves the date. Here is my conversion code: dr("Time") = DateTime.Parse("9:32 PM") Thanks in advance! Tag: data access layer Tag: 109593
  - 8
    - BCP Invalid collation name I'm trying to load data to an sqlserver table from a comma delimeter file. I'm trying to use the FORMATFILE property. I've bcp out the format of the table so it can be used as the load format. However, when I used the file to import I get error "invalid collation name for source column 3. Here's what the data file looks like. Intelis,LOCK.E23100,3.00000,3.00000,3.00000,3.00000,3.00000,3.00000,3.00000 And here's the bcp out formatfile. I have tried various names for the collation column, but nothing seems to work. 8.0 14 1 SQLCHAR 0 20 "" 1 Entity SQL_Latin1_General_CP1_CI_AS 2 SQLCHAR 0 15 "" 2 Account SQL_Latin1_General_CP1_CI_AS 3 SQLDECIMAL 0 9 "" 3 Period 1 "" 4 SQLNUMERIC 0 9 "" 4 Period 2 "" 5 SQLDECIMAL 0 9 "" 5 Period 3 "" 6 SQLDECIMAL 0 9 "" 6 Period 4 "" 7 SQLDECIMAL 0 9 "" 7 Period 5 "" 8 SQLDECIMAL 0 9 "" 8 Period 6 "" 9 SQLNUMERIC 0 9 "" 9 Period 7 "" 10 SQLDECIMAL 0 9 "" 10 Period 8 "" 11 SQLDECIMAL 0 9 "" 11 Period 9 "" 12 SQLDECIMAL 0 9 "" 12 Period 10 "" 13 SQLDECIMAL 0 9 "" 13 Period 11 "" 14 SQLDECIMAL 0 9 "" 14 Period 12 "" "" -- Hutty Tag: data access layer Tag: 109589
  - 9
    - Filter on child row column Hi I know that to perform a DataView.RowFilter based on a child relation, an aggregation must be performed. But I have a datagrid bound to a DataView (filtered by various columns) of my parent table on which Insert/Update/Delete operations are performed. The parent table has a relationship with it's child on a one to many basis. One of my required filter columns is however in the child table. My tables are in a strongly typed dataset and trips back to the sql server are not permitted. What would be the recommended approach to solving this problem? Any help would be most appreciated. -- Many Thanks Terry Tag: data access layer Tag: 109584
  - 10
    - trouble reading excel with ado.net Whenever I try to read an excel sheet using ado.net, I keep getting this error "Could not find installable ISAM. " I tried what microsoft had to say on this link http://support.microsoft.com/?scid=kb;EN-US;209805 but the problem still persists. Does anyone have any idea why? Tag: data access layer Tag: 109583
  - 11
    - Explicit locking Does SqlDataAdaptor do an explicit table lock when calling Update? We have an importer that connect to SQL Server 2000 and anytime imports are being written to a table and a query is ran against that table at the same time we get a timeout exception. Not locking the table is fine for us because the imports don't effect any user and is only done by one person. Tag: data access layer Tag: 109581
  - 12
    - Error - Deleted row information cannot be accessed through row I am using a dataset containing Parent/Child tables. When I attempt to delete more than a single row, I get the DeletedRowInaccessibleException error on the second row to be deleted. I cannot figure out why. I have looked at the data just before the failed call (identified by ==>). I have verified that the tblDetail.Select method creates a datarow array with the correct number of rows deleted and the data in those rows represent the rows which I deleted (in a grid). The Insert and Update sql commands work as expected. The snippet follows. I left in the code which helped me verify that the deleted data was correct. The method creating the DataAdapter's DeleteCommand is at the bottom. Thanks for any help, Lars ==================================================== Dim cn As New SqlConnection(ConnectionSettings.cnString) Dim daMaster As New SqlDataAdapter("usp_Master_Sel_All", cn) Dim daDetail As New SqlDataAdapter("usp_MasterDetail_Sel_All", cn) Dim tblMaster As DataTable = ds.Tables(0) Dim tblDetail As DataTable = ds.Tables(1) daMaster.InsertCommand = Me.CreateInsertMaster(cn) daMaster.UpdateCommand = Me.CreateUpdateMaster(cn) daMaster.DeleteCommand = Me.CreateDeleteMaster(cn) daDetail.InsertCommand = Me.CreateInsertDetail(cn) daDetail.UpdateCommand = Me.CreateUpdateDetail(cn) daDetail.DeleteCommand = Me.CreateDeleteDetail(cn) cn.Open() Try ' Submit the only new Master/Detail rows daMaster.Update(tblMaster.Select("", "", DataViewRowState.Added)) daDetail.Update(tblDetail.Select("", "", DataViewRowState.Added)) ' Submit the only modified Master/Detail rows daMaster.Update(tblMaster.Select("", "", DataViewRowState.ModifiedCurrent)) daDetail.Update(tblDetail.Select("", "", DataViewRowState.ModifiedCurrent)) ' Submit only deleted Detail then Master rows Dim strx As String Dim dr() As DataRow = tblDetail.Select("", "", DataViewRowState.Deleted) Dim num As Integer = dr.Length() For Each rw As DataRow In dr For Each col As DataColumn In tblDetail.Columns If Not rw.IsNull(col, DataRowVersion.Original) Then strx = rw.Item(col, DataRowVersion.Original) End If Next Next ==>daDetail.Update(tblDetail.Select("", "", DataViewRowState.Deleted)) daMaster.Update(tblMaster.Select("", "", DataViewRowState.Deleted)) Catch ex As SqlException MessageBox.Show(Exceptions.HandleError(ex)) End Try cn.Close() Private Function CreateDeleteDetail(ByVal cn As SqlConnection) As SqlCommand Dim cmd As New SqlCommand("usp_MC_Del", cn) cmd.CommandType = CommandType.StoredProcedure Dim parm As SqlParameter Dim pc As SqlParameterCollection = cmd.Parameters parm = pc.Add("@MCID", SqlDbType.Int, 0, "MCID") parm.SourceVersion = DataRowVersion.Original Return cmd End Function Tag: data access layer Tag: 109573
  - 13
    - Catching Errors with try catch Hi all, I'm trying to catch errors and when running in VS.net 2003 my try catch doesn't seam to catch the error? this error comes up: why does this not catch it? Catch ex As System.Data.SqlClient.SqlException "A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in system.data.dll Additional information: System error." my code is this: Public Function RunSQLcmd(ByVal SQLquery As String, ByVal Msgtxt As String) Dim result As String = String.Empty Dim MessageFrm As StatMess = New StatMess MessageFrm.StatusMessage(Msgtxt) Try ' Reset the cursor to the hourglass postion. Cursor.Current = System.Windows.Forms.Cursors.WaitCursor SQLConnection(loginDatabase, loginIPaddress, "Standard", loginuser, loginpass) Dim MyCmd2 As New SqlCommand(SQLquery) MyCmd2.CommandTimeout = 600 MyCmd2.Connection = SQLConn SQLConn.Open() MyCmd2.ExecuteNonQuery() SQLConn.Close() Application.DoEvents() Catch ex As System.Data.SqlClient.SqlException result = "There was an SqlException.." & vbCrLf & "Details..." & vbCrLf & ex.ToString() Catch ex As InvalidCastException result = "There was an InvalidCastException..." & vbCrLf & "Details..." & vbCrLf & ex.ToString() Catch ex As Exception result = "There was an Unhandled Exception..." & vbCrLf & "Details..." & vbCrLf & ex.ToString() Finally If IsNothing(MessageFrm) = False Then MessageFrm.Dispose() If IsNothing(SQLConn) = False Then If SQLConn.State <> Data.ConnectionState.Closed Then SQLConn.Close() : SQLConn.Dispose() End If MsgBox(result) Cursor.Current = System.Windows.Forms.Cursors.Default MessageFrm.close() End Try ' Reset the cursor to the default for all controls. Cursor.Current = System.Windows.Forms.Cursors.Default SQLConn.Close() MessageFrm.close() End Function thanks Gerry Tag: data access layer Tag: 109572
  - 14
    - Refresh labels in program Hello: I have a Progress Bar in my program and I also wrote some code that will show the Percent completed. I know that the percent complete is actually being calculated but when i run the program it does not show up. Instead when the process is complete it just shows the 100%. Is there a way that the label can get refreshed so it actually shows the percentage change. Should i be using a text box instead? Thank You Prasun Tag: data access layer Tag: 109570
  - 15
    - OracleDataAdapter returns gibberish I'm using Microsoft OracleDataAdapter 1.0.5 with Oracle client 9.2 and Oracle DB 8.1.7 with NLS_LANGUAGE=AMERICAN and NLS_CHARACTERSET, NLS_NCHAR_CHARACTERSET=IW8ISO8859P8 Sometimes, when the result exceeds several hundred rows, I'm receiving gibberish in the lower part of the result. Any suggestions? Thanks in advance, Eli Tag: data access layer Tag: 109563
  - 16
    - SQL Access and COM+ I need a component to reside in COM+ (as it'll be component load balanced). My code is written in VB.Net and the class inherits ServicedComponent. To avoid multiple calls, I have a simple procedure that does all I need in 1 go, ie 1) Gets the connection string from the registry 2) Uses a SQLdataAdapter to populate a dataset. 3) Disposes the command & connection I then use a simple web service to test the procedure, and automate it using Application Test Centre. The throughput doesn;t seem to get above 3 requests per second. This seems pretty poor considering the query runs incredibly quick, and all components (IIS / SQL etc are on my local pc). Any idea how I could identify where the bottleneck is, or perhaps someone could confirm my structure? Tag: data access layer Tag: 109562
  - 17
    - Important -> MDAC and XmlDataDocument Hi! I have a windows forms application and , what I basically do is: 1.- Instanciate a DataSet. 2.- Instanciate a XMlDataDocument attaching the DataSet to it. 3.- Load XmlDataDocument with a local XML document (C:\mydoc.xml) 2.- Bind it to a Datagrid. That is all! My app doesn't need to access SQL Server, or any other ODBC, not even to MS Access.... Is MDAC still required on the target machine in order for this to work? I have not tried it on a machine that does not have MDAC installed so I don't know if it works.... Thanks in advance. -- I love .NET! Tag: data access layer Tag: 109560
  - 18
    - kind of memory leak in dataset/dataadapter update? Hi, we had strange memory usage / memory leak of our web application which we could strip down to a (at least for us) unexpected behaviour of a dataset. Scenario: With application start we open a dataset keeping a reference of some datatables as long as the app runs. During this time we do regular updates of these tables by changing the dataset and then passing it to the dataadapter for update. During update the dataadapter seems to build some help tables and/or structures which are held as long as the dataset exists. Because this is done for every update again and again, the allocated memory sums up to an unacceptable amount. Our first workaround is to regularly copy the dataset to free the old dataset with all related memory. Is this a known behaviour/bug? We use framework 1.1. Is this maybe fixed with SP1? Are there other workarounds for this? best regards Helge Tag: data access layer Tag: 109552
  - 19
    - typed datasets and maintaining object relations Hi, We are implementing Data Transfer Objects (DTO) as Typed DataSets. This means that we would have multiple typed DataSet classes that overlap slightly, sharing tables between them. The issue with typed datasets is that it inherits from the DataSet class and hence a typed dataset class cannot inherit from any other class. For example consider two datasets MemberDS and AgentDS both sharing a Person table between them. By default each generated dataset would contain a schema defining the Person table, meaning that if the table changes, you'll have to change the schema in two different places. Ideally, you need a way to define each type once; no matter how many times your application uses or extends that type. That way, when the shared Person table changes, you'll only have to modify your code in one place. We are planning to write a tool that will take of the issues mentioned above. Your help is welcome. Thanks. Tag: data access layer Tag: 109551
  - 20
    - Help in getting the output parameter from sp This is a multi-part message in MIME format. ------=_NextPart_000_0010_01C54B4F.FDAE8620 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable hi all, i have storedprocedure who takes one input/output parameter the sp also return 2 tables. i m using sqldataadapter. and read the contents of sp in dataset in dataset 2 tables are automatically added by the sp which it returns but how can i get the value of output parameter. please help me thanks Jamil Nawaz ------=_NextPart_000_0010_01C54B4F.FDAE8620 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META http-equiv=3DContent-Type content=3D"text/html; = charset=3Diso-8859-1"> <META content=3D"MSHTML 6.00.2900.2604" name=3DGENERATOR> <STYLE></STYLE> </HEAD><FONT face=3DArial><FONT size=3D2> <BODY> <DIV>hi all,</DIV> <DIV>i have storedprocedure who takes one input/output parameter</DIV> <DIV>the sp also return 2 tables.</DIV> <DIV>i m using sqldataadapter.</DIV> <DIV>and read the contents of sp in dataset</DIV> <DIV>in dataset 2 tables are automatically added by the sp which it=20 returns</DIV> <DIV>but how can i get the value of output parameter.</DIV> <DIV> </DIV> <DIV>please help me</DIV> <DIV> </DIV> <DIV>thanks</DIV> <DIV>Jamil Nawaz</DIV></BODY></HTML></FONT></FONT> ------=_NextPart_000_0010_01C54B4F.FDAE8620-- Tag: data access layer Tag: 109549
  - 21
    - Is it possible to access an ACCESS DB with password protected? Is it possible to access an ACCESS DB with password protected? I have the user/password information of an access db, how can I access it by System.Data.OleDb ? Another question is, is it possible to compact a access db by a System.Data.OleDb ? Thank you very much! ZhangZQ Tag: data access layer Tag: 109541
  - 22
    - ADO.Net 1.1 I had large database about 2GB size and consist of 1 million+ records. I use ADO.Net 1.1 in ASP.Net 1.1 for connection pooling this database and it always prompt "Stack Memory Error". The information request is about 2MB if I running at SQL Query Analyzer. Please advise. Many thanks. Coding ------- Dim okcon As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString")) Dim okcmd As New SqlCommand() okcmd.CommandText = "Select gbaid,account_num,acc_desc,amount from f0902 where gbmcu = " & _ TextBox_Account.Text & " and gblt = '" & DropDownlist_LedgerType.SelectedItem.Value & _ "' and amount <> 0 and dateyear like '%" & DateYear & "%' order by account_num" okcmd.Connection = okcon Try okcon.Open() Dim okrd As SqlDataReader = okcmd.ExecuteReader() myDataGrid.DataSource = okrd myDataGrid.DataBind() okrd.Close() Finally okcon.Close() okcon.Dispose() myDataGrid.DataSource = Nothing myDataGrid.Dispose() End Try Tag: data access layer Tag: 109533
  - 23
    - Parsing DataTable Columns Hello All In my DataTable the following data is returned Object_IDX Object_FullName 48984232 |MS_Net|EUR|Server02 48986409 |MS_Net|EUR|Server21 48990195 |MS_Net|EUR|WKS23 48995964 |MS_Net|EUR|WKS2 48996647 |MS_Net|EUR|Test01 I want to parse out the Object_FullName and create 3 Columns in the DataTable Object_IDX Object_FullName Network Domain Host oDataTable = oDataSet.Tables[0]; oDataTable.Columns.Add("Network",typeof(string)); oDataTable.Columns.Add("Domain",typeof(string)); oDataTable.Columns.Add("Host",typeof(string)); How do populate these Cols? Results Object_IDX Object_FullName Network Domain Host 48984232 |MS_Net|EUR|Server02 MS_Net EUR Server02 Thanks Stuart Tag: data access layer Tag: 109529
  - 24
    - Best Practice for Uploading to SQL Server? Hi there, I have an ArrayList containing approximately 20,000 database INSERT command objects. Each of these commands needs to be executed. The commands do not need to be executed in any particular order, nor do they need to be part of a transaction. I'm currently accomplishing this task by iterating through the ArrayList via the foreach construct and Executing each command. This works fine but I'm wondering if there's a way to get the upload to execute faster. Here's why I ask: During the upload: - The CPU useage on the uploader machine is very low. - The CPU useage on the database machine is very low. - The network bandwidth between machines isn't saturated. Since neither computer is even breathing hard during this operation, it leads me to believe that there is room for optimization. Here's one approach that I'm considering: Store the commands in a Queue instead of an ArrayList. Spawn multiple threads. Each thread attempts to pop a command off the Queue and execute it using its own database connection. The threads will keep working until the Queue is empty. Am I on the right track? If so, how many threads should I spawn? I feel like there should be a way to speed up my upload but I'm not sure how to proceed. I know that there is a program called BCC designed for this sort of thing, but in my case, I want everything to be done in my C# app instead of shelling out to another app. Thanks, The Oracle Tag: data access layer Tag: 109521
  - 25
    - Trying to re-sort a datatable My program grabs a single datatable (dt) from an Excel file, loads it into a dataset (ds) and displays it in a datagrid (dg). The datatable has a couple of "calculated" columns that start out blank. After loading the datattable, the program fills in these blank columns using the values in other columns. Next, I want to re-sort the datatable using the "calculated" columns. I use the following code: Ds.dt.DefaultView.Sort = "calculatedcol1, calculatedcol2" Dg.DataSource() = ds.dt This causes the datagrid to sort the datarows correctly. HOWEVER, I also want to declare a loop that goes through each datarow in the datatable and does something with that datarow. It's important that the datarows are sorted as shown in the code. However, when I do the loop, it's clear that the underlying datatable has not actually been re-sorted. My question is this: Is it possible either to re-sort the actual underlying datatable, OR is it possible to somehow loop through each value of the DefaultView instead of the table itself? Thanks!!!! Tag: data access layer Tag: 109520
- Next
  - 1
    - General Network error I am currently getting a General Network Error were I was not getting one before. The batty thing is the error happens about every other connection to the database. I have looked at the SQLException that gets generated and the error number is 20. Tag: data access layer Tag: 109514
  - 2
    - Inserting data from multiple related tables - need help I'm new to .NET.. I have multiple related tables that I need to insert data into. Lets say for example: Customers and Orders. Orders has a foreign key Customer_Number column in it. I want to use the dataadapter update method to do the inserts for me. Ideally I'd use a stored procedure that will generate a unique id for the Customer Number column on insert. But I can't figure out how will I fill the Customer_Number in the orders table? I really have no clue here, can someone link me to an example of code that does something like this? Thanks! Tom Tag: data access layer Tag: 109513
  - 3
    - single to double quotes to input parameter of a stored procedure...does not work Hi all, I'm using VB.NET for this one, and it is straight forward. I have a sp that takes an input parameter (a string) for querying the db. Here is the sp: CREATE PROCEDURE [dbo].[web_SearchByName] @LastName varchar(80) AS SELECT Parcel.[Parcel ID],[Parcel Number], RTRIM([address Number]) + ' ' + RTRIM([Address Street Name]) As [Address], [Last Name] FROM Parcel INNER JOIN [Ownership] on [Ownership].[Parcel Id]= [Parcel].[Parcel Id] INNER JOIN [Entity] ON [Entity].[Entity ID]=[Ownership].[Entity ID] WHERE [TO DATE] IS NULL and [Primary owner]=1 and [Last Name] LIKE @LastName ORDER BY [Last Name] --REPLACE(@LastName, CHAR(39), CHAR(39) + CHAR(39)) GO Note, I even used the REPLACE statement after LIKE instead of the plain @LastName, but still does not work. In my code, I format the input string to have doubel quotes if there is a single quote: myString = Replace(mysring, "'", "''") and I do verify that the input parameter contains double quotes when running from source code ......but I still do not get results. Any ideas why? I also tried replacing each sinque quote by 4 consecutive single quotes, and nothing. Please your quick will be appreciated. Tag: data access layer Tag: 109511
  - 4
    - Parameters with IN clause. Im using OleDb How do I send a parameter to an In clause ? My SQL is like this. Select name from contact where accountid in (?) accountids are char fields of length 15. Since the number of arguments inside the in clause could vary I cannot go on putting ? for each item in it. I tried creating a coma seperated string of accountids and then tried to send it as a paramerter to the dbcmd. When executing I think the command considers the value of my paramerter as one string instead of an array of values. Im sure someone would have come across this problem. Any help would be appreaicated. -- Satish Tag: data access layer Tag: 109510
  - 5
    - Re-Sorting a filtered Datagrid I've looked everywhere and haven't found anybody with the same problem I am facing. I load a datagrid upon open the web page with data. I have soting by asc/desc working when the user clicks the datagrid headers. The users can also select filtering criteria via dropdowns on the web form. I take that criteria, run a SQL statement and bind the datagrid with the results. Now how do I allow sorting on this filtered data? When the users click the datagrid headers the sorting reverts back to re-sorting all rows that were returned on the page_load not the current, filtered view of the dataset. My DataGrid1_SortCommand code is as follows: Dim dg As DataGrid = DataGrid1 Dim StrSort = dg.Attributes("SortExpression") Dim StrASC = dg.Attributes("SortASC") dg.Attributes("SortExpression") = e.SortExpression dg.Attributes("SortASC") = "yes" If e.SortExpression = StrSort Then If StrASC = "yes" Then dg.Attributes("SortASC") = "no" Else dg.Attributes("SortASC") = "yes" End If End If Dim dt As DataTable = DsLMSProductListings1.Tables(0) Dim dv As DataView = New DataView(dt) dv.Sort = dg.Attributes("SortExpression") If dg.Attributes("SortASC") = "no" Then dv.Sort &= " DESC" End If dg.CurrentPageIndex = 0 SqlDataAdapter1.Fill(DsLMSProductListings1) dg.DataSource = dv dg.DataBind() Thanks in advance for any suggestions! From http://www.developmentnow.com/g/7_2004_10_8_0_0/dotnet-framework-adonet.htm Posted via DevelopmentNow.com Groups http://www.developmentnow.com Tag: data access layer Tag: 109509
  - 6
    - Using AutoIncrement/Identity in DataSet/Database I'm trying to use the general architecture of Duwamish7... the Data layer loads selected rows from several related DataBASE tables into one DataSET object and then returns the DataSET to the business/user layers. The business/user layer then modifies the DataSET and passes it back to the Data layer, which then reconnects and sync's any changes with the DataBASE tables. My question regards how to handle DataBASE (SQL2K) "Identity" columns in the DataSET... Specifically, should those columns in the DataBASE which are "Identity" columns be defined as AutoIncrement columns in the DataSET? If the columns in the DataSET ARE defined as AutoIncrement, then it seems there will be problems loading the existing values of these columns from the DataBASE into the DataSET (ie, the DataSet will want to assign the values itself, and not accept the values already assigned in the DataBASE). On the other hand, if the columns in the DataSET are NOT defined as AutoIncrement, this will create a problem when the business/user layer inserts a row into the DataSET (the column value won't be automatically assigned) . Or is there maybe another scenario, like maybe not using the DataBASE's values for the equivalent columns in the DataSET (and vice-versa). That is, "isolating" the autoincremented columns between the DataBASE and DataSET, and never transferring the auto-assigned values from one to another. Or am I just generally on the wrong track....??? Thanks, DT Tag: data access layer Tag: 109506
  - 7
    - DataAdapter.Fill with multiple connections Hi Folks, I've come across a problem while trying to fill a DataSet. Hopefully someone can help me out. What I have is the need to combine data from several databases. The schema in each DB is the same with the possible exception of unicode vs nounicode data sets. (note: at this time there are no unicode characters in use.) What I do: Build the connection and sql strings for each DB from an XML file of data: e.g. strConnection = "Data Source=MyDataSource;User Id=MyUserName; Password=MyPassword" strSQL = "SELECTcol1, col2,col3 FROM SchemaName.MyTable Where col1 = 'MyVariable" At this time the unicode DB is hit last. Looping through for each connection string I call the funtion to collect the data and fill the Dataset. public void fillDataSet(string strSQL, string strConnection) { try { OracleDataAdapter DataAdapter = new OracleDataAdapter(sqltxt,connectString); DataAdapter.Fill(clientDataSet); DataAdapter.Dispose(); } catch (Exception e) { ErrorMessage.Text = e.Message; } } Now this works perfectly fine if each connection returns data: Connect1 = 1 rows Connect2 = 1 rows Connect3 = 1 rows Dataset = 3 rows ----everything is happy However, if say Connect 2 returns 0 rows, and if I know Connect3 has 1 rows to return. I get: Connect1 = 1 rows Connect2 = 0 rows Connect3 = 0 rows Dataset = 1 rows -- Connect3 data is missing. I do not recieve any exceptions back from this. Hope you can help, Beverly "prgmrblu" Treadwell Tag: data access layer Tag: 109504
  - 8
    - Transaction IsolationLevel problem Hi all, I have an application, which requires several SQL activities, so I wrap them in a transaction: SqlConnection conn = new SqlConnection(connectionString); conn.Open(); SqlTransaction tran = conn.BeginTransaction(IsolationLevel.ReadUncommitted); I use MS Application Block for Data Access to handle all SQL statements: SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sql); I have following 2 problems: 1) When I debug this application from my local machine, in the middle of this transaction, I cannot use my Sql Query Analyzer to do anything against some tables used in this transaction, even though I set transaction's isolation level to read uncommitted. Until I finish my debugging, result will return from query analyzer. 2) I have a common class, and it contains a function shared by some other functions, public bool Audit(SqlTransaction tran) { string sql = "insert into AuditTable ...."; try { SqlHelper.ExecuteNonQuery(tran, CommandType.Text, sql); return true; } catch (Exception ex) { return false; } } Every other function passes its own transaction object to this function. I frequently find dead lock caused by sql statement in this function. Is there any limitation to use transaction object across classes? The other funny thing is, the audit table is used to be inserted only, no other piece of code will read/write to this table, so how comes this relative independent table will cause dead lock? Welcome to any comment. Thanks! -- WWW: http://hardywang.1accesshost.com ICQ: 3359839 yours Hardy Tag: data access layer Tag: 109503
  - 9
    - Error Handling code in Excel Based App Hello: I am trying to handle exceptions in my code. Here the the code i have Code: Dim XLFileConn As New OleDbConnection 'New OleDbConnection object XLFileConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & filename & _ ";Extended Properties=""Excel 8.0;HDR=YES;IMEX=1""" 'New connection string Try Try XLFileConn.Open() 'Open a connection to the choosen Excel file Catch ex As Exception 'Code to detect and handle an exception generated by a connection error MessageBox.Show("Connection to File Failed - Invalid File, File type or Path was chosen.", "File Connection Error", _ MessageBoxButtons.OK, MessageBoxIcon.Error) 'If error occurs - Display message box alerting user about the error Exit Sub 'Exit Sub-Routine End Try I have built the file including the installer and stuff. I run the program and try to test the error handling by inputing a pdf file instead of an excel file, It gives me my message box and after i click OK, it then gives me a default exception handling dialog box and it gives me a quit or continue option. Do i need to clear some kind of error flag? Thank You Prasun Tag: data access layer Tag: 109502
  - 10
    - Outparameter Problem I'm testing OleDbCommand for calling a store procedure in a Sybase DB I'm using an output parameter but always I get DBNull here is my code: Dim conn As New OleDbConnection(connStr) Dim cmd As New OleDbCommand("HO_ProcTest", conn) cmd.CommandType = CommandType.StoredProcedure Dim workParam As OleDbParameter cmd.Parameters.Add("@firstDate", OleDbType.DBTimeStamp) cmd.Parameters("@firstDate").Value = Date.Now workParam = cmd.Parameters.Add("@Out", OleDbType.Integer) workParam.Direction = ParameterDirection.Output Try conn.Open() cmd.ExecuteNonQuery() Dim str = cmd.Parameters("@Out").Value() Catch ex As Exception Finally conn.Close() End Try obviously the store procedure is fine I test it, From my point of view this is a bug. Any Solution?? Thanks Tag: data access layer Tag: 109500
  - 11
    - need some oop Help guys i need some object help here. im just starting to break away from all my bad programming habits and use n' -tier methods. and any book reccommendations are appreciated. i'm down to my last 25 books on asp.net. i'm having good success creating a .dll and using it for my data access layer. i can pass datasets back to the form with no problem. just instantiate the object.method and declare a dataset and im fine. my problem is this. i'd like to create a separate business logic layer. so what used to sit in the 'code behind' and pass values to say 3 - 20 different global variables or possibilities, now needs to be in that separate .dll, handle the value passed to it, then subsequently pass that back to the form to be displayed. ive tried Session,Application,Viewstate & Public variables to pass back and asp.net complains on either the form side or the class side about the non-declaration for that variable. so, what i used to do so comfortably by using a module with global variables and passing 'willy-nilly', im now sort of stuck as to where do i go from here. remember, what i'm trying to accompish can be one of several possibilities, not just one value. that value when examined can go a few places. as i said before, i'm happy with a book recommendation(may have it already). say for an employee that's either on a day off, on holiday or just worked 8 hrs. i pass the value into the function and based on what's there. then it needs to go back to the form 'one of three different places' Public reg as integer ' would like to pass one of these three back to the form Public DOff as string Public Hol as integer Public Function getSomething(ByVal value As String) As String If IsNumeric(value) Then Return reg ElseIf value = "DO" Then Return DOff ElseIf HO_Reverse(value) = "HO" Then ''other func is below Return Hol ElseIf value = "" Then End If End Function '''this just flips it and looks for the text HO - THEN passes it back Function HO_Reverse(ByVal value As String) Dim MyStr, justText As String justText = StrReverse(UCase(value)) MyStr = StrReverse(justText.Substring(0, 2)) Return MyStr End Function any insight into this would be appreciated. im sure im not the only one out there that had to break from the no-tiered into the oop world. right now though, for me its all oops thanks again rik ********************************************************************** Sent via Fuzzy Software @ http://www.fuzzysoftware.com/ Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources... Tag: data access layer Tag: 109495
  - 12
    - Synchronising data b/w MS-Access & SQL Server I have multiple sites where standalone MS-Access database are running at different demographic locations (which some VB application is accessing), what i want is that the data from these sites are copied to some centralized location by any means (i've to make a daemon for this purpose which didn't interfere with the VB application which is accessing MS-Access database) where i'll be able to take some decisions/statistics/etc ... I need a programmatic solution for this approach, can any of you tell me the way with which I can do so? any response is highly appreciated, Thanks. Tag: data access layer Tag: 109490
  - 13
    - MySQL How do I get at a MySQL database? Is there an interface, "SQLClient" type, to get at it somewhere? Thanks... Bruce Tag: data access layer Tag: 109482
  - 14
    - Stored procedure and two databases I need to create stored procedure that transfers data from one database to another. I don't know how to make two connections in the same time - can I do it in stored procedure or can stored procedure take connections as arguments? I'm working in C# and writing procedure in SQL Server. Thanks a lot Tag: data access layer Tag: 109481
  - 15
    - DAAB, Enterprise Lib, connection timeout. Hi there! I'm using the Enterprise Library ver. 2.0 for all data access in my web application but have problems to free up resources though I close readers as stated in the documentation. The error message I get is; "Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached." I have configured my connection pool to have max 5 connections and at least 1 open connection (this for testing purposes). My typical data access code look like the following; public bool Authenticate(string username, string password) { SqlDataReader reader = null; try { Database db = DatabaseFactory.CreateDatabase(); DBCommandWrapper wrapper = db.GetStoredProcCommandWrappe("Authenticate"); wrapper.AddInParameter("username", DbType.String, username); wrapper.AddInParameter("pwd", DbType.String, password); using(reader = (SqlDataReader) db.ExecuteReader(wrapper)) { if(reader.Read()) { return true; } } } catch(ConfigurationException ce) { //TODO Use the Enterprise library Exception handling framework. throw ce; } return false; } I have also tried to close the reader object in a try - finally block instead of using the "using" statement, no difference! In the above code, do you see anything that could cause the problem. It's not necessary to close the underlying connection explict, or is it? Best regards Niclas Tag: data access layer Tag: 109479
  - 16
    - Literature advice Hi, I've been developing in ADO.Net for around 2 years now and all taught via internet tutorials and hands on testing. I wanted to know if anyone could recommend a good book that provides an excellent overview of ADO.Net on issues such as Dataset transactions and concurrency. Also of importance is replication on the compact framework. I would also prefer it if the book was geared towards readers who alread have experience of ADO.Net and are looking to further enhance those skills. If anyone could drop me a few book titles that they have found useful and why I would really appreciate it. Regards, Brian Scott. Tag: data access layer Tag: 109478
  - 17
    - Microsoft Data access application block - static methods Hi, I came across the Microsoft Data access applicaiton block, and noticed that all the methods in the sqlhelper class are static. Suppose if i use the Microsoft DAL block in an ASP.NET application will having static methods affect when muliple users use the applcation. I have read that we need to use lock (C#) keyword if we use static variables in ASP.NET applicaton. Please comment. Raagz Tag: data access layer Tag: 109474
  - 18
    - error aspnet_wp.exe, stopped unexpectedly Hello, I'm wondering if anybody has seen this problem. I basically need to cycle through ~30000 db rows to update the data, I load up the id of the rows I need first, put them into ArrayList, close the connection, then process through one record at a time, so there is no nested transaction. It normally take 1 hour or more, after about 45 min, the aspx page gives up with a server unavailable msg, but the server still goes on in the background for 15 min or more then stop. App event in the server shows this: aspnet_wp.exe (PID: 2400) stopped unexpectedly Does anyone know what the problem is? Thanks! Tag: data access layer Tag: 109458
  - 19
    - InterBase Connection Anyone had any success in connecting with InterBase through ADO.NET? I would like to know how to do it. Tag: data access layer Tag: 109454
  - 20
    - XMlDataDocument and MDAC Hi! I just have a little but important question. If I need to read and XMLDocument (XmlDataDocument) into a DataSet and bind it to a Datagrid.... Does the target computer require MDAC installed in order for this to work? Thanks!!! Tag: data access layer Tag: 109449
  - 21
    - MS Access Queries I know that you can execute a SQL stored procedure in the Command object but can you do the same for a query sored in an MS Access database? Regards Nick Tag: data access layer Tag: 109442
  - 22
    - Bound controls not updating new DataRow Using VB.Net, Framework 1.0, Jet 4.0. Two ComboBoxes (prefixed "cbo") and three DateTimePickers (prefixed "dtp") are bound to a DataSet (m_DS) populated from four hierarchical tables: Company, which is the parent of Invoice, which is the parent of both Expense and Timesheet. These are the bindings: With cboCompany .DataSource = m_DS.Tables("Company") .DisplayMember = "Name" .ValueMember = "IDNum" If .DataBindings.Count = 0 Then .DataBindings.Add("SelectedValue", m_DS, "Company.IDNum") End If End With With cboInvoiceNum .DataSource = m_DS.Tables("Invoice") .DisplayMember = "InvoiceNum" .ValueMember = "IDNum" If .DataBindings.Count = 0 Then .DataBindings.Add("SelectedValue", m_DS, "Invoice.IDNum") End If End With With dtpInvoiceDate If .DataBindings.Count = 0 Then .DataBindings.Add("Text", m_DS, "Invoice.InvoiceDate") End If End With With dtpFrom If .DataBindings.Count = 0 Then .DataBindings.Add("Text", m_DS, "Invoice.FromDate") End If End With With dtpThrough If .DataBindings.Count = 0 Then .DataBindings.Add("Text", m_DS, "Invoice.ThroughDate") End If End With A "New" button calls Me.BindingContext(m_DS, "Invoice").AddNew() and sets cboInvoiceNum, dtpInvoiceDate, dtpFrom, and dtpThrough. A "Save" button calls Dim cm As CurrencyManager = _ CType(Me.BindingContext(m_DS, "Invoice"), CurrencyManager) cm.EndCurrentEdit() If Me.m_DS.HasChanges Then Dim ds As DataSet = Me.m_DS.GetChanges() If Not ds.HasErrors() Then Me.m_daInvoice.Update(ds, "Invoice") Me.m_DS.AcceptChanges() End If End If The new record is inserted in the database, but only the IDNum (an AutoNumber) and the FromDate columns are populated. Why not the others? Any help would be greatly appreciated. -- Many thanks, Tom Tag: data access layer Tag: 109440
  - 23
    - Error clicking datagrid after delete Hi I have a readonly datagrid bound to a relation and a button with the code below in the click event: Dim bm As BindingManagerBase = Me.DataGrid1.BindingContext(Me.DataGrid1.DataSource, Me.DataGrid1.DataMember) Dim dr As DataRow = CType(bm.Current, DataRowView).Row Dim intPos As Integer intPos = bm.Position dr.Delete() dr.EndEdit() If intPos > bm.Count - 1 Then bm.Position = bm.Count - 1 Else bm.Position = intPos End If Me.DataGrid1.Select(bm.Position) Try Me.DataGrid1.Focus() Catch ex As Exception MessageBox.Show(ex.Message) End Try It works fine for all datagrid rows except the last one, for which the following error is thrown by the Me.DataGrid1.Focus() line. Also applied if use clicks as well: "index was outside the bounds of the array" Importantly, the Messagebox in the error trap above is NOT HIT! Any info. would be most appreciated. -- Many Thanks Terry Tag: data access layer Tag: 109430
  - 24
    - How to build an SQL-string from a record Hi, I need a solution for this in SQL Server, or in VB.NET, so for this reason I posted it to the 2 newsgroups. I need to build an SQL String from a given record. For exemple: I have in my table tblMyCows this record: CowID: 1 CowName: Bella (a typical Belgian cowname) CowGender: Female I should have something that generates me the Insert-statement for it: like this: "INSERT INTO tblMyCows (CowID, CowName, CowGender) VALUES (1, 'Bella', 'Female')". If possible the same with an update an delete statement, and it would be really nice if it could detect itself the primary keys, and use them for for the update and Delete statements. Anybody any idea? Thanks a lot in advance, Pieter Tag: data access layer Tag: 109426
  - 25
    - BULK INSERT ADO.NET & ORACLE Hi, I want to do a bulk insert of around 25k records into oracle databse . can anyone help me out with some work arounds, I have used the bulk insert in SQL SERVER , but in ORACLE I am unable to do. your expert advice and support would be highly appreciated. Rgds Shiju Tag: data access layer Tag: 109424

In VS 2003 we used Data access layers intensively.

Is the same pattern valid for VS 2005? Or new data source features ceases
its
existence...

Top

RE: data access layer by NoSpamMgbworld

NoSpamMgbworld
Thu Apr 28 10:12:04 CDT 2005

If you want to switch to drag and drop, the entire data connectivity is
encapsulated in DataSourceControls. This will eliminate the need for a DAL. I
am not fond of drag and drop, so I would say no.

---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

"Tunga Torgal" wrote:

> In VS 2003 we used Data access layers intensively.
>
> Is the same pattern valid for VS 2005? Or new data source features ceases
> its
> existence...
>
>
>
>

Top

Re: data access layer by Sahil

Sahil
Thu Apr 28 10:37:54 CDT 2005

Drag and drop in VS2k5 is much better, but in a real application I'd still
advocate a data layer.

- Sahil Malik [MVP]
http://codebetter.com/blogs/sahil.malik/

"Tunga Torgal" <tungatorgal@hotmail.com> wrote in message
news:uBYkDBATFHA.1152@tk2msftngp13.phx.gbl...
> In VS 2003 we used Data access layers intensively.
>
> Is the same pattern valid for VS 2005? Or new data source features ceases
> its
> existence...
>
>
>

Top