|
Thank you, got the answer.
Logu
|
|
|
|
|
What database do you use?
|
|
|
|
|
Good Day,
When using stored procedures with multiple statements, how do you tell your stored procedure to rollback any changes when the stored procedure did not successfully executed all statements?
Thanks!
It is said that the most complex structures built by mankind are software systems. This is not generally appreciated because most people cannot see them. Maybe that's a good thing because if we saw them as buildings, we'd deem many of them unsafe.
|
|
|
|
|
do a begin tran, execite your code and after each piece check @@error - if its <> 0 rollback tran and return. At the end commit tran
create proc p1
as
DECLARE @SqlErrorNum int,
@SqlErrorText varchar(255),
@Process varchar (64)
select @Process = object_name(@@procid)
begin tran
update table ....
select @SqlErrorNum = @@ERROR
IF @SqlErrorNum != 0
BEGIN
select @SqlErrorText = 'updating table'
goto ErrorHandler
END
update table2 ....
select @SqlErrorNum = @@ERROR
IF @SqlErrorNum != 0
BEGIN
select @SqlErrorText = 'updating table2'
goto ErrorHandler
END
commit tran
return 0
ErrorHandler:
SELECT @SqlErrorText = @Process + 'ERROR ' + @SqlErrorText
INSERT INTO T_In_thinkFolioOvernightLog (processName,note) values (@Process,@SqlErrorText)
RAISERROR @SqlErrorNum @SqlErrorText
RETURN 1
Hope this helps
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Thanks, solved my problem. 
It is said that the most complex structures built by mankind are software systems. This is not generally appreciated because most people cannot see them. Maybe that's a good thing because if we saw them as buildings, we'd deem many of them unsafe.
|
|
|
|
|
i made simple program to insert data in table,it shows the problem that operation must be updateable query.it works well on
local computer but i load on Internet website it shows error that it must be updateable query.
My Code is as follows:
OleDbConnection conn;
OleDbDataAdapter dataAdapter;
DataSet ds;
string cs;
conn = new OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source="+
Server.MapPath("db1.mdb"));
conn.Open();
cs="INSERT INTO trader(name)values(@firstname)";
OleDbCommand dc=new OleDbCommand(cs,conn);
OleDbParameter fparam=new OleDbParameter("@firstname",OleDbType.VarChar,10);
fparam.Value=TextBox1.Text;
dc.Parameters.Add(fparam);
dc.ExecuteNonQuery();
conn.Close();
|
|
|
|
|
I would guess that the Access database is Read Only when it is put on the website.
|
|
|
|
|
SQL novice here..
Hi, I'm having trouble formulating an SQL query and tbh I'm not even sure if its possible to do what I'm trying to do [without a cursor].
I have two tables with a standard 1->M relationship. If a certain field in ALL the linked records are set to a certain value, I need to update a field in the parent record, if the field is set to a different value in one of the linked records, i do nothing. How should I phrase the query?
Edit for clarification: Lets say it is a Client table, with a related Tasks table, and the intermediate ClientTasks table. If all the Tasks related to a Client are marked as done, I need to update a field in the Client's record to mark the client as having no outstanding tasks.
Thanks in advance..
modified on Tuesday, June 17, 2008 4:26 AM
|
|
|
|
|
can u clarify Your question
regards
Joe
|
|
|
|
|
|
hi
from your clarification i understand that you have to update a master table an a child table...you can handle this either in Stored procedure or in Code itself
Regards
Joe
|
|
|
|
|
There are several ways, but the quickest (assuming decent indexing) should be along the lines of
update client
set alldone = 'Y' -- signifies no outstanding tasks
from client c
where not exists (select 1 from clienttasks t where t.clientid = c.clientid and t.alldone = 'n')
This may not mathc your tables, but you should get the general idea I hope.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
select IntValue+VarcharValue from tbl
but it's not possible so how to convert IntValue in varchar in query only like
select convert.tostring(IntValue)+VarcharValue from tbl
what can I do despite of convert.toString because convert.Tostring wrong
thanks
You get the best out of others when you give the best of yourself.
|
|
|
|
|
hi
You can use Convert function for this.
like
select convert(varchar,Intvalue)+varcharvalue from table.
Regards
Joe
|
|
|
|
|
Use Convert or Cast functions
SELECT Convert(VarChar(10), IntValue) + VarcharValue .....
SELECT Cast(IntValue, VarChar(10)) + VarcharValue .....
in the example I've used 10 characters i.e. max 10 degit number
Regards
KP
|
|
|
|
|
Try using SQL Books Online rather than putting .NET code into sql statements and expecting them to work.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Generally I return just the data from SQL Server and perform any formatting necessary in the 'client' code (i.e. the code that called into SQL Server).
DoEvents: Generating unexpected recursion since 1991
|
|
|
|
|
Hi...
Try This One Will Work
Select Convert(varChar(20), Role) + LogInID From LogIn
By
Take Care..
|
|
|
|
|
I have been thinking a bit about sql injection (and have read Colin Angus Mackay's very good article SQL Injection Attacks and Some Tips on How to Prevent Them[^]).
I am already using stored procedures et al, but am now wondering what best practice would be for string replacement in parameters.
For SQL Server 2K5, I am already using:
Replace ' with ''
But should I extend this to include:
Replace ; with [empty string]
Replace drop[space] with [empty string]
Are there any other replacements that could be done to make string parameters safer, or is this approach just overkill?
Any thoughts?
|
|
|
|
|
RCoate wrote: I am already using stored procedures et al, but am now wondering what best practice would be for string replacement in parameters.
For SQL Server 2K5, I am already using:
Replace ' with ''
Why? If the string is arriving as a parameter there should be nothing to replace. Unless you aren't parameterising your query properly.
Does your SqlCommand.CommandText look like this:
EXEC MyStoredProcedure @Name='John O''Conner';
RCoate wrote: But should I extend this to include:
Replace ; with [empty string]Replace drop[space] with [empty string]
If you replace ; with an empty string that means you can only submit one command at a time. You would also risk altering perfectly legitimate data if your replace was indiscriminate. Ditto with "Drop"
For example: Let's say a column was to contain some free form text. That text could include:
My favourite Dutch confectionary is known as "drop zoute"; It is the best.
If you are not careful you will alter perfectly legitimate data.
If you are, as you claim, already using stored procedures then you have little to worry about. The parameters and command are separated out by the .NET Framework (if you are using it properly - by adding the parameters to the Parameters collection on the SqlCommand.
|
|
|
|
|
Colin Angus Mackay wrote: Does your SqlCommand.CommandText look like this:
EXEC MyStoredProcedure @Name='John O''Conner';
No, I use code sort of like this;
cmd.CommandText = "SprocName"
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = [open connection object]
Dim pFirstParameter As SqlParameter = SetParam("@ParamName", ParameterDirection, Datatype, DataSize)
pFirstParameter.Value = theValue
cmd.Parameters.Add(pFirstParameter)
dr = cmd.ExecuteReader
Where SetParam is a small function that returns a sqlparameter.
The general type of sproc I use is:
@FirstParam varchar(100)
AS
IF EXISTS(SELECT * FROM MyTable WHERE (SearchField=@FirstParam))
BEGIN
SELECT
Field1,
Field2,
...
FieldN
FROM
MyTable
WHERE
SearchField=@FirstParam
END
For my purposes, One command at a time is all I want and I am aware of the problem with restricting free text. The thing is I have very few free text fields - Most of the stuff I am doing is membership/authentication, so I am primarily thinking of user names and passwords. User names are assigned (users can not choose) and I can put in a list of forbidden characters/terms if needed.
My question is really, how far should you take user data sanitation?
|
|
|
|
|
RCoate wrote: My question is really, how far should you take user data sanitation?
As far as is practical. But don't overdo it either. Don't sanitise stuff out where the framework already assists you.
There is nothing wrong with apostrophes when you use parameters properly.
If you must build up dynamic SQL (and there are a few cases where it is necessary) then check things like do the table and column names that you are dynamically adding to the SQL. Do they really exist? If not reject it.
|
|
|
|
|
--find day,month,year
--for day
select datediff(d,'01 may 2008',getdate())
--
--for month
select datediff(m,'01 jun 2006',getdate())
--
--for year
select datediff(year,'01 jun 2006',getdate())
above working fine but suppose difference is 1 year 4 month and 2 month 15 days then It's giving 1 year and 2 month respectively.
but I want completely so I can use this in case of expired user in my project.
User can be expired in 1 month,3 month and 1 year. So I'm not able to recognize.
thanks
You get the best out of others when you give the best of yourself.
|
|
|
|
|
You can subtract dates from each other without using the datediff, which makes for easier comparison of Dates.
This means you could do something along the line of:
<br />
IF GetDate() - '01 may 2008' > DateAdd(MONTH,3,'1900-01-01 00:00:00.000') <br />
PRINT('Current Date is more then 3 months from 01 may 2008')<br />
ELSE <br />
PRINT('Current Date is less then, or equal to, 3 months from 01 may 2008')<br />
Edit: a more simple method might be:
<br />
<br />
IF GetDate() - DateAdd(MONTH, 3, '01 may 2008') > 0<br />
PRINT('Current Date is more then 3 months from 01 may 2008')<br />
ELSE<br />
PRINT('Current Date is less then, or equal to, 3 months from 01 may 2008')<br />
This works in SQL Server 2005 at least.
Here I utilize the fact that internally datetime is represented by an integer, which means I can check it against 0 (which in SQL Server is the same value as 1900-01-01 00:00:00.000)
Clean the GetDate for its time portion to make the comparison more exact (or if you use SQL Server 2008, there is a Date datatype without the time portion).
|
|
|
|
|
thanks a lot
I have got what I wanted because of your kind help.
thanks
You get the best out of others when you give the best of yourself.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
