|
Dear all,
i have tow tables 'Users' and 'Applications'
every user may be has no,one or more applications
how to write a SQL satement that get me every user who hasn't application.
in other word i want user without application...
thanks.
|
|
|
|
|
It would be helpful to answer if you state the field names, type
and with which field both tables are connected.
I think you have to do something like:
select userId from user_table where (Application_table.applCnt = 0 and Application_table.userId = user_table.userId)
if both tables are connected by userId.
Thanks & Regards,
Suman
|
|
|
|
|
select * from Users
where not exists (
select A.AppID from Applications A
where A.AppID = Users.AppID
)
|
|
|
|
|
SELECT DISTINCT Users.IDApp FROM Users WHERE Users.IDApp NOT IN (SELECT Application.IDAp FROM Application)
"My advice to you is to get married. If you find a good wife, you will be happy; if not, you will become a philosopher." Socrates
|
|
|
|
|
Hi
I have a problem of filling null value in the column.
I have written the following query,
select c.column_name,t.constraint_type from all_cons_columns c, user_constraints t where t.table_name=c.table_name and c.table_name='student'
student table
student id - primary key
studentname - varchar
but the oracle query returns only the column having primary or foreign key. not returing the studentname. i want student name also with constraint type=null
we can do this is sql server by isnull(c.constraint_type,'')
but how to do the same in oracle? Any one help me please.........
kesavan
|
|
|
|
|
NK7 wrote: we can do this is sql server by isnull(c.constraint_type,'')
For Oracle try out, nvl(c.constraint_type,''). I think that should be equivalent.
Chris Meech
I am Canadian. [heard in a local bar]
I agree with you that my argument is useless. [Red Stateler]
Hey, I am part of a special bread, we are called smart people [Captain See Sharp]
The zen of the soapbox is hard to attain...[Jörgen Sigvardsson]
I wish I could remember what it was like to only have a short term memory.[David Kentley]
|
|
|
|
|
First Off, I'm new to code project so if I post something thats already posted sorry. But I'm hoping to find someone who can help out with this build error that I'm receiving on an oledbadapter.insertcommand.
I'm loading data into a datagrid for review. After reviewing the data I'm using a sub procedure to load the data into the database from the datagrid.
"oledbadapter.InsertCommand(Insert Into table1 from )"
error(s):
(249): Comma, ')', or a valid expression continuation expected.
(249): Property access must assign to the property or use its value.
I'm having problems figuring out what to put after the "from" statement. If this is not enough information just let me know and also let me know if I'm posting this right with out being irate about it.
Swish
|
|
|
|
|
Second attempt at this as my connection broke the first time round and IE lost the post.
Swisher24 wrote: "oledbadapter.InsertCommand(Insert Into table1 from )"
error(s):
(249): Comma, ')', or a valid expression continuation expected.
(249): Property access must assign to the property or use its value.
Okay. The OleDbDataAdapter class doesn't have a method called InsertCommand (see the class reference on MSDN[^]). It does, however, have a property called InsertCommand so your code should read something like this:
oledbadapter.InsertCommand = someSqlString
Swisher24 wrote: I'm having problems figuring out what to put after the "from" statement
Okay, this second part is that you are using an INSERT command. You get data "from" a table, you put data "into" a table. You have already specified which table you are putting data "into". See the INSERT Command reference[^] on MSDN.
Your command needs to be something along the lines of:
INSERT INTO Table1(Column1, Column2, Column3) VALUES ('value1', 'value2', 'value3')
So, you need to specify the columns that the data will be going into and the values of the data.
I've not used Data Adapters much (they don't fit most of the applications I've written) so I'm going on 3 year old knowledge that you typically specify the values as a series of parameters. If your database is SQL Server the parameter name is in the format @paramName, if you are using Access the parameters are a simple ? placeholder (so remember that the order of the columns must match the order of the columns in the DataTable being passed into the adapter)
Does this help?
|
|
|
|
|
Thanks Colin but I've figured out another way to take the data from the datagrid into the database.
Swish
|
|
|
|
|
Hi All,
I want to write a script/tool which would update the schema of the existing database (in SQL Server 2005)
i.e Add/drop a table, column, constraint etc. on a ongoing basis.
The input to my tool would be the latest_schema script which creates the database with all the latest tables, columns, constraints etc.
So the tool that I am going to write should perform a schema-diff with my db Vs the script provided and add/drop the tables, cols, constraints on my database.
I have a couple of solutions in my mind but feel that they are not very efficient.
1. Create a new database running the provided script, write a stored procedure to loop thro all the tables to figure out the diff between the existing db and the current db and update the current db: Problem here is my current db has data and lots of constraints, which might pose a problem.
2. Use some third party tools to generate the sync script and execute it against my current db but I am not sure about the free tools available out there.
Can somebody please provide me some guidelines/tips on how to go ahead about this.
Thanks in advance.
PJ
|
|
|
|
|
Does anyone know how codeproject stores its articles ??? in a field of type "ntext" in a text file ???
|
|
|
|
|
Hello. I have a sql statement that searches money.
SELECT [fldItemId] FROM tblCost where ([fldCost] BETWEEN 0 AND 2100)
the tbl has about 40 records in it.When I use the sql server interface that was provided by godaddy, and use the same sql statement, I get about 20 records that match. But when I use a web page (aspx using c#), I get no results. If anyone can offer me any advice, it would be great. I have been on this for a few days and I want to be able to search my records by cost.
|
|
|
|
|
When you say you get no results. What do you mean? How do you know? Are you binding it to something? If so, have you done the following?
item.DataSource = myDataSource;
item.DataBind();
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
I am binding to a data grid.
The following code is a snippit of a function that searches the database.
Sql = "SELECT [fldItemId] FROM tblCost where ";
Sql += "([fldCost] BETWEEN 0 AND 100)";
conn.Open();
com = new SqlCommand(Sql, conn);
reader = com.ExecuteReader();
while (reader.Read())
_ItemIdList.Add((int)reader["fldItemId"]);
conn.Close();
}
return _itemIdList;
To call the function, I use
GridSearchResults.DataSource = SearchControl.Search(query);
GridSearchResults.DataBind();
I ran other statements that did not look in the fldCost and I got records back. (btw fldCost is sql money type)
|
|
|
|
|
Have you tried putting a breakpoint on this line
<br />
GridSearchResults.DataSource = SearchControl.Search(query);<br />
and seeing if you actually get any results, but they are just not being displayed?
Also, sql money gets mapped to a decimal in c#
ChrisB
|
|
|
|
|
OK, I found what was wrong. It seems that I was clearing my list and returning a blank list. I founf this out because I ran a count in the while(reader.Read()) loop and it said 42 when the datagrid was empty.
Thanks everyone for your help.
|
|
|
|
|
Select a.*, b.* from Customers a,(Select OrderID,Orderdate,CustomerID from Orders) b where a.CustomerID = b.CustomerID
facing a prob while modifying this one through my program.. the code seems right.. not v good with sql.. is the syntax of this query correct?
|
|
|
|
|
This syntax is not correct. It seems as though you are trying to do this:
SELECT a.*, b.OrderID, b.OrderDate, b.CustomerID
FROM Customers a
INNER JOIN Orders B
ON a.CustomerID = b.CustomerID
This returns all of the records that match the conditions in BOTH a and b. Note that it is generally not good practice to use * to retrieve columns. This is just lazy practice and should be discouraged as much as possible. Instead of doing this, retrieve the parameters explicitly.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
I have written one stored procedure(SP). Just I want to check whether it is running correctly or not. How can I debug a SP in sql server 2000/ 2005? is there any diff between sql server 2000 and 2005? pls suggest me.
thanks,
chakri
|
|
|
|
|
There are many ways to do this. One way would be to print out values at appropriate points.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
In SQL Server 2000, you can right click the stored procedure and debug but for SS2005 I guess you can debug only thro Visual Studio 2005.
|
|
|
|
|
I have this error when i try to update a field from my program:
SQL:Statement too long because my table contains about 15000 records and i have 26 columns CRecorrdset::Update(); he will update all !
if i try to update 1 table with 50 records is work fine!
What can i do?
My program is CDialog ,MFC!
My table visualfox 6.0!
please someoane help me!
Bravoone
|
|
|
|
|
How long is the statement ? Perhaps you need to break it up into smaller ones.
Christian Graus - C++ MVP
'Why don't we jump on a fad that hasn't already been widely discredited ?' - Dilbert
|
|
|
|
|
You are probably getting a timeout because of the volume of data that you are trying to update. Try to split this into smaller batches and see if that fixes it.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
i dont write an SQL statement why it give me that error ?
Bravoone
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
