Hi can someone tell me whats wrong with this stored procedure. All im trying to do is get the publicationID from the publication table in order to use it for an insert statement into another table. The second table PublicaitonFile has the publicaitonID as a foriegn key.
Stored procedure error: cannot insert null into column publicationID, table PublicationFile - its obviously not getting the ID.
ALTER PROCEDURE dbo.StoredProcedureUpdateDocLocField
IF EXISTS (SELECT * FROM Publication WHERE title = @title)
SELECT @publicationID = (SELECT publicationID FROM Publication WHERE title = @title)
SET @publicationID = @@IDENTITY
END
IF NOT EXISTS(SELECT * FROM PublicationFiles WHERE publicationID = @publicationID)
BEGIN
INSERT INTO PublicationFile (publicationID, filePath)
VALUES (@publicationID, @filePath)
END
I executed them and got the following results in SSMSE: TopSixAnalytes Unit AnalyteName 1 222.10 ug/Kg Acetone 2 220.30 ug/Kg Acetone 3 211.90 ug/Kg Acetone 4 140.30 ug/L Acetone 5 120.70 ug/L Acetone 6 90.70 ug/L Acetone ///////////////////////////////////////////////////////////////////////////////////////////// Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming: //////////////////--spTopSixAnalytes.vb--///////////
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")
Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)
'Pass the name of the DataSet through the overloaded contructor
'of the DataSet class.
Dim dataSet As DataSet ("ssmsExpressDB")
sqlConnection.Open()
sqlDataAdapter.Fill(DataSet)
sqlConnection.Close()
End Sub
End Class ///////////////////////////////////////////////////////////////////////////////////////////
I executed the above code and I got the following 4 errors: Error #1: Type 'SqlConnection' is not defined (in Form1.vb) Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb) Error #3: Array bounds cannot appear in type specifiers (in Form1.vb) Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)
Please help and advise.
Thanks in advance, Scott Chang
More Information for you to know: I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly. I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.
I have a sub that passes values from my form to my stored procedure. The stored procedure passes back an @@IDENTITY but I'm not sure how to grab that in my asp page and then pass that to my next called procedure from my aspx page. Here's where I'm stuck: Public Sub InsertOrder() Conn.Open() cmd = New SqlCommand("Add_NewOrder", Conn) cmd.CommandType = CommandType.StoredProcedure ' pass customer info to stored proc cmd.Parameters.Add("@FirstName", txtFName.Text) cmd.Parameters.Add("@LastName", txtLName.Text) cmd.Parameters.Add("@AddressLine1", txtStreet.Text) cmd.Parameters.Add("@CityID", dropdown_city.SelectedValue) cmd.Parameters.Add("@Zip", intZip.Text) cmd.Parameters.Add("@EmailPrefix", txtEmailPre.Text) cmd.Parameters.Add("@EmailSuffix", txtEmailSuf.Text) cmd.Parameters.Add("@PhoneAreaCode", txtPhoneArea.Text) cmd.Parameters.Add("@PhonePrefix", txtPhonePre.Text) cmd.Parameters.Add("@PhoneSuffix", txtPhoneSuf.Text) ' pass order info to stored proc cmd.Parameters.Add("@NumberOfPeopleID", dropdown_people.SelectedValue) cmd.Parameters.Add("@BeanOptionID", dropdown_beans.SelectedValue) cmd.Parameters.Add("@TortillaOptionID", dropdown_tortilla.SelectedValue) 'Session.Add("FirstName", txtFName.Text) cmd.ExecuteNonQuery() cmd = New SqlCommand("Add_EntreeItems", Conn) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add("@CateringOrderID", get identity from previous stored proc) <------------------------- Dim li As ListItem Dim p As SqlParameter = cmd.Parameters.Add("@EntreeID", Data.SqlDbType.VarChar) For Each li In chbxl_entrees.Items If li.Selected Then p.Value = li.Value cmd.ExecuteNonQuery() End If Next Conn.Close()I want to somehow grab the @CateringOrderID that was created as an end product of my first called stored procedure (Add_NewOrder) and pass that to my second stored procedure (Add_EntreeItems)
Hi,I have a stored procedure that insert data into 2 temp tables. The problem that I have is when I insert a data into a first table, how do I get the @@identity value from that table and insert it into the second table?? The following code is what I have:Create #Temp1 @StateID Identity, @State nvarchar(2), @wage moneyINSERT INTO #Temp1 (State, wage)SELECT State, Wage FROM Table1 INNER JOIN Table2 ON Table1.Table1_ID = Table2.Table2_IDCreate #Temp2 @ID Identity @EmployeeID int @StateID Int @Field1 Money @Field2 MoneyINSERT INTO #Temp2 (EmployeeID, StateID, Field1, Field2)SELECT EmployeeID, StateID, Field1, Field2 FROM SomeTable
So, The first part I created a #Temp1 table and insert data into the table. Then after the insert, I want the @@Identity value and insert into the #Temp2 table. This is my first time doing stored procedure, so, I am wondering how do I retrieve the @@identity value and put into the second select statement. Please help. ahTan
Help Stored procedure working but not doing anything New Post Quote Reply Please i need some help.
I am calling a stored procedure from asp.net and there is a cursor in the stored procedure that does some processing on serval tables.
if i run the stored procedure on Query Analyzer it works and does what it is suppose to do but if i run it from my asp.net/module control it goes. acts likes it worked but it does not do what is suppose to do. i believe the cursor in the stroed procedure does not run where is called programmatically from the asp.net/module control page.plus it does not throw any errors
This is the code from my control System.Data.SqlClient.SqlParameter [] param={new System.Data.SqlClient.SqlParameter("@periodStart",Convert.ToDateTime(startDate)),new System.Data.SqlClient.SqlParameter("@periodStart",Convert.ToDateTime(endDate)),new System.Data.SqlClient.SqlParameter("@addedby",UserInfo.FullName+ " "+UserInfo.Username)};
set @NoOfDeadPensioners=0 set @NoOfEnrolledPensioners=0 set @DeactivatedPensioners=0 set @actionType ="PayList Generation"
DECLARE paylist_cursor CURSOR FORWARD_ONLY READ_ONLY FOR
select p.pensionerId,p.isAlive,p.isActive,py.currentMonthlypension from pensioner p left outer join pensionpaypoint py on p.pensionerid=py.pensionerId
where p.isActive = 1
OPEN paylist_cursor
FETCH NEXT FROM paylist_cursor INTO @pensioner_id,@isAlive,@isActive,@currentMonthlypension
WHILE @@FETCH_STATUS = 0 BEGIN
set @NoOfLoadedPensioners=@NoOfLoadedPensioners+1 if(@isAlive=0) begin update Pensioner set isActive=0 where pensionerid=@pensioner_id set @DeactivatedPensioners =@@ROWCOUNT+@DeactivatedPensioners set @NoOfDeadPensioners =@@ROWCOUNT+@NoOfDeadPensioners end else begin insert into pensionpaylist(pensionerId,dateAdded,addedBy, periodStart,periodEnd,amountPaid) values(@pensioner_id,getDate(),@addedby, @periodStart, @periodEnd,@currentMonthlypension) set @NoOfEnrolledPensioners =@@ROWCOUNT+ @NoOfEnrolledPensioners end
-- Get the next author. FETCH NEXT FROM paylist_cursor INTO @pensioner_id,@isAlive,@isActive,@currentMonthlypension END
CLOSE paylist_cursor DEALLOCATE paylist_cursor
set @reportSummary ="The No. of Pensioners Loaded: "+Convert(varchar,@NoOfLoadedPensioners)+"<BR>"+"The No. Of Deactivated Pensioners: "+Convert(varchar,@DeactivatedPensioners)+"<BR>"+"The No. of Enrolled Pensioners: "+Convert(varchar,@NoOfEnrolledPensioners)+"<BR>"+"No Of Dead Pensioner from Pensioners Loaded: "+Convert(varchar,@NoOfDeadPensioners) insert into reportSummary(dateAdded,hasExceptions,periodStart,periodEnd,reportSummary,actionType) values(getDate(),0, @periodStart, @periodEnd,@reportSummary,'Pay List Generation')
if (@@ERROR <> 0) BEGIN
insert into reportSummary(dateAdded,hasExceptions,periodStart,periodEnd,reportSummary,actionType) values(getDate(),1, @periodStart,@periodEnd,@reportSummary,'Pay List Generation')
Hello ! I just can't access @@IDENTITY in my requests !Here is my stored procedure : (I removed a few lines for clarity)set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[AssignerLicence] ( @Apprenant int = 0 ) AS BEGIN SET NOCOUNT ON;
DECLARE @NBLICENCES int DECLARE @COMMANDEID int DECLARE @LICENCEID int
SET @NBLICENCES = (SELECT SUM(commande_licences_reste) FROM Commande WHERE commande_valide = 1)
IF @NBLICENCES > 0 BEGIN
UPDATE Commande SET commande_licences_reste = (commande_licences_reste - 1) WHERE commande_id = (SELECT TOP 1 C_min.commande_id FROM Commande AS C_min WHERE C_min.commande_licences_reste > 5 ORDER BY C_min.commande_licences_reste ASC, C_min.commande_date ASC);
SET @COMMANDEID = @@IDENTITY
INSERT INTO Licence (licence_date_debut, commande_id) VALUES (GETDATE(), @COMMANDEID)
SET @LICENCEID = @@IDENTITY
UPDATE Apprenant SET licence_id = @LICENCEID WHERE apprenant_id = @Apprenant
END
END But it always throws an error saying that I can't insert null into commande_id (basically, I think @@IDENTITY isn't executed at all) Any idea why it doen't works ? Thanks !
In the following stored procedure I would like to test whether @TopCategoryID is null. If so I would like to insert the @CategoryID value into the @TopCategoryID value. So, once @CategoryID recieves the @@IDENTITY value, how do I enter this same value into @TopCategoryID as well???
CREATE PROCEDURE sp_CT_InsertCategory
@Namevarchar(50), @Description varchar(250), @topCategory bit, @ParentCategoryID int, @TopCategoryID int, @CategoryID int OUTPUT AS DECLARE @CurrID int
I have a stored procedure where I am passing in several strings which are concatenated to form an Insert SQL statement. I then return the identity value from the insert - problem is, the identity value is coming back NULL.
FYI, the real problem I am trying to solve is simply to return the identity after an insert. I could write a simple insert sp that returns the identity, but my code is written to handle generic situations and build an INSERT statement as needed. So if there are any other ideas of executing an insert statement and getting the identity, please let me know.
CREATE PROCEDURE testProc AS BEGIN CREATE TABLE #tblTest(ID INT NOT NULL IDENTITY, Col1 INT) INSERT INTO #tblTest(Col1) SELECT colA FROM tableA ORDER BY colA
END
This is my simple procedure, I wanted to know whether the IDENTITY values created in #tblTest will always be consistent, I mean without losing any number in between. i.e. ID column will have values 1,2,3,4,5..... or is there any chance of ID column having values like 1,2, 4, 6,7,8....
Having a little trouble not seeing why this insert is not happening.... --snip-- DECLARE c_studId CURSOR FOR SELECT studentId FROM students FOR READ ONLY OPEN c_studId FETCH NEXT FROM c_studId INTO @studentId IF( @@FETCH_STATUS = 0 ) BEGIN SET @studRec = 'Found' END CLOSE c_studId DEALLOCATE c_studId BEGIN TRAN IF (@studRec <> 'Found') BEGIN INSERT INTO students (studentId) VALUES (@studentId) END Well, you get the idea, and I snipped a lot of it.Why is it not inserting if the user is not found?Thanks all,Zath
I have a SP below that authenticates users, the problem I have is that activate is of type BIT and I can set it to 1 or 0. If I set it to 0 which is disabled, the user can still login. Therefore I want users that have activate as 1 to be able to login and users with activate as 0 not to login
Hi there below is my code for a sproc. however when i run it, it doesnt seem to create a table
USE [dw_data] GO /****** Object: StoredProcedure [dbo].[usp_address] Script Date: 10/24/2007 15:33:21 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO
CREATE PROC [dbo].[usp_address] ( @TableType INT = null
)
AS
IF @TableType is NULL OR @TableType = 1
BEGIN
IF NOT EXISTS (SELECT 1 FROM [DW_BUILD].INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'address')
Hi All,I am trying to write a basic stored procedure to return a range ofvalues but admit that I am stumped. The procedure syntax used is:ALTER PROCEDURE Rpt_LegacyPurchaseOrderSp(@StartPoNumber PONumberType = 'Null',@FinishPoNumber PONumberType = 'Null')ASSET @StartPoNumber = 'PO_NUMBER_POMSTR'SET @FinishPoNumber = 'PO_NUMBER_POMSTR'SELECTIPPOMST_SID,--Start tbl_IPPOMSTPO_NUMBER_POMSTR,VENDOR_NUMBER_POMSTR,SHIP_NUMBER_POMSTR,CHANGE_ORDER_POMSTR,FOB_POINT_POMSTR,ROUTING_POMSTR,DATE_ISSUED_POMSTR,DATE_LAST_RECPT_POMSTR,CONTACT_PERSON_1_POMSTR,PREPAID_COLLECT_POMSTR,TERMS_POMSTR,AMOUNT_ESTIMATED_POMSTR,AMOUNT_RECEIVED_POMSTR,AMOUNT_PAID_POMSTR,LOCATION_CODE_POMSTR,SHIPPING_POINT_POMSTR,PRINT_IND_POMSTR,BUYER_POMSTR,SHIPMENT_POMSTR,STATUS_POMSTR,CURRENCY_POMSTR,CURRENCY_STATUS_POMSTR,AMOUNT_EST_CUR_POMSTR,AMOUNT_REC_CUR_POMSTR,AMOUNT_PAID_CUR_POMSTR,--Finish tbl_IPPOMSTIPPOITM_SID,--Start tbl_IPPOITMPO_NUMBER_POITEM,ITEM_NUMBER_POITEM,CATEGORY_POITEM,DESCRIPTION_POITEM,VENDOR_NUMBER_POITEM,DATE_ORIGINAL,DATE_RESCHEDULED,ACCOUNT_NUMBER_POITEM,STOCK_NUMBER_POITEM,JOB_NUMBER_POITEM,RELEASE_WO_POITEM,QUANTITY_ORDERED_POITEM,QUANTITY_RECVD_POITEM,UOM_POITEM,UNIT_WEIGHT_POITEM,UNIT_COST_POITEM,EXTENDED_TOTAL_POITEM,MATERIAL_NUMBER_POITEM,COMPLETE_POITEM,LOCATION_CODE_POITEM,INSPECTION_POITEM,BOM_ITEM_POITEM,COST_ACCOUNT_POITEM,CHANGE_ORDER_POITEM,TAX_CODE_POITEM,ISSUE_CODE_POITEM,QUANTITY_INSPECT_POITEM,EXC_RATE_CURR_POITEM,UNIT_COST_CURR_POITEM,EXTENDED_TOTAL_CURR_POITEM,PLANNER_POITEM,BUYER_POITEM--Finish tbl_IPPOITMIPVENDM_SID,--Start tbl_IPVENDMVENDOR_NUMBER_VENMSTR,VENDOR_NAME_VENMSTR,ADDRESS_LINE_1_VENMSTR,ADDRESS_LINE_2_VENMSTR,ADDRESS_LINE_3_VENMSTR,CITY_VENMSTR,STATE_VENMSTR,ZIP_CODE_VENMSTR,COUNTRY_VENMSTR--Finish tbl_IPVENDMFROM tbl_IPPOMSTJOIN tbl_IPPOITMON tbl_IPPOITM.PO_NUMBER_POITEM = tbl_IPPOMST.PO_NUMBER_POMSTRJOIN tbl_IPVENDMon tbl_IPVENDM.VENDOR_NUMBER_VENMSTR = tbl_IPPOMST.VENDOR_NUMBER_POMSTRWHERE tbl_IPPOMST.PO_NUMBER_POMSTR >= @StartPoNumber ANDtbl_IPPOMST.PO_NUMBER_POMSTR <= @FinishPoNumberBasically, no rows are returned for the valid (records in database)range I enter. I have been troubleshopoting the syntax. This hasinvolved commenting out references to @FinishPoNumber so in effect Ijust pass in a valid PO Number using @StartPoNumber parameter. Thisworks in terms of returning all 76545 PO records.Can anyone help me to identify why this syntax will not return a rangeof PO records that fall between @StartPoNumber and @FinishPoNumber?Any help would be greatly appreciated.Many Thanks*rohan* & Merry Christmas!
I have stored procedure which contains follwing part of it. it says syntax when i worte line to get @@identity valuewhen delete that line command succesful. but i need to get @@identity from the insert statement and assign it to a variable and use it after any body pls tell me how to get this within a stored prosedure or what is the error of the following code bit. (#tblSalesOrders is a temporary table which containsset of records from orginal table )DECLARE @soNo1 INT DECLARE @CursorOrders CURSOR SET @CursorOrders = CURSOR FAST_FORWARD FOR select fldSoNo from #tblSalesOrders declare @newSONO1 int OPEN @CursorOrders FETCH NEXT FROM @CursorOrders INTO @soNo1 WHILE @@FETCH_STATUS = 0 BEGIN ----for each salesorder insert to salesorderline insert into tblSalesOrders (fldWebAccountNo,fldDescription,fldSoDate,fldGenStatus) select (fldWebAccountNo,fldDescription,fldSoDate,fldGenStatus) from #tblSalesOrders where fldSoNo =@soNo1;
set @newSONO1=SCOPE_IDENTITY; -------in this section again create another cursor for another set of records and insert to a table passing identity value return from the above insert -------------------------- SELECT @intErrorCode = @@ERRORIF (@intErrorCode <> 0) GOTO PROBLEM FETCH NEXT FROM @CursorOrders INTO @soNo1 END CLOSE @CursorOrders DEALLOCATE @CursorOrders
I have a table, which is being replicated with the identity column set "not for replication" and a stored procedure, which is also replicated (execution) which inserts into this table. When the execution of the stored procedure happens, the replication monitor complains about identity value not being provided.other than removing the stored procedure from replication?
OK, I have been raking my brains with this and no solution yet. I simply want to update a field in a table given the record Id. When I try the SQL in standalone (no sp) it works and the field gets updated. When I do it by executing the stored procedure from a query window in the Express 2005 manager it works well too. When I use the stored procedure from C# then it does not work: 1. ExecuteNonQuery() always returns -1 2. When retrieving the @RETURN_VALUE parameter I get -2, meaning that the SP did not find a matching record. So, with #1 there is definitely something wrong as I would expect ExecuteNonQuery to return something meaningful and with #2 definitely strange as I am able to execute the same SQL code with those parameters from the manager and get the expected results. Here is my code (some parts left out for brevity):1 int result = 0; 2 if (!String.IsNullOrEmpty(icaoCode)) 3 { 4 icaoCode = icaoCode.Trim().ToUpper(); 5 try 6 { 7 SqlCommand cmd = new SqlCommand(storedProcedureName);(StoredProcedure.ChangeAirportName); 8 cmd.Parameters.Add("@Icao", SqlDbType.Char, 4).Value = newName; 9 cmd.Parameters.Add("@AirportName", SqlDbType.NVarChar, 50).Value = (String.IsNullOrEmpty(newName) ? null : newName); 10 cmd.Parameters.Add("@RETURN_VALUE", SqlDbType.Int).Direction = ParameterDirection.ReturnValue; 11 cmd.Connection = mConnection; // connection has been opened already, not shown here 12 cmd.CommandType = CommandType.StoredProcedure; 13 int retval = cmd.ExecuteNonQuery(); // returns -1 somehow even when RETURN n is != -1 14 result = (int)cmd.Parameters["@RETURN_VALUE"].Value; 15 16 } 17 catch (Exception ex) 18 { 19 result = -1; 20 } 21 }
And this is the stored procedure invoked by the code above:1 ALTER PROCEDURE [dbo].[ChangeAirfieldName] 2 -- Add the parameters for the stored procedure here 3 @Id bigint = null,-- Airport Id, OR 4 @Icao char(4) = null,-- ICAO code 5 @AirportName nvarchar(50) 6 AS 7 BEGIN 8 -- SET NOCOUNT ON added to prevent extra result sets from 9 -- interfering with SELECT statements. 10 SET NOCOUNT ON; 11 12 -- Parameter checking 13 IF @Id IS NULL AND @Icao IS NULL 14 BEGIN 15 RETURN -1;-- Did not specify which record to change 16 END 17 -- Get Id if not known given the ICAO code 18 IF @Id IS NULL 19 BEGIN 20 SET @Id = (SELECT [Id] FROM [dbo].[Airports] WHERE [Icao] = @Icao); 21 --PRINT @id 22 IF @Id IS NULL 23 BEGIN 24 RETURN -2;-- No airport found with that ICAO Id 25 END 26 END 27 -- Update record 28 UPDATE [dbo].[Airfields] SET [Name] = @AirportName WHERE [Id] = @Id; 29 RETURN @@ROWCOUNT 30 END
As I said when I execute standalone UPDATE works fine, but when approaching it via C# it returns -2 (did not find Id).
I've got a stored procedure in database A that calls the sp_start_job stored procedure in msdb as follows:
CREATE PROCEDURE xxxxx WITH EXECUTE AS 'domainusername'
AS
EXEC msdb.dbo.sp_start_job B'jobname' ;
RETURN
The domainusername is the in the database sysadmin role and the owner of the job. To make this work originally, I had to change the msdb database to be trusted.
This worked for the past several months.
Now it doesn't work (perhaps after a reboot but not sure). The error I get is "The EXECUTE permission was denied on the object 'sp_start_job', database 'msdb', schema 'dbo'
I looked to make sure that the account had grant execute rights and it does. I tried setting it via GRANT statement and it was granted successfully yet the error still occurs. I've tried changing accounts and anything else I can think of to no avail.
Any ideas how to troubleshoot this issue. I've tried all the tricks I can think of.
I am having a problem with this stored procedure. I'm using SQL Server 2005 Developer's edition and if I execute the procedure in a query window, I get no errors. Also, when the script runs from a website call there are no errors. The problem is that it doesn't return the information that is in the database. It is supposed to return the orders from Washington state between such and such dates. The orders are there in the database, so I think the where clause must be wrong.
Thanks for the help.
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CommerceLibOrdersGetWashingtonState]') AND type in (N'P', N'PC'))
I'm having a problem I do an insert into a table but I want to return the value of the identity field of that insert so I can email a confirmation. For some reason this code doesn't work. Below is the stored procedure I'm calling and below that the code I'm using. What am I doing wrong. The value I have returned is null when it should be a number. Any suggestions. Why does finalMagicNum2 come back null when it should grab the identity field of the inserted record.
' Sequence ID for request magicCommand.SelectCommand.Parameters.Add(New SqlParameter("@theSequence", SqlDbType.NVarChar, 8)) magicCommand.SelectCommand.Parameters("@theSequence").Value = "41833"
' Subject for new Wac Ticket magicCommand.SelectCommand.Parameters.Add(New SqlParameter("@theSubject", SqlDbType.NVarChar, 8)) magicCommand.SelectCommand.Parameters("@theSubject").Value = "1064"
' First Name Field magicCommand.SelectCommand.Parameters.Add(New SqlParameter("@theFirstName", SqlDbType.NVarChar, 50)) magicCommand.SelectCommand.Parameters("@theFirstName").Value = orderFirstName
' Last Name Field magicCommand.SelectCommand.Parameters.Add(New SqlParameter("@theLastName", SqlDbType.NVarChar, 75)) magicCommand.SelectCommand.Parameters("@theLastName").Value = orderLastName
DSMagic = new DataSet() magicCommand.Fill(DSMagic,"employees")
If DSMagic.Tables("_smdba_._telmaste_").Rows.Count > 0 Then finalMagicNum2 = DSMagic.Tables("_smdba_._telmaste_").Rows(0)("finalMagic").toString End If
how do i return the identity from stored procedure to asp.net code behind page? CREATE PROCEDURE [dbo].[myInsert] ( @Name varchar(35), @LastIdentityNumber int output)AS insert into table1 (name) values (@name) DECLARE @IdentityNumber intSET @IdentityNumber = SCOPE_IDENTITY()SELECT @IdentityNumber as LastIdentityNumber code behind: public void _Insert( string _Name, { DbCommand dbCommand = db.GetStoredProcCommand("name_Insert");db.AddInParameter(dbCommand, "name", DbType.String, _userId); db.AddParameter(dbCommand, "@IdentityNumber", DbType.Int32, ParameterDirection.Output, "", DataRowVersion.Current, null); db.ExecuteNonQuery(dbCommand); int a = (int)db.GetParameterValue(dbCommand,"@IdentityNumber"); whats wrong with to the above code?
I have a problem with an existing stored procedure that is used to insert a new entry to a table (using an Insert statement).
I have been using the @@Identity global variable to return the identity column (id column) back to the calling routine. This has worked fine for years until recently an ‘after insert Trigger’ has been added to the table being updated.
Now the @@Identity is returning the identity value of the trigger that was called instead of the original table insert.
Does anyone know how I can code around this issue (without using a select statement within my stored proc, as these have been known to cause locks in the past).
Hello!I use a procedure to insert a new row into a table with an identitycolumn. The procedure has an output parameter which gives me theinserted identity value. This worked well for a long time. Now theidentity value is over 700.000 and I get errors whiles retrieving theinserted identitiy value. If I delete rows and reset the identityeverything works well again. So I think it is a data type problem.My Procedure:create procedure InsertProduct@NEWID int outputasbeginset nocount oninsert into PRODUCT(D_CREATED)values(getdate()+'')set nocount offselect @NEWID = @@IDENTITYendMy C# code:SqlCommand comm = new SqlCommand("InsertProduct", sqlCon);comm.CommandType = CommandType.StoredProcedure;comm.Parameters.Add(new SqlParameter("@NEWID",System.Data.SqlDbType.Int)).Direction =System.Data.ParameterDirection.Output;try{SqlDataReader sqlRead = comm.ExecuteReader();object o = comm.Parameters["@NEWID"].Value;//...}catch ( Exception ex ){throw ex;}The object o is alwaya System.DbNull. I also tried to use bigint.Any hints are welcomeCiaoSusanne
Creating a temporary table in stored procedure and using a sql query to insert the data in temp. table.I am facing the error as : String or binary data would be truncated.The statement has been terminated. The procedure i created is as : ALTER PROCEDURE fetchpersondetails AS CREATE Table #tempperson (personID int,FirstName nvarchar(200),LastName nvarchar(250),title nvarchar(150),Profession nvarchar(200),StreetAddress nvarchar(300), StateAddress nvarchar(200),CityAddress nvarchar(200),CountryAddress nvarchar(200),ZipAddress nvarchar(200),Telephone nvarchar(200),Mobile nvarchar(200), Fax nvarchar(200),Email nvarchar(250),NotesPub ntext,Affiliation nvarchar(200),Category nvarchar(200))
Insert into #tempperson SELECT dbo.tblperson.personID, ISNULL(dbo.tblperson.fName, N'') + ' ' + ISNULL(dbo.tblperson.mName, N'') AS FirstName, dbo.tblperson.lname AS LastName, dbo.tblperson.honor AS Title, dbo.tblperson.title AS Profession, dbo.tblperson.street + ' ' + ISNULL(dbo.tblperson.suite, N'') AS StreetAddress, dbo.tblperson.city AS cityaddress, dbo.tblperson.state AS stateaddress, dbo.tblperson.postalCode AS zipaddress, dbo.tblperson.Phone1 + ',' + ISNULL(dbo.tblperson.Phone2, N'') + ',' + ISNULL(dbo.tblperson.Phone3, N'') AS Telephone, dbo.tblperson.mobilePhone AS mobile, dbo.tblperson.officeFax + ',' + ISNULL(dbo.tblperson.altOfficeFax, N'') + ',' + ISNULL(dbo.tblperson.altOfficeFax2, N'') AS Fax, ISNULL(dbo.tblperson.Email1, N'') + ',' + ISNULL(dbo.tblperson.Email2, N'') + ',' + ISNULL(dbo.tblperson.Email3, N'') AS Email, dbo.tblperson.notes AS NotesPub, dbo.tblOrganizations.orgName AS Affiliation, dbo.tblOrganizations.orgCategory AS Category, dbo.tblCountry.countryNameFull AS countryaddress FROM dbo.tblperson INNER JOIN dbo.tblOrganizations ON dbo.tblperson.orgID = dbo.tblOrganizations.orgID INNER JOIN dbo.tblCountry ON dbo.tblperson.countryCode = dbo.tblCountry.ISOCode
I am trying to follow an exercise for debugging a stored procedure in the .net IDE. I have had success in adding a connection to the application, until I get the point of where I should set a BreakPoint in the stored procedure "Ten Most Expensive Products" which belongs to the Northwind database. When I open Sever Explorer -->Data Connections- ->WI0001.NorthWind.dbo-->Stored Procedures and Right-click the procedure, the context menu does not give me an edit item to select to open this SP in the editor. Nor can I double-click on the stored procedure to open it either.
This is my first attempt at a loop in a stored procedure, and it is not working, but the rest of the sp works fine. Could anyone please provide me with some feedback. I am not sure if I need to execute the loop first or all the sp at once ? Thanks so much.CREATE PROCEDURE Table_1TT_1T (@PartNo varchar(20), @Wkorder varchar(10), @Setup datetime, @Line smallint, @TT integer, @Tester smallint, @LT1 integer, @LT2 integer, @LT3 integer, @LT4 integer, @LT5 integer, @LT6 integer, @LT7 integer, @LT8 integer, @LT9 integer, @LT10 integer, @LT11 integer, @LT12 integer, @LT13 integer, @LT14 integer, @LT15 integer, @LT16 integer, @LT17 integer, @LT18 integer, @LT19 integer, @LT20 integer, @LT21 integer, @LT22 integer, @LT23 integer, @LT24 integer, @LT25 integer, @LT26 integer, @LT27 integer, @LT28 integer, @LT29 integer, @LT30 integer, @LT31 integer, @LT32 integer, @LT33 integer, @LT34 integer, @LT35 integer, @LT36 integer, @UnitFound integer OUT, @parameters_LamType varchar(50) OUT, @parameters_Shunt real OUT, @parameters_ShuType varchar(50) OUT, @parameters_Stack real OUT, @parameters_Steel varchar(50) OUT, @Partno11 varchar(20) OUT, @Wkorder11 varchar(10) OUT, @Partno12 varchar(20) OUT, @Wkorder12 varchar(10) OUT, @Partno24 varchar(20) OUT, @Wkorder24 varchar(10) OUT, @Partno29 varchar(20) OUT, @Wkorder29 varchar(10) OUT, @Partno34 varchar(20) OUT, @Wkorder34 varchar(10) OUT, --@DL1 integer OUT, --@DL2 integer OUT, --@DL3 integer OUT, --@DL4 integer OUT, --@DL5 integer OUT, --@DL6 integer OUT, --@DL7 integer OUT, --@DL8 integer OUT, --@DL9 integer OUT, --@DL10 integer OUT, @DL11 integer OUT, @DL12 integer OUT, --@DL13 integer OUT, --@DL14 integer OUT, --@DL15 integer OUT, --@DL16 integer OUT, --@DL17 integer OUT, --@DL18 integer OUT, --@DL19 integer OUT, --@DL20 integer OUT, --@DL21 integer OUT, --@DL22 integer OUT, --@DL23 integer OUT, @DL24 integer OUT, --@DL25 integer OUT, --@DL26 integer OUT, --@DL27 integer OUT, --@DL28 integer OUT, @DL29 integer OUT, --@DL30 integer OUT, --@DL31 integer OUT, --@DL32 integer OUT, --@DL33 integer OUT, @DL34 integer OUT) --@DL35 integer OUT, --@DL36 integer OUT)ASSET @Tester = 1WHILE @Tester < 36 BEGIN Set @Line = (Select Line from dbo.location where Tester = @Tester) IF @Line = 453 BEGIN If @Tester = 1 BEGIN SET @LT1 = 453 END If @Tester = 2 BEGIN SET @LT2 = 453 END If @Tester = 3 BEGIN SET @LT3 = 453 END If @Tester = 4 BEGIN SET @LT4 = 453 END If @Tester = 5 BEGIN SET @LT5 = 453 END If @Tester = 6 BEGIN SET @LT6 = 453 END If @Tester = 7 BEGIN SET @LT7 = 453 END If @Tester = 8 BEGIN SET @LT8 = 453 END If @Tester = 9 BEGIN SET @LT9 = 453 END If @Tester = 10 BEGIN SET @LT10 = 453 END If @Tester = 11 BEGIN SET @LT11 = 453 END If @Tester = 12 BEGIN SET @LT12 = 453 END If @Tester = 13 BEGIN SET @LT13 = 453 END If @Tester = 14 BEGIN SET @LT14 = 453 END If @Tester = 15 BEGIN SET @LT15 = 453 END If @Tester = 16 BEGIN SET @LT16 = 453 END If @Tester = 17 BEGIN SET @LT17 = 453 END If @Tester = 18 BEGIN SET @LT18 = 453 END If @Tester = 19 BEGIN SET @LT19 = 453 END If @Tester = 20 BEGIN SET @LT20 = 453 END If @Tester = 21 BEGIN SET @LT21 = 453 END If @Tester = 22 BEGIN SET @LT22 = 453 END If @Tester = 23 BEGIN SET @LT23 = 453 END If @Tester = 24 BEGIN SET @LT24 = 453 END If @Tester = 25 BEGIN SET @LT25 = 453 END If @Tester = 26 BEGIN SET @LT26 = 453 END If @Tester = 27 BEGIN SET @LT27 = 453 END If @Tester = 28 BEGIN SET @LT28 = 453 END If @Tester = 29 BEGIN SET @LT29 = 453 END If @Tester = 30 BEGIN SET @LT30 = 453 END If @Tester = 31 BEGIN SET @LT31 = 453 END If @Tester = 32 BEGIN SET @LT32 = 453 END If @Tester = 33 BEGIN SET @LT33 = 453 END If @Tester = 34 BEGIN SET @LT34 = 453 END If @Tester = 35 BEGIN SET @LT35 = 453 END END SET @Tester = @Tester + 1 ENDSELECT @parameters_LAMTYPE = LAMTYPE, @parameters_SHUNT = SHUNT, @parameters_SHUTYPE = SHUTYPE, @parameters_STACK = STACK, @parameters_STEEL = STEEL FROM DBO.PARAMETERS A INNER JOIN .DBO.XREF B ON A.PARTNO = B.XREF WHERE B.PARTNO = @PARTNO SET @UnitFound = @@rowcountIF @UnitFound = 0 BEGIN SELECT @parameters_LAMTYPE = LAMTYPE, @parameters_SHUNT = SHUNT, @parameters_SHUTYPE = SHUTYPE, @parameters_STACK = STACK, @parameters_STEEL = STEEL FROM DBO.PARAMETERS WHERE PARTNO = @PARTNO SET @UnitFound = @@rowcount END --IF @LT1 = @Line BEGIN SET @DL1 = 1 END --IF @LT2 = @Line BEGIN SET @DL2 = 1 END --IF @LT3 = @Line BEGIN SET @DL3 = 1 END --IF @LT4 = @Line BEGIN SET @DL4 = 1 END --IF @LT5 = @Line BEGIN SET @DL5 = 1 END --IF @LT6 = @Line BEGIN SET @DL6 = 1 END --IF @LT7 = @Line BEGIN SET @DL7 = 1 END --IF @LT8 = @Line BEGIN SET @DL8 = 1 END --IF @LT9 = @Line BEGIN SET @DL9 = 1 END --IF @LT10 = @Line BEGIN SET @DL10 = 1 END IF @LT11 = 453 BEGIN SET @Partno11 = @Partno SET @Wkorder11 = @Wkorder SET @DL11 = 1 END --IF @LT11 = @Line BEGIN SET @DL11 = 1 END IF @LT12 = 453 BEGIN SET @Partno12 = @Partno SET @Wkorder12 = @Wkorder SET @DL12 = 1 END --IF @LT13 = @Line BEGIN SET @DL13 = 1 END --IF @LT14 = @Line BEGIN SET @DL14 = 1 END --IF @LT15 = @Line BEGIN SET @DL15 = 1 END --IF @LT16 = @Line BEGIN SET @DL16 = 1 END --IF @LT17 = @Line BEGIN SET @DL17 = 1 END --IF @LT18 = @Line BEGIN SET @DL18 = 1 END --IF @LT19 = @Line BEGIN SET @DL19 = 1 END --IF @LT20 = @Line BEGIN SET @DL20 = 1 END --IF @LT21 = @Line BEGIN SET @DL21 = 1 END --IF @LT22 = @Line BEGIN SET @DL22 = 1 END --IF @LT23 = @Line BEGIN SET @DL23 = 1 END IF @LT24 = 453 BEGIN SET @Partno24 = @Partno SET @Wkorder24 = @Wkorder SET @DL24 = 1 END --IF @LT25 = @Line BEGIN SET @DL25 = 1 END --IF @LT26 = @Line BEGIN SET @DL26 = 1 END --IF @LT27 = @Line BEGIN SET @DL27 = 1 END --IF @LT28 = @Line BEGIN SET @DL28 = 1 END IF @LT29 = 453 BEGIN SET @Partno29 = @Partno SET @Wkorder29 = @Wkorder SET @DL29 = 1 END --IF @LT30 = @Line BEGIN SET @DL30 = 1 END --IF @LT31 = @Line BEGIN SET @DL31 = 1 END --IF @LT32 = @Line BEGIN SET @DL32 = 1 END --IF @LT33 = @Line BEGIN SET @DL33 = 1 END IF @LT34 = 453 BEGIN SET @Partno34 = @Partno SET @Wkorder34 = @Wkorder SET @DL34 = 1 END --IF @LT35 = @Line BEGIN SET @DL35 = 1 END --IF @LT36 = @Line BEGIN SET @DL36 = 1 ENDGO
Hi, [I'm using VWD Express (ASP.NET 2.0)] Please help me understand why the following code, containing an inline SQL SELECT query (in bold) works, while the one after it (using a Stored Procedure) doesn't:
Hy all. My main goal would be to create some kind of map of the database, wich contains information of all connecting fields, including wich fields has key references to the other one and wich table. I'd like to have a table wich shows all the database connections tablename, field, field/table_key (from or to the key points), field_key_type (prymary or foreign) So I thaugh to get sp_fkeys and sp_pkeys from master table and inner joining their results, but I simplay cannot "catch" their result sets. The script must have been written in SQL. Obviously I'd like to do this: SELECT * FROM EXEC sp_fkeys @table_name = 'xy' of course it is not working this way, but I'd like something like this.
When replicating a table which has an identity column I get the error: "Procedure cp_insert_tblname expects parameter @C1, which was not supplied.". The stored procedure appears to be called without any parameters so my insert stored procedure does not work. I know I'm missing something basic here!! Do I have to add the field names when telling replication to use a custom stored procedure. If not, how do arguments get passed to my SP, as globals somehow?
Does SELECT @@identity works with in SQL Server compact edition? I have tried in SQL management sql query. It does work however when i use in VS.NET 2005 / Orcas beta1 , the generated tableAdapter does not recongize the syntax. So any workaround with it ? or any alternative that i can return the new inserted ID in my application.
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie exec dbo.DeriveStatusID 'Created' returns an int value as 1 (performed by "SELECT statusID FROM statusList WHERE statusName= 'Created') but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie: exec dbo.AddProduct_Insert 'widget1' which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID) I want to simply the insert to perform (in one sproc): SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID) This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example). My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert). Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
