Typed DataSets in MS Access with Stored Procedures

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
    - Problem with Expression in DataColumn Hi, I get errors when I try the following expression in my strongly typed dataset: "Sum(Parent.Child(Relation1).Value)" The error I get is: "Syntax error in aggregate argument: Expecting a single column argument with possible 'Child' qualifier." What I want to do is to navigate from the current row to its parent, and then use one of the parents child relations to do a sum. It seems like navigating more than one step is not supported. Can this be true? regards Carl Tag: Typed DataSets in MS Access with Stored Procedures Tag: 139461
  - 2
    - How does GetSchema get foreign key constraints? The GetSchema documentation hurts, specifically: "Understanding the Provider-Specific Schema Collections" at http://msdn2.microsoft.com/en-us/library/ms254969(VS.80).aspx I'm trying to create a DataSet that matches an SQL Server database, and I've gotten close. I'm calling SqlConnection's GetSchema( "Tables"), and then for each table name, I'm doing: sqlDataAdapter = new SqlDataAdapter( "SELECT * FROM " + dataTableName, sqlConnection); sqlDataAdapter.FillSchema( dataSet, SchemaType.Mapped, dataTableName); Doing so initializes the data tables of the data set, including primary keys. What remains to be done is to create the foreign key constraints. Calling GetSchema( "ForeignKeys") returns the following columns: CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_CATALOG TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE IS_DEFERRABLE INITIALLY_DEFERRED What I expected to see was a local table name, the local field of the foreign key, the referenced table name, and the primary key field of the referenced table. Where is that? Thanks in advance. Tag: Typed DataSets in MS Access with Stored Procedures Tag: 139457
  - 3
    - XSD generated by the xsd tool vs The Designer Hi I created an xsd file by assign a sample xml file to the xsd.exe tool I opened the xsd file in the vs 2005(designer) and saw that the data entities were represented as an element (E sign on the top, left of each data entity) When I create data entities by my self they represented as datatables 1) Why can't I get the XSD by tool in the same way 2) I thought to workaround that problem by create the diagram from the tool based xsd to the manual created digram, but it's not possible(probably because one is elemnt and the other datatable). Is there any other workaround? 3)If I create the diagram by myself, can I be assure tthat xml serialization will give me same structure? Tag: Typed DataSets in MS Access with Stored Procedures Tag: 139456
  - 4
    - TransactionScope Problem Enterprise Library 2.0 We have a web application that calls a business/data layer to do database updates. There is a lot of coordination in the web page to handle id's updated from one class to be used as child id's in other classes. Here's the scenario. Using EnterpriseLibrary 2.0. Surround all class updates with TransactionScope. Call Update on ClassA - works correctly Set ChildID (ForeignKey) on ClassB and Call Update - works correctly Commit Transaction Scope - works correctly. If there are never any problems with the inner update, it works as planned, the problem occurs when there is a problem with the update on ClassB. Call Update on ClassA - works correctly Set ChildID (ForeignKey) on ClassB and Call Update - problem with other data (invalid cast, etc) Dispose Transaction Scope - works correctly. In this case, it works as planned, neither record is saved to db, but when we bring back the page to the user, and they correct the data, on this second attempt is when the problem occurs. Call Update on ClassA - works correctly Set ChildID (ForeignKey) on ClassB and Call Update - problem with other data (invalid cast, etc) Dispose Transaction Scope - works correctly. Display error to user, and they correct bad data Call Update on ClassA - works correctly Set ChildID (ForeignKey) on ClassB and Call Update - Get a FOREIGN KEY constraint violation 'The INSERT statement conflicted with the FOREIGN KEY constraint ...' I'm not sure why the inner update fails the second time because the id generated from the first update is not recognized, like they are in different scopes. fyi, I am using a SCOPE_IDENTITY() to grab the newly inserted id's Tag: Typed DataSets in MS Access with Stored Procedures Tag: 139454
  - 5
    - VersionNotFoundException Hello, I have a DataGridView, bound to a typed DataTable (without any TableAdapter etc.). The values of that DataTable are changed in a second thread using the following code: DataSetOrderManager.DbOrderManagerWlanRow row = dtWlan.FindByHardwareID(HardwareID); if (row != null) row.LastVitalSign = DateTime.Now; row.LastIP = FromIpAddress.ToString(); row.LastAccessPoint = AccessPointMac; } Mostly, this is working fine, but sometimes I get an DataGridView.DataError with the VersionNotFoundException. I think, this happans, because the row(s) are changed in a different thread!?? I don't want to "Invoke" to the main thread, because the row(s) will be changed very often, at worst, maybe 100 times per second! My first attempt was just catching the DataError Event and ignoring the error, but then, sometimes I saw wrong values in the datagrid!! So I added a InvalidateCell in the DataError Event. Well, it seems to work, but I think, avoiding that error is better! Now the questions are: 1. Is it OK what I do in the DataError Event, or may this produce problems? 2. The better way: How to avoid the VersionNotFoundException? Thanks in advance, Robert Witter Tag: Typed DataSets in MS Access with Stored Procedures Tag: 139453
  - 6
    - Need help with Cinema System I have a form called frmSales which has three datagridviews on it which are linked to a Cinema.mdb database using SQL. The first datagridview (Film) displays the FilmNumber(Unique Key) and FullTitle of the Film, the second datagridview (Date) displays the days the film is on for and the third datagridview (Session) displays SessionNo(Unique Key), Time, Cinema and SeatNumbers. I have two buttons on this form called Sales and the other called Return. What I need help with is the code for the buttons so that when the user has selected the three datagridviews and depending on whether Cinema 1 or Cinema 2 was selected in datagridview Session then when either the Sales or Return button is pressed they will be taken to the corresponding Cinema form (In this case either frmCinema1 or frmCinema2). Any help would be appreciated. Cheers, Kryt0n1t3 Tag: Typed DataSets in MS Access with Stored Procedures Tag: 139449
  - 7
    - Need help with Cinema System Hi I am having trouble with a Cinema System I am creating in VB.NET. I have a form called frmSales which has three datagridviews on it which are linked to a Cinema.mdb database using SQL. The first datagridview (Film) displays the FilmNumber(Unique Key) and FullTitle of the Film, the second datagridview (Date) displays the days the film is on for and the third datagridview (Session) displays SessionNo(Unique Key), Time, Cinema and SeatNumbers. I have two buttons on this form called Sales and the other called Return. What I need help with is the code for the buttons so that when the user has selected the three datagridviews and depending on whether Cinema 1 or Cinema 2 was selected in datagridview Session then when either the Sales or Return button is pressed they will be taken to the corresponding Cinema form (In this case either frmCinema1 or frmCinema2). Any help would be appreciated. Cheers, Kryt0n1t3 Tag: Typed DataSets in MS Access with Stored Procedures Tag: 139448
  - 8
    - Problem with Oracle using ADO.NET Hi, I have a problem to call a stored function with a C# application. I have tested the function directly on Oracle Server and the function is well compiled and it runs on SQL*PLUS. I think the problem is due on the RETURN parmeter, because if I create a procedure without a return valule the procedure can be called by my C# application. So the Oracle Function is returning an INT (Oracle). To call it on the console (it runs), I'm using the following code : --------------------------------------------------------------- DECLARE STATUS INT; BEGIN STAUS := MYFUNCTION(); END; ---------------------------------------------------------------- The code inserted in the C# application is the following: ----------------------------------------------------------------- OracleParameter oF = new OracleParameter(); ... oF = cmd.Parameters.Add( "RETOUR", OracleDbType.Int32, ParameterDirection.ReturnValue); cmd.CommandText = procName; cmd.CommandType = CommandType.StoredProcedure; Connection.Open(); cmd.ExecuteNonQuery(); Console.WriteLine(oF.Value); Connection.Close(); ... ----------------------------------------------------------------- When "cmd.ExecuteNonQuery()" is called I obtained the message: ORA-06502: PL/SQL : numeric error or error on a value : conversion error from characters to numbers. If I don't declare the "oF" parameter, I obtain: PLS-00221: 'MYFUNCTION' isn't a procedure or is undefined. If someone had the same problem, thanks to give me more information. Tag: Typed DataSets in MS Access with Stored Procedures Tag: 139445
  - 9
    - Get extern changes back into dataset without completely refill Hello, I have a (typed) dataset for some tables in a sql-server express. Well, loading the datas with Fill and sending changes to the sql server with Update works fine. What I like to do is, sending the changes in the sql server (done by an other instance of my application on a different pc) back into the dataset WITHOUT having to fill the whole dataset again with the Fill-command. My sollution at the time is, that every row has a timestamp value and I ceep track of the highest timestamp value of any table. So I can fill with "WHERE timestamp > oldtimestamp". That works good, but what about deleted rows? Saving them in a own table (cause my own application delets them, I can do this), read that table and remove the deleted rows from dataset... Well, since I'm shure, I'm not the only one wanting to do this, I hope, there is a better, easier, elegant way doing this?? Hoping for help and new ideas, Robert Witter Tag: Typed DataSets in MS Access with Stored Procedures Tag: 139443
  - 10
    - object-relational mapping Hi, We're looking for recommendations for an object-relational mapping product that will work with Microsoft's Data Access Layer and is either free or very reasonable. We have a very complex database and would need a robust product able to map complex objects. We are currently using a custom framework that turns our object "reads" into hundreds of "select" statements. We'd prefer to use a stored procedure approach which is driving our decision to "shop". We've done a tiny bit of research and plan to check out nHibernate and .NetTiers first. We would love feedback from anyone who has used either of these. We're just beginning the search and will be downloading trials/demos, so please save us some time if we should stay away from any really bad ones! thanks, Deanna Tag: Typed DataSets in MS Access with Stored Procedures Tag: 139440
  - 11
    - Renaming files in SSIS Hi, I need to transfer data from a .dat file to different tables in SQL DB. After the transfer i need to rename the .dat file appending current date. I have been successfull in transferfing data. I have problems renaming the file. Can anybody help me with this? Regards, Rocksie *** Sent via Developersdex http://www.developersdex.com *** Tag: Typed DataSets in MS Access with Stored Procedures Tag: 139436
  - 12
    - Help, Oledb connection problem Hi,all I met a strange problem, I connected to access database using oledb connection, connection string like "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=\\Server\Public\QAD\MDB\QADData.mdb;User ID='apiprogram';Password='35msaccessAPI'; Jet OLEDB:Database Password='1';Jet OLEDB:System database=\\Server\Public\QAD\MDB\TFSC_BJ.mdw; Mode=1" In access database, there exist tables linked to antother data server throught ODBC. When exeucting program, I found if there is less records,for example less than 1000, the data read without problem, but if there was large scale records count, the program prompt "odbc error". Why? if I should increase the ODBC Command Time Out or Max Locks Per File properties liking in ADO connection. But in ADO.Net, it does not support this manner, so what shall I do? Tag: Typed DataSets in MS Access with Stored Procedures Tag: 139435
  - 13
    - datareader.getstring(0) doesn't return the whole string. Hi, I am using an Access DB and a datareader to retrieve a memo field. When I execute the following code the datareader only ever returns the first 255 characters, even though I have used a string builder (and a regular string) which I believe can hold more than 255 characters. Is there an issue with the datareader or the memo field of an access database. If I use a dataset and an OleDBDataAdapter then all of the characters (i.e. > 255) are read in and can be viewed in a datagrid. Many thanks, Jason. Dim cmd As New OleDbCommand("SELECT tblBOMS.bBOM, Count(tblBOMS.bBOM) AS CountOfbBOM FROM tblBOMS GROUP BY tblBOMS.bBOM HAVING (((Count(tblBOMS.bBOM))>=2)) ORDER BY Count(tblBOMS.bBOM) DESC;", cn4) Dim f As New FileInfo("C:\Duplicate Boms.txt") Dim s As StreamWriter = f.CreateText() If cn4.State = ConnectionState.Closed Then cn4.Open() End If Dim dr As OleDbDataReader = cmd.ExecuteReader(CommandBehavior.Default) While dr.Read Dim str As New StringBuilder(dr.GetString(0)) s.WriteLine("=============================================================") s.WriteLine(str) s.WriteLine("=============================================================") ' Find all catalogue number with this BOM Dim dv As New DataView dv.Table = ds2.Tables("BOMS") dv.RowFilter = "bBOM like '" & str.ToString() & "'" Dim drv As DataRowView For Each drv In dv Dim strA As String = drv("bName") s.WriteLine(strA) Next End While s.Close() s.Dispose() Tag: Typed DataSets in MS Access with Stored Procedures Tag: 139434
  - 14
    - Prepare yourself for ASP.Net Certification Excellent articles/material for web application (ASP.Net). Complete reference available all in one place. You should visit following URL for vast information about web application. http://www.top-itarticles.com/mcsd/mcsdweb.asp Tag: Typed DataSets in MS Access with Stored Procedures Tag: 139431
  - 15
    - partial merge? Hi, I'm using DataTable.Merge() to merge values from a source DT into a destination DT which are in seperate Datasets. The source DT has columns A,B,C and the destination DT has columns A,B,D. column A is primary key for both of them. So, basically, my target is to copy all the values from column B in the source DT to column B in the destination DT. So far so good (Merge with MissingSchemaAction.Ignore does the job). However, The source DT has 1000 rows and the destination DT has 50 rows. The Merge action copy all the 1000 rows into the destination DT, where I'm only interested in updating the existing 50 rows in the destination DT. (Just update, no Insert). From what I've read, if merge doesn't find a corresponding PK it adds the new rows. Is there any way to go around this with merge? any other way, without using merge? (I tried to save looping over all the rows in one DT and locating them in the other one) Thanks in advance Eran Tag: Typed DataSets in MS Access with Stored Procedures Tag: 139428
  - 16
    - Changing Database Name and Owner (but not Table Name)in a TypedDat I have a TypedDataSet whose source tables live on a SQL Server. The DataTables in the TypedDataSet have lots of customized TableAdapters to execute special SELECTS. Took a lot of work. I have to change the Database name and Owner (schema actually on SQL 2005) but not the name of the tables on the SQL server. This wreaks havoc as you can imagine with the DataSource Wizard. If I add the tables anew to the TypedDataSet, I loose all the modifications to the original DataTables. I want to just modify the definitions of the existing DataTables in the TypedDataSet to reflect the changes in the underlying SQL database. It's not as simple as changing the Connection properties! It seems that the only way to change the Database Name and Owner is to Find/Replace within the .xsd file itself. A bit scary. Is there a better way? Is it safe just to make the suggested replacement? -- Michael Tag: Typed DataSets in MS Access with Stored Procedures Tag: 139425
  - 17
    - OleDb and x64 Hi, Since there *appears* to be no 64-bit support for any data source other than SQL Server (and I wouldn't use OleDb to interface with SQL Server anyway as there's a native .NET data provider for that), what is the "accepted wisdom" for porting 32-bit apps to 64-bit when they require OleDb support...? Can it simply not be done...? E.g. let's say I have an existing 32-bit WinForms app written in VS.NET 2005 (C#) and my client has now asked me to create a 64-bit version, but the existing 32-bit app uses Jet as its back-end database... What are my options...? Maybe the WinForms app is a data consolidation tool which receives remote data feeds in the form of emails with attached Jet databases or Excel spreadsheets...? Jet and Excel remain hugely popular file formats in the corporate world, and that seems unlikely to change in the near future, especially Excel... Do I have to tell my client that, regrettably, it's just not possible to create a 64-bit version of any app which needs to interface with Jet or Excel...? Any assistance gratefully received. Mark -- http://www.markrae.net Tag: Typed DataSets in MS Access with Stored Procedures Tag: 139424
  - 18
    - CFront 3.01 Do somebody know how to connect to CFront 3.01 from a Windows Mobile, Using Visual Basic .net 2003 ? or a tool data acces to Cfront? Tag: Typed DataSets in MS Access with Stored Procedures Tag: 139422
  - 19
    - Query in memory dataset c# 2 Is there a way to query an in-memory dataset with sql or can you only work with the individual table through DataTable.Select()? How can you go about getting the identity of a new record if you use an autoincrement primary key on the datatable? Thanks Tag: Typed DataSets in MS Access with Stored Procedures Tag: 139418
  - 20
    - How to populate an empty table in DB? Hi, I am wondering how I could populate the ampty table in the database from the DataSet or DataTable. So far I am using SqlBulkCopy. Is there any other way, or is it I should be using? I could make Insert row by row, but it is slow, I guess. Thanks, Lubomir Tag: Typed DataSets in MS Access with Stored Procedures Tag: 139414
  - 21
    - SQLCLR Hi, I have a stored procedure, for example like the following: Public static void MyProc() { using ( SqlConnection conn = new SqlConnection(â??Context connection = trueâ??)) { String mySql = â??â?¦..â?? SqlCommand cmd = new SqlCommand (mySql, conn); conn.Open(); cmd.ExecuteNonQuery (); } } I can call this procedure from the application : using ( SqlConnection conn = new SqlConnection(MyConnString)) { SqlCommand cmd = â?¦ cmd.CommandType = CommandType.Storedprocedure; cmd.CommandText = â??MyProcâ??; conn.Open(); cmd.ExecuteNonQuery(); } This code is pretty redundant with the code in the stored procedure. Is there any other way how I could call the store procedure? Thanks, Lubomir Tag: Typed DataSets in MS Access with Stored Procedures Tag: 139412
  - 22
    - SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange Hi everybody, In last month I'm experiencing a very strange problem and I think it's linked to ado.net. My team and I developed a lot of WebApplications and our environment is on .NET 2.0 and Sql Server 2000. (All Service packs have been installed) Most of time (99%) everything is running well, but sometimes (when exactly, we don't know) the same Web Applications (that worked 100000 times) fail. It seems sp doesn't return right fields or something similar. This happens occasionally and, when it occurs, a lot of components have the same problem. In Event Viewer warning messages appear about our libraries like: System.IndexOutOfRangeException: Site_Uid at System.Data.ProviderBase.FieldNameLookup.GetOrdinal(String fieldName) at System.Data.SqlClient.SqlDataReader.GetOrdinal(String name) at USNext.CommonUserControls.ContactInfo.GetLoginSites(SqlConnection sqlCnn) DataBinding: 'System.Data.Common.DbDataRecord' does not contain a property with the name 'name'. I'm pretty sure that the code and the stored procedure are fine also because they are old and executed a lot of times (more than 10000) every day. We started to have this type of problem a couple of months ago, initially sometime then every day around same hour (during the morning, when the server load is high). One month ago I increased the Max Pool Size setting of connection string to 500 and now it happens one time every 7-10 days. When it occurs I check the connections opened on SQL and they are not a lot. The only solutions we found: - Wait a few minutes then everything return to work fine - Restart IIS This post has been opened in Nov 2005 on Microsoft MSDN forums http://forums.microsoft.com/msdn/showpost.aspx?postid=137373&SiteID=1 and it's still open. Does someone know how to solve this problem? Does someone experiencing same problem? Thanks, Emanuele Tag: Typed DataSets in MS Access with Stored Procedures Tag: 139409
  - 23
    - Help, Connection to Access Database Hi,all I met one problem of connecting Access database in asp.net web program. I use ODBC link to Access, the connection string like "PWD=12345;DSN=QADLinkMDB;DriverId=25;MaxBufferSize=2048;FIL=MS Access;PageTimeout=5;UID=apiprogram" while runing program, system prompt error: the workgoup file is missing or open exculsive by anthor user. My access database located in another server,if I copy the Access database to my local place, the program run without problem, but now always display above error. Then what's the problem? Anyone could give me a hand? Thanks! Tag: Typed DataSets in MS Access with Stored Procedures Tag: 139406
  - 24
    - TableAdapter not updating table if the table is empty when loaded? I am having problems with the table adapter not updating (inserting a new record) the sql server table if I open the form with out any records already in the sql server table. If I add on record in sql server management studio, the the table adapter will work correctly, but there has to be at least on record. What is going on?? There are not any exceptions being thrown. I checked the table adapter commands (delete, insert, select, update) and they match the other table adapter that are working correctly. (just different fields and parameters) What else can I check for? Thanks Tag: Typed DataSets in MS Access with Stored Procedures Tag: 139404
  - 25
    - Ole DB Jet 4.0 Provider corrupts recipient Excel database predicta VS2005 C# 2.0 I'm using ADO.NET and the Jet 4.0 OLE DB provider to append a row to an Excel spreadsheet. I have a data table of type System.Data.DataTable, and a table adapter of type System.Data.OleDb.OleDbDataAdapter to fill the data table. These are generated by the VS2005 dataset designer. My connection string, created using the Data Links wizard is Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\FootAnalNovel.xls; Extended Properties="Excel 8.0;HDR=Yes";Persist Security Info=False 1. faAdapter.Fill() to fill the table from the worksheet 2. Create a new empty row for the table. 3. Set values for elements of the row. 4. Add the row to the table. 5. Call faAdapter.Update() to send results back to the spreadsheet In the target spreadsheet I observe: 1. The row was added. The first column was autoincremented as desired. 2. All numeric columns in the sheet, including dates, are correct. 3. The header line has not been modified - that is correct. 4. All text columns in the table have been corrupted. But, in a very particular way: they are filled in row by row by text that was originally in the sheet, in the order that it appeared originally in the sheet. Both originally empty cells and filled cells are overwritten. Thanks in advance for your help. Jim Tag: Typed DataSets in MS Access with Stored Procedures Tag: 139402
- Next
  - 1
    - How do I make lots of changes to a database table using a dataset Hi, I'd like to take a table in a database and modify some rows and delete some others. So I created a dataset, and tried to make modifications to one of its tables, and then I tried to send it back to the database. But I got an error message: "update requires a valid UpdateCommand when passed datarow collection with Modified rows." Here is my code fragment. What's missing? I looked at the Microsoft MSDN pages, but they often seem to explain one method at a time, without showing how to use the methods together to accomplish a task. -- HA Function PlayWithExperimentalTable() As Boolean Dim myOleDbDataAdapter As OleDbDataAdapter Dim myDataSet As DataSet = New DataSet Dim ChangesDataSet As DataSet = New DataSet Dim myDataTable As DataTable = New DataTable Dim myOleDbconnection As OleDbConnection Dim retval As Boolean Dim RowCount As Integer Dim Index As Integer Dim myDisplayRow As DataRow Dim strSQL As String Dim FirstName As String Dim LastName As String Dim Age As Integer strSQL = "Select * from ExperimentalTable" If ConnectToAddinDbase(constStockDatabase, myOleDbconnection) Then Try myOleDbDataAdapter = New OleDbDataAdapter(strSQL, myOleDbconnection) myOleDbDataAdapter.TableMappings.Add("Table", "ExperimentalTable") myOleDbDataAdapter.Fill(myDataSet) myDataTable = myDataSet.Tables("ExperimentalTable") RowCount = myDataTable.Rows.Count If RowCount = 0 Then ErrorBox("No Records found in experimentalTable") Else For Index = 0 To RowCount - 1 myDisplayRow = myDataTable.Rows(Index) FirstName = myDisplayRow("FirstName").ToString LastName = myDisplayRow("lastname").ToString Age = CInt(myDisplayRow("Age")) myDataTable.Rows(Index)("FirstName") = FirstName & "-" & Left(FirstName, 1) & "junior" If Index = 5 Then myDataTable.Rows(Index).Delete() End If Next If myDataSet.HasChanges(DataRowState.Modified Or DataRowState.Added Or DataRowState.Deleted) Then ' Use GetChanges to extract subset. ChangesDataSet = myDataSet.GetChanges(DataRowState.Deleted Or _ DataRowState.Modified Or DataRowState.Added) myOleDbDataAdapter.Update(ChangesDataSet) End If End If retval = True Catch ex As Exception InternalErrorBox("Error Reading From TS2IBSymbol. Internal Message: " & ex.Message & ". routine was 'PlayWithExperimentalTable', sql statement was " & strSQL) retval = False Finally myOleDbconnection.Close() myOleDbconnection = Nothing End Try Return (retval) Else Return (False) End If End Function Tag: Typed DataSets in MS Access with Stored Procedures Tag: 139398
  - 2
    - Merge Replication SQL 2000 and SQL Server Compact I have a desktop application using SQL Server Compact Edition and I want to use merge replication with an SQL Server 2000 database (SP4). I have installed all the necessary things (sqlcesa30.dll is available via IIS and displays the message: "Microsoft SQL Server Compact Edition Server Agent" as expected, when I use a web browser from the desktop to navigate to it). I have configured publishing, without any issues arrising, the publisher and subscriber apparently being created without problem and the snapshot data is entered into the snapshot shared folder. My problem is that I cannot get my VB.NET application to synchronize, I catch the exception thrown by: SqlServerCe.SqlCeReplication.Synchronize(), but I am just getting a blank error message. Can anyone suggest what might be wrong? Thanks. Tag: Typed DataSets in MS Access with Stored Procedures Tag: 139396
  - 3
    - SELECTing only the first n records from a database I want to select only the first n records from a database using VB.NET. I have declared a DataTable and OleDB.OleDbDataAdapter as follows: Dim linkstable As New DataTable Dim linksadapter As New OleDb.OleDbDataAdapter("SELECT * FROM worldnews ORDER BY updated", System.Configuration.ConfigurationManager.AppSettings("connectionstring")) I want to use the Fill method, and I thought the following overload looked like it might work: Fill(startRecord As Integer,maxRecords As Integer,ParamArray dataTables As DataTable()) The documentation describes the parameters as follows: Parameters startRecord A DataTable to fill with records and, if necessary, schema. maxRecords The maximum number of records to retrieve. dataTables One of the CommandBehavior values. The maxRecords parameter is obvious, and I assumed I would just use the DataTable I wanted to fill for the dataTables parameter (in my case, linkstable). However, I was confused by the startRecord parameter because it is an Integer, but the description says that it is a DataTable. What should I do to Fill the DataTable with only the first n records? I will also need a way to Fill the DataTable with all the records other than the first n records. Any ideas? Thanks. -- Nathan Sokalski njsokalski@hotmail.com http://www.nathansokalski.com/ Tag: Typed DataSets in MS Access with Stored Procedures Tag: 139387
  - 4
    - Concurrency control in updating Access records? I'm using OleDbDataAdapter to update records to the Access database. Refer to the "Introduction to Data Concurrency in ADO.NET" topic in MSDN: "The Saving-All-Values Approach" section ms-help://MS.MSDNQTR.v80.en/MS.MSDN.v80/MS.VisualStudio.v80. en/dv_raddata/html/d5293098-4a88-4110-abd2-34d9e6661664.htm The Update command text is in the following format: UPDATE Customers SET CustomerID = @currCustomerID, CompanyName = @currCompanyName, ....... WHERE (CustomerID = @origCustomerID) AND ..... (CompanyName = @origCompanyName OR @origCompanyName IS NULL AND CompanyName IS NULL) ....... ; SELECT CustomerID, ...... FROM Customers WHERE (CustomerID = @currCustomerID) Regarding the named parameter @origCompanyName, there are two occurrences in the statement (similarly for all other columns that allow null). However, since OleDb use question marks (?) as parameter placeholders, adding parameters for the above equivalent of OleDbDataAdapter.UpdateCommand would logically cause the parameter mis-position problem (unless we are allowed to add the same parameter twice gracefully). It seems there's no way to correctly add the same parameters or is there any way to achieve that? Thanks for your help... -- Message posted via DotNetMonster.com http://www.dotnetmonster.com/Uwe/Forums.aspx/dotnet-ado-net/200705/1 Tag: Typed DataSets in MS Access with Stored Procedures Tag: 139386
  - 5
    - data-access approach Hi, Your opinion is welcome on choosing a DAL strategy for our .Net web project. We want a single unified DAL approach, but which one? Our new modules (40% of the code) use the DAL created by the XSD designer for calling SQLs and SPs. Our old modules (60% of the code) connect directly to the DB. The query-designer has good dev speed and automation but it has limits (?): 1) doesn't support returning multiple tables from a SP 2) doesn't supoprty (?) BL-level transactions (such as select, update and an update based on the last update's result). It seems that using the DAAB involves manual coding that takes more time and more bug-risk than the automatic code generated by the XSD query designer. Is it worth the time to replace the direct DB access in our old code with DAAB code? Do you know a good SQL code-generation tool? Can it create automatic runtime update SQLs for optimistic-lock checks of all field values? So what do YOU experts think? Which DAL approach and tools do you recommend based on your experience? Thanks for any input, Ofer Tag: Typed DataSets in MS Access with Stored Procedures Tag: 139385
  - 6
    - SQL Server Stored Procedure Try Catch and ADO.Net exception handli Hi We have recently moved onto SQL 2005 and our DBA wants us to use Try Catch within our stored procedures. If I have an SP such as: CREATE PROCEDURE NoTry AS BEGIN INSERT INTO MyTable(Id, Description) VALUES (3, 'Toodles'); END and I have a primary key on the Id field and record 3 already exists. When I call it from ADO.Net it will result in a SQLException being thrown with the approrpiate Constraint Violation details shown. Now if I wrap it in a Try Catch block like CREATE PROCEDURE TryCatch AS BEGIN BEGIN TRY INSERT INTO MyTable(Id, Description) VALUES (3, 'Toodles'); END TRY BEGIN CATCH -- Log the error / rollback transaction etc. END CATCH END As you would expect the exception is now not raised to ADO.Net as it is handled by the SP. My question is if I need to re-raise the error how do I do it or alternatively how do I signal to ADO.Net that an error has occurred. My first thought was to use RAISEERROR in the CATCH block however this results in a 50000 error not the original error. Based on this my current approach is concatinating all the error information in the message string. Then have code in my .Net Catch block checking if it is a 50000 error and unpacking the exception. An alternative was to simply set the retun value to the error number. My final thought was to ignore TRY CATCH functionality in SQL totally however that seems extreme and self-defeating. Have I missed something and it is possible to Reraise a SQL error? Has anybody got any best practices on the best approach to take? Thanks in advance Phil Tag: Typed DataSets in MS Access with Stored Procedures Tag: 139381
  - 7
    - ReadXML Dataset - Original Rows, RowState = Added (not Unchanged) I realize the original quesiton was dealt with but this appeared to be the most appropriate thread to post my question under. Here is my senario: 1) Connect to DB 2) Fill DataSet with tables, set Relationships, Constraints 3) Save DataSet to file 4) Send DataSet (and additional support files to client from Web Service) 5) Open Dataset (from file) in Client App My problem lies in the fact that once the DataSet is saved to file and then reloaded from the file all RowState values change to "Added" even though no rows have been modified/deleted/added. How can I keep the RowState from changing for the original DataRows that have not been modified by the ClientApp? Tag: Typed DataSets in MS Access with Stored Procedures Tag: 139379
  - 8
    - How to get Column Description from SQL Server Hi, Can I get the column's description in my code, for each columns in the table. 'Column description' is set at the table design time. Thanks. - Aamir Tag: Typed DataSets in MS Access with Stored Procedures Tag: 139376
  - 9
    - Calling code behind without losing values Hi, I'm trying to set up a refresh of some of the data on a page and am using this: <meta http-equiv="refresh" content="120"> This works great with one exception. I only want to refresh one grid on the page, not the entire page. I have the values I don't want to refresh in the "is not page.postback" if statement, but because that line is apparently doing an entire refresh of the page, the if statement doesn't behave like I'd like and all my other data gets refreshed too. How do I set it up so a page is "refreshed" but does not act like a post back so I can refresh just what I want? I would prefer to avoid doing this in javascript if I could. Tag: Typed DataSets in MS Access with Stored Procedures Tag: 139374
  - 10
    - Supporting Transactions A La System.Transactions in a custom system First of all I apologize that this is a dupe of a post tot he Framework.* mailing list, but this seemed like a more appropriate place for it. I'd like to write an ADO.NET provider for a non-relational data store. I'd like to make it support transactions, but since it's a third party tool I'm using to store and retrieve data, and it doesn't 'support transactions' I'd like to know just what exactly that means. I mean, how do I implement an interface layer that does support transactions, and what is required? Is this 'transaction support' simply meaning that it has to have some interface to the pre-built (D)Com Style Distributed Transaction Coordinator, or what? If I were to go about making something that were to 'support transactions' (as everyone says as if there is not a way to actually make something that does so that already does not). Do I have to do some native Code for Com, do I have to register something somewhere? What interface do I have to implement? I certainly hope it doesn't just mean 'you must use SQL, Access, DB2, or Oracle' or something like that, because then that sure as heck closes a lot of doors. Believe it or not there are better ways to store data than the relational model for many applications. So, what's up? Can anyone enlighten me about this? What exactly does it mean (not at a high level, but under the covers) when one says that I must be dealing with a datastore that 'supports transactions' in order to use System.Transactions namespace? Tag: Typed DataSets in MS Access with Stored Procedures Tag: 139373
  - 11
    - TransactionScope - Unable to get error message (6106) hi using the new TransactionScope class in .NET 2.0, we occasionally get an "Unable to get error message (6106) message" returned from .net, after (it seems) 60 seconds. we are connecting to Oracle 9.2.0.5 database, with the same version client. it happens approx 10% of the time. has anyone else seen this? google turned up nothing... thanks cj Tag: Typed DataSets in MS Access with Stored Procedures Tag: 139371
  - 12
    - DefaultValue not set in columns of Data.DataTable VS2005 I use a stored procedure to retrieve data from a SQL Server 2000 dB using Data.SqlClient.SqlDataAdapter to fill a DataTable. In SQL several columns of the table have default values but the DefaultValue of each DataColumn is always System.dbNull (which causes a problem with the InsertCommand). We can fix the problem in the stored procedure for the InsertCommand but that means we have to maintain the column default values in two places (ie the table definition and the InsertCommand stored procedure, actually we probably need to do it for the UpdateCommand SP as well). It's hard to believe that this is true - am I missing something? Tag: Typed DataSets in MS Access with Stored Procedures Tag: 139370
  - 13
    - populating a varchar2 field with .net I am trying to populate a varchar2(1) field in an oracle database by createing a varcharparam in .net - but the field is never getting updated. The values I am sending in through the parameter are either "Y" or "N" - however the field value is always "1" My question is - Is it possible to use varcharparam for varchar2 fields? Tag: Typed DataSets in MS Access with Stored Procedures Tag: 139368
  - 14
    - Cannot read file from Client Hi all: I have an ASP.NET form that reads an Excel file and populates a datagrid. If I load the form in IE on the server, and select a local file, the code works fine. However if I load the form in IE from a client (http://server/readexcel.aspx) and try to read a local Excel file, the file cannot be opened. I imagine it's some kind of a security problem in sending the Excel data back to the web server, but I am not sure. Does anyone know how to fix this? Thanks for any and all help. -- John. http://mscrmguy.blogspot.com/ Tag: Typed DataSets in MS Access with Stored Procedures Tag: 139363
  - 15
    - What's the purpose of "0=1" clause in the Update Command? I use SqlCommandBuilder to auto-generate update commands (INSERT, UPDATE, DELETE) before calling data adapter's Update method. I view the Sql statement using SQL Server Profiler tool. The following is the update statement: exec sp_executesql N'UPDATE [tblEmployee] SET [FirstName] = @p1 WHERE (([ID] = @p2) AND ([FirstName] = @p3) AND ([LastName] = @p4) AND ((@p5 = 1 AND [DepartmentID] IS NULL) OR ([DepartmentID] = @p6)) AND ((@p7 = 1 AND [Salary] IS NULL) OR ([Salary] = @p8)))', N'@p1 varchar(5),@p2 int,@p3 varchar(5),@p4 varchar(7),@p5 int,@p6 int,@p7 int,@p8 money', @p1='Karen',@p2=19,@p3='Katie',@p4='Chideya',@p5=0,@p6=4, @p7=0,@p8=$76000.0000 Refer to the above statement, it seems that every column that allow NULL (in SQL Server table) always come with @p# = 1 (where @p# always equals 0), what's the purpose of it? So what's the different between: 1. ((@p5 = 1 AND [DepartmentID] IS NULL) OR ([DepartmentID] = @p6)) 2. (([DepartmentID] IS NULL) OR ([DepartmentID] = @p6)) Isn't the statement be the same without that (@p# = 1)? I appreciate your assistance... -- Regards, Pedestrian, Penang. Message posted via http://www.dotnetmonster.com Tag: Typed DataSets in MS Access with Stored Procedures Tag: 139352
  - 16
    - 'INSERT INTO' in Access Hi. I wrote the code to insert a new record into an Access database. I get no errors. However a new record is not inserted. Any ideas? Please help. Thanks.Emil. Tag: Typed DataSets in MS Access with Stored Procedures Tag: 139341
  - 17
    - I am trying to use Multiple Column Dropdownlist for Access This is a newbie question. I am using "Multiple Column Dropdownlist for the ASP.NET DataGrid" by Jayarajan S Kulaindevelu (http://www.codeproject.com/aspnet/MultiColDdList.asp) with an Access database but without the DataGrid. I have been using <asp:DropDownList ID="cmbRecordLookup" Style="z-index: 201; left: 200px; position: absolute; top: 203px" runat="server" Width="396px" Height="20px" BackColor="Yellow" Font-Names="Doulos SIL" ForeColor="0" OnSelectedIndexChanged="cmbRecordLookup_SelectedIndexChanged" DataSourceID="AccessDataSource1" DataTextField="forBookID" AutoPostBack="true" AppendDataBoundItems="True"> <asp:List Value="-1">-- Choose a Book ID --</asp:ListItem> </asp:DropDownList> <asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="~/App_Data/pub_db_4_be.mdb" SelectCommand="SELECT Books.BookID & Books.TitleNormalized & Languages.LanguageName & Languages.EthnologueCode AS forBookID FROM Books INNER JOIN Languages ON Languages.LanguageID=Books.LanguageID ORDER BY Books.BookID"> </asp:AccessDataSource> that stores 4 concatenated fields in "forBookID". Three things that I want to have over my old DropDownList: Only one field selected (over the four fields); there are space and a "|" between each field; and when you are going through the dropdown list box it is shorter than the selection (because the selection is just one of those fields). I'm almost pulling my hair out with all the stuff I've tried. I know that you have to get rid of AccessDataSource because mcDDList has DataSource, etc. Could somebody with a knowledge of multiple column dropdownlists help me out of this hole please? Oh, I'm using net 2.0. Scott Tag: Typed DataSets in MS Access with Stored Procedures Tag: 139336
  - 18
    - failed to open a rowset !!!! dear ... I'm using crystal reports 8.5 with vb.net ... and my problem is that when a larg number of parameters passed to the report, using this code : Private Sub ReportsViewer2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load Me.CrystalReportViewer1.ReportSource = source Dim myArrayList As ArrayList = New ArrayList For Each obj As Object In arrayObj myArrayList.Add(CStr(obj)) Next Dim myParameterFields As ParameterFields = CrystalReportViewer1.ParameterFieldInfo SetCurrentValuesForParameterField(myParameterFields, myArrayList) End Sub Private Sub SetCurrentValuesForParameterField(ByVal myParameterFields As ParameterFields, ByVal myArrayList As ArrayList) Dim currentParameterValues As ParameterValues = New ParameterValues For Each submittedValue As Object In myArrayList Dim myParameterDiscreteValue As ParameterDiscreteValue = New ParameterDiscreteValue myParameterDiscreteValue.Value = submittedValue.ToString() currentParameterValues.Add(myParameterDiscreteValue) Next Dim myParameterField As ParameterField = myParameterFields(paramName) myParameterField.CurrentValues = currentParameterValues End Sub when the number of Items in myArrayList is large_ for example 100 item_ I got this error message from crystal report when its trying to open the report: "failed to open a rowset" waiting for your help Tag: Typed DataSets in MS Access with Stored Procedures Tag: 139334
  - 19
    - vbnet Checkbox true or false Hi How to databanding checkbox in windowsform code below does not work Me.txtFax.DataBindings.Add("text", bsAdressen, "Fax") 'Works fine Me.txtEmail.DataBindings.Add("text", bsAdressen, "Email") 'Works fine Me.chkKlant.DataBindings.Add("Checked", bsAdressen, "Klant") 'does not work Data from mssql server 2005 i try data type bit and tinyint Many thanks Scotty Tag: Typed DataSets in MS Access with Stored Procedures Tag: 139331
  - 20
    - What is the max number of tables advised to use with typed datasets? I am helping develop a SQL Server 2005 application with a C# front-end. Originally I was using untyped datasets and stored procedures and it worked fine. Now I have been told to used typed datasets and table adapters. Is it a good idea to use typed datasets on a database with about 25 tables? Typed datasets are much easier in some aspects, but I am finding it difficult to change certain things without the application blowing up, and it takes a lot of effort to maintain the typed datasets if the underlying SQL Server database structure changes (new fields, renamed fields, new constraints, etc.). Any suggestions or opinions would be greatly appreciated. Thanks Tag: Typed DataSets in MS Access with Stored Procedures Tag: 139325
  - 21
    - Best way to get the identity for new records? I am helping develop a SQL Server 2005 application with a C# front-end. Originally I was using untyped datasets and stored procedures and it worked fine. Now I have been told to used typed datasets and table adapters. How do I get the identity for new rerecords? Is it a good idea to use typed datasets on a database with about 25 tables? The issue is that I have multiple datagrids that are dependant on each other and are linked with foreign keys. When I save the records I start with the highest level form and work my way down on the inserts. The identity is not generated until the records is written to the db, but I need the identity for the following child tables. Thanks Tag: Typed DataSets in MS Access with Stored Procedures Tag: 139324
  - 22
    - vb.net take array data and put in combobox I am new to vb.net. I have a multidimensional array that I want to reference and replace current data in a combobox with each line of data from the array in the combox on an click event. I am having issues getting the data to be displayed in the combobox. Any help/ideas?? I can't get it to clear or add. here is what I have, it isn't recognizing the combobox, but not sure why. Private Sub mnuRace1_Click(ByVal sender As System.Object, ByVal e As System. EventArgs) Handles mnuRace1.Click Dim objhorseform As New Form1 Dim cbxHrs As New ComboBox Dim x As Integer Dim count As Integer count = Race01.GetUpperBound(x) Do While count <>0 cbxHrs.Items.Clear cbxHrs.Items.Add(Race01(x)) objhorseform.cbxhorses.Items.Add(cbxHrs) count -= 1 Loop Tag: Typed DataSets in MS Access with Stored Procedures Tag: 139322
  - 23
    - Fun with products and Expression columns I have somewhat of an interesting scenario: The form allows the user to select a service, which populates a a grid of product information related to that service ("service grid"). The user can then select from the products in the first grid and those choices populate the second "choices grid". The product grid is bound to a strongly-typed dataset table which can be filled in a couple of different ways. More on this shortly. The choices datatable contains expression columns related to a product table. So if the user choses Widget A, B, and C, the product prices and a lot of other details for that service are reflected into the choices grid without those values actually being duplicated into the choices table. All of the structure works well technically. But there is a fundamental concept decision to be made. That decision is in how to populate the products table. At some future date, the product catalog may grow to be 50,000, 100,000 or even greater. My instinct is to individually populate the products datatable with ONLY the Widgets that relate to Service1 when it is selected, and then ONLY populate the widgets that releate to Service 2 when it is selected (each service requires a subset of the entire product catalog, thus limiting how big the product table may be). Where this becomes problematic is that the user may need 2 or more Services. So if they select products that relate to Service 1, Service 2, etc., then the catalog to be displayed must grow much bigger. Displaying just one set of products at a time will not work because this would cause a partial display of the expression columns in the choices grid (since the non-populated products still have a primary key back to the products table, they display a row, but not the expression data). The easiest answer would be to just populate the entire catalog in the products grid. Of course, the easiest solution is the most expensive in terms of database and processor resources. The next easiest answer would be to populate the product grid with just one set of products at a time. But this creates the above-mentioned issue with the expression columns. The technical implementation is not an issue. I'm just looking for some ideas or thoughts from anyone who has been down this road with multiple grids and expression columns. Tag: Typed DataSets in MS Access with Stored Procedures Tag: 139315
  - 24
    - Does sorting a dataview cause its datatable to change If I sort a dataview using dataview.sort = "sort string", can the dataview.table change? -- Jerry J Tag: Typed DataSets in MS Access with Stored Procedures Tag: 139312
  - 25
    - Need help in Calling Oracle Function from EntLib Data Access App Block(from c#) I have the following oracle function: FUNCTION GetLoanStatus(LOAN_ID IN VARCHAR2) RETURN VARCHAR2 AS status VARCHAR2; BEGIN SELECT CURRSTATUS INTO status FROM LOAN WHERE AP# = LOANID; RETURN status; END; I have the following c# code: Database db = DatabaseFactory.CreateDatabase(); using (DbCommand cmd = db.GetStoredProcCommand("Test.GetLoanStatus")) { db.AddInParameter(cmd, "LOAN_ID", DbType.String, loanID); object obj = db.ExecuteScalar(cmd); status = (obj == null) ? null : obj.ToString(); } I'm getting the following exception when I try to run:System.Data.OracleClient.OracleException: ORA-06550: line 1, column 7: PLS-00221: 'GETLOANSTATUS' is not a procedure or is undefined ORA-06550: line 1, column 7: PL/SQL: Statement ignored Can anybody please help me to figure out how to call an oracle function from c# using EntLib 3.0 Data Access Application Block? Thanks, Hari. Tag: Typed DataSets in MS Access with Stored Procedures Tag: 139304

I am using VS 2005 and a MS Access 2003 DB. In VS I use the server explorer
to drag and drop tables and existing queries into the dataset designer
window. I am having several problems.

1. In the Server Explorer tables are under the Tables folder but queries are
under the Views folder or if they have parameters they will be under the
Functions folder but never under the Stored Procedures folder. How does the
Server explorer classify MS Access queries?

2. For queries (i.e. "Views" or "Functions") in the dataset designer
whenever I use the "TableAdapter Configuration WIzard" Stored Procedures is
grayed out and only the "Use SQL option is available". How can I make those
other options available?

3. The main problem is queries which already exist in my database. I
cannot seem to fill the DataTableAdapter with any data. There are no errors
but no data either. I tried the same query inside MS Access and it does
return data. The datatable adapter for my tables works fine. I did use the
dataset properties window for the query to change the command type to "stored
procedure" but it still cannot retrieve any data. Why?

By the way if I manually code a non-typed dataset for a "stored procedure"
it works fine.
--
JT

Top

RE: Typed DataSets in MS Access with Stored Procedures by JJT

JJT
Mon May 21 13:20:00 CDT 2007

Since the initial post I discovered the problem was with the fact that in my
Access DB I was linking tables in a SQL Server DB. Although I could populate
the dataset with the linked tables I could not populate the dataset with data
from a query.

I then tried direct assess (read only) to the SQL Server DB and created a
querydatatable. The query wizard would only let me create a query that
returned scalar value. The option to return multiple rows was grayed out.
Why can't I develop a query that will return rows.
--
JT

"JJT" wrote:

> I am using VS 2005 and a MS Access 2003 DB. In VS I use the server explorer
> to drag and drop tables and existing queries into the dataset designer
> window. I am having several problems.
>
> 1. In the Server Explorer tables are under the Tables folder but queries are
> under the Views folder or if they have parameters they will be under the
> Functions folder but never under the Stored Procedures folder. How does the
> Server explorer classify MS Access queries?
>
> 2. For queries (i.e. "Views" or "Functions") in the dataset designer
> whenever I use the "TableAdapter Configuration WIzard" Stored Procedures is
> grayed out and only the "Use SQL option is available". How can I make those
> other options available?
>
> 3. The main problem is queries which already exist in my database. I
> cannot seem to fill the DataTableAdapter with any data. There are no errors
> but no data either. I tried the same query inside MS Access and it does
> return data. The datatable adapter for my tables works fine. I did use the
> dataset properties window for the query to change the command type to "stored
> procedure" but it still cannot retrieve any data. Why?
>
> By the way if I manually code a non-typed dataset for a "stored procedure"
> it works fine.
> --
> JT

Top