Database

riced28-Jun-09 22:44

riced

28-Jun-09 22:44

Use query designer to construct the query then switch to SQL view.
You can then copy and paste resulting SQL.
Note that Access tends to put in redundant brackets but they don't cause any problem.

Regards
David R
---------------------------------------------------------------
"Every program eventually becomes rococo, and then rubble." - Alan Perlis

A k ch28-Jun-09 23:04

A k ch

28-Jun-09 23:04

Thanks.

Thanks & Regards,
Anil Chelasani

Niladri_Biswas28-Jun-09 23:07

28-Jun-09 23:07

Try this

SELECT * FROM  
                           ( 

                                 ( STUDENTS AS A  LEFT JOIN SUBDETAILS AS B ON A.BID=B.BID)
                                   
                                  LEFT JOIN SUBJECTS C ON B.SUBID=C.SUBID
                            )

                           LEFT JOIN DETAILS D ON C.SUBID=D.SUBID 

                   WHERE D.HNO=1234 

                   AND B.BID=2 

                   AND B.SEMID=1

                   AND A.HNO=D.HNO

N.B.~ If you want to perform more than one left join in MS Access, you should use parenthesis in the From clause.

Hope this helps
Smile | :)

Niladri Biswas

A k ch28-Jun-09 23:33

A k ch

28-Jun-09 23:33

Thanks this is working fine but when i am trying to check second condition in the ON that is like
LEFT JOIN DETAILS D ON C.SUBID=D.SUBID AND C.HNO = A.HNO

it is again giving the error.

can you please tell me how to check for second condition in the "ON".

Thanks & Regards,
Anil Chelasani

riced29-Jun-09 0:43

riced

29-Jun-09 0:43

Here's the SQL generated by the query designer.
Two versions: first with fixed values, second using parameters.
Note the redundant parentheses (esp in WHERE) - and the layout is pretty poor.

SELECT a.*
FROM ((Student AS a LEFT JOIN Subdetails AS b ON a.bid = b.bid) 
LEFT JOIN Subjects AS c ON b.subid = c.subid) 
LEFT JOIN details AS d ON c.subid = d.subid
WHERE (((b.bid)=2) AND ((b.semid)=1) AND ((d.hno)=1234) AND ((d.hno)=[a].[hno]));


PARAMETERS inBid Long, inSemid Long, inHno Long;
SELECT a.*
FROM ((Student AS a LEFT JOIN Subdetails AS b ON a.bid = b.bid) 
LEFT JOIN Subjects AS c ON b.subid = c.subid) 
LEFT JOIN details AS d ON c.subid = d.subid
WHERE (((b.bid)=[inBid]) AND ((b.semid)=[inSemid]) AND ((d.hno)=[inHno]) AND ((d.hno)=[a].[hno]));

Looking at your original post I suspect this is a problems
left join details d on c.subid=d.subid and a.hno=d.hno
The tables on both sides of the and must be the same i.e. c=d or a=d - cannot have c=d and a=d in same join clause.

Regards
David R
---------------------------------------------------------------
"Every program eventually becomes rococo, and then rubble." - Alan Perlis

FLOAT vs NUMERIC for financial transaction

A k ch29-Jun-09 1:00

A k ch

29-Jun-09 1:00

it worked fine. Thanks for ur time and consideration.

Thanks & Regards,
Anil Chelasani

devvvy28-Jun-09 15:23

28-Jun-09 15:23

hello

Do you use FLOAT or NUMERIC for financial transaction? If NUMERIC, what precision? For M$SQL, looks like float offers more digits.

Thanks

http://doc.ddart.net/mssql/sql70/da-db_1.htm[^]

dev

Niladri_Biswas28-Jun-09 16:37

28-Jun-09 16:37

Float
------
- Approximate data-type

- Not all values in the data type range can be precisely represented.

- Float is better for percentage calculations

Numeric/Decimal datatypes
------------------
- Fixed precision and scalable.

- The values ranges from - 10^38 +1 through 10^38 - 1 when maximum
precision is used.

- Appropriate in many places where money/currency is involved

Money Data Type
---------------

- Money is compatible for currency with dollar and cent format.

- The datatype is accurate to 10 thousand times of the monetary unit.

- If the field is capable enough to store large aggregates rather than just the largest value, it is recommended to go with Money data type.

- Also with money, you have the choice to use thousand of separators.

Coming to your question directly, Do you use FLOAT or NUMERIC for financial transaction?

If it is mathematical calculations of currency or any aggregation of the same(e.g. Add,Multiply etc. which will be in fraction), or any salary field is involved, go with money datatype as opposed to the other two datatypes.

Hope this helps
Smile | :)

Niladri Biswas

devvvy28-Jun-09 16:41

28-Jun-09 16:41

Thanks but float has more digit, see reference in original post.

Also I have seen in financial apps where float is used, also NUMERIC(19,5)

What's your opinion?

dev

Niladri_Biswas28-Jun-09 16:44

28-Jun-09 16:44

Money Data Type
---------------

- Money is compatible for currency with dollar and cent format.

- The datatype is accurate to 10 thousand times of the monetary unit.

- If the field is capable enough to store large aggregates rather than just the largest value, it is recommended to go with Money data type.

- Also with money, you have the choice to use thousand of separators.

Coming to your question directly, Do you use FLOAT or NUMERIC for financial transaction?

If it is mathematical calculations of currency or any aggregation of the same(e.g. Add,Multiply etc. which will be in fraction), or any salary field is involved, go with money datatype as opposed to the other two datatypes.
Smile | :)

Niladri Biswas

devvvy28-Jun-09 16:57

28-Jun-09 16:57

yes, in some financial apps I used before (for reputable banks), numeric and float

now this app i'm doing is compat with all MySQL, M$SQL and oracle, so don't like "money".

I'm just thinking, say if I use NUMERIC(19,5), mean 5 digits after the decimal. Over say 10k transaction in say one account, USD 0.000001 x 10k = USD 0.01 = one cent USD

It's still appreciable in my opinion and I'd be more comfortable with say NUMERIC(19,7) ... but really I have seen NUMERIC(19,5) in real app.

What's your take I'm asking for an opinion.

dev

Niladri_Biswas28-Jun-09 17:33

28-Jun-09 17:33

SQL Server considers each specific combination of precision and scale

as a different data type in case of Numeric data types.

That implies that for FLOAT(8) 99.99 and 99.999999 are same data types

though SQL SERVER interpretes NUMERIC(4,2) and NUMERIC(6,6) as different

data types (e.g. 99.99 and 99.999999 are different types )

In lieu of the above fact I would like to go with FLOAT
Smile | :)

Niladri Biswas

Niladri_Biswas28-Jun-09 22:30

28-Jun-09 22:30

Having said that in my earlier reply that I would like to go with float,

I have read this article where Mr. Alexander is stating that in certain cases decimal/numeric performs better than float.

Have a look

http://sqlblog.com/blogs/alexander_kuznetsov/archive/2008/12/20/for-better-precision-cast-decimals-before-calculations.aspx[^]

Smile | :)

Niladri Biswas

devvvy30-Jun-09 13:49

SQL Server Express and Visual C#

30-Jun-09 13:49

Thanks very much Niladri. Sorry about the late reply.

dev

kikeman28-Jun-09 7:56

kikeman

28-Jun-09 7:56

Hi,

I am currently using ACCESS 2003 but I am planning to develop a GUI using .NET (Visual C#). I have been told from other forums here in msdn that the best option is to use SQL Server instead of ACCESS 2003 because I am planning to use this database from many computers on the asme LAN at the same time. The DB is located on a network drive.

I saw other forums that someone say that SQL Server Express is limited to 10 users only and 4G bytes of data. Is this true?

What are the limitations? I am planning to have around 30 persons using this DB, is this possible? The data is not big, it is around 100M bytes ...

Thanks for your time

Henry Minute28-Jun-09 8:13

Henry Minute

28-Jun-09 8:13

kikeman wrote:
I saw other forums that someone say that SQL Server Express is limited to 10 users only and 4G bytes of data. Is this true?

The figures that you have given are roughly correct, in fact I think that you may be high with the users figure. (I thought that it was nearer to 4). It may also be true (it is for other M$ Express versions) that you cannot use it in production.

However, the Express version is still valid to develop against, and in most cases will port to one of the production versions with no problems.

You may find this link[^] useful

Henry Minute

Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”

kikeman29-Jun-09 6:26

kikeman

29-Jun-09 6:26

Hi, I am confused now, I am not allow to use any server, I only have the option of using a network drive, currently the ACCESS DB is located there and everyone goes there to make their queries. I just want to create a better GUI using VS_C#.

My question would be now.
Is it necesary to have a server in order to use SQL Server Express?

Could it work only as a collection of data on a network-drive, so that, the DB operations would be performed on the C# application? Transactions?

Henry Minute29-Jun-09 6:43

Henry Minute

29-Jun-09 6:43

I develop against SqlExpress 2005 and 2008, both of which run on my dev box. My data however is in all sorts of places, including network drives.

So in my scenario, each computer that will access the data needs the appropriate version of SqlExpress running on it and what you then have to do is ensure that the connectionstrings used in your applications are written in such a way as to guarantee to locate the correct database, regardless of which machine is being used.

kikeman30-Jun-09 7:40

kikeman

30-Jun-09 7:40

So what you are saying is that SQL Server Express does not need a centralized computer (server) to run.

Can I develop a distributed database?
So that, actually the DB is all over on the networked computers?

Henry Minute30-Jun-09 9:24

Henry Minute

30-Jun-09 9:24

kikeman wrote:
Can I develop a distributed database?
So that, actually the DB is all over on the networked computers?

I am sorry to say that the answer to that is beyond my knowledge. I think probably not, but cannot be sure.

If I were you I would post that as a new thread, something like "Can I do a Distributed Database with SQLExpress", or something like that.

Sorry not to be able to help. Sniff | :^)

Niladri_Biswas28-Jun-09 16:53

Conversion failed when converting datetime from character string

28-Jun-09 16:53

Hi,

Advantages of SQL SERVER EXPRESS EDITION

http://msdn.microsoft.com/en-us/library/ms345154(SQL.90).aspx[^]

Q1)Limitations of SQL SERVER EXPRESS EDITION

<a href="http://www.dotnetspider.com/SqlServer-Tutorial-158.aspx">http://www.dotnetspider.com/SqlServer-Tutorial-158.aspx</a>[<a href="http://www.dotnetspider.com/SqlServer-Tutorial-158.aspx" target="_blank" title="New Window">^</a>]

Q2) I am planning to have around 30 persons using this DB, is this possible?

There is no hard-coded limit to the number of users that can attach to SQL Server Express but their CPU and memory limits impose practical limits on the number of users that can achieve acceptable response times from a SQL Server Express database.

(Obtained from SQL Server 2005 Express Edition Overview)

You may also be interested in SQL Server 2005 Features Comparison
http://www.microsoft.com/Sqlserver/2005/en/us/compare-features.aspx[^]

Hope it helps
Smile | :)

Niladri Biswas

piticcotoc27-Jun-09 21:13

piticcotoc

27-Jun-09 21:13

I have a database (mssql 2005) with a table which has a datetime column. I'm trying to insert data into tha table from c# but i'm getting this exception: "Conversion failed when converting datetime from character string". Here is the code (stripped of connection declaration).

SqlParameter currentDate = new SqlParameter();
currentDate.Value = System.DateTime.Now;
currentDate.ParameterName = "@dataTrimitereService";
string insert = "INSERT INTO table(col 1, col 2, date) VALUES ('value 1', 'value 2', '@dataTrimitereService');
sqlCommand.ExecuteNonQuert(insert);

I've also tried changing regional settings and date format but no success. Thanks.

Re: Conversion failed when converting datetime from character string [modified]

Niladri_Biswas27-Jun-09 21:32