|
INSERT INTO tblOutgoingLetters
([To], [From], Subject, RefNumber, RefDate, OutwardNumber, OutwardDate, FileNumber, Remarks)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
this is the command that I have in the CommandText property of Command object..but instead of inserting a new record into the table , it is changing the content i.e. updating the last record
Do u have any idea about why is this problem occurring?
Sadaf
|
|
|
|
|
EVERY TIME I OPEN THE SOLUTION FILE TO MAKE SOME CHANGES IN MY PROJECT , THE RECORDS ADDED AFTER USING ENVIRONMENT.CURRENTDIRECTORY ARE DELETED . HOWEVER THE RECORDS THAT WERE INSERTED INTO THE DATABASE BEFORE I USED THIS REMAIN AS IT IS.
WHAT COULD BE THE CAUSE?
Sadaf
|
|
|
|
|
Please don't shout. Pay attention to forum rule no. 6
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
This is actually a problem with you program logic. Insert statement can never update another record. So check your logic may be on insertion the program calls the update function.
Do good and have good.
|
|
|
|
|
i want to store a jpg file in oracle and want to access from vb through ado connection.
|
|
|
|
|
Hope it helps.
BOOL CMyDoc::ImportPic(LPCTSTR filePath)
{
CFile file;
file.Open(filePath,CFile::modeRead);
DWORD len=file.GetLength();
BYTE *pBuf=new BYTE[len+1];
file.ReadHuge(pBuf,len);
VARIANT varBLOB;
SAFEARRAY *psa;
SAFEARRAYBOUND rgsabound;
_RecordsetPtr pRs;
pRs.CreateInstance(__uuidof(Recordset));
pRs->Open(_T("picdata"),m_pConn.GetInterfacePtr(),adOpenStatic,adLockOptimistic,adCmdTable);
pRs->AddNew();
pRs->PutCollect(_T("name"),_T("a"));
rgsabound.lLbound=0;
rgsabound.cElements=len;
psa=SafeArrayCreate(VT_UI1,1,&rgsabound);
for (LONG i=0;i<(LONG)len;++i)
SafeArrayPutElement(psa,&i,pBuf++);
varBLOB.vt=VT_ARRAY|VT_UI1;
varBLOB.parray=psa;
pRs->GetFields()->GetItem("pic0")->AppendChunk(varBLOB);
pRs->Update();
pRs->Close();
pRs.Release();
return TRUE;
}
|
|
|
|
|
Why did you do it in C++ when the OP is using VB?
|
|
|
|
|
Yes, only the ado part may help.
|
|
|
|
|
IF EXIST(SELECT 1 FROM t where name='abc' ADN age=20 AND job='worker')
UPDATE t set num=num+1 WHERE name='abc' ADN age=20 AND job='worker'
ELSE INSERT INTO t(id,name,age,job,num) VALUES(NEWID(),'abc',20,'worker',1)
|
|
|
|
|
What is your question?
"I guess it's what separates the professionals from the drag and drop, girly wirly, namby pamby, wishy washy, can't code for crap types." - Pete O'Hanlon
|
|
|
|
|
|
Not sure where you can optimize it. Looks pretty tight. Why do you think it needs optimizing? Is it taking a long time to run?
"I guess it's what separates the professionals from the drag and drop, girly wirly, namby pamby, wishy washy, can't code for crap types." - Pete O'Hanlon
|
|
|
|
|
Yes.
Two conditions duplicates.
|
|
|
|
|
I don't think you can improve this. You can't escape duplicating the WHERE clause in the EXISTS and UPDATE queries. It's not really duplication anyway - the UPDATE query is only fired under certain conditions.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
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?
|
|
|
|