How To Get The Last Item Added
Dec 14, 2004
Not 100% sure how to do this so I would appreciate some directions. I have 2 tables, Systems and Contacts. A system can have 1 to infinite contacts and contact can have 1 to infinite systems. So I use a 3rd table Sys_Con to add the contact needed for each system.
Now my question is once I add the System and contact how do I know for sure which ContactID to add in my Sys_Con table? Because while I am doing this operation maybe someone can add an other contact. So is there a way for my store proc Add_Contact to return the contactID needed for my store proc Add_Sys_Con ?
TABLE Sys_Con
SystemID int
ContactID int
Table Contact
ContactID int Identity
ContactName
...
Table System
SystemID
...
View 3 Replies
ADVERTISEMENT
Jul 10, 2007
I am getting this error when clicking-through in Report Builder on a report using a report model as the data source -
An item with the same key has already been added.
I searched for the error but came up empty in the SSRS space. Does anyone out there know when this is generated and where I need to look in my model/properties to see what I have set incorrectly?
A little background/detail - It's a simple report against two fields (type & #count) in one entity (customer) in a (guessing here) medium sized model (~16 transactional/fact entities, ~40 lookup entities, ~15 what I am calling domain entities). The entity has 5 DefaultDetailAttributes, one Identifying. The report has 2 fields type (a lookup role) and #customers in the data area (so I can click it to test the click through). Then I click the # Customers to look at the detail for those X customers and after a long while and no queries against the DB (I have profiler running) I get this error.
Thanks in advance for the help.
View 1 Replies
View Related
Jun 6, 2007
I am developing a small project that converts a dataset to an equivalent structure in SqlCe. The application converts every field, table and constraints to SqlCe, decomposing de dataset structure into Sql sentences.
The application works fine, the tables and constraints are reconstructed without error. but I found some issues:
After conversion, the usual test is to connect to SqlCe, using Data->Add New Data Source and generate a dataset from the resulting tables.
I found that some tables throw the following message:
Failed to add relation(s).An item with the same key has already been added.
After that, the dataset returned contains the tables, but not the relations.
After some trial I found that Data Source Configuration Wizard (DSCW) has some problem with foreign keys using Primary keys with multiple fields. If I drop that foreign keys, DSCW inferes the dataset without problem.
Interesting note: I am able to convert the dataset to an Sql Server using the same Sql sentences. If I use Data Source Configuration Wizard I have no errors.
Any advise to understand of fix this problem will be aprecciated.
Is that a bug from DSCW ?
Thanks
Fernando
View 2 Replies
View Related
Jun 1, 2004
Hi there,
I have a search page that redirects to another page transfering the search terms in a querystring. In the page to display the search results I use SQL parameters to show the results. Everything runs fine until I do a page refresh or use paging to bring up another set of results.
Everytime I try to refresh/page etc. I get the following error:
Item has already been added. Key in dictionary: "@CityTown" Key being added: "@CityTown"
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.ArgumentException: Item has already been added. Key in dictionary: "@CityTown" Key being added: "@CityTown"
Source Error:
Line 20:
Line 21: 'Creates parameters for the WHERE statement taking the querystring contents from previous page
Line 22: dsPropertyListing.Parameters.Add("@CityTown", CityTown)
Line 23: dsPropertyListing.Parameters.Add("@Suburb", Suburb)
Line 24: dsPropertyListing.Parameters.Add("@Rooms", Rooms)
Source File: K:detailsdetailspropertylisting.aspx Line: 22
Stack Trace:
[ArgumentException: Item has already been added. Key in dictionary: "@CityTown" Key being added: "@CityTown"]
System.Collections.Hashtable.Insert(Object key, Object nvalue, Boolean add) +931
System.Collections.Hashtable.Add(Object key, Object value) +11
ASP.detailspropertylisting_aspx.Page_Load(Object sender, EventArgs e) in K:detailsdetailspropertylisting.aspx:22
System.EventHandler.Invoke(Object sender, EventArgs e) +0
System.Web.UI.Control.OnLoad(EventArgs e) +55
System.Web.UI.Control.LoadRecursive() +27
System.Web.UI.Page.ProcessRequestMain() +731
Any sugestions that anyone could provide would be greatly appreciated. This error obviously renders the search features useless.
Thanks in anticipation,
TCM
View 3 Replies
View Related
Apr 23, 2008
Hi Team Expert,
I am working on an ASP.NET and c# project. I am creating a page that allow user to add new Workitem to TFS and successfully do so. Now I would like to implement the way to email to notify the user that the new WI is added €¦. Does anyone know how to do this or have an example of how to implement that.
Any help would be greatly appreciated.
Thanks so much
Ddee
View 1 Replies
View Related
Dec 6, 2013
I have a BOM table with all finished item receipes and semi items recipes. create a query where semi item materials are also listed in finished item recipe.
View 5 Replies
View Related
Mar 6, 2007
Hi,
Here's my problem. I have 2 tasks defined in my Control Flow tab:
EXECUTE SQL--------->EXECUTE DTS 2000 PACKAGE
When I attempt to run it, by right-clicking the EXECUTE SQL task, and selecting "Execute Task", it only runs the EXECUTE SQL part (successfully), and does not "kick off" the EXECUTE DTS 2000 PACKAGE, after it is done running (even though it completes successfully, as shown by the green box).
Yes, they are connected by a dark green arrow, as indicated in my diagram above.
Why is this?? Am I missing something here? Need help.
THANKS
View 3 Replies
View Related
Jul 12, 2007
Hello,Basically, I have a table with 2 fieldsId item#1 33332 33333 22224 22225 22226 33337 33338 3333I would like to only select the last identical Item# which in this case would be the id 6,7 and 8Any idea how could I do that?Thanks
View 1 Replies
View Related
Jan 18, 2007
hi everyone
what the matrix's class name ?
where is the matrix's dll?
Can i create a new class extends the matrix?
I want to override the matrix's onpaint method.
Can i do this ?
why the table not have the column group?
View 9 Replies
View Related
Oct 16, 2014
I am having trouble finishing the last bit of a report. The report shows orders that customers have placed that contain 0 promo items, All promo items (all items in order are promo items), and a mix of promo and non promo (at least 1 promo item and 1 non-promo item). Ive simplified this a bit for ease of understanding but lets assume we have 2 tables: A Promo table that contains the items on promotion and the dates that promotion is valid, and a Sales table, that contains the order number, order date, and sku ordered.
I've already written code that finds orders that have at least 1 promo item in them, and using that, I can determine what orders have 0 promo items in them. Where I am stuck is taking the orders that have at least 1 promo item in them, and separating them into orders that have only promo items, and those that have both promo and not promo items in them. Also, there are several promos throughout the year (called "Offers") so in my code below, you can see 2 different Offers ("JF" and "MA") with their corresponding dates they are valid. They will never overlap. My results also have to be split out by Offer so management can look at the results of each offer separately. Here is some code:
Code:
create table #Promos (
Offer varchar(2) null,
SKU int null,
StartDt date null,
EndDt date null
[Code] ....
So my results should show OrderNo A1111 in the Promo and No Promo group because of SKU 5 not being promotional during the time that order was placed. OrderNo A2222 should be in the Promo Only group because both SKUs on the order were promotional at the time the order was placed.
View 6 Replies
View Related
Nov 22, 2006
Hi! could you please check my code on adding record on my DB.. Im using SQL Server included on VWD.Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click Dim con As String = "Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Request.mdf;Integrated Security=True;User Instance=True" Dim sqlcon As New Data.SqlClient.SqlConnection(con) Dim sqlcmd As New Data.SqlClient.SqlCommand("Insert into uRequest (eticket, ename, edept, edetails, ejobpend, edate) values (@eticket,@ename,@edept,@edetails,@jobpend,@edate)") sqlcon.Open() sqlcmd.Parameters.Add("@eticket", Data.SqlDbType.Char, 10).Value = "11-0010-06" sqlcmd.Parameters.Add("@ename", Data.SqlDbType.Char, 100).Value = User.Identity.Name sqlcmd.Parameters.Add("@edept", Data.SqlDbType.Char, 50).Value = DropDownList1.Text sqlcmd.Parameters.Add("@edate", Data.SqlDbType.DateTime).Value = Date.Now sqlcmd.Parameters.Add("@edetails", Data.SqlDbType.Text).Value = TextBox1.Text sqlcmd.Parameters.Add("@ejobpend", Data.SqlDbType.Text).Value = TextBox2.Text sqlcon.Close() End Sub I've check the Data on my DB but nothing has been added. nwy, how can u create a confirmation message if the record has been successfully added.Thanks and sorry for the trouble.
View 1 Replies
View Related
Jul 23, 2005
hi i have a question how can i delete last added row. I have 2 tables .source and destination . I take a 1 row from source table , do someoperation on it and save to destination table . after succesfull written Iwant to delete added row from source table.. i'm using a coursors. the mainproblem is : is there any function to check which row was last added. Now Iam doing it using select * from destionation where (and necessaryconditions). but if destination table will be 100000000 rows for example ittakes too much time... Is there another possibility to do it ???please helpMarcin Wolkuwolku
View 2 Replies
View Related
Sep 27, 2006
In developing a VWDE project I added 2 columns to the User table in the ASPNETDB.mdf database. I can see the columns in the Data Definition and I can see the values I added when I Show Table Data but I cannot access them with a SQLDataSource control? The SQLDataSource shows all of the columns in that table except the ones I added. Any suggestions? Thanks.
View 2 Replies
View Related
Dec 12, 2003
I would like to retreive the identity field value of a record that was just added to the table.
In other words, I have a form and on submission, if it is a new record, I would like to know the identity value that the SQL Server has assigned it.
This may be overkill, but here is my code to process the form:
Protected Sub processForm(ByVal thisID As String, ByVal myAction As String)
Dim sqlConn As New SqlConnection(ConfigurationSettings.AppSettings("connectionString"))
sqlConn.Open()
Dim sql As String
Select Case myAction
Case "save"
If thisID > 0 Then
sql = "update INCIDENT set " & _
"RegionID = @RegionID, " & _
"DistrictID = @DistrictID, " & _
"DateReported = @DateReported, " & _
..CODE...
"WHERE IncidentID = " & myIncidentID
Else
sql = "insert into INCIDENT(" & _
"RegionID, " & _
"DistrictID, " & _
"DateReported, " & _
...CODE...
") " & _
"values(" & _
"@RegionID, " & _
"@DistrictID, " & _
"@DateReported, " & _
...CODE...
")"
End If
Case "delete"
sql = "delete from INCIDENT where IncidentID = " & myIncidentID
Case Else
End Select
Dim sqlComm As New SqlCommand(sql, sqlConn)
sqlComm.Parameters.Add(New SqlParameter("@RegionID", SqlDbType.NVarChar))
sqlComm.Parameters.Add(New SqlParameter("@DistrictID", SqlDbType.NVarChar))
sqlComm.Parameters.Add(New SqlParameter("@DateReported", SqlDbType.NVarChar))
...CODE...
sqlComm.Parameters.Item("@RegionID").Value = ddRegionID.SelectedValue
sqlComm.Parameters.Item("@DistrictID").Value = ddDistrictID.SelectedValue
sqlComm.Parameters.Item("@DateReported").Value = db.handleDate(txtDateReported.SelectedDate)
...CODE...
Dim myError As Int16 = sqlComm.ExecuteNonQuery
'Response.Redirect("incident.aspx?id=" & )
End Sub
The response.redirect at the end of the sub is where I would like to put the identity field value.
View 6 Replies
View Related
Jan 4, 2006
How many triggers can be added in a table?
View 2 Replies
View Related
Jan 28, 2004
how do I position a newly added column in the table?
the default is at the very end.
what would I have to add to this code:
ALTER TABLE Vehicles
ADD Model varchar(55)
Thanks
View 2 Replies
View Related
Jan 8, 2008
Hi,
Is there a built in capability with sql server 2005 which sends emails to users upon a record insertion.
Thanks
View 2 Replies
View Related
Mar 30, 2008
is there a way to set up a scheduled sp to email me if no records are added to a certain table within the hour?
is this possible?
View 1 Replies
View Related
Jul 23, 2005
Here's the deal.I've been working on an Intranet application for my clients, and todayI went and installed the first prototype. It's a fairly standard thing- VS2003 ASP.NET/VB.NET on a SQL Server 2000 database.I restored the database onto their db server, and installed theapplication on their intranet server, and made the necessary changes tothe web.config and other configuration files.I logged on with no problem. The default form is a Search/Finder formwith no default recordset. I fired up the filter to return all therecords and the thing crashed. The filter form wouldn't get any data(there *was* data in the database - I checked that!). So I set a traceon the database and this is the SQL that was being sent to the server(note that this SQL is dynamically built and is being sent in-line):SET FMTONLY OFF; SET FMTONLY ON;SELECT fldID, fldReferenceNumber,fldRevision, fldPartNumber, fldIndentNumber, fldDescription,fldBatchNumber, fldCreatedDate FROM tblMyTable SET FMTONLY OFF;Note those SET FMTONLY OFF; SET FMTONLY ON;...SET FMTONLY OFF;directives. These direct SQL Server to return only metadata, and notdata rows. The SQL which SHOULD have been sent, and which I have justcaptured in the SQL Profiler on MY system here is:SELECT fldID, fldReferenceNumber, fldRevision, fldPartNumber,fldIndentNumber, fldDescription, fldBatchNumber, fldCreatedDate FROMtblMyTableWHERE (((tblMyTable.fldReferenceNumber LIKE N'%')))Again, note that the WHERE clause is missing from the first lot of SQL.Does anyone have any ideas about why this might be happening? I thinkit might have something to do with ownership/permissions, but Icouldn't find anything different about this database than the system onwhich this new application was modelled, which has no such problems.Edward
View 3 Replies
View Related
Oct 5, 2005
Hello all, i have a question i've beenk thinking for a little bit andcant seem to come up with a solution. What i want to do is add a uniquecounter to a resultset in query. For exampleSELECT ColumnA, ColumnB, ColumnC, ColumnDFROM AnyCombinationOfJoinsOrTablesWHERE SomeConditionIsTruewThe query above will produce a resultest of 4 columns.'A1', 'B1', 'C1', 'D1''A2', 'B2', 'C2', 'D2''A3', 'B3', 'C3', 'D3'......'An', 'Bn', 'Cn, 'Dn'Now what i want to do is add a unique identifier to every row. Theeasiest way would be to add a counter integer so the resultset wouldlook like1, 'A1', 'B1', 'C1', 'D1'2, 'A2', 'B2', 'C2', 'D2'3, 'A3', 'B3', 'C3', 'D3'......n, 'An', 'Bn', 'Cn, 'Dn'Is there a way to achieve this in T-SQL without using cursors.Thanks,Gent
View 3 Replies
View Related
Sep 14, 2007
I have a stored procedure that works fine in reporting services. It grabs the total of Yes's and No's by dates . But then i went ahead and added 2 more parameters to the proc, and now the totals are all wrong. I dont understand how that can mess everything up. Here is the previous stored proc, that gives the correct sum.
Code Snippet
ALTER PROCEDURE [dbo].[Testing_Questions_ALL_YESOrNO]
@Question char(80)
AS
BEGIN
SELECT
Qry_Questions.Question
, Qry_Questions.Date
, Qry_Questions.response
, B.Total
FROM Qry_Questions
INNER JOIN Qry_Sales_Group
ON dbo.Qry_Questions.sales_person_code COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code
INNER JOIN
( Select COUNT(qq.response)as Total, Question, Date, response
FROM Qry_Questions qq
Where qq.response in ('Yes','No')
GROUP by qq.[Question] , qq.Date,qq.response ) B
ON Qry_Questions.Date = B.Date AND
Qry_Questions.Question =B.Question and
Qry_Questions.response=B.response
WHERE Qry_Questions.[Response Type]='YesNo' and Qry_Questions.Question=@Question
GROUP BY Qry_Questions.question,Qry_Questions.Date,Qry_questions.Response,B.Total
ORDER BY Qry_Questions.Question, Qry_Questions.Date
END
SET NOCOUNT OFF
Here is the edited version which only has two new parameters added to the proc. The edits are highlighted.
Code Snippet
ALTER PROCEDURE [dbo].[Testing_Questions_ALL_YESOrNO_Totals]
(@Region_Key int=null,@QuestionCode char(5),@Question char(80))
AS
BEGIN
SELECT
Qry_Questions.Question
, Qry_Questions.Date
, Qry_Questions.response
, B.Total
FROM Qry_Questions
INNER JOIN Qry_Sales_Group
ON dbo.Qry_Questions.sales_person_code COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code
INNER JOIN
( Select COUNT(qq.response)as Total, Question, Date, response
FROM Qry_Questions qq
Where qq.response in ('Yes','No')
GROUP by qq.[Question] , qq.Date,qq.response ) B
ON Qry_Questions.Date = B.Date AND
Qry_Questions.Question =B.Question and
Qry_Questions.response=B.response
WHERE Qry_Questions.[Response Type]='YesNo'
AND REGION_KEY=@Region_Key
AND LEFT(Qry_Questions.[Question Code],2)IN (@QuestionCode)
AND Qry_Questions.Question=@Question
GROUP BY Qry_Questions.question,Qry_Questions.Date,Qry_questions.Response,B.Total
ORDER BY Qry_Questions.Question, Qry_Questions.Date
END
SET NOCOUNT OFF
View 6 Replies
View Related
Sep 19, 2007
Here is a new one for me. I added the /3GB switch to the boot.ini, restarted the server and now receive a number of messages when the OS tries to load.
Explorer.exe - Application Error
The application failed to initialize properly.
If I click OK the server sits on a blue screen with no information on the screen.
The other messages are the result of me trying to do anything within Task Manager, with the exception of rebooting the server. These messages make sense since Explorer.exe had a problem.
The server has the following configuration.
HP DL580
Windows Server 2003 Enterprise Edition - SP2
SQL Server 2000 Enterprise Edition - SP3a
14GB Memory
(4) dual core procs
PAE is enabled in the boot.ini
If I remove the /3GB the OS starts without a problem. Any suggestions?
Thanks, Dave
View 8 Replies
View Related
Jan 3, 2008
I'm running vb 2088 express with SQL express. I have limited VB and very limited SQL knowledge.
I have a VB app that inserts candidate data into the SQL table (called front) all works fine.
I now have a second VB app (separate from the above VB app) that runs at the same time; it does some extra work on the candidate data.
My question is when a new candidate is added to the SQL table, how I can get the second VB app to automatically know that a new record has been inserted (and the record key) and then start the VB subroutine.
I'm unsure if I should look at triggers or stored procedures (or something else) for this task.
Any help or pointers to additional resources would be appreciated.
Thank you!
TJ
View 7 Replies
View Related
May 15, 2007
I ran the code below that adds a column to an SQL table.
I refreshed Data Sources, but, the new column does not appear.
I refreshed Server Explorer and the new column does not appear.
I tried running the code again and I got:
"Column names in each table must be unique. Column name 'CaseNbr' in table 'Patient' is specified more than once."
so the column did actually get added, but, I can't see it.
Imports System.Data.SqlClient
Public Class Form1
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Try
Dim sqlConnection As New System.Data.SqlClient.SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Patient.mdf;Integrated Security=True;User Instance=True")
Dim str As String = "ALTER TABLE Patient ADD CaseNbr Varchar(50)"
Dim command As New System.Data.SqlClient.SqlCommand(str, sqlConnection)
sqlConnection.Open()
command.ExecuteNonQuery()
sqlConnection.Close()
'Catch
'End Try
Close()
End Sub
End Class
View 5 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
Aug 2, 2007
I am using Reporting Services 2000 and therefore VS 2003 and am having a problem editing the T-SQL query in the data view of Visual Studio.
Basically if I manually edit the T-SQL query or touch anything in the designer view of the query I get odd characters added in the T-SQL and the query no longer works. I have tried re-installing VS2003 and RS 2000 but it's still happening.
FYI I also have SQLServer 2005 and VS 2005 on my machine as all applications we develop are in .Net 2.0 and 3.0. We only use VS2003 for RS.
Does anyone have a clue how to fix this? Thanks
View 3 Replies
View Related
Apr 14, 2005
Howdie y'all,
I'm quit new to SQL server and I'm getting there finally, but it's quit hard to find some good info on how to create stored procedures... But I've got the following one....
CREATE PROCEDURE [dbo].[spAddUser]@UserEmail VARCHAR(255),@UserPassword VARCHAR(16),@UserName VARCHAR(32)ASINSERT INTO [dbo].[tblUsers](UserEmail, UserPassword, UserName)VALUES (@UserEmail, @UserPassword, @UserName)GO
I actually would like to get value of the UserId column for the newly added record.
Can anyone of you folks help me with this?
Cheers,
Wes
View 4 Replies
View Related
Mar 15, 2006
For some reason, there are extra trailing spaces being added to all my
data as it is placed in the db. I am collecting information from,
processing a TRIM-like function in javascript, and then again in the
ASP.net code before it is being placed in the db.
The extra spaces are causing problems with my application.
Any help would be greatly appreciated.
View 3 Replies
View Related
Dec 9, 2002
We're replicating columns in a table from 7.0 server to SQL2k server. Once we've got the data on SQL2k, we were able to take advantage of DTS that was not available in version 7.0.
I recenlty added a column to the article and changed the DTS accordingly (transformation columns). However, I'm getting this error ever though I added the new column in the DTS.
-------
The number of columns in the bcp file does not match what is defined in the DTS package. Regenerate the package.
-----
I've recreated the package with no luck. This is becoming urgent as it's supposed to go into production soon.
Please help!!
Thanks,
Colleen
View 2 Replies
View Related
Nov 4, 2005
Is it possible to find out when a particular column was added to any table?
Thanks
View 1 Replies
View Related
Apr 11, 2006
i recorded a script for a change i need to make. actually 15 so far, i am getting ready to bring an access db with no pk or fk and only 1 relation over to ss05
my scripts are used to add the need pk fk to the tables and then move the data from the temptbl to the new one
1 thing i have been noticing is code like below will rename the table dbo.aMgmt.Employee and with that all the remaing lines of the script will fail.
DROP TABLE aMgmt.Employee
GO
EXECUTE sp_rename N'dbo.Tmp_Employee', N'aMgmt.Employee', 'OBJECT'
GO
any help?
View 2 Replies
View Related
Dec 31, 2007
I have a SQL database that gets populated by another program. Is there a way to determine when a new record is added to that database?
Thanks
View 4 Replies
View Related
Mar 26, 2008
Hi..
I created a publication on my server and I just clicked the box to include all of the stored procedures. If I add a new procedure, will that procedure also be included? Or will I have to modify the publication for each SP I create?
Thanks!
-Greg
View 1 Replies
View Related
