|
Where are stored childs of subject? In which table?
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
can you give structure of tables you are talking about and structure of output you need?
|
|
|
|
|
You might want to have a google and look at recursive common table expressions (CTE) (if you're on SQL2005).
|
|
|
|
|
OK, here is the deal. I've got a table with 2 columns: "ID"(int), "Name"(string). "ID" - is a unique key field used in relations with other tables, but the values of the "Name" should be unique as well. I need to add more data into this table, but not to create some duplicate information.
I know about INSERT DISTINCT, but I am not using an SQL command, instead I use a VS generated TableAdapter (in my case it is named "companiesTableAdapter"). Here is a small code snippet:
this.companiesTableAdapter.Insert("XYZ Company");
this code does insert the string into a table, but it doesn't care if "XYZ Company" is already there.
(the DB itself is created in Access2003, if this may be helpful)
If someone has done something similar, I would really appreciate their help.
|
|
|
|
|
First check if that name exists, in case if exists then don't insert otherwise do insert.
example:
if(select count(name) from tablename where name='value')=0 <br />
begin<br />
insert into tablename values ('idvalue','namevalue')<br />
end<br />
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you then save my time by voting my post.
|
|
|
|
|
Worked great! Thanks a lot
|
|
|
|
|
No problem.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
You can add an unique index on the name column.
|
|
|
|
|
How can I do that? (Add a unique numerical index to a column)
|
|
|
|
|
Simply really
Create unique index fred on myTable(myColumn)
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi all,
I need to be able to allow characters like (') into a column in MYSQL, I have tried using 'text' and 'blob' but both of these seem to reject any inserts. the only way I found is to place a backslash infront of the character (\').
The only problem Is the application will be public and I will not be able to explain this to every user.
oh, also using fckeditor to forat text.
Any ideas?
Really appreciate your thoughts.
D.
|
|
|
|
|
example:
<br />
'this string contain''singlequotes'''
result
<br />
this string contain'singlequotes'
-----------
<br />
'this string contain''singlequote'
result
<br />
this string contain'singlequote
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you then save my time by voting my post.
|
|
|
|
|
Is that valid for MYSQL? I thought that was SQL Server syntax?
|
|
|
|
|
Yes it is SQL Server syntax.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
dgreat1 wrote: Any ideas?
Use parameters, let ADO.NET figure it out.
|
|
|
|
|
Good Afternoon,
I am fairly new to access coding. I am trying to write a SQL in access that will get make a packing slip based upon the return shipping address, but if the return shipping address is null, then the shipping would be the order address.
I started with this, but it is not working
=IIf(IsNull([Returns]![Address]),[ Orders]![Addr1], [Returns]![Address])
& IIf(IsNull([Returns]![Address]),[ Orders]![Addr2],[Returns]![Address])
& IIf(IsNull([Returns]![Address]),[ Orders]![Addr3], [Returns]![Address])
& IIf(IsNull([Returns]![City]),[ Orders]![City],[Returns]![City])
& IIf(IsNull([Returns]![State]),[ Orders]![State],[Returns]![State])
& IIf(IsNull([Returns]![Zip]),[ Orders]![Zip],[Returns]![Zip])
& IIf(IsNull([Returns]![Country]),[ Orders]![Country],[Returns]![Country])
& IIf(IsNull([Returns]![Phone]),[ Orders]![Phone],[Returns]![Phone])
So, what I wanted was the person wanted to return a product, and his return address is different from his order address, So I wanted some kind of jet SQL script to say if this person put in a return address then the packaging slip would use that but if that person did not put in a return address, then it would use the order address where that return address is null.
I appreciate anyones help on this, thank you
|
|
|
|
|
IIRC you can create functions within Access where you can write the code to return 1 address (decision is made in the function) and then use the function in your select statement.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Can I get an example of this?
|
|
|
|
|
Nope, I have not used Access in 10 years and that is the way I like to keep it.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Can anyone tell me how to change time for a DB in SQL Server 2005? I need to correct the time for a database, thanks.
|
|
|
|
|
You set the time of the windows server.
|
|
|
|
|
so you mean the database will use the time of windows server. my issue is for a specific database, say DB002, I wanna change its current time setting (set its current time 01-01-2006), can I do that?
|
|
|
|
|
SQL Server uses windows clock which then again uses clock from BIOS. So you can have only one time at the server which is the time of all databases.
I don't know what happens if you set up a virtual PC. Since virtual PC has emulated bios it could be possible to have "several times" on one physical server. But... this is just a guess.
|
|
|
|
|
maybe we got into a wrong direction. let me clarify this issue. in one of my databases, I have some tables having timestamp columns, now I just wanna change displayed time for those columns. Can I just use some scripts like: update TABLE ** set **=getdate()
|
|
|
|
|
Sorry, I misunderstood you.
Is the column's data type timestamp? Timestamp-typed columns cannot be modified via DML. Moreover they are not timestamps, but only unique identifiers that database engine update.
|
|
|
|