|
Write Query like this-
Select ISNULL([K1] + ';', '') + ISNULL([K2] + ';', '') + ISNULL([K3] + ';', '') AS AllK FROM TEST
Ofcourse I am using the property of SQL that sum of a NULL string with another string is NULL. So if any of the K1, K2 or K3 field is NULL, [K1] + ';' will give NULL.
HTH.
Gaurav
|
|
|
|
|
hi all i have the table like
[ number ] [ name ] [ k1 ] [ k2 ] [ k3 ] [ h1 ] [ h2 ] [ h3 ] [ allK ] [ allh]
[ 1 ] [ pp ] [ 2 ] [ 3 ] [ 4 ] [ 5 ] [ 6 ] [ 7 ]
[ 2 ] [ dd ] [ a ] [ b ] [ c ] [ d ] [ e ] [ f ]
how should i do to get following table?? from each and every single record, take k1,k2,k3 and put into allk as k1;k2;k3
similarly, take h1,h2,h3 and put into allh as h1;h2;h3
<br />
[ number ][ name ] [ k1 ] [ k2 ] [ k3 ] [ h1 ] [ h2 ] [ h3 ] [ allK ] [ allh ] <br />
[1 ] [ pp][2 ] [ 3 ] [ 4 ] [ 5 ] [ 6 ] [ 7 ] [ 2;3;4 ] [ 5;6;7 ] <br />
[ 2 ] [ dd ] [ a ] [ b ] [ c ] [ d ] [ e ] [ f ] [ a;b;c ] [ d;e;f ] <br />
|
|
|
|
|
Well, the way to go would definitively be by creating a computed column. See MSDN for details on how to do this with the CREATE TABLE command. The expression you need is something like this:
k1 + ';' + k2 + ';' + k3
so, the column on the create table would be defined as:
allk as k1 + ';' + k2 + ';' + k3
Doing things this way, you won't need storage space for allk, and it will be calculated as needed.
Another (better, in most cases) way is by using a view.
ORACLE One Real A$#h%le Called Lary Ellison
|
|
|
|
|
Thank you very much for your reply..
I'm totally new to SQL. I'm using Access. I have the form, the user select all the fields as neccessary, and insert to the table by clicking next. I dont' want to create the new table but getting the some coloumn fields data ( information ) and insert it to another column of the same row. I dont' even know if I have to use SQL.
similar to this table
<br />
[num][k1][k2][k3][h1][h2][h3][allK][allH]<br />
[001][aa][bb][cc][dd][ee][ff]<br />
<br />
<br />
updated table<br />
[num][k1][k2][k3][h1][h2][h3][allK][allH]<br />
[001][aa][bb][cc][dd][ee][ff][aa;bb;cc][dd;ee;ff]<br />
<br />
and I have 1000s of record in the same table.
Can you please tell me how to do it, can you also give me some SQL code if i need it.
Thank you Daniel
|
|
|
|
|
SELECT [k1] & ';' & [k2] & ';' & [k3] AS AllK FROM TEST;
this is the query statement that I got the table..
I have another problem.. how can I not entrying anything to the ALLK fields if the fields k1,k2,k3 contain empty text?
anyway of saying is how can i check whether k1,k2,and k3 contain emtpy text?
|
|
|
|
|
I just found out that the OLEDB driver for SQL Server 2000 does not support nested transactions. I've solved this by applying a small "hack" in a "helper" class of mine.
However, I feel that my hack isn't robust enough (I'm relying on an error code returned by CSession/ITransactionLocal::StartTransaction()). Surely, this is a problem many have bumped into. Do you have any favorite workarounds for this?
I have a couple of ideas up my sleeve, but I'm too tired to deal with this now (at the end of the day). So I figured you'd have some ideas which I could consider and compare to what I have in (my currently dizzy) mind tomorrow morning while sipping some tea. 
--
Now we live in a world of uncetainty
Fear is the key - to what you want to be
You don't get a say, the majority gets it's way
You're outnumbered by the bastards till the day you die...
|
|
|
|
|
Hi All!!
I have binded my text fields to DataColumns of table at runtime.In data navigation mode, all text fields of my application are disabled. When I press the 'Add New' button, all fields are enabled and cleared.
Problem is that when I focus to next control, all the data of current record is again loaded in all textboxes. While I want to remain them clear.
plzzzzzzzzzzzzzzzzz help me.
Thanx in Advance
sorry for my bad English.
|
|
|
|
|
Is there a way to get the last created identity column from access mdb
and what does the memo type map to in the OleDbType enum for access mdb
and the max length?
Thank You
Bo Hunter
|
|
|
|
|
|
Or the DataReader's GetBytes method (consider using the SequentialAccess command behaviour to optimise memory usage).
|
|
|
|
|
Friends,
In an application developed in Visual C++, i used ADO to connect to database. This application runs round the clock. Application establish five connections to the database server and we call stored procedures from SQL server which are very small.
The problem is that our application runs normally for about 13 or 15 hours, after that application starts getting Timeout Error from ADO (SQL server). This error can occur any time, sometimes it occurs just after 4 hours. On netstat at command prompt, i can see that all five connections to database server are in "Established" state. I am unable to find the reason of this error. I changed the operating system, i run both application and SQL server on same machine and on different machines, run my tests on different networks;...but nothing worked for me. Our tables are not too big.
Please tell me if you also experienced such problem
|
|
|
|
|
Use TCP/IP rather than named pipes.
onwards and upwards...
|
|
|
|
|
|
Shah Shehpori wrote:
Friends,
In an application developed in Visual C++, i used ADO to connect to database. This application runs round the clock. Application establish five connections to the database server and we call stored procedures from SQL server which are very small.
The problem is that our application runs normally for about 13 or 15 hours, after that application starts getting Timeout Error from ADO (SQL server). This error can occur any time, sometimes it occurs just after 4 hours. On netstat at command prompt, i can see that all five connections to database server are in "Established" state. I am unable to find the reason of this error. I changed the operating system, i run both application and SQL server on same machine and on different machines, run my tests on different networks;...but nothing worked for me. Our tables are not too big.
In my experience working with databases, long held connections are not reliable. I've worked with DB2, Oracle, SQL Server, Sybase, and all of them are simply not adequate for long term connections. My suggestion would be creating a connection pool and work with disconnected client-side recordsets, which is, btw, the way server applications normally work.
ORACLE One Real A$#h%le Called Lary Ellison
|
|
|
|
|
 I have 3 tables:
table1:
DateMod (smalldatetime)
StId
Eid
table2:
DateMod (smalldatetime)
StId
Wid
table3:
stid
I am selecting the max(Wid) and max(eid) based onstid:
select max(Wid),date
from table2 a,table 3
where a.stid=b.stid
group by a.stid
union
select max(eid),date
from table1 a,table 3
where a.stid=b.stid
group by a.stid
At this point I need to compare the dates between the results
(if sid listed in both results) and select the most resent value
from one of the tables for each stid.
How do I do this?
|
|
|
|
|
If this is against SQL Server or Oracle, you could create a stored proc to do this in two passes or get real complex using subqueries in one pass.
TWO PASS:
create procedure GetLastModDate
AS
declare @tStidDates (stid int NULL,
LastModDate smalldatetime NULL)
set nocount on
insert @tStidDates
(
stid,
LastModDate
)
select
A.stid,
MAX(A.Wid)
from
table2 a,
table 3 b
where
a.stid = b.stid
group by
a.stid
insert @tStidDates
(
stid,
LastModDate
)
select
A.stid,
MAX(A.eid)
from
table1 a,
table 3 b
where
a.stid = b.stid
group by
a.stid
select
stid,
MAX(LastModDate)
from
@tStidDates
group by
stid
order by 1
onwards and upwards...
|
|
|
|
|
for some reason SQL is not taking this line:
declare @tStidDates (stid int NULL,
LastModDate smalldatetime NULL)
|
|
|
|
|
Sorry, it should be
declare @tStidDates table (stid int NULL,
LastModDate smalldatetime NULL)
onwards and upwards...
|
|
|
|
|
How do I define my database connection path with out defining the entire path(ex. c:\program files\blah blah\data). I have the database stored in a directory \data\testdb.mdb just off the main program directory. I can not seem to make it read just the realative path. It is making me define the entire path. I have done this in the past but can not remember. Thanks for you help!
|
|
|
|
|
I need to programatically change an ODBC connection. So I was wondering, where is the ODBC connection 'stored'? Is it in the registry, a file somewhere, or what? My feeble efforts haven't been able to turn up anything useful... Thanks 
Paul 
Computers let you make more mistakes than any other invention in history...with the possible exception of handguns and tequila.
modified 18-Jul-18 11:59am.
|
|
|
|
|
OK found it thanks - in the registry... For those of you that were wondering:
HKLM\SOFTWARE\ODBC\ODBC.INI\<conn_name_here>
Paul
Computers let you make more mistakes than any other invention in history...with the possible exception of handguns and tequila.
modified 18-Jul-18 11:59am.
|
|
|
|
|
Yes. For more info on using the setup/config API, see ODBCINST.H.
onwards and upwards...
|
|
|
|
|
Hiya I am using ADO and need to be able to detect if a table already exists.
If the table exists, I need to drop the table and create this new table.
This is my code so far:
// create table and don't allow any zero value records
sqlcmd.Format( "CREATE TABLE Pxhsinv (Invoice varchar(30) NOT NULL,Invoice_Record varchar(8000) NOT NULL)" );
// execute the sql string
pConnection->Execute(_bstr_t(sqlcmd), RecordsAffected, 1);
// empty string
sqlcmd.Empty();
How do I detect if the table exists already..
Thanks.
|
|
|
|
|
I think that if the table already exists, the database will throw an error. Could you trap it?
|
|
|
|
|
Run the Query -
Select name from sysobjects where name like 'Pxhsinv' and type = 'U'
If this query gives some record in the resultset, the table is present else not.
Gaurav
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
