tell VB when new row inserted in SQL server

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
 - Get related rows for a typed datarow Hi, How do I get the related datarows for a typed datarow? Thanks Henrik Tag: tell VB when new row inserted in SQL server Tag: 130980
 - 2
 - converting system.datatype to DbType Is there any dotnet function that will convert an object of type System.DataType to a IDataParameter.DbType enumeration? When I read off a table schema (IDataReader.GetSchemaTable) the data types for each column in the table are returned as System.DataType. I would like to write a function to create IDbDataParameters for sql queries generically according to the type stored in the table schema. or am i missing something in GetSchemaTable that's already storing the DbType and so I dont have to do the conversion. thanks Tag: tell VB when new row inserted in SQL server Tag: 130978
 - 3
 - Connection Pooling Hi, I have an application, which opens many connection and after completing work never closes connection. This is becoming a problem on sql server side. I am thinking to write a tool which will close all the sleeping connection at one run. Is this possible ?. how will I get a info about connection from sql server? Kishor Tag: tell VB when new row inserted in SQL server Tag: 130974
 - 4
 - How to export data from SQL Server to Excel in .net Hello All, I want to do bulk export using ado.net from SQL Server database to MS Excel document. I would highly appreciate small examle showing how to do this. Best Regards, Neo Tag: tell VB when new row inserted in SQL server Tag: 130972
 - 5
 - 2 DataGrids with using same SQL Server table I have a windows form with two datagrid objects. The goal is to display rows from the same SQL Server table, using different SELECT commands. Here's a sample of the code: Data Grid 1: ---------------------------------------------------------------------------------------------------------------------------------- strCMD = @"SELECT ID, ClassID, ClassCode, StartDate, EndDate" + @" FROM sessions" + @" WHERE StartDate < GetDate()" + @" ORDER BY StartDate, ClassCode"; myCN = new System.Data.SqlClient.SqlConnection(); myCN.ConnectionString = frmMain.strConn; myCN.Open(); myDA1 = new System.Data.SqlClient.SqlDataAdapter(strCMD, myCN); myDS1 = new DataSet(); myDA1.Fill(myDS1, "sessions"); this.myDA1.TableMappings.AddRange(new System.Data.Common.DataTableMapping[] { new System.Data.Common.DataTableMapping("Table", "sessions", new System.Data.Common.DataColumnMapping[] { new System.Data.Common.DataColumnMapping("ID", "ID"), new System.Data.Common.DataColumnMapping("ClassID", "ClassID"), new System.Data.Common.DataColumnMapping("ClassCode", "ClassCode"), new System.Data.Common.DataColumnMapping("StartDate", "StartDate"), new System.Data.Common.DataColumnMapping("EndDate", "EndDate")})}); dataOrgs1.DataSource = myDS1; dataOrgs1.DataMember = "sessions"; dataOrgs1.SetDataBinding(myDS1, "sessions"); ---------------------------------------------------------------------------------------------------------------------------------- DataGrid 2: ---------------------------------------------------------------------------------------------------------------------------------- strCMD = @"SELECT ID, ClassID, ClassCode, StartDate, EndDate" + @" FROM sessions" + @" WHERE Status = 'Open Reg'" + @" ORDER BY StartDate, ClassCode"; myCN = new System.Data.SqlClient.SqlConnection(); myCN.ConnectionString = frmMain.strConn; myCN.Open(); myDA = new System.Data.SqlClient.SqlDataAdapter(strCMD, myCN); myDS = new DataSet(); myDA.Fill(myDS, "sessions"); this.myDA.TableMappings.AddRange(new System.Data.Common.DataTableMapping[] { new System.Data.Common.DataTableMapping("Table", "sessions", new System.Data.Common.DataColumnMapping[] { new System.Data.Common.DataColumnMapping("ID", "ID"), new System.Data.Common.DataColumnMapping("ClassID", "ClassID"), new System.Data.Common.DataColumnMapping("ClassCode", "ClassCode"), new System.Data.Common.DataColumnMapping("StartDate", "StartDate"), new System.Data.Common.DataColumnMapping("EndDate", "EndDate")})}); grdOrgs.DataSource = myDS; grdOrgs.DataMember = "sessions"; grdOrgs.SetDataBinding(myDS, "sessions"); ---------------------------------------------------------------------------------------------------------------------------------- If I comment out one, the active datagrid displays the right rows. If I make both active, the first data grid displays the same rows as the second datagrid. The only difference is the number of rows displayed. Any ideas what I'm missing here? Tag: tell VB when new row inserted in SQL server Tag: 130971
 - 6
 - ADO.NET forces unload ODBC Driver Come across a problem with VB.net application using an ODBC Driver. On application shutdown, it forces unloading the ODBC Driver without giving enough time for the driver to handle DLL_PROCESS_DETACH (or CWinApp::ExitInstance) We have our own ODBC Driver and found a problem with lengthy driver unloading process. The ODBC Driver takes more than 3-5 seconds to clean up during DLL unloading. And it looks like ADO.NET/.NET interrupts normal unloading process, if it takes more than 2 seconds (or about). We tried to emulate lengthy unloading process with normal DLL (not ODBC driver) and DllImport directive in C#, but it seems to work fine. We tried to use others (SQLite) ODBC driver source code and we get the same behaviour from Ado.net Thanks! Tag: tell VB when new row inserted in SQL server Tag: 130970
 - 7
 - Change in Typed Datasets between 1.x and 2.x I know/gather that the changes between typed datasets between 1.x and 2.x are pretty significant, but there's one specific thing I could do in 1.x and I can't figure out how to do it in 2.x, or if it is still possible. There is a stored procedure that returns multiple result sets (4 in particular). In 1.x, I could drag and drop the stored procedure from Server Explorer and it would create 4 'tables' for me, and then I'd make my edits, etc. In 2.x, it only shows the first table. There are many ways that I know will give me workarounds to get to the end result, and I've taken care of this specific case already, I guess I'd just like to know if there's a quick way to get the 1.x functionality for future cases. TIA, jdn Tag: tell VB when new row inserted in SQL server Tag: 130967
 - 8
 - how to lock the counter table if using OLE DB database Hi, all, I'm having problem on multi user environment, currently I have over 20 concurrent users creating records on Access database table, because my database have a Counter table for generating Record ID. My problem is using ADO.net, i need to generate Record ID in 2 steps: Step 1) use dataset or data reader to get the next record ID Step 2) use dataAdapter to update next record ID In multi user environment, anyway to lock this Counter table such that I can ensure the above steps perform as I expected?? I know if I lock the table, two users may have change to read the same Record ID. Tag: tell VB when new row inserted in SQL server Tag: 130964
 - 9
 - SQL Parameter bug I am having a function which accepts Database parameter array, and it inserts to the database inside a loop. Note: inside the loop i am changing the values of the parameter array . it works fine if the loop executes for only one time, but if the loop executes more than one time i am getting the error : "field xyz is already contained by another SqlParameterCollection" Function abc(Parameter_Array) begin loop alter Parameter_Array' values Create command object Add parameters to the command object //i am getting error at this point,if this is executed for the second time . Execute command.parameters.close(); set connection,command etc.. to null end Tag: tell VB when new row inserted in SQL server Tag: 130958
 - 10
 - Oracle behaving strangely with multiple commands executing in a tight loop We've run into a very strange issue when running against Oracle using System.Data.OracleClient. As part of an import process that we're running we're doing a number of validations on data that has already been inserted into our database. For each validation within our import process we're logging errors by doing something along the lines of: INSERT INTO ImportMessages (ErrorContext, ErrorType, Timestamp, RowId) SELECT Col1, 1, CurrentDate, RowIdColumn FROM TableWithImportedRecords WHERE {validation condition} We subsequently update each invalid record in our bulk import table with a simple UPDATE statement. UPDATE BulkImport SET IsValid = 0 WHERE {validation condition} We have approximately 15 validations that are run against the database in sequence. foreach(ImportValidation validation in validations) { validation.Validate(); // this executes the two SQL statements above errorCount = validation.ErrorCount; warningCount = validation.WarningCount; } When we run our imports we're getting extemely inconsistent (random) behavior from Oracle. When we execute the process against Sql Server everything works reliably and consistently. As part of our debugging efforts we tracked things down to the foreach() loop shown above and found that there is some sort of timing issue when we execute our commands against Oracle. When we run in the debugger with a breakpoint set on the errorCount line everything works perfectly, but, as soon as we take the breakpoint off the code results in extemely inconsistent and incorrect results. As an experiment we placed a Thread.Sleep(500) statement inside the loop which resulted in everything working properly. What could be happening when those statements are executed against Oracle that would cause them to return unexpected results when run in a tight foreach loop? Are there any Oracle specific settings that I should look into (auto-commit?)? Any ideas? I'm fresh out Tag: tell VB when new row inserted in SQL server Tag: 130954
 - 11
 - How to increase the width of Crystal report. Hi all, how to increase the width of crystal report of an application. while displaying the columns. thanks in advance, sai.t Tag: tell VB when new row inserted in SQL server Tag: 130921
 - 12
 - Parameters and CrystalReports. I am new to CrystalReports and I just want to use the print functionality. I am currently passing a data set and that works just fine: Dim rpt As New QuoteDetail rpt.SetDataSource(ds) rpt.PrintToPrinter(1, False, 0, 0) rpt.Dispose() I have added some parameter fields because the existing printout needs to be modified. How do I add a parameter to Crystal Reports? I have already added the parameters to the report and now when I execute the above lines I get an exception indicating that there are no parameters. I have parameters such as Terms QuoteFile PreparerName Which are all strings. If I just want to set the parameter 'Terms' to something like "NET 30" how do I do that? I have tried rpt.Parameter_Terms.CurrentValues.AddValue("Net 30") But that does not seem to add the parameter. Any suggestions? Kevin Tag: tell VB when new row inserted in SQL server Tag: 130914
 - 13
 - Command Timeout on Queries I'm reading mixed opinions on the effect of a command timeout, and wanted to asked for clarification. From what I understood (and from what my testing seems to indicate), CommandTimeout affect non-queries executes only. Therefore if ADO was calling an SP, and the Command object was set to 1 second timeout, and a Command.ExecuteReader was then called, and the SP required 20 seconds to pull the data, there would be no timeout and the data would be returned. However, if an SP required 20 seconds to perform (an update or whatever), and a Command.ExecuteNonReader was called, then it would timeout. Again, I'm not 100% sure, especially after reading so many differing opinions on this. Can someone clarify? THe reason I ask... we are running into "Timeout" issues sometimes with SqlDataReaders and some suggest bumping up the CommandTimeout. I'm thinking its a different problem. Tag: tell VB when new row inserted in SQL server Tag: 130912
 - 14
 - Efficient Filling Multiple DataTables Hi all, I am transitioning from ADO.NET 1.1 to 2.0. One of my 1.1 practices when filling a dataset with several related datatables was to explicitly open the connection, call fill for each dataAdapter involved, explicitly close the connection. This saved the overhead of the connection opening and closing for every dataAdapter.Fill. In 2.0, if I fill via TableAdapters, this approach could get pretty convoluted. Is it worth pursuing? Any known examples of an efficient approach to this situation? Thanks. -- John Tag: tell VB when new row inserted in SQL server Tag: 130908
 - 15
 - Polling a database - Performance question I created a couple of custom performance monitor counters. Basically I have a service that runs and updates the counter on an interval as often as once per second. So the question is, for performance on the SQL Server and the app itself, so I establish the connection once and maintain it until the service is stopped, or do I reconnect every time the counters get updated? (It's a small query, just reading blocks and database names from a couple of the master tables.) Thanks. Jerry Tag: tell VB when new row inserted in SQL server Tag: 130905
 - 16
 - Can anyone See where I'm going wrong? Hi Everyone, Can anyone see where I'm going wrong? Win [C#] OleDbConnection thisConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\HOST 017.MDB"); thisConnection.Open(); OleDbDataAdapter thisAdapter = new OleDbDataAdapter("SELECT * from TableTemplate", thisConnection); OleDbCommandBuilder thisBuilder = new OleDbCommandBuilder(thisAdapter); DataSet thisDataSet = new DataSet(); thisAdapter.Fill(thisDataSet,"TableTemplate"); MessageBox.Show("Before Change:" + thisDataSet.Tables["TableTemplate"].Rows[1]["Catagory"]); thisDataSet.Tables["TableTemplate"].Rows[1]["Catagory"] = "100"; thisAdapter.Update(thisDataSet, "TableTemplate"); MessageBox.Show("After Change: " + thisDataSet.Tables["TableTemplate"].Rows[1]["Catagory"]); Error being generated is : Syntax error(missing operator) in query expression "((ID = ?) and ((? IS NULL AND Nam IS NULL) or (NAME = ?) AND.... It keeps ong on from there. But any and all help would be appreciated. Thanks MikeY Tag: tell VB when new row inserted in SQL server Tag: 130903
 - 17
 - 2100 sqlparameter Hallo Ich möchte einen bulk insert mittels eines SqlCommands und dazugehörigen SqlParametern machen. Dazu habe ich innerhalb einer for schleife einen Stringbuilder mit insert statements gefüllt. Ebenfalls habe ich innerhalb der forschleife die Sqlparameter erzeugt. Danach den CommandText mit dem Inhalt des Stringbuilders gefüllt und das ganze ausgeführt. Leider hat es den Anschein das ich nicht mehr als 2100 SqlParameter benutzen kann. Es gibt immer eine Exception;) Kennt jemand das Problem und gibt es einen workaround? Ich benutze Dotnet1.1 und SqlServer 2000 thx Björn Beispiel Code: for (int i=1;i<526;i++) { sql.Append("insert into tabelle (name,text,nummer,num2) values (@name"+i+",@text"+i+",@num"+i+",@numm"+i+"); "); sqlCom.Parameters.Add("@name"+i,name); sqlCom.Parameters.Add("@text"+i,text); sqlCom.Parameters.Add("@num"+i,num); sqlCom.Parameters.Add("@numm"+i,num2); } sqlCom.CommandText = sql.ToString(); sqlCom.ExecuteNonQuery(); Tag: tell VB when new row inserted in SQL server Tag: 130899
 - 18
 - Ado.Net and Access Is there any easy way of creating a new Access file and adding a table to this new Access file from within a c# application? Thanks Ferdinand Tag: tell VB when new row inserted in SQL server Tag: 130898
 - 19
 - DataReader, Update the same reading row ? Hi everyone, I have a small problem, I am reading the data using the datareader. and I want to update the same row later using a different connection and a transaction. When I update the same row which is currently loaded to the datareader it blocks the whole thing and waits.. ? how do i update a data row which is currently read by the datareader ? thanks, -Aruna Tag: tell VB when new row inserted in SQL server Tag: 130891
 - 20
 - Remote IDbConnections, opening/closing and errors Hi Im newish with databases so I'd thought I'd ask a general question. Im using a remote MySQL database accessed across the internet with dotnet provider components. The code I've written creates a new connection for each transaction on the database then closes it again afterwards. So there are many Connection.open/close during the lifetime of my application. I'm wondering if I should be doing this or if I should only open the connection when my database wrapper object is instantiated and close it when it is destroyed. The reason I did it this way was because I was worried about loss of network after the connection was opened. I've no idea about what possible errors this would cause and also how to write nice error handling for a constantly opened connection that dies during the objects lifetime. I also don't know what performance hits that constant opening and closing would cause. I would appreciate that someone with experience please advise me about how to handle this. thanks Claire Tag: tell VB when new row inserted in SQL server Tag: 130890
 - 21
 - Dataset to excel Hi and TIA! I have a recordset retrieved into a datgrid in my web app, from an SQL database that I want to save as an excel formatted file. I would like the user to click a button, get a prompt to save/name the file. Is this possible and if so where can I find information on accomplishing this task. Any advice is appreciated. Using (.Net 1.1 / VS2003 / SQL Server 2000 / WinXP Pro). I posted to several groups cause not sure the best place to ask the question. Thanks for your time. -- ****************** Reggie Tag: tell VB when new row inserted in SQL server Tag: 130886
 - 22
 - Dataset to excel Hi and TIA! I have a recordset retrieved into a datgrid in my web app, from an SQL database that I want to save as an excel formatted file. I would like the user to click a button, get a prompt to save/name the file. Is this possible and if so where can I find information on accomplishing this task. Any advice is appreciated. Using (.Net 1.1 / VS2003 / SQL Server 2000 / WinXP Pro). I posted to several groups cause not sure the best place to ask the question. Thanks for your time. -- ****************** Reggie Tag: tell VB when new row inserted in SQL server Tag: 130885
 - 23
 - Help Updating Northwind Syntax Win[C#] OLEDB Hi Everyone, Even though this is a OLE question, hopefully someone can help me. I'm having problems with my syntax on the updating part for the Northwind.MDB. All other code seems fine. It has to do with the Order Details on the thisAdapter.Update( ). Or I believe the problem lies with with space between Order and Details and not knowing how to get around that problem. I would like to keep with the space being left in-between the two names. The error I'm getting is The error that it is triggering is "Update unable to find TableMapping['[Order Details]'] or Data Table '[Order Details]'. Order Details is just one table. Is There a way to use a name space name without a hassle? My Code is as follows: Yes & sorry I have posted on another forum, but to no avail. So any and all help is appreciated. Mike private void DataUpdate() { try { OleDbConnection thisConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Northwind.MDB"); thisConnection.Open(); OleDbDataAdapter thisAdapter = new OleDbDataAdapter("SELECT * FROM [Order Details]", thisConnection); OleDbCommandBuilder thisBuilder = new OleDbCommandBuilder(thisAdapter); DataSet thisDataSet = new DataSet(); thisAdapter.Fill(thisDataSet, "Order Details"); thisDataSet.Tables["Order Details"].Rows[0]["Quantity"] = "99"; thisAdapter.Update(thisDataSet, "[Order Details]"); thisConnection.Close(); } catch(Exception myExcept) { MessageBox.Show(myExcept.Message); } } Tag: tell VB when new row inserted in SQL server Tag: 130884
 - 24
 - =?utf-8?B?UmU6IERldGVybWluaW5nIGZpZWxkIHR5cGUgQ0hBUiB2cyBWQVJDSEFS?= As an alternative to being specific to SQL Server, you might want to look at DbDataReader.GetSchemaTable(). It provides a mechanism to get the schema of a SqlDataReader (which might be a good way of getting this data). No way that I know of to get this from a DataAdapter/DataSet/DataTable, but does work across managed providers. Thanks, Shawn Wildermuth Wildermuth Consulting Services, LLC http://adoguy.com C# MVP, MCSD.NET, Author and Speaker > -----Original Message----- > From: Jared [mailto:google@tripletreesoftware.com] > Posted At: Thursday, July 13, 2006 6:35 PM > Posted To: microsoft.public.dotnet.framework.adonet > Conversation: Determining field type CHAR vs VARCHAR > Subject: Re: Determining field type CHAR vs VARCHAR > > > Hi Marina, > > I was hoping this would be exposed in DataColumn, but > querying the system tables should work, too. Thanks for your > quick response! > > Jared > > Marina Levit [MVP] wrote: > > You can query the sql server system tables to find out. Try > looking at > > syscolumns. > > > > > Tag: tell VB when new row inserted in SQL server Tag: 130880
 - 25
 - =?utf-8?B?UmU6IFNRTCBTZXJ2ZXIgQXV0aGVudGljYXRpb24gdmVyc3VzIFdpbmRvd3MgQXV0aGVudGljYXRpb24=?= Its more complicated than that unfortunately. Using SQL Server authentication has some security implications so it is generally better (IMHO) to use Windows Authentication to SQL Server, though by using a Integrated Security you are using the current principal running the ASP.NET page. In general this is either a very limited user (e.g. Network Service under W2K3) or a specific windows principal. If you are using Windows Authentication on your ASP.NET (e.g. Intranet projects), then integrated security means that your actual users are authenticating to the SQL Server. This is good and bad. Bad in that you need to make sure they have access, but good if you need to audit database changes as it will be annotated with who made what changed in audit logs. But this is a usually not the case. I think the best practice with ASP.NET is to use a user account to authenticate with instead of SQL Server security. Whether you use integrated security to pass the credentials to the SQL Server is up to you, but in either case the user should probably be somewhat limited in access to the database to prevent intrusions into the website causing havok. Does this make any sense? Thanks, Shawn Wildermuth Wildermuth Consulting Services, LLC http://adoguy.com C# MVP, MCSD.NET, Author and Speaker > -----Original Message----- > From: supvine@gmail.com > Posted At: Thursday, July 13, 2006 5:14 PM > Posted To: microsoft.public.dotnet.framework.adonet > Conversation: SQL Server Authentication versus Windows Authentication > Subject: SQL Server Authentication versus Windows Authentication > > > Hello, > > I am hearing conflicting point of views regarding ASP.Net > development - some folks say that when connecting to a SQL > Server via ASP.Net it is better to connect using Integrated > Windows Authentication (trusted > connection) while others say that it is better to connect > using SQL Server Authentication. > > The two modes mean the following to me - > > Windows Authentication Mode - meaning a domain account given > permission to a particular SQL Server database. > > SQL Server Authentication Mode - meaning an user created in > SQL Server itself and given specific permissions to a database. > > Could someone provide me with some direction regarding these > two options and what are the advantages and disadvantages of > each of these? > > Thanks for your help regarding this. > > > Tag: tell VB when new row inserted in SQL server Tag: 130876
- Next
 - 1
 - SQL Server Authentication versus Windows Authentication Hello, I am hearing conflicting point of views regarding ASP.Net development - some folks say that when connecting to a SQL Server via ASP.Net it is better to connect using Integrated Windows Authentication (trusted connection) while others say that it is better to connect using SQL Server Authentication. The two modes mean the following to me - Windows Authentication Mode - meaning a domain account given permission to a particular SQL Server database. SQL Server Authentication Mode - meaning an user created in SQL Server itself and given specific permissions to a database. Could someone provide me with some direction regarding these two options and what are the advantages and disadvantages of each of these? Thanks for your help regarding this. Tag: tell VB when new row inserted in SQL server Tag: 130875
 - 2
 - Determining field type CHAR vs VARCHAR Is there a way to programmatically determine if a SQL Server field is of type CHAR vs. VARCHAR? I'm able to use the DataColumn.DataType property to determine that a field is a string, and to retrieve its max length. As far as I can tell, however, both CHAR and VARCHAR fields look the same when using the DataColumn object. This for a stored procedure code generator that is complete except for this one feature. TIA Jared Tag: tell VB when new row inserted in SQL server Tag: 130873
 - 3
 - How to execute SP with type of Table.. Guru's, I have oracle sp with the below types defined, and my sp uses these types for outparameter, i am using DAAB. Need to know how to execute this kind of SP. highly appreciate if you can get me solution for this.. Thanks in advance.. ------- TYPE SECTTABTYPE IS TABLE OF VARCHAR2(5) INDEX BY BINARY_INTEGER; TYPE MCHTABTYPE IS TABLE OF VARCHAR2(7) INDEX BY BINARY_INTEGER; TYPE NUMTABTYPE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; ------------ PROCEDURE GET_CAP( P_SECTIONS IN VARCHAR2, P_NUM_DAYS IN NUMBER, P_PROC_ALL_MCHS IN VARCHAR2, P_SECTS OUT SECTTABTYPE, P_MCHS OUT MCHTABTYPE, P_RQDS OUT NUMTABTYPE, P_PLANCAPS OUT NUMTABTYPE) IS --some code ******** // .Net code for executing SP using DAAB.. DBCommandWrapper dbCommandWrapper = db.GetStoredProcCommandWrapper("dcp_PKG.get_cap"); dbCommandWrapper.AddInParameter("P_SECTIONS", DbType.String, section); dbCommandWrapper.AddInParameter("P_NUM_DAYS", DbType.Int32,NumDays); dbCommandWrapper.AddInParameter("P_PROC_ALL_MCHS", DbType.String, Mac); dbCommandWrapper.AddOutParameter("P_SECTS", DbType.String,20); dbCommandWrapper.AddOutParameter("P_MCHS", DbType.String,24); dbCommandWrapper.AddOutParameter("P_RQDS", DbType.Int32,4); dbCommandWrapper.AddOutParameter("P_PLANCAPS", DbType.Int32,4 ); //Execute the stored procedure db.ExecuteDataSet(dbCommandWrapper); Tag: tell VB when new row inserted in SQL server Tag: 130870
 - 4
 - Any way to avoid using SP_EXECUTESQL in ADO.NET? Hi, Our development team has encountered some performance problems when using ADO.NET (using the SqlCommand and SqlDataAdapter objects) to perform queries on large data tables. We've drilled into the issue considerably, and the problem seems to be related to how ADO.NET uses the SP_EXECUTESQL when running text queries. I say this because when we run our query in Query Analyzer by itself the performance is less than a second, but when running it with the SP_EXECUTESQL command (which is what Profiler is telling us is being used), it takes nearly half a minute. >From what I know about SP_EXECUTESQL, it uses dynamic cursors to execute the command it is given. Our query involves User Defined Functions (UDFs) that call UDFs, Group By statements, and Aggregate functions that are not necessarily good for dynamic cursors (or at least that is what I have heard), so the issue seems to make sense. Anyways, what I was wondering was whether or not there is a way to have ADO.NET run our query without using SP_EXECUTESQL. If this is not possible, then I am interested in knowing whether or not it is a bad practice to have UDFs calling other UDFs, as opposed to having a larger query that does all the work but is not as reusable. Thanks for your time Tag: tell VB when new row inserted in SQL server Tag: 130869
 - 5
 - DataAdapter not updating underlying SQL Database! Help! Thanks in advance! I'm using Visual studio.net 2005 I can't get the underlying table (incoming) to update. I modify the datarows, checking the database before and after running (the code gets several hits while iterating the records), and it doesn't update it. What am I doing wrong? Sub scrubTrackedExclusions() 'Dim contactInfo As New contactStruct Dim sqlFindTracked As New SqlCommand 'Dim sqlUPDATE As New SqlCommand Dim intRec As Integer = 1 Dim dtIncoming As New DataTable Dim dtTracked As New DataTable Dim dtExclusions As New DataTable Dim dtCurrad As New DataTable Dim drvCurrad As System.Data.DataRowView() Dim drvRow As System.Data.DataRow Dim dsCurrent As DataSet = New DataSet Dim dsChanges As DataSet = New DataSet Dim drIncoming As DataRow Dim drTracked As DataRow Dim drExclusions As DataRow Dim dcPK1(0), dcPK2(0), dcPK3(0) As DataColumn Dim i As Integer = 0 Dim strSQL As String = "SELECT * FROM Tradoc.dbo.Incoming WHERE op = 'INSERT' or op = 'UPDATE'; " _ & "SELECT * FROM TRACKED Where Status = 'ACTIVE'; SELECT * FROM EXCLUSIONS; SELECT * FROM CURRAD;" Dim strSqlUpdate As String = "UPDATE [Tradoc].[dbo].[Incoming] SET [cn] = @cn, " _ & "[ou] = @ou, [dn] = @dn, [op] = @op, [adsPath] = @adsPath, [reasonCode] = @reasoncode " _ & "WHERE [targetAddress] = @targetAddress" opensql(1) Dim sqlCMD As SqlCommand = New SqlCommand(strSQL, sqlConnection1) sqlCMD.CommandTimeout = 30 Dim sqlDA As SqlDataAdapter = New SqlDataAdapter sqlDA.SelectCommand = sqlCMD Dim sqlUpdate As SqlCommand = New SqlCommand(strSqlUpdate, sqlConnection1) sqlUpdate.CommandType = CommandType.Text sqlDA.UpdateCommand = sqlUpdate sqlDA.UpdateCommand.Parameters.Add(New SqlParameter("@cn", SqlDbType.VarChar, 100, "CN")) sqlDA.UpdateCommand.Parameters.Add(New SqlParameter("@ou", SqlDbType.VarChar, 400, "ou")) sqlDA.UpdateCommand.Parameters.Add(New SqlParameter("@dn", SqlDbType.VarChar, 400, "dn")) sqlDA.UpdateCommand.Parameters.Add(New SqlParameter("@op", SqlDbType.VarChar, 10, "op")) sqlDA.UpdateCommand.Parameters.Add(New SqlParameter("@adsPath", SqlDbType.VarChar, 400, "adsPath")) sqlDA.UpdateCommand.Parameters.Add(New SqlParameter("@reasonCode", SqlDbType.VarChar, 50, "reasonCode")) sqlDA.UpdateCommand.Parameters.Add(New SqlParameter("@targetAddress", SqlDbType.VarChar, 400, "targetAddress")) sqlDA.Fill(dsCurrent, "Table") dsCurrent.Tables(0).TableName = "Incoming" dsCurrent.Tables(1).TableName = "Tracked" dsCurrent.Tables(2).TableName = "Exclusions" dsCurrent.Tables(3).TableName = "CurrAD" dtIncoming = dsCurrent.Tables("Incoming") dtTracked = dsCurrent.Tables("Tracked") dtExclusions = dsCurrent.Tables("Exclusions") dtCurrad = dsCurrent.Tables("currAD") dcPK1(0) = dsCurrent.Tables("Incoming").Columns("targetAddress") dcPK2(0) = dsCurrent.Tables("Tracked").Columns("targetAddress") dcPK3(0) = dsCurrent.Tables("Exclusions").Columns("targetAddress") 'dcPK4(0) = dsCurrent.Tables("Currad").Columns("targetAddress") dtIncoming.PrimaryKey = New DataColumn() {dtIncoming.Columns("TargetAddress")} dsCurrent.Tables("Incoming").PrimaryKey = dcPK1 dtTracked.PrimaryKey = New DataColumn() {dtTracked.Columns("TargetAddress")} dsCurrent.Tables("Tracked").PrimaryKey = dcPK2 dtExclusions.PrimaryKey = New DataColumn() {dtExclusions.Columns("TargetAddress")} dsCurrent.Tables("Exclusions").PrimaryKey = dcPK3 'dtCurrad.PrimaryKey = New DataColumn() {dtCurrad.Columns("TargetAddress")} 'dsCurrent.Tables("Currad").PrimaryKey = dcPK4 Dim dvCurrad As DataView = New DataView(dsCurrent.Tables("Currad"), "", "targetAddress", DataViewRowState.CurrentRows) 'Example find... ' drFIND = dtTracked.Rows.Find("SMTP:brett.mack@usaac.army.mil") Dim iIndex As Integer = 1 For Each drIncoming In dtIncoming.Rows lblGeneral.Text = "Scrubbing record #: " & intRec & " for Tracking & Exclusions." 'Check & modify info if it's in the tracked database... drTracked = dtTracked.Rows.Find(drIncoming.ItemArray(8).ToString) 'Dim intIndex As Integer = dvAllad.Find(strcn) If Not drTracked Is Nothing Then 'If we get a hit, then check the current contacts db 'for a target address match... drvCurrad = dvCurrad.FindRows(drIncoming.ItemArray(8).ToString) 'Okay, if we get a hit on the targetAddress on the Current 'contacts, check to see if the objectGuid matches. If it matches, 'Go ahead and modify the data and set the op to Update... 'Check if it's empty... If drvCurrad.Length >= 1 Then For i = 0 To drvCurrad.Length 'If not, check for a Guid match.... If drTracked.Item(6).ToString = drvCurrad(i).Row(6).ToString Then 'if it matches, reset the appropriate incoming columns drIncoming.Item(1) = drTracked.Item(1) drIncoming.Item(25) = drTracked.Item(25) drIncoming.Item(26) = drTracked.Item(26) drIncoming.Item(27) = "UPDATE" drIncoming.Item(28) = drTracked.Item(28) drIncoming.Item(29) = "TRACKED" Else 'Not yet implemented End If Next End If drExclusions = dtExclusions.Rows.Find(drIncoming.ItemArray(8).ToString) 'Check & see if the contact is in the Excluded database. If so, then 'go ahead and mark it as excluded; this way it won't be touched. If Not drExclusions Is Nothing Then 'drIncoming.Delete() drIncoming.Item(27) = "EXCLUDED" drIncoming.Item(29) = "EXCLUDED" drIncoming.Item(33) = "EXCLUDED" End If iIndex += 1 intRec += 1 End If Next 'This is where I'm pretty sure it should update everything! dsChanges = dsCurrent.GetChanges If Not dsChanges Is Nothing Then sqlDA.Update(dsChanges, "Incoming") dsCurrent.Merge(dsChanges, True) End If sqlCMD.Dispose() sqlDA.Dispose() 'contactInfo = Nothing lblRejects.Text = "REJECTED " & intRejects & " Records." 'sqlReader.Close() closesql(1) End Sub Tag: tell VB when new row inserted in SQL server Tag: 130860
 - 6
 - Help - DataAdapter.Update is not updating Access database Hi all, I'm trying to update a table in an Access database with the records in a table from another Access database that both tables have exact schema. Table one has about 10,000 records and table two usually has 10-20 records. Here is the sample code that works for the most part, but not for the place that changes need to be sent back to the database: 'Table one Dim daGetRecords As New OleDbDataAdapter Dim dsGetRecords As New DataSet daGetRecords.SelectCommand = New OleDbCommand(strSQL, cn) daGetRecords.Fill(dsGetRecords) 'Table 2 from different database, using the same sql statement different conection Dim daRecords As New OleDbDataAdapter Dim dsRecords As New DataSet daRecords.SelectCommand = New OleDbCommand(strSQL, cn2) daRecords.Fill(dsRecords) 'Here I assign primary keys of two tables ' ' 'And merge rows Dim dr As DataRow For Each dr In dsRecords.Tables(0).Rows Dim drow(0) As DataRow drow(0) = dr dsGetRecords.Merge(drow) Next ' I've checked and dsGetRecords has been updated correctly. ' I'm ready to send changes back to the database. ' And this is the place that I'm having problem daGetRecords.Update(dsGetRecords) Please comment: 1. Why update method of dataadapter is not updating database? 2. Is there a better way to do the whole process? Thanks in advance, Roy Tag: tell VB when new row inserted in SQL server Tag: 130859
 - 7
 - Adding column description to Access table, HOW? Hi I'm trying to add a column and it's description to an Access table using the following code: objTable.Columns.Append("RefId", adInteger) objTable.Columns("RefId").Properties("Description").Value = "Reference Id" however I get the following exception: "Item cannot be found in the collection corresponding to the requested name or ordinal." Looking at the Column using a debugger, I can see that the column doesn't contain anything in the Properties collection... But how do I add a Description to this collection ?? TIA Søren Tag: tell VB when new row inserted in SQL server Tag: 130856
 - 8
 - Async Database methods with a Oracle or Postgres The ADO.NET 2.0 SqlDataProvider provides a nice set of async methods to perform database access. Unfortunatly this is the only database provider that I can find that supports these. Is anyone aware of an Oracle implementation that provides Begin/End versions of the Execute Methods? I need a real implementation of this, something similar to the SqlClient approach - so that callbacks happen on IOCP threads and threadpool threads aren't sitting blocked in a Wait the entire time a db operation is pending. It's somewhat surprising that Microsoft didn't provide this generically in the ADO.Net infrastructure, but rather grafted it directly into the SqlDataProvider. -- Chris Mullins Tag: tell VB when new row inserted in SQL server Tag: 130851
 - 9
 - How to Send XML to client I am using VS2005 to develop Asp.Net program. I use the codes below to create a XML stream: How can I set the Stream to client? //----------------------------------------------------------------------------- MemoryStream sm= new MemoryStream(); dsHealth.WriteXml(sm); Tag: tell VB when new row inserted in SQL server Tag: 130849
 - 10
 - Foreign key constraints from DB at runtime I'm trying to figure out how to discover foreign key constraints at runtime by asking the database. All the code samples I see in .NET have you manually create the foreign key constraints. I'm trying to detect them from the database's schema. I'm working with MySQL4.1. When I query the database, I get my UniqueConstraints but I don't get my ForeignKeyConstraints, and wondered if anyone knew how to do it. In my program, the business objects are associated in the database with a main table, and collections within this object are associated by foreign key constraints to other tables. So say I have an object, User. It's associated with the table "user", which is identified by a unique integer key, ObjectId. It contains no foreign key constraints. A User can belong to any number of Groups, and a Group can contain any number of Users, so I have a "groups" table keyed by its own integer ObjectId. It also contains no foreign key constraints. Connecting the two is a "usergroups" table, also keyed by integer ObjectId, and all it contains is two foreign keys. "UserId" refers to "user.ObjectId" and "GroupId" refers to "groups.ObjectId". My code looks like this: StringBuilder sb = new StringBuilder(); sb.Append( "SELECT * FROM " ); sb.Append( strTableName ); using ( OdbcCommand cmd = new OdbcCommand( sb.ToString(), connection ) ) { using ( OdbcDataAdapter da = new OdbcDataAdapter() ) { da.SelectCommand = cmd; // Get the schema for this table and add // the schema for any foreign key tables to the // dataset DataTable table = new DataTable( strTableName ); da.FillSchema( table, SchemaType.Source ); ds.Tables.Add( table ); foreach ( Constraint constraint in table.Constraints ) { if ( constraint is ForeignKeyConstraint ) { ... do stuff... } } } } When you run this code on the "usergroups" table, which has two foreign key constraints, no ForeignKeyConstraints exist. I have my UniqueConstraint on UserId but that's it. If I look in the debugger, table.Constraints.Non-Public members.fLoadForeignKeyConstraint is false, which looks like the culpret. Do I need to set this flag to give me the foreign key constraint? If I do, how do I do it? I see there's the DataTable.ParentRelations and ChildRelations properties but I haven't tried using them because I think they just access the ForeignKeyConstraints. I figured if there are no ForeignKeyConstraints in the collection, I'm going to get the same results if I rip out my code and rewrite it to use DataRelations. Does anyone know how to do this? Thanking you for in advance for your time and thoughts. Shelah Tag: tell VB when new row inserted in SQL server Tag: 130846
 - 11
 - Driver's SQLAllocHandle on SQL_HANDLE_ENV failed I'm trying to connect to an R:Base database on my machine. I created a system DSN using the R:Base provider which connects fine. In my project, I can browse the database by adding this connection in the server explorer so I know it connects just fine. However, when trying to use an ODBCConnection in my app, I get the following error when using the open() method on the connection: Driver's SQLAllocHandle on SQL_HANDLE_ENV failed I was thinking that this was some sort of security error, but I gave the ASPNET account security access to the *.RBx files and even the *.dll provider files. Can anybody shed some light? Tag: tell VB when new row inserted in SQL server Tag: 130843
 - 12
 - Connection timeout - Sleeping Processes - Connection Pools? Why oh Why Hi Everyone, I have a problem with a system in production. Basically we are getting timeouts on getting a connection from the connection pool... I will give the background now... We have a Web Server and a Database Server with the below configurations. Web Server Win2k3 IIS6 Database Win2k3 Sql Server 2000 (8.00.2039 - SP4) We use MSMQ on the Web server to do message processing... So we have one HTTP Application sending messages to another HTTP Application. Lets call the first one MessageSender HTTP Application and the other the MessageProcessor HTTP Application. So we have asynchronous Queue Listeners in the MessageProcessor Application, and to make sure we never get overwhelm with messages I have implemented a thread counting approach to make sure we never have too many threads running at one... At the moment I have set the maximum to 5. Each thread calls a stored procedure on the Database... Anyway for some freak reason... the queue listener stopped listening... and we had a backlog of 8000 messages on one queue... When we restarted the MessageProcessor the queue listener was throttles so no more than 5 threads could run and after about 300 or so messages basically it took up the whole connection pool and then started throwing exceptions... I managed to stop the QueueListener before it brought down the whole application... And I had these observations ... There were similar amounts of "sleeping" processes which only ever disappeared when the Application Pool in IIS was recycled or after a considerable amount of time (I am assuming Garbage Collection Kicked in). So the simple answer is that we are not closing the connections... well we went through the whole application and all of the connections are closed... we tried a close and a dispose call on the connections...Which started throwing the below error (even though we do not reuse connection objects in each thread). ExecuteReader requires an open and available Connection. The connection's current state is Closed So we do not know where to look to next... What could be causing the connections to be held and not reused in the connection pool, is it linked with the sleeping processes? Any Help would be muchly appreciated... Regards Mark Tag: tell VB when new row inserted in SQL server Tag: 130840
 - 13
 - Proper resource deallocation through Dispose() Hi All, Trying to get a grip on the proper deallocation of unmanaged resources. I asked some general questions in another group, here's my ADO question: If I call Dispose on a DbDataAdapter, for example, am I correct in understanding that it will dispose of the DbConnection that it uses? This statement in the .Net 2.0 documentation for DbDataAdapter.Dispose() seems to imply that it does: "Releases all resources used by the Component". It would appear that I'm going to run into problems in this scenario: I have a single DbConnection as an instance variable in a class. Within that class, I have a method that creates, and then disposes of, a DbDataAdapter that uses this connection. The second time I call that method, it will fail because the disposal of the adapter also disposed of the connection (as opposed to just closing it). Or am I totally misinterpreting the documentation? Tag: tell VB when new row inserted in SQL server Tag: 130836
 - 14
 - generating crystal report from multiple tables hi all, i am working on a MS.access project, in this i need to generate a report from multiple tables. I have used stored queries for operation on database tables how to do generate Crystal report from multiple tables?. Regards, sai Tag: tell VB when new row inserted in SQL server Tag: 130834
 - 15
 - Prevent DataRow Deletion Hi! Scenario: A User deletes a row in a DataTable; a MessageBox comes up and asks if he/she really wants to delete the row; in case the answer is "no", the row deletion should be cancelled. There are straightforward solutions for this scenario when an intelligent control like the DataGridView is used, because such control exhibits a "UserDeletingRow" event that provides a CANCEL property to be set to TRUE. But in case no such control is used, I cannot see a clear solution. I have searched the newsgroups for this, and I find two proposals: 1) Use the RowDeleted event of the DataRow by calling DataRow.RejectChanges(). I admit this to be a feasible solution, but it has nasty side effects, like: - the row will first be deleted and only then be "un-deleted" - including possible child rows (possibly several generations of them), that also have to be un-deleted - after the delete, a lot of other actions occur, like position changes, for example, that also have to be undone I conclude that using RowDeleted is not a good a solution (and I guess that the architects of the DataGridView would agree, because they provided something better). 2) Use the RowDeleting event of the DataRow. This looks better, because the RowDeleting event is called before the row is actually deleted. But - helas - this event doesn't have a CANCEL property. In the newsgroups I found a solution based on this idea by David Sceppa (whom I truely admire), which goes like this (I cite): *******David Sceppa speaking****** You could trap for the RowDeleting event and call the DataRow's RejectChanges method if you do not want to allow the deletion. You could also throw an exception if that's appropriate in your application. Here's some sample code I used to accomplish this task: DataTable tbl = new DataTable(); tbl.Columns.Add("CustomerID"); tbl.Columns.Add("CompanyName"); tbl.LoadDataRow(new object[] {"Comp1", "Company #1"}, true); tbl.RowDeleting += new DataRowChangeEventHandler(DeleteHandler); Console.WriteLine(tbl.Rows.Count); try { tbl.Rows[0].Delete(); } catch (Exception ex) { Console.WriteLine(ex.Message); } Console.WriteLine(tbl.Rows.Count); static void DeleteHandler(object sender, DataRowChangeEventArgs e) { e.Row.RejectChanges(); throw new Exception("Deletion disallowed"); } I hope this information proves helpful. *******End of Citation ******* The point here seems to be to throw the exception. As far as I can see, RejectChanges() doesn't do anything, because at this point in time, the row to be deleted still has a RowState of "Unchanged" (this is what my debugger tells me), and you cannot reject a change that has not yet taken place. In David's example, to throw an exception does the job, because the row is deleted *programmatically*, and hence, there is a point where the exception thrown can be caught. But what can I do when the row is deleted by a user action, like, pressing the Delete button on a BindingNavigator? I have no idea where I could catch the exception thrown. Does anybody have an idea? Thanks for any hints. Axel Hecker Tag: tell VB when new row inserted in SQL server Tag: 130828
 - 16
 - Copy all records from access MDB to others via ADONET I'm generating an MDB or XLS or CSV from an access database (MDB) I want to copy all records in the query over to the newly created database and (empty) table. I seen: http://www.w3schools.com/sql/sql_select_into.asp which only shows a basic select into.. Assume i have two connections open, the xls table i can create if it's a must. I wanted to use alias fieldnames so the target may have a different fieldname. Possibly even the target tablename could be different, while this is not an issue yet. Any idea? (besides some dumb and slow row/col loop) Remember, from MDB to XLS e.o.. Tag: tell VB when new row inserted in SQL server Tag: 130826
 - 17
 - Migration Dear All, What is the relevant way of migration of ASP to ASP.net Tag: tell VB when new row inserted in SQL server Tag: 130825
 - 18
 - run multiple SQL statement in OleDbCommand Hello: I am using 2 datagrid in a master-detail, now if I want delete a record in master, I should delete all child record of this master record before I delete the master. I don't know how to run mutiple SQL statement in my custom OleDbCommand? I really appreciate your help. here is the code: Dim cmdDelete As New OleDbCommand("DELETE FROM detail WHERE id = ?", _myUtilDB.irraConnection) 'Dim cmdDelete As New OleDbCommand("DELETE FROM master WHERE id = ?", _myUtilDB.irraConnection) With cmdDelete.Parameters.Add("@p1", GetType(Double)) .SourceColumn = "id" .SourceVersion = DataRowVersion.Original End With _da.DeleteCommand = cmdDelete Tag: tell VB when new row inserted in SQL server Tag: 130816
 - 19
 - Unspecified error OLEDB Hello, I developed an export to Excel that runs in an ASP.NET 1.1 page (code-behind). Sometimes, when calling the Open() method of the OLEDB connection to Excel, an "Unspecified Error" occurs. I have the impression that it has to do with some security on IIS, but cannot figure out exactly what it is. Has anyone encountered any problem and found a solution for the above problem? Thanks. Mike Tag: tell VB when new row inserted in SQL server Tag: 130811
 - 20
 - No dataview item property This was initially posted on the C# forum and was meant to be cross-posted here. Noting first that I'm coming from VS2003 and VB.Net to C# and VS2005 ... Using an untyped dataset, I create a dataview. When I try to use the Item property, "Item" does not appear on the Intellisense dropdown and I also get the build error "'System.Data.DataView' does not containt a definition for 'Item'." The documentation for VS2005 does show Item as a property of a dataview -- can anyone see the issue? DataView dvContacts = new DataView(ds.Tables["dtContacts"]); int intCounter = dvContacts.Count; for (intCounter = 0; intCounter < -1; intCounter++) { intContactID = System.Convert.ToInt32(dvContacts.Item(intCounter).Column["ContactID"]); ... } Tag: tell VB when new row inserted in SQL server Tag: 130810
 - 21
 - DataRelation left outer join different database types Hello, I'm trying to create an application that reads data from two tables, where the one table is stored in a MySQL database (Table 1) and the other is stored in SQL Server (Table 2). I need all the data from table 1 that doesn't exist in table 2, in other words I want to left outer join table 1 on table 2. I prefer not to use the linked server functionality or import the MySQL data into SQL Server. Is it possible to solve this using ADO.NET, for instance using the DataRelation? It seems like the DataRelation only supports inner joins, however maybe I'm missing parts of the functionality. Best regards, Tag: tell VB when new row inserted in SQL server Tag: 130809
 - 22
 - weird connection error This is a multi-part message in MIME format. ------=_NextPart_000_0006_01C6A4FD.93E69080 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Name have been changed to protect the innocent.=20 This is the only class that is causing me this issue and I can't figure = out why. Everything before this one loads fine, and the ones after this = load fine. I have checked the underlying tables and the user has proper = permissions. I do not get the error, but I have more permissions than he = does. The other person using the application does not get this error = either, but he has elevated privileges too...I have done everything I = can think of and it is still occurring ... The bit of code that is causing this is simply a matter of loading a new = instantiation of a product class from dsinvmgmt.products.getallproducts. The class that is actually the editor for that class loads before it but = in a different class. So I wouldn't think that would be interfering. = Below is the message of the ex.tostring. --------------------------- System.Data.SqlClient.SqlException: Login failed for user 'DOMAIN\user'. at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException = exception, Boolean breakConnection) at = System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateOb= ject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, = SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet = bulkCopyHandler, TdsParserStateObject stateObj) at = System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean = enlistOK) at = System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnect= ion owningObject, SqlConnectionString connectionOptions, String = newPassword, Boolean redirectedUserInstance) at = System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIden= tity identity, SqlConnectionString connectionOptions, Object = providerInfo, String newPassword, SqlConnection owningObject, Boolean = redirectedUserInstance) at = System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionO= ptions options, Object poolGroupProviderInfo, DbConnectionPool pool, = DbConnection owningConnection) at = System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbCon= nection owningConnection, DbConnectionPool pool, DbConnectionOptions = options) at = System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection = owningObject) at = System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection = owningObject) at = System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection = owningObject) at = System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection = owningConnection) at = System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection = outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, = DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String = srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, = Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior = behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable) at = dsInvmgmtDataSetTableAdapters.ProductsTableAdapter.GetAllProducts() in = dsInvmgmtDataSet.Designer.vb:line 16398 at Designers.Plant_Orders_Designer.initializeTables() in = Plant_OrdersDesigner\Plant_Orders_Designer.vb:line 260 ---------------------------------------------------------------------- this is line 260: SetProducts(ProductsTableAdapter.GetAllProducts) ------=_NextPart_000_0006_01C6A4FD.93E69080 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META http-equiv=3DContent-Type content=3D"text/html; = charset=3Diso-8859-1"> <META content=3D"MSHTML 6.00.2900.2873" name=3DGENERATOR> <STYLE></STYLE> </HEAD> <BODY bgColor=3D#ffffff> <DIV> <SPAN=20 style=3D"FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: navy; FONT-STYLE: = italic; FONT-FAMILY: 'Times New Roman'">Name=20 have been changed to protect the innocent. <SPAN=20 style=3D"FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: navy; FONT-STYLE: = italic; FONT-FAMILY: 'Times New Roman'">This=20 is the only class that is causing me this issue and I can't figure out = why.=20 Everything before this one loads fine, and the ones after this load = fine. I have=20 checked the underlying tables and the user has proper permissions. I do = not get=20 the error, but I have more permissions than he does. The other person = using the=20 application does not get this error either, but he has elevated = privileges=20 too...I have done everything I can think of and it is still occurring=20 ... <SPAN=20 style=3D"FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: navy; FONT-STYLE: = italic; FONT-FAMILY: 'Times New Roman'">The=20 bit of code that is causing this is simply a matter of loading a new=20 instantiation of a product class from=20 dsinvmgmt.products.getallproducts. <SPAN=20 style=3D"FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: navy; FONT-STYLE: = italic; FONT-FAMILY: 'Times New Roman'">The=20 class that is actually the editor for that class loads before it but in = a=20 different class. So I wouldn't think that would be interfering. Below is = the=20 message of the ex.tostring. <SPAN=20 style=3D"FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: navy; FONT-STYLE: = italic; FONT-FAMILY: 'Times New Roman'">  <FONT=20 face=3D"Times New Roman" color=3Dnavy size=3D4><SPAN=20 style=3D"FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: navy; FONT-STYLE: = italic; FONT-FAMILY: 'Times New = Roman'">--------------------------- <FONT=20 face=3D"Times New Roman" color=3Dnavy size=3D4><SPAN=20 style=3D"FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: navy; FONT-STYLE: = italic; FONT-FAMILY: 'Times New = Roman'">System.Data.SqlClient.SqlException:=20 Login failed for user 'DOMAIN\user'. <FONT=20 face=3D"Times New Roman" color=3Dnavy size=3D4><SPAN=20 style=3D"FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: navy; FONT-STYLE: = italic; FONT-FAMILY: 'Times New Roman'">  =20 at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException = exception,=20 Boolean breakConnection) <FONT=20 face=3D"Times New Roman" color=3Dnavy size=3D4><SPAN=20 style=3D"FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: navy; FONT-STYLE: = italic; FONT-FAMILY: 'Times New Roman'">  =20 at = System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateOb= ject=20 stateObj) <FONT=20 face=3D"Times New Roman" color=3Dnavy size=3D4><SPAN=20 style=3D"FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: navy; FONT-STYLE: = italic; FONT-FAMILY: 'Times New Roman'">  =20 at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, = SqlCommand=20 cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet = bulkCopyHandler,=20 TdsParserStateObject stateObj) <FONT=20 face=3D"Times New Roman" color=3Dnavy size=3D4><SPAN=20 style=3D"FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: navy; FONT-STYLE: = italic; FONT-FAMILY: 'Times New Roman'">  =20 at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean=20 enlistOK) <FONT=20 face=3D"Times New Roman" color=3Dnavy size=3D4><SPAN=20 style=3D"FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: navy; FONT-STYLE: = italic; FONT-FAMILY: 'Times New Roman'">  =20 at = System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnect= ion=20 owningObject, SqlConnectionString connectionOptions, String newPassword, = Boolean=20 redirectedUserInstance) <FONT=20 face=3D"Times New Roman" color=3Dnavy size=3D4><SPAN=20 style=3D"FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: navy; FONT-STYLE: = italic; FONT-FAMILY: 'Times New Roman'">  =20 at = System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIden= tity=20 identity, SqlConnectionString connectionOptions, Object providerInfo, = String=20 newPassword, SqlConnection owningObject, Boolean=20 redirectedUserInstance) <FONT=20 face=3D"Times New Roman" color=3Dnavy size=3D4><SPAN=20 style=3D"FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: navy; FONT-STYLE: = italic; FONT-FAMILY: 'Times New Roman'">  =20 at=20 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionO= ptions=20 options, Object poolGroupProviderInfo, DbConnectionPool pool, = DbConnection=20 owningConnection) <FONT=20 face=3D"Times New Roman" color=3Dnavy size=3D4><SPAN=20 style=3D"FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: navy; FONT-STYLE: = italic; FONT-FAMILY: 'Times New Roman'">  =20 at=20 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbCon= nection=20 owningConnection, DbConnectionPool pool, DbConnectionOptions=20 options) <FONT=20 face=3D"Times New Roman" color=3Dnavy size=3D4><SPAN=20 style=3D"FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: navy; FONT-STYLE: = italic; FONT-FAMILY: 'Times New Roman'">  =20 at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection=20 owningObject) <FONT=20 face=3D"Times New Roman" color=3Dnavy size=3D4><SPAN=20 style=3D"FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: navy; FONT-STYLE: = italic; FONT-FAMILY: 'Times New Roman'">  =20 at = System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection = owningObject) <FONT=20 face=3D"Times New Roman" color=3Dnavy size=3D4><SPAN=20 style=3D"FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: navy; FONT-STYLE: = italic; FONT-FAMILY: 'Times New Roman'">  =20 at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection=20 owningObject) <FONT=20 face=3D"Times New Roman" color=3Dnavy size=3D4><SPAN=20 style=3D"FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: navy; FONT-STYLE: = italic; FONT-FAMILY: 'Times New Roman'">  =20 at = System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection=20 owningConnection) <FONT=20 face=3D"Times New Roman" color=3Dnavy size=3D4><SPAN=20 style=3D"FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: navy; FONT-STYLE: = italic; FONT-FAMILY: 'Times New Roman'">  =20 at = System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection=20 outerConnection, DbConnectionFactory=20 connectionFactory) <FONT=20 face=3D"Times New Roman" color=3Dnavy size=3D4><SPAN=20 style=3D"FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: navy; FONT-STYLE: = italic; FONT-FAMILY: 'Times New Roman'">  =20 at System.Data.SqlClient.SqlConnection.Open() <FONT=20 face=3D"Times New Roman" color=3Dnavy size=3D4><SPAN=20 style=3D"FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: navy; FONT-STYLE: = italic; FONT-FAMILY: 'Times New Roman'">  =20 at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, = DataTable[]=20 datatables, Int32 startRecord, Int32 maxRecords, String srcTable, = IDbCommand=20 command, CommandBehavior behavior) <FONT=20 face=3D"Times New Roman" color=3Dnavy size=3D4><SPAN=20 style=3D"FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: navy; FONT-STYLE: = italic; FONT-FAMILY: 'Times New Roman'">  =20 at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32=20 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior=20 behavior) <FONT=20 face=3D"Times New Roman" color=3Dnavy size=3D4><SPAN=20 style=3D"FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: navy; FONT-STYLE: = italic; FONT-FAMILY: 'Times New Roman'">  =20 at System.Data.Common.DbDataAdapter.Fill(DataTable=20 dataTable) <FONT=20 face=3D"Times New Roman" color=3Dnavy size=3D4><SPAN=20 style=3D"FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: navy; FONT-STYLE: = italic; FONT-FAMILY: 'Times New Roman'">  =20 at dsInvmgmtDataSetTableAdapters.ProductsTableAdapter.GetAllProducts() = in=20 dsInvmgmtDataSet.Designer.vb:line 16398 <FONT=20 face=3D"Times New Roman" color=3Dnavy size=3D4><SPAN=20 style=3D"FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: navy; FONT-STYLE: = italic; FONT-FAMILY: 'Times New Roman'">  =20 at Designers.Plant_Orders_Designer.initializeTables() in=20 Plant_OrdersDesigner\Plant_Orders_Designer.vb:line = 260 <FONT=20 face=3D"Times New Roman" color=3Dnavy size=3D4><SPAN=20 style=3D"FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: navy; FONT-STYLE: = italic; FONT-FAMILY: 'Times New = Roman'">-----------------------------------------------------------------= -----<FONT=20 face=3D"Times New Roman" color=3Dnavy size=3D4><SPAN=20 style=3D"FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: navy; FONT-STYLE: = italic; FONT-FAMILY: 'Times New Roman'"><FONT=20 size=3D2> this is line 260:=20 SetProducts(ProductsTableAdapter.GetAllProducts)   <FONT=20 face=3D"Times New Roman" color=3Dnavy size=3D4><SPAN=20 style=3D"FONT-WEIGHT: bold; FONT-SIZE: 14pt; COLOR: navy; FONT-STYLE: = italic; FONT-FAMILY: 'Times New = Roman'"> </DIV></BODY></HTML> ------=_NextPart_000_0006_01C6A4FD.93E69080-- Tag: tell VB when new row inserted in SQL server Tag: 130806
 - 23
 - First Connection To DB2 Hello, I have used mainly Oracle and VS, but now I am taking a crack at DB2. I am using DB2 Mainframe. If I go to the DB2 software which is installed on my machine amd bring up "information center", it brings back the following information. =========================================== About DB2 Administration Tools Environment =========================================== DB2 administration tools level: Product identifier SQL08015 Level identifier 02060106 Level DB2 v8.1.5.540 Build level special_10709 PTF WR21334 =================================== Java development kit (JDK): Level IBM Corporation 1.3.1 =================================== Is there a .Net provider for DB2? I have gone to their web site and I can see a reference to a .Net provided but I am unsure if it is applicable to the version of DB2 which I am using. What do I need to know to determine if I can/should loade the .Net provider for VS 2005? I am able to connect to DB2 using the DSN in the in the "ODBC Data Source Administration window". I am also seemingly able to connect from within VS 2005, but when I click on (+) on the tables object, I get an error message saying "[ERROR] 56072 .. Function not supported by server". Why am I getting this. I have used WinSql and it provides me this information (Table names, attribute names, etc.) just fine(although it takes about 5 minutes to retrieve all of the tables), so why doesn't VS 2005? Tag: tell VB when new row inserted in SQL server Tag: 130803
 - 24
 - how to query a .Net(2.0) dataTable? Hello, I pull sql server data into a .Net (2.0) dataTable (vb2005 app) using criteria like CompanyName = '3com%' which will return every variation of '3com' in the source data like '3com inc', '3com Group'.... The rows all contain a recordID column. The recordID column in this datatable will contain non-unique recordIDs. I need to retrieve/extract all distinct/unique recordIDs contained in this resultant dataTable. Is there a method/mechanism to perform this operation on my .Net dataTable from within my application? What is this method/mechanism? Maybe a control or some object? I looked at the dataview object, but that only perfroms sorting and filtering. Or am I limited to have to write an additional query against the source data in the sql server? THe issue here is that the source data tables are very large. I would like to keep the number of calls to the server to a minimum. Ideally, I would like to write a query against the .Net dataTable within the application. Select Distinct recordID From myDataTable Thanks, Rich Tag: tell VB when new row inserted in SQL server Tag: 130799
 - 25
 - Selecting Dates for database connection I have a calendar control that I am using to have a person select a date. Then I connect to a database and I want to display anything that is equal to that date or where the date selected is atleast between the start date and end date. I just don't know how I am suppose to get this to work in SQL. Here is my code Dim selectedDate As String = cldrEvents.SelectedDate.ToString("d") Dim sqlConnectionString As String sqlConnectionString = ConfigurationManager.ConnectionStrings("eventsConnectionString").ConnectionString.ToString Dim sqlConnection As New SqlConnection(sqlConnectionString) This below is the part I don't understand. I want selectedDate to be compared between events_startdate and events_enddate. If the selectedDate is between or equal to the events_starttime and events_endtime then display the event. How do I get this to happen? Should I be converting selectedDate to a DateTime object before putting it into the the sql command? Dim sqlCommand As New SqlCommand("", sqlConnection) sqlConnection.Open() Dim sqlReader As SqlDataReader = SqlCommand.ExecuteReader() gdvEvents.DataSource = sqlReader gdvEvents.DataBind() Tag: tell VB when new row inserted in SQL server Tag: 130795

I am running SQL server 2005 and want to be notified in VB when a new
row is inserted on a particular table.
Can I link to the table in visual express 2005 and have an event fired
when there is an insert on a particular table?

thanks

j

Top

Re: tell VB when new row inserted in SQL server by William

William
Tue Jul 18 11:16:47 CDT 2006

It's possible to setup a SQL Dependency that tracks a table (a selected
subset) for changes.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

<JoelH@GlobalF.com> wrote in message
news:1153234852.554253.124280@m79g2000cwm.googlegroups.com...
>I am running SQL server 2005 and want to be notified in VB when a new
> row is inserted on a particular table.
> Can I link to the table in visual express 2005 and have an event fired
> when there is an insert on a particular table?
>
> thanks
>
> j
>

Top