sqlBulkCopy

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
    - DataTextFormatField: {0:d/60} ??? Hi over there, I've got a DataBoundField in a GridView which name is (both gridview-column and the column in the database) "TimeInMinutes". And I want another column, that displays the hours, means the same value as in the TimeInMinutes-Column divided through 60. I tried to achieve that with the DataTextFormat field, but I've got no luck. Is that possible anyway, or do I have to do that by manual by code? DataTextField: TimeInMinutes DataTextFormatField: {0:d/60} ??? How can I achieve that I've got another column in my grid view, that is not existent in the database, but visible in the gridview, with the value of the database-column "TimeInMinutes" displayed in hours? any help appreciated, cheers, Markus Kofler Tag: sqlBulkCopy Tag: 144346
  - 2
    - 2008 Express Edition missing Data drivers? I just started doing some basic exercises with Visual Basic 2008 Express Edition (Version 9.0.21022.8 RTM, Microsoft .NET Framework Version 3.5) and I do not seem to have what I would call routine data drivers installed. Initially, I only want to connect to a Northwinds db in my local SQL Server 2000 instance but the only drivers available are Microsoft Access DataBase file, Microsoft Sql Server Compact 3.5, and Microsoft SQL Server Database File. I don't need Access and the other two don't seem to create db connections in a manner I am familiar with. What am I missing and how do I add it? Or is it impossible to do 'normal' database connections in the Express version? Tag: sqlBulkCopy Tag: 144345
  - 3
    - Wierd error on data set Occassionaly (and its rare, but i think there are repo steps to this, i just cant completely determin them, but here is what I figured out so far) I have a data set (say its called DS) I have three tables... call them people, peoplejunitem and item... peoplejunitem is a many to many junction table between people and items... well say I load one set of information about a person into the data set's tables... ok this person has items in the junction... say I alter the information in the junction but dont commit it to the database... now I go and load another person... but while loading the next person it clears the information in the junction table and the person table and replaces it with the 2nd person... ok I make changes and push the data back to the database... and boom error... zero rows updated when 1 was expected to be... well that one row updates was from the FIRST! person... even though I did a ds.table("PersonJunItems").rows.clear somehow it still thinks it has rows to push! why?! this happens randomly to me! I tested out the T-SQL statements like crazy and they are not at fault, they are return the correct response, but while watching the SQL Server profiler I saw the commands to update the pervious person going through on person 2 to the database... any ideas how to stop this or fix the problem? or anyone ever see anything similar to this? thanks! Tag: sqlBulkCopy Tag: 144342
  - 4
    - Open file suddenly stops existing?! A file is successfully opened, partly read, and not closed, but suddenly it stops existing? How does Ado.Net accomplish this result? This seems to be a 100% repro on one file but 100% no problem on a different file. Again how does Ado.Net do this? // Sample dirName is C:\myDir and tableName is myFile1.csv or myFile2.csv. OleDbConnection connection = new OleDbConnection( "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dirName + ";Extended Properties='Text;Extensions=asc,csv,tab,txt;" + "HDR=Yes;FMT=Delimited'"); connection.Open(); OleDbDataAdapter dataAdapter = new OleDbDataAdapter( "SELECT * FROM [" + tableName + "]", connection); DataTable dataTable = new DataTable(tableName); dataAdapter.FillSchema(dataTable, SchemaType.Source); DataColumnCollection dataColumns = dataTable.Columns; int fileColumnCount = dataColumns.Count; foreach (DataColumn dataColumn in dataColumns) { dataColumn.DataType = typeof(string); } // This works up to here. FillSchema worked. // The next call works on myFile1.csv but fails on myFile2.csv. dataAdapter.Fill(dataTable); // Exception: file does not exist. Wtf? Tag: sqlBulkCopy Tag: 144338
  - 5
    - Returning the id of an inserted record I am writing an ASP.NET application, and am forced to use Access as my database. I need a way to return the id (which is an AutoNumber field in the table I am inserting into) of a record when I insert it. In SQL Server, I am able to use a statement like the following: INSERT INTO tablename (fieldnames) OUTPUT inserted.id VALUES(valuestoinsert) Take note of the OUTPUT inserted.id part of this statement. This returns the value of the field named 'id' for the record that was just inserted. Unfortunately, this technique does not work in Access. Is there any way to retrieve the id of the record that was just inserted when using Access? Any help would be appreciated. Thanks. -- Nathan Sokalski njsokalski@hotmail.com http://www.nathansokalski.com/ Tag: sqlBulkCopy Tag: 144336
  - 6
    - Logging exceptions to Database using Enterprise Library 3.1 Can somebody direct me to an example of logging exceptions to SQL Server Database using Enterprise Library 3.1 Exception Handling block. I created database listener. But it's doing nothing. Thank You Reddy Tag: sqlBulkCopy Tag: 144331
  - 7
    - How to solve DataSet Designer SQL errors? Hi there, I have writing easy to moderate SQL in the DataSet Designer and Query Builder, using a Jet 4.0 connection to an Access 2003 DB. I am running VB.NET 2005 sp1. As soon as I try to do anything more than the most trivial SQL (e.g. simple JOINs, more than 1 parameter other than in the WHERE clause, etc.), the Designer/Query Builder balks giving SQL parsing error messages. This means that the Fill, GetData() is not set up properly and compiler errors prevent me from running my app. This is despite the fact that the SQL is perfectly fine (it runs in Access) and in some situations it is possible to 'trick' the Designer (ref. link below). Aside from the critical issue of my app not running, I am not able to leverage strongly typed DataSets, and all the useful commensurate objects (e.g. DataTable, data row). I have been promoting this approach within our development teams, but all these problems make significantly reduce confidence in using these objects and the Designer. Nor it is sensible to use a blend of approaches in my app (e.g. trivial SQL through strongly-typed DataSets and the rest explicitly written in code) from a software design/maintenance perspective. If this can't be solved, I'll look at dropping use of the Designer and going back to hand-coded SQL. Any and all feedback that provides solutions to this problem (e.g. is there an update/patch to the Designer?) would be appreciated. Thanks in advance, David Newsgroup posting "Dataset designer and complex Stored procedure" on microsoft.public.dotnet.framework.adonet http://groups.google.ca/group/microsoft.public.dotnet.framework.adonet/browse_thread/thread/9c0a1938b49329ea/68917fe53b75058c?lnk=st&q=%22dataset+designer%22+sql+error#68917fe53b75058c Tag: sqlBulkCopy Tag: 144304
  - 8
    - OleDbCommandBuilder insert query will not work? dataset to Access I am trying to take a very strangely delimited text file and put it into access. So far I was able to get the text file into a dataset and figured from there I could get it into access. I am trying to use the code below but the insert query(oledbcommandbuilder) just will not work. I am working with a blank access database and I am able to get the table and the field names in the database(with a primary key just set ot the first row). I just cannot get the rest of the data. I get an error that there is incorrect syntax in the insert query. Can someone please help! I've been all over the site reading a buch of different things and tried to just write inserts for each row but cannot get that to work either. Thank you! DataSet result = new DataSet(); ...get delimited file into dataset... OleDbConnection DB = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\\Conversions\\" + agencyid + "\\" + agencyid + ".mdb"); OleDbDataAdapter DataAdapter = new OleDbDataAdapter(); DataAdapter.SelectCommand = new OleDbCommand("SELECT * FROM " + TableName, DB); OleDbCommandBuilder CmdBuilder = new OleDbCommandBuilder(DataAdapter); DB.Open(); DataAdapter.FillSchema(result, SchemaType.Source, TableName); DataAdapter.InsertCommand = CmdBuilder.GetInsertCommand(); //I added the line above because the insert query did not show in the data adapter. //It now has: insert into tablename(fieldname1,fieldname2...) Values (?,?,?...) DataAdapter.Fill(result, TableName); DataAdapter.Update(result, TableName);//Fails here on the insert query syntax DB.Close(); Tag: sqlBulkCopy Tag: 144295
  - 9
    - timeout error Hi, I've a method in my web service..which makes a call to db and sends back a dataset.. the query takes 2 mins to fetch the results..by that time i'm seeing a 'operation has timed out error' on my screen.. is there any to get rid of this error? Tag: sqlBulkCopy Tag: 144291
  - 10
    - Problem with AdomdCommand with passing DataTable with DBNull in some cells The following code would fail, with the error: "The '' string cannot be converted to the double type." when trainTable contains rows with DBNull in some of the columns. How can I pass NULL to MSAS? using (AdomdCommand comm = connAS.CreateCommand()) { comm.CommandText = "INSERT INTO MINING MODEL [MyModel] ([statbbrev],[lpop],[ldem],[lemp]) @TrainTable"; comm.Parameters.Add("TrainTable", trainTable); comm.ExecuteNonQuery(); } Tag: sqlBulkCopy Tag: 144284
  - 11
    - Problem with AdomdCommand when Passing DataTable with DBNull's The following code would fail, with the error: "The '' string cannot be converted to the double type." when trainTable contains rows with DBNull in some of the columns. How can I pass NULL to MSAS? using (AdomdCommand comm = connAS.CreateCommand()) { comm.CommandText = "INSERT INTO MINING MODEL [MyModel] ([statbbrev],[lpop],[ldem],[lemp]) @TrainTable"; comm.Parameters.Add("TrainTable", trainTable); comm.ExecuteNonQuery(); } Tag: sqlBulkCopy Tag: 144283
  - 12
    - ORA-01406: fetched column value was truncated Using data reader against one of my Oracle tables is crashing with following error: System.Data.OracleClient.OracleException: ORA-01406: fetched column value was truncated. This happens with Microsoft ADO driver to Oracle. Oracle ADO driver works just fine. Error happens only with some records (there are some pretty large columns in a table), but not other. I wonder if anyone has any insights to this issue â?? we donâ??t want to compile Oracle drivers into our base applications, because most of our customers are using SQL Server. Thanks in advance. Tag: sqlBulkCopy Tag: 144282
  - 13
    - Need some advice on my Deployment Pkg Hello. Using XP Pro w/SP4 and VS 2003. I wrote my program with the connection string and the path for loading the Maps as follows: C:\MVB.NDbProJim\Ch7\ROE6AllRecords2\Maps. The Maps sub-directory containes all 116 Maps of the Lots and also the ROE6.mdb database. But when I deplay this app to my other PC it gets an error that this path is invalid so it cannot even load the database. Since the MSI installs this package to C:\Program Files\Microsoft\ROE6 I decided to have the Maps subdirectory relocated to this Path. I had to change (in my program) the Connection String and all the 116 paths for it to load the Maps but it now works just fine on the other PC. I must have missed something because it is unreasonable to have to keep the support files at the same location on my PC as the installer is going to use on the target PC. Any words of wisdom to get me straightened out? TIA, Jim Tag: sqlBulkCopy Tag: 144279
  - 14
    - problem with excel hi, i'm facing a strange problem.. In my requirement , I'm suppose to load the data from datagrid into excel file. make the required changes in the file and load the file back into the datagrid. i'm able to export to excel but after i make changes in the excel and load it into grid, my changes are not visible..i'm getting null values.. i've datetime columns,decimal columns in my file...The values are visible in the file but when i load into grid..i'm seeing blanks spaces.. has anyone encountered such problem.? How can load from excel to datagrid Tag: sqlBulkCopy Tag: 144277
  - 15
    - LINQ passing Array for a parameter I have a web page where a user can select multiple items for use in a select statement. SELECT columns FROM table WHERE ID in (1,2,3). Since its a web application I use a stored procedure to get the results. Currently my sproc GetResults (@EventIdList varChar(@256)) uses a table value function that parses the item list and returns a table. I use this method because I don't do dynamic SQL in sprocs and sprocs can't take arrays. Select columns from MyTable INNER JOIN dbo.ParseIntToTable(@EventIdList,',') evn on MyTable.EventTypeID = evn.ColumnValue ALTER FUNCTION [dbo].[ParseIntToTable] ( @StringList varchar(8000), @Delimiter char(1) ) RETURNS @TempTable table ( ColumnValue int ) AS BEGIN DECLARE @StringValue varchar(1000) -- value taken off list DECLARE @Pos int -- position of delimiter SET @StringList = LTRIM(RTRIM(@StringList)) IF RIGHT(@StringList,1) <> @Delimiter SET @StringList = @StringList + @Delimiter SET @Pos = CHARINDEX(@Delimiter, @StringList, 1) IF REPLACE(@StringList, @Delimiter, '') <> '' BEGIN WHILE @Pos > 0 BEGIN SET @StringValue = LTRIM(RTRIM(LEFT(@StringList, @Pos - 1))) IF @StringValue <> '' BEGIN INSERT INTO @TempTable (ColumnValue) VALUES (CAST(@StringValue AS int)) END SET @StringList = RIGHT(@StringList, LEN(@StringList) - @Pos) SET @Pos = CHARINDEX(@Delimiter, @StringList, 1) END END RETURN In LINQ how do you write a query that takes an array for a parameter. ( select columns from table where ID IN (1,2,3) ). The query result will be used in a GridView and needs to support paging and sorting. thanks, Tag: sqlBulkCopy Tag: 144275
  - 16
    - OracleDataReader Returns No Records With Nested SQL Hello All, Can anyone tell why one sql statement works with the data reader but not the other. To test, I uncomment the string I need. Dim QueryString As String ***This string does not work 'QueryString = "SELECT * FROM print_server WHERE LS = (SELECT LS FROM vw_turboinfo WHERE tsn = '080226086')" ***This string does work, and the result returned does exist in print_server 'QueryString = "SELECT LS FROM vw_turboinfo WHERE tsn = '080226086'" ***This string does work 'QueryString = "SELECT * FROM print_server WHERE LS = 4042390" Dim Command As New OracleCommand(QueryString, oraCon) Command.CommandType = CommandType.Text ***I have copied the CommandText into TOAD for Oracle for each of the 3 ***strings, and each is valid and returns the correct information. Debug.Print(Command.CommandText) Dim reader As OracleDataReader Try reader = Command.ExecuteReader While reader.Read Debug.Print(reader.Item(1)).ToString End While Catch ex As Exception MsgBox(ex.Message) Finally End Try Tag: sqlBulkCopy Tag: 144267
  - 17
    - IIS ASP.NET 2.0 Not creating a connection pool nor re-using I need some help! I have a webapp in ASP.NET 2 running on 2003 R2 that doesn't seem to want to bring up a connection pool. When I run a trace on the DB server I can see Audit Logoff and Audit Logon between query batches, which leads me to believe that this is occurring. This is a replacement platform for our current live systems, so I have just tried to build everything as similarly as possible throughout. The main difference is that the DB server is now SQL2005 SP2 whereas before it was SQL2000. I haven't read anything indicating that this would be the cause, but I am very open to ideas. The connection string I am using is: Application Name='Live';pooling=true;Min Pool Size=1;Max Pool Size=1000;database=DB1;User ID=USER ;Password=PASSWORD;Server=live- db1; I cannot find any way to verify information on connection pooling working, other than by using SP_WHO - which does appear to show an idle connection or two from the webapp, but like I say, I can see the connections being torn down and back up every batch from the profiler trace. The reason I'm investigating this is that the application is serving pages incredibly slowly when they have to hit the database, but I've tried running the query batches directly through MSMS on both the DB server and App servers, and they are very fast - much faster than when run via .NET. This connection latancy is the only thing I can put it down to at the moment. I'd welcome any insight. Jon Tag: sqlBulkCopy Tag: 144266
  - 18
    - datagrid to excel Hi, I;ve a requirement wherein the need to export the data from grid to excel file using .net 1.1.. While I do the export to file, sfew columns in teh excel file should be locked, in the sense..use shouldn't be able to edit the values in those columns... is this possible thru C#? any samples Tag: sqlBulkCopy Tag: 144263
  - 19
    - Can't add a new row to a database Why won't this code add a row to my database? It runs as expected but when I look in the SQL database using SQL Server Management Studio the table is empty. DataRow newRow = this.dsHealthClub.NotInHouse.NewRow(); newRow["Name"] = dsEmployees.Employees.Rows[0].ItemArray.GetValue(0); newRow["DateOut"] = DateTime.Now; newRow["Comments"] = ""; newRow["Badge"] = visitorID; this.dsHealthClub.NotInHouse.Rows.Add(newRow); Tag: sqlBulkCopy Tag: 144261
  - 20
    - Database Table and XML Synchroniazation Hello to all, Is it possible to sychronize a Database Table and an XML file. To be more specific, I want to read data from a database, convert them to XML, make changes to the XML and then update the Database table from the XML file. Is it possible? Regards Dimitris Tag: sqlBulkCopy Tag: 144260
  - 21
    - a problem about connect to access with ODBC System.Data.Odbc.OdbcConnection conn = new System.Data.Odbc.OdbcConnection(@"Driver={Microsoft Access Driver(*.mdb)};Dbq=C:\abc.mdb;Uid=Admin;Pwd="); when invoke conn.Open(); the program throw an OdbcException ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified Tag: sqlBulkCopy Tag: 144252
  - 22
    - Must delcare the scalar variable I am running an Update routine that calls a function. It appears that the function never executes because I receive: Must Declare The Scalar Variable @Country. Code: Dim newCustomer As New Customer newCustomer.CustomerID = customer.CustomerID Me.PutCustomerData(newCustomer) Try If Not CustomerDB.UpdateVendor(customer, newCustomer) Then MessageBox.Show("Another user has updated or deleted that customer.", _ "Database Error") Me.DialogResult = Windows.Forms.DialogResult.Retry Else customer = newCustomer Me.DialogResult = Windows.Forms.DialogResult.OK End If Catch ex As Exception MessageBox.Show(ex.Message, ex.GetType.ToString) End Try The only time @Country is used is in a Insert function. In the Update fuction, I use @NewCountry and @OldCountry, so I am not sure why this is happening. Please help. Tag: sqlBulkCopy Tag: 144251
  - 23
    - Incresase the Excution Time? Hi EveryBod: I have an object cmd as SqlCommand object and and I face problem with safe large data by using this object there is an errore appear tell me that the excution time out. Sombody can tell me how can I increase the Excution time? I am using SQL server 2005 any help will be appreciated regard's Husam Tag: sqlBulkCopy Tag: 144250
  - 24
    - DataAdapter Solution with support for paging? Hello, is there are ready component (maybe an data adapter) existing which has advanced features like paging (with server cursors) but still allows merging? Thank you, Frank Tag: sqlBulkCopy Tag: 144249
  - 25
    - Attempt on SqlConnection.BeginOpen I have a need for a SqlConnection.Open that times out in an predictable matter (i.e. if the server does not answer, I do not have to wait for TCP or NIC timeouts to find out). The "Connection Timeout" in the connectionstring will not help, as this only applies to the connection to the SQL Server, not to the actual TCP port. Therefore I have had a crack at it myself, and would appreciate any constructive comments on the implementation. The method BeginOpen(SqlConnection,int) will take an SqlConnection and a timeout (in ms), and will try to open it. If it for any reason cannot, it will return within the specified timeout. If there is an exception in connection before the timeout occurs, it will throw that, otherwise it will throw a custom Exception. There is an 4-5 ms overhead by doing this, but I'm willing to sacrifice this for a predictable timeout. Any comments appreciated... private void BeginOpen(SqlConnection connection, int msTimeout) { // Start timeout timer here, it includes all work done in this method. // This assures that this method uses no more than msTimeout to complete DateTime connectionAbortTime = DateTime.Now.AddMilliseconds(msTimeout); // Transfer ambient transaction to thread Transaction currentTransaction = Transaction.Current; // Save the thread exception (if any) Exception threadException = null; // Signifies that we could connect and communicate with the database bool connectSuccess = false; Thread workerThread = new Thread( delegate() { try { Transaction.Current = currentTransaction; connection.Open(); // This might hang indefinatly connection.ChangeDatabase(connection.Database); connectSuccess = true; } catch (Exception ex) { threadException = ex; } } ); workerThread.IsBackground = true; // Make sure application tears it down when it exits workerThread.Name = "SqlConnection.BeginOpen"; // Start work and wait for the thread to finish or it times out workerThread.Start(); while (connectionAbortTime > DateTime.Now) if (workerThread.Join(1)) // Check if thread is finished break; // For some reason, the connection could not be opened, throw an exception if (!connectSuccess) { if (threadException != null) // Encountered an exception before timeout throw threadException; else // Either timed out OR did not have time to save exception in threadException throw new SqlConnectionTimeoutException("Could not connect to server within timeout"); } } public class SqlConnectionTimeoutException : Exception { public SqlConnectionTimeoutException(string message) : base(message) {} } Tag: sqlBulkCopy Tag: 144248
- Next
  - 1
    - Disabling EnforceConstraints Hi Is there a way to disable EnforceConstraints on a dataset? I have this statement ds.EnforceConstraints = true in MyDataSet.Designer.vb but I am reluctant to set it to false as the designer may overwrite the code. I only wish to do it temporarily to speed up fill operations. Thanks Regards Tag: sqlBulkCopy Tag: 144239
  - 2
    - Filling a DataTable causing RowState to change in another table I have 2 separate DataTables in a DataSet. These tables are in no way related to one anohter with Relations. When I Fill one table, it causes a row's RowState in the other table to change from Unchanged to Modified. In the absence of editing the row! Can anyone think of ways to figure out what might be causing this? -- Michael Tag: sqlBulkCopy Tag: 144234
  - 3
    - ADO.NET query execution much slower than SQL Management Studio I have a stored procedure that when called from SQL 2005 Management Studio takes less than one second to execute. The same SP, when called from .NET code takes about 13 seconds. I am using a SqlCommand object with CommandType set to StoredProcedure and I am passing arguments through the parameters collection. I have tried using a SqlDataReader and a DataAdapter to retrieve the data, but both are equally slow. From stepping thru the debugger, I know that the specific statement that takes a long time to execute is either reader.ExecuteReader() or dataAdapter.Fill(dataTable), equivalently depending on the method I tried. I did a trace in the Profiler, and got nearly identical result for either method of .NET SqlClient Data Provider. Here is an example: EventClass: RPC:Completed CPU: 13390 Reads: 559475 Writes: 0 Duration: 13496 Binary Data: (a long hex value) When I copied the TextData (SQL Statement being executed) from Profiler into SQL Management Studio, I get the following trace: EventClass: SQL:BatchCompleted CPU: 437 Reads: 9998 Writes: 0 Duration: 440 BinaryData: (empty) (Immediately prior to this there is a corresponding SQL:BatchStarting trace, with empty CPU, Reads, Writes, and Duration columns.) What could explain the orders of magnitude difference in reads and duration? Is the problem due to RPC? What about the binary data? I have tried using the overload of ExecuteReader(CommandBehavior.SingleResult) with no improvement. What can I try to improve it? Thanks, Dustin Tag: sqlBulkCopy Tag: 144229
  - 4
    - Server Browser accessible from code I'm wanting to implement a tree view in C# that lists user selected servers/databases/fields/etc. I would like to be able to pull up that standard form that I see that allows someone to search for servers, then name the database, then "Test Connection", etc. Is this accessible from within run-time code? Tag: sqlBulkCopy Tag: 144225
  - 5
    - load flat file into datatable Anyone, I'm looking for a way to load flat files into a datatable in vb.net (2005) My flat files are tabbed delimited files with the field names on the first row I could dump the data into a sqlserver via DTS and then query against a table, but I'd like to figure out how to go directly from flat file to datatable/dataset. Any examples out there? -- thanks in advance, georgejetson Tag: sqlBulkCopy Tag: 144224
  - 6
    - Initial opening speed Hi My vb.net seems to be slow on initial loading. My clients are not impressed as they had used the ms access version of the app previously and think that the old app is faster in loading. I understand .net apps need to compile themselves before execution. Is there any way I can speed up this process or in general do anything else to speed up initial loading of the vb.net app? Thanks Regards Tag: sqlBulkCopy Tag: 144221
  - 7
    - OleDB locks excel file Greetings, I use OleDB from my c# application to populate an excel sheet. I open the connection in a using clause (which automatically close the connection after exit) and the excel file is created. However, I am not able to view excel file before I exit the application. It seems that there is a lock on the excel file and that the transaction is not fully completed before application close (or sometimes when I open and close other parts of the application). Does anyone know a way to force Ole Db to release all locks on the file? connectionstring setting..? ConnectionString: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"c:\\temp\ \myExcelFile.xls\";Extended Properties=\"Excel 8.0;OLE DB Services=0;HDR=Yes;Mode = 3;\""; Tried Services 0/-4 Example code: using(OleDbConnection connection = new OleDbConnection(connectionString.ToString())) { connection.Open(); transaction = connection.BeginTransaction(IsolationLevel.ReadUncommitted); CreateInspectionSheet(inspection, connection, transaction); transaction.Commit(); // does not seem to force data written to file } transaction.Dispose(); Any help highly appreciated! -thomas Tag: sqlBulkCopy Tag: 144220
  - 8
    - Populating data set on background thread Hi I have a master data set. I need to populate a copy of this data set in the background (via background thread?) and once filled to replace master data set with the newly filled dataset. Is there a code example of this that can see? Thanks Regards Tag: sqlBulkCopy Tag: 144214
  - 9
    - How import data from SQL Server? What's the best way to import all the tables (w/data) from an SQL Server database (in production) to my SQL Express database (on my local dev machine)? Tag: sqlBulkCopy Tag: 144211
  - 10
    - Creation of table in database based upon ado.net database Part of the application which I am building must support either Access or Sql Server Databases. The code to generate the databases via sql scripts is done. However, I have a set of tables (they will all have the same columns, but have different results since they are doing seperate calculations/modeling that is being written to each table) that need to be created based upon selections done by the user. It is easy to create an in memory table that holds this information and to clone it to hold the results of each individual model. The problem is how do I take this and create a set of tables with that structure within either of the two database types. If I can get a clue on how to take a single in memory table and to then build either an Access or Sql Server table in an already existing database, I can work out the rest. Any help would be appreciated, since I am stuck at not having any good solution for this. Tag: sqlBulkCopy Tag: 144206
  - 11
    - Database access spped issue Hi I have a terrible scenario. I had an ms access front end/back end app that searched on partial strings. I have coded the app in .net (access backend) and am using the below code to search on partial strings;. daCompanyLocate.SelectCommand = selCompanyLocate selCompanyLocate.CommandText = "SELECT Company, [Company 2] as Parent, Type, Tel, [Address 1] as Address, [Post Code], Town, ID " & _ "FROM Clients " & _ "ORDER BY Company" ds.CompanyLocate.Clear() daCompanyLocate.Fill(ds.CompanyLocate) dv = New DataView(ds.CompanyLocate) dv.RowFilter = "Company like '*" & Me.txtLocate.Text & "*' OR [Parent] like '*" & Me.txtLocate.Text & "*'" grdLocate.DataSource = dv Unfortunately client is not impressed with the speed of this ado.net/vb.net version compared to the same feature in access. Could someone please advise me if I can speed this up somehow? Thanks Regards Tag: sqlBulkCopy Tag: 144203
  - 12
    - re-order a dataset Hi, I have a dataset (vb.Net 1.1) that I need to go through and re-order based on the data. I'm wondering if there is a nice relatively automated way to do this, but I kind of doubt it. There is not currently a single field to use in a DataView's order by parameter. The data has two fields that decide the order, node_id and parent_id. The parent_id is the node_id of the row above. If there is no corresponding row to the parent_id then we have row 0. I've messed around a little with looping through the table and I haven't found any elegant (or even not ugly) solutions. Two options that come to mind are cloning the table and copying the rows in order, probably making multiple passes. Another would be to add an "order" column and populating that as I loop through and then use a dataview to order the rows according to that field. Anyone got any better solutions? Could I create a recursive DataRelation to do this? I'd love to hear any ideas! Thanks! Matt Tag: sqlBulkCopy Tag: 144202
  - 13
    - Getting command from LINQ query I want to get a DataReader from a Linq Query so I wrote this Extension Method public static IDataReader QueryToDataReader(this System.Data.Linq.DataContext ctx, object query) { IDbCommand command = ctx.GetCommand(query as IQueryable); command.Connection = ctx.Connection; command.Connection.Open(); IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection); return reader; } Sometimes it pukes because the Query passed in is a Type System.Collections.Generic.IEnumerable<Application> not an IQueryable. Query comes back as a IEnumerable when I call a sproc that returns after a select statement, even though the return type is defined in the datacontext. If my sproc returns a TABLE object, the Query comes back as a IQueryable. I don't understand why I get IEnumerable sometimes and IQueryable others? Is their a way to get my function to work in both cases? Tag: sqlBulkCopy Tag: 144192
  - 14
    - Using Access db for Membership provider Any step by steps (or web links) on how to easily use an Access db for being the membership provider? I am using VS 2008 and .net 3.5. Thanks! Tag: sqlBulkCopy Tag: 144188
  - 15
    - dbml error for Access database Created a new Access mdb file. Created a Data Connection in Server explorer for it. Created a new "LINQ to SQL Class". When I try to drag/drop a table into the designer I get this error: THE SELECTED OBJECT(S) USE AN UNSUPPORTED DATA PROVIDED. Any idea why? Thanks! Tag: sqlBulkCopy Tag: 144186
  - 16
    - Creating table in Access using C# Hello Group I have made an application that can create Mail Merges with Word. The application creates a WordTemplate and set the Document.MailMerge.OpenDatasource's filename to a text (tab spe.) file. Containing all fields and data to merge with. I would like to change the MailMerge.OpenDatasource's datasource to an Access database instead. In another table i have a list of fields that can be used in the mailmerge. These fields can change from time to time, and therefore i will have to change the table that i used as the MailMerge datasource table. So: I'll need to first Delete the table - easy - just Execute the SQL "DROP TABLE [tablename];" Then create the table with the columns matching my merge fields.. How can i create a new table in Access 2003 using C# and Visual Studio 2005 Kind Regards Johnny E. Jensen Tag: sqlBulkCopy Tag: 144183
  - 17
    - MySQL - Error on adding a new data source Hi All! I'm working on Visual Studio 2008 Pro and when I add a new data source from a MySQL database via ODBC I get the following error: <'table'..'field'> Could not get column information for database object named 'table'..'field' Does anyone have this problem? What could I do to solve this problem? Thanks in advance! Vanessa Tag: sqlBulkCopy Tag: 144181
  - 18
    - What is Delphi dotnet Dear Friends, can somebody tell me what is delphi dot net. Rgards Sridhar Tag: sqlBulkCopy Tag: 144178
  - 19
    - Getchunk & Appendchunk When I used VB6 & ADO I used GetChunk & AppendChunk to get/save large image data to SQL Server. In ADO.Net that is not available but I found the below link and it describes what to use as an alternative. Looking at the code I don't see how the data in saved/retrieved in "chunks", is it done natively in ADO.Net? If not, how should I do that now? http://support.microsoft.com/kb/q309158/ Regards, Dan Tag: sqlBulkCopy Tag: 144176
  - 20
    - Tables and columns name can contain accented words? In my language, Italian language, we have words with accent, for example "città" or "perchè"; can I create tables or columns in sql server with names that contain letters with the accent (i.e. "à" "è" "ì" "ò" "ù")? Is this absolutely not recommended? Or is it an acceptable practice? Thanks in advance. Best regards. Tag: sqlBulkCopy Tag: 144175
  - 21
    - Encrypting config files as part of a VS build event Is it possible to create a post build event to encrypt my config file (which holds my connection strings)? The reason I would like to do this is that while we are developing I would like the developers to be able to see/modify the connection strings appropriately, but when we do a release build and deploy the application and config file to a target machine I want the config files (or at least the connection strings with the file) to be encrypted. I realize this can be achieved within the code, and then place the resulting encrypted config file under source control, but any changes to one or more connection strings would be a burden (ie. recontructing the original file, making the changes, then re-encrypting) Cheers Justin Tag: sqlBulkCopy Tag: 144172
  - 22
    - Null Values eliminates the row? I have a TableAdapter linked to an SQL Server 2000 table. Some of the cells have null string values. In VS2005, I can preview the data linked by the TableAdapter in design mode, and all fields are displayed - even the ones with cells containing null values (they just show "null" in the cell). When I run my application, I create a DataTable and use an SqlDataAdapter and fill it using the TableAdapter. To search my data, I use the DataTable's Select method; however, any row that contains a null value will not show up in the DataRows that are returned. Has anyone ever encountered something like this? Why is this happening, and how do I get around it? Tag: sqlBulkCopy Tag: 144167
  - 23
    - SQLOLEDB error I have a remote desktop running SQL (MSDE) connecting to an SQL 2005 server. I do insert and update commands to get the data from one to the other in a VB.NET application. All commands run w/o any issue except for one. That one command is pulling the most data from the biggest file, but other than that it is of the same format as other commands that are working. When I run the command (it is inserting data onto the laptop from the server for data that doesn't exist on the laptop), I receive: Index #0 Message: The operation could not be performed because the OLD DB provider 'SQLOLEDB' was unable to begin a distributed transaction. LineNumber: 2 Source: .Net SqlClient Data Provider But, other commands on the same types of tables (both have identity columns as the key), the insert works fine. I have checked everything, and it is only this table causing an issue. Could this error occur because of a timeout? Darin *** Sent via Developersdex http://www.developersdex.com *** Tag: sqlBulkCopy Tag: 144166
  - 24
    - deleting rows with SQLCommandBuilder I have a task in an ASP.Net page that I think ought to work, but doesn't. It's fairly straightforward. It's an event scheduling site, and I want a user to be able to delete an event. When he clicks the delete button, it posts back to the page with the event id for the deleted event in the query page, and then tries to delete the rwo with an SQLCommandBuilder object. The code looks like this: string qstring = "Select * from Schedule WHERE EventNumber = " + Request.QueryString["EventID"]; System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(qstring, MyConnectionString); System.Data.DataSet ds = new DataSet(); da.Fill(ds); if (ds.Tables[0].Rows.Count == 1)//Verify that it returned exactly one row. { DataRow dr = ds.Tables[0].Rows[0];//Select the only row. System.Data.SqlClient.SqlCommandBuilder bldr = new System.Data.SqlClient.SqlCommandBuilder(da); ds.Tables[0].Rows.Remove(dr);//Remove it da.Update(ds);//Update the data set. Response.Redirect("~/allevents.aspx"); } I can verify that the correct dataset is being loaded. It has one table, and that one table has one row. When I call "remove", I can verify that the row leaves the dataset, so that ds now has one table with zero rows. I call Update, and I expect to delete the row from the database, but it doesn't. How do I use the CommandBuilder object to do the delete? Tag: sqlBulkCopy Tag: 144161
  - 25
    - DataRow problem please help Guys, I have declared 2 dataRows and assigned New daraRows as below. Dim aRow as DataRow aRow = OutFormat.DataSet.Tables("demotbl").NewRow '*** Point 1 ---- ---- Dim bRow as DataRow bRow = OutFormat.DataSet.Tables("demotbl").NewRow '***Point 2 --- --- bRow = OutFormat.DataSet.Tables("demotbl").NewRow 'No. '*** Point 3 --- --- Point 1: I have created aRow and assigned some fields to its columns. Point 2:Then I created bRow and assigned some fields (In the debug mode I can see aRow is still active). Point 3:Then I created again bRow and this time It has erased all the contents of aRow. How would that possible? I have not changed anything aRow. Please help. Tag: sqlBulkCopy Tag: 144156

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

Top

Re: sqlBulkCopy by William

William
Fri Feb 29 16:45:48 CST 2008

Hummm, this is a DBA issue. I have not had that problem 'cause I cheated. My
account has SA rights. I would take this to the SQL Server newsgroup.
I'll also echo it in the SQL Server MVP list as well. Perhaps one of the
gurus over there have seen this.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhikerâ??s Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________
"Beth" <Beth@discussions.microsoft.com> wrote in message
news:693907BD-253D-42CA-9D34-55C3CFC54CA1@microsoft.com...
> 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
>

Top