Instead Of Insert - Identity Null Error
Sep 24, 2004
I'm atempting to use an Instead of Insert Trigger on a view to insert a record into a base table. The base table boundaryline has an identity primary key defined. The following is the error I'm receiving:
The column 'id1' in table 'dbo.ParcelBoundaries1' cannot be null.
Could not insert a record in the database.
The view is as follows:
SELECT IS_TAXLOT, IS_OTHER, IS_CARTOGRAPHIC, IS_RAILROAD_ROW, IS_IRRIGATION_ROW, IS_STREET_ROW, IS_CLOSING_ROW, IS_CONSTRUCTION,
IS_CONFLICT, TYPE, GMPID, BEARING, DISTANCE_LEGAL, DISTANCE_CALCULATED, GEOMETRY, GEOMETRY_XLO, GEOMETRY_XHI,
GEOMETRY_YLO, GEOMETRY_YHI, id1
FROM dbo.BoundaryLine
The trigger is as follows:
CREATE TRIGGER TR_ParcelBoundaries1 on ParcelBoundaries1
INSTEAD OF INSERT
as
BEGIN
INSERT INTO BoundaryLine
(IS_TAXLOT,
IS_OTHER,
IS_CARTOGRAPHIC,
IS_RAILROAD_ROW,
IS_IRRIGATION_ROW,
IS_STREET_ROW,
IS_CLOSING_ROW,
IS_CONSTRUCTION,
IS_CONFLICT,
GMPID,
BEARING,
DISTANCE_LEGAL,
DISTANCE_CALCULATED,
GEOMETRY,
GEOMETRY_XLO,
GEOMETRY_XHI,
GEOMETRY_YLO,
GEOMETRY_YHI)
Select 'YES', 'NO','NO','NO','NO','NO','NO','NO','NO', GMPID, BEARING, DISTANCE_LEGAL,
DISTANCE_CALCULATED, GEOMETRY, GEOMETRY_XLO, GEOMETRY_XHI,
GEOMETRY_YLO, GEOMETRY_YHI
from
inserted
END
Any thoughts would be greatly appreciated?
View 3 Replies
ADVERTISEMENT
Mar 8, 2007
Hey all, I have been working with Northwind on SQL Server Express
Trying to learn Transact-SQL. I am stuck at this point from code in the instruction manual. I don't know if I have a permission set to something that I need or what but I get the following error that is outlined at the bottom of the code. Any help would be appreciated.
USE Northwind
GO
ALTER PROC spInsertDateValidatedOrder
@CustomerIDnvarchar(5),
@EmployeeIDint,
@OrderDatedatetime= NULL,
@RequiredDatedatetime= NULL,
@ShippedDatedatetime= NULL,
@ShipViaint,
@Freightmoney,
@ShipNamenvarchar(60)= NULL,
@ShipAddressnvarchar(40)= NULL,
@ShipCitynvarchar(15)= NULL,
@ShipRegionnvarchar(15)= NULL,
@ShipPostalCodenvarchar(10)= NULL,
@ShipCountrynvarchar(15)= NULL,
@OrderIDintOUTPUT
AS
DECLARE @InsertedOrderDatesmalldatetime
--Test to see if supplied date is over seven days old. If so
--replace with NULL value otherwise, truncate the time to be midnight.
IF DATEDIFF(dd, @OrderDate, GETDATE()) > 7
BEGIN
SELECT @InsertedOrderDate = NULL
PRINT 'Invalid Order Date'
PRINT 'Supplied Order Date was greater than 7 days old'
PRINT 'The value has been reset to NULL'
END
ELSE
BEGIN
SELECT @InsertedOrderDate = CONVERT(datetime, (CONVERT(varchar,@OrderDate, 112)))
PRINT 'The Time of Day in Order Date was truncated'
END
--create the new record
INSERT INTO Orders
VALUES
(
@CustomerID,
@EmployeeID,
@InsertedOrderDate,
@RequiredDate,
@OrderDate,
@ShippedDate,
@ShipVia,
@Freight,
@ShipName,
@ShipAddress,
@ShipCity,
@ShipRegion,
@ShipPostalCode,
@ShipCountry
)
--Move the identity value from the newly inserted record into output variable.
SELECT @OrderID = @@IDENTITY
Errors received,
Msg 8101, Level 16, State 1, Procedure spInsertDateValidatedOrder, Line 46
An explicit value for the identity column in table 'Orders' can only be specified when a column list is used and IDENTITY_INSERT is ON.
Thanks
View 2 Replies
View Related
Oct 18, 1999
Hi
I'm just wondering why the IDENTITY value is still used when you attempt
an INSERT that fails?
I placed a Foreign Key Constraint on my table. When I put bogus data in
the foreign key field to break the INSERT and get a 'Referential integrity Violated, Unable to Insert error' the Identities are still used!
For example, the Identity was last at 5. Then, I try 10 "bad" Inserts with Foreign Key Violations. The next time I Insert a record Successfully, the indentity starts at 16!!
why does the Identity get incremented when the Insert fails?
Angel
View 1 Replies
View Related
Dec 14, 2004
Cannot insert explicit value for identity column in table 'tblUsed' when IDENTITY_INSERT is set to OFF.
:confused:
View 1 Replies
View Related
Feb 18, 2008
I guess there is first for everything...I had never seen error like this before
Violation of PRIMARY KEY constraint 'PK_table1'. Cannot insert duplicate key in object 'table1'. The statement has been terminated.
In this case Primary Key is Identity column. I do not include Idenity column in Insert statement.
Incidently SQL server machines had cluster failure couple of days before. I am not sure whether it has anything to do with it.
I see this error randomly.
How should I debug it
View 1 Replies
View Related
Jul 24, 2007
I am trying to capture the rows that fail during an insert.
The insert is an OLE DB command component.
I have config'd errors to redirect to a flat file.
My problem is this
I need to override the identity, fine if you are using 'fast load' but then you can't capture the redirected rows.
So I am using the regular 'table or view' data access mode, but then I lose the 'keep identity' checkbox.
At this point I add in another OLE DB command "SET IDENTITY_INSERT table OFF" before the actual insert ODB component.
But now it won't validate. I changed the task 'delay validation' property to true, but still it is failing.
It seemingly cannot detect this statement.
So how then can I capture error rows from an insert where I need to switch the identity off?
[This is a one off historic load I am working on]
View 8 Replies
View Related
Mar 9, 2008
The following SQL statement fails on SQL CE 3.5 but works on SQL Express 2005:
"INSERT INTO BOOKINGS VALUES(@now,'"+note+"'," + p + "); SELECT @@IDENTITY;"
Compact 3.5 doesnt like the SELECT statement claiming that:
There was an error parsing the query. [ Token line number = 1,Token line offset = 72,Token in error = SELECT ]
Can anyone suggest the correct SQL to implement this via Compact? i.e. How do I retrieve the Identity value during and insert statement?
I have removed the SELECT @@IDENTITY; portion of the statement and it runs fine.
View 9 Replies
View Related
Feb 20, 2008
Good Afternoon,
I'm using the personal website starter kit. I would like to add tags to my photos so I've added a Tags table (identity, tagDesc) and an intersection table PhotoTags (PhotoID,TagID). I pass a set of tags as a comma deliminated string such as (Home, Family). The procedure below (sorry I'm not a TSQL programmer) errors out with the message "Cannot insert null into TagDesc" or something like that.
Code Snippet
ALTER PROCEDURE dbo.AddNewPhoto
@AlbumID int,
@Caption nvarchar(50),
@BytesOriginal image,
@BytesFull image,
@BytesPoster image,
@BytesThumb image,
@Latitude numeric,
@Longitude numeric,
@Tags varchar(500)
AS
Begin
declare @Photoid numeric, @TagID numeric,@Spot smallint, @Tag varchar(25)
BEGIN TRAN
--First Insert into the Photos Table
INSERT INTO [Photos] (
[AlbumID],
[BytesOriginal],
[Caption],
[BytesFull],
[BytesPoster],
[BytesThumb],
[Latitude],
[Longitude] )
VALUES (
@AlbumID,
@BytesOriginal,
@Caption,
@BytesFull,
@BytesPoster,
@BytesThumb,
@Latitude,
@Longitude )
SET @Photoid = SCOPE_IDENTITY()
IF @@ERROR <> 0 ROLLBACK TRAN
------------------------------------------------------------
--now parse the tags and enter into PhotoTags and Tags Table
------------------------------------------------------------
--see if the tag exists
WHILE @Tags <>''
BEGIN
SET @Spot = CHARINDEX(',',@Tags)
IF @Spot > 0
BEGIN
SET @Tag = LEFT(@Tag,@Spot -1)
SET @Tags = RIGHT(@Tags,LEN(@Tags) - @Spot)
END
ELSE
BEGIN
SET @Tag = @Tags
SET @Tags = ''
END
IF (NOT EXISTS(SELECT TagID FROM Tags WHERE LOWER(TagDesc) = LOWER(@Tag)))
BEGIN
INSERT INTO Tags (TagDesc) VALUES (@Tag)
SET @TagID = SCOPE_IDENTITY()
END
ELSE
SELECT @TagID = TagID FROM Tags WHERE LOWER(TagDesc) = LOWER(@Tag)
--finally insert into the intersection table
INSERT INTO [PhotoTags] (
[PhotoID],
[TagID]
)
VALUES (
@Photoid,
@TagID
)
END
COMMIT TRAN
RETURN
END
Can someone see my error?
View 5 Replies
View Related
Jul 9, 2006
While I have learned a lot from this thread I am still basically confused about the issues involved.
.I wanted to INSERT a record in a parent table, get the Identity back and use it in a child table. Seems simple.
To my knowledge, mine would be the only process running that would update these tables. I was told that there is no guarantee, because the OLEDB provider could write the second destination row before the first, that the proper parent-child relationship would be generated as expected. It was recommended that I create my own variable in memory to hold the Identity value and use that in my SSIS package.
1. A simple example SSIS .dts example illustrating the approach of using a variable for identity would be helpful.
2. Suppose I actually had two processes updating these tables, running at the same time. Then it seems the "variable" method will also have its problems. Is there a final solution other than locking the tables involved prior to updating them or doing something crazy like using a GUID for the primary key!
3. We have done the type of parent-child inserts I originally described from t-sql for years without any apparent problems. (Maybe we were just lucky.) Is the entire issue simply a t-sql one or does SSIS add a layer of complexity beyond t-sql that needs to be addressed?
TIA,
Barkingdog
View 10 Replies
View Related
Jun 30, 2006
I want to insert a new record into a table with an Identity field and return the new Identify field value back to the data stream (for later insertion as a foreign key in another table).
What is the most direct way to do this in SSIS?
TIA,
barkingdog
P.S. Or should I pass the identity value back in a variable and not make it part of the data stream?
View 12 Replies
View Related
Jul 15, 2015
I have a flat file with the following columns
SampleID Rep_Number Product Protein Fat Solids
In the flat file SampleID and Product are populated in the first row only, rest of the rows only have values for Rep_Number, Protein, Fat, Solids.
SampleID and Product are blank for the rest of the rows. So my task is to fill those blank rows with the first row that has the sampleID and Product and load into the table.
View 7 Replies
View Related
Jun 19, 2008
Hi,
I am having problem in bulk update of a sql server table haning identity column from a datatable( has no identity column) using sqlbulkcopy. I tried several approaches, but it does not show any error nor is the table getting updated. But the identity value seems to getting increased every time.
thanks.
varun
View 6 Replies
View Related
Apr 2, 2007
I am getting this error: "Cannot insert the value NULL into column 'OrderID', table 'outman.outman.Contact'; column does not allow nulls. INSERT fails." -- But my value is not null. I did a response.write on it and it show the value. Of course, it would be nice if I could do a breakpoint but that doesn't seem to be working. I'll attach a couple of images below of my code, the error, and the breakpoint error.
Server Error in '/' Application.
Cannot insert the value NULL into column 'OrderID', table 'outman.outman.Contact'; column does not allow nulls. INSERT fails.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'OrderID', table 'outman.outman.Contact'; column does not allow nulls. INSERT fails.Source Error:
Line 89: sContact.Phone = sPhone.Text.Trim
Line 90: sContact.Email = sEmail.Text.Trim
Line 91: sContact.Save()
Line 92:
Line 93: Dim bContact As Contact = New Contact()Source File: F:InetpubwwwrootOutman KnifeCheckout.aspx.vb Line: 91 Stack Trace:
[SqlException (0x80131904): Cannot insert the value NULL into column 'OrderID', table 'outman.outman.Contact'; column does not allow nulls. INSERT fails.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +857354
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734966
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838
System.Data.SqlClient.SqlDataReader.HasMoreRows() +150
System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout) +214
System.Data.SqlClient.SqlDataReader.Read() +9
System.Data.SqlClient.SqlCommand.CompleteExecuteScalar(SqlDataReader ds, Boolean returnSqlValue) +39
System.Data.SqlClient.SqlCommand.ExecuteScalar() +148
SubSonic.SqlDataProvider.ExecuteScalar(QueryCommand qry) +209
SubSonic.DataService.ExecuteScalar(QueryCommand cmd) +37
SubSonic.ActiveRecord`1.Save(String userName) +120
SubSonic.ActiveRecord`1.Save() +31
Checkout.btnCheckout_Click(Object sender, EventArgs e) in F:InetpubwwwrootOutman KnifeCheckout.aspx.vb:91
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42
View 8 Replies
View Related
May 7, 1999
I have discovered a problem(?) with using replication on 7.0. It seems, no matter which type of replication I use (and I've used all of them), once the server is set up to be a publisher the global identity value is set to NULL. This doesn't seem to affect the identity values in existing tables but could very well affect the program code running the front end program.
I tried creating a new database on the same server just to see what would happen and the @@identity on that database is also NULL.
Does anyone know why this happens and is there a workaround to this? Again, it doesn't seem to affect the tables on which there are identity columns, they are incrementing properly, but I'm worried about the front end programs that are already in existence and are in the process of being developed.
View 1 Replies
View Related
Feb 22, 1999
Hi,
I have upgraded database from sql6.5 to sql 7.o
I have a table tblnetwork on which identity property
is defined on column networkid.
When I issue the following command new record is getting inserted into
tblnetwork and identity column is getting incremented properly.
But, I am not able to get the @@identity value
It is returned as NULL.
insert tblnetwork(formalname,networktype)
values ("name2",11897)
select @@identity
I need this value to insert into some other table.
Pls suggest me why @@identity is returning NULL
thanks,
MMS
View 5 Replies
View Related
Jan 4, 2007
hi,
I've done Googling and forum hunting but haven't had success finding a simple answer... My table schema is such that it requires the (int) LinkedItemID field to be nullable but still those fields which are set must not be duplicates. I see constraint is out of question and also identity doesn't seem to fit since I'm not using autofill for this particular field. Is there some other way doing this on Sql Server 2005?
Thank you.
View 7 Replies
View Related
Jan 30, 2007
Good evening,
I have created a table like this:
create table Teacher(tid integer,
fname varchar(20),
lname varchar(30),
primary key(tid));
Well i insert many Teachers uning : insert into values ... etc.
These inserts are being placed in an sql file named insert.sql.
All the insertions are fine.
Now I'm trying to insert another teacher using another file e.g. my_insert.sql and it says cannot insert NULL into teacher tid etc.
Why does this happen,in my my_insert.sql file all the filds of the tid are filled with non null values?
What's going wrong?
Thanks, in advance!
View 12 Replies
View Related
Apr 18, 2008
Hello,
I'm just learning SSIS and I've hit my first bump. I am doing a bulk import from a tab delimited text file to an empty sql table that has a Idendity column defined. How do I tell the bulk insert task to skip that column when inserting from the text file. If I remove the identity column it imports the data fine, but I want to create the indentity column in the table too.
Thanks.
View 8 Replies
View Related
Nov 26, 2005
after i have inserted a row into the DB i am trying to get the last identity like this :
Dim myCommand2 As New SqlCommand("SELECT @@IDENTITY AS 'Identity'", )
Dim myReader2 As SqlDataReader
myReader2 = myCommand2.ExecuteReader()
where myConnection2 is the connection objectafter i did this i dont underdsand how do i get the 'Identity' value?i tried myReader2 ('Identity') but no luckany 1 can helpthnaks in advancepeleg
View 1 Replies
View Related
May 6, 2002
Let me start off by saying I have posted this on:
comp.databases.ms-sqlserver
My apologies, try not to do that again.
I have a table that I am trying to insert into with data from another
table. Here is that Query:
INSERT INTO item (identifier, name, customlgtxt1, weight,mainprice,
customnumber3, customnumber4)
Select partno, name, description, weight,price, qtyprice, qty
From Import2
This seems to work fine.
My dilemma has to do with an 'id' column in that item table. This is
incrementally updated by one..ie Natural Key. I have set it to
Identity and used Set @@IDENTITY AS 'id'...worked like a charm. But
for reasons that have to do with a front end admin tool used by the
home office I can't set this column to have an Indentity property.
Screws up the insert done with the admin tool.
This is a item/price database by the way. Name of item, price,
description, qty price..etc.
So I've tried to put this trigger on the item table
CREATE TRIGGER auto_fill ON [dbo].[item]
FOR INSERT, UPDATE
AS
BEGIN
declare @maxc int
set @maxc = (select Max(id) from item)
set @maxc = @maxc +1
Select 'id' AS '@maxc',
notorderable = '0',
createdon = getdate(),
createdat = getdate(),
createdby = 'Steve',
modifiedon = getdate(),
modifiedat= getdate(),
modifiedby = 'Steve'
END
But the Insert Into statement (see above) will not work...giving the
error
"Cannot insert the value NULL into column 'id', table
'new_Catalog1v1.dbo.item"
Which I know...that's why I set it to Identity..but that cannot be.
So the question is how to set an autonumber (or natural key or I'm not
sure of the name) that updated from the max(id)table when inserting from another table.
And then...one more.
I have to update a table named UNIQUEIDS with the lastest value of the
id column (max(ID))..UNIQUEIDS keeps track of the latest value inserted
into the id column for a number of tables. Here is another trigger I
put on the item table to update the UNIQUEIDS table.
create trigger upUniqueIds_item on [dbo].[item]
for update,Insert, delete
as
begin
Select @@Identity as 'id' from inserted
Update uniqueids
set id = @@identity
Where tablename = 'item'
End
But of course this doesn't work if I can't set the columns to
IDENTITY.
I hope someone can help and I hope my explanation had made sense.
Need to increment the id field using max(id) and update another table with the last imported value of max(id). One occurs during insert..another after the insert..i think?
View 1 Replies
View Related
Aug 27, 2002
I need to insert into a table that has an identity insert set to on.
I need to know how to turn it off while I insert data into the table.
Here is the senerio
column 1
1
2
View 2 Replies
View Related
Aug 27, 2002
I need to insert into a table that has an identity insert set to on.
I need to know how to turn it off while I insert data into the table.
Here is the senerio
column 1
1
2
View 1 Replies
View Related
Feb 22, 2001
Help!
I need to be able to insert into an Identity column and tried putting
SET IDENTITY_INSERT table ON
in a stored procedure , but it didn't like this if not dbo, which will be the case.
Is there anyway of globally making this setting and leaving it on permenantly
KB
View 1 Replies
View Related
Dec 8, 2004
Is it possible to create a trigger that inserts the @@identity (primary key) from Table1 into a field in Table2?
If so, how? Thank you.
-D-
View 1 Replies
View Related
Nov 10, 2006
Hi
how can I insert @@identity at once (for MS SQL 2000 / 2500)
INSERT INTO Table1 (name) VALUES ('any')
DECLARE @ID int SELECT @@Identity
INSERT INTO Table2 (id_Table1) VALUES (@ID)
does not work
thank you
View 8 Replies
View Related
Sep 7, 2007
hi all,
i have a question:
i have a table that has an identity id column called pId and also a column called ParentPid.
now my question is that i want to insert the value of pId into the ParentPid when i'm adding a new Property to a table.
any idea?
thanks
View 6 Replies
View Related
Apr 2, 2007
Hi All,
I tried enabling the IDENTIY_INSERT ON for 2 tabls in a database. It says that I cannot have 2 tables in a database with IDENTITY INSERT ON.
Why is this so?
Thanks,
Prakash.P
View 8 Replies
View Related
Jun 11, 2007
If I have my table setup like so...
CREATE TABLE Customer
(SID integer IDENTITY(1,1) PRIMARY KEY,
Last_Name varchar(30),
First_Name varchar(30));
How would I do an insert statement so that it auto increments and creates a unique number?
I keep getting errors.
View 4 Replies
View Related
Feb 28, 2008
I have twoo tables Table A (Col1 int, Col2 varchar(10)) and Table B (Col1 Identity(1,1), Col2 varchar(10))
Table A has
1 ABC
2 XYZ
NULL JIM
NULL KIM
10 min
I want to insert this in Table B
Well I seperated teh data per the TableA.Col1 where if Null, Table B will generate its Col1
But how will I do it in SSIS when TableA.Col1 in NOT NULL.
How would I set the IDENTITY INSERT ON on Table B while Inserting through SSIS?
Please advice
View 1 Replies
View Related
Nov 14, 2006
Hey, I've been having problems - when trying to insert a new row i've been trying to get back the unique ID for that row. I've added "SELECT @MY_ID = SCOPE_IDENTITY();" to my query but I am unable get the data. If anyone has a better approach to this let me know because I am having lots of problems. Thanks,Lang
View 2 Replies
View Related
Feb 29, 2008
I have 2 tables - tblOrders and tblOrderDetails. Every time an order is placed, 2 INSERT statements are executed. The first one enters the general order and customer information in the tblOrders table:INSERT INTO tblOrders (custname, custdetails, orderdate) VALUES (@custname, @custdetails, @orderdate)The primary key in this table is OrderID which is an Identity column. This is the foreign key in the tblOrderDetails table.I'm trying to get the Identity value from the first INSERT statement to use in the second INSERT statement:INSERT INTO tblOrderDetails (orderid, productid, productcost) VALUES (@orderid, @productid, @productcost)How do i obtain this value and how would I supply it to the second INSERT statement?
View 9 Replies
View Related
Apr 5, 2008
Hi All: I have what I'm sure is a common scenario...I have a table to track pageviews of a form, and which also tracks when a person viewing the form submits it.
The table has three fields: an INT identity/PK field, a DATETIME (default getdate()) field, and a BIT field with default "false".
When the page is viewed, I insert a record into the dB:
Protected Sub Page_load(ByVal src As Object, ByVal e As EventArgs)
conn = New SqlConnection("Server=myserver;Database=mydb;User ID=user;Password=password;Trusted_Connection=false;")
If Not IsPostBack Then
AddTrack()
End If
End Sub
Sub AddTrack()
Dim myCommand As SqlCommand
Dim insertTrack As String
insertTrack = "Insert PageTracker (submitted) Values (0)"myCommand = New SqlCommand(insertTrack, conn)
myCommand.Connection.Open()
Try
myCommand.ExecuteNonQuery()tempTxt.Text = "<br>Ticked</b><br>" & insertTrack
Catch ex As SqlException
tempTxt.Text = ex.Number.ToString()
End Try
myCommand.Connection.Close()
End Sub
And if I view the page, the record is inserted into the table. But now I need to know the value of the identity field, so when the form is submitted, I can update the field "submitted" from "0" to "1".
The way I would do it in ASP is to add a "SELECT @@identity" to the query, and get the value using RS.nextrecordset. How would I do this in .NET? or is there a better way for me to do this?
View 6 Replies
View Related
Jun 1, 2006
I am a "newbie" and have been struggling with this for days! I have users enter their residence information and insert which generates houseid. I want to use/display that houseid on next page/step. I am VERY FRUSTRATED and would appreciate any assistance!
<script runat="server">
Protected Sub SqlDataSource1_Inserting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles SqlDataSource1.Inserting
e.Command.Parameters("@house").Size = 5
End Sub
Protected Sub SqlDataSource1_Inserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlDataSource1.Inserted
Dim house = e.Command.Parameters("@house").Value
Response.Write(house)
End Sub
Protected Sub Wizard1_FinishButtonClick(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.WizardNavigationEventArgs)
End Sub
Protected Sub SqlDataSource1_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs)
End Sub
</script>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues"
ConnectionString="<%$ ConnectionStrings:ic_registerConnectionString %>"
oninserted="SqlDataSource1_Inserted"
oninserting="SqlDataSource1_Inserting"
DeleteCommand="DELETE FROM [household] WHERE [householdid] = @original_householdid AND [housenum] = @original_housenum AND [streeraddr] = @original_streeraddr AND [aptnum] = @original_aptnum AND [city] = @original_city AND [state] = @original_state AND [zipcode] = @original_zipcode AND [HHPhone] = @original_HHPhone AND [timedate] = @original_timedate"
InsertCommand="INSERT INTO [household] ([housenum], [streeraddr], [aptnum], [city], [state], [zipcode], [HHPhone], [timedate]) VALUES (@housenum, @streeraddr, @aptnum, @city, @state, @zipcode, @HHPhone, { fn NOW() }); SELECT @house = SCOPE_IDENTITY()"
OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT * FROM [household]"
UpdateCommand="UPDATE [household] SET [housenum] = @housenum, [streeraddr] = @streeraddr, [aptnum] = @aptnum, [city] = @city, [state] = @state, [zipcode] = @zipcode, [HHPhone] = @HHPhone, [timedate] = @timedate WHERE [householdid] = @original_householdid AND [housenum] = @original_housenum AND [streeraddr] = @original_streeraddr AND [aptnum] = @original_aptnum AND [city] = @original_city AND [state] = @original_state AND [zipcode] = @original_zipcode AND [HHPhone] = @original_HHPhone AND [timedate] = @original_timedate" OnSelecting="SqlDataSource1_Selecting">
<InsertParameters>
<asp:Parameter Name="housenum" Type="String" />
<asp:Parameter Name="streeraddr" Type="String" />
<asp:Parameter Name="aptnum" Type="String" />
<asp:Parameter Name="city" Type="String" />
<asp:Parameter Name="state" Type="String" />
<asp:Parameter Name="zipcode" Type="String" />
<asp:Parameter Name="HHPhone" Type="String" />
<asp:Parameter Type = "String" Name="house" Direction= "Output"/>
</InsertParameters>
Next question is this easier to do using a Wizard Control and DetailsView on a "step" or using seperate pages and FormView? Or does it matter?
View 2 Replies
View Related