|
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,
|
|
|
|
|
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
|
|
|
|
|
sorry , i don't know what's you meaning yet.
can you write a demo use sql?
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
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.
|
|
|
|
|
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?
|
|
|
|
|
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?
|
|
|
|
|
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
|
|
|
|
|
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?
|
|
|
|
|
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
|
|
|
|
|
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.
|
|
|
|
|
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.
|
|
|
|
|
Here is part of it:
SQL Server Data Types Corresponding VB Data Type
http://www.tsqlsolutions.com/Articles/Index.cfm?ArticleID=9728
|
|
|
|
|
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)
|
|
|
|
|
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
|
|
|
|
|
Check this 
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. -
|
|
|
|
|
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?
|
|
|
|
|
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.
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
Well according to the download page this comes as part of Mdac 2.7, which is installed as part of .NET, so I should already have this.
I'll take a look at it when I get back to the home office.
|
|
|
|
|
Ray Cassick wrote:
Well according to the download page this comes as part of Mdac 2.7
No, it says it requires MDAC 2.7 
Trust me, I've been using it for a while. It lacks some of the wizards that OleDb uses, but that's only a good thing in my opinion.
Paul
|
|
|
|
|
Well I'll give it a try then...
Thanks.
|
|
|
|
|
How to store pics or photo at SQL 2000 server database?
I Love C++
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
