|
I got it with
SELECT 'Announcement' as ContentType, Title, ItemID, ModuleID, CreatedByUser, CreatedDate, URL,
Users.UserID, Users.FirstName, Users.LastName
FROM Announcements
LEFT OUTER JOIN
Users ON Announcements.CreatedByUser = Users.UserID
WHERE DATEADD(d, 0, DATEDIFF(d, 0, CreatedDate)) >= DATEADD(d, -7, DATEDIFF(d, 0, GETDATE()))
UNION
SELECT 'Document' as ContentType, Title, ItemID, ModuleID, CreatedByUser, CreatedDate , URL,
Users.UserID, Users.FirstName, Users.LastName
FROM Documents
LEFT OUTER JOIN
Users ON Documents.CreatedByUser = Users.UserID
WHERE DATEADD(d, 0, DATEDIFF(d, 0, CreatedDate)) >= DATEADD(d, -7, DATEDIFF(d, 0, GETDATE()))
UNION
SELECT 'Links' as ContentType, Title, ItemID, ModuleID, CreatedByUser, CreatedDate , URL,
Users.UserID, Users.FirstName, Users.LastName
FROM Links
LEFT OUTER JOIN
Users ON Links.CreatedByUser = Users.UserID
WHERE DATEADD(d, 0, DATEDIFF(d, 0, CreatedDate)) >= DATEADD(d, -7, DATEDIFF(d, 0, GETDATE()))
ORDER BY CreatedDate DESC
|
|
|
|
|
Using a DateAdd of 0 is a waste. All you need is one DateDiff. Your where clause should look like this:
where DateDiff(dd,CreatedDate,GetDate()) <= 7
to show rows created in the past 7 days.
onwards and upwards...
|
|
|
|
|
I’m trying to extract the PK’s and FK’s of a table given it’s name.
I can get as far as confirming that a table has a PK by obtaining that PK’s entry in the sysindexes table, but now my problem is that I can’t seem to figure out what column in that table actually represents the PK.
In the sysindexes table it merely specifies that this is a primary key and this is the parent table, but not which column in that table.
What I really want, given a table name is:
myprimaryKeyColumnName[s]
myForeignKeyColumnName[s] & it's associated Table[s]
btw, I know it's in the sys* tables, but I'm just missing something and don't have my systables poster at home. 
Cheers,
Simon
sig ::
"Don't try to be like Jackie. There is only one Jackie.... Study computers instead.", Jackie Chan on career choices.
article :: animation mechanics in SVG picture :: my first abstract photo
|
|
|
|
|
Try sp_fkeys (tablename) to display the foriegn keys. Or, if you want to dig it out the hard way, try something like:
select
Object_Name(fkeyid) + '.' + Col_Name(fkeyid,fkey),
keyno
from
sysforeignkeys
where
rkeyid = Object_ID('YourPrimaryTableName')
order by
1, 2
onwards and upwards...
|
|
|
|
|
Try using ADO's Connection.OpenSchema method - haven't checked but think will give you the nec. info
"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox
|
|
|
|
|
|
I am using SQL Server 2000. I ran into a situation where I needed to increment a revision. The revision could be up to two letters in length. The code below works great. This is just for an example to anyone who may need it.
Check This out:
declare @r varchar(2)
declare @MaxRevLetter INT
declare @seriesLetter1 VARCHAR(1)
declare @seriesLetter2 VARCHAR(1)
declare @NewRevision VARCHAR(2)
-- SET Revision Letter(s) to Check
SET @r = 'CX'
SET @MaxRevLetter = ASCII('X')
-- test for length. If the Revision is only one letter then it is easy to handel.
IF LEN(@r) > 0 AND LEN(@r) < 2 BEGIN
-- if the revision letter is a Z then we can not increment it using the following method
-- so set it.
IF (@r != 'Z') BEGIN
IF ASCII(LTRIM(RTRIM(@r))) <> @MaxRevLetter BEGIN
SET @NewRevision = CHAR(ASCII(@r)+1)
END
END
ELSE BEGIN
SET @NewRevision = 'X'
END
END
ELSE BEGIN
-- get the letters in the Revision
SET @seriesLetter1 = SUBSTRING(@r,0,LEN(@r) )
SET @seriesLetter2 = SUBSTRING(@r,2,LEN(@r) )
-- because we know that if there is a Z the letter must be set and can not be
-- incremented. We need to test for a Z.
IF (@seriesLetter1 + @seriesLetter2 != 'ZZ' AND
@seriesLetter1 + @seriesLetter2 != 'ZX' AND
@seriesLetter1 + @seriesLetter2 != 'XZ' AND
@seriesLetter1 + @seriesLetter2 != 'XX') BEGIN
-- Ok if the first letter is NOT an X and the second letter is not an X
-- increment the second letter so AB would be AC
IF (ASCII(@seriesLetter2) <> @MaxRevLetter) BEGIN
SET @seriesLetter2 = CHAR(ASCII(@seriesLetter2)+1)
SELECT NewRevision = @seriesLetter1 + @seriesLetter2
END
ELSE BEGIN
-- ok the second letter was an X Ex: AX so we need to increment the first letter
-- and set the second letter back. AX would be BA
SET @seriesLetter1 = CHAR(ASCII(@seriesLetter1)+1)
SET @seriesLetter2 = 'A'
SELECT NewRevision = @seriesLetter1 + @seriesLetter2
END
END
ELSE BEGIN
-- ok there is some combo of X and Z in the string. so set the value
IF (@seriesLetter1 = 'X' AND @seriesLetter2 = 'Z') BEGIN
SET @seriesLetter1 = 'X'
SET @seriesLetter2 = 'X'
SELECT NewRevision = @seriesLetter1 + @seriesLetter2
END
IF (@seriesLetter1 = 'Z' AND @seriesLetter2 = 'X') BEGIN
SET @seriesLetter1 = 'X'
SET @seriesLetter2 = 'A'
SELECT NewRevision = @seriesLetter1 + @seriesLetter2
END
IF (@seriesLetter1 = 'Z' AND @seriesLetter2 = 'Z') BEGIN
SET @seriesLetter1 = 'Z'
SET @seriesLetter2 = 'X'
SELECT NewRevision = @seriesLetter1 + @seriesLetter2
END
IF (@seriesLetter1 = 'X' AND @seriesLetter2 = 'X') BEGIN
SET NewRevision = 'The Revision XX is the last revision possable.'
END
END
END
enjoy.
Let me know if you have any questions
Will
|
|
|
|
|
The following line is not correct:
IF (@seriesLetter1 = 'X' AND @seriesLetter2 = 'X') BEGIN
SET @NewRevision = @seriesLetter1 = 'The Revision XX is the last revision possable.'
END
please change it to
IF (@seriesLetter1 = 'X' AND @seriesLetter2 = 'X') BEGIN
SET @NewRevision = 'The Revision XX is the last revision possable.'
END
Will
|
|
|
|
|
I've been using MySQL for some months which has a build-in "md5()" function for md5 excryption, very convenient. Now I need to do something uses SQL server 2000 instead of MySQL, I found that there's no such function or similar feature in SQL server 2000... Hmm then how do you encrypt user passwords? Do you have to develop your own DLL to do the encryption(which would suck)?
|
|
|
|
|
Do it on the client side. For unmanaged code, look at the CryptoAPI documentation; for managed code, there's the System.Security.Cryptography.MD5 class.
You might be able to use OPENQUERY and an extended stored procedure, but then we're heavily in the realms of suckiness. 
|
|
|
|
|
Hi
I'm trying to save a mp3 files in a table in access file.
SO I'm using ADO. I can save and read data in access file if it is of the tipes (Long , String, BOOL .) But I don't know how to Read & Write BIG array.
I think that I should use VARIANT but I did not know how!
Thanks very much for any kind of help
![Rose | [Rose]](https://www.codeproject.com/script/Forums/Images/rose.gif)
|
|
|
|
|
I am now interested in SQL / database programming and I would like to find and learn about sql. Does anyone know some good compilers maybe free?? also a website or book about sql / database programming. I have 1 year in visual c++ 6 and about 1/2 a year in c++(you know in console...).
I would appreciate it if anyone replied. 
[It is possible to represent everything in this universe by using 0 and 1]
|
|
|
|
|
SQL is a generic term for a language used to comunicate with a database. That language can vary from various SQL databases. There is no "compiler" as there is in C++. Choose your database software and then learn the associated SQL syntax that goes with the database software.
Some Free database software:
----------------------------
MSDE (Microsoft Data Engine)
MySQL
PostgreSQL
Some Non-Free software:
-----------------------
Microsoft SQL Server
Oracle
DB/2
Once you've chosen your database software that you'll need to search online for some SQL primers. There are tons of sites which will cover the basics of SQL programming.
|
|
|
|
|
I have found SQL Team[^] a valuable resource, especially when you start to get into more complex SQL statements.
Hope this helps!
Jeremy Oldham
|
|
|
|
|
 THANKS ALOT!!!
[It is possible to represent everything in this universe by using 0 and 1]
|
|
|
|
|
How can I add PrimaryKey to my DataTable in dataset in Runtime!
I have >>
<xs:key name="SignKey1"><br />
<xs:selector xpath="." /><br />
<xs:field xpath="mstns:PrsId" /><br />
</xs:key>
in my Dataset?! but it gives error that I dont have PrimaryKey! Isnt it PrimaryKey?!?!
Always,
Hovik.
|
|
|
|
|
Just starting to play around with database access in dotNET. I've run through a number of tutorials, and reviewed a number of articles on this web site. All was well and good until I tried to do something on my own... Uh Oh...
I created a simple relational database with the following tables...
Table - PhoneNumber
PhoneNumber_ID
PhoneNumber
PhoneNumberTypeID
PersonID
Table - PhoneNumberType
PhoneNumberType_ID
PhoneNumberType
Table - Person
Person_ID
FirstName
LastName
I threw some data in the PhoneNumberType and Person tables, and then wanted to see if I could create a form to add entries to the PhoneNumber table.
I added two comboboxes to the form, and bound them to datasets filled with the contents of PhoneNumberType and Person tables. The valuemember of the comboboxes is the ***_ID column of the selected row in the combobox. That's working fine.
I added a textbox to the form for the phone number.
So, I have all the elements necessary to add a new row to the PhoneNumber table, but I'm not sure of the most efficient method of performing the insert. After poking around on the internet for a while, it looks like there might be two options avaliable to me (there are probably more)...
1) Create a DataAdapter for the PhoneNumber table, then create and fill a dataset with all the phone numbers in the PhoneNumber table. Add the new row and then update the database with the new dataset (need to figure out how to do that). This seems like overkill for this simple function.
2) Create an SQLCommand and execute it with the new row values. I vaguely remember seeing an article that discussed using ?'s in the SQL command, and then filling in the parameters afterwards, but I can't seem to find it again.
Is either of these the preferred approach, or is there another approach I might consider.
Thanks... whew that was a mouthful.
|
|
|
|
|
Here's my brute force method...
// Load the current phone numbers
sqlDataAdapterPhoneNumber.Fill(dataSetPhoneNumber1);
// create a new row
System.Data.DataRow row = dataSetPhoneNumber1.Tables["PhoneNumber"].NewRow();
// Fill it
row["PhoneNumberTypeID"] = comboBoxPhoneTypeID.SelectedValue;
row["EntityID"] = comboBoxName.SelectedValue;
row["PhoneNumber"] = textBoxPhoneNumber.Text;
// Add it to the dataset
dataSetPhoneNumber1.Tables["PhoneNumber"].Rows.Add(row);
// Update the database (might experiment with GetChanges)
sqlDataAdapterPhoneNumber.Update(dataSetPhoneNumber1);
|
|
|
|
|
Hi there:
In how many ways we can get Dataset position?!
Always,
Hovik.
|
|
|
|
|
I just made a little application...
It uses xp visual sytles & a sql 2000 database...
Usually on my normal apps(that don't use visual styles or sql), I can just make a quick installer, or just copy the whole file to the other computer and use the application...
Now, with sytles and sql it wont...
What are the special things I have to do to get the app to run on other computers???
/\ |_ E X E GG
|
|
|
|
|
How can I Update more than two columns. Everytime I try to update more than two I get a syntax error. Can somebody help me. This code gave me the syntax error.
private void datagrid_UpdateCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)
{
int ID = (int) datagrid.DataKeys[(int) e.Item.ItemIndex];
string name = ((TextBox)e.Item.Cells[1].Controls[0]).Text;
string type = ((TextBox)e.Item.Cells[2].Controls[0]).Text;
string test = ((TextBox)e.Item.Cells[3].Controls[0]).Text;
string sql =
"UPDATE EmployeeInfo SET Fir=\"" + name +
"\", Las=\"" + type + "\"" +
"\", Res=\"" +test+ "\""
"WHERE ID=" + ID;
ExecuteNonQuery(sql);
datagrid.EditItemIndex = -1;
ReadRecords();
}
The code does not give the syntax error but only updates the two columns.
private void datagrid_UpdateCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)
{
int ID = (int) datagrid.DataKeys[(int) e.Item.ItemIndex];
string name = ((TextBox)e.Item.Cells[1].Controls[0]).Text;
string type = ((TextBox)e.Item.Cells[2].Controls[0]).Text;
string sql =
"UPDATE EmployeeInfo SET Fir=\"" + name +
"\", Las=\"" + type + "\"" +
" WHERE ID=" + ID;
ExecuteNonQuery(sql);
datagrid.EditItemIndex = -1;
ReadRecords();
}
Can anyone give some pointers. Thanks
|
|
|
|
|
The only thing that jumps out at me is that you do not have a space between your final field and the WHERE clause in the first bit of code. What is the syntax error you get?
Notice the difference, but it may just be a cut/paste anomaly...
Syntax error
kornstyle wrote:
"WHERE ID=" + ID;
non syntax error
kornstyle wrote:
" WHERE ID=" + ID;
|
|
|
|
|
I'm getting a "missing operator" syntax error.
|
|
|
|
|
Put the space in still didn't work.
|
|
|
|
|
Doh! If I would have looked harder the first time I would have caught it. I was thinking you were getting a SQL syntax error.
kornstyle wrote:
string sql =
"UPDATE EmployeeInfo SET Fir=\"" + name +
"\", Las=\"" + type + "\"" +
"\", Res=\"" +test+ "\""
"WHERE ID=" + ID;
Add a + to the end of the line where you set Res...
string sql = <br />
"UPDATE EmployeeInfo SET Fir=\"" + name + <br />
"\", Las=\"" + type + "\"" +<br />
"\", Res=\"" +test+ "\"" +<br />
"WHERE ID=" + ID;
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
