Showing posts with label selecting. Show all posts
Showing posts with label selecting. Show all posts

Wednesday, March 28, 2012

Please help ??

I have installed SQL Server 2005 Express on Windows XP Pro with SP2.

I have also installed ASP.NET Web Matrix on same machine. Upon selecting the Data tab (next to the Workspace tab), I click on the Database Connection button. I am asked if I want to connect to an Access db or a SQL Server db. I select the SQL Server db and I then receive a dialog box that asks me the Server Name: the default is (local) and I have used that as well as localhost. I use SQL Server authentication and enter a username of sa and a password that I set up during the installation process. I finally select the Create a New Database link and I then get the message SQL Server does not exist or access denied.

I have checked that my services are running. I also have uninstalled and reinstalled both SQL Server Express 2005 and ASP.NET Web Matrix but still the same glitch.

Any help would be greatly appreciated...

Glenn

Hello All,

I have solved my problem. The server name was my issue. I had list (local), localhost and my machine name, none of which worked. I then added my machine name\sqlexpress and it worked.

GO FIGURE ...

sql

Friday, March 9, 2012

Pl/SQL Beginner Problem, Selecting all records?

SET SERVEROUTPUT ON;

DECLARE
student_rec student%ROWTYPE;
BEGIN
SELECT *
INTO student_rec
FROM student
WHERE student_id = 156 ;
DBMS_OUTPUT.PUT_LINE ('Last Name : '|| student_rec.last_name || chr(10)||
'First Name : '|| student_rec.first_name || chr(10)||
'Phone Number : '|| student_rec.phone || chr(10)||
'Reg Date : '|| student_rec.registration_date);
EXCEPTION
WHEN no_data_found THEN
RAISE_APPLICATION_ERROR(-20001, 'Student with id = 156 is not in the Database');
WHEN others THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE || ' '|| substr(SQLERRM,1,80));
END;
.

In the above code it selects the information for the student with the id of 156, how do I change it so that it selects all students from the table instead of just the one?

Any help appreciated.just remove the condition tht where student id = 156:

SELECT *
INTO student_rec
FROM student

above script will select all the student in student and store it into the sudent_rec .

RAISE_APPLICATION_ERROR(-20001, 'Student with id = 156 is not in the Database');

when you want to raise the exeception just write it ther is no record in the table instad of student with id= 156 is not in the database.|||Hi,

Use a cursor & remove the Hard coded value 156 in the query.

Originally posted by iknownothing
SET SERVEROUTPUT ON;

DECLARE
student_rec student%ROWTYPE;
BEGIN
SELECT *
INTO student_rec
FROM student
WHERE student_id = 156 ;
DBMS_OUTPUT.PUT_LINE ('Last Name : '|| student_rec.last_name || chr(10)||
'First Name : '|| student_rec.first_name || chr(10)||
'Phone Number : '|| student_rec.phone || chr(10)||
'Reg Date : '|| student_rec.registration_date);
EXCEPTION
WHEN no_data_found THEN
RAISE_APPLICATION_ERROR(-20001, 'Student with id = 156 is not in the Database');
WHEN others THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE || ' '|| substr(SQLERRM,1,80));
END;
.

In the above code it selects the information for the student with the id of 156, how do I change it so that it selects all students from the table instead of just the one?

Any help appreciated.|||When I remove the WHERE statement, I get an error when I run it saying
"-1422 ORA-01422: exact fetch returns more than requested number of rows"|||Hi,

Use CURSOR to avoid this error. Because you can fetch only one row into the record type student_rec . When the value 156 is harcoded in the query , exactly one row is fetched into student_rec . So it works fine. But once you remove the hard coded value 156 from the query, all the records are fetched . Since the record type student_rec can accept only one value, it displays the error ORA-01422: exact fetch returns more than requested number of rows. To avoid this error & fetch all the records into student_rec, use a CURSOR.

Originally posted by iknownothing
When I remove the WHERE statement, I get an error when I run it saying
"-1422 ORA-01422: exact fetch returns more than requested number of rows"|||Its ok, fixed it! Thanks.|||As an aside from your question, you should get out of the habit of doing this:

EXCEPTION
WHEN no_data_found THEN
RAISE_APPLICATION_ERROR(-20001, 'Student with id = 156 is not in the Database');
WHEN others THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE || ' '|| substr(SQLERRM,1,80));
END;

All that does is potentially hide errors from the user and allow inconsistent partial transactions to be committed. It should be simply:

EXCEPTION
WHEN no_data_found THEN
RAISE_APPLICATION_ERROR(-20001, 'Student with id = 156 is not in the Database');
END;