Database Discussion Boards

CodingYoshi25-May-08 8:40

25-May-08 8:40

How can I assign the result of a query to a variable. For example

DECLARE @FoundID VARCHAR(10)
IF NOT EXISTS(SELECT @FoundID = Employee.EmployeeID
FROM Employee
JOIN EmployeeTerritory
ON Employee.EmployeeID = EmployeeTerritory.EmployeeID
JOIN Territory
ON EmployeeTerritory.TerritoryID = Territory.TerritoryID
WHERE AssignedPostal = SUBSTRING(@CustomerPostalCode, 1, 1))
RAISERROR('The speicified CustomerPostalCode is not assigned to any salesperson! Unassigned PostalCode!', 11, 1)

So basically I want to assign Employee.EmployeeID from the query result to the variable. Most examples online show assigning result of aggregate functions to variables.

Thanks

Re: Assigning value to variable

Colin Angus Mackay25-May-08 13:32

Colin Angus Mackay

25-May-08 13:32

CodingYoshi wrote:
So basically I want to assign Employee.EmployeeID from the query result to the variable. Most examples online show assigning result of aggregate functions to variables.

If your SELECT results in only one row being returned then it should work. However, you are doing the assignment inside an IF (NOT) EXISTS which doesn't obey the normal rules of processing. It is designed to short circuit as soon as any matching data is found in order to allow the appropriate conditional statements to be run.

I would suggest moving the SELECT out of the IF condition and changing the IF condition to an ISNULL(@FoundId).

Recent blog posts:
* Introduction to LINQ to XML (Part 1) - (Part 2) - (part 3)

My website | Blog

Re: Assigning value to variable

CodingYoshi26-May-08 8:33

CodingYoshi

26-May-08 8:33

ISNULL(@FoundID) takes 2 parameters and when provided results in error. But this works
IF @FoundID IS NULL
RAISERROR('The speicified CustomerPostalCode is not assigned to any salesperson! Unassigned PostalCode!', 11, 1)
ELSE
SET @SalesPersonID = @FoundID

Thanks for advising to move the select statement out of if statement.

Re: Assigning value to variable

Colin Angus Mackay26-May-08 13:37

Colin Angus Mackay

26-May-08 13:37

CodingYoshi wrote:
ISNULL(@FoundID) takes 2 parameters and when provided results in error.

Ah yes... Sorry, I was going off the top of my head. Glad it still pointed you in roughly the right direction.

Recent blog posts:
* Introduction to LINQ to XML (Part 1) - (Part 2) - (part 3)

My website | Blog

Re: Assigning value to variable

Laxmikant Lad25-May-08 19:17

Laxmikant Lad

25-May-08 19:17

DECLARE @FoundID VARCHAR(10)
SELECT @FoundID = Employee.EmployeeID
FROM Employee
JOIN EmployeeTerritory
ON Employee.EmployeeID = EmployeeTerritory.EmployeeID
JOIN Territory
ON EmployeeTerritory.TerritoryID = Territory.TerritoryID
WHERE AssignedPostal = SUBSTRING(@CustomerPostalCode, 1, 1)

IF NOT EXISTS(@FoundID)
RAISERROR('The speicified CustomerPostalCode is not assigned to any salesperson! Unassigned PostalCode!', 11, 1)

Re: Assigning value to variable

CodingYoshi26-May-08 8:32

CodingYoshi

26-May-08 8:32

NOT EXISTS(@FoundID) results in error. But this works
IF @FoundID IS NULL
RAISERROR('The speicified CustomerPostalCode is not assigned to any salesperson! Unassigned PostalCode!', 11, 1)
ELSE
SET @SalesPersonID = @FoundID

Thanks for advising to move the select statement out of if statement.

Invalid descriptor index ??

Vaclav_25-May-08 7:27

Vaclav_

25-May-08 7:27

Could some please translate this error to human language?
I get it after I attempt to do recordset update.
What is a descriptor index and if it is invalid what is is?
Can I get more info from VC 6.0??
Thanks for reading.
Vaclav

PS Yes I did google it and came up with nothing solid.

Conversion of Data Access method of SQL Server 2005 to Data Access method of ADO.NET

kashshay25-May-08 7:23

kashshay

25-May-08 7:23

I have developed a project for a tutor academy in VS2005. For data accessing I used SQL Server 2005 Express features like Data Adapter etc. Now I want to implement the data accessing through ADO.NET. Is there any conversion tool available on the internet which can translate / convert data accessing methodology of SQL Server Express to ADO.NET? If not how can I achieve it?

DotGuru

Re: Conversion of Data Access method of SQL Server 2005 to Data Access method of ADO.NET

Hesham Amin25-May-08 21:23

Hesham Amin

25-May-08 21:23

kashshay wrote:
I used SQL Server 2005 Express features like Data Adapter etc. Now I want to implement the data accessing through ADO.NET

Data adapter is an ado.net technology, sql express has nothing to do with it. So you arr already using ado.net.

Hesham A. Amin
My blog

how to connect lan sql server with delphi

hesam11025-May-08 3:33

hesam110

25-May-08 3:33

hi
I want to know how can i connect to other pc sqlserver with another pc
over lan connection
Thanks

Re: how to connect lan sql server with delphi

Colin Angus Mackay25-May-08 5:18

Colin Angus Mackay

25-May-08 5:18

You have to enable TCP/IP on the SQL Server. By default it is switched off. You can do this in the SQL Server Configuration Manager.

You will then be able to connect to the SQL Server from other PCs.

Recent blog posts:
* Introduction to LINQ to XML (Part 1) - (Part 2) - (part 3)

My website | Blog

Re: how to connect lan sql server with delphi

Blue_Boy25-May-08 13:43

Blue_Boy

25-May-08 13:43

Yo can use UDL file [^]too.

I Love T-SQL
"Don't torture yourself,let the life to do it for you."

Use Page Locks when Accessing the Index

Brendan Vogt25-May-08 1:57

Brendan Vogt

25-May-08 1:57

Hi,

I am using SQL Server 2005. When I create an index on a foreign key there is an option to check the "Use Page Locks when Accessing the Index" option. I do not know when to check this and when not to?

Can someone please help clear this for me? If checked will it reduce performance, or is it standard procedure to always have it checked?

Thanks
Brendan

substring from smalldatetime

Mr.Kode25-May-08 0:50

Mr.Kode

25-May-08 0:50

i want to spilit the date on format dd/mm/yyyy and insert the substring as primary key field on another table
i tried the following but it failed

declare @exitDate smalldatetime
set @exitdate=(select Exit_date from invoice_details)

select substring( cast (@exitDate As varchar) As exitDateText,5,4)
from invoice_details

help please
regards

Re: substring from smalldatetime

Colin Angus Mackay25-May-08 5:16

Colin Angus Mackay

25-May-08 5:16

I'm guessing, because you didn't explain (you just supplied some broken code and said it didn't work) that you want to extract the year. Is that correct?

If so, why not just use the YEAR(@someDate) function? You can then cast that into a varchar.

Recent blog posts:
* Introduction to LINQ to XML (Part 1) - (Part 2) - (part 3)

My website | Blog

SQL Server Database Access

hariram2825-May-08 0:35

hariram28

25-May-08 0:35

Hi,

I'm using SQL Server 2000 with VB.NET 2005.

I'm trying to access the database which is in remote machine thru internet.

Can you help me with the required details & connection string...

Thanks & Regards.
Hariram

Re: SQL Server Database Access

Colin Angus Mackay25-May-08 5:19

Colin Angus Mackay

25-May-08 5:19

The connection string will be as normal, but with the IP address or full domain name as the server name.

Recent blog posts:
* Introduction to LINQ to XML (Part 1) - (Part 2) - (part 3)

My website | Blog

Re: SQL Server Database Access

hariram2826-May-08 2:15

hariram28

26-May-08 2:15

Thanks Colin,

The IP Address should be a STATIC IP right...?

Regards,
Hariram

Re: SQL Server Database Access

Colin Angus Mackay26-May-08 4:13

Colin Angus Mackay

26-May-08 4:13

The IP address of the SQL Server must be static

Recent blog posts:
* Introduction to LINQ to XML (Part 1) - (Part 2) - (part 3)

My website | Blog

ODBC ms access insertion problem via mfc

elegantcharm24-May-08 9:06

elegantcharm

24-May-08 9:06

hi,
This is code i am using I've picked it from some where and I've changed it according to my requirements but its not inserting a new record. it gives error like attempt failed or record set is read only. I don't know i am using dynaset as an opening mode but still i get the error. kindly any one knows where i am going wrong.

CDatabase *mydb=new CDatabase;
ASSERT(mydb);
mydb->OpenEx("DSN=ff");

if(!mydb->IsOpen())
{

MessageBox("Unable to connect to Database. Please\ncontact your server administrator.", "Database Error", MB_ICONEXCLAMATION);
return;
}
CRecordset*myrec=new CRecordset(mydb);
ASSERT(myrec);
myrec->Open(CRecordset::dynaset,_T("SELECT s,p FROM e"),CRecordset::noDirtyFieldCheck);

if(!myrec->CanUpdate())
{
MessageBox("Database reports unable to update.", "Database Error", MB_ICONEXCLAMATION);
myrec->Close();
return;
}

if(!set.Open(CRecordset::dynaset,"SELECT * From e",CRecordset::noDirtyFieldCheck))
{
MessageBox("Unable to open database record. (SELECT * e)", "Database Error", MB_ICONEXCLAMATION);
set.Close();
return;
}
/*if (!set.CanAppend())
{
MessageBox("Database does not support adding records. Please\ncontact your server administrator.", "Database Error", MB_ICONEXCLAMATION);
set.Close();

}*/
set.MoveFirst();
while( !set.IsEOF() )
{
set.MoveNext();
}
int lRecCnt = set.GetRecordCount();
int newID=lRecCnt+2;

m_g=lRecCnt;
UpdateData(FALSE);

// Prepare for a new record addition.
try
{
set.MoveLast();
set.AddNew();
set.m_s = "Becky Dugan";
set.m_p = "Becky Dugan";

set.Requery();
set.SetFieldDirty(NULL);
set.Update();

}
catch(CException* pE)
{

char buf[256];
pE->GetErrorMessage(buf, 256, NULL);
AfxMessageBox(buf);
pE->Delete();
}

// Close Recordset
set.Close();
// Close Database
mydb->Close();

Re: ODBC ms access insertion problem via mfc

Vaclav_25-May-08 7:54

Vaclav_

25-May-08 7:54

I have a similar problem, but I think you should not use Requery.
I tryied it in my code and it wiped out the data added to record created by AddNew.
Vaclav

Re: ODBC ms access insertion problem via mfc

elegantcharm27-May-08 9:21

elegantcharm

27-May-08 9:21

hey thr...its not working even if i eliminate the Requery statment...anything else ????

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005

mr.mohsen23-May-08 21:17

mr.mohsen

23-May-08 21:17

hi friends
what is this error?
i have not sqlserver 2005, i just install sqlserver 2000 but in the error text you see a text about failding to connect to sqlserver 2005?????
what happen for my server or program?
is there anybody to tell me what happen?

nobody help you...
you have to help you yourself
and this is success way.

System perfomance get down when i runthe local report through reporting services

bhushi998123-May-08 20:20

bhushi9981

23-May-08 20:20

Hello,

When i run the report through my application, at that time system perfomance get down(memory usgae get down).

Please help me to sort out this issue.

-Bhushan

Bhushan Kukade

SQL query

Kartik Rathi23-May-08 20:11

Kartik Rathi

23-May-08 20:11

I am writing this query

select r.csno,CM.course_name,CTM.cat_name+','+r.sex+','+
(case when r.subcat_code='02'

then SCTM.subcat_name + '-' + c.ESM_Priority else SCTM.subcat_name end) as category,
max(r.rank) as maxrank
from registration r,
institute_master IM,course_master CM,category_master CTM,subcategory_master SCTM ,
candidate c
where r.inst_code!='000' and im.inst_code=r.inst_code
and c.roll_no=r.roll_no and c.subcat_code=r.subcat_code
and cm.course_code=r.course_code and ctm.cat_code=r.cat_code and
sctm.subcat_code=r.subcat_code
--and r.csno=@csno and r.inst_code=@inst_code
group by r.csno,r.inst_code,r.course_code,r.cat_code,r.subcat_code,r.sex,
CM.course_name,CTM.cat_name,SCTM.subcat_name,c.ESM_Priority
order by r.course_code,r.cat_code,r.subcat_code,r.sex

and the result of this query is
something like this

01 Computer Science & Engg. AIC,F,Others 33039.0
01 Computer Science & Engg. AIC,F,Others 11302.0
01 Computer Science & Engg. AIC,F,Others 35305.0
01 Computer Science & Engg. AIC,F,Others 7679.0
01 Computer Science & Engg. AIC,F,Others 63768.0
01 Computer Science & Engg. AIC,F,Others 41363.0
01 Computer Science & Engg. AIC,F,Others 58615.0
01 Computer Science & Engg. AIC,F,Others 56984.0
01 Computer Science & Engg. AIC,F,Others 66286.0
01 Computer Science & Engg. AIC,F,Others 22362.0
01 Computer Science & Engg. AIC,F,Others 43684.0
01 Computer Science & Engg. AIC,F,Others 88502.0
01 Computer Science & Engg. AIC,F,Others 75349.0
01 Computer Science & Engg. AIC,F,Others 79841.0
01 Computer Science & Engg. AIC,F,Others 78248.0
01 Computer Science & Engg. AIC,F,Others 53999.0
01 Computer Science & Engg. AIC,F,Others 17456.0
01 Computer Science & Engg. AIC,F,Others 100834.0
01 Computer Science & Engg. AIC,F,Others 93685.0
01 Computer Science & Engg. AIC,F,Others 31434.0
01 Computer Science & Engg. AIC,F,Others 30982.0
01 Computer Science & Engg. AIC,F,Others 64501.0
01 Computer Science & Engg. AIC,F,Others 38245.0
01 Computer Science & Engg. AIC,F,Others 44901.0
01 Computer Science & Engg. AIC,F,Others 75336.0
01 Computer Science & Engg. AIC,F,Others 95223.0
01 Computer Science & Engg. AIC,F,Others 79918.0
01 Computer Science & Engg. AIC,F,Others 22798.0
01 Computer Science & Engg. AIC,F,Others 59257.0
01 Computer Science & Engg. AIC,F,Others 69944.0
01 Computer Science & Engg. AIC,F,Others 31593.0
01 Computer Science & Engg. AIC,F,Others 89065.0
01 Computer Science & Engg. AIC,F,Others 41946.0
01 Computer Science & Engg. AIC,F,Others 55956.0
01 Computer Science & Engg. AIC,F,Others 62812.0
01 Computer Science & Engg. AIC,F,Others 105264.0
01 Computer Science & Engg. AIC,F,Others 58567.0
01 Computer Science & Engg. AIC,F,Others 39512.0
01 Computer Science & Engg. AIC,F,Others 88404.0
01 Computer Science & Engg. AIC,F,Others 29413.0
01 Computer Science & Engg. AIC,F,Others 110726.0
01 Computer Science & Engg. AIC,F,Others 29939.0
01 Computer Science & Engg. AIC,F,Others 101460.0
01 Computer Science & Engg. AIC,F,Others 63403.0
01 Computer Science & Engg. AIC,F,Others 79171.0
01 Computer Science & Engg. AIC,F,Others 120370.0
01 Computer Science & Engg. AIC,F,Others 106321.0
01 Computer Science & Engg. AIC,F,Others 100908.0
01 Computer Science & Engg. AIC,F,Others 131467.0
01 Computer Science & Engg. AIC,F,Others 115509.0
01 Computer Science & Engg. AIC,F,Others 103949.0
01 Computer Science & Engg. AIC,F,Others 95519.0
01 Computer Science & Engg. AIC,F,Others 123022.0
01 Computer Science & Engg. AIC,F,Others 136771.0
01 Computer Science & Engg. AIC,F,Others 112522.0
01 Computer Science & Engg. AIC,F,Others 211598.0
02 Computer Science & Engg. AIC,F,Others 50449.0
02 Computer Science & Engg. AIC,F,Others 18078.0
02 Computer Science & Engg. AIC,F,Others 74740.0
02 Computer Science & Engg. AIC,F,Others 12867.0
02 Computer Science & Engg. AIC,F,Others 54947.0
02 Computer Science & Engg. AIC,F,Others 124523.0
02 Computer Science & Engg. AIC,F,Others 86040.0
02 Computer Science & Engg. AIC,F,Others 81094.0
02 Computer Science & Engg. AIC,F,Others 32750.0
02 Computer Science & Engg. AIC,F,Others 46014.0
02 Computer Science & Engg. AIC,F,Others 135872.0
02 Computer Science & Engg. AIC,F,Others 104810.0
02 Computer Science & Engg. AIC,F,Others 134683.0
02 Computer Science & Engg. AIC,F,Others 125076.0
02 Computer Science & Engg. AIC,F,Others 85418.0
02 Computer Science & Engg. AIC,F,Others 20628.0
02 Computer Science & Engg. AIC,F,Others 136908.0
02 Computer Science & Engg. AIC,F,Others 157406.0
02 Computer Science & Engg. AIC,F,Others 32246.0
02 Computer Science & Engg. AIC,F,Others 47138.0
02 Computer Science & Engg. AIC,F,Others 74428.0
02 Computer Science & Engg. AIC,F,Others 42476.0
02 Computer Science & Engg. AIC,F,Others 71440.0
02 Computer Science & Engg. AIC,F,Others 121619.0
02 Computer Science & Engg. AIC,F,Others 161855.0
02 Computer Science & Engg. AIC,F,Others 133762.0
02 Computer Science & Engg. AIC,F,Others 34453.0
02 Computer Science & Engg. AIC,F,Others 67582.0
02 Computer Science & Engg. AIC,F,Others 106875.0
02 Computer Science & Engg. AIC,F,Others 99424.0
02 Computer Science & Engg. AIC,F,Others 67169.0
02 Computer Science & Engg. AIC,F,Others 72990.0
02 Computer Science & Engg. AIC,F,Others 121548.0
02 Computer Science & Engg. AIC,F,Others 134975.0
02 Computer Science & Engg. AIC,F,Others 81303.0
02 Computer Science & Engg. AIC,F,Others 82506.0
02 Computer Science & Engg. AIC,F,Others 116105.0
02 Computer Science & Engg. AIC,F,Others 40175.0
02 Computer Science & Engg. AIC,F,Others 145820.0
02 Computer Science & Engg. AIC,F,Others 85772.0
02 Computer Science & Engg. AIC,F,Others 127614.0
02 Computer Science & Engg. AIC,F,Others 95483.0
02 Computer Science & Engg. AIC,F,Others 77450.0
02 Computer Science & Engg. AIC,F,Others 159026.0
02 Computer Science & Engg. AIC,F,Others 130038.0
02 Computer Science & Engg. AIC,F,Others 118117.0
02 Computer Science & Engg. AIC,F,Others 140501.0
02 Computer Science & Engg. AIC,F,Others 138995.0
02 Computer Science & Engg. AIC,F,Others 119339.0
02 Computer Science & Engg. AIC,F,Others 137108.0
02 Computer Science & Engg. AIC,F,Others 152161.0
02 Computer Science & Engg. AIC,F,Others 160658.0
02 Computer Science & Engg. AIC,F,Others 139247.0
02 Computer Science & Engg. AIC,F,Others 151151.0
01 Computer Science & Engg. AIC,M,Others 26444.0

Now i want to get rid of course name.Like for 01, Computer Science & Engg. is showing multiple times.I want to show Computer Science & Engg. only one time for 01.

Kindly help me as early as possible.

Last Visit: 31-Dec-99 18:00 Last Update: 8-May-24 3:59

Refresh

ᐊ Prev 1...1161 1162 1163 116411651166 1167 1168 1169 1170 Next ᐅ

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.