Database

Blue_Boy2-Sep-09 2:38

Blue_Boy

2-Sep-09 2:38

Do you get error message?
Explain more your question.

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.

www.aktualiteti.com

David Mujica2-Sep-09 3:32

David Mujica

2-Sep-09 3:32

1) Are you using transactions ? Make sure you have a "Commit" after your update statement.
2) Do you realize that in your example there is no "where" clause ? Every row in the table would be affected.

I believe the root of your problem is that you can't have a function on the left side of the assignment operator in the update statement.

The following code seems to work for me ...

select first_name from fsuser where user_code = 'DMUJICA'

begin transaction
update fsuser set first_name = substring(first_name,1,1) + 'XXX' + 
	substring(first_name,5,len(first_name)-4) where user_code = 'DMUJICA'

select first_name from fsuser where user_code = 'DMUJICA'
--commit
rollback

The output is:
DAVID
DXXXD

Abhishek Sur2-Sep-09 9:43

Abhishek Sur

2-Sep-09 9:43

This is weird... Confused | :confused:

You are setting a constant term with another constant..

If you write

Set substring(orderno, 6,2) = 'tt'
It means if order no is 12345678 then you write

78 = tt... OMG | :OMG:

You can only apply use functions on the left hand side when you are using a comparison...

In your case the query should be

update tale1 set orderno = substring(orderno,1,6) + tt + substring(orderno,8, len(orderno) - 8)

Remember, you are missing with where clause. So it will update every order no...

Thumbs Up | :thumbsup:

Abhishek Sur

My Latest Articles
Create CLR objects in SQL Server 2005
C# Uncommon Keywords
Read/Write Excel using OleDB
Don't forget to click "Good Answer" if you like to.

Niladri_Biswas2-Sep-09 17:18

Niladri_Biswas

2-Sep-09 17:18

Hi, I really cannot understand your requirement.

What you are trying to achieve, is not at all clear in the statement presented here.

I fully agree with Abhishek.

However, if you invoke the statement "update tale1 set substring(orderno,6,2) = 'tt'" you will get the following error

"Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ','."

Now, I have a table say tblOrder with the following records.

OrderNo
------------
123456789
234567891
345678912
45678123
67
78
89

My goal is to update those orderno's that will satisfy the condition substring(orderno,6,2).

Means, if the order no is 987623456 , then substring('987623456',6,2) will yield me 34. So if any OrderNo 34 is present, that will be updated.

The query is

update tblOrder set  OrderNo =  'tt'

where OrderNo in( select substring(OrderNo,6,2) from tblOrder)

The output is

OrderNo
------------
123456789
234567891
345678912
45678123
tt
tt
tt

Hope this helps
Smile | :)

Niladri Biswas

e-blood bank

noo.dyab2-Sep-09 1:07

noo.dyab

2-Sep-09 1:07

hello there
i want to make a project about central blood bank which can connect to any other small blood bank in other hospitals and take information about available blood units thers and then any bank could connect to central blood bank and make request of the blood it need then the central bank will search in its database if it availabe in it it will reply if not it will connect to others hospitals and ask them about the request then when it find what ht need he will reply immediatle to the bank who request the blood and inform them of what he find. else we should care about doners and donation processes , test , etc.

i want to implement the project with asp.net and mysql for database
help me if any one know how to make it and please i want to get the database or ER-Diagram as soon as possible

Ashfield2-Sep-09 1:28

Ashfield

2-Sep-09 1:28

Sounds like you don't have a clue. If you are being paid to do this, give the money back now. Alternatively, try a site like rentacoder.com and pay for it.

Seriously, there are so many things wrong with what you are asking for you have no chance of really doing it. For example, just think about the security issues of connecting to a hospital database.

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Re: e-blood bank [modified]

Garth J Lancaster2-Sep-09 1:41

Garth J Lancaster

2-Sep-09 1:41

chuckle - pretty well what I told him over in the MySQL forum !

'g'

annathor2-Sep-09 1:42

annathor

2-Sep-09 1:42

I'am glad I probably never gonna go to a hospital that uses your "system".
but anyway, here is a handy 10 points step by step guide Smile | :)

Step 0: Get a clue of what you are trying to do.
Step 1: Learn to code.
Step 2: Write a good system spesification, including databse design, software design, user interface and so on.
Step 5: Develope an prototype (Low-fidellity).
step 3: Choose an appropiate development methology for the prodject (eks. SCRUM, Extreme Programming, Rational Rose, Spiral model).
step 4: Choose an appropiate system structure (eks. n-tier, business Layer, MVC).
Step 5: Develope an prototype (High-fidellity).
Step 6: Implement the database design.
Step 7: write alot of code.
Step 8: Do alot of testing.
Step 9: Do alot of usability testing.
step 10. profit.

modified on Wednesday, September 2, 2009 7:59 AM

samawat8-Dec-09 4:49

samawat

8-Dec-09 4:49

i want a to make a blood bank data base can one help me please

samawat8-Dec-09 4:51

samawat

8-Dec-09 4:51

please any one have any idea for this send to me here or in my email to comunicat with him/here

AlexeiXX33-Sep-09 9:47

AlexeiXX3

3-Sep-09 9:47

Is this a school project or is this really gonna be a real worl app??
Are you a developer or you just have the idea??
if not a school project, are you getting paid for this??

Alexei Rodriguez

Exporting a database

MarkB7771-Sep-09 23:09

MarkB777

1-Sep-09 23:09

Hi,

I would like to export a database I use at work to test at home.

I'm wondering if there is a way to export it so the whole database is stored as a file, and you can connect to the file as if it was the SQL server (I cant access the SQL server from home).

Is something like this possible?

I am running SQL Server Management Studio 2005.

Cheers,

Mark Brock
"We're definitely not going to make a G or a PG version of this. It's not PillowfightCraft." -- Chris Metzen

SeMartens2-Sep-09 0:14

SeMartens

2-Sep-09 0:14

Hi,

you could use the SQL Server 2005 Express Edition at home. It is free to use... Then just create a complete backup at work and restore it at home.

Regards
Sebastian

It's not a bug, it's a feature!

Check out my CodeProject article Permission-by-aspect.

Me in Softwareland.

Jerry Hammond2-Sep-09 2:34

Jerry Hammond

2-Sep-09 2:34

Yes. Use the copy database wizard.

“If you think you are worth what you know, you are very wrong. Your knowledge today does not have much value beyond a couple of years. Your value is what you can learn and how easily you can adapt to the changes this profession brings so often.”-– Jose M. Aguilar

Abhishek Sur2-Sep-09 9:53

Abhishek Sur

2-Sep-09 9:53

Just run backup...

Run this script in your office computer :

BACKUP DATABASE [db] TO  DISK = N'c:\Backup\db.bak' WITH NOFORMAT, NOINIT,  NAME = N'db-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

It will produce an output db.bak in your local C:\backup folder.
[Please replace db with your database name on the script.]

Now in home run this :

RESTORE DATABASE [db] FROM  DISK = N'C:\Backup\DB.bak' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10
GO

Here db is the name of the database it will create when it restores, place the db.bak file from your office to your home computer in C:\backup or whatever you like to and finally run it..

Cool | :cool:

Abhishek Sur

My Latest Articles
Create CLR objects in SQL Server 2005
C# Uncommon Keywords
Read/Write Excel using OleDB
Don't forget to click "Good Answer" if you like to.

εїзεїзεїз2-Sep-09 11:43

εїзεїзεїз

2-Sep-09 11:43

if you have good internet connection then you can connect remotely as well as by using sql express and their are also so many other Sql Management tools are availbale same like sql server management studio you can use them also and there are some hand tools for sql server datase copying /backup and emailing automatically

http://www.sqlbackupandftp.com/[^]

http://www.download3k.com/System-Utilities/Backup-Restore/Download-SQLBackupAndFTP.html[^]

while other regular options are their as well as normal backups and copy database , mirroring etc

Best Of Regards,
SOFTDEV
Sad like books with torn pages, sad like unfinished stories ...

Abhishek Sur2-Sep-09 11:59

Abhishek Sur

2-Sep-09 11:59

Means he needs to expose the SQLServer browser from the firewall of the server... This would not be a good option I think.
Big Grin | :-D

Abhishek Sur

My Latest Articles
Create CLR objects in SQL Server 2005
C# Uncommon Keywords
Read/Write Excel using OleDB
Don't forget to click "Good Answer" if you like to.

What is SQL Query:Master child table [modified]

εїзεїзεїз2-Sep-09 13:42

εїзεїзεїз

2-Sep-09 13:42

if possible then he can add Execption depends upon cicrumstances and is one of the option if possible otherwise there copying or backup is good option and he can even export only some tables and if he can export tables into XML and then can import then it will be even another way to do it , depends upon the nature as far as my understanding is he is not using at clients he is using for his test pc at home

Best Of Regards,
SOFTDEV
Sad like books with torn pages, sad like unfinished stories ...

xodeblack1-Sep-09 20:24

xodeblack

1-Sep-09 20:24

I have following two tables

1st table is Fee:

PK ----   payable 
1  ----   1500 
2  ----   1200
3  ----   1900

2nd table is Fee_Detail:

pk ---- paid ---- fk_fee
1  ---- 700  ---- 1
2  ---- 400  ---- 1
3  ---- 400  ---- 1
4  ---- 800  ---- 2
5  ---- 300  ---- 2

Result should be

payable ---- paid
1500    ---- 1500
1200    ---- 1100
1900    ---- 0

what is sql query

modified on Wednesday, September 2, 2009 2:39 AM

Mycroft Holmes1-Sep-09 21:14

Mycroft Holmes

1-Sep-09 21:14

You need and inner join a group by and a sum

 Select FeeID, sum(payable), sum(paid)
from Fee
  iner join Fee_detail FD on FD.fk_fee = fee.FeeID
group by 
  FeeID

xodeblack1-Sep-09 22:39

xodeblack

1-Sep-09 22:39

I want to fetch all records of left hand side
and only sum of 'paid amount' matching records of child table(right hand side) according to fk_fee(foreign key column)..

for example

Fee table:
Pk ---- payable
1 ---- 1500
2 --- 700

Fee Detail:
Pk ---- Paid ---- fk_fee
1 ---- 1100 ----1
2 ---- 200 ----1
3 ---- 100 ----1

Result should be

payable ---- paid
1500 ---- 1400
700 ---- 0

Mycroft Holmes1-Sep-09 22:50

Mycroft Holmes

1-Sep-09 22:50

Then you need to use a left join and deal with null values

Select FeeID, sum(payable), sum(ISNULL(paid,0))
from Fee
  left join Fee_detail FD on FD.fk_fee = fee.FeeID
group by 
  FeeID

search here for articles on joins, there is a good one around, sorry I don't have a link