|
Hello!
I can't execue the following sql string because I am getting an error message. How can I increment the specific field in the table by one?
SQLSTR = "UPDATE setup SET TotalUser=TotalUser + 1 WHERE RecordNumber = 1"
Thanks 
Journey
|
|
|
|
|
B Journey wrote: I can't execue the following sql string because I am getting an error message.
What is the error message?
|
|
|
|
|
SQLSTR = "UPDATE setup SET TotalUser=TotalUser + 1 WHERE RecordNumber = 1"
Call Initialize_Connection()
Call Execute_Statement(SQLSTR) ' the error occurs in this procedure
the procedure content is here:
Sub Execute_Statement(GetSQL)
Set ObjectRecord = Server.CreateObject ("ADODB.Recordset")
ObjectRecord.CursorLocation = adUseServer
ObjectRecord.CursorType = adOpenkeyset
ObjectRecord.LockType = adLockOptimistic
ObjectRecord.Open GetSQL, ObjectConnection,,, adCmdText 'error points this line
End Sub
Thanks
|
|
|
|
|
Okay - so at least we now know WHERE the error is. You still have not told us WHAT the error is.
What is the error message?
|
|
|
|
|
Sorry! Here is the error message. I have double checked all fields in the table nothing looks wrong. It is working on my local server but when I transfer it to server in Internet, I am getting this error message.
Microsoft JET Database Engine error '80040e10'
No value given for one or more required parameters.
/emlak/include/scripts.inc, line 138
I have included the procedure in my previous message where the error is pointing the line 138.
Thanks
|
|
|
|
|
That's a typical error when you have misspelled one of the column names (e.g. TotalUsers or RecordNumber)
Developers, Developers, Developers, Developers, Developers, Developers, Velopers, Develprs, Developers!
We are a big screwed up dysfunctional psychotic happy family - some more screwed up, others more happy, but everybody's psychotic joint venture definition of CP
Linkify!|Fold With Us!
|
|
|
|
|
Try using an ADODB.Command object instead of a recordset. You are performing an update.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
That update should work. I would guess that either you don't have rights to update the setup table. Or you need to better qualify the setup table. Perhaps there is a dbo.setup table and a username.setup table. It is always a good idea to qualify your table names. If it isn't that most likely it is a security thing, the user that is running the update doesn't have update rights on that table. If you provided the error message we might be able to pin point the issue.
Hope that helps.
Ben
|
|
|
|
|
Please refresh my rusty SQL skills. I have a DocMaster table with Status and StatusTime fields, and I have a DocUpdate table with UpdateTime and DocStatus fields. The DocUpdate table stores a history of all status updates for a document, and the DocStatus and StatusTime fields in the DocMaster table are redundant copies of the UpdateTime and DocStatus fields for the most recent DocUpdate record for a given document. How do I update the DocMaster table to correct the values in these fields?
|
|
|
|
|
Hi Brad
Normally you would use a trigger to keep the two tables synchronised. If the tables are currently out of sync then the following should fix the master table:
update DocMaster set
DocStatus = A.DocStatus,
UpdateTime = A.UpdateTime
from DocUpdate A
where A.DocId = DocMaster.DocId
and A.UpdateTime = (select max(UpdateTime) from DocUpdate
where DocUpdate.DocId = DocMaster.DocId)This assumes that the primary key on DocMaster is named "DocId", and that the "UpdateTime" is unique for each document (if you have an identity column on DocUpdate then you should use that instead).
Hope that helps.
Andy
|
|
|
|
|
That's perfect, thanks Andy. I do have triggers doing the normal sync work, but whenever I create redundant or derived fields like these I always start with an update procedure that makes everything all good again, if something goes wrong.
|
|
|
|
|
Hi friends
What is a clustered index? How many records can take clustered index in sql server?
what is a cursor?
what is a sql injuction?
thanks in advance
khan
|
|
|
|
|
imrankhanpathan wrote: What is a clustered index?
You will find the answer in SQL-Server's "Books Online". A table can only have one clustered index defined (unless told otherwise SQL-Server will use a clustered index for primary keys). There is no practical limit on the number of records that can be held in a clustered index.
imrankhanpathan wrote: what is a cursor?
Again the answer is in "Books Online".
imrankhanpathan wrote: what is a sql injuction?
You can find out about "SQL Injection" here[^]
|
|
|
|
|
I have a single query that selects a product id. Everytime is finds a product id it also selects several other queries to build up a row of statistics. Therefore my query retrieves rows as follows:
Product ID | Stat Number One | Stat Number Two | More Stats
125 | 76% | 45% |
What I want is a final overall score at the end of the query in a final column. Built on the fly from the other columns. How can I do this?
Eg
(StatNumberOne + Stat Number Two + Rest of Stat Columns / Amount of Columns) As Overall Score
I don't know how to reference these columns on the fly like this ??
|
|
|
|
|
one way doing this is using union
Eg.
SELECT prodid, no1, no2, 0 as overall from ......
union
SELECT prodid, 0 as no1, 0 as no2, (no1 + no2) / x as overall from ......
other way of doing is use procedure
|
|
|
|
|
I am trying to call a Oracle stored procedure using ADO in C++.
The store procedure has 12 input parameters, and 2 output
parameters with one of them being a Ref Cursor. I followed the
example in a post by Koushik Biswas on the subject
(http://www.codeproject.com/script/Articles/list_articles.asp?userid=811562).
But when I called
pRecordset = pCommand->Execute(NULL, NULL,
adCmdStoredProc | adCmdUnspecified );
I got an error message of "Unspecified error".
I don't have visibility to the stored procedure, but using describe
from sqlplus, I got the following:
SQL> describe myproc
Parameter Type Mode Default?
----------------------- ---------- ---- --------
P_1 VARCHAR2 IN
P_2 NUMBER IN
P_3 NUMBER IN
P_4 NUMBER IN
P_5 NUMBER IN
P_6 NUMBER IN
P_7 NUMBER IN
P_8 NUMBER IN
P_9 NUMBER IN
P_10 NUMBER IN
P_11 DATE IN
P_12 VARCHAR2 IN
P_OUT_ERROR_TXT VARCHAR2 OUT
P_OUT_CURSOR REF CURSOR OUT
Most of the parameters can be set to NULL.
Here is part of my code:
spCmdOracle->CommandType = adCmdStoredProc;
spCmdOracle->CommandText = "{CALL myproc(?,?,?,?,?,?,?,?,?,?,?,?,?)}";
_bstr_t strdata = "MYTEST";
_ParameterPtr p_1 = spCmdOracle->CreateParameter("@p_1",
adVarChar, adParamInput,
strdata.length(), strdata);
spCmdOracle->Parameters->Append(p_1);
_variant_t d((float)25);
_ParameterPtr p_2 = spCmdOracle->CreateParameter("@p_2",
adSingle, adParamInput,
sizeof(float), d);
spCmdOracle->Parameters->Append(p_2);
_variant_t a((float)40);
_ParameterPtr p_3 = spCmdOracle->CreateParameter("@p_3",
adSingle, adParamInput,
sizeof(float), a);
spCmdOracle->Parameters->Append(p_3);
_variant_t null_id;
_ParameterPtr p_4 = spCmdOracle->CreateParameter("@p_4",
adSingle, adParamInput,
sizeof(float), null_id);
spCmdOracle->Parameters->Append(p_4);
_ParameterPtr p_5 = spCmdOracle->CreateParameter("@p_5",
adSingle, adParamInput,
sizeof(float), null_id);
spCmdOracle->Parameters->Append(p_5);
_variant_t o((float)10000);
_ParameterPtr p_6 = spCmdOracle->CreateParameter("@p_6",
adSingle, adParamInput,
sizeof(float), o);
spCmdOracle->Parameters->Append(p_6);
_variant_t p((float)20000);
_ParameterPtr p_7 = spCmdOracle->CreateParameter("@p_7",
adSingle, adParamInput,
sizeof(float), p);
spCmdOracle->Parameters->Append(p_7);
_ParameterPtr p_8 = spCmdOracle->CreateParameter("@p_8",
adSingle, adParamInput,
sizeof(float), null_id);
spCmdOracle->Parameters->Append(p_8);
_ParameterPtr p_9 = spCmdOracle->CreateParameter("@p_9",
adSingle, adParamInput,
sizeof(float), null_id);
spCmdOracle->Parameters->Append(p_9);
_ParameterPtr p_10 = spCmdOracle->CreateParameter("@p_10",
adSingle, adParamInput,
sizeof(float), null_id);
spCmdOracle->Parameters->Append(p_10);
_ParameterPtr p_11 = spCmdOracle->CreateParameter("@p_11",
adSingle, adParamInput,
sizeof(float), null_id);
spCmdOracle->Parameters->Append(p_11);
_ParameterPtr p_12 = spCmdOracle->CreateParameter("@p_12",
adBSTR, adParamInput,
sizeof(float), null_id);
spCmdOracle->Parameters->Append(p_12);
char out_error[1000];
_ParameterPtr p_13 = spCmdOracle->CreateParameter("@P_OUT_ERROR_TXT",
adBSTR, adParamOutput,
sizeof(out_error), out_error);
spCmdOracle->Parameters->Append(p_out_error_txt);
spRsOracle = spCmdOracle->Execute(NULL, NULL, adCmdStoredProc | adCmdUnspecified );
The last line caused an exception with "Unspecified error".
If I change the setting of spCmdOracle->CommandText to use the stored procedure name
instead of the excape sequence, I got an error message PLS-00306:
Code meaning = IDispatch error #3092,
Description = ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'MYPROC'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
I am new on this subject and cannot find any help nearby. I would greatly appreciate any
here can give me some hint on what could be wrong.
Thanks.
|
|
|
|
|
hi there
i have 2 table with no relationship between them
and i need 2 extract from table1 the col1
and the phase of col1 in table2 col2
for example:
Table1
Col1
1000
1001
1002
=============================================
Table2
Col2
A1000A
bbb1000bcd
cdf1000frg
A1001a
bvc1001dcfe
=============================================
Result:
colRes count_in_table_2
1000 3
1001 2
1002 0
thanks in advance
roni vars
|
|
|
|
|
The following will result in a table scan, but it will do the job:
SELECT T1.Col1, COUNT(*) FROM Table1 T1
INNER JOIN Table2 T2 ON T1.Col1 LIKE '%' + T2.Col2 + '%'
Another alternative would be to create a full-text index on Table2.Col2. It all depends on how much data we're talking about and how often and how busy your server is when you run the query.
|
|
|
|
|
hi there thaks for your quick answer
it's not working performance it's not issue
i work with small table
thanks again
any suggestion
roni vars
|
|
|
|
|
hi again
i think u forget the group by T1.Col1
SELECT T1.Col1, COUNT(*) FROM Table1 T1
INNER JOIN Table2 T2 ON T1.Col1 LIKE '%' + T2.Col2 + '%'
group by T1.Col1
it's still not working
roni vars
|
|
|
|
|
Sorry,
I wrote that a little quickly. Yes, I forgot the GROUP BY, but the main problem was I reversed the references on the join . Try this (I tested it this time  ):
SELECT T1.Col1, COUNT(*) FROM Table1 T1
INNER JOIN Table2 T2 ON T2.Col2 LIKE '%' + T1.Col1 + '%'
group by T1.Col1
|
|
|
|
|
hi Mark
i'm sorry but is still doesn't work i try both sql
and return me 0 rows
SELECT T1.Col, COUNT(*) FROM tbl1 T1
INNER JOIN tbl2 T2 ON T2.col LIKE '%' + T1.col + '%'
group by T1.Col
SELECT T1.Col, COUNT(*) FROM tbl1 T1
INNER JOIN tbl2 T2 ON T1.Col LIKE '%' + T2.Col + '%'
group by T1.Col
i also try this 2 sql sentences and is stil doesn't work
its return me
1000 0
1001 0
etc....
1)
select t1.col ,(SELECT count(*) FROM tbl2 t2
WHERE t1.col in (select t2.col from tbl2 t2))
From tbl1 t1
2)
select tbl1.col ,count(case when CHARINDEX(tbl1.col ,tbl2.col) > 0 then 1 else null end)
from tbl1,tbl2
group by tbl1.col
by the way i work with sql server 2005
thanks again
waiting for rescue
|
|
|
|
|
I'm running 2005 also. This script:
IF OBJECT_ID('Table1') IS NOT NULL
DROP TABLE Table1
IF OBJECT_ID('Table2') IS NOT NULL
DROP TABLE Table2
go
CREATE TABLE Table1(Col1 Varchar(15))
CREATE TABLE Table2(Col2 VARCHAR(100))
INSERT INTO Table1 VALUES('1000')
INSERT INTO Table1 VALUES('1001')
INSERT INTO Table1 VALUES('1002')
INSERT INTO Table2 VALUES('A1000A')
INSERT INTO Table2 VALUES('bbb1000bcd')
INSERT INTO Table2 VALUES('cdf1000frg')
INSERT INTO Table2 VALUES('A1001a')
INSERT INTO Table2 VALUES('bvc1001dcfe')
SELECT T1.Col1, COUNT(*) FROM Table1 T1
INNER JOIN Table2 T2 ON T2.Col2 LIKE '%' + T1.Col1 + '%'
GROUP BY T1.Col1
Gives me these results:
1000 3
1001 2
If this isn't working for you, you'll need to give me your schema and what the results are (or the error messages).
|
|
|
|
|
thanks mark is work fine when the column type is varchar
but when the type is nchar it's doesnt work becuse it's fix length
roni vars
|
|
|
|
|
hi mark
You realy help in this Query
You are like Books Online
thanks again for your important help
and if it is nchar column type need to use ltrim rtrim function
SELECT T1.Col1, COUNT(*) FROM Table1 T1
INNER JOIN Table2 T2 ON ltrim(rtrim(T2.Col2)) LIKE '%' + ltrim(rtrim(T1.Col1)) + '%'
GROUP BY T1.Col1
regards
roni vars
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
