|
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.
|
|
|
|
|
data type for the column is 'datetime', column name could be anything.
|
|
|
|
|
If it's datetime then you can simply set a desired date to the column:
UPDATE TableName SET DateTimeColumn = CONVERT(datetime, '01/01/2006', 101) ...
which will use midnight for time portion
or you can use current date and subtract desired amount of days like:
UPDATE TableName SET DateTimeColumn = GETDATE() - 975 ...
which will today end up to 01/01/2006, but using current time
FYI, if the column name would be timestamp, it is hardcoded to many libraries that is must have timestamp as data type. This will lead to problems in some cases (typically in cursor handling) if the actual datatype isn't timestamp.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
