How WCF Can Get New Inserted SQL Data ?
dear all,
I have a WCF service which is host in a console application for the time beeing.
This service provide methods for retriving history data when request.
So far so good.
My WCF service need also to know when a particular table (ALARMS tabel ) gets updated with DELETE, INSERT, OR UPDATE. This because my client application (WinForm) need to refresh a datagrid binding to that ALARMS table.
In other words my WCF servcie would send a callback event to my client when it as been notice for a change..
But my problem is how my WCF service can be notify from an update in my SQL table ?
I have tried SQLDependency class, but I give up was not working properly...and hard to know the xact context you are runing on.
Was thinking also of having a timer whcih pool every 1s by calling my tabel store procedure and verifiy is somerow ha changed....
What to do, how to do, what is the best way and thred safe method
Thnaks for help and advise
regards
serge
View Complete Forum Thread with Replies
Related Forum Messages:
Monitoring Inserted Data And Comparing Against Selected Data
I made ahuge load script in SQL Server 2000 as i load data from manytables(select some of each one collumns) into one single table and iwant to test the loaded data against the selected data to make surethat the loaded data is the same the selected datais there a code or tool to make this test or monitoring ?? pleaseurgent ....
View Replies !
Getting Inserted Data In Trigger
I am using SQL Server 2000.I want to create an after insert trigger on one of my tables, but I have forgotten how I reference the inserted data to do some business logic on it. Can someone please help. Thanks Jag
View Replies !
INSERT INTO - Data Is Not Inserted
hi thereCreated sproc - it stops dead in the first lineWhy ????Thanks in advanceCREATE PROCEDURE [dbo].[test] ASinsert into timesheet.dbo.table1 (RE_Code, PR_Code, AC_Code, WE_Date,SAT, SUN, MON, TUE, WED, THU, FRI, NOTES, GENERAL, PO_Number,WWL_Number, CN_Number)SELECT RE_Code, PR_Code, AC_Code, WE_Date, SAT, SUN, MON, TUE,WED, THU, FRI, NOTES, GENERAL, PO_Number, WWL_Number, CN_NumberFROM dbo.WWL_TimeSheetsWHERE (RE_Code = 'akram.i') AND (WE_Date = CONVERT(DATETIME,'1999-12-03 00:00:00', 102))GO
View Replies !
Access Inserted Data
i have a oledb destination in my data flow pointing to table ABC and an error output if the insert failed..follow the error output, i have a lookup on table ABC which doesn't seem to work..is it possible to access new data in table ABC follow the error output? thanks
View Replies !
Prevent Data Being Inserted Twice
I have a table with 3 columns: ID, Status, DateTime. I created a stored procedure to insert a staus value for each ID. This will run every hour. The DateTime stores the time, date when the Status was inserted. If the procedure was to be run a second time in hour window I do not want any Status to be inserted. Note: that I cannot rely on the procedure being run at exactly the right time - if it was scheduled to run on the hour (i.e at 1:00, 2:00, 3 :00 etc) but didn't run until 1:20 it sould still be able to run at 2:00. Does anyone know if there is anyway I can gaurd against this?
View Replies !
How To Get The ID Of An Inserted Data In A Stored Procedure
hi iam working for a stored procedure where i am inserting data for a table in a database and after inserting i must get the ID in the same procedure.later i want to insert that output ID into another table inthe same stored procedure. for example: alter procedure [dbo].[AddDetails] ( @IndustryName nvarchar(50), @CompanyName nvarchar(50), @PlantName nvarchar(50), @Address nvarchar(100), @Createdby int, @CreatedOn datetime ) as begin insert into Industry(Industry_Name,Creadted_by,Creadted_On) OUTPUT inserted.Ind_ID_PK values(@IndustryName,@Createdby,@CreatedOn) insert into Company(Company_Name,Company_Address,Created_by,Created_On,Ind_ID_FK) OUTPUT inserted.Cmp_ID_PK values(@CompanyName,@Address,@Createdby,@CreatedOn) insert into Plant(Plant_Name,Created_by,Creadted_On,Ind_ID_FK,Cmp_ID_FK)values(@PlantName,@Createdby,@CreatedOn,@intReturnValueInd,@intReturnValueComp) end Here iam getting the output ID of the inserted data as OUTPUT inserted.Ind_ID_PK and later i want to insert this to the company table into Ind_ID_FK field.how can i do this. Please help me, i need the solution soon.
View Replies !
Converting Data To Be Inserted Into A Database
Hi,I am using web matrix, and I am trying to insert a data into a MSDE database. I have used webmatrix to generate the update code, and it is executed when a button is pressed on the web page. but when the code is executed I get the error:Syntax error converting the varchar value 'txtAmountSold.text' to a column of data type int.So I added the following code to try to convert the data, but i am still getting the same error, with txtAmountSold.text replaced with "test"dim test as integer test = Convert.ToInt32(txtAmountSold.text)Here is the whole of the function I am using:Function AddItemToStock() As Integer dim test as integer test = Convert.ToInt32(txtAmountSold.text) Dim connectionString As String = "server='(local)Matrix'; trusted_connection=true; database='HawkinsComputers'" Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString) Dim queryString As String = "INSERT INTO [stock] ([Catagory], [Type], [Name], [Manufacturer], [Price], [Weight"& _ "], [Description], [image], [OnOffer], [OfferPr"& _ "ice], [OfferDescription], [AmountInStock], [AmountOnOrder], [AmountSold]) VALUES ('CatList.SelectedItem.text', 'txtType.text', 'txtname.text', 'txtmanufacturer.text'"& _ ", convert(money,'txtPrice.text'), 'txtWeight.text', 'txtDescription.text', 'txtimage.text', 'txtOnOffer"& _ ".text', convert(money,'txtOfferPrice.text'), 'txtOfferDescrip"& _ "tion.text', 'txtAmountInStock.text', 'txtAmountOnOrder.text', 'test')" Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand dbCommand.CommandText = queryString dbCommand.Connection = dbConnection Dim rowsAffected As Integer = 0 dbConnection.Open Try rowsAffected = dbCommand.ExecuteNonQuery Finally dbConnection.Close End Try Return rowsAffected End FunctionAny help in solving this problem would be greatly appreciated, as I am really stuck for where to go next.
View Replies !
Check Inserted Data In A SQL Database
Hi im having problems as im new to ASP.NET C# i have created a button to add details into a SQL database but i want to check the details before i insert the new values from the textboxes can anyone help....... this is what i have to insert into the database........i just want some help to compare the user name eg... if user name exists a message will appear telling the user to change a different user name Thanks private void Button1_Click(object sender, System.EventArgs e) { string connectionString = "server='(local)'; trusted_connection=true; database='tester'"; //System.Data.IDbConnection conn = new System.Data.SqlClient.SqlConnection(connectionString); System.Data.IDbConnection conn = new System.Data.SqlClient.SqlConnection(connectionString); conn.Open(); string commandString = "INSERT INTO Users (UserName, Password) " + "Values(@UserName, @Password)"; //SqlCommand dbCommand = new SqlCommand (commandString, dbconn); System.Data.IDbCommand dbCommand = new System.Data.SqlClient.SqlCommand(); //System.Data.SqlClient.SqlCommand myCmd = new System.Data.SqlClient.SqlCommand(queryString, conn); dbCommand.CommandText = commandString; dbCommand.Connection = conn; SqlParameter unParam = new SqlParameter ("@UserName", SqlDbType.NVarChar, 60); unParam.Value = txtUser.Text; dbCommand.Parameters.Add(unParam); SqlParameter paParam = new SqlParameter ("@Password", SqlDbType.NVarChar, 60); paParam.Value = txtPassword.Text; dbCommand.Parameters.Add(paParam); dbCommand.ExecuteNonQuery(); conn.Close(); Response.Redirect ("WebForm1.aspx"); }
View Replies !
Clipping Data Inserted Into NText Field
I'm having a major problem. I'm executing an sql statement to insert data into an nText field. It's clipping the text though. I haven't seen any patterns yet. Example "Welcome to the show.", it would save "Welcome to t". And it's not all the time. Please Help. Thanks for your time, Randy
View Replies !
Incorrect Data Is Inserted Into The SQL Table From OLEDB Command
I have an OLEDB command in a package that inserts the data into two tables. When I run the package, the data is getting inserted as divided by 100 of original data for derived columns. For example: Say col1 is my input column from flat file with value 1000. I am dividing it by 100 in Derived column and then inserting into the table. So the value 100 should be inserted into the table. But it is not so, the value 1 is getting inserted. Two Tables involved here have referential integrity constraints between them. SQL Script for the operation I am doing looks like the one just below INSERT INTO PrimaryKeyTable(ID, FirstName) VALUES (?,?) If @@rowcount=1 BEGIN DECLARE @MaxID as int SELECT @MaxID =max(AutoID) FROM dbo.PrimaryKeyTable --AutoIncremented column INSERT INTO [ForeignKeyTable] ( [MaxID] ,[Cost] ,[MarkDownDollars] ,[VersionCode] ) VALUES(@MaxID,?,?,'act') END But this script did not work in OLEDB commandL So I wrote the below script for which I am facing the problem mentioned INSERT INTO PrimaryKeyTable(ID, FirstName) VALUES (?,?) If @@rowcount=1 BEGIN DECLARE @Cost AS money SET @Cost=? DECLARE @MarkDownDollars AS money SET @MarkDownDollars=? DECLARE @MaxID as int SELECT @MaxID =max(AutoID) FROM dbo.PrimaryKeyTable INSERT INTO [ForeignKeyTable] ( [MaxID] ,[Cost] ,[MarkDownDollars] ,[VersionCode] ) VALUES(@MaxID,@Cost,@MarkDownDollars,'act') END
View Replies !
Using Inserted / Deleted Tables With Text / NText / Image Data Type
Hi folks, Table: a int, b int, c int, d text I need to change my AFTER - Trigger from this (example!): select * into #ins from inserted to something like select *(without Text / nText / image -columns) into #ins from inserted. So I tried to build a string like this: (using INFORMATIONSCHEMES) select @sql = 'select a,b,c into #ins from inserted' exec(@sql) a,b,c are not of Text, nText or Image datatype. After executing the trigger, I get an error, that inserted is unknown. Does anyone know how to solve this ? Thx.
View Replies !
INSERT INTO - Data Is Not Inserted - Using #temp Table To Populate Actual Table
Hi thereApplication : Access v2K/SQL 2KJest : Using sproc to append records into SQL tableJest sproc :1.Can have more than 1 record - so using ';' to separate each linefrom each other.2.Example of data'HARLEY.I',03004,'A000-AA00',2003-08-29,0,0,7.5,7.5,7.5,7.5,7.0,'Notes','General',1,2,3 ;'HARLEY.I',03004,'A000-AA00',2003-08-29,0,0,7.5,7.5,7.5,7.5,7.0,'Notes','General',1,2,3 ;3.Problem - gets to lineBEGIN TRAN <---------- skipsrestINSERT INTO timesheet.dbo.table14.Checked permissions for table + sproc - okWhat am I doing wrong ?Any comments most helpful......CREATE PROCEDURE [dbo].[procTimesheetInsert_Testing](@TimesheetDetails varchar(5000) = NULL,@RetCode int = NULL OUTPUT,@RetMsg varchar(100) = NULL OUTPUT,@TimesheetID int = NULL OUTPUT)WITH RECOMPILEASSET NOCOUNT ONDECLARE @SQLBase varchar(8000), @SQLBase1 varchar(8000)DECLARE @SQLComplete varchar(8000) ,@SQLComplete1 varchar(8000)DECLARE @TimesheetCount int, @TimesheetCount1 intDECLARE @TS_LastEdit smalldatetimeDECLARE @Last_Editby smalldatetimeDECLARE @User_Confirm bitDECLARE @User_Confirm_Date smalldatetimeDECLARE @DetailCount intDECLARE @Error int/* Validate input parameters. Assume success. */SELECT @RetCode = 1, @RetMsg = ''IF @TimesheetDetails IS NULLSELECT @RetCode = 0,@RetMsg = @RetMsg +'Timesheet line item(s) required.' + CHAR(13) + CHAR(10)/* Create a temp table parse out each Timesheet detail from inputparameter string,count number of detail records and create SQL statement toinsert detail records into the temp table. */CREATE TABLE #tmpTimesheetDetails(RE_Code varchar(50),PR_Code varchar(50),AC_Code varchar(50),WE_Date smalldatetime,SAT REAL DEFAULT 0,SUN REAL DEFAULT 0,MON REAL DEFAULT 0,TUE REAL DEFAULT 0,WED REAL DEFAULT 0,THU REAL DEFAULT 0,FRI REAL DEFAULT 0,Notes varchar(255),General varchar(50),PO_Number REAL,WWL_Number REAL,CN_Number REAL)SELECT @SQLBase ='INSERT INTO#tmpTimesheetDetails(RE_Code,PR_Code,AC_Code,WE_Da te,SAT,SUN,MON,TUE,WED,THU,FRI,Notes,General,PO_Nu mber,WWL_Number,CN_Number)VALUES ( 'SELECT @TimesheetCount=0WHILE LEN( @TimesheetDetails) > 1BEGINSELECT @SQLComplete = @SQLBase + LEFT( @TimesheetDetails,Charindex(';', @TimesheetDetails) -1) + ')'EXEC(@SQLComplete)SELECT @TimesheetCount = @TimesheetCount + 1SELECT @TimesheetDetails = RIGHT( @TimesheetDetails, Len(@TimesheetDetails)-Charindex(';', @TimesheetDetails))ENDIF (SELECT Count(*) FROM #tmpTimesheetDetails) <> @TimesheetCountSELECT @RetCode = 0, @RetMsg = @RetMsg + 'Timesheet Detailscouldn''t be saved.' + CHAR(13) + CHAR(10)-- If validation failed, exit procIF @RetCode = 0RETURN-- If validation ok, continueSELECT @RetMsg = @RetMsg + 'Timesheet Details ok.' + CHAR(13) +CHAR(10)/* RETURN*/-- Start transaction by inserting into Timesheet tableBEGIN TRANINSERT INTO timesheet.dbo.table1select RE_Code,PR_Code,AC_Code,WE_Date,SAT,SUN,MON,TUE,WE D,THU,FRI,Notes,General,PO_Number,WWL_Number,CN_Nu mberFROM #tmpTimesheetDetails-- Check if insert succeeded. If so, get ID.IF @@ROWCOUNT = 1SELECT @TimesheetID = @@IDENTITYELSESELECT @TimesheetID = 0,@RetCode = 0,@RetMsg = 'Insertion of new Timesheet failed.'-- If order is not inserted, rollback and exitIF @RetCode = 0BEGINROLLBACK TRAN-- RETURNEND--RETURNSELECT @Error =@@errorprint ''print "The value of @error is " + convert (varchar, @error)returnGO
View Replies !
Orw Is Not Inserted
while executing this command locally, ita working fine Insert into tblorderDetails (OrderID,ProductID,SofaPackageID,Quantity,UnitPrice,TotalOrd,ItemStatus ) values(1, 3915, 0, 1, 2049.00, 2049.00, 'PO') but when executing online, giving me following msg: (1 row(s) affected) Msg 207, Level 16, State 1, Line 1 Invalid column name 'Location16FreeStock'. and also data is not inserted in table.
View Replies !
Nothing Is Being Inserted
I will paste my code below. Â I inserted a breakpoint but nothing is being sent to the database and nothing came up when I ran it with the breakpoint. Â Can anyone tell me how to fix this?Protected Sub btn_addfriend_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btn_addfriend.Click Dim add_friend_source As New SqlDataSource add_friend_source.InsertCommand = "INSERT INTO [Friends] ([UserName], [UserID], [IP], [AddedOn], [FriendName]) VALUES (@UserName, @UserID, @IP, @AddedOn, @FriendName)" detailsview_addfriend.DataSource = add_friend_source add_friend_source.DataBind() add_friend_complete.Text = "Success!" End SubThe "Success!" text is the only thing that seems to work properly...
View Replies !
Why Are "&" Inserted As "_"
I have a program to insert rows into a SQL Server table from an ASCII file. There appears to be some data mapping going on. For example: "J & J Smith" is imported as "J _J Smith". This doesn't happen on all servers. I can run it against my database and get the desired results. I looked into the Server Configuation. Under the Security tab there are Mapping options. It appears that you can map _ to something else, but I don't see where you can map & to anything.
View Replies !
Getting Last Inserted Value
hi, this is my spc: select top 1 u.userid, u.user_name, u.password, c.code_description as role_code, u.expiry_date, u.created_date, u.active, convert(varchar,v. user_date, 103) + ' ' + right('0' + stuff(right(convert(varchar,v.user_date, 109), 14), 8, 4, ''), 10) as user_date, v.operation,h.user_name as updatedby from [usermaster] u inner join [codeMaster] c on 'sp'=c.code inner join [HRUser_developerlog] v on u.userid=v.inserted_id and v.operation='update' inner join [usermaster] h on v.userid=h.userid where u.userid = '3' order by v. user_date if i use it gives the v.user_date as fist modified date it is not giving last modified date. select * from HRUser_developerlog user_date operat userid ion 2007-01-25 14:28:17.000insert1 2007-01-24 13:02:18.093insert4 2007-03-03 11:30:29.310update2 2007-03-03 11:30:55.373insert3 2007-03-03 11:31:31.717insert26 2007-01-25 14:28:17.000insert3 2007-03-03 11:43:39.733update26 2007-03-03 11:48:04.543delete3 2007-03-03 14:26:22.420update3 2007-03-03 14:27:00.280update3 2007-03-03 14:27:12.013update2 2007-03-03 14:27:35.763update1 2007-02-08 14:28:17.030update2 2007-03-03 14:27:55.967update3 2007-03-03 14:29:18.827update3 2007-03-03 14:30:52.983update3 so it has to show 2007-03-03 14:30:52.983 but it shows the first updated id only 2007-03-03 14:26:22.420 please help me to get my need
View Replies !
How Return Inserted ID
Hi! I would like to get the last inserted ID in a sql 2005 table to a variable.My code is: SqlDataSource ds = new SqlDataSource();ds.ConnectionString = ConfigurationManager.ConnectionStrings["ConnString"].ToString();ds.InsertCommandType = SqlDataSourceCommandType.Text;ds.InsertCommand = "INSERT INTO Products(SellerID, Price) OUTPUT INSERTED.ProductID VALUES(@SellerID,@Price)"; ds.InsertParameters.Add("SellerID", "Sony"); ds.InsertParameters.Add("Price", "123");string output = ds.Insert().ToString(); // it's return rowsAffected but I need productID (SCOPE_IDENTITY or @@IDENTITY )Thank you for your time and help :)
View Replies !
Need To Get The ID Of The Last Record That Was Inserted
I've seen a lot of info on how to do this with @@IDENTITY and SCOPE_IDENTITY(), but can't get this to work in my situation. I am inserting a record into a table. The first field is a GUID (UNIQUEIDENTIFIER) that uses newid() to generate a unique GUID. Since I am not using an int, I can't set the IsIdentity property of the field. Without IsIdentity set, @@IDENTITY and SCOPE_IDENTITY() do not work. How can I get the ID (or whole record) of the last record that I inserted into a SQL database? Note that I am doing this in C#. As a last resort, I could generatate the GUID myself before the insert, but I can't find C# code on how to to this.
View Replies !
How Do I Retreive Id Of Just Inserted Row?
I'm creating a web application that has user input on 3 seperate pages. Each page prompts the user for specific information and at the bottom of the page the user will click on the "Submit" button to post the info from the form to the database table. Once the user has submitted the first page of information how do I retrieve the ID from the CustID column of that row so I can use the Update function to add additional info to that row when the user clicks on the submit button on page 2 & 3. I don't want to hold all the information in variables until the end in case they bail out of the form. TIA Steve
View Replies !
Getting The Id Of The Row That I Inserted With SQL 2005
Hi,I use a Stored Procedure who works very well....INSERT INTO Computers (CategoryID, SubCategoryID, ......VALUES (@CategoryID, @SubCategoryID, .........But as soon as it creates the new row, i want to be able to get the Id (ComputerId) of this row. I use ComputerId as the primary key.How can i do that? I code in VB.Thanks
View Replies !
Return Inserted Row
how do i insert a record, then return the inserted record back to VS (ASP + VB) to display? maybe just the ID will be enough. then i will do a select
View Replies !
DataSet - Inserted Row ID
I have a dataset that uses generated stored procedures to do its select, insert, update, delete. I am inserting a row to that dataset, and after the update, using the ID of newly created row. This worked just fine until I added triggers to some of the tables on my DB, and now, when I insert a row, the row's ID is not available after the update (it's 0) Any idea what happened / what I have to do to fix this? Thnx!
View Replies !
Return ID Of The Last Inserted Row
Hello, I want to return the ID of the last inserted row. I am using an ObjectDataSource and a FormView to perform the insert operation. Here is my presentation code: <asp:ObjectDataSource ID="odsOrders" runat="server" DataObjectTypeName="Auction.Info"InsertMethod="InsertOrder" SelectMethod="GetOrders" OnInserted="ReturnNewOrderID" OldValuesParameterFormatString="original_{0}" TypeName="Auction.Controller"> <SelectParameters> <asp:Parameter DefaultValue="00" Name="ModuleId" Type="Int32" /> </SelectParameters></asp:ObjectDataSource> <asp:FormView ID="fvAddOrder" runat="server" DataSourceID="odsOrders" DefaultMode="Insert" HorizontalAlign="Center" Width="100%"> <InsertItemTemplate>.... and my VB code: Protected Sub ReturnNewOrderId(ByVal sender As Object, ByVal e As ObjectDataSourceStatusEventArgs) Response.Write(e.ReturnValue) Response.Write("Test") End Sub and my Stored Procedure: ALTER PROCEDURE [dbo].[Auction_InsertOrder] (@ModuleID int,@FirstName nvarchar(50),@LastName nvarchar(50),@Email nvarchar(250),@Phone nvarchar(20),@Fax nvarchar(20),@DelAddress1 nvarchar(250),@DelAddress2 nvarchar(250),@DelTown nvarchar(50),@DelPostCode nvarchar(20),@DelCountry nvarchar(50),@InvAddress1 nvarchar(250),@InvAddress2 nvarchar(250),@InvTown nvarchar(50),@InvPostCode nvarchar(20),@InvCountry nvarchar(50),@AuctionName nvarchar(50),@Quantity int,@Price decimal(18,2),@PostCosts decimal(18,2))ASINSERT INTO Auction_Orders(ModuleID, FirstName, LastName, Email, Phone, Fax, DelAddress1, DelAddress2, DelTown, DelPostCode, DelCountry, InvAddress1, InvAddress2, InvTown, InvPostCode, InvCountry, AuctionName, Quantity, Price, PostCosts, DateEntered)VALUES (@ModuleID, @FirstName, @LastName, @Email, @Phone, @Fax, @DelAddress1, @DelAddress2, @DelTown, @DelPostCode, @DelCountry, @InvAddress1, @InvAddress2, @InvTown, @InvPostCode, @InvCountry, @AuctionName, @Quantity, @Price, @PostCosts, getdate())RETURN SELECT @@IDENTITY As NewOrderID I can insert the record into the database but the e.ReturnValue does not return anything. Thank you for your help/ Many thanks, Vincent
View Replies !
How Do I Get The Uniqueidentifier Of Just Inserted Row?
Hello there! it was a while since i studied SQL and that brings us to my problem... I'm creating a Stored Procedure wich first insert information in a table. That table has a uniqueidentifier fild that is default-set to newid(). later in the SP i need that uniqueidentifier value? how do I get it? I tried this: CREATE PROCEDURE spInsertNews @uidArticleId uniqueidentifier = newid, @strHeader nvarchar(300), @strAbstract nvarchar(600), @strText nvarchar(4000), @dtDate datetime, @dtDateStart datetime, @dtDateStop datetime, @strAuthor nvarchar(200), @strAuthorEmail nvarchar(200), @strKeywords nvarchar(400), @strCategoryName nvarchar(200) = 'nyhet' AS INSERT INTO tblArticles VALUES( @uidArticleId,@strHeader,@strAbstract,@strText,@dt Date,@dtDateStart,@dtDateStop,@strAuthor,@strAutho rEmail,@strKeywords) declare @uidCategoryId uniqueidentifier EXEC spGetCategoryId @strCategoryName, @uidCategoryId OUTPUT INSERT INTO tblArticleCategory(uidArticleId, uidCategoryId) VALUES(@uidArticleId, @uidCategoryId) But i get an error when I EXEC the SP like this: EXEC spInsertNews @strHeader = 'Detta är den andra nyheten', @strAbstract = 'dn första insatt med sp:n', @strText = 'här kommer hela nyhetstexten att stå. Här får det plats 2000 tecken, dvs fler än vad jag orkar skriva nu...', @dtDate = '2003-01-01', @dtDateStart = '2003-01-01', @dtDateStop = '2004-01-01', @strAuthor = 'David N', @strAuthorEmail = 'david@davi.com', @strKeywords = 'nyhet, blajblaj, blaj' the errormessage is: Syntax error converting from a character string to uniqueidentifier. does anyone have a sulution to this problem? Can I use something similar to the @@IDENTITY? I will be greatful for any ideas... thanks /David, Sweden
View Replies !
Getting An Int Id From An Inserted Record
Hello,I would like do an insert into a table. The table has an autoincrimenting unique int id. After I do the insert how do i get theunique int id of the record that I just inserted? Is there a straightforward way of accomplishing this?Thanks,Billy
View Replies !
How Do I Get The Uniqueidentifier Of Just Inserted Row?
Hello there! it was a while since i studied SQL and that brings us to my problem... I'm creating a Stored Procedure wich first insert information in a table. That table has a uniqueidentifier fild that is default-set to newid(). later in the SP i need that uniqueidentifier value? how do I get it? I tried this: CREATE PROCEDURE spInsertNews @uidArticleId uniqueidentifier = newid, @strHeader nvarchar(300), @strAbstract nvarchar(600), @strText nvarchar(4000), @dtDate datetime, @dtDateStart datetime, @dtDateStop datetime, @strAuthor nvarchar(200), @strAuthorEmail nvarchar(200), @strKeywords nvarchar(400), @strCategoryName nvarchar(200) = 'nyhet' AS INSERT INTO tblArticles VALUES( @uidArticleId,@strHeader,@strAbstract,@strText,@dt Date,@dtDateStart,@dtDateStop,@strAuthor,@strAutho rEmail,@strKeywords) declare @uidCategoryId uniqueidentifier EXEC spGetCategoryId @strCategoryName, @uidCategoryId OUTPUT INSERT INTO tblArticleCategory(uidArticleId, uidCategoryId) VALUES(@uidArticleId, @uidCategoryId) But i get an error when I EXEC the SP like this: EXEC spInsertNews @strHeader = 'Detta är den andra nyheten', @strAbstract = 'dn första insatt med sp:n', @strText = 'här kommer hela nyhetstexten att stå. Här får det plats 2000 tecken, dvs fler än vad jag orkar skriva nu...', @dtDate = '2003-01-01', @dtDateStart = '2003-01-01', @dtDateStop = '2004-01-01', @strAuthor = 'David N', @strAuthorEmail = 'david@davi.com', @strKeywords = 'nyhet, blajblaj, blaj' the errormessage is: Syntax error converting from a character string to uniqueidentifier. does anyone have a sulution to this problem? Can I use something similar to the @@IDENTITY? I will be greatful for any ideas... thanks /David, Sweden
View Replies !
Find What I Just Inserted
Hey! I need to find the record_id of the record I just inserted so that I can display the newly created record id. Right now it looks like this: (I'm using Cold Fusion to insert and retrieve records, but I think this is an SQL question) INSERT INTO table (value1, value2, value3) values (value1, value, value3) Select record_id FROM table WHERE value1 = value1 AND value2 = value2 AND value3 = value3 The problem is when someone puts in the exact same data for values 1 through 3 I get back only the first record_id, not the last one. Maybe that's the trick right there, add an ORDER BY field_id DESC?? I thought MySQL had a way to grab the last inserted record, or return the record_id while I'm still in the insert statement, that would be preferable. -Matt
View Replies !
Inserted Table ?
Trying to get my head around this table: I thought it contained all data used in an insert; meaning if i enter a row in a table the inserted table should then contain all data entered? Ive created multiple tables linked via primary key Learner_ID this key is unique to any/all students and can never be duplicated hence i thought i would use it to link the tables. I have tables Fab / Main / Elec / Learner_info / WS_WR. For Fab / Main / Elec the only data i need form Learner_info is the Learner_ID field, i simply used: INSERT INTO FAB(Leaner_ID) SELECT Learner_ID FROM Inserted as a trigger to insert the ID into these tables. Everything works perfectly fine. So i assumed that i would be able to use something very similar to insert say: Learner_ID, FNAME, LNAME, ONAME and C_NUM from the learner_info table into the WR_WS table. Apparently i was wrong I can insert the learner_ID perfectly but if i then try to add another insert trigger eg: insert fname i get an sql error: Cannot insert the value NULL into the column 'LEARNER_ID', table 'swdt_im.WS_WR'; column does not allow nulls. INSERT fails. Now from reading the error it suggests to me that because the second trigger is running my learner_id value has become a NULL. Obviously a primary key wont accept this value so the statement fails. What im struggling to understand, im sure i will solve the problem eventually but i would apreciate help, and this is most important to me rather than a solution is this: If i enter data into a row on a table does that data go into the inserted table in the same row? or does the data go into inserted as a single entry row, meaning when i tab along from learner_ID into FNAME does Learner_ID become row 1 (in inserted) and FNAME the current row? or do they go into the appropriate columns? I was under the assumption that i could enter: LEARNER_ID / FNAME / LNAME / ONAME / C_NUM into one table and have a trigger insert those values into multiple other tables. Dont suppose someone can help me out im alright querying a SQL db but ive never had to develop anything other than a single table one myself before Thanks in advance
View Replies !
From Inserted Question
I have created an updatabable view. It uses the INSTEAD OF INSERT clause to fire because there are multiple base tables. lets say the view has 6 fields field1,field2, field3, field4, field5, field6 to get the values from the inserted record I have to declare the variables first declare field1 varchar(50),field2 varchar(50), field3 varchar(50), field4 varchar(50), field5 varchar(50), field6 varchar(50) I then have to assign the information to these variables select @field1 = field1,@field2 = field2,@field3 = field3, @field4 = field4,@field5 = field5,@field6 = field6 from inserted I can then use these values to execute store procedures exec sp1 @field1, @field2, @field3 exec sp2 @field4, @field5, @field6 is there anyway to just do this exec sp1 inserted.field1, inserted.field2, inserted.field3 exec sp2 inserted.field4, inserted.field5, inserted.field6 I tried exec spec select field1, field2, field3 from inserted and it did not give me an error message BUT it did not work I would like to use my stored procedures for data insertion I appreciate your help
View Replies !
Return The ID Of A Newly Inserted Row.
I am using Visual Web Developer 2005 Express Edition, ASP.NET 2.0 and SQL Server 2005 Express Edition.I'm using a DetailsView control with the default set to "Insert". (DetailsView1)I'm using a table adapter for the datasource. (ordersDS) I have a table with the first column as an identity integer. When using the DetailsView control (default is set to insert) and I click the insert button to insert a new record, how can I get the identity integers value of the newly inserted record?? Thank you in advance
View Replies !
Get Primary Key Of Last Inserted Record
Ok I know this might not be the most accurate place to post this but I know someone here has an answer for me on it.I need to get the product_ID of the new record that is created by this insert statement INSERTINTO products ( class_ID,category_ID,product_name,product_desc,product_image,product_dimension,product_o1,product_o2,product_o3,product_ac,product_ph,product_photo ) SELECT class_ID,category_ID,product_name,product_desc,product_image,product_dimension,product_o1,product_o2,product_o3,product_ac,product_ph,product_photo FROM productsWHERE product_ID = @productID
View Replies !
DLink -get Last Row Inserted Identity
hello everyone, I am using DLink (Link for Database) in visual studio 2008.does anyone knows how to get the identity of an inserted row?in sql server in stored procedure we used to write @@identitybut how do we do it in DLink.??? if we don't want to call a stored procedure
View Replies !
Getting Identity/Serial Of Row Just Inserted?
This isn't so much purely a SQL Server question as a question on ASP.NET VB technique. In particular, I have a situation where I am either inserting a NEW row for a "profile table" (name, email, etc.) or Updating an existing one. In both cases, I need to create a new row in a related table which has the identity/serial column of the parent table as the primary key for the data to be inserted into this subsidiary table (for which there may be many rows inserted, all tying back to the parent). At the time I do the update, of course, I have the identity/serial of the "parent" so it's easy to update/insert. However, if the profile is NEW, I need to capture the identity/serial which was inserted so as to use it for the child table insert. (I remember a call to an obscure function which was -- essentially -- "give me the identity/serial of that which was just INSERTed" but I am unable to locate equivalent functionality. (I have searched various online help files for "Insert serial", "Insert identity" and the like with no results. Hints? Mahalos in advance ... :) KevInKauai
View Replies !
Getting Last Inserted Identity Values
i have a website that accepts users on it. first the user will apply to make use of my site and the data that he supplied will be put to account table. my problem is how can i get the last inserted identity value lets say id, to create the id of the person applied by simply incrementing it... i dont want to use the built in function of the sql server. can anybody help me of this process. asap...
View Replies !
Duplicate Inserted Record
Hi EverybodyThis Code duplicate the record in the database, can somebody help me understand why that happen. Thanks a LOT CompanyName: <asp:textbox id="txtCompanyName" runat="server" /><br />Phone:<asp:textbox id="txtPhone" runat="server" /><br /><br /><asp:button id="btnSubmit" runat="server" text="Submit" onclick="btnSubmit_Click" /><asp:sqldatasource id="SqlDataSource1" runat="server" connectionstring="<%$ ConnectionStrings:dsn %>" insertcommand="INSERT INTO [items] ([smId], [iTitleSP]) VALUES (@CompanyName, @Phone)" selectcommand="SELECT * FROM [items]"> <insertparameters> <asp:controlparameter controlid="txtCompanyName" name="CompanyName" /> <asp:controlparameter controlid="txtPhone" name="Phone" /> </insertparameters></asp:sqldatasource> VBPartial Class Default2 Inherits System.Web.UI.Page Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click SqlDataSource1.Insert() End SubEnd Class ----------------------------------------------Yes is an Identity the Primary Key of the Table items
View Replies !
SQL Help: Conditional Statement Using Inserted
I'm learning SQL and here I'm trying to use two things that I'm not familiar with - IF statements and the Inserted temporary table. Here's the background - skip this paragraph if you like. I'm working on a tasking system for the Help Desk - they get requests from the web site for various items and I break up the request into Software, Hardware, Accounts, etc tables and list the status of each item as "Requested". I'm also keeping a Tasks table to make work orders for each item requested. I've got triggers on the Accounts and Hardware tables that automatically make a new task for those items but the Software is more tricky because all software for a given request should be just one task. Software installs are all done by one person at the same time. So I'm trying to make a trigger that creates a new Task when a new Software record is inserted. But if a task already exists with the same RequestID (meaning they requested two peices of software and this is the second one), then I just want to update the task already created. Here's what I got: 1 CREATE TRIGGER [NewSoftwareTask] 2 ON [dbo].[Software] 3 AFTER INSERT 4 AS 5 BEGIN 6 7 -- If a software task already exists for this request 8 -- then update it. Otherwise create a new task. 9 10 if exists(select TasksID 11 from Tasks 12 where Tasks.RequestsID = inserted.RequestsID and 13 TasksType = 'Software') 14 BEGIN 15 UPDATE [BGHelpdesk].[dbo].[Tasks] 16 SET [TasksDescription] = [TasksDescription] + vbcrlf + "Install " + inserted.SoftwareType + ". " + inserted.SoftwareComments 17 WHERE Tasks.RequestsID = inserted.RequestsID and 18 TasksType = 'Software' 19 END 20 21 else 22 23 BEGIN 24 INSERT INTO [BGHelpdesk].[dbo].[Tasks] 25 ([RequestsID] 26 ,[TasksType] 27 ,[TasksSubType] 28 ,[TasksTitle] 29 ,[TasksDescription]) 30 SELECT 31 s.RequestsID 32 ,'Software' 33 ,s.SoftwareType 34 ,'New ' + s.SoftwareType + ' Account for Request ' + cast(s.RequestsID AS varchar) 35 ,s.SoftwareComments 36 FROM Software s join 37 inserted ON s.SoftwareID = inserted.SoftwareID 38 END 39 END 40 GO It keeps balking at lines 12 and 17 saying "The multi-part identifier "inserted.RequestsID" could not be bound." The ELSE statement is what I use on the other tables and it works fine so the inserted temp record seems pretty straightforward but I must be doing something wrong...
View Replies !
Inserted Row Deletes After Trigger
I'm hoping someone has seen this before because I have no idea what could be causing it. I have an SQL 2005 database with multiple tables and several triggers on the various tables all set to run after insert and update. My program inserts a record into the "items" via a SP that returns the index of the newly added row. The program then inserts a row into another table that is related to items. When the row is inserted into the second table it gets an error that it cannot insert the record because of a foreign key restraint. Checking the items table shows the record that was just inserted in there is now deleted. The items record is only deleted when I have my trigger on that table enabled. Here is the text of the trigger: GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER TRIGGER [dbo].[TestTrigger]ON [dbo].[items]AFTER INSERT AS BEGIN SET NOCOUNT ON; INSERT INTO tblHistory(table_name, record_id, is_insert) VALUES ('items', 123, 1) END tblHistory's field types are (varchar(50), BigInt, bit). As you can see there is nothing in the trigger to cause the items record to be deleted, so I have no idea what it could be? Anyone ever see this before? Thanks in advance!
View Replies !
Which Method To Use So That Last Inserted Value Can Be Retrieve.
Dear all, i am using asp.net ,C# (VS 2005) and sql server 2005. i have written sp for inserting the the data which written last inserted idendity no. i would like to which method should i use(reader , nonexecutequery or executescalar ) so that i get that value and display the value in the form. As executenonquery return only affected rows. please guide me. thanks
View Replies !
Retriving The ID Of The Last Record Inserted
I would appreciate help with retriving the ID of the last record inserted. Have spent considerable time in forums and google but can't find anything that works for me. Here is my VB Code Dim queryString As String = "INSERT INTO [DUALML] ([UseriD], [Company]) VALUES (@UseriD, @Company)" Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand dbCommand.CommandText = queryString dbCommand.Connection = dbConnection Dim dbParam_useriD As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_useriD.ParameterName = "@UseriD" dbParam_useriD.Value = useriD dbParam_useriD.DbType = System.Data.DbType.Int32 dbCommand.Parameters.Add(dbParam_useriD) Dim dbParam_company As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_company.ParameterName = "@Company" dbParam_company.Value = company dbParam_company.DbType = System.Data.DbType.[String] dbCommand.Parameters.Add(dbParam_company) Dim rowsAffected As Integer = 0 dbConnection.Open Try rowsAffected = dbCommand.ExecuteNonQuery Finally dbConnection.Close End Try Return rowsAffected End Function
View Replies !
How To Find An Inserted Value In A Table
Hello, I have 2 tables, and use objectdatasource and stored procedures, with sql server. Let say in the first table I have IDCustomer as a datakey, and other records, and in the second I have the same IDCustomer and CustomerName. I have an INSERT stored procedure that will create a new record in the first table (so generate a new IDCustomer value), and I would like to insert immediately this new value in the second table. How can I know the value of this new IDCustomer ? What is the best way to handle that ? Once the insert in the first table is done should read it the table and extract (with an executescalar) the value and then insert it in the second table ? This solution should work but I am not sure this is the best one. Thanks for your help.
View Replies !
Get PK For Inserted Record In SQLdatasource
I have a table named invoice that contains the following columns-invoiceno - Primary key and is set to autonumber-customerno-incoicedateand on my VB code i did the following InsertCommandSqlDataSource1.InsertCommand = "INSERT INTO invoice(customerno, invoicedate) VALUES('" & Session("UID") & "', GetDate()) "SqlDataSource1.Insert()My Question is how do i get the Primary Key Value it generated during the insert operation(invoice['incoiceno'])? Besides the creationg a stored procedere like the one in the MSDN Library
View Replies !
|
TRACKER
