|
Hi
I know how to get column name in sql server.U try it in access
Hope it will be working.
SELECT COL_NAME(OBJECT_ID(Tablename), index of column)
Thank
monika
software developer
|
|
|
|
|
Hi;
How could I find out if the record im delteing from a table is related to other tables in my database.
Say if I have a Table A and Table B and there is a record in table A which is related to table B data then dont delete it and if no then deletion is ok.
Is there any specific sql query for that?
Thanks,
Riazi
-- modified at 8:26 Saturday 4th November, 2006
|
|
|
|
|
delete from TableA
where not exists(select IDColumn
from TableB
where TableB.IDColumn = TableA.IDColumn)
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Hi Eric;
Thanks for the reply. But this is not what I wanted. I know I could use this simple sql but what if I have more tables and want to check them all. I thougt there is a specific query to do this simpler for me. you know a command like "SELECT RELATED(TABLE A) FIELDS FROM ALL TABLES" and get the results if any thing related.
Is there any thing like that at all?
Thanks again
Riazi
|
|
|
|
|
EricDV gave you a valid answer. You have to repeat it for each related table. You could create a stored procedure that does a general thing by have it query the database schema and have it generate and execute the SQL you need but that would take some time to put together.
|
|
|
|
|
I'm having a quite known problem, but I simply cannot find my way around it.
There's a DataSet wich holds the data collected from the database. When I add a row on a table in the dataset, and I update the database with the changes from the DataSet, the database id and the dataset row id are different.
I've tried ways of working with DataRowVersions and getting back an identity from the db(stored proc), but no luck.
Could anyone point me to a good example or way of working with this problem?
Thanks
|
|
|
|
|
Why not use the DataRow.RowState property?
|
|
|
|
|
i have a query for SQL SERVER Any body help me.
"a table which does not have any Primary key" ok!
this table have a 100 Rows with same data in every rows or (all table has same data like "pavan").
and i want to have only two Rows; and all 98 will be deleting form the table."
Note:
1) this table does not have any kind of Unique Value in Row
2) this table have only one Column(fldName) which consist a same data like "pavan"
3) we have required only 2 Row from this table, all rows will be delete from this table.
please help me write me Query.
Pavan Pareta
|
|
|
|
|
This is an idea.
You will need to implement it in SQL:
1. Select top(2) into temptable
2. Delete all from your table
3. insert into your table values from temptable
Sincerely,
Elina
Life is great!!!
Enjoy every moment of it! 
|
|
|
|
|
hi Elina
i had try number of time but its not working.
please give me right solution.
thanx
Pavan Pareta
|
|
|
|
|
What exactly you tried?
Here is the idea. Translate it to SQL
Select TOP(2)
INTO TempTable
FROM yourTable
now you can drop your table and rename the temp one.
OR TRUNCATE yourTable and move rows from tempTable into yours.
Sincerely,
Elina
Life is great!!!
Enjoy every moment of it!
|
|
|
|
|
the example is:
strConnect = _T("Provider=sqloledb;Data Source=MyServerName;"
"Initial Catalog=MyDatabaseName;"
"User Id=MyUsername;Password=MyPassword;");
mine is:
Provider=sqloledb;Data Source=ronald-40gb;
Initial Catalog=northwind; User Id=ronald;Password=;
why it always show error messages ===> connection failed
what should i do? thanks before.
|
|
|
|
|
mine is:
Provider=sqloledb;Data Source=ronald-40gb;
Initial Catalog=northwind; User Id=ronald; password=1234;
why it always show error messages ===> connection failed
what should i do? thanks before.
|
|
|
|
|
Is the database runnning? Is the name of the server correct? Is the SQL Server accepting TCP/IP requests or is it using just named piped? (If the latter then try changing the server name to "(local)") Have you supplied valid user name and password? (Why aren't you using a trusted connection?)
|
|
|
|
|
the way i always create connection strings:
create a text file called foo.udl
double click it:
set up your params and test
open file in notepad and copy and paste the conn string out. This way you know that you have a string that managed to connect
HTH
Russ
|
|
|
|
|
i think i've seen the problem with your conn string.
i guess you're using an SQLConnection object. This object doesn't need to be told the provider as it is sql specific.
Russ
|
|
|
|
|
I have create a SQL there r some calculation involve, but i got this error:Divide by zero error encountered.
But there is some which work when i select some others DATE but with some other date which no result came out becasue "Divide by zero error encountered." how can i make the SQL will just continue show those value out even is 0.01... something??
select bizdate,concesscode,con.<br />
<br />
[description],totaltrans,totalqty,totalwgt,totalsales,taxper,<br />
<br />
(totalsales*taxper/100) as TaxAmt,baseamt,level3per as APPLY ,<br />
<br />
(level3per*(totalsales-Taxper/100))as LeaseAmt,(totalsales-baseamt-<br />
<br />
(totalsales*taxper/100)-(level3per*(totalsales-taxper/100)))as <br />
<br />
AmountDue<br />
,discountamt, returnamt,(totalsales-totalcost)*100/totalsales as GP <br />
from tot_item ti, concess con <br />
where con.code=ti.concesscode and ti.bizdate='2006-04-11' and<br />
<br />
concesscode>='A' and concesscode<='D'
|
|
|
|
|
Try adding
" and totalsales<>0"
to your where clause
Chandra
|
|
|
|
|
All,
I have the following table
CREATE TABLE TEST
(
TEST1 binary(32)
)
go
The TEST1 column is a binary with 256 bits.
How can I write T-SQL code to turn an arbitrary bit in TEST1 on or off, or check its state?
You obviously get arithmetic overflow if you try something like:
INSERT INTO TEST VALUES(0)
go
UPDATE TEST SET TEST1 = TEST1 | POWER(2, 137) /* Try to set bit 138 to "on" */
go
"............ Arithmetic overflow occurred ......."
Any ideas?
cheers,
Neil
|
|
|
|
|
It's really hard. If you want to support yes/no type values in SQL, use a bit column. The database - well, SQL Server, at least - stores bit columns by bit manipulation in a field that's just long enough to store all the bit columns in a row. That is, if you have 8 bit columns in a particular table, they will only take one byte of space per row.
|
|
|
|
|
Many thanks for your post.
I thought of using the bit type, but then I'd have to have to 256 columns!
Not ideal, and it would be hard to correlate them as well. And how would I update column 178 in code without some kind of array?
I am looking into using a combination of
SUBSTRING(), STUFF(), CAST(), CONVERT() and ASCII() .. I'm sure it's possible with some work!
I thought someone must have done this .. don't want to reinvent the wheel..
cheers,
Neil
|
|
|
|
|
I have worked out how to do this, so I thought I should post the answer here in case anyone is interested:
INSERT INTO TEST VALUES(0)
/* Turn bit 137 on */
UPDATE TEST SET TEST1 = CONVERT(binary(32), STUFF(TEST1, 137 / 8 + 1, 1, CONVERT(binary(1), SUBSTRING(TEST1, 137 / 8 + 1, 1) | POWER(2, 137 % 8))))
/* Test bit 137 value */
SELECT SUBSTRING(TEST1, 137 / 8 + 1, 1) & POWER(2, 137 % 8) FROM TEST
/* Turn bit 137 off */
UPDATE TEST SET TEST1 = CONVERT(binary(32), STUFF(TEST1, 137 / 8 + 1, 1, CONVERT(binary(1), SUBSTRING(TEST1, 137 / 8 + 1, 1) & ~POWER(2, 137 % 8))))
cheers,
Neil
|
|
|
|
|
The int datatype is 4 bytes.
CREATE TABLE BitTesting<br />
(<br />
TestField int<br />
)<br />
insert into BitTesting (TestField) values (0)<br />
UPDATE BitTesting SET TestField = TestField | 138
| is the bitwise OR operator.
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Many thanks for your post.
That idea is fine, but an int has only 4 bytes. That is just 32 bits ... I need a much longer array of bits (256). And I need to be able to set on/off 1 bit at a time... your example of 138 is actually turning on 3 bits:
138 = 128 + 8 + 2 = 10001010
So I am basically running into problems with large numbers....
cheers,
Neil
|
|
|
|
|
Oh...guess I didn't read your initial post carefully enough. Maybe you could just have a separate table with the following columns:
<br />
FKeyID int<br />
<br />
BitLoc tinyint<br />
<br />
BitVal bit
This table could have 256 records for each record in the parent table (BitLoc would be 0 to 255).
Just another idea.
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
