Violation of PRIMARY KEY constraint 'PK_VTA020SAP2'. Cannot insert duplicate key in object 'VTA020SAP2'.
The statement has been terminated.
INSERT INTO VTA020SAP2 SELECT VTA020SAP1.*, NULL as xCLIPRO, NULL AS xPAIS, NULL AS xCONFREI, NULL AS xCONCPER,
NULL AS xMONEDA, NULL AS xFAX, NULL AS xTIPEXP, NULL AS xCASILLA, NULL AS xDIAENTR, NULL AS xPUNVEN,
NULL AS xCIUDAD, NULL AS xREGION, NULL AS xCONDIC, NULL AS xINSPAG, NULL AS xCUMPLE,
NULL AS xPERVIS, NULL AS ESTADO FROM dbo.VTA020SAP1
INNER JOIN VTA020
ON VTA020SAP1.CNRCLI = VTA020.CNRCLI
AND VTA020SAP1.CNRCLI IN (SELECT MAX(VTA020SAP1.CNRCLI) FROM VTA020SAP1
GROUP BY VTA020SAP1.CNUECOD)
AND (not EXISTS
(SELECT VTA020SAP2.* FROM dbo.VTA020SAP2
INNER JOIN VTA020SAP1 ON VTA020SAP1.CNUECOD = VTA020SAP2.CNUECOD
WHERE dbo.VTA020SAP2.CNUECOD = dbo.VTA020SAP1.CNUECOD))
So, I need to delete the "timestamps" the an user has done, which the difference between one stamping and the following one is less than 2 minutes for the inputs for entrances and I need to remove it when the "stampings" which the difference between an one stamping and the previous one is less than 2 minutes for exits.
Can you help me please? I will appreciate a lot your help.
Im trying to insert a record in my sql server 2005 express database.The following function tries that and without an error returns true.However, no data is inserted into the database...Im not sure whether my insert statement is correct: I saw other example with syntax: insert into table values(@value1,@value2)....so not sure about thatAlso, I havent defined the parameter type (eg varchar) but I reckoned that could not make the difference....Here's my code: Function CreateNewUser(ByVal UserName As String, ByVal Password As String, _ ByVal Email As String, ByVal Gender As Integer, _ ByVal FirstName As String, ByVal LastName As String, _ ByVal CellPhone As String, ByVal Street As String, _ ByVal StreetNumber As String, ByVal StreetAddon As String, _ ByVal Zipcode As String, ByVal City As String, _ ByVal Organization As String _ ) As Boolean 'returns true with success, false with failure Dim MyConnection As SqlConnection = GetConnection() Dim bResult As Boolean Dim MyCommand As New SqlCommand("INSERT INTO tblUsers(UserName,Password,Email,Gender,FirstName,LastName,CellPhone,Street,StreetNumber,StreetAddon,Zipcode,City,Organization) VALUES(@UserName,@Password,@Email,@Gender,@FirstName,@LastName,@CellPhone,@Street,@StreetNumber,@StreetAddon,@Zipcode,@City,@Organization)", MyConnection) MyCommand.Parameters.Add(New SqlParameter("@UserName", SqlDbType.NChar, UserName)) MyCommand.Parameters.Add(New SqlParameter("@Password", Password)) MyCommand.Parameters.Add(New SqlParameter("@Email", Email)) MyCommand.Parameters.Add(New SqlParameter("@Gender", Gender)) MyCommand.Parameters.Add(New SqlParameter("@FirstName", FirstName)) MyCommand.Parameters.Add(New SqlParameter("@LastName", LastName)) MyCommand.Parameters.Add(New SqlParameter("@CellPhone", CellPhone)) MyCommand.Parameters.Add(New SqlParameter("@Street", Street)) MyCommand.Parameters.Add(New SqlParameter("@StreetNumber", StreetNumber)) MyCommand.Parameters.Add(New SqlParameter("@StreetAddon", StreetAddon)) MyCommand.Parameters.Add(New SqlParameter("@Zipcode", Zipcode)) MyCommand.Parameters.Add(New SqlParameter("@City", City)) MyCommand.Parameters.Add(New SqlParameter("@Organization", Organization)) Try MyConnection.Open() MyCommand.ExecuteNonQuery() bResult = True Catch ex As Exception bResult = False Finally MyConnection.Close() End Try Return bResult End FunctionThanks!
If I want to change the value of a special bit in a column, how to wirte the SQL sentence in sqlserver. I mean in a column, the value is 'test', then how to change the 's' to 'e' or any other value by just one SQL sentence. Thanks.
I'm using the sentence NOLOCK for selects, but I have many sentences, Is there any way to set a parameter in the DBMS, to use NOLOCK parameter by default ???? I mean, I don't like to lock any table for selects.
Hi, all friends. I'm using SQL-SERVER 2000.I have a table like this:NIF Name----------------------------------------A-1234 Company1B-123-B1 Company2C-4568 Company4D-453-DF Company5I want delete the symbol "-" in the row "NIF". eg. A-1234 change toA1234, D-453-DF to D453DFSo how can I write the sentence . Should I use the Stored Procedure?Thank you very much!!Simon
i have insert/update SQL sentence, but sometimes there are empty values because there not required in the database so sometimes the sql sentence look this way:
INSERT INTO EquipmentAndPlace (EquipmentID,EquipmentEmdaNo,EquipmentPlace,EquipmentIDForRecognize, EquipmentRemarks,EquipmentLastChecked) VALUES ('3','','2','1','','12/1')
with empty values, but then it doent update in the dataBase-only if all the values appear- what the solution of it? Thanks
I have two tables. A table called users (content speaks by it self) and a table called groups. Since i want every user to be able to be a member of several groups and, of course, a group to have several users i have made a junction table called jt. The junction table contains userId's and groupId's according to the user-group bindings. The primary key(identity) in the junction table is a int named jtId. Now i want to take out all posts from the junctiontable and the corrresponding userName (s) from the users-table and the corresponding groupName from the groups-table. Can somebody please help me to make a SQL command that will di that for me. I have tried with INNER JOIN and several SELECTS in the same command. Thanks in advance, Greetings from Esben
I would like to know how can I change a "not in" clause with the same results in a SQL sentence in order to improve the performance of my SQL sentence.
I want to add a parameter to the following SQL-sequence. ActivityGroup is the table and I want to select the current activityGroupID in place of XXXXX. Do I need to put this in a stored procedure?
select * from ActivityGroup where cpgActivityGroupID = XXXXX and cgpRegistredFrom > getDate() and cgpRegistredTo < getDate()";
I have a project in which I have about 20,000 records in sql database table.
What I would like to do is generate a query that lists all the unique words in a particular field acros the entire table so as to generate a glossary of words.
if we had a table that looked like
ID Description
001 This is the first record
002 This is the second record
003 This is not the first record
and the query was run on the description field, then the result I would like to see is
This is the first second not I hope this makes sense. Any help is appreciated.
My MASTER table that contains text is very simple: TEXT_ID INT TEXT varchar
I was thinking of something like creating another table to keep track of selection criteria: USER_NAME varchar // keeps track of which user made the selection TEXT_ID INT // points to the text id in the MASTER table START_POS INT // start position of the selection END_POS INT // end position of the selection COLOR INT // color of the selection
The thing I don't like about this method, is if user(s) have many words higlighted in the sentences, this table may get very large.
I also thought of maybe combining START_POS, END_POS & COLOR into comma delimited entries:
EX: 1:5:240, 25:30:125 // START_POS:END_POS:COLOR
The thing I don't like about this method, the parsing calculations may take a while to execute.
My goal is to stream as much text data as possible and make it as fast as possible. How does Google or any one else do it?
P.S. Having a copy of a MASTER table with HTML tags in TEXT field is not an option.
I have to parse a sentence into separate words. This has to be done in a Stored Proc.
If the sentence is: "The sun is rising". Then I want to store the words "The" "sun" "is" "rising" into 4 separate variables. (The separator is a space).
Has someone already written something like this before? I do not want to re-invent the wheel.
CREATE TABLE [dbo].[instructions]( [site_no] [int] NOT NULL, [instructions] [text] NULL ) Select top 3 * from instructions
Output
Site_no Instructions 20 Request PIN#510 then proceed 21 Request PIN#987 if wrong request name 22 Request PIN#688 allowed to use only numbers
All text instructions start with “Request PIN#XXX” also after that the text are different for every site_no
I need insert in all site_no rows and after the “Request PIN#XXX” the text “and codeword” keeping the current rest of text
How can I set e REPLACE CAST sentence using something LIKE PIN%%%%
To get these type of results
site_no instructions ----------- ---------------------------------------------------- 20 Request PIN#510 and codeword then proceed 21 Request PIN#987 and codeword if wrong request name 22 Request PIN#688 and codeword allowed to use only numbers
the talbe row like this: ID Name Scoe 11 Tome 20 12 Jack 30 11 Tome 40 12 Jack 10 13 John 10 My query command like this: Select T1.Id,T1.Name,T2.mathfrom st T1right join(Select Id as Id2,Sum(Math) as Math from St group by id) T2on T1.id=t2.id2where t1.id = t2.id2 While the reuslt is : Id Name Score 11 Tom 60 11 Tom 60 12 Jake 40 12 Jack 40 13 John 10
I am wonder :the T1 gives a table with six rows, the T2 gives a table with three rows, and I use RIGHT JOIN to connect the two table,the result should be a table with only three rows.I tried INNER JOIN, the result is same. but why ? please help me !
Is it possible to pass entire where sentence to a store procedure?From app, I'll generate a where sentence like below:
where orderID = '123' and orderCidy='London'
I created a store procedure like below but got an error said that An expression of non-boolean type specified in a context where a condition is expected, near 'END'
CREATE PROCEDURE getorder @mywhere VARCHAR(100) AS BEGIN SET NOCOUNT ON; select * from order @mywhere END
Hi everyone!I am working with Delphi v7 and MS SQLServer.I am trying to insert data in a table with a SQL sentence. Some of thefields of my table are type char or varchar, and they can have nullvalues.What do i have to write in the SQL sentence to insert a null value inthose fields?I tried with '', an empty String, but it doesnt work, the tablesstores an empty String (logical :-)).In the SQLServer GUI you have to press CTRL + 0 to insert a NULLvalue, but how can i tell this to the SQLServer through a SQLSentence?Well, thank you very much.
I was recently told SSRS does not support the ability to underline a portion of text in a sentence. Does anyone know if this is true? Couldn't "text decoration" "underline" and "span" be used to underline the web address in the sample sentence below?
I have a illegal keyword table name as "Illegal_keyword_Master" where near about 2000 illegal keyword are stored... Now I have a big Sentence(bunch of words)...
If any illegal is keywords is found from "Illegal_keyword_Master" table in that sentence then the sentence is "banned" otherwise the sentence is "OK"....
I have an very long ntext field, made up of many sentences that I append a full stop to every one, I also strip out any line breaks within the text. However I get this error, when I look it up it comes up with "Failed to locate the ending boundary of a sentence."
For example,I have a table "authors" with a column "author_name",and it has three value "Anne Ringer,Ann Dull,Johnson White".Here I want to create a new table by using a select sentence,its columns come from the values of the column "author_name".
can you tell me how can I complete this with the SQL?
i can't seem to get this query to work, it just keep returning nulls with ever values i set . 1 SELECT Bedrooms, Description, Image, 2 (SELECT Location 3 FROM Location_Table 4 WHERE (Property_Table.LocationID = LocationID)) AS Location, LocationID, Price, Price AS PriceMax, PropertyID, Title, TypeID, 5 (SELECT TypeOfProperty 6 FROM Type_Table 7 WHERE (Property_Table.LocationID = TypeID)) AS TypeOfProperty 8 FROM Property_Table 9 WHERE (TypeID = @TypeID OR 10 TypeID IS NULL) AND (LocationID = @LocationID OR 11 LocationID IS NULL) AND (Price >= @MinPrice OR 12 Price IS NULL) AND (PriceMax <= @MaxPrice OR 13 PriceMax IS NULL)
SELECT... "CAST(MONTH(Some_Date) as int) as Month, " &_ "CAST(DAY(Some_Date) as int) as Day " &_ "FROM Deceased " &_ "WHERE Active = 1 AND " &_ "MONTH(Some_Date) >= MONTH(GETDATE()) " &_ "ORDER BY Month, Day DESC" This is NOT: SELECT... "CAST(MONTH(Some_Date) as int) as Month, " &_ "CAST(DAY(Some_Date) as int) as Day " &_ "FROM Deceased " &_ "WHERE Active = 1 AND " &_ Month >= MONTH(GETDATE()) " &_ "ORDER BY Month, Day DESC" it says - Invalid column name 'Month'
I'm just learning SQL after using it for about a year now and I'm trying to add a Check constraint to a Social Security Field (See Below) and I can't figure out what is wrong with the syntax. In QA it errors out stating: Line 4: Incorrect syntax near '0-9'.
use Accounting Alter Table Employees Add Constraint CK_SNN Check (SSN Like [0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9])
Hello !! I have just createt a simple login page and reg page, login is working when I make one useraccound directly on to MsSql server, I can login successfully, but the problem is REGISTER PAGE with INSERT code. Here down is the code ov the login.aspx page
Function AddUser(ByVal userID As Integer, ByVal userName As String, ByVal userPassword As String, ByVal name As String, ByVal email As String) As Integer Dim connectionString As String = "server='(local)'; trusted_connection=true; database='music'" Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "INSERT INTO [users] ([UserID], [UserName], [UserPassword], [Name], [Email]) VALUE"& _ "S (@UserID, @UserName, @UserPassword, @Name, @Email)" Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand dbCommand.CommandText = queryString dbCommand.Connection = dbConnection
Dim dbParam_userID As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_userID.ParameterName = "@UserID" dbParam_userID.Value = userID dbParam_userID.DbType = System.Data.DbType.Int32 dbCommand.Parameters.Add(dbParam_userID) Dim dbParam_userName As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_userName.ParameterName = "@UserName" dbParam_userName.Value = userName dbParam_userName.DbType = System.Data.DbType.String dbCommand.Parameters.Add(dbParam_userName) Dim dbParam_userPassword As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_userPassword.ParameterName = "@UserPassword" dbParam_userPassword.Value = userPassword dbParam_userPassword.DbType = System.Data.DbType.String dbCommand.Parameters.Add(dbParam_userPassword) Dim dbParam_name As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_name.ParameterName = "@Name" dbParam_name.Value = name dbParam_name.DbType = System.Data.DbType.String dbCommand.Parameters.Add(dbParam_name) Dim dbParam_email As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_email.ParameterName = "@Email" dbParam_email.Value = email dbParam_email.DbType = System.Data.DbType.String dbCommand.Parameters.Add(dbParam_email)
Dim rowsAffected As Integer = 0 dbConnection.Open Try rowsAffected = dbCommand.ExecuteNonQuery Finally dbConnection.Close End Try
Return rowsAffected End Function
Sub LoginBtn_Click(sender As Object, e As EventArgs)
If AddUser(txtUserName.Text, txtUserPassword.Text, txtName.Text, txtEmail.Text) > 0 Message.Text = "Register Successed, click on the link WebCam for login"
Else Message.Text = "Failure" End If End Sub
and here is the error I receive when I try to open this register.aspx page:
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.
Compiler Error Message: BC30455: Argument not specified for parameter 'email' of 'Public Function AddUser(userID As Integer, userName As String, userPassword As String, name As String, email As String) As Integer'.
Source Error:
Line 52: Sub LoginBtn_Click(sender As Object, e As EventArgs) Line 53: Line 54: If AddUser(txtUserName.Text, txtUserPassword.Text, txtName.Text, txtEmail.Text) > 0 Line 55: Message.Text = "Register Successed, click on the link WebCam for login" Line 56:
Hi please lok at this SP I have written and point where am I commiting mistake. The PROD_ID_NUM field is a varchar field in the actual database.
Any help appreciated.
CREATE PROCEDURE [cp_nafta_dws].[spMXGetProductDetails] ( @ProductCode Int = null )
AS
DECLARE @sqlString AS nvarchar(2000) SET @sqlString = 'SELECT Master.PROD_ID_NUM AS ProductCode, Master.PROD_DESC_TEXT AS ProductName, Detail.PiecesPerBox, Detail.Price FROM cp_nafta_dws.PRODUCT AS Master INNER JOIN cp_nafta_dws.PRODUCT_MEXICO AS Detail ON Master.PROD_ID_NUM = Detail.PROD_ID_NUM' BEGIN IF NOT (@ProductCode = NULL) BEGIN SET @sqlString = @sqlString + ' WHERE Master.PROD_ID_NUM = ' + @ProductCode END END
Here is the codeLine 84: Line 85: searchDataAdapter = New System.data.sqlclient.sqldataadapter("SELECT * FROM Inventory Where title=" & title, searchConnection)Line 86: searchDataAdapter.Fill(objItemInfo, "ItemInfo")Line 87: Line 88: Return objItemInfohere is the errorLine 1: Incorrect syntax near '='. 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.Data.SqlClient.SqlException: Line 1: Incorrect syntax near '='.Source Error:
I have a SQL query in my asp.net & c# application. im trying to retrieve the data from two tables where the ID's of the tables match. once this is found i am obtaining the value associated with one of the keys. e.g. my two tables Event EventCategoryTypeField Type Example Field Type ExampleEventID Int(4) 1 CategoryID(PK)int(4) 1CategoryID(FK)int(4) 1 Type varchar(50) ExerciseType varchar (200) Exercise Color varchar(50) Brown The CategoryID is a 1:n relationship. my SQL query will retrive the values where the CategoryIDs match and the Tyoe matches as well. once this is found it will apply the associated color with that categoryID (each unique category has its own Color). my application will read all the data correctly (ive checked it with a breakpoint too and it reads all the different colors for the different ID's) but it wont display the text in the right color from the table. it will just display everything in the first color it comes across. Im including my code if it helps. can anyone tell me where i am going wrong please?? (the procedures are called on the On_Page_Load method)
private void Load_Events(){///<summary>///Loads the events added from the NewEvent from into a dataset///and then just as with the Holidyas, the events are wriiten to///the appropriate calendar cell by comparing the date. only the ///title and time will be displayed in the cell. other event details///such as, Objective, owner will be shown in a dialog box syle when ///the user hovers over the event.///</summary>
mycn = new SqlConnection(strConn);myda = new SqlDataAdapter("SELECT * FROM Event, EventTypeCategory WHERE Event.CategoryID = EventTypeCategory.CategoryID AND Event.Type = EventTypeCategory.CategoryType", mycn);myda.Fill(ds2, "Events");} private void Populate_Events(object sender, System.Web.UI.WebControls.DayRenderEventArgs e){///<summary>///This procedure will read all the data from the dataset - Events and then///write each event to the appropriate calendar cell by comparing the date of ///the EventStartDate. if an event is found, the title and time are written///to the cell, other details are shown by hovering over the cell to bring///up another function that will display the data in a dialogBox. once the///event is written, the appropriate color is applied to the text.///</summary>if (!e.Day.IsOtherMonth){foreach (DataRow dr in ds2.Tables[0].Rows){if ((dr["EventStartDate"].ToString() != DBNull.Value.ToString())){DateTime evStDate = (DateTime)dr["EventStartDate"];string evTitle = (string)dr["Title"];string evStTime = (string)dr["EventStartTime"];string evEnTime = (string)dr["EventEndTime"];string evColor = (string)dr["CategoryColor"]; if(evStDate.Equals(e.Day.Date)){e.Cell.Controls.Add(new LiteralControl("<br>"));e.Cell.Controls.Add(new LiteralControl("<FONT COLOR = evColor>"));e.Cell.Controls.Add(new LiteralControl(evTitle + " " + evStTime + " - " + evEnTime));}}}}else{e.Cell.Text = "";}}
Just a quickie... Can anyone see anything wrong with this SQL. I'm using Microsoft SQL Server 2000.
Code:
SELECT * FROM PFP_UserProfiles, Users, PFP_UserSkills, PFP_UserIndustries WHERE PFP_UserSkills.SkillID IN ( '222', '221', '182') AND PFP_UserProfiles.IsPublic = 1;
