How To Get Autonumber And Insert Into Other Table?
Nov 27, 2007
Hi
I have 3 table (tbl1, tbl2, tbl3).
tbl1 colums ---Pid(autonNumber),Pcode(unique values),PGuid
tbl2 columns---Pid, CatId,Fid
tbl3 Colums----Pid,BundleId
The user will upload 1 excel(CSV) sheet 500 rows. I managed to bulkupload to tbl1. But how do I get the all Pid generated (after the uplaod) from tbl1 and insert into Pid of tbl2 and tbl3. Any ideas or code example will be more appreceatable. I am using asp.net with sql 2000, but expecting some kind of lead to get started.
Is it possible to do using sqlprocedure ? View ? etc
Advance thanks
View 6 Replies
ADVERTISEMENT
Oct 24, 2006
Simple question, I hope. How can I retrieve the AutoNumber generated by
INSERTing a row using SqlCommand? This would be the unique ID of the
record and I need to perform other operations with it.
Any help would be great. Thanks in advance
View 5 Replies
View Related
Mar 28, 2006
Hiim new to ms sql server, having previously used mysql. How do i make a auto number field? What datatype shall i use for it? like autonumber for mysql. Ive tried setting my primary key field to uniqueidentifier data type but then i still need to manually add a guid key in there. i want it so it automatically generates a unique key everytime i add a new row. is this possible?!hope someone can help!thanks
View 2 Replies
View Related
Apr 30, 2015
table2 is intially populated (basically this will serve as historical table for view); temptable and table2 will are similar except that table2 has two extra columns which are insertdt and updatedt
process:
1. get data from an existing view and insert in temptable
2. truncate/delete contents of table1
3. insert data in table1 by comparing temptable vs table2 (values that exists in temptable but not in table2 will be inserted)
4. insert data in table2 which are not yet present (comparing ID in t2 and temptable)
5. UPDATE table2 whose field/column VALUE is not equal with temptable. (meaning UNMATCHED VALUE)
* for #5 if a value from table2 (historical table) has changed compared to temptable (new result of view) this must be updated as well as the updateddt field value.
View 2 Replies
View Related
Jan 31, 2008
I have created a table Table with name as Varchar and id as int. Now i have started inserting the rows like, insert into Table values ('arun',20).Yes i have inserted a row in the table. Now i have got the values " arun's ", 50. insert into Table values('arun's',20) My sqlserver is giving me an error instead of inserting the row. How will you solve this problem?
View 3 Replies
View Related
Apr 21, 2008
An insert statement was not inserting all the data into a table. Found it very strange as the other fields in the row were inserted. I ran SQL profiler and found that sql statement had all the fields in the insert statement but some of the fields were not inserted. Below is the sql statement which is created dyanmically by a asp.net C# class. The columns which are not inserted are 'totaltax' and 'totalamount' ...while the 'shipto_name' etc...were inserted.there were not errors thrown. The sql from the code cannot be shown here as it is dynamically built referencing C# class files.It works fine on another test database which uses the same dlls. The only difference i found was the difference in date formats..@totalamount=1625.62,@totaltax=125.62are not inserted into the database.Below is the statement copied from SQL profiler.exec sp_executesql N'INSERT INTO salesorder(billto_city, billto_country, billto_line1, billto_line2, billto_name,billto_postalcode, billto_stateorprovince, billto_telephone, contactid, CreatedOn, customerid, customeridtype,DeletionStateCode, discountamount, discountpercentage, ModifiedOn, name, ordernumber,pricelevelid, salesorderId, shipto_city, shipto_country,shipto_line1, shipto_line2, shipto_name, shipto_postalcode, shipto_stateorprovince,shipto_telephone, StateCode, submitdate, totalamount,totallineitemamount, totaltax ) VALUES(@billto_city, @billto_country, @billto_line1, @billto_line2,@billto_name, @billto_postalcode, @billto_stateorprovince, @billto_telephone, @contactid, @CreatedOn, @customerid,@customeridtype, @DeletionStateCode, @discountamount,@discountpercentage, @ModifiedOn, @name, @ordernumber, @pricelevelid, @salesorderId,@shipto_city, @shipto_country, @shipto_line1, @shipto_line2,@shipto_name, @shipto_postalcode, @shipto_stateorprovince, @shipto_telephone,@StateCode, @submitdate, @totalamount, @totallineitemamount, @totaltax)',N'@billto_city nvarchar(8),@billto_country nvarchar(13),@billto_line1 nvarchar(3),@billto_line2 nvarchar(4),@billto_name nvarchar(15),@billto_postalcode nvarchar(5),@billto_stateorprovince nvarchar(8),@billto_telephone nvarchar(3),@contactid uniqueidentifier,@CreatedOn datetime,@customerid uniqueidentifier,@customeridtype int,@DeletionStateCode int,@discountamount decimal(1,0),@discountpercentage decimal(1,0),@ModifiedOn datetime,@name nvarchar(33),@ordernumber nvarchar(18),@pricelevelid uniqueidentifier,@salesorderId uniqueidentifier,@shipto_city nvarchar(8),@shipto_country nvarchar(13),@shipto_line1 nvarchar(3),@shipto_line2 nvarchar(4),@shipto_name nvarchar(15),@shipto_postalcode nvarchar(5),@shipto_stateorprovince nvarchar(8),@shipto_telephone nvarchar(3),@StateCode int,@submitdate datetime,@totalamount decimal(6,2),@totallineitemamount decimal(6,2),@totaltax decimal(5,2)',@billto_city=N'New York',@billto_country=N'United States',@billto_line1=N'454',@billto_line2=N'Road',@billto_name=N'Hillary Clinton',@billto_postalcode=N'10001',@billto_stateorprovince=N'New York',@billto_telephone=N'124',@contactid='8DAFE298-3A25-42EE-B208-0B79DE653B61',@CreatedOn=''2008-04-18 13:37:12:013'',@customerid='8DAFE298-3A25-42EE-B208-0B79DE653B61',@customeridtype=2,@DeletionStateCode=0,@discountamount=0,@discountpercentage=0,@ModifiedOn=''2008-04-18 13:37:12:013'',@name=N'E-Commerce Order (Before billing)',@ordernumber=N'BRKV-CC-OKRW5764YS',@pricelevelid='B74DB28B-AA8F-DC11-B289-000423B63B71',@salesorderId='9CD0E11A-5A6D-4584-BC3E-4292EBA6ED24',@shipto_city=N'New York',@shipto_country=N'United States',@shipto_line1=N'454',@shipto_line2=N'Road',@shipto_name=N'Hillary Clinton',@shipto_postalcode=N'10001',@shipto_stateorprovince=N'New York',@shipto_telephone=N'124',@StateCode=0,@submitdate=''2008-04-18 14:37:10:140'',@totalamount=1625.62,@totallineitemamount=1500.00,@totaltax=125.62
thanks
View 7 Replies
View Related
Mar 24, 2008
Is there a way to avoid entering column names in the excel template for me to create an excel file froma dynamic excel using openrowset.
I have teh following code but it works fien when column names are given ahead of time.
If I remove the column names from the template and just to Select * from the table and Select * from sheet1 then it tells me that column names donot match.
Server: Msg 213, Level 16, State 5, Line 1Insert Error: Column name or number of supplied values does not match table definition.
here is my code...
SET @sql1='select * from table1'SET @sql2='select * from table2'
IF @File_Name = '' Select @fn = 'C:Test1.xls' ELSE Select @fn = 'C:' + @File_Name + '.xls' -- FileCopy command string formation SELECT @Cmd = 'Copy C:TestTemplate1.xls ' + @fn
-- FielCopy command execution through Shell Command EXEC MASTER..XP_CMDSHELL @cmd, NO_OUTPUT -- Mentioning the OLEDB Rpovider and excel destination filename set @provider = 'Microsoft.Jet.OLEDB.4.0' set @ExcelString = 'Excel 8.0;HDR=yes;Database=' + @fn
exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT * FROM [Sheet1$]'') '+ @sql1 + '') exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT * FROM [Sheet2$]'') '+ @sql2 + ' ')
View 4 Replies
View Related
Apr 20, 2008
On my site users can register using ASP Membership Create user Wizard control.
I am also using the wizard control to design a simple question and answer form that logged in users have access to.
it has 2 questions including a text box for Q1 and dropdown list for Q2.
I have a table in my database called "Players" which has 3 Columns
UserId Primary Key of type Unique Identifyer
PlayerName Type String
PlayerGenre Type Sting
On completing the wizard and clicking the finish button, I want the data to be inserted into the SQl express Players table.
I am having problems getting this to work and keep getting exceptions.
Be very helpful if somebody could check the code and advise where the problem is??
<asp:Wizard ID="Wizard1" runat="server" BackColor="#F7F6F3"
BorderColor="#CCCCCC" BorderStyle="Solid" BorderWidth="1px"
DisplaySideBar="False" Font-Names="Verdana" Font-Size="0.8em" Height="354px"
onfinishbuttonclick="Wizard1_FinishButtonClick" Width="631px">
<SideBarTemplate>
<asp:DataList ID="SideBarList" runat="server">
<ItemTemplate>
<asp:LinkButton ID="SideBarButton" runat="server" BorderWidth="0px"
Font-Names="Verdana" ForeColor="White"></asp:LinkButton>
</ItemTemplate>
<SelectedItemStyle Font-Bold="True" />
</asp:DataList>
</SideBarTemplate>
<StepStyle BackColor="#669999" BorderWidth="0px" ForeColor="#5D7B9D" />
<NavigationStyle VerticalAlign="Top" />
<WizardSteps>
<asp:WizardStep runat="server">
<table class="style1">
<tr>
<td class="style4">
A<span class="style6">Player Name</span></td>
<td class="style3">
<asp:TextBox ID="PlayerName" runat="server"></asp:TextBox>
</td>
<td>
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server"
ControlToValidate="PlayerName" ErrorMessage="RequiredFieldValidator"></asp:RequiredFieldValidator>
</td>
</tr>
<tr>
<td class="style5">
<td class="style3">
<asp:DropDownList ID="PlayerGenre" runat="server" Width="128px">
<asp:ListItem Value="-1">Select Genre</asp:ListItem>
<asp:ListItem>Male</asp:ListItem>
<asp:ListItem>Female</asp:ListItem>
</asp:DropDownList>
</td>
<asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server"
ControlToValidate="PlayerGenre" ErrorMessage="RequiredFieldValidator"></asp:RequiredFieldValidator>
</td>
</tr>
</table>
Sql Data Source
<asp:SqlDataSource ID="InsertArtist1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" InsertCommand="INSERT INTO [Playerst] ([UserId], [PlayerName], [PlayerGenre]) VALUES (@UserId, @PlayerName, @PlayerGenre)"
ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>">
<InsertParameters>
<asp:Parameter Name="UserId" Type="Object" />
<asp:Parameter Name="PlayerName" Type="String" />
<asp:Parameter Name="PlayerGenre" Type="String" />
</InsertParameters>
</asp:SqlDataSource>
</asp:WizardStep>
Event Handler
To match the answers to the user I get the UserId and insert this into the database to.protected void Wizard1_FinishButtonClick(object sender, WizardNavigationEventArgs e)
{
SqlDataSource DataSource = (SqlDataSource)Wizard1.FindControl("InsertArtist1");
MembershipUser myUser = Membership.GetUser(this.User.Identity.Name);
Guid UserId = (Guid)myUser.ProviderUserKey;String Gender = ((DropDownList)Wizard1.FindControl("PlayerGenre")).SelectedValue;
DataSource.InsertParameters.Add("UserId", UserId.ToString());DataSource.InsertParameters.Add("PlayerGenre", Gender.ToString());
DataSource.Insert();
}
View 1 Replies
View Related
Oct 12, 2007
Hi,
i have a file which consists data as below,
3
123||
456||
789||
Iam reading file using bulk insert and iam inserting these phone numbers into table having one column as below.
BULK INSERT TABLE_NAME FROM 'FILE_PATH'
WITH (KEEPNULLS,FIRSTROW=2,ROWTERMINATOR = '||')
but i want to insert the data into table having two columns. if iam trying to insert the data into table having two columns its not inserting.
can anyone help me how to do this?
Thanks,
-Badri
View 5 Replies
View Related
Jul 24, 2015
I'm inserting from TempAccrual to VacationAccrual . It works nicely, however if I run this script again it will insert the same values again in VacationAccrual. How do I block that? IF there is a small change in one of the column in TempAccrual then allow insert. Here is my query
INSERT INTO vacationaccrual
(empno,
accrued_vacation,
accrued_sick_effective_date,
accrued_sick,
import_date)
[Code] ....
View 4 Replies
View Related
Nov 21, 2006
For reasons that are not relevant (though I explain them below *), Iwant, for all my users whatever privelige level, an SP which createsand inserts into a temporary table and then another SP which reads anddrops the same temporary table.My users are not able to create dbo tables (eg dbo.tblTest), but arepermitted to create tables under their own user (eg MyUser.tblTest). Ihave found that I can achieve my aim by using code like this . . .SET @SQL = 'CREATE TABLE ' + @MyUserName + '.' + 'tblTest(tstIDDATETIME)'EXEC (@SQL)SET @SQL = 'INSERT INTO ' + @MyUserName + '.' + 'tblTest(tstID) VALUES(GETDATE())'EXEC (@SQL)This becomes exceptionally cumbersome for the complex INSERT & SELECTcode. I'm looking for a simpler way.Simplified down, I am looking for something like this . . .CREATE PROCEDURE dbo.TestInsert ASCREATE TABLE tblTest(tstID DATETIME)INSERT INTO tblTest(tstID) VALUES(GETDATE())GOCREATE PROCEDURE dbo.TestSelect ASSELECT * FROM tblTestDROP TABLE tblTestIn the above example, if the SPs are owned by dbo (as above), CREATETABLE & DROP TABLE use MyUser.tblTest while INSERT & SELECT usedbo.tblTest.If the SPs are owned by the user (eg MyUser.TestInsert), it workscorrectly (MyUser.tblTest is used throughout) but I would have to havea pair of SPs for each user.* I have MS Access ADP front end linked to a SQL Server database. Forreports with complex datasets, it times out. Therefore it suit mypurposes to create a temporary table first and then to open the reportbased on that temporary table.
View 6 Replies
View Related
Apr 11, 2008
Hi,ALL
I wants to insert data into multiple table within a single insert query
Thanks
View 3 Replies
View Related
Mar 1, 2004
Hi
I am trying to use multiple insert for a table T1 to add multiple rows.
Ti has trigger for insert to add or update multiple rows in Table T2.
When I provide multiple insert SQL then only first insert works while rest insert statements does not work
Anybody have any idea about why only one insert works for T1
Thanks
View 10 Replies
View Related
Jun 28, 2007
Help please!
I have an asp page with some simple vbscript to add a record to a table, the record has a datefield (dob).
the insert results in a US formated date if I add a record to a dynamic recordset but a UK formated date if I insert direct to the table ?????
i.e.
if request("dob") is "01/11/2007" (1st november 2007)
set conn = server.createobject("adodb.connection")
set rs = server.createobject("adodb.recordset")
rs.open "tez", mc, 2, 2 rs.addnew
rs("dob") = request("dob")
rs.update
11 jan 2007 stored in table
while
set trs = Server.CreateObject("ADODB.RecordSet")
qfn= "insert tez values('"+request("dob")+"')"
trs.Open qfn,mc
results in
1 november 2007 is written to the table.
Both of these methods are used in the same asp page.
This is on a windows2003 server, sql2005,iisv6, asp.netv2
I have tried every setting I can find in iis,asp,sql server to no avail.
I need the recordset method to work correctly.
Terry
View 8 Replies
View Related
Apr 23, 2001
Is there a way to create a(unique id) that will automatically populate similar to ACCESS?
View 1 Replies
View Related
Oct 13, 1998
I am new to SQL Server coming from Access.
In the work I do, I use the Autonumber data type often.
I don`t seem to see this SQL Server.
Can you tell me if this can be done?
jstravato@ccri.cc.ri.us
View 1 Replies
View Related
Aug 3, 2001
Is there somthing in SQL Server similar to the AutoNumber feature in access that could just add a new key + 1 of the last when you add a new entry?
View 1 Replies
View Related
Jan 18, 2004
sql 2000
how can i set primary key to autonumber?
i created a form with this table,, i get error when trying to insert..
i want "id: column to autogenerate number
.................................................. .....................................
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert the value NULL into column 'id', table 'Guestbook.dbo.guestbook'; column does not allow nulls. INSERT fails.
View 4 Replies
View Related
Mar 21, 2006
Hi
When we open the table in design view in access , we get to see the Data type as Autonumber there.
How to find out the same in SQL Server , if a particualar column fas been made autonumber & how to make it also .
Thanks
Bharat Butani
View 3 Replies
View Related
Apr 25, 2007
create table tblCasCodeDef(
CasCodeDefID int not null,
CasCodeDef char(100),
CasDescription char(200),
EngineeringFunction char(100),
Primary Key (CasCodeDefID));
In this code, how can I make my CasCodeDefID as autonumber?
===============
JSC0624
===============
View 2 Replies
View Related
May 7, 2007
I have my id column set to automatically provide and id number, I am wondering if it will be ever be posible that it will give me the value ZERO 0.
( I want to use the lack of the 0 id, as a way to detect that there is need of a new record in a client application)
View 2 Replies
View Related
Oct 11, 2005
Hi,I am always confused about what to use as the key for a table.Let's say I have a company-employee table.[company]---*[employee]My co-worker likes to use an AutoNumber or Counter as the key for the[employee] table (and everything).I personally use an actual key set for the [employee] table.So, his table will have one (Autonumber or LONG) column as the key. Thecompany_id is just another attribute.Mine, on the other hand, has 2 columns as the key. (i.e.) company_id +employee_idSo, what is the deal?--[color=blue]> There is no answer.> There has not been an answer.> There will not be an answer.> That IS the answer!> And I am screwed.> Deadline was due yesterday.>> There is no point to life.> THAT IS THE POINT.> And we are screwed.> We will run out of oil soon.[/color]
View 22 Replies
View Related
Feb 1, 2008
I am working with a database that was created in Lotus and importingit into Access. There are hundreds of clients in this data base thathave to be kept and maintained. My question is: Will auto numberingwork with this imported file and will it continue with the last numberthat is in there and if not can I make it work and how?
View 2 Replies
View Related
Jul 20, 2005
Sorry for my bad English but I'm Italian...I have a question for you...I delete all record of table but I don't set autonumber field to start withvalue 1....What do you do to set a values of autonumber colomns with Query Analizer orEnterprise Manager...???
View 4 Replies
View Related
Jul 20, 2005
HiPlease help me URGENTHow do i make an tableField with aAuto-Incr, the same like in an access DB where it'scalled AutoNumberThanx in advance__________________________________________________ ________________ FlemmingPaulsen ICQ#: 270065050 Current ICQ status: + More ways to contact me__________________________________________________ ________________
View 1 Replies
View Related
Jul 20, 2005
What is the equivelant to Autonumber coming from an Access World toSQL?TIAMichael
View 8 Replies
View Related
Nov 13, 2006
The problem is to set the right type for "Question_number"/"test_str"I have tried Integer & String (Long Integer din't work....)
The field: Question_number is autonumber dim test_str as Integerdim besvart_str as string
answer_str = "Yes" Date_now = nowtest_str = 187 Dim StrSQL_2 as String = "UPDATE q_a_table" & _ " SET answer = '" & answer_str & "' WHERE [q_a_table].Question_number= '" & test_str & "'"
Larsoslo
View 1 Replies
View Related
Jun 15, 2007
Hello friends,
I am using Sql Server 2005 in my web application. In one scenario I had made one field of a table as Autonumber by keeping its datatype as int and then setting its Identity flag to true. Now is there any inbuild function that can give us the next number tat will be generated if a new record will be inserted. This is needed in the case if last 1 or 2 records are deleted for e.g.
last record has value 7 and if the record is deleted still for the new record the number generated will be 8 i.e. after 6 there is 8. So by any means can we get the next number, or can we keep that autonumber to move serially even in case some record is deleted it should go for the max + 1 number.
Please let me know.
Thanks & RegardsGirish Nehte
View 5 Replies
View Related
Dec 8, 2003
Hi, I wish to build a datagrid, in an ASP.NET web page, in which I can display a row from a table and when I press a button it will move forward one row. The table is not currently sorted and I need it to be alpahbetical by surname so I thought if I created a view I could sort the table alphabetically on the 'Surname' column. I know that ADO.NET when used in disconnected mode doesn't have a cursor which remembers the last row it was on so I thought if I could create an autonumber on the view then I could use this in the 'where' clause to tell SQLserver which line I wished to select by taking the previous number and adding one. Is this the best way to do this and if so could some one give advice on how to write the line of the query that will add in an autonumber column
thanks
John
View 2 Replies
View Related
Aug 16, 2004
Hello all,
Has anyone heard about autonumber fields skipping one number? For example, the autonumber field goes from 39 to 41 skipping 40 entirely.
Any ideas why it would do this?
Thanks in advance.
Richard M.
View 2 Replies
View Related
Oct 4, 2004
hi,
i am thinking of using autonumber in my table which is more likely to grow over time. but i am also concerned that what if the records get too much? is there is any other fields to use instead of autonumber? is uniqueidentifier a better option? i have a bit of discussion with my colleague about this matter.
any help is appreciated
View 1 Replies
View Related
Oct 27, 2004
Hi,
I remember in Access. You can set the primary to increment by one automatically. Can you do this in asp.net?
Thanks.
Dennis
View 3 Replies
View Related
Dec 1, 2004
i am using autonumber as a primary key in one table. in my app., row from that table will be moved to another table after some time but that autonumber is important. when there are no any data in first table, and new row is inserted, autonumber starts from 1 which i don't want as there will be data redundancy when it will be moved to another table. so is there any way i can force the autonumber to start from previous value rathe than 1?
View 2 Replies
View Related
