Retrieving ID After INSERT Behind The Scenes - Not Using A Grid View
Jun 3, 2006
Hi there. I looked through many other posts describing scope_identity but I am trying to achieve the same thing from the code behind. i.e. I need to some how call a method to execute the insert command and then return the ID so I can update other tables with this value.
I was going down the road of something like:
addnew as sqldatasource = new sqldatasource
addnew.insertcommand = "Insert into....; def @NewID as scope_identity"
addnew.insert()
The problem is I don't know how to add a output parameter using VB or how to retrieve it.
Any help would be much appreciated, this is doing my head in....
Doug.
View 1 Replies
ADVERTISEMENT
Feb 9, 2007
hi, i have done some testing and its only when i put a grid view or any other type of data viewer on the page, and then connect it to the sql datasource that i get an error
Line 1: Incorrect syntax near ')'.
now i really cant figure out what it is, here is the code i am using
SQL data source code :
asp:SqlDataSource ID="SQLDS_view_one_wish" runat="server" ConnectionString="<%$ ConnectionStrings:wishbank_DBCS %>"
SelectCommand="SELECT [msg], [Date_Time] FROM [tbl_MSG] WHERE (([Activated] = @Activated) AND ([msgID = @msgID]) )ORDER BY [Date_Time] DESC">
<SelectParameters>
<asp:Parameter DefaultValue="Y" Name="Activated" Type="String" />
<asp:SessionParameter Name="msgID" SessionField="sWV" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
session variable code which sends it to this page
protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
{
GridViewRow row = GridView1.SelectedRow;
Session["sWV"] = row.Cells[1].Text;
Response.Redirect("www/viewwf.aspx");
}
if you have an idea please let me know as im stuck!
View 1 Replies
View Related
Jan 28, 2007
Inside my gridview, the user can key in new record, delete record and update record. but dont know why my insert function cant work out and i dunno why this is happen? Can somebody help me out with this?Thanks
My Code:<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"InsertCommand="INSERT INTO [rest_info] ([resname], [menu], [price], [date]) VALUES (@resname, @menu, @date, @price)"<InsertParameters> <asp:Parameter Name="resname" Type="Char" /> <asp:Parameter Name="menu" Type="char" /> <asp:Parameter Name="price" Type="Decimal" /> <asp:Parameter Name="date" Type="datetime" /> </InsertParameters></asp:SqlDataSource>
View 3 Replies
View Related
Jun 21, 2007
i seem to have problems with my grid view not displaying anything when if i test my SQLDataSource, it shows me rows of data.i seem to have this problem only with my Stored procedure, or when i change my Database structure, like add fieldsHere is my procedure: can i do this? select a row and also return a varchar?ALTER PROCEDURE dbo.SelectUpload
(
@FileName varchar(50),
@Return varchar(MAX) = NULL OUTPUT
)
AS
SELECT * FROM Uploads
WHERE FileName = @FileName
RETURN
SELECT WebPath FROM Uploads
WHERE FileName = @FileName
View 3 Replies
View Related
Sep 25, 2006
Hi all,I asked a similar question a few weeks ago but I didn't explain myself to well so I was hoping for some more input.I have created a webpage in Visual web developer that contains a gridview that points to an SQL table.All works well as far as searching the table and sorting. The SQL table itself is Droped ( deleted ) and then re-created every 1 minute from a query to a linked DB2 database so that we have a "live" table of our DB2 system but in SQL.....However,There is now a requirement to be able to ADD data to this SQL table. eg: a comments field.. from the website.My procedure for re-creating the SQL DB is now useless as any data added through the website would be lost when the table gets re-created.What is the best way for me to achieve a permanent table in SQL that gets updated from the Linked server DB2 but that I can also add comments from the front end (Website Gridview)..I'm assuming some sort of update query and a join to a seperate, permanent table that contains the comments?.Please help. Ray..
View 1 Replies
View Related
Feb 27, 2008
hiii, i am using asp.net 2005 and sql server 2005...i hav a page in which i have used gridview and sql data source..i have written the update command for the same..the problem is when i add the where clause in the query i get an error ,,,,here is the code
UpdateCommand="UPDATE SMEtre_Master SET FirstName =@FirstName, LastName =@LastName, Type_of_SME =@Type_of_SME, Agency_Name =@Agency_Name, Email =@Email, Address =@Address, Phone =@Phone, Mobile =@Mobile, Fax =@Fax, Experience =@Experience, City =@City, State =@State where SME_Id=@SME_Id"
View 3 Replies
View Related
Feb 23, 2006
I put a grid view on a web form ,when I run it -the SELECT, EDIT works
the UPDATE,DELETE makes an error although I use the sama data,I added the error :
Anyone can help?
Server Error in '/CrystalReportsWebSite1' Application.
The data types text and nvarchar are incompatible in the equal to operator.
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: The data types text and nvarchar are incompatible in the equal to operator.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
[SqlException (0x80131904): The data types text and nvarchar are incompatible in the equal to operator.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +95
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +82
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +346
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +3244
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +186
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1121
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +334
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +407
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +149
System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +493
System.Web.UI.WebControls.SqlDataSourceView.ExecuteUpdate(IDictionary keys, IDictionary values, IDictionary oldValues) +915
System.Web.UI.DataSourceView.Update(IDictionary keys, IDictionary values, IDictionary oldValues, DataSourceViewOperationCallback callback) +179
System.Web.UI.WebControls.GridView.HandleUpdate(GridViewRow row, Int32 rowIndex, Boolean causesValidation) +1140
View 2 Replies
View Related
Apr 10, 2008
i have one grid view in a web page
then i want to set GRIDVIEW header value according to user input
how can i do this pls help me ....
Yaman
View 2 Replies
View Related
Jul 19, 2013
I want to update a data in grid view
I have three columns in grid view
First two column are shift id and date these two column are taken from table 1
The third column is shift column this column are taken from table 2
Now I want to update a data where shift id and shift column are same
table 2 have these column
shift
shiftid
and table 1 has these column
shift id,
date
View 5 Replies
View Related
Jun 28, 2007
I have gridview bound with SqlDataSource control. One of the grid columns is a dropdown list which is populated programatically, (the gridview has template column with edittemplate field with dropdown list), dropdown Value contains of course collection of IDs.Please tell me how to declare UpdateCommand which will update the row with the dropdown list. The problem is with parameter regarding the column with dropdown list. When i declare this parameter and bind it to SelectedValue property of the dropdownlist from edittemplate tag - it doesnt work, because datasource control cannot see this dropdown list. So, how am i supposed to declare the ID of the row being updated in <updateParameters>? Thanks in advance!
View 4 Replies
View Related
Aug 31, 2007
I have consistent column names that load into a grid view. I now need to change the names in the grid view programatically. I was originally trying to get cute with SQL to do this, but I've been told my below solution will not work and I'm better off to do this in the presentation layer. If this is true, remember I'm still wet behind the ears here...how do I do this in vs2005?
Here's my post to the SQL devs to give you an idea what I'm trying to do.
I have a query that grabs fields from a denormalized table. The result is column names Week1, Week2, Week3....Week26. Users want to see the actual date instead of Week#. So I have a table (lkpdatecaptions ) that contains the fields "fldfieldno" and "Fldcaption". fldfieldno Fldcaption----------- --------------11 9/07/200712 9/14/200713 9/21/2007So fieldno 11 represent week1 and so on. So my hope is to update the alias with a query like: Select fldcaption from forecast.tlkpdatecaptionswhere fldfieldno = 11That quey returns the value of 9/7/2007 and is the value I need to represent the coumn alias name.My current query looks like this:SELECT SUM(forecast.tblforecastdenormalized.fldwk01) AS WEEK1,So can I do something like the following?SELECT SUM(forecast.tblforecastdenormalized.fldwk01) AS (Select forecast.tlkpdatecaptions.fldcaption from forecast.tlkpdatecaptions where fldfieldno = 11), ...
View 6 Replies
View Related
Oct 23, 2015
I am looking for a way to create a stored procedure that will show inventory availability. I would like to show the Inventory Name, The Date, and if the inventory is "checked out" using the ID name of the person who has the item.
For example it would look like this:
--------------------------------------------------------------------------------------------------
Inventory Name | 10/24/2015 | 10/25/2015 | 10/26/2015 | 10/27/2015 | 10/28/2015
--------------------------------------------------------------------------------------------------
Laptop | Tom | Tom | Tom | Avail | Avail
Projector | Avail | Avail | Avail | Avail | Bob
Air Card | Bob | Bob | Bob | Bob | Bob
It seems like I want to do a pivot table but there really is no aggregate so I am not sure what to use.
View 8 Replies
View Related
Oct 27, 2006
hi,i have an sql database on my server on the world wide web.i can make a connection to the database in visual web developer and all the tables etc are shown in the 'database explorer' of visual web developerwhen i make the query in visual web developer it does retrieve the data froom the remote server database when i 'test query'.... so looking great!the connection string in the webconfig file is left as the default when i run the prgram on my LOCAL HOST with the inbult server that comes with visual web developer it runs fine......so.....running on my local pc it will connect to the database on my www host server and does display the data from that database.i need to change the webconfig files path to the connection by default it is |DataDirectory|if i change this to the path i suspect is correct as this is the connection i use to the database C:Inetpubvhostsarcvillage.comhttpdocsApp_DataTestdatabase.mdfwhen i change the path in webconfig then it stops working on my localhost and the error when i ftp it to my host is:Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path. Please make sure the user has a local user profile on the computer. The connection will be closed. any ideas?
View 2 Replies
View Related
Mar 16, 2008
Hello,I have inserted a drop down list in my Edit template of Grid view. This DDL should control the User_ID who is responsible for the Computer being edited. However I want it to display the User Names rather than the user IDs. I have a COMPUTERS and a USERS table. They are related by having User_ID in both. Below is my grid view with the parts i feel relative highlighted in bold:<asp:GridView ID="GridView1" runat="server" AllowSorting="True" AutoGenerateColumns="False" DataKeyNames="Computer_ID" DataSourceID="SqlDataSource1" CellPadding="4" ForeColor="#333333" GridLines="None"> <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /> <RowStyle BackColor="#F7F6F3" ForeColor="#333333" /> <Columns> <asp:CommandField ShowEditButton="True"/> <asp:BoundField DataField="Computer_ID" HeaderText="Computer_ID" InsertVisible="False" SortExpression="Computer_ID" ReadOnly="true" /> <asp:BoundField DataField="Computer_Name" HeaderText="Computer Name" SortExpression="Computer_Name"/> <asp:BoundField DataField="Manufacturer" HeaderText="Manufacturer" SortExpression="Manufacturer"/> <asp:TemplateField HeaderText="User Name" SortExpression="Name"> <EditItemTemplate> <asp:DropDownList ID="DDL_Name" runat="server" DataSourceID="SQLDataSource2" DataValueField="User_ID" SelectedValue='<%# Bind("User_ID") %>'></asp:DropDownList> </EditItemTemplate> <ItemTemplate> <asp:Label ID="lblName1" runat="server" Text='<%# Bind("Name") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> </Columns> <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" /> <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" /> <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /> <EditRowStyle BackColor="#999999" /> <AlternatingRowStyle BackColor="White" ForeColor="#284775" /> </asp:GridView> <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="Data Source=.SQLEXPRESS;AttachDbFilename=C:AjaxControlToolkitWebSite1App_DataAssetDatabase.mdf;Integrated Security=True;User Instance=True" ProviderName="<%$ ConnectionStrings:AssetDatabaseConnectionString.ProviderName%>" SelectCommand="SELECT * FROM USERS" > </asp:SqlDataSource> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="Data Source=.SQLEXPRESS;AttachDbFilename=C:AjaxControlToolkitWebSite1App_DataAssetDatabase.mdf;Integrated Security=True;User Instance=True" ProviderName="<%$ ConnectionStrings:AssetDatabaseConnectionString.ProviderName%>" SelectCommand="SELECT COMPUTERS.Computer_ID, USERS.User_ID, COMPUTERS.Computer_Name, COMPUTERS.Manufacturer, USERS.Name FROM COMPUTERS INNER JOIN USERS ON COMPUTERS.User_ID = USERS.User_ID" UpdateCommandType="StoredProcedure" UpdateCommand="StoredProcedure1" > <UpdateParameters> <asp:Parameter Name="Computer_ID" Type="Int32" /> <asp:Parameter Name="User_ID" Type="Int32"/> <asp:Parameter Name="Name" Type="String" /> <asp:Parameter Name="Computer_Name" Type="String" /> <asp:Parameter Name="Manufacturer" Type="String" /> </UpdateParameters> </asp:SqlDataSource> <br /> <br /> </form></body></html> THANKS =]
View 6 Replies
View Related
Dec 1, 2014
I can create a string into a local "@" variable. It can include a date/time, and text for my timing testing.
I need to be able to send that string to the results window where table output goes, rather than to the message window where a PRINT statement goes.
How do I do that?
View 2 Replies
View Related
May 11, 2007
I have the membership stuff up and running. I've added a field to the membership table called custnmbr. Once a user logs in, I want store his custnbmr in the session and use that to lookup data in another db.
ie: Joe logs in and his custnumbr is 001, he goes to the login success page and sees his list of service calls which is:
select top 10 * from svc00200 where custnmbr = 001 (the membership.custnmbr for the logged in user)
I know how to do this in old ASP using session variables....but I have no idea where to even start with .Net.
Many thanks
View 7 Replies
View Related
May 2, 2007
Hi ! I have a textbox and a Search button. When the user inputs a value and press the button, i want a datagrid to be filled.
The following code runs:
Dim connect As New Data.SqlClient.SqlConnection( _
"Server=SrvnameSQLEXPRESS;Integrated Security=True; UID= ;password= ; database=dtbsname")
connect.Open()
Dim cmd As New SqlCommand
Dim valor As New SqlParameter("@valor", SqlDbType.VarChar, 50)
cmd.CommandText = "Ver_Contactos_Reducido"
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = connect
cmd.Parameters.Add(valor)
cmd.Parameters("@valor").Value = texto
Dim adapter As New SqlDataAdapter(cmd)
Dim ds As New System.Data.DataSet()
adapter.Fill(ds)
GridViewContactos.DataSource = ds
GridViewContactos.DataBind()
connect.Close()
I drag a datagrid on the page and only changed its Id.
Into the SQL database the stored procedure is the following:
ALTER PROCEDURE [dbo].[Ver_Contactos_Reducido]
(@Valor VARCHAR(100))
AS
BEGIN
SET NOCOUNT ON;
SELECT NombreRazonSocial, Nombre, Apellido,TelefonoLaboral,
Interno, TelefonoCelular, Email1, Organizacion
FROM CONTACTOS
WHERE NombreRazonSocial = @Valor OR Nombre = @Valor OR Apellido = @Valor OR TelefonoLaboral = @Valor OR Interno = @Valor OR
TelefonoCelular =@Valor OR Email1 = @Valor OR Organizacion= @Valor
END
When i run the page i can't see the datagrid, and after i enter a text and press the button, nothing happens. What am i doing wrong??
Thks!!
View 2 Replies
View Related
May 25, 2006
Hello, I am running SQL 2000 SP4 on a Windows 2003 standard edition server.
The SQL database gets changed/updated through a web interface. I was wondering if there was any way for me to see which SQL commands were being run on the database when changed like deleting/inserting users are being applied from the Web frontend?
Any help is appreciated, thanks.
Robert
View 1 Replies
View Related
Oct 11, 2007
I've heard that if one doesnt have a clustered index on his/her table, that clustering occurs on some underlying row id. Is this true? If so, is that id retrievable?
View 16 Replies
View Related
Jul 22, 2004
Hi, I have a Stored procedure doind an INSERT which then returns @@Identity.
I have set this parameters direction to output, however, when i run it I get an error saying procedure is expecting this output parameter..
Not sure where I am going wrong...
Can someone please help with retrieving the ID after an insert. What is the correct code in .NET?
Many Thanks
View 3 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
Mar 7, 2006
The query below should be inserting unique records in the PropertyItem table which only has propertyitem, propertyid, and itemid columns, all of which are PK's in other tables. I'm not doing the insert correctly b/c it's inserting 72 records instead of 24. I really just want to automatically insert the values once I've inserted in the other tables but I'm not sure how. Maybe On Update Cascade?
--PROPERTYITEM
INSERT INTO [USCONDEX_Production].[dbo].[propertyItem]( [propertyId], [ItemId])
SELECT Property.propertyId, ITEM.ItemID
FROM ITEM RIGHT OUTER JOIN
miamiherald ON ITEM.StartDate = miamiherald.FirstInsertDate AND ITEM.Price = miamiherald.PropertyPrice AND ITEM.Classified = convert(int,miamiherald.AdNumber) LEFT OUTER JOIN
Property ON property.adprintid = miamiherald.adprintid
WHERE validAD=1
View 1 Replies
View Related
Sep 14, 2006
Can some one offer me some assistance? I'm using a SQLDataSource control to call a stored proc to insert a record into a control. The stored proc I'm calling has an output paramater that returns the new rows identity field to be used later. I'm having trouble getting to this return value via the SQLDataSource . Below is my code (C#): SqlDataSource1.InsertParameters["USR_AUTH_NAME"].DefaultValue = storeNumber;SqlDataSource1.InsertParameters["usr_auth_pwd"].DefaultValue = string.Empty;SqlDataSource1.InsertParameters["mod_usr_name"].DefaultValue = "SYSTEM";SqlDataSource1.InsertParameters["usr_auth_id"].Direction = ParameterDirection.ReturnValue;SqlDataSource1.Insert();int id = int.Parse(SqlDataSource1.InsertParameters["usr_auth_id"].DefaultValue); below is the error I'm getting: System.Data.SqlClient.SqlException: Procedure 'csi_USR_AUTH' expects parameter '@usr_auth_id', which was not supplied. Has anyone done this before and if so how did you do it?
View 1 Replies
View Related
Oct 2, 2007
The following code inserts a record into a table. I now wish to retrieve the IDENTITY of that entry into a variable so that I can use it again as input for other inserts. Can someone offer assistance in handling this.... I tried several alternatives that I found on the internet but none seem to work...
Thanks!
Dim objConn3 As SqlConnectionDim mySettings3 As New NameValueCollectionmySettings3 = AppSettingsDim strConn3 As StringstrConn3 = mySettings3("connString")objConn3 = New SqlConnection(strConn3)Dim strInsertPatient As StringDim cmdInsert As SqlCommandDim strddlSex As StringDim strddlPatientState As StringDim rowsAffected As Integer
strddlSex = ddlSex.SelectedItem.TextstrddlPatientState = ddlPatientState.SelectedItem.TextstrInsertPatient = "Insert ClinicalPatient ( UserID, Accession, FirstName, MI, " & _"LastName, MedRecord, ddlSex, DOB, Address1, Address2, City, Suite, strddlPatientState, " & _"ZIP, HomeTelephone, OutsideNYC, ClinicalImpression, Today_Date_Month, Today_Date_Day, " & _"Today_Date_Year) Values (@UserID, @Accession, @FirstName, @MI, @LastName, @MedRecord, " & _"'" & strddlSex & "', @DOB, @Address1, @Address2, @City, @Suite , '" & strddlPatientState & "', " & _"@ZIP, @HomeTelephone, @OutsideNYC, @ClinicalImpression, @Today_Date_Month, @Today_Date_Day, " & _"@Today_Date_Year)SELECT @@IDENTITY AS NewID SET NOCOUNT OFF"
cmdInsert = New SqlCommand(strInsertPatient, objConn3)
cmdInsert.Parameters.Add("@UserID", "Joe For Now")cmdInsert.Parameters.Add("@Accession", Accession.Text)cmdInsert.Parameters.Add("@LastName", LastName.Text)cmdInsert.Parameters.Add("@MI", MI.Text)cmdInsert.Parameters.Add("@FirstName", FirstName.Text)cmdInsert.Parameters.Add("@MedRecord", MedRecord.Text)cmdInsert.Parameters.Add("@ddlSex", strddlSex)cmdInsert.Parameters.Add("@DOB", DOB.Text)cmdInsert.Parameters.Add("@Address1", Address1.Text)cmdInsert.Parameters.Add("@Address2", Address2.Text)cmdInsert.Parameters.Add("@City", City.Text)cmdInsert.Parameters.Add("@Suite", Suite.Text)cmdInsert.Parameters.Add("@strddlPatientState", strddlPatientState)cmdInsert.Parameters.Add("@ZIP", zip.Text)cmdInsert.Parameters.Add("@HomeTelephone", Phone.Text)cmdInsert.Parameters.Add("@OutsideNYC", OutsideNYC.Text)cmdInsert.Parameters.Add("@ClinicalImpression", ClinicalImpression.Text)cmdInsert.Parameters.Add("@Today_Date_Month", Today_Date_Month.Text)cmdInsert.Parameters.Add("@Today_Date_Day", Today_Date_Day.Text)cmdInsert.Parameters.Add("@Today_Date_Year", Today_Date_Year.Text)
objConn3.Open()cmdInsert.ExecuteNonQuery()objConn3.Close()
View 1 Replies
View Related
Aug 27, 2007
I have tried many code sample but I am very stuck
The primary key of my database (SQL server 2005) table is a uniqueidentifier.
I am using the following code to insert a row into my table:
myCommand.CommandText = sqlEvent.ToString(); //add the sql query to the command
myCommand.Connection = this.dbConnection; //add the database connection to the command
myCommand.ExecuteNonQuery(); //execute the insert query
I need to retrieve the GUID that is automatically generated when the insert command is executed.
Can someone help me? How do I get the GUID that is automatically generated? I have tried lots of things like using
string _id = (string)myCommand.ExecuteScalar();
and I am still stuck. I will really appreciate it if someone can refer me to some code sample.
HELP
View 1 Replies
View Related
Nov 12, 2007
Good day everyone,
I have implemented a simple package that has only a Data Flow Task in the Control Flow. The corresponding Data Flow Components are as follows (in the correct order):
1. A Flat File Source adpater which parses a file containing circa 1 million rows.
2. A Script Component that generates IDs for two of my destination tables.
3. A Mulicast Component that distributes all the columns retrieved from the source file as well as the 2 generated keys.
4. Four OLE DB Destination adapters where I insert the data from the different columns together with the generated keys as primary or foreign keys respectively.
My questions are mainly about how the "Flat File Source" and "OLE DB Destination" work behind the scenes.
Questions:
A. "Flat File Source" adapter:
Does the "Flat File Source" adapter load all the rows from the source file into the memory and then start pushing the rows one by one into the data flow?
My concern is actually about the huge sizes of my import files, which might eat up the memory.
Or does the "Flat File Source" adapter load a certain number of rows, pushes them into the pipeline and then fetches the next batch based on a certain configuration?
B. "OLE DB Destination":
I have set the four OLE DB destinations to "Fast Load" with a commit size of 5,000.
Can I ever run into the problem of having for instance 10,000 records inserted in Table1, but only 5,000 inserted in Table2. The error case I am thinking about is as follows:
After the second commit on Table1, an erroneous record occurs during the insertion in Table2. Thus, the second commit for Table2 gets rolled back and the whole package fails.
In this case, I get an inconsistent state in my database.
In other words, how can I synchronize the commit operation in all my destination tables, s.t. they either all commit the same batch or all do not commit it?
Thank you in advance for your support.
Regards,
Samar
View 5 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 3 Replies
View Related
May 22, 2008
Hi,
I need to write a stored procedure which can insert into a table some records and these records are results of a view.
Can anyone tell me what should I do?
Thanks
View 3 Replies
View Related
Jul 20, 2006
Hi all ,
I have a problem to insert new data to a view which was created from 2 tables.
I am trying to so from run time
Any help will be appreciated
View 1 Replies
View Related
Apr 20, 2004
Is is possible to insert a record through a view. If so, how?
USE Northwind
GO
CREATE TABLE tbForms (
FormID INT IDENTITY (1,1) NOT NULL,
Form varchar (100) NOT NULL
)
GO
ALTER TABLE tbForms
ADD CONSTRAINT tbForms_pk PRIMARY KEY (FormID)
GO
CREATE TABLE tbDoubleTeeForms (
fkFormID INT NOT NULL,
Form varchar(100) NOT NULL,
Width FLOAT,
Height FLOAT,
Flange FLOAT,
Leg FLOAT,
LegCount INT
)
GO
ALTER TABLE tbDoubleTeeForms
ADD CONSTRAINT tbDoubleTeeForms_pk PRIMARY KEY (fkFormID)
GO
ALTER TABLE tbDoubleTeeForms
ADD CONSTRAINT tbDoubleTeeForms_fk FOREIGN KEY (fkFormID)
REFERENCES tbForms (FormID)
GO
CREATE TABLE tbFlatPanelForms (
fkFormID INT NOT NULL,
Form varchar(100) NOT NULL,
Width FLOAT,
HEIGHT FLOAT
)
GO
ALTER TABLE tbFlatPanelForms
ADD CONSTRAINT tbFlatPanelForms_pk PRIMARY KEY (fkFormID)
GO
ALTER TABLE tbFlatPanelForms
ADD CONSTRAINT tbFlatPanelForms_fk FOREIGN KEY (fkFormID)
REFERENCES tbForms (FormID)
GO
CREATE VIEW MyProducts AS
SELECT fkFormID, Form FROM tbDoubleTeeForms UNION ALL
SELECT fkFormID, FOrm FROM tbFlatPanelForms
GO
-- How can I insert a new record, the pk of the forms table is identity.
-- Can this be done?
INSERT INTO MyProducts (Form)
VALUES ('My First Entry')
GO
SELECT * FROM MyProducts
GO
DROP VIEW MyProducts
GO
DROP TABLE tbFlatPanelForms
GO
DROP TABLE tbDoubleTeeForms
GO
DROP TABLE tbForms
GO
Mike B
View 13 Replies
View Related
Apr 12, 2007
I have tried just about everything i could find on the internet to fix this.
Anyway.....I have a sql databasewith 3 colums: Client ID, Client Name, and Client Address
I have no trouble reading the database through the gridview, but when i try to add a script that allows me to add to that database off of my site, I get error after error.
Anyone who can figure this out must be a sql genius (or I am a complete idoit).
Here is the code I have so far:
<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" %>
<asp:Content ID="Content1" runat="server" ContentPlaceHolderID="ContentPlaceHolder1">
<script runat="server">
Dim strClient_Name As String
Dim strClient_Address As String
</script>
<asp:DetailsView ID="DetailsView1" runat="server" AutoGenerateRows="False" DataKeyNames="Client ID"
DataSourceID="SqlDataSource1" Height="50px" Width="125px">
<Fields>
<asp:BoundField DataField="Client Name" HeaderText="Client Name" SortExpression="Client Name" />
<asp:BoundField DataField="Client Address" HeaderText="Client Address" SortExpression="Client Address" />
<asp:BoundField DataField="Client ID" HeaderText="Client ID" InsertVisible="False"
ReadOnly="True" SortExpression="Client ID" />
<asp:CommandField ShowInsertButton="True" />
</Fields>
</asp:DetailsView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:clientInfoConnectionString %>"
InsertCommand="INSERT INTO [Clients] ([Client Name], [Client Address]) VALUES (strClient_Name, strClient_Address)"
SelectCommand="SELECT [Client Name], [Client Address], [Client ID] FROM Clients">
</asp:SqlDataSource>
</asp:Content>
Best Regards and Thank You,
The King
View 6 Replies
View Related
Jun 2, 2006
Hi to all
I have a View of 3 tables I would like to be able to update insert
data into Views table that in return do the same in the original tables is that possible?
Thank you
View 1 Replies
View Related
May 7, 2008
Hi Folks,
I have been trying to setup distributed views. On two machines I was successful. These machines do not have any domain configured. I was running SQL 2000 Enterprise on both machines with Win2K.
The steps I followed were:
1) Add linked servers
EXEC master.dbo.sp_addlinkedserver @server = 'FED2', @srvproduct = 'SQLServer OLEDB Provider',@provider='SQLOLEDB', @datasrc='WEBSERVER1', @catalog='Federated_Bridge'
2) Add linked servers login
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname='FED2',@useself='False',@locallogin=NULL,@rmtuser='sa',@rmtpassword='mysapwd'
3) Create table
create table Entity_2(id int not null ,EntityID int not null)
go
4) Add Table constraints
ALTER TABLE [dbo].Entity_2 ADD
CONSTRAINT [CK_11] CHECK ([EntityID] <= 10),
CONSTRAINT [PK_Entity_2] PRIMARY KEY CLUSTERED (id,EntityID )
GO
5) Set Xact Abort on
SET XACT_ABORT ON
GO
..The above steps were repeated on two machines. Step 4 was changed so the check constraint did not overlap.
The steps worked. But now I am trying to do the same thing on two different machines. Both are running Win2K and SQL 2000. The only difference that I can see in the setup is that these new machines are setup under a single domain - ie there is a domain controller - whereas in the first case there is no domain controller.
The error message I get is:
Server: Msg 7391, Level 16, State 1, Line 1
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].
Please help.
View 3 Replies
View Related
