Home » Asp.net

ASP.NET insertion and joining data between tables

I have the following tables:






Each table has a primary key assigned. The customers table has an ID column and a CUSTOMER_NAME column with unique index property set to avoid any duplication. How do I insert values in the product_information table that has a foreign key column from the CUSTOMERS table if we insert a value in the CUSTOMER_NAME field of the CUSTOMERS table. I want to know how can I create a join between all the tables so that when I insert values in individual tables they are joined with a serial number column in the PRODUCT_INFORMATION  table?

Please suggest!!


1 Answer Found


Answer 1

The customers table has an ID column and a CUSTOMER_NAME column with unique index property set to avoid any duplication. How do I insert values in the product_information table that has a foreign key column from the CUSTOMERS table if we insert a value in the CUSTOMER_NAME field of the CUSTOMERS table.

you can do this way: make ID column in the Customers Table a primary and identity. When you insert any value into the Customers table, you can get the latest CustomerID and insert a record in ProductName.

Declare @Customer as table
       (ID int primary key identity, 
        customer_Name varchar(10) unique);

Declare @ProductName 
as table(ID int identity,Product_Name varchar(100), 
         customer_Name varchar(10) unique

-- Insert a new customer
insert into @Customer values('My Name 1');

-- Insert Latest Customer into Product Names Table
insert into @ProductName
    select 'My Product Name 1',
  from @Customer 

select * from @Customer;

select * from @ProductName

That inserts a row into Customer and then inserts a new record into product name with the newly inserted Customer Name. You can apply same logic to other tables  as well.

how can I create a join between all the tables so that when I insert values in individual tables they are joined with a serial number column in the PRODUCT_INFORMATION  table?

Are you taking about a trigger to insert a record into Product Information table when a new customer is created?




I have been trying to create a sql statement that uses a subquery to join 2 oracle tables to create a strongly typed table adapter.  For example:

Select productName, (select categoryName from categories where categories.category_id = products.category_id) as Category from Products

The ASP .Net tutorial I am working through recommends using subqueries instead of joins for the insert, update and delete commands to be automatically generated by Visual Studio 2010. When I click Finish in the table adapter wizard I get the following error (I am using MySQL version 5.1 and the MySQL Data Connector 6.3.5)

Generated SELECT statement.

            Error in SELECT clause: expression near ‘SELECT’.

            Error in SELECT clause: expression near ‘FROM’.

            Missing FROM clause.

            Error in SELECT clause: expression near’,’.

            Unable to parse query text.



I am well versed with Join/Outer Join/Inner Join. My question is not related to normal Joining of two tables. Its little complex. Let me see if I can explain well.

There are two tables, were data is stored.

Name_Info : nvarchar(10)
Date_Info : smalldate()
Spec_Info_1 : tinyint
Spec_Info_2 : tinyint
Spec_Info_3 ; tinyint

Name_Data : nvarchar(10)
City_Data : nvarchar(10)
State_Data : nvarchar(10)

To join these two tables, I use the following query (joined on Name_Info ON Name_Data)


SELECT Name, Date=CONVERT(varchar, Date, 101), City, State,
CASE Spec_Info_1 WHEN '0' THEN 'House' WHEN '2' THEN 'Shop' WHEN '3' THEN 'Bay' ELSE '-' END AS Spec_Info_1, 
CASE Spec_Info_2 WHEN '0' THEN '10 Feet' WHEN '2' THEN '20 Feet' WHEN '3' THEN '40 Feet' ELSE '-' END AS Spec_Info_2, 
CASE Spec_Info_3 WHEN '0' THEN '2 Windows' WHEN '2' THEN '4 Windows' WHEN '3' THEN '6 Windows' ELSE '-' END AS Spec_Info_3
FROM Table_Spec_Data LEFT OUTER JOIN Table_Name_Data On Table_Spec_Data.Name_Info=Table_Name_Data.Name_Data  

Now, as you can see the tinyint information in each of Spec_Info_* field has a different meaning.

What you see in the above query is hard-coded. However, as there will be multiple views being created using the similar queries, it will become a hassle in adding new definitions. Requires updating 100+ views. I want to reduce that work.

For that I created a new table.

D_Name : nvarchar(20)
D_Value : tinyint
D_Description : nvarchar(50)

Date in this table looks like this:

Spec_Info_2010 Feet
Spec_Info_2220 Feet
Spec_Info_2340 Feet



Now, what I want to do is, to join this third table and let the query/view show the D_Description for the corresponding D_Name.D_Value
For example, in any view, where ever Spec_Info_1 shows and it has value 2, it should automatically show Shop. Instead of me hard-coding the values.

I think this should be possible. How? I am not sure about that. Any suggestions/ideas which can put me in this direction will be helpful.






how to asp.net Data serach Ajex jquery  and show in a table from data base


Hi I Want to know garbage collector works in .net there is something heap and stack funda.so can u please tell me from where i get reference of whole funda of that.and i have another question is that Please see below the code.here i declare one dt and get some data in it.and i don't want that dt after data come in text boxes.so in finally method i do dispose using dt.dispoe().

but after that i write little code to check whether the dt correctly dispose or not.so i check with if

condition after disposing.you can check code given below for that condition.as per condition it gives me "Nikunj" Ouput in message box it meanse dt is not disposed after calling the dispose method.so can u please tell me why?And i Want to know about all resources which i used i want to know how can i free it.whether it is variable,object,datatable,datareader etc..... if it is any where with example then please give me link.and if it's from msdn then it's better. i want to know from Microsoft that how Microsoft Suggest to do that.


Private Sub grd_expense_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles grd_expense.DoubleClick
        Dim dt As DataTable
            i = grd_expense.Item(0, grd_expense.CurrentRow.Index).Value
            dt = oClsDataaccess.Get_dataTable("Select Expense_Id,Expense_Name,Comment From dbo.Tbl_Expense where Expense_Id=" & i)

            If dt.Rows.Count > 0 Then
                txtexpense.Text = dt.Rows(0)("Expense_Name").ToString()
                txtcomment.Text = dt.Rows(0)("Comment").ToString()
            End If
        Catch ex As Exception
            LogHelper.Error("Expnese_Master:grd_expense_DoubleClick" & ex.Message())
            If dt.Rows.Count > 0 Then
                MsgBox("Nikunj", MsgBoxStyle.Information)
                MsgBox("Dilip", MsgBoxStyle.Critical)
            End If
        End Try
    End Sub

And Finally i want ask to microsoft that is there any bug in this fourm or any updation is working at 25-Jan-2011 at 01.00 a.m. because it doesn't shows me sub category of fourm

and fires alert that select fourm.if u want screen shot then tell me i will provide you. And I think here this Provision must be there so i can directly put image also.



I just started with my first ASP.net page with C# and I want to create a dynamic table with information from my database. I have connected to the database to the page with datareader, so that is solved.

The problem is making the table and import the data into the different rows and columns. I also want the possibility to edit and remove this information from the table. Its supposed to be a table with information about customers.

I have no idea whats the best solution for this and I hope you guys got information or links that show how to do this?




how to get another website data which in table using jquery

in this site


a table

i require only data how to get it




how to get it


Hi Guys, 

i need to create a dynamic table asp.net (vb) datatable with textbox entry option,

what i have is, a access table with the parameter of

Columns : 10 or 16 (values can be change)  (10 =  has to create 10 cols, 16 = 16 cols, but please note that it can be any number) 


Rows :  5 or 10 

with the header text for each column, aslo i need to create a function to capture input data on a dynamic data-table in asp.net page. 

Please help 

kind Regards 



1 My DataTable Return following Data

Acct    Books    Annual
Acct    Books    Monthly
Acct    Tax    Income Tax
Admin    GH    Eqpt
Admin    Offc    Eqpt
Admin    Offc    Misc
Admin    Offc    Eqpt
BPO    DE    DE.US
Corp    Comp    Insurance
Corp    Legal    Court
Corp    Legal    Court

but i require in this format

  Income Tax
<ul>   GH 
<li>   Eqpt

<ul> Offc 



I faced a problem which i was trying to solve for the past few days. Hope you guys can help Smile

I am developing a BIZTALK solution using BIZTALK 2006 R2 to grab a XML file from a folder and process it by inserting the data in 2 different tables in MS SQL. I am not sure how to insert the data into 2 different rows. And also what bothers me is that it involve multiple rows insertion at a time in 1 of the table.

To explain more, Below is a sample Detination XML. The destination XML consists of 2 major parts. The data in "EDI_IMPORT_SHIPMENT_INS" element need to insert into a table and the data in "FEE" element need to be insert into another. As you guys can see, the Fee element may have more than 1 element in a XML.

<ns0:InsReq xmlns:ns0="http://ShpINS">


<ns0:FEE TYPE="TDT" AMT="648.8000" CUR_TYPE="AUD" LINE_NUM="1"></ns0:FEE>

<ns0:FEE TYPE="GST" AMT="648.8000" CUR_TYPE="AUD" LINE_NUM="1"></ns0:FEE>

<ns0:FEE TYPE="TDT" AMT="98.1200" CUR_TYPE="AUD" LINE_NUM="2"></ns0:FEE>

<ns0:FEE TYPE="GST" AMT="98.1200" CUR_TYPE="AUD" LINE_NUM="2"></ns0:FEE>



Hope you guys can help me with this as I urgently need to complete this project ASAP. THanks in advance.

Hi Guys,I have a regular table displayed in aspx file.  It has following structure with 2 rows and 3 row split in 2 cells.  When I mimic this code in asp.net it doesnt fill the window heightwise even though i have table height=100%.  Widthwise it does.I am fixing the heights of first two rows as shown in the code, with the third row (having 2 td) filling the remaining height.  If i paste the same code in classic asp, it works fine.am i missing anything?thanks in advance for your help.<!--<table border="1" width="100%" id="table1" height="100%"> <tr> <td colspan="2" height=79>Row1</td></tr> <tr> <td colspan="2" height=27>Row2</td></tr> <tr>  <td >R3C1</td>  <td>R3C2</td> </tr></table>-->


I have common sub to save data from data table to database like this


It works fine, but problem comes when i populate Datagridview using a joined query which takes values from more than six tables, for example

        qry = "SELECT rsiteressr, rsitesr, rsitesitesr as [Site ID], cityname as [City], sitelocation as [Location], dispsize as [Size]"

        qry = qry & ", rsiteacqdate as [Date Acq], rsiteexpdate as [Date Exp], DATEDIFF(Day,rsiteacqdate,rsiteexpdate)+1 as [Days], rsitelastrate as [Last Vendor Rate]"

        qry = qry & ", rsiteamount as [Current Vendor Rate],rsitecutout as [Cutout], rsiteillumination as [Illumination],rsiteothercost as [Other Cost], rsitenote as [Remarks]"

        qry = qry & ", siteimageid"

        qry = qry & " FROM subressite"

        qry = qry & " LEFT JOIN site ON  rsitesitesr=sitesr"

        qry = qry & " LEFT JOIN city ON sitecity=citysr"

        qry = qry & " LEFT JOIN dsize ON sitesize=sizesr"

        qry = qry & " WHERE rsiteressr=" & txtID.Text

main table name is subressite where from i populate grid and want to save data in this table. but i also have to show some fields as description NOT editable and not part of table subressite, data for such fields is coming from joined query like cityname, sitelocation etc. and filled in grid,

What should i do to  call ad.Update(tb) method to save data in table and how to handle unbound fileds like, cityname, sitelocation as they are just to display not editable and even they are not defined in table subressite. i have seen an example joined view, but it fills data from all tables which is loss of resources and extra work




Suppose I have two tables "t1" and "t2"

t1 :
id  FirstName  Age
1   Joe        22
2   James      33
3   Bart       28
id  LastName  
1   Coymer
2   Manes

I can combine the data from the two tables using an inner join.

1  Joe    22  CoyLap
2  James  33  Manes

The problem though is that the person "Bart" with an Id of 3 is
left out because he does not have an entry in the second table.
So how can I include Bart in the output in such a way that the output
might look like this:

1  Joe    22   CoyLap
2  James  33   Manes
3  Bart   null null

Or spaces can instead appear for Barts last name and age instead of null.
Basically I just need Bart in the output as well even though
he has no age and last name.


I have a question whether we can join the data based on row index.

I want to fetch the data from table 1 and table 2, based on row index. i.e when the row at index 1 in table 1 and index 1 at table 2 have same value of 1st column. Even when the first column of table 1 is repeated many times in table 2, I do not need all those rows. I need a row where value of column 1 is same in table 1 and table 2 as well as index of that row is same in both the tables.


Below tables given for just example purpose.

I want to join table 1 and table 2 below such that I get emp_id in table 1 is same as table 2 and that row's index in second row is same as that of table 1.

In below e.g. emp_id 1 is present twice in table 2 but only first row of table 2 is to be picked in result. Because emp_id 1 is present in table 1 as well as table 2 & at same row index 1.

Can you please help how can I do this? I have tried Join.. But seems like Join is not going to work for me.


I got two tables something like below:

Emp_id Name

1          west

2          east

3          hello

I have another table like below:

Emp_id Training_month

1           may

1           aug

2           may


I want result table something like

Emp_id    name      training month

1             west       may



I want to create a data view from two tables using a primary key like we do in sql.

Consider I have two data tables Table A and Table B.

Table A - id,name,address

Table B - id,phone,email

I have to get fill the data view using the id field as the primary key. Like below,

DataView - id,name,address,phone,email

And I want to know about how put where condition like id=1

Note: I am using .NET 2.0 framework. And i dont want to do anything in the database.

Kindly help me ASAP.

Thanks in Advance,



I want to create a dataview from two tables using a primary key like we do in sql.


Consider I have two tables Table A and Table B.

Table A - id,name,address

Table B - id,phone,email


I have to get fill the data view using the id field as the primary key. Like below,

DataView - id,name,address,phone,email


Kindly help me ASAP.

Thanks in Advance,


I have two tables Employee and Customer.
EMployee             Customer
ID                            ID
EmpID                    Type
DealerID                 Symbol
EmpDate                 Code

I need the output to retrieve the following results.. i just gave only 4 records here..
ID        EmpID          DealerID       EmpDate          Price         Type       Symbol         Code
123         1                    XU            5/1/2010           30             I                AG             1
234         2                    XU            5/1/2010           32             I                AG             0

456         3                    AG            5/10/2010         45             U               CAT           2
456         4                    AG             5/10/2010        34             U               CAT           0

the result must be in a way the

EmpID must be different
ID can be same or different
DealerID, EmpDate, Type Symbol must be the same
where as the Code must be different and code field has only values - 0, 1, 2 in it.

I tried using self join for both tables and made an inner join to connect both the self join tables but it doesn't work.

Can you please give your suggestions..



i have ui layer

and i have two table for examlple



in database

in ui layer i have seperate panels for insertion of dept fields, and emp fields

i have two conditions apply sepreatly , here for insertion of two fields 

1)if dept fields insertion  faild i dont want to insert employee fields to insert in emp table

2)if dept fileds  insertion is sucsessfull and  employee fields insertion fail i dont want to insert employee fields in employee table and i  want to delete previously inserted dept row  from dept table

here no relation between two tables and here can i use transations


I have recently been working through the entity framework sample. http://msdn.microsoft.com/en-us/library/cc488549.aspx

Having downloaded and run the Enabling Dynamic Data in ASP.NET Data-Bound Controls sample I found that the Gridview does not actually update the database when the SaveChanges method is called. The reason for this is that the Attach method is not successfully attaching. See the following code:

Public Sub Update(ByVal p As SalesOrderDetail)
            Using db As New EnableDynamicData_EF.AdventureWorksLT_DataEntities()
                ' db.DeferredLoadingEnabled = false;
            End Using
End Sub

The only work around I could find was to avoid using the attach command. Is there a reason for this?



i have two tables in my database  A and B and the data in the data are

A table                       B table

ques                          val

-----                            -----

jhsgf                             5

sdgfjh                            1

jhgjh                              89

hjgfjh                            121

the above is the sample of the i want to join both the column in those table and get a result like this 

table c:

ques         val

-----        -----

jhsgf          5

sdgfjh       1

jhgjh         89

hjgfjh       121

i tried this querie but this is mutiplying the rows   create view xc as select ques,val from a,b;

plseae tell me how to write sql querie for this operation 

thanks in advance


i m facing the Proble

i want to insert values into multiple table in one insertion Procedure is it Possible?

see my info

Table name UserPersonal columns



second table userRegForm columns


Thired table UserAddress columns

           ([ UserRegform_pkID]
           ,[ UserAddress_StreetAddress1]
           ,[ UserAddress_StreetAddress2]
           ,[ UserAddress_City]
           ,[ UserAddress_District]
           ,[ UserAddress_State]
           ,[ UserAddress_Country]
           ,[ UserAddress_Pincode]
           ,[ UserAddress_EmailID]
           ,[ UserAddress_UpdatedDate])


Now i want to insert values of these three table in one insertion Procedure 

in this UserRegForm Table is the Master Table

so pls write ainsertion Pro for me

i m new to sql  PLS






<< Previous      Next >>

Microsoft   |   Windows   |   Visual Studio   |   Sharepoint   |   Azure