Database

4-Sep-02 7:54

i want to use ,such as :
select t1.a,sum(t1.b),sum(t2.b) from
(Select a,b from t1) t1 left outer join
(select a,b from t2) t2 on t1.a=t2.a
group by t1.a

some rows exist in t1,but it not exist in t2
so in the result the sum(t2.b) is NULL

my database is Microsoft Access,so can't use function isnull(t2.b,0),---the function isnull(column,replacevalue) only used in Microsoft SQL Server
How can i do?

but can't use such as :
create table table1(
column2 float DEFAULT 0,

Mazdak4-Sep-02 7:59

Mazdak

4-Sep-02 7:59

use select count(b) from table1 first.It retreive the number of rows in that table.So you can figure out if it is 0 or not.I have never used it in Access but it works in SQL Server.

Mazy

"If I go crazy then will you still
Call me Superman
If I’m alive and well, will you be
There holding my hand
I’ll keep you by my side with
My superhuman might
Kryptonite"Kryptonite-3 Doors Down

freeworldbird4-Sep-02 8:07

4-Sep-02 8:07

sorry , i don't know what's you meaning yet.
can you write a demo use sql?

Mazdak4-Sep-02 23:24

Mazdak

4-Sep-02 23:24

First you run this query:

select count(columnname) from tablename

It will return only one record and its number of rows in that column and table,so if there is no row in that table the returning record value will be 0 so there is no problem if it is null or not,if it is bigger that 0 then you can run your query.

Clear?

Mazy

"If I go crazy then will you still
Call me Superman
If I’m alive and well, will you be
There holding my hand
I’ll keep you by my side with
My superhuman might
Kryptonite"Kryptonite-3 Doors Down

Nick Parker4-Sep-02 9:31

Nick Parker

4-Sep-02 9:31

Not exactly sure what it is you are trying to find, if you only want to find the entries in one table that aren't in another then you simply need to do an outer join against the table like such:

SELECT * FROM table1 t1 LEFT OUTER JOIN table2 t2 ON t1.a = t2.a

This should grab everything that is in table1 that is not in table2 where there is a relation between t1 and t2 on field a. Does this help, or are you trying to do something else?

Nick Parker

freeworldbird4-Sep-02 16:35

4-Sep-02 16:35

in fact i used it,such as :

select t1.a,sum(t1.b-t2.b) from
(Select a,b from t1) t1 left outer join
(select a,b from t2) t2 on t1.a=t2.a
group by t1.a

so ,the sum(t1.b-t2.b) will return NULL as long as t2.b is NULL.

freeworldbird5-Sep-02 16:08

Datetime in SQL server<>ADO

5-Sep-02 16:08

of cause it can work,table define such as

create table t1(
a varchar(10),
b float
);

create table t2(
a varchar(10),
b float
);

can you test in your computer?

Rulle3-Sep-02 23:01

Rulle

3-Sep-02 23:01

Hi!

I use the Datetime dataype in SQL-Server 2000 to store date and time eg "2002-09-04 09:18:20". When I insert data in through VB6.0 and ADO 2.6 i set the in-parameter to an adDBTime and it seems to work fine.

Could there be problems in the future and/or is there a better datatype in ADO for this case?

Re: Datetime in SQL server<>ADO

Jon Hulatt4-Sep-02 5:46

Jon Hulatt

4-Sep-02 5:46

adDBTime was designed for that purpose. Who knows what could happen in the future though.

You can use adVarChar too, if you like

STL is a religeon. Enquiries to Reverend Christian Graus

Re: Datetime in SQL server<>ADO

Rulle4-Sep-02 20:54

Rulle

4-Sep-02 20:54

I found a nice matrix over datatypemappings SQL-server 2000 <> ADO 2.6 at:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adosql/adoprg02_294j.asp

Refering to the matrix, varchar should be mapped to adChar, but adVarchar seems more logic to me. Is it an printerror?

plz tell how to get rid of database error?

drmzunlimited3-Sep-02 11:22

drmzunlimited

3-Sep-02 11:22

dim str as string
str= "INSERT INTO employees Values(@ID,@FN,@LN)"
dim InsCom as new SqlCommand(str,SqlConnection1)

plz tell me what is the error in this code.

MS Visual Studio.NEt shows error in the following SqlCommand function.

when i write this line dim InsCom as new sqlcommand(str,Sqlconnection1)

then after new the window pops up but there is nothing like sqlcommand in there?

OR
plz tell how to execute a query from a MS-ACCESS database?

thnx
Rashmi

Re: plz tell how to get rid of database error?

notadood4-Sep-02 4:14

notadood

4-Sep-02 4:14

You need to build your sql string differently. The parameters are inside of your quote marks so the values can not be passed in. Depending on your datatypes it should look something more like:

dim str as string
str= "INSERT INTO employees Values(" + @ID + "," + @FN + "," + @LN + ")"
dim InsCom as new SqlCommand(str,SqlConnection1)

If your datatypes are int you may have to do something like str(@ID) as well as add single quotes if your datatypes are char.

SQL -> DOTNET data types

Ray Cassick3-Sep-02 9:21

Re: SQL -> DOTNET data types

3-Sep-02 9:21

Is there some master chart somewhere that gives me a map on how to relate data types between VB / C# and SQL server?

I have looked all over google and see tons of posts just like mine with regards to VB6 but no one seemes to answer them.

notadood3-Sep-02 10:25

notadood

3-Sep-02 10:25

Here is part of it:

SQL Server Data Types Corresponding VB Data Type
http://www.tsqlsolutions.com/Articles/Index.cfm?ArticleID=9728

Re: SQL -> DOTNET data types

SimonS6-Sep-02 19:29

SimonS

6-Sep-02 19:29

I'm presuming that you want the .NET to SQL datatype mappings?
Check out the help on MSDN for "System.Data.SqlTypes Namespace".
It shows the mapping between native SQL types and .NET SQLType types.

Cheers,
Simon

"Sign up for a chance to be among the first to experience the wrath of the gods.", Microsoft's home page (24/06/2002)

Locks: How-to

Le centriste3-Sep-02 5:56

Le centriste

3-Sep-02 5:56

How do you perform row locking in Transact-SQL? Do you have an example please?

Michel

It is a lovely language, but it takes a very long time to say anything in it, because we do not say anything in it, unless it is worth taking a very long time to say, and to listen to.

- TreeBeard

Re: Locks: How-to

Joao Vaz3-Sep-02 6:32

Joao Vaz

3-Sep-02 6:32

Check this Big Grin | :-D

http://www.4guysfromrolla.com/webtech/101100-1.shtml[^]

Cheers,
Joao Vaz
A Programming Language is a tool that has profound influence on our thinking habits -The late giant Edsger Dijkstra 1930 - 2002

And if your dream is to care for your family, to put food on the table, to provide them with an education and a good home, then maybe suffering through an endless, pointless, boring job will seem to have purpose. And you will realize how even a rock can change the world, simply by remaining obstinately stationary. -

ADO.NET and DSN

Ray Cassick2-Sep-02 18:44

2-Sep-02 18:44

I have an odd question here for all you data-centric folks...

Let's say that I am developing a webservice that will perform some actions on a SQL database. I am to the point where I am starting to code the data-centric portion of the service. I want to add a connection to my webservice, but find that I can't seem to reference a DSN. It looks to me like I have to reference the servername and database directly.

Alot of this fancy ADO.NET integration into the IDE seems to me to look like they expect us to develop on the same system we are going to deploy on? If I am developing on a system where the servers name is 'Serv01' and use all the fancy IDE integration wizzards to set things up, then move to a deployment where I have no control over my server names, how if my app supposed to function? This is why I like DSNs. I can mandate what they are named, and as long as they reference a server that has the proper database that I need my app does not care.

Am I the only one that sees this as a problem?

notadood3-Sep-02 4:30

notadood

3-Sep-02 4:30

We got around this by storing the database server name and database name in the registry. It is set during the installation of the program on the end computer.

Ray Cassick3-Sep-02 6:14

3-Sep-02 6:14

yesh, I understand that part, but then all the fancy wizzard stuff goes to hell.

If it let you point to a DSN it could all be happy.

Paul Riley3-Sep-02 7:20

Paul Riley

3-Sep-02 7:20

To point to a DSN, you need to download OBDC.NET from Microsoft's site
ODBC .NET Data Provider[^]
and instead of using System.Data.OleDb... objects, you use Microsoft.Data.Odbc... objects.

Remember that ODBC is a Windows thing but .NET is supposed to be as neutral as possible.

Paul

Ray Cassick3-Sep-02 8:07