Dlookup function

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
    - Reference database application request Hi I am looking for a sample master-detail database winform application that handles multi-user issues like concurrency violations to inspect its code for good db development practices. Would appreciate link to any such app. Many Thanks Regards Tag: Dlookup function Tag: 144503
  - 2
    - What FW version to target Hi I am developing a database app in vs 2008 and I don't need wpf. What fw version do I need to target? Thanks Regards Tag: Dlookup function Tag: 144488
  - 3
    - SqlConnection.ClearPool as best practice on command timeout? If a timeout occurs on a SqlCommand while it is in the middle of executing a transaction contained withing a stored procedure (transaction is started in the stored procedure with BEGIN TRANS, as opposed to being started in the .Net application), database resources can stay locked even after the connection is closed when connection pooling is enabled. However, if SqlConnection.ClearPool is called prior to closing the connection, then the connection is discarded (not returned to the pool) when it is closed. This results in the transaction being rolled back, freeing the database resources. So it would seem that a 'best practice' would be to always call SqlConnection.ClearPool whenver a command timeout occurs. I haven't seen any discussion of this anywhere. Was wondering what other people might think about doing an unconditional ClearPool under these circumstances. (It would be nice if there was some way to detect if there was an open transaction on a connection, in the case where the transaction was started from within a stored procedure, but don't believe this is the case. That way, the ClearPool could be called only when there was actually an open transaction that needed to be rolled back.) Tag: Dlookup function Tag: 144487
  - 4
    - Sample database app Hi I am looking for a sample database app that demonstrates how a reasonably written database app should look like in code. Any recommendations would be appreciated. Many Thanks Regards Tag: Dlookup function Tag: 144483
  - 5
    - retrieving individual row counts from a stored procedure (this is related to my previous post yesterday but I think i'm asking a distinct question so it's a new post) say I have a stored procedure that does 3 updates: update table1 set .... where ... update table2 set .... where ... update table3 set .... where ... if I execute this using management studio, the output window shows (for instance): (1 row(s) affected) (5 row(s) affected) (10 row(s) affected) however, if I execute it in ado.net using ExecuteReader, I just get a single resultset with 16 rows affected. RxecuteNonQuery and ExecuteScalar don't help either. is there any way at all to get the individual row counts from ado.net? Thanks Andy Tag: Dlookup function Tag: 144465
  - 6
    - DataTable.Load() Does the DataTable.Load() method do its operation in one database round-trip when it is passed a DataReader? Tag: Dlookup function Tag: 144448
  - 7
    - How Do I setup a RowChangedEvent in a Typed DataSet Greetings, I have a strongly typed dataset and when I write the identity column to the database, I set it and retrieve it. This works fine. Now I want an event to trigger for each record that is set and retrieved from the DataSet to update the children of the DataSet with the new identity column. The DataSet Created two Classes for the Products Class called ProductsRowChangeEvent - and - ProductsRowChangeEventHandler which will probably do the trick if I knew how to use them, or could find some documentation on these two Classes. Any thoughts would be greatly appreciated. -Devon Tag: Dlookup function Tag: 144447
  - 8
    - returned row count when a trigger is invoked Hi, in my .net application, I am using IDbCommand.ExecuteNonQuery() to update a single row. as a matter of good programming practice, I check the "rows affected" return value to verify that only 1 row was updated. the problem comes if there is a database trigger on the table which does further updates. it seems to add the number of rows updated by the trigger to the rows affected by the original SQL and return me the total. I would like to ignore the count of rows updated by the trigger. is there any way I can either (a) code the trigger so that it doesn't affect the value returned to my program; or (b) get more information about how the "rows updated" count is comprised (to distinguish between rows affected by the original SQL and those affected by the trigger) Many thanks in advance for any clues Andy Tag: Dlookup function Tag: 144445
  - 9
    - MDAC required Hi, I have an application (that I wrote) that use ADO.NET to access an JET (Access) database. My application was working fine, until I installed and uninstalled a trial version of Microsoft Office 2007. I don't know if it was working between the installation and uninstallation of office, because I didn't tried it. Now, it does not work anymore. I get this error (translated from french) : The .Net Framework Date Providers requires Microsoft Data Access Components(MDAC). Install Microsoft Data Access Components (MDAC)2.6 or later. Of course I tried to install MDAC 2.8 SP1, but it says I don't need it since my OS already have. So I looked on the web and found a way to install it anyway. The installation seemed to work, but it did not resolve the problem. I uninstalled en re-installed the .Net Framework. It did not helped either. So the situation is I _seem_ to have a valid installation of MDAC 2.8, but my dotnet 2.0 application keep complaining that it need me to install MDAC 2.6 or higher. What should I try next? In advance, thanks for your help. Mathieu Pagé Tag: Dlookup function Tag: 144442
  - 10
    - Getting Byte[] data with MySQL -Cast(... AS BINARY) Hi, I am trying to get (and transfer over ASP.NET) some encrypted data from some MySQL fields. Since the data contains many unicode characters, I tried to get the data as a series of ASCII values, transfer those numeric values over ASP.NET. I had no problem doing this on my local computer, by getting the field with "cast(field as BINARY)" so that on ASP.NET I have a byte[] array.Then send every field of array over ASP.Net. However when I upload application to remote server, "cast(field as BINARY)" returns field data as String type. I have tried many ways to convert this string data to byte array, however data always become corrupted. So I have to get data as a byte array directly from MySQL. As I've said, I had no problem doing this on my local computer, however, on my local computer I was connecting to same computer using same driver (MySQL Odbc 3.51), so when I upload the ASP.NET page to remote server, ONLY thing that is changing is location and -maybe- ASP.NET version (I am sure both are ASP.NET 2 , maybe different builds) So, the bottom line is: I am trying to get MySQL data as byte[] type, on local web server I have no problem, on remote web server (connecting to SAME MySQL Server) I get data as String, instead of Byte[]. How can I achieve same result on remote server? Tag: Dlookup function Tag: 144438
  - 11
    - Call for speakers for DataServices World (June 24, New York City) Call for Speakers Closes March 15 DataServices World New York City (June 24, 2008) The complexities of distributed processing and the importance of middleware can elude even senior technical people, including enterprise architects, IT managers, consultants and designers. Enterprise developers embraced the separation of presentation, data services and application logic. Internet computing, web services and service-oriented architecture (SOA) continued a trend towards moving data access and data integration logic into a separate data services layer. The data services layer, databases and data access are the focus of DataServices World. We're looking for technical presentations about architecture and solutions for data access and data integration from heterogeneous data sources including SQL tables, geo-coded data, content management systems, enterprise resource planning (ERP) systems, data warehouses, and web data. Topics of interest include: · End-to-end performance optimization: (database, metadata, data distribution, caching, query tuning, load balancing, protocols, data services) · Information as a service · Data services development: architecture, tools, LINQ, ADO.NET Entity Framework, Astoria · Essential capabilities for effective, robust, secure data services If you're interested in speaking, you must submit a proposal by March 15, 2008. Curriculum sketch and more information: http://dataservicesworld.sys-con.com/general/papers0608.htm ======== Ken North =========== www.KNComputing.com www.WebServicesSummit.com www.SQLSummit.com www.GridSummit.com Tag: Dlookup function Tag: 144437
  - 12
    - DataAdapter and DataSet in Disconnected Mode I am new to ADO.Net and trying to use DataAdapter & DataSet. I read the data from database table into dataset using DataAdapter. Do I have to use the same DataAdapter object to update and commit the changes made to DataSet? I will appreciate any pointers to articles / documentations. Regards, Abdul Tag: Dlookup function Tag: 144433
  - 13
    - Get error raised from sql command? Is there a way to get an error that is raised from a sql server stored procedure when executed from a sqlcommand object? thanks! Tag: Dlookup function Tag: 144429
  - 14
    - Silly ODBC Connection problem I have an Oracle database running on Windows Server 2003, for which I have created a System DSN named "MyDSN" in the ODBC Administrator tool. I can test the connection without problems in the ODBC Administrator, however when I use conn = gcnew OdbcConnection("DSN=MyDSN;UID=User;PWD=Pass"); It gives me the error "ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified" I am at a loss, the DSN is defined as a System DSN which can be succesfully tested with the same user and password, yet through the above code I cannot get a connection. Any thoughts would be hugely appreciated Cheers Justin Tag: Dlookup function Tag: 144426
  - 15
    - SQLHelper & DataGridView I am trying to populate a MS DataGridView with Stored Procedure using SQLHelper. I am connecting to a FireBird database. I'm also using SQLHelper for FireBird. For all intents and purposes the code is the same for SQL Server. The DataSource property is set to use the SP and I have changed the ObjectType to int to reflect the ParameterType. When I supply the parameter and PreviewData, the SP executes as expected. However, when I run the program the DataGridView displays with the correct column header text but the data is missing. Sample code follows: FbParameter[] rsParms = new FbParameter[3]; // @ID Input Parameter rsParms[0] = new FbParameter("@ID", FbDbType.Integer, 0); rsParms[0].Direction = ParameterDirection.Input; rsParms[0].Value = inComingID; //int value supplied by the user via the form // @NAME Output Parameter rsParms[1] = new FbParameter("@NAME", FbDbType.Integer); rsParms[1].Direction = ParameterDirection.Output; // @ADDRESS Output Parameter rsParms[2] = new FbParameter("@ADDRESS", FbDbType.Integer); rsParms[2].Direction = ParameterDirection.Output; SqlHelper.ExecuteReader(Properties.Settings.Default.dbConnection, CommandType.StoredProcedure, "SP_CONTACTS",rsParms); I am clueless. All help greatly appreciated. From http://www.developmentnow.com/g/7_2005_12_22_0_0/dotnet-framework-adonet.ht Posted via DevelopmentNow.com Group http://www.developmentnow.com Tag: Dlookup function Tag: 144425
  - 16
    - How to install .NET wrapper for old ADODB dll? Hello, does anyone know how to get a .NET wrapper assembly for old ADODB? In C:\Windows\assemby I do not have the assembly ADODB version 7.0.3300.0. So I have to install the assembly explicitely. Any ideas? Thanks in advance, Norbert Tag: Dlookup function Tag: 144420
  - 17
    - Data migration questions? I am about to develop code to migrate data from an old application to our new one. But first, I have a couple of questions: First, should I use a DataSet to bring the data down to the local machine that will run the code and execute the transformation logic from it. Considerations: The source database is about 100 Gb, and one of the tables includes an Image column. Second, will I get better performance by using Stored Procedures or Views. Thanks in advance, Mervin Williams Tag: Dlookup function Tag: 144417
  - 18
    - datacolumn datetime from UTC to Localtime Hi, VB.net 2005 CLR 2.0 I have a dataset with a datatable...there is one datetime field, and about 30 double fields. The datetime field is in UTC Time, but my client application needs to see this in localtime. The datetime field is accurate to the millisecond... The data source is a flatfile (not a db) [tabbed delimited], thus, I can't easily use the functionality of a server to return the gernerated column I need (utc to local time) I'm using the ms text driver, and Schema.ini to get the data into my datatable, and this works fine. Is there a way to add a datacolumn to my datatable (type datetime) that uses an expression and convert the utc time in the orig field to localtime in a new field? thanks, george jetson On the side, against sqlserver 2000 in ado.net, I can't seem to write a query against a view to return some rows when the timestamp has millisecond resolution (When I don't use this resolution, it works) i.e.: SELECT myField from myTable where myTime = '3/6/2008 9:30:00' WORKS i.e.: SELECT myField from myTable where myTime = '3/6/2008 9:30:00.723' DOES NOT WORK -- thanks in advance, georgejetson Tag: Dlookup function Tag: 144410
  - 19
    - Get Different values in gridview for Edit Btn (wrong CommandArgume I am at wits end trying to figure this out!! Basically i have a GridView in a UserControl. I assign the pk of the item bound to it via a LinkButton in a template. All works fine until i raise an event from the usercontrol, change the data from the calling aspx page and rebind it. By doing so, the LinkButton has the WRONG CommandArgument - to confuse things even further i tested by adding a button field that is bound to the same value -- the text output of this is correct, however the CommandArgument in the linkbutton is not - how is that even possible? Here is the buttonField and the LinkButton, both of which get diffrent values for pkContentPage <asp:ButtonField CommandName="Edit" DataTextField="pkContentPage" /> <asp:LinkButton ID="LinkButton1" runat="server" CausesValidation="false" CommandArgument='<%# DataBinder.Eval(Container.DataItem, "pkContentPage") %>' CommandName="Edit" Text="Edit" </asp:LinkButton> Any help would be greatly appreciated Tag: Dlookup function Tag: 144406
  - 20
    - Batch of Stored Procedures In our .Net 2.0 web service we need to insert/update data records into SQL Server 2005 that have been uploaded to us in XML documents. There could be anywhere from one to literally thousands of records in each uploaded transaction, and there are several dozen destination tables. Since performance is critical for this particular app, we were thinking of combining the stored procedure calls into configurable- sized batches using CommandType.Text and multiple concatenated EXEC statements, so we minimize database roundtrips as opposed to calling each SP individually in its own SqlCommand. Is this a good approach? One concern is that SQL Server would have to compile each and every batch since they will all be different, and the overhead involved in this could become significant. Is this really something to worry about, or not? Wouldn't a few batch compiles per transaction still be better than potentially thousands of roundtrips? Are there other problems with this approach? Of course we are planning to stress test to see how it actually responds, but I just wanted to get some opinions first in case someone has done this before and can give advice/recommendations/suggestions before we get too far into it. Thanks! MJ Tag: Dlookup function Tag: 144405
  - 21
    - ODBCCommandBuilder produces different UpdateCommand with Oracle using If I use a command that contains field name aliases, I get a different result for _cmdBldr.GetUpdateCommand().CommandText, depending on whether I am connected to SQL Server 2005 or Oracle 10g. Here is the code in question: string _sqlString = "SELECT ID, PLANGROUP AS PG, DESC_R FROM TABLEA WHERE ID = 100002"; OdbcConnection _dc = new OdbcConnection(); _dc.ConnectionString = "<connectionstring>"; _dc.Open(); OdbcDataAdapter _da = new OdbcDataAdapter(_sqlString, _dc); DataTable _dt = new DataTable(); _da.Fill(_dt); _dt.Rows[0].BeginEdit(); _dt.Rows[0]["DESC_R"] = "ABC"; _dt.Rows[0].EndEdit(); OdbcCommandBuilder _cmdBldr = new OdbcCommandBuilder(_da); MessageBox.Show(_cmdBldr.GetUpdateCommand().CommandText); try { _da.Update(_dt); } catch(Exception ex) { } When I run this for a SQL Server connection, the _da.Update(_dt); command executes without an exception and _cmdBldr.GetUpdateCommand().CommandText returns: "UPDATE TABLEA SET ID = ?, PLANGROUP = ?, DESC_R = ? WHERE ((ID = ?) AND ((? = 1 AND PLANGROUP IS NULL) OR (PLANGROUP = ?)) AND ((? = 1 AND DESC_R IS NULL) OR (DESC_R = ?)))" When I run this for an Oracle connection the _da.Update(_dt); command generates an exception: "ERROR [42S22] [Oracle][ODBC][Ora]ORA-00904: "PG": invalid identifier" and _cmdBldr.GetUpdateCommand().CommandText returns: "UPDATE TABLEA SET ID = ?, PG = ?, DESC_R = ? WHERE ((ID = ?) AND ((? = 1 AND PG IS NULL) OR (PG = ?)) AND ((? = 1 AND DESC_R IS NULL) OR (DESC_R = ?)))" Note that in SQL Server, the original field name "PLANGROUP" is used in the UpdateCommand. In Oracle, the field name alias "PG" is used. Is there a way to force the command builder to create an update command with the orginal field name when using Oracle? Is there another approach I should be using, other than always creating my own update command from scratch for Oracle? Thanks. Tag: Dlookup function Tag: 144403
  - 22
    - How to connect to a database through code? Beginner question: Can anyone point me in the right direction for learning (url's, books, etc.) how to connect to an sql or access database through code rather than using typed datasets? What I am most interested in is sample code that would connect to say the Northwind database and allow a user to insert, delete, update data from the tables. I want the user to first select the location of the database he wants to connect to, then to load my datagridview with the datasource/ tableadapter etc. Thanks for all responses. Peter Tag: Dlookup function Tag: 144401
  - 23
    - Transactions in ADO.NET or SQL SP What is the best approach? I've created a .NET 2.0 windows service which queries & updates data in SQL using Stored Procedures. Note, this windows service spawns a few worker threads which query/update the database - thus ending up with multiple simultaneous database connections (queries/updates). Currently I've implemented transactions in the SQL SP (also using XACT_ABORT, etc...), as WELL as the ADO.NET layer. However, I've been told that its best to implement it just at the ADO.NET layer. The more I think of this, the more sense it makes. Surely there's a best pratice for this! Tag: Dlookup function Tag: 144399
  - 24
    - Scope_Identity() Dear, how can I get the scope_identity after inserting records into the database <System.ComponentModel.DataObjectMethod(ComponentModel.DataObjectMethodType.Select, True)> _ Public Function Insertdata(@Name, @firstname) as integer return adapter.insert(@name,@firstname) end function The return statement only returns 1 incase the insert was successful........this while I'm interested to get the last Id from this insert. your help is most appreciated!! -- Best regards Nicole Tag: Dlookup function Tag: 144382
  - 25
    - Overriding dataset connection Hi I have a windows form app with one master table and several details (grids). As I am filling both the master and details using vs 2005 generated dataset and data adapters, there are multiple connection open/close during the whole operation (one connection open/close per adapter fill). Is there a way to override the vs generated connection management code to open the connection only once at start, do all fills and then close the connection at the end, to speed up the whole fill process? As dumping the vs 2008 dataset altogether will involve too much code writing, I am reluctant to let go of it and would prefer to only override the connection management if I can. Many Thanks Regards Tag: Dlookup function Tag: 144363
- Next
  - 1
    - ado.net data access performance Hi I am using a strongly typed dataset created by vs 2008. I have added a query to select next record to the data adapter as below; SELECT TOP 1 <field list> FROM MyTable WHERE (ID > ?) ORDER BY ID ? is replaced by the ID of the current record. My problem is when I fill using this query there is a noticeable about half a second delay before data is retrieved. Is there any way to improve the performance of this query? Thanks Regards Tag: Dlookup function Tag: 144357
  - 2
    - Is getting a column by name slower then ordinal? If I am doing a very large loop in a data reader say 20,000 rows being read in... now I need to read data so I am doing the reader.Get[Type](reader.GetOrdinal("[columnname]"))... which seems like it'd be slow doing that x number of times per row... is it better to use the ordinal by itself? does looking up an ordinal have any major performance impact? should I look them up once and cache them in variables? what do you guys thinks? thanks! Tag: Dlookup function Tag: 144351
  - 3
    - sqlBulkCopy Hi. I have some VS 2005 VB.NET code that uses the writeToServer method of a sqlBulkCopy object to write records in a dataTable to the SQL server. The code works fine against our SQL Server 2000 database, but we get a 'login failed' error against 2005. The DBA says the account on 2005 has dbOwner permissions and bulk administrator permissions. I'm not setting any of the copyOptions of the writeToServer method. Any ideas for what to try to get this to work on our 2005 server? Thanks, -Beth Tag: Dlookup function Tag: 144348
  - 4
    - DataTextFormatField: {0:d/60} ??? Hi over there, I've got a DataBoundField in a GridView which name is (both gridview-column and the column in the database) "TimeInMinutes". And I want another column, that displays the hours, means the same value as in the TimeInMinutes-Column divided through 60. I tried to achieve that with the DataTextFormat field, but I've got no luck. Is that possible anyway, or do I have to do that by manual by code? DataTextField: TimeInMinutes DataTextFormatField: {0:d/60} ??? How can I achieve that I've got another column in my grid view, that is not existent in the database, but visible in the gridview, with the value of the database-column "TimeInMinutes" displayed in hours? any help appreciated, cheers, Markus Kofler Tag: Dlookup function Tag: 144346
  - 5
    - 2008 Express Edition missing Data drivers? I just started doing some basic exercises with Visual Basic 2008 Express Edition (Version 9.0.21022.8 RTM, Microsoft .NET Framework Version 3.5) and I do not seem to have what I would call routine data drivers installed. Initially, I only want to connect to a Northwinds db in my local SQL Server 2000 instance but the only drivers available are Microsoft Access DataBase file, Microsoft Sql Server Compact 3.5, and Microsoft SQL Server Database File. I don't need Access and the other two don't seem to create db connections in a manner I am familiar with. What am I missing and how do I add it? Or is it impossible to do 'normal' database connections in the Express version? Tag: Dlookup function Tag: 144345
  - 6
    - Wierd error on data set Occassionaly (and its rare, but i think there are repo steps to this, i just cant completely determin them, but here is what I figured out so far) I have a data set (say its called DS) I have three tables... call them people, peoplejunitem and item... peoplejunitem is a many to many junction table between people and items... well say I load one set of information about a person into the data set's tables... ok this person has items in the junction... say I alter the information in the junction but dont commit it to the database... now I go and load another person... but while loading the next person it clears the information in the junction table and the person table and replaces it with the 2nd person... ok I make changes and push the data back to the database... and boom error... zero rows updated when 1 was expected to be... well that one row updates was from the FIRST! person... even though I did a ds.table("PersonJunItems").rows.clear somehow it still thinks it has rows to push! why?! this happens randomly to me! I tested out the T-SQL statements like crazy and they are not at fault, they are return the correct response, but while watching the SQL Server profiler I saw the commands to update the pervious person going through on person 2 to the database... any ideas how to stop this or fix the problem? or anyone ever see anything similar to this? thanks! Tag: Dlookup function Tag: 144342
  - 7
    - Open file suddenly stops existing?! A file is successfully opened, partly read, and not closed, but suddenly it stops existing? How does Ado.Net accomplish this result? This seems to be a 100% repro on one file but 100% no problem on a different file. Again how does Ado.Net do this? // Sample dirName is C:\myDir and tableName is myFile1.csv or myFile2.csv. OleDbConnection connection = new OleDbConnection( "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dirName + ";Extended Properties='Text;Extensions=asc,csv,tab,txt;" + "HDR=Yes;FMT=Delimited'"); connection.Open(); OleDbDataAdapter dataAdapter = new OleDbDataAdapter( "SELECT * FROM [" + tableName + "]", connection); DataTable dataTable = new DataTable(tableName); dataAdapter.FillSchema(dataTable, SchemaType.Source); DataColumnCollection dataColumns = dataTable.Columns; int fileColumnCount = dataColumns.Count; foreach (DataColumn dataColumn in dataColumns) { dataColumn.DataType = typeof(string); } // This works up to here. FillSchema worked. // The next call works on myFile1.csv but fails on myFile2.csv. dataAdapter.Fill(dataTable); // Exception: file does not exist. Wtf? Tag: Dlookup function Tag: 144338
  - 8
    - Returning the id of an inserted record I am writing an ASP.NET application, and am forced to use Access as my database. I need a way to return the id (which is an AutoNumber field in the table I am inserting into) of a record when I insert it. In SQL Server, I am able to use a statement like the following: INSERT INTO tablename (fieldnames) OUTPUT inserted.id VALUES(valuestoinsert) Take note of the OUTPUT inserted.id part of this statement. This returns the value of the field named 'id' for the record that was just inserted. Unfortunately, this technique does not work in Access. Is there any way to retrieve the id of the record that was just inserted when using Access? Any help would be appreciated. Thanks. -- Nathan Sokalski njsokalski@hotmail.com http://www.nathansokalski.com/ Tag: Dlookup function Tag: 144336
  - 9
    - Logging exceptions to Database using Enterprise Library 3.1 Can somebody direct me to an example of logging exceptions to SQL Server Database using Enterprise Library 3.1 Exception Handling block. I created database listener. But it's doing nothing. Thank You Reddy Tag: Dlookup function Tag: 144331
  - 10
    - How to solve DataSet Designer SQL errors? Hi there, I have writing easy to moderate SQL in the DataSet Designer and Query Builder, using a Jet 4.0 connection to an Access 2003 DB. I am running VB.NET 2005 sp1. As soon as I try to do anything more than the most trivial SQL (e.g. simple JOINs, more than 1 parameter other than in the WHERE clause, etc.), the Designer/Query Builder balks giving SQL parsing error messages. This means that the Fill, GetData() is not set up properly and compiler errors prevent me from running my app. This is despite the fact that the SQL is perfectly fine (it runs in Access) and in some situations it is possible to 'trick' the Designer (ref. link below). Aside from the critical issue of my app not running, I am not able to leverage strongly typed DataSets, and all the useful commensurate objects (e.g. DataTable, data row). I have been promoting this approach within our development teams, but all these problems make significantly reduce confidence in using these objects and the Designer. Nor it is sensible to use a blend of approaches in my app (e.g. trivial SQL through strongly-typed DataSets and the rest explicitly written in code) from a software design/maintenance perspective. If this can't be solved, I'll look at dropping use of the Designer and going back to hand-coded SQL. Any and all feedback that provides solutions to this problem (e.g. is there an update/patch to the Designer?) would be appreciated. Thanks in advance, David Newsgroup posting "Dataset designer and complex Stored procedure" on microsoft.public.dotnet.framework.adonet http://groups.google.ca/group/microsoft.public.dotnet.framework.adonet/browse_thread/thread/9c0a1938b49329ea/68917fe53b75058c?lnk=st&q=%22dataset+designer%22+sql+error#68917fe53b75058c Tag: Dlookup function Tag: 144304
  - 11
    - OleDbCommandBuilder insert query will not work? dataset to Access I am trying to take a very strangely delimited text file and put it into access. So far I was able to get the text file into a dataset and figured from there I could get it into access. I am trying to use the code below but the insert query(oledbcommandbuilder) just will not work. I am working with a blank access database and I am able to get the table and the field names in the database(with a primary key just set ot the first row). I just cannot get the rest of the data. I get an error that there is incorrect syntax in the insert query. Can someone please help! I've been all over the site reading a buch of different things and tried to just write inserts for each row but cannot get that to work either. Thank you! DataSet result = new DataSet(); ...get delimited file into dataset... OleDbConnection DB = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\\Conversions\\" + agencyid + "\\" + agencyid + ".mdb"); OleDbDataAdapter DataAdapter = new OleDbDataAdapter(); DataAdapter.SelectCommand = new OleDbCommand("SELECT * FROM " + TableName, DB); OleDbCommandBuilder CmdBuilder = new OleDbCommandBuilder(DataAdapter); DB.Open(); DataAdapter.FillSchema(result, SchemaType.Source, TableName); DataAdapter.InsertCommand = CmdBuilder.GetInsertCommand(); //I added the line above because the insert query did not show in the data adapter. //It now has: insert into tablename(fieldname1,fieldname2...) Values (?,?,?...) DataAdapter.Fill(result, TableName); DataAdapter.Update(result, TableName);//Fails here on the insert query syntax DB.Close(); Tag: Dlookup function Tag: 144295
  - 12
    - timeout error Hi, I've a method in my web service..which makes a call to db and sends back a dataset.. the query takes 2 mins to fetch the results..by that time i'm seeing a 'operation has timed out error' on my screen.. is there any to get rid of this error? Tag: Dlookup function Tag: 144291
  - 13
    - Problem with AdomdCommand with passing DataTable with DBNull in some cells The following code would fail, with the error: "The '' string cannot be converted to the double type." when trainTable contains rows with DBNull in some of the columns. How can I pass NULL to MSAS? using (AdomdCommand comm = connAS.CreateCommand()) { comm.CommandText = "INSERT INTO MINING MODEL [MyModel] ([statbbrev],[lpop],[ldem],[lemp]) @TrainTable"; comm.Parameters.Add("TrainTable", trainTable); comm.ExecuteNonQuery(); } Tag: Dlookup function Tag: 144284
  - 14
    - Problem with AdomdCommand when Passing DataTable with DBNull's The following code would fail, with the error: "The '' string cannot be converted to the double type." when trainTable contains rows with DBNull in some of the columns. How can I pass NULL to MSAS? using (AdomdCommand comm = connAS.CreateCommand()) { comm.CommandText = "INSERT INTO MINING MODEL [MyModel] ([statbbrev],[lpop],[ldem],[lemp]) @TrainTable"; comm.Parameters.Add("TrainTable", trainTable); comm.ExecuteNonQuery(); } Tag: Dlookup function Tag: 144283
  - 15
    - ORA-01406: fetched column value was truncated Using data reader against one of my Oracle tables is crashing with following error: System.Data.OracleClient.OracleException: ORA-01406: fetched column value was truncated. This happens with Microsoft ADO driver to Oracle. Oracle ADO driver works just fine. Error happens only with some records (there are some pretty large columns in a table), but not other. I wonder if anyone has any insights to this issue â?? we donâ??t want to compile Oracle drivers into our base applications, because most of our customers are using SQL Server. Thanks in advance. Tag: Dlookup function Tag: 144282
  - 16
    - Need some advice on my Deployment Pkg Hello. Using XP Pro w/SP4 and VS 2003. I wrote my program with the connection string and the path for loading the Maps as follows: C:\MVB.NDbProJim\Ch7\ROE6AllRecords2\Maps. The Maps sub-directory containes all 116 Maps of the Lots and also the ROE6.mdb database. But when I deplay this app to my other PC it gets an error that this path is invalid so it cannot even load the database. Since the MSI installs this package to C:\Program Files\Microsoft\ROE6 I decided to have the Maps subdirectory relocated to this Path. I had to change (in my program) the Connection String and all the 116 paths for it to load the Maps but it now works just fine on the other PC. I must have missed something because it is unreasonable to have to keep the support files at the same location on my PC as the installer is going to use on the target PC. Any words of wisdom to get me straightened out? TIA, Jim Tag: Dlookup function Tag: 144279
  - 17
    - problem with excel hi, i'm facing a strange problem.. In my requirement , I'm suppose to load the data from datagrid into excel file. make the required changes in the file and load the file back into the datagrid. i'm able to export to excel but after i make changes in the excel and load it into grid, my changes are not visible..i'm getting null values.. i've datetime columns,decimal columns in my file...The values are visible in the file but when i load into grid..i'm seeing blanks spaces.. has anyone encountered such problem.? How can load from excel to datagrid Tag: Dlookup function Tag: 144277
  - 18
    - LINQ passing Array for a parameter I have a web page where a user can select multiple items for use in a select statement. SELECT columns FROM table WHERE ID in (1,2,3). Since its a web application I use a stored procedure to get the results. Currently my sproc GetResults (@EventIdList varChar(@256)) uses a table value function that parses the item list and returns a table. I use this method because I don't do dynamic SQL in sprocs and sprocs can't take arrays. Select columns from MyTable INNER JOIN dbo.ParseIntToTable(@EventIdList,',') evn on MyTable.EventTypeID = evn.ColumnValue ALTER FUNCTION [dbo].[ParseIntToTable] ( @StringList varchar(8000), @Delimiter char(1) ) RETURNS @TempTable table ( ColumnValue int ) AS BEGIN DECLARE @StringValue varchar(1000) -- value taken off list DECLARE @Pos int -- position of delimiter SET @StringList = LTRIM(RTRIM(@StringList)) IF RIGHT(@StringList,1) <> @Delimiter SET @StringList = @StringList + @Delimiter SET @Pos = CHARINDEX(@Delimiter, @StringList, 1) IF REPLACE(@StringList, @Delimiter, '') <> '' BEGIN WHILE @Pos > 0 BEGIN SET @StringValue = LTRIM(RTRIM(LEFT(@StringList, @Pos - 1))) IF @StringValue <> '' BEGIN INSERT INTO @TempTable (ColumnValue) VALUES (CAST(@StringValue AS int)) END SET @StringList = RIGHT(@StringList, LEN(@StringList) - @Pos) SET @Pos = CHARINDEX(@Delimiter, @StringList, 1) END END RETURN In LINQ how do you write a query that takes an array for a parameter. ( select columns from table where ID IN (1,2,3) ). The query result will be used in a GridView and needs to support paging and sorting. thanks, Tag: Dlookup function Tag: 144275
  - 19
    - OracleDataReader Returns No Records With Nested SQL Hello All, Can anyone tell why one sql statement works with the data reader but not the other. To test, I uncomment the string I need. Dim QueryString As String ***This string does not work 'QueryString = "SELECT * FROM print_server WHERE LS = (SELECT LS FROM vw_turboinfo WHERE tsn = '080226086')" ***This string does work, and the result returned does exist in print_server 'QueryString = "SELECT LS FROM vw_turboinfo WHERE tsn = '080226086'" ***This string does work 'QueryString = "SELECT * FROM print_server WHERE LS = 4042390" Dim Command As New OracleCommand(QueryString, oraCon) Command.CommandType = CommandType.Text ***I have copied the CommandText into TOAD for Oracle for each of the 3 ***strings, and each is valid and returns the correct information. Debug.Print(Command.CommandText) Dim reader As OracleDataReader Try reader = Command.ExecuteReader While reader.Read Debug.Print(reader.Item(1)).ToString End While Catch ex As Exception MsgBox(ex.Message) Finally End Try Tag: Dlookup function Tag: 144267
  - 20
    - IIS ASP.NET 2.0 Not creating a connection pool nor re-using I need some help! I have a webapp in ASP.NET 2 running on 2003 R2 that doesn't seem to want to bring up a connection pool. When I run a trace on the DB server I can see Audit Logoff and Audit Logon between query batches, which leads me to believe that this is occurring. This is a replacement platform for our current live systems, so I have just tried to build everything as similarly as possible throughout. The main difference is that the DB server is now SQL2005 SP2 whereas before it was SQL2000. I haven't read anything indicating that this would be the cause, but I am very open to ideas. The connection string I am using is: Application Name='Live';pooling=true;Min Pool Size=1;Max Pool Size=1000;database=DB1;User ID=USER ;Password=PASSWORD;Server=live- db1; I cannot find any way to verify information on connection pooling working, other than by using SP_WHO - which does appear to show an idle connection or two from the webapp, but like I say, I can see the connections being torn down and back up every batch from the profiler trace. The reason I'm investigating this is that the application is serving pages incredibly slowly when they have to hit the database, but I've tried running the query batches directly through MSMS on both the DB server and App servers, and they are very fast - much faster than when run via .NET. This connection latancy is the only thing I can put it down to at the moment. I'd welcome any insight. Jon Tag: Dlookup function Tag: 144266
  - 21
    - datagrid to excel Hi, I;ve a requirement wherein the need to export the data from grid to excel file using .net 1.1.. While I do the export to file, sfew columns in teh excel file should be locked, in the sense..use shouldn't be able to edit the values in those columns... is this possible thru C#? any samples Tag: Dlookup function Tag: 144263
  - 22
    - Can't add a new row to a database Why won't this code add a row to my database? It runs as expected but when I look in the SQL database using SQL Server Management Studio the table is empty. DataRow newRow = this.dsHealthClub.NotInHouse.NewRow(); newRow["Name"] = dsEmployees.Employees.Rows[0].ItemArray.GetValue(0); newRow["DateOut"] = DateTime.Now; newRow["Comments"] = ""; newRow["Badge"] = visitorID; this.dsHealthClub.NotInHouse.Rows.Add(newRow); Tag: Dlookup function Tag: 144261
  - 23
    - Database Table and XML Synchroniazation Hello to all, Is it possible to sychronize a Database Table and an XML file. To be more specific, I want to read data from a database, convert them to XML, make changes to the XML and then update the Database table from the XML file. Is it possible? Regards Dimitris Tag: Dlookup function Tag: 144260
  - 24
    - a problem about connect to access with ODBC System.Data.Odbc.OdbcConnection conn = new System.Data.Odbc.OdbcConnection(@"Driver={Microsoft Access Driver(*.mdb)};Dbq=C:\abc.mdb;Uid=Admin;Pwd="); when invoke conn.Open(); the program throw an OdbcException ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified Tag: Dlookup function Tag: 144252
  - 25
    - Must delcare the scalar variable I am running an Update routine that calls a function. It appears that the function never executes because I receive: Must Declare The Scalar Variable @Country. Code: Dim newCustomer As New Customer newCustomer.CustomerID = customer.CustomerID Me.PutCustomerData(newCustomer) Try If Not CustomerDB.UpdateVendor(customer, newCustomer) Then MessageBox.Show("Another user has updated or deleted that customer.", _ "Database Error") Me.DialogResult = Windows.Forms.DialogResult.Retry Else customer = newCustomer Me.DialogResult = Windows.Forms.DialogResult.OK End If Catch ex As Exception MessageBox.Show(ex.Message, ex.GetType.ToString) End Try The only time @Country is used is in a Insert function. In the Update fuction, I use @NewCountry and @OldCountry, so I am not sure why this is happening. Please help. Tag: Dlookup function Tag: 144251

Hi

Is there a dlookup function in vb.net like in access which returns a column
value from a table for a given criteria? If not, is there a sample I can
look at?

Thanks

Regards

Top