|
If you only put last name, the SQL statement will be like
SELECT * FROM Users WHERE Last_Name = 'LName' AND FName = ''
Most likely it won't return you anything as there won't be a person with FName = ''
The easiest way is to use AND FName LIKE'" & strSearchFName & "%'"
The recommended way is to make sure your SQL is correct (build a dynamic SQL, e.g. remove FName from condition if FName is not supplied)
Hope it helps,
Edbert P.
Sydney, Australia.
|
|
|
|
|
i have another question that i need help in solving it or at least a hint
please......................
i hope you cn help me solve it...
Write down a single SQL statement that displays the following output by using tables EMP and DEPT.
Output:
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
EMPNO ENAME
---------- ----------
7782 CLARK
7839 KING
7934 MILLER
20 RESEARCH DALLAS
EMPNO ENAME
---------- ----------
7369 SMITH
7566 JONES
7788 SCOTT
7876 ADAMS
7902 FORD
30 SALES CHICAGO
EMPNO ENAME
---------- ----------
7499 ALLEN
7521 WARD
7654 MARTIN
7698 BLAKE
7844 TURNER
7900 JAMES
6 rows selected.
40 OPERATIONS BOSTON
no rows selected
creating emp and dept tables::
CREATE TABLE DEPT (
DEPTNO NUMBER(2) NOT NULL,
DNAME VARCHAR2(14),
LOC VARCHAR2(13));
INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');
CREATE TABLE EMP (
EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) NOT NULL);
INSERT INTO EMP VALUES (7839,'KING','PRESIDENT',NULL,TO_DATE('17-NOV-1981','DD-MON-YYYY'),5000,NULL,10);
INSERT INTO EMP VALUES (7698,'BLAKE','MANAGER',7839,TO_DATE('1-MAY-1981','DD-MON-YYYY'),2850,NULL,30);
INSERT INTO EMP VALUES (7782,'CLARK','MANAGER',7839,TO_DATE('9-JUN-1981','DD-MON-YYYY'),2450,NULL,10);
INSERT INTO EMP VALUES (7566,'JONES','MANAGER',7839,TO_DATE('2-APR-1981','DD-MON-YYYY'),2975,NULL,20);
INSERT INTO EMP VALUES (7654,'MARTIN','SALESMAN',7698,TO_DATE('28-SEP-1981','DD-MON-YYYY'),1250,1400,30);
INSERT INTO EMP VALUES (7499,'ALLEN','SALESMAN',7698,TO_DATE('20-FEB-1981','DD-MON-YYYY'),1600,300,30);
INSERT INTO EMP VALUES (7844,'TURNER','SALESMAN',7698,TO_DATE('8-SEP-1981','DD-MON-YYYY'),1500,0,30);
INSERT INTO EMP VALUES (7900,'JAMES','CLERK',7698,TO_DATE('3-DEC-1981','DD-MON-YYYY'),950,NULL,30);
INSERT INTO EMP VALUES (7521,'WARD','SALESMAN',7698,TO_DATE('22-FEB-1981','DD-MON-YYYY'),1250,500,30);
INSERT INTO EMP VALUES (7902,'FORD','ANALYST',7566,TO_DATE('3-DEC-1981','DD-MON-YYYY'),3000,NULL,20);
INSERT INTO EMP VALUES (7369,'SMITH','CLERK',7902,TO_DATE('17-DEC-1980','DD-MON-YYYY'),800,NULL,20);
INSERT INTO EMP VALUES (7788,'SCOTT','ANALYST',7566,TO_DATE('09-DEC-1982','DD-MON-YYYY'),3000,NULL,20);
INSERT INTO EMP VALUES (7876,'ADAMS','CLERK',7788,TO_DATE('12-JAN-1983','DD-MON-YYYY'),1100,NULL,20);
INSERT INTO EMP VALUES (7934,'MILLER','CLERK',7782,TO_DATE('23-JAN-1982','DD-MON-YYYY'),1300,NULL,10);
|
|
|
|
|
Have you considered doing your own homework ?
Christian
I have several lifelong friends that are New Yorkers but I have always gravitated toward the weirdo's. - Richard Stringer
|
|
|
|
|
I ALREADY SOLVED 2 PROBLEMS AND THOSE ARE THE REMAINNING 2 QUESTIONS
NO BODY SLVED THOSE TWO QUESTIONS
|
|
|
|
|
I'm not sure how that is relevant. Neither question is particularly hard, and the core point is that if you don't take the time to do your homework when it's relatively easy, how will you cope as it gets harder, and you've become accustomed to asking strangers to do it for you ?
If you had posted your attempt and asked for help, I'd have been glad to help, but no-one here is going to do your homework for you.
Christian
I have several lifelong friends that are New Yorkers but I have always gravitated toward the weirdo's. - Richard Stringer
|
|
|
|
|
I DONT KNOW WHY YOU ARE YOU TALKING TOO MUCH
BUT I AM TRYING TO SOLVE IT FOR MORE THAN TWO WEEKS AND I TRIED SEVERAL WAYS
HERE IS THE MOST RECENT WAYS BUT IT DIDNT WORK:
SELECT DISTINCT D.DEPTNO, D.DNAME, D.LOC,
(SELECT E1.EMPNO FROM EMP E1 WHERE E1.DEPTNO = 10) Dept_10,
(SELECT E2.EMPNO FROM EMP E2 WHERE E2.DEPTNO = 20) Dept_20,
(SELECT E3.EMPNO FROM EMP E3 WHERE E3.DEPTNO = 30) Dept_30
FROM DEPT D, EMP E
WHERE E.DEPTNO = D.DEPTNO;
AND I WILL TRY TO FIND MY OTHER ATTEMPTS AND PUT IT FOR YOU
I WANT TO KNOW HOW COME IT IS VERY SIMPLE QUESTIONS?
FOR YOUR INFO WE ARENT TEACHED THE SQL STATEMENTS BUT WE ARE STUDYING THE DATA MODELING AND OTHER STAFF AND WE HAVE TO LEARN SQL OURSELF.SO DONT THINK WE ARE EXPERTS OR EVEN STUDENTS OF SQL, WE ARE ONLY SELF-LEARNNING STUDENTS.
I'V WENT TO THE COMPLETE DOCUMENTATION OF THE SQL BUT I DIDNT FIND THE CORRECT FUNCTIONS TO USE!!
|
|
|
|
|
i have this questions that i need help in solving it or at least a hint
please......................  
i hope you cn help me solve it...
Write down single SQL statement to display currency (salary) in words without using any direct function. e.g:
EMPNO ENAME SAL Salary in words
---------- ---------- -------- ---------------
7369 SMITH 800 Eight hundred
7499 ALLEN 1600 One thousands and six hundred
7521 WARD 1250 One thousands, two hundred and fifty
|
|
|
|
|
THESE OUR VERSION OF THE SOLUTION BUT IT DIDNT FULLY MEET THE REQUIRMENTS
BECAUSE AS YOU SEE IT MUST CONTAIN THE COMMA(,) AFTER THE thousands IF THERE IS TENS AFTER THE hundredS ANS AN (AND) BEFORE THE hundredS AND IT SHOULD CONTAIN AN (AND) AFTER THE thousands IF THERE IS NO TENS AFTER THE hundredS
IF YOU DIDNT UNDERSTAND ME YOU CAN COMPARE THE OUTPUT OF MY QUERY BELOW AND THE OUTPUT OF THE EXAMPLE OF THE FIRST THREAD:
SELECT EMPNO, ENAME, SAL, TO_CHAR(TO_DATE(TO_CHAR(SAL),'YYYY'), 'YEAR') SALARY
FROM EMP
ORDER BY EMPNO;
|
|
|
|
|
I am aware that ADO.Net transaction mechanism is the same as SQL Server.
My question is does is make any difference on handling transaction by ADO.net or directly on SQL Server database.
Is either of two better then other? in short SQL Transaction vs ADO.Net Transaction. 
ketty
|
|
|
|
|
Hi,
I have bought some space on MS SQL server to host my site. the company has asked for IP address that will be accessing the server.
My question is what IP addresses do I need to provide them:
Is it the IP address of my local machine?
Is it the IP address of my web site?
Any ideas??
|
|
|
|
|
hasanali00 wrote:
IP address that will be accessing the server
I think you've answered your own question. It is the IP address of the machine that you will be using to access the server. I'm assuming that the web application will be hosted by this company also, so the IP address of the web site will be internal to them. The other IP address, the one you provide, must therefore be the one you have for administrative access - i.e. the IP address of the machine you will be using to administer the account.
DISCLAIMER: This is mostly based on a set of assumptions, whether stated or otherwise, that may or may not be correct. If you need clarity you MUST go back to your hosting service provider and ask them what they mean.
Do you want to know more?
Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Please remember that the force of gravity can go up as well as down.
|
|
|
|
|
|
I'm writing a new database driven application. In one of my stored procedures, I want to add user data and get back user id from table.
The table has this structure:
ID, numeric, identity with identity seed =1,
username, char(25)
email, char(40)
my stored procedure looks like this:
ALTER PROCEDURE dbo.InsertNewUser
(
@UserName char(25),
@email char(40),
@ID numeric OUTPUT
)
AS
INSERT INTO Users
(UserName, email)
VALUES (@UserName, @email);
RETURN
How I changed the stored procedure to return the new added user's ID?
I code in C#.
Best regards,
A. Riazi
|
|
|
|
|
If this is SQL Server 2000:
SET @ID = SCOPE_IDENTITY() If SQL Server 7.0, you have to use the @@IDENTITY variable; however, this may produce incorrect results if a trigger fires which inserts rows.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
|
How do we retreive data from SQL database. Could anyone please explain in VB.net with a small example from database connection to databinding and retreiving the data in web application
|
|
|
|
|
This is an absolute beginner question - you should try reading the site at large and/or MSDN before asking a question like this. There is tons of info online.
Christian
I have several lifelong friends that are New Yorkers but I have always gravitated toward the weirdo's. - Richard Stringer
|
|
|
|
|
I guess time matters. There are only code snippets on simple data binding but not in complex data binding. Because i was unable to find it i have asked this question.
|
|
|
|
|
OK, well, this is more detail than the original question. What do you mean by 'complex data binding' ? Have you followed any of the 'simple' examples on the web, to give you some idea of what you require ?
Christian
I have several lifelong friends that are New Yorkers but I have always gravitated toward the weirdo's. - Richard Stringer
|
|
|
|
|
I have started working on a project where I will collect all metadata from a specific sql server database. I have got so far that I can collect the names of all tables and the names and datatypes of all columns in the tables.
Now I need to check what column is set as the primary key (if any) in each table. This has caused me some troubles. In the sysobjects table there are rows for every primary key. The look something like this: PK__Customers__78B3EFCA. Here PK is the name of the column and Customers is the name of the table. There must be a better way to control the name of the primary key column than to check the string above.
The other problem is also about the primary key. Often the primary key is set to be autonumbered. How can I control this? In a table I created recently I created this T-SQL script:
CREATE TABLE Accounts
(
AccountNr int PRIMARY KEY IDENTITY(100001, 1),
OwnerFK int,
Amount float
)
Is it possible to get all that information about the primary key?
|
|
|
|
|
EXEC sp_pkeys 'Accounts'
SELECT COLUMNPROPERTY(OBJECT_ID('Accounts'),'AccountNr','IsIdentity')
|
|
|
|
|
How do I get the  MSI installer to proceed with a MSDE install again?
It is not in Add/Remove and Ic na not find much in the registry.
Are there 'magical' hidden files somewhere? That MSI will look at for 'product is installed' indicators or something?
|
|
|
|
|
One component returns me an instance of IDBConnection. But I need an instance of
OleDBConnection class. I have no control over the component. Please advise.
|
|
|
|
|
Hi,
I am binding a DataTable to a DataGrid. To push the changes from
DataTable to the actual database I need to use OleDbCommandBuilder.
I have an instance of IDbDataAdapter. The restriction is that I
cannot create instances of OleDbDataAdaper. Also OleDbCommandBuilder
constructor does not accept IDbDataAdapter as a parameter.
Is there a workaround.
Help is appreciated.
Thanks and Regards,
Alomgir
|
|
|
|
|
Alomgir Miah wrote:
I have an instance of IDbDataAdapter
This is being pedantic, but you don't have an instance of IDbDataAdapter. That is an interface and you cannot instantiate interfaces or abstract classes, you can only instantiate concrete types like an OleDbDataAdapter. You have an IDbDataAdapter reference to a concrete type, i.e. something that inherits from IDbDataAdapter.
Alomgir Miah wrote:
The restriction is that I cannot create instances of OleDbDataAdaper
To create OleDbDataAdapter objects you need to place a
using System.Data.OleDb;
line at the top of the file. The documentation for the OleDbDataAdapter class[^] says this.
Quite probably you already have an OleDbDataAdapter that your are referencing through the IDbDataAdapter interface. If this is the case all you need to do is cast it to an OleDbDataAdapter in order to use it. You do that like this:
(OleDbDataAdapter)myIDbDataAdapterReference
Finally, and this is because it is a pet hate of mine, don't use command builders. They give false hope, and they hide from you what is actually going on. For more information, read this excellent article: Weaning Developers from the Command Builder[^]
Does this help?
Do you want to know more?
Vogon Building and Loan advise that your planet is at risk if you do not keep up repayments on any mortgage secured upon it. Please remember that the force of gravity can go up as well as down.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
