SCOPE IDENTITY Using SqlDataSource WITHOUT Stored Proc

Oct 27, 2006

Ok, So I am trying to do 1 insert into a table and then insert more information into another table but I need to get the [TKTNUM] (the column with the idenity increment on it) value that was auto-generated when the insert was done. I have done a search and I am well aware that this are tons of posts on this subject already that is how I found out to use the SCOPE IDENTITY(). But nobody is using the insert syntax that I am using and since I am new, I don't understand any of it. I think they are using ADO.NET. I am extremely new to ASP.NET so take it easy on me ok guys... But can someone take a look at my code and tell me what the problem is. Again, I'm new so I am learning as I go so I might be missing something really stupid. Any help would be greatly appreciated. O yeah, I am running this code in a button click event NOT in a stored procedure (dont really know now to use those anyway).
Here is my code:
Protected Sub submitBtn_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Dim newTktNum
If Page.IsValid Then
Dim it3Conn As SqlDataSource = New SqlDataSource()
it3Conn.ConnectionString = ConfigurationManager.ConnectionStrings("ConnectionString1").ToString
it3Conn.InsertCommand = "INSERT INTO [IT_TICKET] ([REQUEST_DATE], [LOGGED_BY], [REQUESTED_BY], [TKTSTATUS],[NEEDED_DATE]) VALUES (@REQUEST_DATE, @LOGGED_BY, @REQUESTED_BY, @TKTSTATUS,@NEEDED_DATE); SELECT @newTktNum = SCOPE_IDENTITY()"
it3Conn.InsertParameters.Add("REQUEST_DATE", DateTime.Now.ToString)
it3Conn.InsertParameters.Add("LOGGED_BY", User.Identity.Name)
it3Conn.InsertParameters.Add("REQUESTED_BY", User.Identity.Name)
it3Conn.InsertParameters.Add("TKTSTATUS", "NEW")
it3Conn.InsertParameters.Add("NEEDED_DATE", needByTB.Text.ToString)
it3Conn.Insert()
End If
MsgBox("Ticket Number: " & newTktNum & " has been created", MsgBoxStyle.OkOnly, "Ticket Created")
End Sub

 

View 2 Replies


ADVERTISEMENT

Scope Identity In SqlDataSource

Oct 15, 2007

Is it possible to write the InsertCommand for a SqlDataSource to return the value of the AUTONumber of a field generated when a new record is added to a database table and display that value on label1.Text after the postback of ItemInsersted event?  For example,  <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:NearMissConnectionString >" InsertCommand="INSERT INTO [NearMiss] ([Branch], [Division] VALUES (@Branch, @Division);  SELECT SCOPE_IDENTITY()" SelectCommand="SELECT [NearMissID], [Branch], [Division], FROM [NearMiss]"             <InsertParameters>                <asp parameter Name="Branch" Type="Int32" />                <asp parameter Name="Division" Type="String" />            </InsertParameters>
        </asp:SqlDataSource>
 
NearMissID would be the value of the autonumber generated by the SQL Server

View 1 Replies View Related

Scope Identity Using SqlDataSource

Oct 22, 2007

I am trying to retrieve the value of the autonumber generated when a new record is inserted into a SQL 2000 Database using a SqlDataSource attached to a FormView.  Is this possible?  Can some give me a short example of how to retrieve that value and display it in a textbox?
Thanks for any help with this.  It seems like it would be a common issue for .net developers.

View 1 Replies View Related

Error: Identifier 'stored Proc Name' Is Out Of Scope

Mar 10, 2005

Does anyone know what this error message is telling me.

Thanks in advance everyone.

RB

View 2 Replies View Related

Any Resources About Stored Procedures && Scope Identity?

Jun 16, 2007

i am a beginner looking for such resources, MSDN is one but can be a little difficult to understand 

View 3 Replies View Related

SqlDataSource + Stored Proc

Mar 8, 2006

I am attempting to use a SqlDataSource to call a stored procudure from an ASP.Net page. There are a lot of parameters in the proc and each one has a matching form field. No matter what I do, I get no results when I use the aspx page to get my data from the proc. If I use datasource editor I can successfully query the proc and I can query it with enterprise manager too. I just can't call it with the aspx page. I don't what I'm doing wrong. Are there known problems with sqldatasource and procs? My web app connection string is using my admin name and password. Could it be a perms problem (no error messages are seen).  <asp:SqlDataSource ID="jobSrc" runat="server" ConnectionString="<%$ ConnectionStrings:PS11_1_NConnectionString %>" SelectCommand="rq_jp_jobstat" SelectCommandType="StoredProcedure">    <SelectParameters>            <asp:Parameter Name="tcDivno" Type="String" />        <asp:ControlParameter ControlID="startJobNoBx" Name="tcSjob" PropertyName="Text"            Type="String" />            <asp:ControlParameter ControlID="endJobNoBox" Name="tcFjob" PropertyName="Text" Type="String" />            <asp:ControlParameter ControlID="orderDateStartBx" Name="tcSordDate" PropertyName="Text"                Type="String" />            <asp:ControlParameter ControlID="orderDateEndBx" Name="tcForddate" PropertyName="Text"                Type="String" />            <asp:ControlParameter ControlID="dueDateStartBx" Name="tcSduedate" PropertyName="Text"                Type="String" />            <asp:ControlParameter ControlID="dueDateEndBx" Name="tcFduedate" PropertyName="Text"                Type="String" />            <asp:ControlParameter ControlID="proofDateStartBx" Name="tcSproofdue" PropertyName="Text"                Type="String" />            <asp:ControlParameter ControlID="proofDateEndBx" Name="tcFproofdue" PropertyName="Text"                Type="String" />            <asp:ControlParameter ControlID="closeDateStartBx" Name="tcSClosedate" PropertyName="Text"                Type="String" />            <asp:ControlParameter ControlID="closeDateEndBx" Name="tcFClosedate" PropertyName="Text"                Type="String" />            <asp:ControlParameter ControlID="cogsDateStartBx" Name="tcSCogsdate" PropertyName="Text"                Type="String" />            <asp:ControlParameter ControlID="cogsDateEndBx" Name="tcFCogsdate" PropertyName="Text"                Type="String" />            <asp:ControlParameter ControlID="custNoDd" Name="tcScust" PropertyName="SelectedValue"                Type="String" />        <asp:Parameter Name="tcFcust" Type="String" />            <asp:ControlParameter ControlID="salesPersonDd" Name="tcSslpn" PropertyName="SelectedValue"                Type="String" />        <asp:Parameter Name="tcFslpn" Type="String" />            <asp:ControlParameter ControlID="openedByBx" Name="tcSjobper" PropertyName="SelectedValue"                Type="String" />        <asp:Parameter Name="tcFjobper" Type="String" />            <asp:ControlParameter ControlID="productBx" Name="tcSprod" PropertyName="Text" Type="String" />        <asp:Parameter Name="tcFprod" Type="String" />            <asp:ControlParameter ControlID="closedRd" Name="tnOpen" PropertyName="SelectedValue"                Type="Int32" />            <asp:ControlParameter ControlID="cogsRd" Name="tnCogs" PropertyName="SelectedValue"                Type="Int32" />            <asp:ControlParameter ControlID="canceledRd" Name="tnCancel" PropertyName="SelectedValue"                Type="Int32" />            <asp:ControlParameter ControlID="purgeRd" Name="tnPurge" PropertyName="SelectedValue"                Type="Int32" />            <asp:ControlParameter ControlID="shipRd" Name="tnShip" PropertyName="SelectedValue"                Type="Int32" />            <asp:ControlParameter ControlID="finalShipRd" Name="tnFinalshp" PropertyName="SelectedValue"                Type="Int32" />            <asp:ControlParameter ControlID="invoiceRd" Name="tnInvoice" PropertyName="SelectedValue"                Type="Int32" />        <asp:Parameter Name="tcVersion" Type="String" />        </SelectParameters>    </asp:SqlDataSource>And here are the parameters for the proc:DECLARE @RC intDECLARE @tcDivno char(2)DECLARE @tcSjob varchar(5)DECLARE @tcFjob varchar(5)DECLARE @tcSordDate char(8)DECLARE @tcForddate char(8)DECLARE @tcSduedate char(8)DECLARE @tcFduedate char(8)DECLARE @tcSproofdue char(8)DECLARE @tcFproofdue char(8)DECLARE @tcSClosedate char(8)DECLARE @tcFClosedate char(8)DECLARE @tcSCogsdate char(8)DECLARE @tcFCogsdate char(8)DECLARE @tcScust varchar(12)DECLARE @tcFcust varchar(12)DECLARE @tcSslpn varchar(3)DECLARE @tcFslpn varchar(3)DECLARE @tcSjobper varchar(15)DECLARE @tcFjobper varchar(15)DECLARE @tcSprod varchar(15)DECLARE @tcFprod varchar(15)DECLARE @tnOpen intDECLARE @tnCogs intDECLARE @tnCancel intDECLARE @tnPurge intDECLARE @tnShip intDECLARE @tnFinalshp intDECLARE @tnInvoice intDECLARE @tcVersion char(2)

View 4 Replies View Related

Returning @@IDENTITY FROM Stored Proc

Nov 15, 2005

I am trying to get the identity of an inserted record using this SP:

<code>
ALTER PROCEDURE acereal_Admin.AuctionInsertCommand
(
    @AuctionID Int OUTPUT,
    @StartDate char(255),
    @StartTime char(255),
    @Location char(255),
    @Title char(255),
    @Description char(255),
    @Images char(255)
)
AS
INSERT INTO Auctions
(
    StartDate,
    StartTime,
    Location,
    Title,
    Description,
    Images
)
VALUES
(
    @StartDate,
    @StartTime,
    @Location,
    @Title,
    @Description,
    @Images
)

SELECT     AuctionID AS ID
FROM         Auctions
WHERE     (AuctionID = @@IDENTITY)
</code>

Then, I am using this class for a file called dataaccess.cs to return the ID:

<code>
public string SaveImageName(string ImageName, string AuctionID)
        {
            SqlConnection
sqlConnection = new
SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);

           
this.newSqlCommand = new
System.Data.SqlClient.SqlCommand("[AuctionImageSave]", sqlConnection);
           
this.newSqlCommand.CommandType =
System.Data.CommandType.StoredProcedure;

            SqlParameter
paramImagePath = new System.Data.SqlClient.SqlParameter("@ImagePath",
System.Data.SqlDbType.Char, 255);
            paramImagePath.Value = ImageName;
            this.newSqlCommand.Parameters.Add(paramImagePath);

            SqlParameter
paramAuctionID = new System.Data.SqlClient.SqlParameter("@AuctionID",
System.Data.SqlDbType.Int, 4);
            paramAuctionID.Value = AuctionID;
            this.newSqlCommand.Parameters.Add(paramAuctionID);

            SqlParameter
paramImageID = new System.Data.SqlClient.SqlParameter("@ImageID",
System.Data.SqlDbType.Int, 4);
            paramImageID.Direction = ParameterDirection.Output;
            this.newSqlCommand.Parameters.Add(paramImageID);

            //Return SqlDataReader Struct
            this.newSqlCommand.Connection.Open();
            this.newSqlCommand.ExecuteNonQuery();
            this.newSqlCommand.Connection.Close();

            int returnID = (int)paramImageID.Value;

            return returnID.ToString();
        }
</code>

The above code returns null.

Can anyone tell me what I am doing wrong?

THanks, Justin.

View 2 Replies View Related

Retreive @@Identity From Stored Proc Call

May 3, 2007

Hi all, I've been struggling with this one for a while, but am still doing something wrong: I have three tables which need to be updated. I have a stored proc which accomplishes the first writes the data in for the first 2 tables. The last table is a one to many, so needs a seperate stored proc which will be called multiple times depending on the number of items in the order.  My only question I am trying to get to here is: How do I get the first stored proc to return me the primary key value from the 1st insert (NOT the second)? I've tried a few different methods: the current one shown below returns me  "2", as in the number of inserts performed.   Dim InsertCmd As New SqlCommand("WriteOrder", oSQLConn)
InsertCmd.CommandType = CommandType.StoredProcedure
InsertCmd.Parameters.AddWithValue("@CartTotal", Session("CartTotal"))
InsertCmd.Parameters.AddWithValue("@CARDFNAME", BillingInfo("CARDFNAME"))
InsertCmd.Parameters.AddWithValue("@CARDLNAME", BillingInfo("CARDLNAME"))
...
InsertCmd.Parameters.AddWithValue("@CONTACTEMAIL", BillingInfo("CONTACTEMAIL"))
InsertCmd.Parameters.AddWithValue("@COMPANYMATCH", 0)
InsertCmd.Parameters.AddWithValue("@RECNUM", 0)
Response.Write("---" & InsertCmd.ExecuteNonQuery().ToString() & "---")


---------------------STORED PROC---------------------

ALTER PROCEDURE [dbo].[WriteOrder]
@CartTotal float,
@CARDFNAME varchar(30),
...
@CONTACTEMAIL varchar(100),
@COMPANYMATCH bit,
@RecNum int = 0 OUTPUT
AS

INSERT INTO [dbo].[ORDER]
([OrderDate], [OrderTotal], [CARDFIRSTNAME], [CARDLASTNAME], [BILLINGADDR1], [BILLINGADDR2], [BILLINGADDR3], [BILLINGCITY], [BILLINGSTATE], [BILLINGPOSTALCODE], [BILLINGCOUNTRY], [BILLINGPHONE], [BILLINGEMAIL], [CCTYPE], [ACCOUNTNUMBER], [CARDEXPIREMONTH], [CARDEXPIREYEAR], [CVV2], [TransactionID], [TransDateStamp], [ProcessorAuthCode])

VALUES
(GetDate() ,@CartTotal , @CARDFNAME, @CARDLNAME, @BILLINGADDRESS1, @BILLINGADDRESS2, @BILLINGADDRESS3, @BILLINGCITY, @BILLINGSTATE, @BILLINGPOSTALCODE, @BILLINGCOUNTRY, @BILLINGPHONE, @BILLINGEMAIL, @CCTYPE, @ACCOUNTNUMBER, @EXPIRATIONMONTH, @EXPIRATIONYEAR, @CVV2,@TransactionID, @TransDateStamp,@ProcessorAuthCode)

SET @RecNum = @@IDENTITY

INSERT INTO [dbo].[CONTACT]
([ORDERID], [CONTACTFNAME], [CONTACTLNAME], [CONTACTADDRESS1], [CONTACTADDRESS2], [CONTACTADDRESS3],
[CONTACTCITY], [CONTACTSTATE], [CONTACTPOSTALCODE], [CONTACTCOUNTRY], [CONTACTPHONE], [CONTACTEMAIL], [COMPANYMATCH])
VALUES
(@@IDENTITY , @CONTACTFNAME, @CONTACTLNAME, @CONTACTADDRESS1, @CONTACTADDRESS2, @CONTACTADDRESS3, @CONTACTCITY, @CONTACTSTATE, @CONTACTPOSTALCODE, @CONTACTCOUNTRY, @CONTACTPHONE, @CONTACTEMAIL, @COMPANYMATCH)
RETURN @RecNum
GO

 I've also tried returning parameters like this, with no luck:        InsertCmd.Parameters(32).SqlDbType = SqlDbType.Int        InsertCmd.Parameters(32).Direction = ParameterDirection.ReturnValue        Response.Write("---" & InsertCmd.Parameters(32).Value() & "---")Any help is greatly appreciated! 

View 4 Replies View Related

SQLdatasource Config Wizard Wont Allow Stored Proc As An Option?

Apr 17, 2007

Thanks for your time: 
Using the SQLdatasource wizard, selecting "Specify a custom statement or Stored Procedure", but the "Stored Procedure" radio button and drop box are NOT enabled (greyed out) and cant be selected.  Login has sa and no schema's are implemetned yet... Help?
Is there a setting I'm missing? (like how table needs a primary key set for wizard to allow auto-generate statements)
Otherwise, Is there a work-around I can use?

View 1 Replies View Related

Scope Identity Again...

Mar 6, 2007

I have been using tis page as a reference http://forums.asp.net/thread/1511323.aspxbut i cant seem to get this to work. The above page suggests using Dim newId As Object = e.Command.Parameters("@RETURN_VALUE").Value to get the value but when i do that i get an error that Command is not a member of system.web.ui.webcontrols.formViewInsertedEventArgs Can anyone help?ThanksMatt 

View 1 Replies View Related

Scope Identity

Apr 19, 2008

Hi folks
I'm using a function to create a record on a database, and then I want to return the ID of that record to passinto another function. I believe its scope identity that does this, but I'm not sure how to do it.   public static void putrecordin(string record)    {        SqlCommand cmd = new SqlCommand("insert Table (record) values (@record; scope identity)"        conn.Open();        cmd.Parameters.Add(new SqlParameter("@record", record));        cmd.ExecuteNonQuery();        conn.Close();         another(new-record-ID, anothervalue);     }     public static void another(string new-record-ID)    {do stuff
    }
so you'll see I have a function called putrecordin, and at the end of the sql statment I want to return the id of the new record and pass it into another function called another.
Anyone know how to do this?
 Thanks!

View 6 Replies View Related

XML And Scope Identity

Nov 26, 2007

OK, So I'm Getting some XML Like this


<PifToMepData Mode="3">
<MEP MEPName="Test Combining PIFs" MEPType="Close" PIFRecId="12" IsPrimaryPIF="1"><AssignedTo>X000525</AssignedTo></MEP>
<MEP MEPName="Test Combining PIFs" MEPType="Close" PIFRecId="13" IsPrimaryPIF="0"><AssignedTo>X000525</AssignedTo></MEP>
</PifToMepData>


I then use


INSERT INTO #myTemp99 (
Mode
, MEPName
, MEPType
, PIFRecId
, IsPrimaryPIF
, AssignedTo)
SELECT *
FROM OPENXML (@idoc, './/AssignedTo',3)
WITH(
Mode varchar(20) '../../@Mode'
,MEPName varchar(2000) '../@MEPName'
,MEPType varchar(500) '../@MEPType'
,PIFRecId int '../@PIFRecId'
,IsPrimaryPIF varchar(20) '../@IsPrimaryPIF'
,AssignedTo varchar(20) '.'
)


To Parse it out. This then has to be inserted into another table with an identity column (damn Identity column), and I need to grab the generated id for each row, because then there are other children tables that need to be populated.

Question: Is there any set way to grab multiple generated id's?
Or do I need to loop or use a cursor?

Is the 1% of the time that they are needed?

Any ideas?
Here's the temp table DDL


CREATE TABLE #myTemp99 (
Mode varchar(20)
, MEPName varchar(2000)
, MEPType varchar(500)
, PIFRecId int
, IsPrimaryPIF varchar(20)
, AssignedTo varchar(20))


And the document prep
DECALRE @idoc varchar(8000)
--Just assign the sampel data
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

EXEC sp_xml_removedocument @idoc

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

View 8 Replies View Related

SCOPE IDENTITY Syntax

Jan 28, 2007

Hi,
I've been trying to insert data in a Sql server (.mdf) db and use SCOPE IDENTITY to be able to insert an id in both the parent and the child tables. However, I don't know how to write one of the lines correctly:
Dim MyConn As SqlConnection = New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("myConnectionString").ConnectionString)
Dim MySQL As String = "INSERT INTO Document (fid, serie, vnr, datum, vtext, sign) " & _
"Values (@fid, @serie, @vnr, @datum, @vtext, @sign) Select @verid=SCOPE_IDENTITY()"
Dim Cmd As New SqlCommand(MySQL, MyConn)
With Cmd.Parameters
.Add(New SqlParameter("@fid", fid))
.Add(New SqlParameter("@serie", SerieDropDownList.SelectedValue))
.Add(New SqlParameter("@vnr", vnr))
.Add(New SqlParameter("@datum", datumet))
.Add(New SqlParameter("@vtext", VtextTextBox.Text))
.Add(New SqlParameter("@sign", sign))
.Add(New SqlParameter("@verid", SqlDbType.Int)) 'HERE IS THE LINE THAT SHOULD BE CHANGED (OR SO I THINK)
End With
MyConn.Open()
Cmd.ExecuteNonQuery()
Dim p As SqlParameter = Cmd.Parameters.Add("@verid", SqlDbType.Int)
p.Direction = ParameterDirection.Output
Dim verid = Cmd.Parameters("@verid").Value.ToString
For Each row As GridViewRow In StampelGridView.Rows
Dim kpnr As String = row.Cells(0).Text
Dim kst As String = row.Cells(1).Text
If kst = "&nbsp;" Then kst = ""
Dim projekt As String = row.Cells(2).Text
If projekt = "&nbsp;" Then projekt = ""
Dim debettext As String = "0.00"
Dim kredittext As String = "0.00"
If row.Cells(3).Text.Length > 3 Then debettext = Replace(row.Cells(3).Text, ",", ".")
If row.Cells(4).Text.Length > 3 Then kredittext = Replace(row.Cells(4).Text, ",", ".")
Dim ptext As String = row.Cells(5).Text
If ptext = "&nbsp;" Then ptext = ""
Dim MySQLpost As String = "INSERT INTO Vpost (verid, kpnr, kst, projekt, debet, kredit, ptext) " & _
"Values (@verid, @kpnr, @kst, @projekt, @debet, @kredit, @ptext)"
Dim Cmdpost As New SqlCommand(MySQLpost, MyConn)
With Cmdpost.Parameters
.Add(New SqlParameter("@verid", p)) 'THIS SHOULD CHECK OUT AS WELL...
.Add(New SqlParameter("@kpnr", kpnr))
.Add(New SqlParameter("@kst", kst))
.Add(New SqlParameter("@projekt", projekt))
.Add(New SqlParameter("@debet", debettext))
.Add(New SqlParameter("@kredit", kredittext))
.Add(New SqlParameter("@ptext", ptext))
End With
Cmdpost.ExecuteNonQuery()
Next
MyConn.Close()
In other words, the verid is the id of the new post in the first table. This id should be inserted in the new table as well.
Thank you very much in advance fro helping me out! I've worked a LONG time with this.
Pettrer

View 3 Replies View Related

Get Scope Identity Value Using ObjectDataSource And Vb.Net

Dec 24, 2007

Hi,
I have been trying to get the scope Identity after inserting a record using an ObjectDataSource.
I can't find what event, or how to get the value that the scope identity returns.
Here is my Sproc.

ALTER PROCEDURE dbo.[YourCompany_LanCustomer_Insert]
    (
    @DNNUserID int,
    @FirstName nvarchar(50),
    @LastName nvarchar(50),
    @Address nvarchar(50),
    @Address2 nvarchar(50),
    @City nvarchar(50),
    @State nvarchar(50),
    @Zip nvarchar(50),
    @EmailAddress nvarchar(50),
    @PhoneNumber nvarchar(50),
    @CustomerID int OUTPUT
    )
    AS
INSERT INTO YourCompany_LanCustomer
(DNNUserID, FirstName, LastName, Address, Address2, City, State, Zip, EmailAddress, PhoneNumber, DateEntered)
VALUES (@DNNUserID, @FirstName, @LastName, @Address, @Address2, @City, @State, @Zip, @EmailAddress, @PhoneNumber, getdate())
SET @CustomerID = Scope_Identity()
RETURN   

View 8 Replies View Related

Is @@IDENTITY Really Unique In Multi-sessions Scope

Jul 23, 2005

I have a table EugeneTest(id_num, fname, minit, lname)where field "id_num" is type IDENTITY and has UNIQUE constraintLet's say 2 user execute this code at the same time:DECLARE @return integeruse EugeneTestINSERT employees( fname, minit, lname)VALUES( 'Eugene3', 'F', 'Josephs')SET @return = @@IDENTITYIs is not possible to get duplicated value in id_num column becuase ofUNIQUEconstraint, but is it possible the both user get the same @@IDENTITYnumber( for example, if @@IDENTITY is evaluated and kept somewhere in thebuffer before the data actually get written to the disk )Thanks, Eugene

View 5 Replies View Related

Can You Trace Into A Stored Proc? Also Does RAISERROR Terminate The Stored Proc Execution.

Feb 13, 2008

I am working with a large application and am trying to track down a bug. I believe an error that occurs in the stored procedure isbubbling back up to the application and is causing the application not to run. Don't ask why, but we do not have some of the sourcecode that was used to build the application, so I am not able to trace into the code.
So basically I want to examine the stored procedure. If I run the stored procedure through Query Analyzer, I get the following error message:
Msg 2758, Level 16, State 1, Procedure GetPortalSettings, Line 74RAISERROR could not locate entry for error 60002 in sysmessages.
(1 row(s) affected)
(1 row(s) affected)
I don't know if the error message is sufficient enough to cause the application from not running? Does anyone know? If the RAISERROR occursmdiway through the stored procedure, does the stored procedure terminate execution?
Also, Is there a way to trace into a stored procedure through Query Analyzer?
-------------------------------------------As a side note, below is a small portion of my stored proc where the error is being raised:
SELECT  @PortalPermissionValue = isnull(max(PermissionValue),0)FROM Permission, PermissionType, #GroupsWHERE Permission.ResourceId = @PortalIdAND  Permission.PartyId = #Groups.PartyIdAND Permission.PermissionTypeId = PermissionType.PermissionTypeId
IF @PortalPermissionValue = 0BEGIN RAISERROR (60002, 16, 1) return -3END 
 

View 3 Replies View Related

Variable Scope And Connection Manger Scope

Mar 13, 2008



I have taken three dtsx files and re written them into one each in its own container. I use the XML Task task alot which the File connection is set by a variable and the variable value is evaluated by expression (the expression makes up the path/filename from other variable values). All the variables that make up the connection are at the container scope. The package will not run now because it is saying that the source (created by variables) for the file connection do not exist.

It seems the answer is that file connections exist at the package level therefore the variable has to be at the package level. This seems to be alot of variables i now have to move to package level to generate the XML source connection. Which in essence makes it confusing as to which variables operate in which container.

My question is can we easily move variable scope (Not ideal as we have alot of variables at package level) Or Can we do the same for connection managers as we do for variables and have them only used in a scope? (this will be ideal as some connections only need to be at a container scope)

View 1 Replies View Related

Stored Proc - Calling A Remote Stored Proc

Aug 24, 2006

I am having trouble executing a stored procedure on a remote server. On my
local server, I have a linked server setup as follows:
Server1.abcd.myserver.comSQLServer2005,1563

This works fine on my local server:

Select * From [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.dbo.TableName

This does not work (Attempting to execute a remote stored proc named 'Data_Add':

Exec [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.Data_Add 1,'Hello Moto'

When I attempt to run the above, I get the following error:
Could not locate entry in sysdatabases for database 'Server1.abcd.myserver.comSQLServer2005,1563'.
No entry found with that name. Make sure that the name is entered correctly.

Could anyone shed some light on what I need to do to get this to work?

Thanks - Amos.

View 3 Replies View Related

Stored Proc Question : Why If Exisits...Drop...Create Proc?

Jun 15, 2006

Hi All,Quick question, I have always heard it best practice to check for exist, ifso, drop, then create the proc. I just wanted to know why that's a bestpractice. I am trying to put that theory in place at my work, but they areasking for a good reason to do this before actually implementing. All Icould think of was that so when you're creating a proc you won't get anerror if the procedure already exists, but doesn't it also have to do withCompilation and perhaps Execution. Does anyone have a good argument fordoing stored procs this way? All feedback is appreciated.TIA,~CK

View 3 Replies View Related

ASP Cannot Run Stored Proc Until The Web User Has Run The Proc In Query Analyzer

Feb 23, 2007

I have an ASP that has been working fine for several months, but itsuddenly broke. I wonder if windows update has installed some securitypatch that is causing it.The problem is that I am calling a stored procedure via an ASP(classic, not .NET) , but nothing happens. The procedure doesn't work,and I don't get any error messages.I've tried dropping and re-creating the user and permissions, to noavail. If it was a permissions problem, there would be an errormessage. I trace the calls in Profiler, and it has no complaints. Thedatabase is getting the stored proc call.I finally got it to work again, but this is not a viable solution forour production environment:1. response.write the SQL call to the stored procedure from the ASPand copy the text to the clipboard.2. log in to QueryAnalyzer using the same user as used by the ASP.3. paste and run the SQL call to the stored proc in query analyzer.After I have done this, it not only works in Query Analyzer, but thenthe ASP works too. It continues to work, even after I reboot themachine. This is truly bizzare and has us stumped. My hunch is thatwindows update installed something that has created this issue, but Ihave not been able to track it down.

View 1 Replies View Related

Identity And SqlDataSource Question

Mar 11, 2007

I'm trying to update my e-commerce approach from ADO-heavy code to a more modern approach based on the SqlData Source.  I also want to move away from Stored Procedures for the moment, if I can, though I may return to them later (mainly for educational purposes at the moment). 
In the past I used @@Identity in a stored procedure to return an ID# which I passed on to the end user as their "order number".  Unfortunately this approach seems to only apply to SPs.
In short, what's the best way to handle this using SqlDataSource and minimal ADO code? 
Just to add a bit more detail, I've got basically three tables, in a fairly obvious relationship.  Orders stores the main order info (Customer's name and address, total, etc), OrderDetails lists the line items, and Products contains detail on the items.
(Put another way, the auto-generated tags in the SqlDataSource object either don't include the OrderID param because it's toggled for Identity in the database, or if I toggle Identity off then I don't know how to trigger it to toggle the next number in sequence.  And either way I don't know how to feed that information back to the program.)
Thanks!

View 3 Replies View Related

Returning Identity With SQLDataSource

Mar 25, 2008

Hello all,I have a sqldatasource that inserts data to the database but I want to get the ID from an Identity field after I am done inserting the data.  How can I best do that?  I'm not using a stored procedure to do this.  <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:onepduConnectionString %>"          InsertCommand="INSERT INTO [tblaccounts] ([fname],[lname], [address], [city], [state], [zip], ,[phone],[credits]) VALUES (@fName,@lname, @address, @city, @state, @zip, @email, @phone, @credits)>                                    <InsertParameters>                <asp:FormParameter Name="fname" FormField="FirstNameTextBox" Type="String" />                <asp:FormParameter Name="lname" FormField="LastNameTextBox" Type="String" />                <asp:FormParameter Name="address" FormField="AddressTextBox" Type="String" />                <asp:FormParameter FormField="cityTextBox" Name="city" Type="String" />                <asp:FormParameter FormField="stateTextBox" Name="state" Type="String" />                <asp:FormParameter FormField="zipTextBox" Name="zip" Type="String" />                <asp:FormParameter FormField="emailTextBox" Name="email" Type="string" />                <asp:FormParameter FormField="phoneTextBox" Name="phone" Type="string" />                <asp:Parameter Name="credits" DefaultValue=0 />                                           </InsertParameters>        </asp:SqlDataSource> 

View 2 Replies View Related

Temp Table Scope In Nested Stored Procedures

Jun 8, 2001

We are migrating a SQL 6.5 application with 1900 stored procedures that use 100's of temp tables to SQL 2000.

A problem we have encountered was that we started out getting an "invalid column" errors on certain procedures. Investigation determined that the error was being generated in a nested procedure. The table that caused the error ended up being a temp table that was created using "select into". The following select statement from that temp table gave the invalid column error.

First thinking it was the "Select Into" we then discovered that the outer most procedure had created a temp table of the same name prior to executing the lower level procedure. After the select into, the next statement was a SELECT that went against what it thought was the inner temp table. However, it grabbed the outermost temp table and then couldn't find the appropriate columns and generated the error.

The solution, of course, was to rename the inner most temp table. We also remove the "select into" in the procedure by explicitly creating the temp table.

We tried creating some test procedures to attempt to reproduce this scenario without complete success.

Our test created 3 procedures (sp1 calling sp2 calling sp3) to mimic the current scenario. Sp1 created a temp table and executed sp2, which executed sp3. Sp3 created another temp table using the same name as the one created in sp1.

If we create all three procedures at the same time, it doesn't matter if we change the order in which they are created or whether we create the inner temp table explicitly or with a "select into", SQL Query Analyzer won't let us create the procedure because it finds that the temp table has been declared twice. If we create the procedures separately however, they compile and allow sp3 to create a temp table by the same name as sp1. After creating the procedures independently, they runs properly in all cases with everything in proper scope and no problems.

Admittedly, this is bad coding to start with, but what is happening with the scope of the temp tables within the stored procedures?

Thanks,
Glen Smith

View 1 Replies View Related

Question With SQLDATASOURCE And User.identity.name

Feb 20, 2007

is there a way that I can use the sqldatasource with a form view where my sqldatasource select statement is like this
select * from tblUsers where vcUserName=@vcUserName
<selectparameters>
<asp:Parameter Name="user.identity.name" Type="String />
</selectParameters>

View 1 Replies View Related

No Way To Pass Identity Back To SqlDataSource?

Mar 13, 2007

Is there no way to pass identity info back through SqlDataSource?  You can only do that with ADO.NET code?
In other words, if I want to run a complex INSERT statement to a table that uses Identity, I can't take that key value back through something like SCOPE_IDENTITY() and use it? 
I know how to do this with ADO code, but I can't figure out how to do it purely with SqlDataSource.  I was hoping to do this without having to write a new Insert statement -- just using the one that's already in the SqlDataSource control.  But there doesn't seem to be any facility for Identity in there.  I tried embedding a separate select statement after the insert statement and a semi-colon, but that didn't seem to do anything.
Thanks!
 

View 5 Replies View Related

Using @@IDENTITY In The Sqldatasource Insert Command

Jan 11, 2008

I apologise if i have not posted this in the correct Topic before i start. But was uncertain where to post this query.
This is my first project in ASP.NET, MS Visual Web Developer 2005 Express and SQL Server 2005 Express. I have relatively little experience, so please bare with me.
I have managed to create a form that inserts data into a table and then inserts the Automatically Created Primary Key(as a foreign key) in another table. I have done this by inserting what is highlighted in red in the code of my InsertCommand below (Please scroll across to the end of the code):-InsertCommand="INSERT INTO [PrinterModel] ([Model], [PrinterMakeID], [CartridgeCode], [PartCode], [Duplex], [NIC], [Wireless], [Parallel], [USB], [Colour], [PrinterTypeID]) VALUES (@Model, @PrinterMakeID, @CartridgeCode, @PartCode, @Duplex, @NIC, @Wireless, @Parallel, @USB, @Colour, @PrinterTypeID) INSERT INTO [Model] ([PrinterModelID],[TypeID]) VALUES (@@IDENTITY, 3)" Can you see any problems that may arise from using this method. This project is an Asset Management System and will be used by no more than a handful of users. My Concern is the use of the @@IDENTITY (As it only stores the last Key used). Should I be using it here? If there is more than one user inserting into tables (Chances of this happening are very low), will the correct Primary key be insert to the table in the above code?Thank you for your comments.

View 2 Replies View Related

HttpContext.Current.User.Identity.Name Form SQLDataSource

Apr 21, 2007

Hi,How do I get hold of the HttpContext.Current.User.Identity.Name from within an sqldatasource? I want to create a WHERE clause that says WHERE UserName = @UserName and the @UserName parameter be filled with the current user name. I would be able to do this in code, but I don't know how to get at this information from within a datasource. At a guess I tried to use the Cookie option from the drop down list, and use the name of the FormsAuthentication Cookie, but this didn't work. Is there a way I can do this? If so how? I don't want to resort to using a hidden field or anything like that if I can help it. Many thanks, Steve 

View 2 Replies View Related

Calling A Stored Proc From Within Another Stored Proc

Feb 20, 2003

I have seen this done by viewing code done by a SQL expert and would like to learn this myself. Does anyone have any examples that might help.

I guess I should state my question to the forum !

Is there a way to call a stored proc from within another stored proc?

Thanks In Advance.

Tony

View 1 Replies View Related

Stored Proc Calls Another Stored Proc

Jan 13, 2006

Hi all,

I have a stored procedure "uspX" that calls another stored procedure "uspY" and I need to retrieve the return value from uspY and use it within uspX. Does anyone know the syntax for this?

Thanks for your help!
Cat

View 5 Replies View Related

Calling Stored Proc B From Stored Proc A

Jan 20, 2004

Hi all

I have about 5 stored procedures that, among other things, execute exactly the same SELECT statement

Instead of copying the SELECT statement 5 times, I'd like each stored proc to call a single stored proc that executes the SELECT statement and returns the resultset to the calling stored proc

The SELECT statement in question retrieves a single row from a table containing 10 columns.

Is there a way for a stored proc to call another stored proc and gain access to the resultset of the called stored proc?

I know about stored proc return values and about output parameters, but I think I am looking for something different.

Thanks

View 14 Replies View Related

Calling T SQL Stored Proc From CLR Stored Proc

Aug 30, 2007

I would like to know if the following is possible/permissible:

myCLRstoredproc (or some C# stored proc)
{
//call some T SQL stored procedure spSQL and get the result set here to work with

INSERT INTO #tmpCLR EXECUTE spSQL
}

spSQL
(

INSERT INTO #tmpABC EXECUTE spSQL2
)


spSQL2
(
// some other t-sql stored proc
)


Can we do that? I know that doing this in SQL server would throw (nested EXECUTE not allowed). I dont want to go re-writing the spSQL in C# again, I just want to get whatever spSQL returns and then work with the result set to do row-level computations, thereby avoiding to use cursors in spSQL.

View 2 Replies View Related

Execute Stored Procedure Y Asynchronously From Stored Proc X Using SQL Server 2000

Oct 14, 2007

I am calling a stored procedure (say X) and from that stored procedure (i mean X) i want to call another stored procedure (say Y)asynchoronoulsy. Once stored procedure X is completed then i want to return execution to main program. In background, Stored procedure Y will contiue his work. Please let me know how to do that using SQL Server 2000 and ASP.NET 2.

View 3 Replies View Related

How Can I Call One Or More Stored Procedures Into Perticular One Stored Proc ?

Apr 23, 2008

Hello friends......How are you ? I want to ask you all that how can I do the following ?
I want to now that how many ways are there to do this ?



How can I call one or more stored procedures into perticular one Stored Proc ? in MS SQL Server 2000/05.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved