Database Discussion Boards

select rate from tbl where FromQty <= @amt and ToQty >= @amt

Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )

SELECT Rate
FROM MyRateTable
WHERE 2 >= FromQty
AND 2 <= ToQty

Or, more generically,

SELECT Rate
FROM MyRateTable
WHERE @qty >= FromQty
AND @qty <= ToQty

Unless the FromQty and ToQty columns are indexed, the db will have to do a full scan to select ALL the rows that meet the @qty >= FromQty, then will have to do another full scan of all the selected rows to determine which of them meet the @qty <= ToQty requirement.

Alternately, if you're using a product that supports the "TOP" keyword, and you have the ToQty column indexed in assending order, you could use

SELECT TOP 1 Rate, ToQty
FROM MyRateTable
WHERE @qty <= ToQty
ORDER BY ToQty

and just ignore the ToQty in the return. This will guarantee good performance as the query will recognize the ORDER BY clause and use the existing index for the select.

Lastly, if your table really is as simple as the data you have used for your example, a mathematical formula would be better.

int getRate( int myQty )
{
if( myQty = 0 ) return 100;
else return (1+((myQty-1)/5))*100;
}

which could also be converted to SQL if you wanted.

Hope this helps.

thank u.it works.

SELECT rate
FROM Table1
WHERE (2 BETWEEN startQty AND endQty)

Pakistani Memon
Prime Minister
Post Meridian

please do help me to send mail from sql server via xp_sendmail

Develop2Program & Program2Develop

Have you set up your mail profile?

Here is a Microsoft link:
http://support.microsoft.com/?id=263556[^]

Hope that helps.
Ben

How can I do that, Please help a bit , for last 5 hours I am wrestling with it

Develop2Program & Program2Develop

Here is a link to a decent example:
http://builder.com.com/5100-6388-6161839-2.html[^]

Ben

Here's another link for I think the mail you are trying to send:
http://articles.techrepublic.com.com/5100-6345_11-5094201.html[^]

Ben

thanks for your prompt reply , I will try those link , Hope i get the result
again thanks a lot

Develop2Program & Program2Develop

HI,

I am stuck in my project as.
i want to select primary key frm the data listed in grid view..so that i can use that key value to actually list the data which are related to tht key in other table...
so could any one tell how can i do tht...

You would have a datasource to ur grid right! that is datatable/dataset for this you can set a primary key.

Gautham

bind your Primary key field to the DataKey of gridview..

and then retreive data with....

Dim primaryField As String<br />
primaryField =myGridview.Datakeys(Seletedrow.index).value.tostring()

Tirtha

Miles to go before I sleep

thanks so much...for the tip..
but tell me one more thing now..
could i use this
"primaryfield" to comapre with detailview which is binded with anotehr dataset in which this primaryfield is a forign key
so will it work

detailview.pageindex = primaryField.to string() ;

or if u could guide me a bit on this..
tht will be very helpful
i am using C#

thanks again

govind

please be specific...I could't understand your problem

Tirtha

Miles to go before I sleep

Hi

select CAST((datediff(day,createdon,getdate())/30) as float) Avg, from customers

I want to give the result in Fraction is it possible? it is not showing the fraction result.
createdon is the datetime field

Any Help is appreciated.

Regards
Shahzad Aslam
Software Engineer

datediff function's date part "day"
returns difference in number of days i.e 1 / 2 / 3 .....

if u r looking for even part of day, get the difference in hours/minutes/ ...
then try converting that to days so that you get the result as you are expecting

Regards
KP

Hi ..
How can I path all rows on the table to change a field .. eg Serial ..
like that

For i=0 to datatabale.Rows.Count-1<br />
.<br />
.<br />
next i

but I want to use any method instead of "Update table set ....."

By using Datatable can I do it "path on all rows to change certain field" by usting the index of the row : DataTable.Rows(i)("Serial")
..
I'm sorry , that my english isn't good ..
but I hope if any one understand what I mean ..

and thanks for my best Forum Rose | [Rose]

jooooo

You can use a cursor to do something similar to a for loop, but it is very slow compared to a single update statement. I would suggest you think seriously about using a single update statement instead.

Ben

Thanks Ben..
but I think on that method :
I want to gove some records a serial >> like 1,2,3 ....
and I can't make that fields Identifier because the serial will be repeated again with another invoice ...
then I'd like to path all selected feilds and give every one it's serial ... from the first to the end ..
then if I user Update statement .. I can't know which record will take No 1 and which will take No 3 ...
I want by the for .. next .. to give all selected row it's serial ..
I know that is strange and there's another method to solve that .. but on my project now , I need this method ...
again .. I hope if any one understand what I mean

jooooo

Well, perhaps you should still look at using an update statement, but then using a scalar function. You could pass in some value you would help the function know what serial number to return. Just a thought.

Ben

I have written a class for operating the SQLCE database with OLE DB, it work perfect on INSERT ,UPTDATE and so on, but when I use 'select' statement to get values from database it doesn’t return values properly even though the GetNextRows method succeeds.

HRESULT hr = NOERROR;
m_pIRowset = NULL;
m_prghRows = &m_rghRows[0];
hr = pICmdText->SetCommandText(DBGUID_SQL, pwszQuery);
if(!FAILED(hr))
{
hr = pICmdText->Execute(NULL, IID_IRowset, NULL, NULL, (IUnknown **)&m_pIRowset);
if (FAILED(hr)){
AfxMessageBox(_T("failed"));
m_pIRowset = NULL;
}
// Get IColumnsInfo interface
hr = m_pIRowset->QueryInterface(IID_IColumnsInfo, (void**)&m_pIColumnsInfo);
if(FAILED(hr)){
AfxMessageBox(_T("failed"));
}
// Get the column metadata m_ulNumCols//m_pDBColumnInfo
hr = m_pIColumnsInfo->GetColumnInfo(&m_ulNumCols, &m_pDBColumnInfo,&m_pStringsBuffer);
if(FAILED(hr) || 0 == m_ulNumCols){
AfxMessageBox(_T("failed"));
}
// Create a DBBINDING array.
m_dwBindingSize = m_ulNumCols;
m_prgBinding = (DBBINDING*)CoTaskMemAlloc(sizeof(DBBINDING)*m_dwBindingSize);
if (NULL == m_prgBinding){
hr = E_OUTOFMEMORY;
AfxMessageBox(_T("Memory allocation failure"));
}
// Set initial offset for binding position
m_dwOffset = 0;
// Prepare structures to create the accessor
UINT lCount;
ULONG dwOrdinal;
for (lCount = 0; lCount< m_ulNumCols; lCount++)
{
dwOrdinal = m_pDBColumnInfo[lCount].iOrdinal;
m_prgBinding[lCount].iOrdinal = dwOrdinal;
m_prgBinding[lCount].dwPart = DBPART_VALUE | DBPART_STATUS | DBPART_LENGTH;
m_prgBinding[lCount].obLength = m_dwOffset;
m_prgBinding[lCount].obStatus = m_prgBinding[lCount].obLength + sizeof(ULONG);
m_prgBinding[lCount].obValue = m_prgBinding[lCount].obStatus + sizeof(DBSTATUS);
m_prgBinding[lCount].wType = m_pDBColumnInfo[lCount].wType;
m_prgBinding[lCount].pTypeInfo = NULL;
m_prgBinding[lCount].pObject = NULL;
m_prgBinding[lCount].pBindExt = NULL;
m_prgBinding[lCount].dwMemOwner = DBMEMOWNER_CLIENTOWNED;
m_prgBinding[lCount].dwFlags = 0;
m_prgBinding[lCount].bPrecision = m_pDBColumnInfo[lCount].bPrecision;
m_prgBinding[lCount].bScale = m_pDBColumnInfo[lCount].bScale;
switch(m_prgBinding[lCount].wType){
case DBTYPE_WSTR:
m_prgBinding[lCount].cbMaxLen = sizeof(WCHAR)*(m_pDBColumnInfo[lCount].ulColumnSize + 1); // Extra buffer for null terminator
break;
default:
m_prgBinding[lCount].cbMaxLen = m_pDBColumnInfo[lCount].ulColumnSize;
break;
}
// Calculate the offset, and properly align it
m_dwOffset = m_prgBinding[lCount].obValue + m_prgBinding[lCount].cbMaxLen;
m_dwOffset = ROUND_UP(m_dwOffset, 8);
}
// Get IAccessor interface
hr = m_pIRowset->QueryInterface(IID_IAccessor, (void**)&m_pIAccessor);
if(FAILED(hr))
{
AfxMessageBox(_T("failed"));
}
// Create the accessor
//
hr = m_pIAccessor->CreateAccessor(DBACCESSOR_ROWDATA, m_dwBindingSize,
m_prgBinding, 0, &m_hAccessor,NULL);
if(FAILED(hr))
{
AfxMessageBox(_T("failed"));
}
// Allocate data buffer.
//
m_pData = (BYTE*)CoTaskMemAlloc(m_dwOffset);
if (NULL == m_pData)
{
hr = E_OUTOFMEMORY;
CString errors;
errors.Format(_T("%d"),m_pDBColumnInfo[2].ulColumnSize);
MessageBox(NULL,errors,NULL,MB_OK);
AfxMessageBox(_T("failed"));
}
// Retrive a row
//
hr = m_pIRowset->GetNextRows(DB_NULL_HCHAPTER, 0, 1, &m_cRowsObtained,&m_prghRows);
if (SUCCEEDED(hr) && hr != DB_S_ENDOFROWSET)
{
// Set data buffer to zero
//
memset(m_pData, 0, m_dwOffset);

// Fetch actual data
hr = m_pIRowset->GetData(m_prghRows[0], m_hAccessor, m_pData);

if (FAILED(hr))
{
m_pIRowset->ReleaseRows(1, m_prghRows, NULL, NULL, NULL);
AfxMessageBox(_T("failed"));
}
}
if (hr == DB_S_ENDOFROWSET)
{
m_bIsEOF = TRUE;
}
}

venkat

how can i let SQL server 2005 accept remote connections

The Surface Area Configurator

Dear All,
I am making web application using Asp.net C#(Visual Studio2005). And Sql server 2000 as a back End upgraded with service pack 3 and analysis service.
Now I am trying to install sql server 2000 report services. But I run SQL2KRSSP1-ENG.EXE setup
Then following error occurs
The upgrade patch can not be installed by window installer because the program to be upgraded may be missing , or the upgrade patch may update a different version of the program Verify that the program to be upgraded exists on your computer and that you have the correct upgrade patch.
I redownload the same exe to other location. And again run but getting same error
Please Guide me or atleast give some help full link.
thanks

regards
imran khan