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
ADVERTISEMENT
May 29, 2008
Hi All :-)
I'm trying to return the identity from a stored procedure but am getting the error - "specified cast is invalid" when calling my function.
Here is my stored procedure - ALTER Procedure TM_addTalentInvite @TalentID INT
AS
Insert INTO TM_TalentInvites
(
TalentID
)
Values
(
@TalentID
)
SELECT @@IDENTITY AS TalentInviteID
RETURN @@IDENTITY
And here is my function - public static int addTalentInvite(int talentID)
{
// Initialize SPROCstring connectString = "Data Source=bla bla";
SqlConnection conn = new SqlConnection(connectString);SqlCommand cmd = new SqlCommand("TM_addTalentInvite", conn);
cmd.CommandType = CommandType.StoredProcedure;
// Update Parameterscmd.Parameters.AddWithValue("@TalentID", talentID);
conn.Open();int talentUnique = (int)cmd.ExecuteScalar();
conn.Close();return talentUnique;
}
Any help you can give me will be greatly appreciated thanks!
View 3 Replies
View Related
Jun 21, 2008
I have a page that inserts customers into the client database. After the data is inserted it redirects to the customer's policy page using the customer's ID set by @@Identity.
The SQL Command is:
ALTER PROCEDURE [dbo].[AddBasic]
(
@ln NVarchar(50),
@fn NVarchar(50),
@mAdd NVarchar(50),
@mCity NVarchar(50),
@mState NVarchar(50),
@mZip NVarchar(50),
@pAdd NVarchar(50),
@pCity NVarchar(50),
@pState NVarchar(50),
@pZip NVarchar(50),
@sAdd NVarchar(50),
@sCity NVarchar(50),
@sState NVarchar(50),
@sZip NVarchar(50),
@hPhone NVarchar(50),
@cPhone NVarchar(50),
@wPhone NVarchar(50),
@oPhone NVarchar(50),
@eMail NVarchar(50),
@DOB NVarchar(50),
@SSN NVarchar(50),
@liState NVarchar(50),
@liNum NVarchar(50),
@acctSource NVarchar(50),
@active NVarchar(50),
@County NVarchar(50)
)
AS
DECLARE @custNum int
INSERT basicInfo
(lastName, firstName, mailingAddress, mailingCity, mailingState, mailingZip, physicalAddress, physicalCity, physicalState, physicalZip,
seasonalAddress, seasonalCity, seasonalState, seasonalZip, homePhone, cellPhone, workPhone, otherPhone, email, DOB, SSN, liscenceState,
driverLiscense, acctSource, [status], county)
VALUES (@ln,@fn,@mAdd,@mCity,@mState,@mZip,@pAdd,@pCity,@pState,@pZip,@sAdd,@sCity,@sState,@sZip,@hPhone,@cPhone,@wPhone,@oPhone,@eMail,@DOB,@SSN,@liState,@liNum,@acctSource,
@active, @County)
SET @custNum = @@Identity
SELECT @custNum
The ASPX page has two parts, the SQL Data Source: <asp:SqlDataSource ID="sqlInsertCustomer" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
InsertCommand="AddBasic"
SelectCommand="SELECT acctNumber FROM basicInfo WHERE (acctNumber = 5)" InsertCommandType="StoredProcedure">
<InsertParameters>
<asp:FormParameter FormField="lName" Name="ln" />
<asp:FormParameter FormField="fName" Name="fn" />
<asp:FormParameter FormField="mAdd" Name="mAdd" />
<asp:FormParameter FormField="mCity" Name="mCity" />
<asp:FormParameter FormField="mState" Name="mState" />
<asp:FormParameter FormField="mZip" Name="mZip" />
<asp:FormParameter FormField="pAdd" Name="pAdd" />
<asp:FormParameter FormField="pCity" Name="pCity" />
<asp:FormParameter FormField="pState" Name="pState" />
<asp:FormParameter FormField="pZip" Name="pZip" />
<asp:FormParameter FormField="sAdd" Name="sAdd" />
<asp:FormParameter FormField="sCity" Name="sCity" />
<asp:FormParameter FormField="sState" Name="sState" />
<asp:FormParameter FormField="sZip" Name="sZip" />
<asp:FormParameter FormField="hPhone" Name="hPhone" />
<asp:FormParameter FormField="cPhone" Name="cPhone" />
<asp:FormParameter FormField="wPhone" Name="wPhone" />
<asp:FormParameter FormField="oPhone" Name="oPhone" />
<asp:FormParameter FormField="eMail" Name="eMail" />
<asp:FormParameter FormField="DOB" Name="DOB" />
<asp:FormParameter FormField="SSN" Name="SSN" />
<asp:FormParameter FormField="dlState" Name="liState" />
<asp:FormParameter FormField="dlNum" Name="liNum" />
<asp:FormParameter FormField="aSource" Name="acctSource" />
<asp:FormParameter FormField="txtCounty" Name="County" />
<asp:Parameter Name="active" DefaultValue="Active" Type="String" />
</InsertParameters>
</asp:SqlDataSource>
and the redirect:Public Sub Redirect()
Dim x As Integer
x = SqlDataSource1.Insert()
Response.Redirect("~/Main/Main.aspx?q=" & x)
End Sub
View 2 Replies
View Related
Jun 23, 2004
I have a stored procedure that inserts a record. I call the @@Identity variable and assign that to a variable in my SQL statement in my asp.net page.
That all worked fine when i did it just like that. Now I'm using a new stored procedure that inserts records into 3 tables successively, and the value of the @@Identity field is no longer being returned.
As you can see below, since I don't want the identity field value of the 2 latter records, I call for that value immediately after the first insert. I then use the value to populate the other 2 tables. I just can't figure out why the value is not being returned to my asp.net application. Think there's something wrong with the SP or no?
When I pass the value of the TicketID variable to a text field after the insert, it gives me "@TicketID".
Anyone have any ideas?
CREATE PROCEDURE [iguser].[newticket]
(
@Category nvarchar(80),
@Description nvarchar(200),
@Detail nvarchar(3000),
@OS nvarchar(150),
@Browser nvarchar(250),
@Internet nvarchar(100),
@Method nvarchar(50),
@Contacttime nvarchar(50),
@Knowledge int,
@Importance int,
@Sendcopy bit,
@Updateme bit,
@ClientID int,
@ContactID int,
@TicketID integer OUTPUT
)
AS
INSERT INTO Tickets
(
Opendate,
Category,
Description,
Detail,
OS,
Browser,
Internet,
Method,
Contacttime,
Knowledge,
Importance,
Sendcopy,
Updateme
)
VALUES
(
Getdate(),
@Category,
@Description,
@Detail,
@OS,
@Browser,
@Internet,
@Method,
@Contacttime,
@Knowledge,
@Importance,
@Sendcopy,
@Updateme
)
SELECT
@TicketID = @@Identity
INSERT INTO Contacts_to_Tickets
(
U2tUserID,
U2tTicketID
)
VALUES
(
@ContactID,
@TicketID
)
INSERT INTO Clients_to_Tickets
(
C2tClientID,
C2tTicketID
)
VALUES
(
@ClientID,
@TicketID
)
View 2 Replies
View Related
Sep 4, 2007
I am creating an ad-hoc reporting module for my website. The user may select a list of columns and specify the WHERE for the report. I build the select statement and the where statement and pass the info to a stored procedure. The stored procedure accepts 3 select parameters and a 3 wherestmt parameters. I finally got the SQLDatasource to work by setting the parameter values in the Selecting method of the SQLDatasource. Binding it to a gridview shows me all my values. Now, I am trying to programmatically call the select method of the SQLDatasource so I can get a dataview or datareader to manipulate further. I am getting an null back when I call the select method. Any ideas why? or a workaround?
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:AssetMgmtRWConnectionString %>"SelectCommand="getAdhocRptData" SelectCommandType="StoredProcedure" DataSourceMode="DataReader">
<SelectParameters>
<asp:Parameter Name="strSelect" Type="String" />
<asp:Parameter Name="strSelect2" Type="String" />
<asp:Parameter Name="strSelect3" Type="String" />
<asp:Parameter Name="strWhere" Type="String" />
<asp:Parameter Name="strWhere2" Type="String" />
<asp:Parameter Name="strWhere3" Type="String" />
<asp:Parameter Name="bitDebug" Type="Boolean" />
</SelectParameters>
</asp:SqlDataSource>
<VB Code>Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
'Getting an error that the object is null? why is the above code not returning any data?
'Tried using a datareader and datasource.Dim rptview As IDataReader = CType(SqlDataSource1.Select(DataSourceSelectArguments.Empty), IDataReader)
If rptview.Read Then
lblMsg.Text = "Got Rows"
End If
End Sub
Protected Sub SqlDataSource1_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) Handles SqlDataSource1.Selecting
'For this sp, need to set each paramater even if it is not being used. Setting it in the wizard
'does not work. Need to be set programmatically.
e.Command.Parameters("@strSelect").Value = "p.field1, p.field2 "
e.Command.Parameters("@strSelect2").Value = "s.field1, s.field2"
e.Command.Parameters("@strSelect3").Value = "a.field1, a.field2, a.field4, a.field5"
e.Command.Parameters("@strWhere").Value = "p.field3 is not null and a.field5 is null"
e.Command.Parameters("@strWhere2").Value = "'"
e.Command.Parameters("@strWhere").Value = ""
e.Command.Parameters("@bitdebug").Value = False
End Sub
thanks for any help.
View 2 Replies
View Related
Oct 10, 2007
TotalSelected.Value = SqlDataSource1.SelectCommand = "SELECT COUNT(*) FROM tblNews";
the reason i am tring to do this is so if i can find out the amount of rows before sqldatasource selects for details view then i can make the sqldataesource select depends on total minus 5 so e.g. if total 200 then - 5 so i can select bottom 195 so it misses top 5 for details view any1 any ideas?
Thanks Andy,
View 5 Replies
View Related
Jun 9, 2008
Does anyone know why would SQLDataSource.Update method return a negative value even though its updating records correctly?Its happening with .NET 2.0.
View 2 Replies
View Related
Apr 5, 2008
Hi All: I have what I'm sure is a common scenario...I have a table to track pageviews of a form, and which also tracks when a person viewing the form submits it.
The table has three fields: an INT identity/PK field, a DATETIME (default getdate()) field, and a BIT field with default "false".
When the page is viewed, I insert a record into the dB:
Protected Sub Page_load(ByVal src As Object, ByVal e As EventArgs)
conn = New SqlConnection("Server=myserver;Database=mydb;User ID=user;Password=password;Trusted_Connection=false;")
If Not IsPostBack Then
AddTrack()
End If
End Sub
Sub AddTrack()
Dim myCommand As SqlCommand
Dim insertTrack As String
insertTrack = "Insert PageTracker (submitted) Values (0)"myCommand = New SqlCommand(insertTrack, conn)
myCommand.Connection.Open()
Try
myCommand.ExecuteNonQuery()tempTxt.Text = "<br>Ticked</b><br>" & insertTrack
Catch ex As SqlException
tempTxt.Text = ex.Number.ToString()
End Try
myCommand.Connection.Close()
End Sub
And if I view the page, the record is inserted into the table. But now I need to know the value of the identity field, so when the form is submitted, I can update the field "submitted" from "0" to "1".
The way I would do it in ASP is to add a "SELECT @@identity" to the query, and get the value using RS.nextrecordset. How would I do this in .NET? or is there a better way for me to do this?
View 6 Replies
View Related
Jun 9, 2004
I am currently using IDENT_CURRENT to return the Id of a new row in SQL 2000, but I am looking for a similar way to do this in SQL 7. Ihave no experience with SQL 7
Anyone remember how they did this ?
View 1 Replies
View Related
Oct 17, 2004
I am inserting a record by calling a stored procedure in my asp.net code. I need to return the identity field from the insert to use elsewhere in my code. I have found many things regarding this but nothing has worked.
Here is my code that works for the insert....
conClasf.Open()
strSQLInsert = "Exec spInsNewCAR '" _
& calDateInitiatedPopup.SelectedDate _
& "', '" & calResponseDatePopup.SelectedDate _
& "', '" & elbPartName.Selectedtext _
& "', '" & elbPartName.Item(elbPartName.SelectedValue).Text2 _
& "', '" & calSupplyDatePopup.SelectedDate _
& "', '" & elbMRBApproval3.Selectedtext _
& "'"
cmdAd = New OleDbCommand(strSQLInsert, conClasf)
cmdAd.ExecuteNonQuery( )
conClasf.Close
Here is the last part of my stored procedure. BTW, I have added Scope_Identity to the end of my sp and when I open my sp up, it is not there...not sure what that is about. I guess my main problem is what command do I use in ASP.net to execute the sp AND hold my returned value...
Thanks,
JOE
View 7 Replies
View Related
Jun 12, 2006
Hi all i'm trying to get the identity field after inserting into db, what am i doing wrong? thanks a lot
my sproc:
CREATE PROCEDURE ng_AddCotacao(...@Codigo_cotacao int OUTPUT)ASBEGINSET NOCOUNT ONINSERT INTONegocios_cotacoes(...)VALUES(...)SELECT @Codigo_cotacao=SCOPE_IDENTITY()SET NOCOUNT OFFENDGO
class file
public class Cotacoes
{
public int codigoCotacao;
}
public class CotacaoAtualiza { public Cotacoes cotacoes = new Cotacoes();
public CotacaoAtualiza() { }
public void AdicionarCotacao( ... ) { SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["stringConexao"]); SqlCommand myCommand = new SqlCommand("ng_AddCotacao", myConnection);
myCommand.CommandType = CommandType.StoredProcedure;
...
SqlParameter paramCodigo_cotacao = new SqlParameter("@Codigo_cotacao", SqlDbType.Int, 4); paramCodigo_cotacao.Direction = ParameterDirection.Output; myCommand.Parameters.Add(paramCodigo_cotacao);
...
myConnection.Open(); SqlDataReader result = myCommand.ExecuteReader();
while(result.Read()) { this.cotacoes.codigoCotacao = (int) result["@Codigo_cotacao"]; }
myConnection.Close(); }
calling into code-behind file:
CotacaoAtualiza ca = new CotacaoAtualiza();
Cotacoes cotacoes = ca.cotacoes;
Response.Redirect("Cotacao_confirma.aspx?cotacao=" + cotacoes.codigoCotacao);
View 1 Replies
View Related
Feb 22, 1999
Hi,
I have upgraded database from sql6.5 to sql 7.o
I have a table tblnetwork on which identity property
is defined on column networkid.
When I issue the following command new record is getting inserted into
tblnetwork and identity column is getting incremented properly.
But, I am not able to get the @@identity value
It is returned as NULL.
insert tblnetwork(formalname,networktype)
values ("name2",11897)
select @@identity
I need this value to insert into some other table.
Pls suggest me why @@identity is returning NULL
thanks,
MMS
View 5 Replies
View Related
Aug 17, 2000
Hello...
This group has been really helpful on a couple of recent qwuestions I have posted - thanks to all!
Now, I have another question. I have a table set up to automatically generate a uniquie number when a record is added(INT NOT NULL IDENTITY).
Now, when this number is generated, I need it returned back to my perl script (thorough DBD::ODBC). Any ideas how to do that?
Thnaks....
View 2 Replies
View Related
Nov 20, 2006
Hi All,
I have a createStudent SProc. When this is called it calls a createContact SProc. This in turn calls a Create Address Stored procedure. Now when I create the address, an Identity column automatically creates a new Identity for me. I need to return this value back to the calling stored procedure. I know how to return a value and all, however, how to I get the identity just entered (the corresponding identity) reliably? I know that select MAX <Identity> could suffice in this case, but is flawed as if someone was to add another object near the same time we could get the wrong identity returned...
Thanks heaps
Chris
View 6 Replies
View Related
Oct 28, 2005
I am storing product information in a SQL Server database table; the product information has no unique fields so I have created an Identity field called ‘uid’. Is there a way of querying the table to find out what value will be given to the next ‘uid’ field before the next record is written to the table? I need to use this as a FK in other tables.
View 3 Replies
View Related
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
Feb 2, 2006
Hello, I have a C# application that adds records to a SQL Server database using a query something like this one:
INSERT INTO table_name
(first_name, last_name, date_added) ('john', 'smith', '1/1/2005 12:00:00pm') ;
SELECT SCOPE_IDENTITY() AS [Scope_Identity]
This works fine unless there's already a John Smith in the database. When that happens, Scope_Identity is null even though the date_added is different. About half the time the record is added even though Scope_Identity is null. I've added code to notify me when this happens, but it's a pain in the neck to re-run my import utility for individual records.
(The table I'm adding to does have a autonumbered key field)
Thanks in advance!
View 1 Replies
View Related
Oct 2, 2007
Hi,
Hope you guys won't mind this rather newbie question. I'm writing a simple blog page for my website and have created a SqlDataSource which queries the database for a list of blog post titles (from the web.Blog table) and the number of comments (from the web.BlogComments table). The SqlDataSource control is:
<asp:SqlDataSource ID="sourceBlogArticles" ProviderName="System.Data.SqlClient" connectionString="<%$ ConnectionStrings:myDatabase %>" runat="server" SelectCommand="SELECT gb.blogID, gb.title, gb.description, gb.tags, gb.dateAdded, COUNT(gbc.blogID) AS noOfComments FROM web.Blog gb LEFT OUTER JOIN web.BlogComments gbc ON gb.blogID = gbc.blogID GROUP BY gb.blogID, gb.title, gb.description, gb.tags, gb.dateAdded ORDER BY gb.dateAdded"></asp:SqlDataSource>
This works perfectly well if each blog entry in the web.Blog table has associated comments in the web.BlogComments table. However, if there are no comments yet defined in the web.BlogComments table for that blogID then no row is returned in ASP.Net (as checked with a GridView control or similar linked to the data source to view what I get)
HOWEVER, I think the SELECT command IS correct: if I use the select command as a query in SQL Server Managment Studio Express, I do get the rows returned, with 0 for the number of comments which is what I would expect for that query:
blogID, title, description, tags, dateAdded, noOfComments
1, title 1, description for title 1, tag1, 2007-09-27 06:49:03.810, 32, title 2, description for title 2, tag2, 2007-09-27 06:49:37.513, 03, title 3, description for title3, tag3, 2007-10-02 18:21:30.467, 0
Can anyone help? The result from the SSMSE query is what I want, yet when I use the very same SELECT statement in my SqlDataSource I don't get any rows returned if the BlogComment count is zero (in the above example I get only the first row). Many thanks for any suggestions!
View 6 Replies
View Related
Dec 9, 2005
Hi, and thanks in advance.
I have VWD 2005 Express and SQL 2005 Express running.
I have a SqlDastasource wired to the stored procedure. When I only include one Control parameter I get results from my Stored procedure, when I inclube both Control Parameters I get no results. I manually remove the second control parameter from the sqldatasource by deleting...
<asp:ControlParameter ControlID="ddlSClosed" DefaultValue="" Name="SClosed" PropertyName="SelectedValue" Type="String" />
I have one Radio Group and one dropdownlist box that supplies the parameters. The dropdownlist parameter is Null or "" when the page is first loaded.
Below is my SQLDatasource and Stored procedure. I am new to Stored Procedures, so be gentle.
Any help would be appreciated!
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Data for DatabaseSQLConnectionString %>"
ProviderName="<%$ ConnectionStrings:Data for DatabaseSQLConnectionString.ProviderName %>"
SelectCommand="spDisplayServiceOrders" SelectCommandType="StoredProcedure" OnSelecting="SqlDataSource1_Selecting" EnableCaching="True" cacheduration="300" OnSelected="SqlDataSource1_Selected">
<SelectParameters>
<asp:ControlParameter ControlID="RadioButtonList1" ConvertEmptyStringToNull="False"
DefaultValue="2005-11-1" Name="SDate_Entered" PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="ddlSClosed" DefaultValue="" Name="SClosed" PropertyName="SelectedValue" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
ALTER PROCEDURE [dbo].[spDisplayServiceOrders]
(
@SDate_Entered SmallDateTime,
@SClosed nvarchar(50)= NULL
)
AS
If @SClosed IS NULL
BEGIN
SELECT Service_Orders.SStore_Assigned_Number, Store_Info.Store_Other, Service_Orders.PO_Number, Service_Orders.SWorkType, Service_Orders.Service_Order_Number, Service_Orders.SDate_Entered, Service_Orders.SContact, Service_Orders.SClosed FROM Service_Orders INNER JOIN Store_Info ON Service_Orders.Store_ID = Store_Info.Store_ID
and SDate_Entered >= @SDate_Entered
Order by SDate_Entered DESC
END
ELSE
BEGIN
SELECT Service_Orders.SStore_Assigned_Number, Store_Info.Store_Other, Service_Orders.PO_Number, Service_Orders.SWorkType, Service_Orders.Service_Order_Number, Service_Orders.SDate_Entered, Service_Orders.SContact, Service_Orders.SClosed FROM Service_Orders INNER JOIN Store_Info ON Service_Orders.Store_ID = Store_Info.Store_ID
and SDate_Entered >= @SDate_Entered
and SClosed = @SClosed
Order by SDate_Entered DESC
END
View 2 Replies
View Related
Jan 24, 2005
Hi ,,
How to write the Sql Query to return the next generated Identity from the Sql server database.
View 1 Replies
View Related
Oct 5, 2006
i recently found a little error in a stored procedure that was included in a project handed over to me....
the sp was rather simple. it just inserted a record into a table and returned the identity and the timestamp as follows
IF @@ERROR>0
BEGIN
SELECT @int_InterventionID = 0
RETURN @@ERROR
END
ELSE
BEGIN
SELECT @int_InterventionIDReturned = MAX(InterventionID) FROM tblIntervention
SELECT @ts_TimestampReturned = [Timestamp] FROM tblIntervention WHERE InterventionID = @int_InterventionIDReturned
SELECT @int_InterventionID = @int_InterventionIDReturned, @ts_Timestamp = @ts_TimestampReturned
RETURN 0
END
i figured that it should be using @@Identity for the interventionIdentity rather than max(InterventionID)
so i changed to...
IF @@ERROR>0
BEGIN
SELECT @int_InterventionID = 0
RETURN @@ERROR
END
ELSE
BEGIN
SELECT @int_InterventionIDReturned = @@IDENTITY
SELECT @ts_TimestampReturned = [Timestamp] FROM tblIntervention WHERE InterventionID = @int_InterventionIDReturned
SELECT @int_InterventionID = @int_InterventionIDReturned, @ts_Timestamp = @ts_TimestampReturned
RETURN 0
END
it returns the @int_InterventionIDReturned but the timestamp now comes back as null??? why??
how can i ensure that i always get the timestamp of the record it has just inserted
any help greatly appreciated,
Cheers,
Craig
View 3 Replies
View Related
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
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
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
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
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
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
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 newTktNumIf Page.IsValid ThenDim it3Conn As SqlDataSource = New SqlDataSource()it3Conn.ConnectionString = ConfigurationManager.ConnectionStrings("ConnectionString1").ToStringit3Conn.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 IfMsgBox("Ticket Number: " & newTktNum & " has been created", MsgBoxStyle.OkOnly, "Ticket Created")End Sub
View 2 Replies
View Related
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
Sep 25, 2007
I am opening a simple command against a view which joins 2 tables, so that I can return a column which is defined as a tinyint in one of the tables. The SELECT looks like this:
SELECT TreatmentStatus FROM vwReferralWithAdmissionDischarge WHERE ClientNumber = 138238 AND CaseNumber = 1 AND ProviderNumber = 89
The TreatmentStatus column is a tinyint. When I execute that above SQL SELECT statement in SQL Server Management Studio (I am using SQL Server 2005) I get a value of 2. But when I execute the same SQL SELECT statement as a part of a SqlDataReader and SqlCommand, I get a return data type of integer and a value of 1.
Why?
View 5 Replies
View Related
Jul 23, 2007
What is the C# code I use to do this?
I'm guessing it should be fairly simple, as there is only one row selected. I just need to pull out a specific field from that row and then insert that value into a different SqlDataSource.
View 7 Replies
View Related
Jan 10, 2007
I hvae a stored procedure that has this at the end of it:
BEGIN
EXEC @ActionID = ActionInsert '', @PackageID, @AnotherID, 0, ''
END
SET NOCOUNT OFF
SELECT Something
FROM Something
Joins…..
Where Something = Something
now, ActionInsert returns a Value, and has a SELECT @ActionID at the end of the stored procedure.
What's happening, if that 2nd line that I pasted gets called, 2 result sets are being returned. How can I modify this SToredProcedure to stop returning the result set from ActionINsert?
View 2 Replies
View Related
Jul 9, 2006
While I have learned a lot from this thread I am still basically confused about the issues involved.
.I wanted to INSERT a record in a parent table, get the Identity back and use it in a child table. Seems simple.
To my knowledge, mine would be the only process running that would update these tables. I was told that there is no guarantee, because the OLEDB provider could write the second destination row before the first, that the proper parent-child relationship would be generated as expected. It was recommended that I create my own variable in memory to hold the Identity value and use that in my SSIS package.
1. A simple example SSIS .dts example illustrating the approach of using a variable for identity would be helpful.
2. Suppose I actually had two processes updating these tables, running at the same time. Then it seems the "variable" method will also have its problems. Is there a final solution other than locking the tables involved prior to updating them or doing something crazy like using a GUID for the primary key!
3. We have done the type of parent-child inserts I originally described from t-sql for years without any apparent problems. (Maybe we were just lucky.) Is the entire issue simply a t-sql one or does SSIS add a layer of complexity beyond t-sql that needs to be addressed?
TIA,
Barkingdog
View 10 Replies
View Related
