|
Hi
I want to update my DataBase (using a data grid) when going to next record...
how can i do it?
tanx
|
|
|
|
|
Hi! Is it possible to do multi-table update in one datagrid?
|
|
|
|
|
Hello,
Is there anything in SQL Server 2000 that I can use to perform a calculation returning the minimum of 2 values. For example:
SET @value = min(@a, @b)
I realise that there is a min() aggregate function, but I don't want that as I am wanting to use this in a computed column. (I have a value calculated from other fields, but must be capped at some limit)
Any ideas?
"You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar
Coming soon: The Second EuroCPian Event
|
|
|
|
|
Hi Colin. How about building your own function?
CREATE FUNCTION MinValue(@val1 decimal, @val2 decimal)
RETURNS decimal
AS
BEGIN
declare @retValue decimal;
If (@val1 < @val2)
set @retValue = @val1
Else
set @retValue = @val2
Return(@retValue);
END
|
|
|
|
|
Yeah! I've done something similar to that - I was just hoping that I'd missed a nice piece of documentation that says there is a built in function for it.
C'est la vie.
"You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar
Coming soon: The Second EuroCPian Event
|
|
|
|
|
Well, if you missed it, so did I
|
|
|
|
|
Hi There
Please try this (Not sure if this is what you are looking for:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Table1]
GO
CREATE TABLE [dbo].[Table1] (
[a] [int] NULL ,
[b] [int] NULL ,
[c] AS (isnull(case when (isnull([a],0) > isnull([b],0)) then (isnull([b],0)) else (isnull([a],0)) end,0))
) ON [PRIMARY]
GO
insert into [table1] values (1,3)
insert into [table1] values (22,3)
insert into [table1] values (1,1)
select [a],[b],[c] from [table1]
Selecting [c] seems to always return the smallest of [a] or [b]
Regards
Peet Schultz
YASP
|
|
|
|
|
Is there any way to rename a table through t-sql, other than using sp_rename.
It's not a bug, it's an undocumented feature. suhredayan@omniquad.com
messenger :suhredayan@hotmail.com
|
|
|
|
|
i want to do it on MS-Sql server.
It's not a bug, it's an undocumented feature. suhredayan@omniquad.com
messenger :suhredayan@hotmail.com
|
|
|
|
|
Look for sp_rename in SQLServer help.
Mazy
"One who dives deep gets the pearls,the burning desire for realization brings the goal nearer." - Babuji
|
|
|
|
|
i think you have not read my question clearly. i was looking for any sql statement not for a stored procedure. cos i want to do it in access2000.
It's not a bug, it's an undocumented feature. suhredayan@omniquad.com
messenger :suhredayan@hotmail.com
|
|
|
|
|
Yes , but in your second post you have mentioned you want to do it in MSSQLServer.
Mazy
"One who dives deep gets the pearls,the burning desire for realization brings the goal nearer." - Babuji
|
|
|
|
|
AKAIK it's not possible in Access without drop / create.....(though SteveS may have a view)
"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox
|
|
|
|
|
Hi
I have an application where I have to open oracle database read some data and update the tables using C# and ADO.Net . I will use OLE Oracle connection for that . The problem is that I have no Idea about oracle , never used it . How are oracle files named , what are the parameteres I have to pass in my connection to be able to open the Oracle database , access the tables then update it again .
" We have either to find a way , or make one "
Hanipal
Dean
|
|
|
|
|
Here is a bit to get you started.
OracleConnection dbConn;
dbConn = new OracleConnection( "DataSource=NameOfDatabase; User ID=USERID; Password=PASSWORD" );
dbConn.Open()
To execute a query, try
string SomeSelect( "select sysdate from dual" );
OracleCommand command = new OracleCommand ( SomeSelect, dbConn);
OracleDataReader query = command.ExecuteReader();
query.Read();
DateTime DBDateTime = query.GetDateTime(0);
Chris Meech
We're more like a hobbiest in a Home Depot drooling at all the shiny power tools, rather than a craftsman that makes the chair to an exacting level of comfort by measuring the customer's butt. Marc Clifton
VB is like a toolbox, in the hands of a craftsman, you can end up with some amazing stuff, but without the skills to use it right you end up with Homer Simpson's attempt at building a barbeque or his attempt at a Spice rack. Michael P. Butler
|
|
|
|
|
Ome more small quastion , ( DataSource=NameOfDatabase ) In oracle file , what would be the NameOfDatabase file extention is it *****.OEA or what extention ?
We will either Find a Way , Or Make One !
Hanipal
Dean
|
|
|
|
|
This stuff is still going to depend upon Oracle client being installed. When you install Oracle client, it typically will walk through an initial network setup of a remote database. During this setup, you will need to provide the local name that defines the remote database. It is this local name that you provide as 'NameOfDatabase'. It is not a file name or anything like that.
I have used this code to connect to both Oracle 8 and Oracle 9 databases. While I haven't tried specifically with an Oracle 7 database, I would expect that it would still connect okay.
Chris Meech
We're more like a hobbiest in a Home Depot drooling at all the shiny power tools, rather than a craftsman that makes the chair to an exacting level of comfort by measuring the customer's butt. Marc Clifton
VB is like a toolbox, in the hands of a craftsman, you can end up with some amazing stuff, but without the skills to use it right you end up with Homer Simpson's attempt at building a barbeque or his attempt at a Spice rack. Michael P. Butler
|
|
|
|
|
Talking about oracle versions , is there a major difference in operation between Oracle 8 and oracle 9 . If I am going to start learning oracle fresh , is it better to start with 8 or 9 .
We will either find a way or make one !
Hanipal
Dean
|
|
|
|
|
I don't think it will matter which version you end working with in order to learn Oracle. My recommendation though would be to take a couple of hands-on training courses. The first would either be a beginner or intermediate course in how to use SQL. Then after you have become comfortable with that knowledge and perhaps even been programming, you should step up and take a database administration course. I've always felt that my best understanding of the how/what/why Oracle does, was a result of an administration course. Good Luck to you.
Chris Meech
We're more like a hobbiest in a Home Depot drooling at all the shiny power tools, rather than a craftsman that makes the chair to an exacting level of comfort by measuring the customer's butt. Marc Clifton
VB is like a toolbox, in the hands of a craftsman, you can end up with some amazing stuff, but without the skills to use it right you end up with Homer Simpson's attempt at building a barbeque or his attempt at a Spice rack. Michael P. Butler
|
|
|
|
|
This is a wierd one, 'cuz it was working just fine yesterday, and I think it has to do with _bstr_t conversions, but ....
The text in subject is that which is shown in a debug assertion error message box displayed when the m_pConn->Open(...) method shown below is called. When this line of code is executed, I am converting a CString (connectionString) to a _bstr_t. The full text of the error message is "Debug Assertion Failed! dbgdel.cpp Line: 52 _BLOCK_TYPE_IS_VALID". Can anyone shed some light on this error?
_bstr_t tablesNames;
_ConnectionPtr m_pConn;
m_pConn.CreateInstance (__uuidof(Connection));
CString connectionString;
if (usingAccess_1)
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source = " + accessDBPath2;
else
connectionString = "Provider=SQLOLEDB;Data Source=" + mvwin32SQLsrvName + ",1433;Network Library=DBMSSOCN;Initial Catalog=" + mvwin32SQLsrvDB + ";" + "Trusted_Connection=Yes;";
m_pConn->Open( _bstr_t( connectionString ), _bstr_t( "" ), _bstr_t( "" ), adConnectUnspecified );
-dotBomb
|
|
|
|
|
I have a function created in SQL Server 2000 that returns a comma delimited list of text values from a 1 to many table. At the client's site they use SQL Server 7.0. Can you compile Functions in SQL 7.0 or is this a new feature in 2000? If you can how do you do it? I am having difficulty compiling.
CREATE FUNCTION fn_SamplesTerminalPortWellLocationText
(
@SampleID int
)
RETURNS varchar(1000)
AS .... Error Message
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'FUNCTION'. Thanks,
Michael
I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)
|
|
|
|
|
It's a feature that was added in SQL Server 2000. I'd consider making a stored procedure with an output parameter in your case, but it's not going to be something you can use in a SELECT statement. You may be able to still accomplish your design goals in a nice way using a view.
Regards,
Jeff Varszegi
|
|
|
|
|
Thanks, creating a stored procedure is what I ended up doing. Most of the time the single output parameter would do fine. However, on the search screen they what the comma delimited list which worked fine at home on 2K but not on the client's 7.0. I'll take the view into consideration.
Thanks,
Michael
I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)
|
|
|
|
|
You're welcome. I thought you might enjoy this article on tricks for passing data around. Before I read that one, I didn't even know about INSERT EXEC, and I've been using SQL Server for some time now.
Regards,
Jeff Varszegi
|
|
|
|
|
Thanks again. I won't be back to the client's site until Monday but it was kind of frustrating today...grrr... Nonetheless, I haven't had a chance to look into the view scenario. But, since I am restricting the user search results to a max of 100 records, I think that the view scenario may be the best solution as this point. Most of the time the output parameter from a query will suffice. The database in the app will be coasting along since everything is cached. That is why I didn't want to do the string concantenation in the app. Here was the function I was using in 2K toc oncantenate the Terminal Port Well Locations (Oil Industry). It obviously failed in SQL 7.0.
CREATE FUNCTION fn_SamplesTerminalPortWellLocationText
(
@SampleID int
)
RETURNS varchar(1000)
AS
BEGIN
DECLARE @RowCount int,
@Debug bit,
@Results varchar(1000)
SELECT @Debug = 0,
@Results = ''
-- Loop thru all of the terminal port and concantenate them together
DECLARE @TerminalPortWellLocation varchar(75)
DECLARE TPWL CURSOR FAST_FORWARD FOR
SELECT tp.TerminalPortWellLocation
FROM tblSamplesSampleTerminalPortWellLocation stp,
tblSamplesTerminalPortWellLocation tp
WHERE stp.TerminalPortWellLocationID = tp.TerminalPortWellLocationID
AND stp.SampleID = @SampleID
AND (stp.Deleted Is Null Or stp.Deleted = 0)
ORDER BY TerminalPortWellLocation
OPEN TPWL
FETCH FROM TPWL
INTO @TerminalPortWellLocation
While @@Fetch_Status = 0
BEGIN
SELECT @Results = @Results + @TerminalPortWellLocation + ', '
FETCH FROM TPWL
INTO @TerminalPortWellLocation
END
-- Close & Deallocate Cursor
Close TPWL
Deallocate TPWL
-- Trim the last comma
-- Return the results
IF (DataLength(@Results) > 2)
SELECT @Results = SubString(@Results, 1, DataLength(@Results) - 2)
RETURN(@Results)
END
I have been using SQL for a while now as well, but hopefully you can help me in this area: Is there an easy way to execute a pivot table in SQL Server. I have completed the task but it is one UGLY procedure. It ended up being a cursor creating columns in a variable to be used in an exec(@variable) statement. Is there an easier way? I can send you the procedure but it is a couple hundred lines of code. Here is the begining of the main ugly query that is executing a string variable.
SELECT tp.OrderBy, tp.PropertyTestID, Replace(tp.PropertyTestName, ',', '') AS PropertyTestName,
SUM(CASE WHEN S.SampleID = 109 THEN ResultDecimal ELSE NULL END) AS [N/A109 03/31/2004],
SUM(CASE WHEN S.SampleID = 108 THEN ResultDecimal ELSE NULL END) AS [N/A108 03/30/2004],
SUM(CASE WHEN S.SampleID = 107 THEN ResultDecimal ELSE NULL END) AS [N/A107 03/17/2004],
SUM(CASE WHEN S.SampleID = 106 THEN ResultDecimal ELSE NULL END) AS [N/A106 03/01/2004],
SUM(CASE WHEN S.SampleID = 105 THEN ResultDecimal ELSE NULL END) AS [N/A105 01/05/2004],
SUM(CASE WHEN S.SampleID = 104 THEN ResultDecimal ELSE NULL END) AS [N/A104 11/17/2003]
FROM tblSamplesPropertyTest tp,
........[the rest of the query blah, blah blah...]
Obviously all subsequent queries from this main query are executed from a string variables as well. Functions are out of the question at this point as well. Is there a better way? Tell me there is...Point me in the right direction.
If you would like the entire procedure, I can email it to you.
Thanks again,
Michael
I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)
|
|
|
|
|