SQL Connection in detail

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
    - "Importing" my existing classes to a dbml? Hi, I'm currently moving (slowly) to use Linq to Sql to replace my custom data access layer. Fortunately, my DAL worked very similar to L2S as far as the use of classes goes; each table is decorated with attributes that tell my DAL how to do what it needs. At first glance, it seemed all I had to do was slap the Table and Column attributes onto my classes, and I could use both Linq and my DAL as I transition. I'm now at the point where I'm creating associations and going to be using Linq to update data, not just read it. I hit a problem, which I think was caused because my backing field for the ONE side of a relationship was MyTable, not EntityRef<MyTable>. Ok, I can fix that, I've only done a few classes. Now I'm looking at the EntitySet side of things. Here it seems more complex; initially I had simply the field for EntitySet<MyTable2>. But it looks like other code is could be needed, such as specifying actions for insert and delete and implementing IPropertyChanging and IPropertyChanged. So.. is there any way to automate getting this code in? Do my data access classes HAVE to be in the Dbml.designer.cs file, all in the same namespace? What are my options here? Tag: SQL Connection in detail Tag: 144584
  - 2
    - Adding Rows to Data-Bound DataGridView A data-bound DataGridView is wonderfully simple to include with a Windows Form. Now that all the data is there, how do I go about adding a new record? When I tried, Visual Studio 2005 Pro gave me an InvalidOperationException, saying, "Rows cannot be programmatically added to the DataGridView's rows collection when the control is data-bound." Am I just not able to add a new record ever if I use a data-bound DataGridView, or do I need to follow some special steps to un-data-bind it, add my data, then re-data-bind it? Tag: SQL Connection in detail Tag: 144572
  - 3
    - Create LINQ to SQL without Visual Studio? How can the .dbml be created programmatically? If I don't want to use the full GUI designer and instead want to automatically build associated data classes when the DB schema changes, how can I do that? Thanks. Tag: SQL Connection in detail Tag: 144570
  - 4
    - My dlookup function Hi I did a simple dlookup function and am posting it here for novices like me. :) Aggregate functions are not supported. Regards Will below do? Thanks Regards Function DLookup(ByVal SearchFld As String, ByVal SearchTbl As String, ByVal _ SearchCriteria As String) As Object Dim Cmd As OleDb.OleDbCommand Dim Reader As OleDb.OleDbDataReader Dim Value As Object = DBNull.Value DLookup = Nothing Cmd = New OleDb.OleDbCommand("SELECT " & SearchFld & " FROM " & SearchTbl & _ " WHERE " & SearchCriteria, DBConnection()) Reader = Cmd.ExecuteReader() If Microsoft.VisualBasic.Left(SearchFld, 1) = "[" Then SearchFld = Microsoft.VisualBasic.Right(SearchFld, Microsoft.VisualBasic.Len(SearchFld) - 1) End If If Microsoft.VisualBasic.Right(SearchFld, 1) = "]" Then SearchFld = Microsoft.VisualBasic.Left(SearchFld, Microsoft.VisualBasic.Len(SearchFld) - 1) End If If (Reader.Read()) Then Value = IIf(Reader.GetValue(Reader.GetOrdinal(SearchFld)) Is Nothing, "", _ Reader.GetValue(Reader.GetOrdinal(SearchFld))) End If DLookup = Value End Function Tag: SQL Connection in detail Tag: 144569
  - 5
    - TableAdapter Updating I have one TableAdapter that has 2 custom updating functions, 1 insert, and the default Fill. In my 2 custome updating functions, one function updates some fields, whereas the other function updates the other fields (we do not always want to update all records). The problem is, when I call my update functions, they both return 0 (no records were updated). They are each in a TRY/CATCH block, but no exceptions are being thrown. Why aren't my TableAdapter's Update functions working? Is there a tutorial online somewhere that can outline the proper technique? Thanks in advance! Tag: SQL Connection in detail Tag: 144565
  - 6
    - Suggestions to track monthly payments? Hello everyone, I am designing an application around a dance school. The school is open for 10 months - September-June. The students pay a monthly tuition. Anyone have any suggestions on how to track the payments? I'm trying to design the table layout and am hitting a mental block. Any suggestions would be greatly appreciated! Thank you, Kevin Tag: SQL Connection in detail Tag: 144562
  - 7
    - No update happening I am using SQL Server 2005. I have a form with a grid on it. I have this code in place: String strFilter = String.Format("Line={0} AND Time='{1:D2}/{2:D2}/{3:D4} {4:D2}:{5:D2}'", m_cd.m_iLine, m_cd.m_dt.Month, m_cd.m_dt.Day, m_cd.m_dt.Year, m_cd.m_dt.Hour, m_cd.m_dt.Minute); DataRow[] rows = callerIDTemplateDataSet.Callers.Select(strFilter); if (rows.Length == 0) callerIDTemplateDataSet.Callers.AddCallersRow(m_cd.m_iLine, m_cd.m_strName, m_cd.m_strPhone, m_cd.m_dt, m_cd.m_iDuration, m_cd.m_iRings, strFirstName, strLastName, strAddress, strCity, strState, strZip, "", m_cd.m_strIO == "I"); else if (rows.Length == 1) { DataRow row = rows[0]; row.BeginEdit(); row["Duration"] = m_cd.m_iDuration; row["Rings"] = m_cd.m_iRings; row.AcceptChanges(); } this.callersTableAdapter.Update(callerIDTemplateDataSet.Callers); A new row is correctly added to the database. The row shows up in the grid and it is persisted. However, if row.Length is 1 and update is needed. The grid shows the update but the data is not persisted to the database. Does anyone know why this would be? The callerIDTemplateDataSet and callersTableAdapter were created simply by dropping the grid onto the form. Tag: SQL Connection in detail Tag: 144561
  - 8
    - datacolumn is it possible to get the size of the datatype of a datacolumn in a datatable? i want to show the datatype with the size of a table. first i got the table in a dataset. then i try to catch the datatype. all are working fine? but my question is "is it possible to get the datasize ie. like varchar(20)?" if possible which method i hav to use? pls help me out. -- sharing doubles brain power Tag: SQL Connection in detail Tag: 144550
  - 9
    - What's new in ADO.NET Hello, What's new in ADO.NET framework 3.5 about new features, changes, improvements ? Regards, Tag: SQL Connection in detail Tag: 144545
  - 10
    - App bombs in production Hi I have a vs 2003 win form app which runs fine in IDE but once it is deployed via setup it bombs on start-up screen and wants to send MS the error report. This happens on multiple pc including the development machine so problem is probably not with pcs. What is the problem and how can I fix it? Thanks Regards Tag: SQL Connection in detail Tag: 144541
  - 11
    - System.EnterpriseServices I am a newbie to the ES namespace and have heard peple talking about using it in conjunction with ADO .NET in aiding in the creation of connections. So far, what I can find out is that ES is related to ADO .NET in transactional processing. Does ES provide any benefit to ADO .NET if I'm not using transactions? Tag: SQL Connection in detail Tag: 144539
  - 12
    - The ConnectionString property has not been initialized. I am using TableAdapters the same way in the code below. The first works, and the second does not. Names shortened here for simplicity, and values hardcoded. In both cases I can stop in the debugger and see the correct connection string in the TableAdapter, which they both get from web.config. I can't see any difference between the two which would indicate why the second one fails. Ideas? XTableAdapter ta = new XTableAdapter(); if (ta.MarkAsDeleted(40) == 1) {//delete ok- now delete children YTableAdapter taContact = new YTableAdapter(); if (taContact.MarkAsDeleted(72) == 1) { Console.WriteLine("OK"); } } Also, I started getting a message box whenever I leave the VS DataSet designer GUI which says "Connection string not set - cannot get data design context". It has no further info. I have looked at every adapter and they all have a connection string specified. Tag: SQL Connection in detail Tag: 144538
  - 13
    - Enum redefinition when upgrading to VS2008 I just finished upgrading to Visual Studio 2008. When I compiled for the first time I got this error message which I didn't get in Visual Studio 2002. As the error indicates the redefinition conflict is between two ADO include files. What is the recomended way to correct this??? Thanks, Neil ***************** ERROR MSG ****************************** 1>c:\program files\microsoft sdks\windows\v6.0a\include\dbdaoint.h(138) : error C2011: 'DataTypeEnum' : 'enum' type redefinition 1> c:\actbooks dev 2007\debug\msado15.tlh(276) : see declaration of 'DataTypeEnum' Tag: SQL Connection in detail Tag: 144537
  - 14
    - using go in a sql batch Hello, in SQL scripts I can use GO to force query analyzer to finalize the batch above. Before continuing with execution of code. This is needed i.e. when i add a new column before i can fill it. Is there an equivalent statement that can be used when hte script is loaded from file and executed with adonet (sqlclient) or do I have to split the script file? Thanks Patrick Tag: SQL Connection in detail Tag: 144534
  - 15
    - System.Data.OracleClient Bug Hi all, There is a fix about System.Data.Oracleclient.OracleConnection object about not removing invalid connections from the pool in the following adress. http://support.microsoft.com/default.aspx?scid=kb;en-us;830173 I am facing the same problem. But i am confused about versions of oracleclient.dll. On the fix page, the version mentioned after the fix for framework 1.1 is 1.1.4322.2038 On the production machine that the application runs, version of the this dll is 1.1.4322.2312. so is it possible that same problem may appear on the future versions of the oracleclient.dll ? thanks. Tag: SQL Connection in detail Tag: 144532
  - 16
    - Bad Uri Format In the docs this is used all over the place: new Uri("somefile.txt") but that throws an exception "Bad Uri Format". It works if you specify: new Uri("somefile.txt", UriKind.Relative) Is this a bug or how it is supposed to work? I thought Uri's were relative by default (though I have 'reflected' it yet). Thanks, Shawn Wildermuth Microsoft MVP (C#) http://wildermuthconsulting.com mailto:swildermuth@REMOVE_ALLCAPS_adoguy.co Tag: SQL Connection in detail Tag: 144524
  - 17
    - www.dotnetvideos.net just released 10 New Videos on Data Tutorials Hi all, www.dotnetvideos.net released 10 new videos in the following categories. every registered user of this site still gets a FREE subscription to asp.netPRO magazine. chek it out!! An Overview of Editing and Deleting Data in the DataList (7 Videos) at http://www.dotnetvideos.net/OverviewofEditingDeletinginDList/tabid/127/Default.aspx Performing Batch Updates (3 Videos) at http://www.dotnetvideos.net/PerformingBatchUpdates/tabid/128/Default.aspx ---venkat Tag: SQL Connection in detail Tag: 144523
  - 18
    - Property from Typed DataSet Error Hi, I have a Typed DataSet named MyDataSet and a Component (System.ComponentModel.Component based) and It last have a property from MyDataSet typed. But when use my component and set the proporty, raise the message "MyDataSet can't convert to "MyDataSet", ¿ How do it ? Alvaro. Tag: SQL Connection in detail Tag: 144522
  - 19
    - Updating a datarow I am having a problem putting all of the pieces together on this one and would appreciate some help. I need to find a row based on certain criteria, retrieve its Item Number or position in the table, then update a field in the row. In other words, locate the row where Field1 = x, update Field2 =y I see examples of how to find a row using Select and I know how to update if I know the Item number, but I can't put everything together. Thanks in advance... Tag: SQL Connection in detail Tag: 144521
  - 20
    - Creating a .NET 1.1 DataSet in Visual Studio 2008 Hi, I'm running a .NET 1.1 application and I'd like to use a .NET 2.0 Web service which uses .NET 2.0 Datasets. I would like to write a proxy class that talks to the Web service on one side and on the other to the .NET 1.1 app. Is it possible to create .NET 1.1 Datasets in the .NET 2.0 environment? If so, how? Are .NET 1.1 and 2.0 interopable in this way? Thanks for any advice Adrian -- Adrian Miller Tag: SQL Connection in detail Tag: 144518
  - 21
    - Generating a dataview from a datatable Hi I have a strongly typed dataset generated by vs 2008. I am trying to fill in one of the datatables in the dataset and then cerate a dataview from the datatable. I have used the below code but it does not work. Public ds As MyDS Public dv As DataView Public daMyTbl As MyDSTableAdapters.MyTblTableAdapter ds.MyTbl.Clear() daMyTbl.Fill(ds.MyTbl) dv = New DataView(ds.MyTbl) For starters it givbes 'Object reference not set to an instance of an object.' error on ds.MyTbl.Clear() line. What am I doing wrong? Many Thanks Regards Tag: SQL Connection in detail Tag: 144513
  - 22
    - Dlookup function 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 Tag: SQL Connection in detail Tag: 144510
  - 23
    - 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: SQL Connection in detail Tag: 144503
  - 24
    - 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: SQL Connection in detail Tag: 144488
  - 25
    - 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: SQL Connection in detail Tag: 144487
- Next
  - 1
    - 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: SQL Connection in detail Tag: 144483
  - 2
    - 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: SQL Connection in detail Tag: 144465
  - 3
    - DataTable.Load() Does the DataTable.Load() method do its operation in one database round-trip when it is passed a DataReader? Tag: SQL Connection in detail Tag: 144448
  - 4
    - 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: SQL Connection in detail Tag: 144447
  - 5
    - 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: SQL Connection in detail Tag: 144445
  - 6
    - 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: SQL Connection in detail Tag: 144442
  - 7
    - 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: SQL Connection in detail Tag: 144438
  - 8
    - 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: SQL Connection in detail Tag: 144437
  - 9
    - 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: SQL Connection in detail Tag: 144433
  - 10
    - 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: SQL Connection in detail Tag: 144429
  - 11
    - 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: SQL Connection in detail Tag: 144426
  - 12
    - 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: SQL Connection in detail Tag: 144425
  - 13
    - 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: SQL Connection in detail Tag: 144420
  - 14
    - 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: SQL Connection in detail Tag: 144417
  - 15
    - 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: SQL Connection in detail Tag: 144410
  - 16
    - 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: SQL Connection in detail Tag: 144406
  - 17
    - 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: SQL Connection in detail Tag: 144405
  - 18
    - 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: SQL Connection in detail Tag: 144403
  - 19
    - 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: SQL Connection in detail Tag: 144401
  - 20
    - 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: SQL Connection in detail Tag: 144399
  - 21
    - 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: SQL Connection in detail Tag: 144382
  - 22
    - 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: SQL Connection in detail Tag: 144363
  - 23
    - 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: SQL Connection in detail Tag: 144357
  - 24
    - 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: SQL Connection in detail Tag: 144351
  - 25
    - 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: SQL Connection in detail Tag: 144348

Hi,

I have a dotnet 2.0 application, that uses the connection pool to connect to
a sql server. That works pretty good.

Which kind of authentication is used between the application server and the
sql server when persistent connections are used?

When I use my sniffer to look into the packets, I can't see the
authentication. Is it somehow encrypted and where can I find something about
the connectionsetup from the TDS protocol point of view?

TIA
Markus

Top

Re: SQL Connection in detail by Patrice

Patrice
Fri Mar 14 06:11:00 CDT 2008

The one you defined ;-)

What is the connection string you are using ? If ASPNET impersonation could
also come into pay.

You could start by using the SQL Server Profiler that shows the identity
under which each statement runs...

--
Patrice

"Markus Hirsch" <news(dot)hirsch(at)syscom(dot)at> a écrit dans le message
de news: eI7C9sUhIHA.3352@TK2MSFTNGP04.phx.gbl...
> Hi,
>
> I have a dotnet 2.0 application, that uses the connection pool to connect
> to a sql server. That works pretty good.
>
> Which kind of authentication is used between the application server and
> the sql server when persistent connections are used?
>
> When I use my sniffer to look into the packets, I can't see the
> authentication. Is it somehow encrypted and where can I find something
> about the connectionsetup from the TDS protocol point of view?
>
> TIA
> Markus
>

Top