how to insert all data from vb.net dataset to a temporary table in sql server database

I always get solutions for my question from the forum , thanks for all your help.

Question is i have select some data from a database in server 1 and save it in to a DataSet in vb.net

now i need to create a temporary table in server 2 test database and insert all rows in DataSet. I know I can read row by row in DataSet.Table(0) and execute a insert statement for each row. but i think this is time consuming. Is there any way that I can transfer all data in DataSet to a temporary table at once.

need your help please.

2
Pathiya007
1/2/2009 11:27:50 AM
asp.net.object-datasource 16182 articles. 0 followers. Follow

3 Replies
4396 Views

Similar Articles

[PageSpeed] 48
Get it on Google Play
Get it on Apple App Store

I am selecting data from Server 1 Datababse (original1) as follows

 Dim sqlCn As New SqlConnection
        Dim daSql As SqlDataAdapter
        Dim dsSql As New DataSet
        Dim cmdSql As New SqlCommand
        Try

            sqlCn.ConnectionString = SoapSageDataFunctions.dbConnectString 'Crates Connection String to server1
            sqlCn.Open() ' Opens Connection

            Dim sqcmd As New SqlCommand
            sqcmd.CommandText = "SET CONCAT_NULL_YIELDS_NULL OFF"
            sqcmd.Connection = sqlCn
            sqcmd.ExecuteNonQuery()

            cmdSql.CommandText = "SET DATEFORMAT 'dmy'"
            cmdSql.Connection = sqlCn
            cmdSql.ExecuteNonQuery()

sSql="select rowid,name,accnumber,creditlimit from tableOriginal1"

            daSql = New SqlDataAdapter(sSql, sqlCn)
            daSql.Fill(dsSql, "Temp")

' ''' '' ' ' ' ' '  ' ' now dsSql DataSet have datafrom tableOriginal1 I need to insert all these rows in to a table in Server 2 database

how to do this please give your answers. I only read data from server 1 tableoriginal1

 

0
Pathiya007
1/2/2009 12:06:53 PM

Hi Pathiya007,

To copy data into SQL Server database, you can take advantage of SqlBulkCopy class. It can help you to copy data to SQL Server tables in a quicker way. For how to use it, please check this example:

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx

Thanks.


David Qian
Microsoft Online Community Support

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
0
Wencui
1/5/2009 8:44:04 AM

hi, Wencui Qian

 thanks , perfect answer . i modified my code now it finish the task with in seconds, earlire took 3 minutes only for 6000 rows.

0
Pathiya007
1/19/2009 4:15:45 PM
Reply:

Similar Artilces:

moving data from vb.net 1.1 Datagrid to sql server 2005 database
Hi, I was hoping someone could help me with a problem I am having. I can get the datagrid to populate from the database fine, like so:     Public Sub BindTheData()         Dim strConn As String        strConn = System.Configuration.ConfigurationSettings.AppSettings("ConnectionString")        Dim objConn As SqlConnection        objConn = New SqlConnection(strConn)        Dim myCmd As SqlCommand        myCmd = New SqlCommand        myCmd.Connection = objConn        myCmd.CommandType = CommandType.StoredProcedure        myCmd.CommandText = "AnswerDataGrid"        Dim DS As SqlDataReader         Dim UserID As New SqlParameter("@User_ID", Session.Item("user_id"))        Dim DivID As New SqlParameter("@Div_ID", DropDownDivision.SelectedValue())        Dim CatID As New SqlParameter("@Cat_ID", DropDownCategory.SelectedValue())        myCmd.Parameters.Add(UserID)        myCmd.Parameters.Add...

Why I cannot create/edit tables in VS.Net Server explorer for SQL Server 2000 Developer local database?
I found that when I install MSDE, then I can create/edit database objects for MSDE database in Visual Studio 2003 Server Explorer, but when I try to create/edit database objects under the default database created by the SQL Server 2000 installation, I cannot do these tasks as no option for these tasks appear when I right-click on the database object in Server Explorer.  Anyone knows why this is happening?sun21170 I reproed your issue. And I found in MSDN it is said the Server Explorer can: -Open and view Server Explorer -Add a data connection -Add a server -Drag a...

How to access data through SQL Server in VB.NET?
Here's my code, it's very simple but never worked for my set up connection part. It always tells me that: BC30636: '>' expected(Compiler error message). You can try it on your own local host using northwind database 'categories' table. It's very simple, but just don't work for me. I'm desperate here. So anyone can help? Thanks. lz74d <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <HTML> <BODY> <script language="vb" runat="server"> sub Page_Load(Sender as Obj...

Y ADO.NET does not return a simple ReadOnly Query data as a DataTable though we need most of the times a Single Table of Data from DataBase ? Similar to DataAdapter returning a DataSet ! Or can we Typ
Hi All, After we moved from JSP and started developing a web application in ASP.NET, the DataAdapter is used for all sort of operations like insert/update/delete for a table. But, most of the time we need to query the data from db for a Table/View and the returned data will be a Single Table of data. Though ADO.NET has a facility of keeping such a data in DataTable, y does ADO.NET have not provided a way to return a DataTable. Similar to DataAdapter returning a DataSet. Its like a mix up of DataReader and DataAdapter with Read Only component returning DataTable. Or, please let me know whether there exists a way to convert a DataReader into a DataTable WITHOUT any looping of col. names and rows and inserting it into DataTable ? Or, can we write a Class and override DataReader / DataAdapter to return data as a DataTable ? Yours, Sankar.B Though ADO.NET has a facility of keeping such a data in DataTable, y does ADO.NET have not provided a way to return a DataTable. Similar to DataAdapter returning a DataSet. When you call DataAdapter.Fill(dataset); the tables returned are stored in the dataset - if your query only returns one table, it will only contain one table - and can be accessed from DataSet.Tables. e.g. DataTable myTable = myDataSet.Tables[0]; [It could be done this way so you can query one table, or many, with the same constructs. That's just a guess though] The Data Adapter already has an override to return a datatable. Adp.Fill(myD...

SQL Server Reporting Services for SQL SERVER 2000 AND Visual Studio.NET 2005 .NET 2.0
Hi, Which version of SQl Server Reporting Services will work with SQL SERVER 2000 in pararrel with ASP.NET 2.0 (.NET 2.0 framework)?  Thanks, Azam HighOnCodingWanna get high! Hi, Okay I got the answer. Yes, we can use the SQL SERVER 2005 Reporting Services with SQL SERVER 2000. HighOnCodingWanna get high! ...

Inserting data in batch mode in a SQL Table using .NET
Hi, I have an ASP.NET Web Service that accepts a DataSet object passed to it. This DataSet will contain a large number of records in it's table. What I want to do (if possible) is insert all records in a SQL table in a batch mode (one go). Is this doable? Thanks,--Mazen depends on the size of the dataset.Kay LeeMySpace.com - http://www.myspace.com/kragieInfrastructure GroupMySpace.com- Code to live, but Live to code....

Sending SMS using vb.net or C#.net using vb.net or c#.net
Hi  My requirement is I hav one csv file with these fields id,mobilenum,messgae,status.intiallu staus is 0. once i read the all fileds and take that mobile number.using tat mobile number  i need to send sms .after sending sms i shuld change status as 1 How to send sms thru coding (please dont provide any links.if it is provide also please give working links becox i checked codeproject .i didnt get any nice link. and also provide the how to update the status field im csv file   Thank ssandhya   To send SMS, you need some third party SMS providers. if you consult them, they will give either an URL or thery will expose some webmethod to send SMS. then in your code, But they will charge for that.  when you call that method  or   response.redirect("that given URL"), then an sms will go.Give a man a fish and you feed him for a day. Teach a man to fish and you feed him forever. Hello Sandhya, Please check out the links: http://www.gotocode.com/art.asp?art_id=66 http://www.codeproject.com/KB/aspnet/SendingSMS.aspx  Regards,Ahmed***************************************************Please don't forget to mark the post as Answer, which helps you.Thank you.www.ahmadtasnim.com Hi,  We have done a similiar implementation in one of the projects. You wont be able to find any built in class for the same in .net ( you may like to have a look at the micorosft exchange server). Rather you will have ...

Insert Data into sql DB using VB.net backend
Hi. I need too use my VB.net backend to insert data into my database from seveeral controls on the page. I have tried writing some code to do this but i am a beginner, and i am completely stuck. Heres what i have so far   Dim userid As String = DropDownList2.SelectedValue Dim username2 As String = DropDownList1.SelectedValue Dim date2 As String = Calendar1.SelectedDate Dim invoiceid As Integer = TextBox1.Text Dim amount As Decimal = TextBox2.Text Dim conn As String = ConfigurationManager.ConnectionStrings("U...

retrieve selected data from SQL database using vb.net
I already watch the video in the vb.net express to learn about the data binding. But the problem that i face is when i want to retrieve certain data from the database. the video just show how to fill and update. who ever know the code to retrieve the selected row from the SQL database using vb.net 2005????? thanks do u want to retrive a particular cell or a particular row from the database?what control do you want to bind the retrived value to? thanks Imran NathaniLet the game begin!!Barcode image generation made easy.Writing an ActiveX Control in C#Generating Relational Data From XML D...

Insert data from a dropdownlist and textbox control into a database ( vb.net)
Hi everyone, I am creating an application for my students.  I teach piano.  There are three levels for them: Beginner, Intermediate, Advanced. After they have logged in, they are given the opportunity to select various pieces of sheet music to learn according to their level from a dropdown list control.  I have created the database which lists the sheet music and bound it to the dropdownlist control.  I have also added a textbox control where I want them to type in the reason why they selected the particular piece of music to learn (I know, kids hate writing essays, but it's not an essay, I promise! :)   Ok, I have also added a gridview control that is bound to the datasource that will display listings of all the pieces of sheet music the student has already selected and learned, just so they can see how they have progressed. (many get a kick out of seeing how they selected a challenging piece and were able to "conquer" it)  Ok, you can tell I really like teaching piano! The gridview contains the following columns: Name, Level, SheetMusicName, Reason, ApprovedByInstructor, Completed Ok, next.  Here is what I want to do: I want to insert into the gridview (into the datasource), the student's selections from the dropdownlist and the text out of the textbox. Student makes a selection from the dropdownlist Student types in the reason for the selection in a textbox Student sees the music they hav...

How to insert data into sql server in bulk using ADO.net
Hi!  I'm building a web application. I need to read data from a text or excel file and process the data and then store the result records into database. The record number is big. I can store the data record into database (SQL Server 2005) one at a time. I think it's slow. Is there any way to insert the data in bulk.   Thanks! ccy Hi, Try DataAdapter's Update() method. Thanks and best regards,Faraz Shah KhanMCP, MCAD.Net, MCSD.Net, MCTS-Win/Web, MCPD-WebBlog HI, If you want to just insert the data then you can use sqlbulkinsert class. If you want to update a...

Using VB.NET to export data from SQL Server 2005 to Excel
Hi There, I just want to know how to export data from SQL Server 2005 to an excel spread sheet, using VB.NET?ThanksSJB  Everytime I should export data from SQL to Excel with code, I simply create a ; seperated file (*.csv). So if you want to do the same you can simply use ADO.Net to query your database and get a result set from your tables. Walk trough the rows and get its value and create a textfile with the System.IO namespace. The file can look like: column1;column2;column3 value1;value2;value3 value1;value2;value3 The first row can if you want have the name of the column tha...

Writing Data From SQL Server to MS Word using VB.NET
Hi All, I need to write data from SQL Server to MS Word. I have never done that and even I dont have any idea how to proceed. I really apprecite if any body can help me. Please post sample code if any. Thanks in advance, Kumar. Word has a fairly robust object model. For starters you should create windows application project and add a COM reference to the Microsoft Word type library. The following code will create a document and add some text: Dim objWord As New Word.Application() Dim objDoc As Word.Document objWord.Visible = True ...

I need help in VB.net code to check the data from SQL database
Hi all, I have a field called "Phone" in a table called "CustInfo", it has 12 digits e.g. 966546500000. and all records are filled of information, I want only to check the existance data. I'm writing my code using VB.NET, If I want to check the phone entry from the user, whether it's existed or not, but the user will only enter 9 digits without the first 3 international digits (966), so the code has to check the number starting from digit 4.Below is my code when the user click enter button after entering the number into text box.how can I modify it to achie...

Sql Insert information from 1 table into another on the fly. VB.NET
This is probably a really simple question, but hey! this is what this forums about I have this code, which is inserting data into 2 different tables from textboxes. The problem is. the "login" table autoincrements but the "details" table needs to have an id the same as the matching row in the login table (im using it for sessions for user profiles)The example here is of a registration page and it inserts your details as stated above. How do i get the value "user_id" from the second insert statement (which is an autoincrement so its not in the code) and inse...

Create SQL Server Project in VB.NET 2.0 (Database Project)
Hi all  How can create trigger in sql server project in VB.NET 2.0 ?thanks in advance   Mitesh Darji Hi miteshdarji , How can create trigger in sql server project in VB.NET 2.0 ? Do you want to create a database trigger thru vb.net ? If yes you can assign "CREATE TRIGGER" statement to a sqlcommand and then call sqlcommand.executenoquery. Standard create trigger statement is as following: Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger) CREATE TRIGGER [ schema_name . ]trigger_name ON { table | view } [ WITH <dml_tr...

Getting only the first paragraph of data from sql server 2005 in vb.net 2.0
Hi all, I have a table in sql server which consists of two colums. column 1 contain "id" integer and column 2 contain "body" varchar(max). For example: id - 1 body - French Industrial production sharply decreased 1.5% month-on-month in November, reversing from a 2.1% increase seen in October, the statistical office Insee said in a report Thursday. Economists were looking for a 0.6% fall. Production in the automotive industry fell steeply 5.3% versus 7.5% rise in October, while energy output declined 2% from 2.1% recorded in the previous month. In the construction...

Working in VB.Net 2005 inserting NULL into a field in Microsoft SQL Server 2005
I am update/inserting records threw a web form in vb.net. I need to insert 'NULL'  into my microsoft sql server database.  I am not talking about the below line of code where website is the name of my paramater.  If i do that it will just place a blank into that field in the database.  If i dont enter anything into that textbox I want it to to say NULL in that field. So if I go into the actual table in the SQL Server Management Studio and look at the website field of the recored I just added or updated and did not type anything into the web site textbo...

uploading a image in to the sql server 2005 database using vb.net 3.5
i am currently developing a web project using asp.net.i need to upload a image file to the database using asp.net.i am using linq to interactwith the database. can some one help me in writing these codes. {i need to know how to get the image size and i need the image content type to upload image) please let me know what are the codes i should use to get the image size and content type. i have used the following code but no use                                   Dim intImageSize As Int64                                    Dim strImageType As String                                    Dim ImageStream As Stream                                     ' Gets the Size of the Image                  &nbs...

SQL Anywhere .NET Data Provider for sybase sqlanywhere database server 8.0
Hello, I would like to connect to a sybase sqlanywhere database server 8.0 from a .NET c# code. I have read, that I need a SQL Anywhere .NET Data Provider for that. Unfortunately this Data Provider is not in the setup program of sybase sqlanywhere database server 8.0 (just from version 10.0.x). Could anyone help me with this problem? From where can I download the SQL Anywhere .NET Data Provider for sybase sqlanywhere database server 8.0, or I think "iAnywhere.Data.SQLAnywhere.dll" would be enought too (for sybase sqlanywhere database server 8.0). Thanks a lot! Janos ...

VB.NET
All, This is probably a much harder question than what should go in the "Getting Started" forum, but I am new and don't know where to put it. Anyways, I am currently developing an FTP class in VB.NET. It's kid tested, mother approved when trying to access an FTP Server on a Windows box meaning I can connect, run commands, upload and download a file no problem. My issues come when I try to use the same class with the same commands to access an FTP server on a UNIX box. I can connect and login just fine, but after that all my commands come back "500 'PWD': command not understood.". This is fo...

how to copy data from sql server to body part of an object of mailmessage class(in system.net.mail)
how to copy data from sql server to body part of an object of mailmessage class(in system.net.mail)  with c#Nitin Dhiman....!!!!Please Mark Answer If Helped....!!!! Here's a simple example. (I converted VB code to C#. Syntax may be off.) string connStr = "some connection string"; SqlConnection conn = new SqlConnection(connStr); string sql = "SELECT * FROM MyTable"; SqlDataAdapter da = new SqlDataAdapter(sql, conn); DataSet ds = new DataSet(); da.Fill(ds); if ((ds.tables[0].rows.count > 0)) { string strBody = "&lt;table>"; foreach (DataRow dr in ds.tables[0].rows) { strBody = (strBody + ("&lt;tr><td>" + (dr("mycolumn") + "&lt;/td></tr>"))); for (strBody = (strBody + "&lt;/table"); ; strBody++) { MailAddress from = new MailAddress("myemail@here.com"); MailAddress tou = new MailAddress("someemail@there.com"); MailMessage message = new MailMessage(from, tou); message.Subject = "SQL Data"; message.Body = strBody; message.IsBodyHtml = true; SmtpClient client = new SmtpClient("mymailserver"); client.Send(message); } } }   hi endengeredmassa,i couldn't understand that increment in for loop and following line of code,will u plz expl...

What happens when u try to update data in a dataset in .NET while the record is already deleted in SQL SERVER as backend?
Hai What happens when u try to update data in a dataset in .NET while the record is already deleted in SQL SERVER as backend? Help me plz Thanks in advanceRegardsSundar sql server take care of this when you are trying to this as three threads are already assign for this.one for front end as or may be you will be using vb or C# .for this it will support.others two are means in the background.Rest of the wuery can be solved in ADO.NET wrox publications...

Can't Connect .Net application from Windows Server 2003 to Database Server (SQL Server 2000)
Hi, I am having a problem connecting my .net applications from the application server to the database server. When I run the application from my windows xp (sp2) box it works fine. When I try to connect via SQL Management Studio to the database server from the application server I get the same error. Here is the error: System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider...

Web resources about - how to insert all data from vb.net dataset to a temporary table in sql server database - asp.net.object-datasource

Temporary - Wikipedia, the free encyclopedia
Text is available under the Creative Commons Attribution-ShareAlike License ;additional terms may apply. By using this site, you agree to the ...

Report: Facebook To Open Temporary Vancouver Office To Recruit, Train Engineering Talent
Facebook will set up a temporary office in Vancouver , British Columbia, where it will conduct “boot camps” to train recent software engineering ...

Facebook brings post insights back to Pages Manager app after temporary removal
A recent update to Pages Manager for iOS has returned detailed post insights to the product after a version earlier this month was released without ...

App Store - Bridgeway Temporary Housing
Get Bridgeway Temporary Housing on the App Store. See screenshots and ratings, and read customer reviews.

Temporary public toilets 1 - Flickr - Photo Sharing!
These are positioned by the city every year over the canals. They are located on the river bank and are therefore subject to flooding every few ...

人人建築 Architecture for the Mass - 尼泊爾臨時住所原型 Temporary Shelter Prototype in Nepal - YouTube
Charles Lai from Hong Kong and Takehiko Suzuki from Tokyo completed a prototype for a rapid-erected shelter with bamboo structure and other local ...

Temporary speed reduction on Abu Dhabi-Dubai highway - The National
Radars along a stretch of the road will be set at 121kph, down from 141kph, as roadwork is carried out.

Charlotte Pass accept temporary lease extension
Charlotte Pass ski village has accepted a 28-month extension on their existing lease with the NSW National Parks and Wildlife Service in a deal ...

Morrison says deficit levy 'only temporary'
Treasurer Scott Morrison says a 'deficit levy' on high-income earners was always a temporary measure.

Apple Watch Diary: A (temporary) world without Apple Pay
I said last summer in my Apple Watch Diary series that Apple Pay arriving in the UK was the tipping point for me in transforming the Watch from ...

Resources last updated: 3/27/2016 10:53:25 PM