.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Insert into many tables at each upload.

Posted By:      Posted Date: September 03, 2010    Points: 0   Category :Sql Server
<!-- /* Font Definitions */ @font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-charset:1; mso-generic-font-family:roman; mso-font-format:other; mso-font-pitch:variable; mso-font-signature:0 0 0 0 0 0;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4; mso-font-charset:0; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-520092929 1073786111 9 0 415 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-unhide:no; mso-style-qformat:yes; mso-style-parent:""; margin-top:0cm; margin-right:0cm; margin-bottom:10.0pt; margin-left:0cm; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi; mso-fareast-language:EN-US;} .MsoChpDefault {mso-style-type:export-only; mso-default-props:yes; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"

View Complete Post

More Related Resource Links

insert new records in tables from dataset to database


I have 2 tables in SqlServer 2008.

Table1: T1id, SomeData

Table2: T2id, T1id, SomeData

I am using DataAdapter.FillSchema to create shema of tables in DataSet. I created DataRelation on columns T1id in both tables.

Now when i try to Update Sql Database T1id in Table2 remains 0 and not the value of T1id in Table1.

I can successfully update Sql Database if i fill DataSet with records first (Using DataAdapter.Fill), but that's not what i want to do. I don't need "old" records in my dataset. I want to use dataset just to store all imputs from user until the proccess is done and then insert all those records "at the same time".

I think i'm getting it wrong with ForeignKeyConstraints, maybe UpdateRule is not set to cascade, i just don't know.

I have been trying to work this out for 6 days now and i'm about to break something on half.

Can someone please guide me to right direction, maybe post some sample, anything.... please!

My old post regarding this:

How to update Sql Server related tables from Dataset(SchemaFill)

SqlDataAdapter.Update related tables

Insert Excel Data via File Upload into sql server database???

Hi all, I have requirement that User can Upload the Excel Sheet Data to sql server Database at once. How i do that Any Article or hint is apperciated.   Regards 

asp.net insert into 3 related tables using three tier architecture and typed datasets

I am using ASP.NET (C# and sql server express 2005) for my web application project. I have three related tables.I want to do an insert using typed datasets,tableadapters and three tier architecture.I wish to know how to do simultaneous inserts in 3 tables.So i wish to insert a row in first table and get its primary key.Same with the second table.I will use the primary keys of those first two tables to do the insert in the third table.How can i accomplish this using formview control and what methods should i use??Please give me suggestions.thanks in advance for reading this. I really appreciate it.

2 questions - Dropdownlist from SQL Table and Insert into several tables

We're trying to convert an older access app with a sql server back end (with many tables) to Sharepoint. There are entry forms, update forms, look up forms, reports, etc the 2 questions I have are: 1. How can I create a dropdownlist that gets its items from a SQL table? 2. How can I create a form that, when filled out, puts items into multiple tables? Basically, I'd like to know if any of this is possible in 2010, and does anyone have any tips on where to start?    

updated view (with insert )on two tables

CREATE TABLE [tbl1](       [ID] [int] IDENTITY(1,1) NOT NULL,       [F1] [int] NULL,  CONSTRAINT [PK_tbl1] PRIMARY KEY CLUSTERED (       [ID] ASC ) ) ON [PRIMARY] GO CREATE TABLE [tbl2](       [ID] [int] NOT NULL,       [f2] [int] NULL,  CONSTRAINT [PK_tbl2] PRIMARY KEY CLUSTERED (       [ID] ASC ) ) ON [PRIMARY] GO CREATE VIEW [View_1] AS SELECT     tbl1.ID, tbl1.F1, tbl2.f2 FROM         tbl1 LEFT OUTER JOIN                       tbl2 ON tbl1.ID = tbl2.ID GO INSERT INTO [BackUpsData].[dbo].[View_1]            ([F1],[f2])      VALUES(1,1) i get this message : Msg 4405, Level 16, State 1, Line 1 View or function 'View_1' is not updatable because the modification affects multiple base tables.   how can i create a view that can insert new rows into ? thanks.  

Insert/Update data to multiple tables on a single button click


Hi experts,

I need to insert/update data to 3 or more tables on a single button click.What is best method for achieving this?I am using mysql as my DB.

Thanks for your valuable reply in advance.

Stored Procedures using XQuery to insert XML data into tables


If I have the following XML being passed as a parameter into a stored procedure.


<name>My Bookstore</name><br/>
<location>New York</location><br/>
 <title lang="eng"&g

INSERT the Results of a Stored Procedure with Multiple SELECT Statements into Multiple Tables?

I have a stored procedure (which is overly complex and written by someone else) that I need to take the results of and put them into tables.  The stored procedure uses Dynamic SQL to build and then run 5 SELECT statements based on literally hundreds of variables.  I need to take the results of these SELECT statements and put them into tables in a different database.  Rewriting or re-creating the logic of the stored procedure is NOT AN OPTION as the logic may change over time and maintaining it in multiple places would be nightmarish.  Can anybody help me with this?  I've googled extensively, but have only been able to find examples of using one result set not multiple ones.

SQL XML Bulk Insert 3 tables format issue


I would like to ask for you help finding a problem in my XML schema.  I need to do a bulk insert into 3 tables that are linked.  I have an address, site and customer table. It is an SQL Server 2008 Database


The customer table links to its address in the address table.  The Address table contains no links to other tables and the site table contains a link to the customer and to the sites address.

From my understanding of this bulk insert procedure, I need to insert my address for my customer then my customer with a link to its address.  Then I want to insert my sites for my customer but they need their addresses too so I want to insert the site address and then the site with the key relationships to its address and customer row in those tables.

The xsd file is below that I have at the moment:

<?xml version="1.0" encoding="utf-8"

How to insert Database and Upload file


I have TBL_PRODUCT table : ProductID, ProductName, CategoryID, Image.

I can insert data to table :

public ActionResult Create(tbl_products products)

       return Redirect("Index");

I can upload image file too

public ActionResult UpHinh(HttpPostedFileBase fileImg)
       var fileName = Path.GetFileName(fileImg.FileName);
       fileImg.SaveAs(Server.MapPath("/Content/Images/" + fileName));

       return Redirect("Index");

But I want two function become one. When I press <Submit>, it's insert data and upload image file.

Help me. Thanks

Upload and insert csv data into SQL from code behind


In my orginal post i asked the question to handle this within the .Net side.
I was trying to stay away from using SSIS package, and with help from another member, i was able to get the process to work with an excel spreadsheet.

But part of my project requires us to allow .csv files.. so my question is how can i modify the code i have now for the excel to work with a .csv file?
You can find the current code in my original post here: http://forums.asp.net/t/1584668.aspx

I not sure what the best solution is for the end result.. I was reading somewhere online that you can right the data from the file to a datalist or gridview so you can present the user with a view of what is being uploaded, then use that datacontrol to insert into SQL.

In my current code, im uploading the file, then using that file to do the insert. This works fine for .xls files.. but i need to accomplish the same thing for a .csv file.

Now not sure if we should force the file to include headers to make this easier or should we allow with and without and just validate before inserting so we can call the right code?


INSERT NULL value in SQL database when there is no file to upload



I've been working on a code that allows users to upload images (jpgs) as an optional field. Alhough the uploading works, I'm struggling when the user doesn't have an image to upload. I can't seem to find the way to NOT insert binary data into my database, I just want an NULL value when there is no image.
I get this error message: ...expects parameter @Image, which was not supplied.


Here is the code: 

Sub InsertItem()

Dim imageData As Byte() = Nothing
Dim MIMEType As String = ""


Insert NULL values into Integer column in Access 2007 tables

hi  there:

  My source data  is below and it's in CSV format

Days waited


My destination is a Number column in a 2007 access table  and the required property is set to NO

First question :

   I used a Data conversion task in DFT to convert [DT_STR] to Integer as SSIS seems to default the source as DT_STR type. However, SSIS keeps complaining that

"The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data."

I know that's because of those NULL values. how to solve this issue?


--Currently using Reporting Service 2000; Visual Studio .NET 2003; Visual Source Safe SSIS 2008 SSAS 2008, SVN --

Urgent: Webform insert into Access (multiple tables)


hello all, 
i have a webform that I am sending to and Access database and i am using dreamweaver cs4. The form consists of fields that would be sent to multiple database tables. I have tried creating server behaviors to insert the record, and have gotten it to add the records to the various tables, HOWEVER the foreign keys of the tables were not linked (i am using autonumbers for the Primary keys). 

i was thinking to split the web form into 3 forms...a form for each table, as i would have to retrieve the autonumber from the database after the first form is sent.  Could someone help me achieve this?

The code that was created by dreamweaver for the first webpage is as follows

<!--#include file="Connections/RequestBooking.asp" -->

Bulkload SQXML insert elements to multiple tables


I'm using sqlxml 3.0 and I'm wondering if it's possible to put elements under one node in an xml to different tables on a sql server?

Here is an example


What I need to do is to put element 1 and 2 in table 1 at the sql server and element 3 and 4 in table 2 at the sql server.

How do I write this in a schema?

EDIT: After more reading I've realized that I need some relationship in the schema for this to work. Is there a way I can combine 2 elements to make the key fields, and can the key field be the same element? For instance in this example:


I would like to use element1 and element2 to create the key in table1 (put them in same column) and then use the same elements to create the key in table2. Then I would put element3 in table1 and element4 i

Cannot insert new record into multiple tables thru. sql 2005 View

I have a sql server 2005 database containing several tables: Orders, Shipping, and Planning.  They are joined together in the sql view V_Main.  I also have a ms access 2007 mdb that links to V_Main via odbc.  I created an access form using the view V_Main as the record source.  I cannot insert a new record into each of the 3 base tables using V_Main as the record source.  A new record is inserted into only one table.
I did find a suggestion in another forum that suggested copying the sql that creates the view into the record source, rather than the name of the view.  This does work, but adds another place to be updated manually should and other table be added to the view in the future.
I did verify that all tables have primary keys.
How can I add a new record for each of the 3 base tables using the view as the record source?  What is the code that could be placed in vb of the form?
Any suggestions would be helpful.
Thank you.

MS SQL Server: Search All Tables, Columns & Rows For Data or Keyword Query

If you need to search your entire database for specific data, this query will come in handy.

So when a client needs a custom report or some sort of custom development using Great Plains, most of the time I will have to track down the data in the system by running this query and find the table(s) it is in.
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend