Database Discussion Boards

dptalt16-Oct-09 3:49

16-Oct-09 3:49

I want to update the top row in a table. I'm trying to get this to work:

update top (1) mytable set myfield="test"

This does not seem to compile. We have Microsoft SQL Server 2000. How can I accomplish this?

Re: Update top row

Eddy Vluggen16-Oct-09 4:50

Eddy Vluggen

16-Oct-09 4:50

AFAIK, the update statement does not support the TOP condition. Try something like this;

UPDATE dbo.myTable
   SET myField = 'test'
 WHERE myPrimaryKeyField = (SELECT TOP 1 myPrimaryKeyField FROM dbo.myTable)

Hope this helps Smile | :)

I are Troll Smile | :)

Re: Update top row

i.j.russell16-Oct-09 10:41

i.j.russell

16-Oct-09 10:41

You should have an ORDER BY clause on the subquery to determine the correct TOP 1.

Re: Update top row

Not Active16-Oct-09 13:08

Not Active

16-Oct-09 13:08

A more efficient statement would be this.

update Table
set field = new_value
where PK = (select min(PK) from Table)

only two letters away from being an asset

Re: Update top row

Geoff Williams16-Oct-09 4:56

Geoff Williams

16-Oct-09 4:56

The predicate "TOP 1" applies only to the SELECT statement, not the UPDATE statement.

In order to update just the first row in your table, your UPDATE statement will need to be

UPDATE mytable
SET myfield = "test"
WHERE ((SELECT TOP 1 myfield FROM mytable) = myfield);

(I've split the statement over several lines to improve readability).

MSSQL Question

Wouter Coinless16-Oct-09 3:13

Wouter Coinless

16-Oct-09 3:13

Is there a way to do this:

@FirstId uniqueidentifier = NULL;
@SecondId uniqueidentifier = NULL;

SELECT ..
FROM ..
WHERE
   IF(@FirstId IS NOT NULL)
      BEGIN
          FirstId = @FirstId
      END
   IF(@SecondId IS NOT NULL)
      BEGIN
          AND FirstId = @FirstId
      END

Or a smarter way? Big Grin | :-D

Thanks in advance!

Re: MSSQL Question

Luc Pattyn16-Oct-09 3:58

Luc Pattyn

16-Oct-09 3:58

Hi,

this is what I would try:

... WHERE (@FirstId=null OR FirstId=@FirstId) AND (@SecondId =null OR SecondId =@SecondId )

Luc Pattyn

I only read code that is properly indented, and rendered in a non-proportional font; hint: use PRE tags in forum messages

Local announcement (Antwerp region): Lange Wapper? Neen!

Re: MSSQL Question

Wouter Coinless16-Oct-09 4:37

Wouter Coinless

16-Oct-09 4:37

I dont want to include the Ids if they are null.

Re: MSSQL Question

J4amieC16-Oct-09 4:54

J4amieC

16-Oct-09 4:54

The suggestion given does exactly what you want it to do.

Break Fullname Into First And Last Name

AndyInUK15-Oct-09 23:29

AndyInUK

15-Oct-09 23:29

Hi ,

How can i break Fullname Into First And Last Name.

For instance there is a column with name "Mr James Smith" and i want to break this into 3 columns with Title, FirstName and LastName.

Thank You

Re: Break Fullname Into First And Last Name

Andy_L_J15-Oct-09 23:34

Andy_L_J

15-Oct-09 23:34

http://www.devx.com/tips/Tip/20009[^]

I don't speak Idiot - please talk slowly and clearly

'This space for rent'

Driven to the arms of Heineken by the wife

Re: Break Fullname Into First And Last Name

Eddy Vluggen16-Oct-09 0:01

Eddy Vluggen

16-Oct-09 0:01

Be carefull with those; some people have no title, but a compound First-name, or a surname that's made up out of multiple names. I'd check the first argument from the string against a known list of titles, just to see whether that part was included or whether it's a first name.

I are Troll Smile | :)

Re: Break Fullname Into First And Last Name

dan!sh 16-Oct-09 0:52

dan!sh

16-Oct-09 0:52

Given the various kinds of name people have in various parts of world, you can not be sure that your code will work fine. It will irritate one person with different kind of name.

Still, you can have something predefined like:
1. First two words will go in as first name.
2. Last word will be the Last name.
3. Rest can be treated as middle name.

Beware, Sean Claude Van Dam will be angry by this approach.

It's not necessary to be so stupid, either, but people manage it. - Christian Graus, 2009 AD

Re: Break Fullname Into First And Last Name

J4amieC16-Oct-09 4:54

J4amieC

16-Oct-09 4:54

d@nish wrote:
Sean Claude Van Dam

Jean

Re: Break Fullname Into First And Last Name

Richard MacCutchan16-Oct-09 5:20

Richard MacCutchan

16-Oct-09 5:20

J4amieC wrote:
Jean

I think Sean is the Irish one Laugh | :laugh:

Re: Break Fullname Into First And Last Name

dan!sh 16-Oct-09 5:51

dan!sh

16-Oct-09 5:51

I wasn't sure about the spelling. Hence I checked in google and got the results. Although I did not bothered to look down the page.

It's not necessary to be so stupid, either, but people manage it. - Christian Graus, 2009 AD

"Cannot open database" if logging into SQL-server too fast [modified]

Jörgen Sigvardsson15-Oct-09 21:31

Jörgen Sigvardsson

15-Oct-09 21:31

I have a service that needs to log into an SQL 2008 Express Server. The account of the service is the local system account. When the service starts, it checks if the SQL server is running. If not, it starts it, and waits for the service status to become "Running". When that has happened, the service logs in using a SqlConnection object.

If the login is done too fast, the SQL server reports "Cannot open database XXX requested by login. The login failed.". ~~This indicates that the security subsystem of the database server is not in a running/complete state.~~ No, I can see now that the database has not yet become online. My current approach is to hammer the database 10 times with 1 second interval to wait for the connection to succeed. This is idiotic, and just begs for sh*t to happen.

Does anybody know a better way? This is driving me insane... Unsure | :~

--
Kein Mitleid Für Die Mehrheit

modified on Friday, October 16, 2009 5:15 AM

Re: "Cannot open database" if logging into SQL-server too fast

Soundman32.216-Oct-09 0:59

Soundman32.2

16-Oct-09 0:59

Can you not make your service depend on the SQL 2008 Express Server?

Check out the Dependencies tab on the control panel/services/properties page.

[Solution] Re: "Cannot open database" if logging into SQL-server too fast

Jörgen Sigvardsson16-Oct-09 1:05

Jörgen Sigvardsson

16-Oct-09 1:05

I could if I wanted to, but it doesn't help. The problem is that once the SQL server is reporting to be started to the SCM, it still hasn't made all databases online yet (only the master database is available). That work progresses after the SCM has been signalled that the service is running.

I found the solution to be easier than I thought. All I have to do is to

SELECT COUNT(*) FROM sys.databases WHERE name=@database_name AND state_desc='online'

and see if it returns 0 or 1. Once the database manager reports the database as online, it is possible to use the database.

--
Kein Mitleid Für Die Mehrheit

Product Page From Database

johntwebb15-Oct-09 15:25

johntwebb

15-Oct-09 15:25

Okay, I'm new here and to programming in general. I don't want to waste a lot of your time as I know the question I have has probably been asked before. I'm just not sure what to search for.

What I'm trying to do is create a site that displays a list of products from a database (which I know how to do) and when you click on the item it displays the product details (which is where I'm stuck). I've built a static html site at here so that you can get an idea of what I'm going for. As you can see there's quite an extensive description of the product which I haven't seen discussed how to create pulling from a database.

Any help would be greatly appreciated. Like I said, I know this has probably been discussed, so all I'm asking for is a point in the right direction. I'm not sure what I should be searching for. Thanks for your help in advance.

Also, I guess I should let you know that I know html and I know the basics of asp.net (c#). And I'm using Visual Studios 2008.

Again Thank You.

Re: Product Page From Database

Mycroft Holmes15-Oct-09 17:43

Mycroft Holmes

15-Oct-09 17:43

Well you got the wrong forum on your first attempt, the ASP forum will give you better results.

Here is a little Goole mojo[^] to get you started, the first result may be useful. There is a basic ASP tutorial around the demonstrates loading a grid and then using a details block to manage the CRUD on an item, that sounds like what you are after (sorry no link).

Re: Product Page From Database

johntwebb16-Oct-09 0:51

johntwebb

16-Oct-09 0:51

Sorry, I wasn't sure where to post since half of what I'm looking for is setting up the database for the product page and the other half is displaying it on the page. I looked at that link and I already know how to set that up. It's when links are clicked and I need a product page with 4-8 descriptions of the product that has me stumped. I guess I'll ask this over at the asp.net section. Thanks for your help.

DateTime from concatenated string

Jay Royall15-Oct-09 4:05

Jay Royall

15-Oct-09 4:05

Hi,

I am trying to insert into a DateTime field by concatenating a string and a nVarChar field like so:

CONVERT(datetime, '1/1/' + tblMembershipRecords.[YearJoined], 103)

But I get a message stating;
'Conversion failed when converting datetime from character string.'

Any ideas what is going wrong?

Re: DateTime from concatenated string

Eddy Vluggen15-Oct-09 4:35

Eddy Vluggen

15-Oct-09 4:35

Liqz wrote:
Any ideas what is going wrong?

The computer tries to "interpret" your string as a date, and it doesn't understand it. It seems that it expects the date to be in a different format.

Try something like this;

SELECT DATEADD(y, tblMembershipRecords.[YearJoined], getdate())

That will add a single year to the DateTime, without converting it to a string.

I are Troll Smile | :)

Re: DateTime from concatenated string

Jay Royall15-Oct-09 4:41

Jay Royall

15-Oct-09 4:41

Got it, thanks for the tip, all working now Smile | :)

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.