Database

Re: Querying SQL2005 in C# on Current DateTime

21-Mar-07 5:41

If you use something like GetDate or DateTime.Now, you are actually working with a date/time instance. What you need to do is convert the date into a day format and compare that to @NoticeDate.

Deja View - the feeling that you've seen this post before.

Laxman Auti21-Mar-07 5:50

Laxman Auti

21-Mar-07 5:50

briwright wrote:
I have also tried WHERE @NoticeDate = 'GetDate()'

Try by saving GETDATE() into local variable and use it for comparision.

DECLARE @curr_datetime datetime<br />
SET @curr_datetime = GETDATE()<br />
<br />
SELECT [NoticeDate], [NoticeTitle], [NoticeDetails], [NoticeID]<br />
FROM tbl_notice<br />
WHERE @NoticeDate = @curr_datetime

Hope this will solve your problem.

Knock out 't' from can't,
You can if you think you can
Cool | :cool:

Is there and difference between .mdf file in MSSQL & SQLEXPRESS?

sir_moreno21-Mar-07 1:38

sir_moreno

21-Mar-07 1:38

Hi,
I'm using a .mdf (&.ldf) sql DB file in my ASP.NET application (it's in \APP_DATA), and I can see in the connection string that it is connected via SQLEXPRESS.
I know I can take the same .mdf file and attach it to MSSQL server and work with the same file under a MSSQL connection.
My question general is: After migrating a DB file from SQLEXPRESS to MSSQL will my DB work as a regular MSSQL DB or it will still have the SQLEXPRESS limitaions?
Thanks
Rafael.

Moreno

Re: Is there and difference between .mdf file in MSSQL & SQLEXPRESS?

Colin Angus Mackay21-Mar-07 4:57

Re: Is there and difference between .mdf file in MSSQL & SQLEXPRESS?

21-Mar-07 4:57

The limitations will most likely be in the software, not the file.

Upcoming events:
* Edinburgh: Web Security Conference Day for Windows Developers (12th April)
* Glasgow: Introduction to AJAX (2nd May), SQL Server, Mock Objects

My website

sir_moreno21-Mar-07 5:18

sir_moreno

21-Mar-07 5:18

So if I use the same mdf file via MSSQL I will Not get any SQLEXPRESS Limitations?

Moreno

Re: Is there and difference between .mdf file in MSSQL & SQLEXPRESS?

Pete O'Hanlon21-Mar-07 5:23

Re: Is there and difference between .mdf file in MSSQL & SQLEXPRESS?

21-Mar-07 5:23

Correct.

Deja View - the feeling that you've seen this post before.

sir_moreno21-Mar-07 6:09

sir_moreno

21-Mar-07 6:09

Thanks

Moreno

How to avoid primary key violation error

JeyKey21-Mar-07 0:15

JeyKey

21-Mar-07 0:15

I Have a MS SQL Store proc, which takes max value of a field from the table for a day ( That field has got date part )and adds 1 if it exists or creates new if it does not exists.

Logic is like this

select a = max ( ID) where ID like (20070321%) /*Denotes March 21 2007*/
if (a is null ) set 200703210001
else a = a+1

insert into table (ID,....) values (a,....)

I am getting primary key violation error occassionaly.
I would like to know, How the SQL Server handles concurrent request for executing storedprocedures.

For example if it is sequential my logic should work fine
else
There can be duplicate values for ID fields.

Please help.

I knew it would end badly when I first met Chris in a Canberra alleyway and he said 'try some - it won't hurt you'..... - Christian Graus on Code Project outages

Pete O'Hanlon21-Mar-07 0:39

21-Mar-07 0:39

If this is in SQL Server 2005, you can wrap it inside a TRY/CATCH block.

Deja View - the feeling that you've seen this post before.

JeyKey21-Mar-07 0:45

JeyKey

21-Mar-07 0:45

Hi,
Thanks for the reply.
It is SQL 2000 / 7.0
Any other optimal solution

A moment of silence please. A programmer's best friend has passed beyond that great exception in the sky.... - Mark Conger on "The coffee machine has died"

Pete O'Hanlon21-Mar-07 2:43

21-Mar-07 2:43

Well, you could always try something like this:

insert into table1(...)
select @id, @col2, @col3
from table1 where not exists (select id from table1 where id = @id)

Deja View - the feeling that you've seen this post before.

scottgp21-Mar-07 3:12

scottgp

21-Mar-07 3:12

Hi,
Personally, in this case I'd use an Identity column for the primary key, and put the date information in another column defaulted to get the date (DEFAULT GETDATE()). I'm not sure what having the date as part of the primary key is buying you.
Scot

Insert into SQL Problem

Memo Soso20-Mar-07 23:13

Memo Soso

20-Mar-07 23:13

How can i insert the char " ' " into my sql Statement so that the SQL server 2000 take it by using VB.net 2005

N a v a n e e t h20-Mar-07 23:15

N a v a n e e t h

20-Mar-07 23:15

Replace ' with ''

str.replace("'","''");

printf("Navaneeth!!")

www.w3hearts.com

Colin Angus Mackay20-Mar-07 23:25

20-Mar-07 23:25

WARNING: The original question could be a symptom of a security hole.

Upcoming events:
* Edinburgh: Web Security Conference Day for Windows Developers (12th April)
* Glasgow: Introduction to AJAX (2nd May), SQL Server, Mock Objects

My website

Memo Soso20-Mar-07 23:30

Memo Soso

20-Mar-07 23:30

Thanx it solved the problem

Colin Angus Mackay20-Mar-07 23:37

20-Mar-07 23:37

Be very careful with his solution at it can hide security problems. It you want to reduce the risk of your database being attacked then I suggest you don't use that solution.

Upcoming events:
* Edinburgh: Web Security Conference Day for Windows Developers (12th April)
* Glasgow: Introduction to AJAX (2nd May), SQL Server, Mock Objects

My website

N a v a n e e t h21-Mar-07 0:42

N a v a n e e t h

21-Mar-07 0:42

Colin Angus Mackay wrote:
Be very careful with his solution at it can hide security problems.

Could you please explain this in detail ? I am not getting the problem about converting ' to ''

printf("Navaneeth!!")

www.w3hearts.com

Pete O'Hanlon21-Mar-07 2:46

21-Mar-07 2:46

Colin wrote this article http://www.codeproject.com/cs/database/SqlInjectionAttacks.asp[^] which details these issues in great depth.

Deja View - the feeling that you've seen this post before.

Colin Angus Mackay21-Mar-07 4:54

21-Mar-07 4:54

Pete O`Hanlon wrote:
Colin wrote this article

Thanks for the plug. Big Grin | :-D

Upcoming events:
* Edinburgh: Web Security Conference Day for Windows Developers (12th April)
* Glasgow: Introduction to AJAX (2nd May), SQL Server, Mock Objects

My website

Colin Angus Mackay20-Mar-07 23:27

Re: SELECT TOP x field FROM

20-Mar-07 23:27

Use parameterised queries.

For example:

INSERT INTO MyTable(Column1, Column2) VALUES(@value1, @value2);

cmd.Parameters.Add("@value1", someValue)
cmd.Parameters.Add("@value2", someOtherValue)
cmd.ExecuteNonQuery()

Upcoming events:
* Edinburgh: Web Security Conference Day for Windows Developers (12th April)
* Glasgow: Introduction to AJAX (2nd May), SQL Server, Mock Objects

My website

SELECT TOP x field FROM

joon _vh.20-Mar-07 22:56

joon _vh.

20-Mar-07 22:56

is the Select Top functionality or an alternative available in sql server 2000?

if I have this data...

100
200
330
445
665
887
999

...I know I can achieve it by ordering it and then using the if value > ALL ... but what if it is a lot more complicated than this?

thanks

Visual Studio can't evaluate this, can you?

public object moo<br />
        {<br />
__get { return moo; }<br />
__set { moo = value; }<br />
}

Colin Angus Mackay20-Mar-07 23:23

Re: SELECT TOP x field FROM

20-Mar-07 23:23

joon_ wrote:
is the Select Top functionality or an alternative available in sql server 2000?

Yes.

Upcoming events:
* Edinburgh: Web Security Conference Day for Windows Developers (12th April)
* Glasgow: Introduction to AJAX (2nd May), SQL Server, Mock Objects

My website

joon _vh.20-Mar-07 23:25

joon _vh.

20-Mar-07 23:25

Which is it?

SELECT TOP didn't exist before 2005, did it..?

Visual Studio can't evaluate this, can you?

public object moo<br />
        {<br />
__get { return moo; }<br />
__set { moo = value; }<br />
}

Re: SELECT TOP x field FROM

Colin Angus Mackay20-Mar-07 23:28