|
I have found a potential problem which may get some people stuck when they have customised their Regional Settings. Too short to submit as an Article. Moderators please do with it what is best.
I live in South Africa. PC set up for English (South Africa).
The problem is, Microsoft says that in South Africa we write our dates as MM/dd/yyyy, when in fact we (ordinary people) use dd/MM/yyyy.
So in Reqional Settings we use Customise and change our Short Date format to dd/MM/yyyy.
Everything works fine if you use datasets to update a table, as the data is already a DateTime. But when you use an INSERT INTO command a problem comes up because the DateTime needs to be placed between single-quotes:
"INSERT INTO (column) VALUES('" + myDate + "')"
In our case, this converts to:
"INSERT INTO (column) VALUES('dd/MM/yyyy')"
But the server is expecting:
"INSERT INTO (column) VALUES('MM/dd/yyyy')" because this is the default short-date format for South Africa.
So the following must be used:
"INSERT INTO (column) VALUES('" + myDate.ToString("MM/dd/yyyy") + "')"
This converts to:
"INSERT INTO (column) VALUES('MM/dd/yyyy')" which is what the server is expecting.
This problem will arise with any Customisations made, including separators. myDate.ToString() must specify the exact format the server is expecting.
Nigel
|
|
|
|
|
|
If needing to parse in date information like that (and you can't use parameterized as suggested by the previous poster), use the ISO format and it should be okay on all regional settings.
Of course there'll be problems if the regional settings are different from what you feed any conversion with. How else do you think it should know whether the date 04/05/2008 means either 4th May or 5th April. How else will you know whether numbers use decimal point period or comma etc.
That is the "problem" with conversions between regions.
|
|
|
|
|
Hi,
I have created a "test" table having 4 column Q1,Q2,Q3 and Q4 with values one,two,three,and four respectively. Please refer the code below:-
Declare @loopId int
set @loopId=1
Declare @vr varchar(100)
declare @variable varchar(100)
while @loopId<=4
begin
set @vr='Q'+''+Convert(varchar,@loopId)+''
Select @variable=@vr from test
print(@variable )
set @loopId=@loopId+1
end
In the above code I am printing the values of each column of table "test" one by one.But the out put of the above code is
Q1
Q2
Q3
Q4
but i want to have the values not column name.This is a sample code which I will be using in one of my stored proc.What wrong I am doing.Please help.
|
|
|
|
|
Pranav Thakur wrote: Select @variable=@vr from test
This line holds the problem. You wil need to create a dynamic query to display values.
|
|
|
|
|
hi...
Try This one....
Declare @loopId int
set @loopId=1
Declare @vr varchar(100)
declare @variable varchar(100)
Declare @qry VarChar(1000)
while @loopId<=4
begin
set @vr='Q'+''+Convert(varchar,@loopId)+''
Select @variable=@vr from test
print(@variable )
Set @qry = (Select @variable=@vr from test)
set @loopId=@loopId+1
Exec(@qry)
end
Take Care ....
sure there will not be any prob now if still having prob..then
reply..
take care...
By
|
|
|
|
|
hi all
i have develping an application in vb.net with mssql express 2005,
now i want to fetch records from a table with different conditions
i want to use a parameter as a column name in which i want to pass differnt field names and an other parametr to pass condition value.
here is the Sp by which i trying to fecth the data but could not....
pl help me
CREATE PROCEDURE TEMP1EMPINFO
--CREATE PROCEDURE TEMP1EMPINF
(
@Tbl_col_name varchar(100),
@Col_val varchar(100)
)
AS
Begin
Select * from table where @tbl_col_name= @col_val
end
GO
thanks
rmshah
Developer
|
|
|
|
|
You will have to build a SELECT statement dynamically and then execute it using sp_executesql[^]. But as far as I know it's not very good performancewise. Google for "dynamic SQL" and read about it.
How many different values of @Tbl_col_name will you be using?
Wouldn't it be easier to create a separate SP for each case?
|
|
|
|
|
Sure you can try this One...
Declare @qry VarChar(1000),
@clmname VarChar(100),
@tabname VarChar(100)
Set @qry = (Select @clmname from @tabname)
Exec(@qry)
hAVE A nICE DAY....
bY...
|
|
|
|
|
Hello sir,
I have a table (TreeMaster) with fields (TreeId, MemberName, ProductId) and I have 1 to 100 records.
Select x.TreeId, x.MemberName, x.ProductId
From TreeMaster x, TreeMaster y
Where x.ProductId = y.TreeId
The above result between 1 to 100 records.
But I want between 10 to 100 records only.
I don't know how to change the above query.
Logu
|
|
|
|
|
I'm assuming you meant TreeID between 10 and 100..
Select x.TreeId, x.MemberName, x.ProductId
From TreeMaster x, TreeMaster y
Where y.TreeId >= 10 and y.TreeId <= 100 and x.ProductId = y.TreeId
|
|
|
|
|
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
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
