|
The following will remove the need for the "if exist" statement:
UPDATE t SET num=num+1
WHERE name='abc'
AND age=20
AND job='worker'
IF (@@ROWCOUNT = 0) BEGIN
INSERT INTO t(id,name,age,job,num)
VALUES(NEWID(),'abc',20,'worker',1)
END Additionally you should look at your indexes. I would expect something like:
CREATE UNIQUE INDEX t_idx1 ON t (name, age, job) I would recommend looking at the other SQL that use this table, then change the order of the columns in the above index to best-suit their requirements.
Regards
Andy
|
|
|
|
|
I'm using ADO.
Any way?
Thanks.
|
|
|
|
|
It would slot-in in the same way as your original code sample. However a better way would be to wrap it in a stored procedure:
CREATE PROCEDURE dbo.Insert_t
@Name VARCHAR(40),
@Age SMALLINT,
@Job VARCHAR(40)
AS BEGIN
SET NOCOUNT ON
UPDATE t SET num = num+1
WHERE name = @Name
AND age = @Age
AND job = @Job
IF (@@ROWCOUNT = 0) BEGIN
INSERT INTO t (id, name, age, job, num)
VALUES (NEWID(), @Name, @Age, @Job, 1)
END
RETURN 0
END You can then create an ADO command object, with parameters, to execute it from your code. Note that you will need to add some error-trapping code. I'll let you figure that all out.
Regards
Andy
|
|
|
|
|
Thanks a lot.
|
|
|
|
|
Interesting approach but I wonder if the failed update statement executes faster than the EXISTS statement? Either way, I don't like the approach beyond the novelty. In cases where I really need "extra" performance I separate the stored procedures and let the application decide which one to call, taking advantage of distributed logic.
Need a C# Consultant? I'm available.
Happiness in intelligent people is the rarest thing I know. -- Ernest Hemingway
|
|
|
|
|
I test it this way
Just after the connection is successfully established, do BeginTrans twice, the 2nd failed, anyway to make it work?
m_pConn->BeginTrans();
m_pConn->BeginTrans();
|
|
|
|
|
I think you have to perform nested transactions in your T-SQL code
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
hi! I am building one web site, and I am a beginner in ASP.NET, I am attaching a database to the project and when I want to start the application I have this problem:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider
Here is my connection string:
SqlConnection con = new SqlConnection("Data Source=DILI/SQLEXPRESS;Initial Catalog=|App_Data|test2.mdf; user id=xxxx;password=xxxx;");
con.Open();
SqlCommand cmd = new SqlCommand("INSERT INTO test VALUES ('" + TextBox1.Text + "')");
cmd.ExecuteNonQuery();
Any Help?? Thanks Ahead
|
|
|
|
|
Possible reasons.
1. The server won't allow you to connect using that Data Source becuase the datasource indicates that TCP/IP is used.
* Use a datasource name that does not require TCP/IP
* Set up SQL Server to permit TCP/IP connections.
2. The user name or password is incorrect.
* Check the user name and password combination is correct
* Use a trusted connection instead
|
|
|
|
|
what if I don't have any password?? What should I use for password??
One more think: When I am writing the data source like this: "Data Source=.\SQLEXPRESS" it gives me a error and it says: "Unrecognized escape sequence", and when I am turning the slash otherway: "Data Source=./SQLEXPRESS" it says is ok. Does that make any difference??? Thanks for the help
|
|
|
|
|
laziale wrote: what if I don't have any password?? What should I use for password??
As I said already - Use a trusted connection instead. See http://www.connectionstrings.com/[^]
laziale wrote: When I am writing the data source like this: "Data Source=.\SQLEXPRESS" it gives me a error and it says: "Unrecognized escape sequence", and when I am turning the slash otherway: "Data Source=./SQLEXPRESS" it says is ok. Does that make any difference???
Ummm... Yes. Why explain what the difference is, then ask me if there is a difference?
|
|
|
|
|
well, I did my connection now as: "Data Source=./SQLEXPRESS;AttachDbFilename=C:\Documents and Settings\angel.MMB\My Documents\Visual Studio 2005\WebSites\WebSite2App_Data\test2.mdf;Trusted_Connection=true"
and the connectionString now is same in the OnClick event of the button and in the web config with the only difference that in the web config file I have <add name="test2cons" before="" starting="" the="" connectionstring,="" and="" problem="" is="" still="" same,="" that="" i="" can="t connect to the database. Thanks ahead for the help ">
|
|
|
|
|
Hi laziale
i want to know did you are using msaccess as backend???
Yogesh Agarwal
|
|
|
|
|
yogesh_kumar_agarwal wrote: i want to know did you are using msaccess as backend???
Obviously he isn't. His previous post indicated that he was using the Express edition of SQL Server.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
i think you didn't read the connection string properly he tries to connect with a mdb database file means Access
Yogesh Agarwal
|
|
|
|
|
yogesh_kumar_agarwal wrote: i think you didn't read the connection string properly he tries to connect with a mdb database file means Access
No, you didn't read the connection string properly. The database file he was trying to connect to had a .mdf extension, not a .mdb extension. This is consistent with trying to connect to a SQL Express database, as this article[^] indicates.
In the future, I suggest that you get your facts straight before engaging in a discussion.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Yes, i realize it, Sorry for discussion
with regards
Yogesh Agarwal
|
|
|
|
|
No, I am using SQL Server 2005
|
|
|
|
|
i had the same problem .WHEN i had not started the SQL server when i was running the page .
try to use the designer for the database connection . writing the code by yourself may caused errors.
f(yf) = yf
|
|
|
|
|
Hi
I have several PDF document uploaded to database SQL Server 2005.
I want to see how many PDF document contains word "technology" and "agreement" within a maximum of 5 word difference.
"Near" clause in CONTAIN finction provides a way to get all document where I can get "technology" and "agreement" is nearby.
But restrcting proximity within 5 word is the main problem.
Following is my query that fetches all DocIDs where word "Technology" is followed by "Agreement" (Does not restrict Agreement word follows within 5 word of "Technology")
SELECT count(DocID)
FROM tblDoc
WHERE CONTAINS(DocContent, 'Technology NEAR Agreement');
GO
I want to get those DocID, which is having "technology" and "Agreement" within 5 words.
Any help is appreciated.
Thanks in advance
Thanks,
Arindam D Tewary
|
|
|
|
|
I have a table with recursive relationship and i want to get all the reords from this table having any specific parent id.
Do good and have good.
|
|
|
|
|
|
The problem is that SQL server CE (3.5) doesn't support the IF statement...
I'm busy updating some table date and I need to do a check if a row exists: if it does it needs to be updated, otherwise the data needs to be inserted.
Does anyone have ideas of how I can do this in SQL?
I could do it in code, but it seems inefficient as it would be a two step process.
Thx!
|
|
|
|
|
Jacquers wrote: I could do it in code, but it seems inefficient as it would be a two step process.
Forget worrying about this with regard to SQL "Server" CE - it's an in-process database so you're pretty much just calling into a DLL.
Something that few developers seem to be aware of is that "SQL" "Server" CE is particularly poor at using SQL. You are much, much better served by binding directly to a table using a SqlCeCommand with the CommandType property set to CommandType.TableDirect and the IndexName property set to the name of the index you want to search by, and calling ExecuteResultSet to get a SqlCeResultSet object. Specify ResultSetOptions.Updatable to directly edit the bound table.
You can then use the Seek method to move to a specific row. If it's there, you can use indexing operations or the SetValue/SetValues/Setdatatype methods to set the values of the row and call Update to commit the change. If not, you can create a new row by creating a SqlCeUpdatableRecord (by calling CreateRecord on the resultset), setting its values, and calling Insert on the resultset to insert it.
For more information and benchmarks, see SQL Compact Edition Insert Performance[^] from PocketPCDN.com.
DoEvents: Generating unexpected recursion since 1991
|
|
|
|
|
Thank you for the info! I'll check out the link as well
|
|
|
|