Re: Urgent - Database Discussion Boards

Re: getting RecordCount = -1

Christian Graus30-Mar-07 3:55

Christian Graus

30-Mar-07 3:55

What happens if you take those parameters, and call the stored proc within SQL Server ?

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 )

Re: getting RecordCount = -1

Ankur.Bakliwal30-Mar-07 3:59

Ankur.Bakliwal

30-Mar-07 3:59

In Sql Server with same parameter that SP gives me 2 records as result.

Thanks,
Ankur Bakliwal

Re: getting RecordCount = -1

Christian Graus30-Mar-07 4:03

Christian Graus

30-Mar-07 4:03

Then there must be a problem with the code. Not sure what, without seeing the proc.

Re: getting RecordCount = -1

roel_v31-Mar-07 2:45

roel_v

31-Mar-07 2:45

I found this on MSDN:

The cursor type of the Recordset object affects whether the number of records can be determined. The RecordCount property will return -1 for a forward-only cursor; the actual count for a static or keyset cursor; and either -1 or the actual count for a dynamic cursor, depending on the data source.

You are using CursorType = 3, which corresponds to a static cursor, so it should return. However, it also says:

The property returns -1 when ADO cannot determine the number of records or if the provider or cursor type does not support RecordCount.

What provider are you using?

In general, the safest solution is to just include a count(*) in your stored procedure and return that as an output parameter.

Let me know if this works for you.

Roel

Distribution of software

drc_no130-Mar-07 3:41

drc_no1

30-Mar-07 3:41

Hi!
I'm rather new to programming db, so... sorry if this may sound stupid.
I am using C# to create a program which does some operations with a database (basically, the project helps me better get along with databases). The .mdf file and .ldf file reside in C:\DATA\ . And on my computer, things go along smoothly.
Still, when i tried to distribute the application to a friend, i realized that i haven't build the application for anybody but me because he doesn't have the .mdf. So, he installed sql server (2000, just like mine), has put the files in C:\DATA, but he still gets an error. I know the error comes from the fact that the database is not attached to his server (asked a few friends) and i know that i must use sp_attach_db function.
The problem is that i don't know whether each time the program is run i should run this function (because if you run the program 2 times i think it gets an error) and, more important, i don't know how to really implement this function (haven't found any tutorial yet).
Another important thing is the connection string to the database. On my computer it is:

connectionString="Data Source=DRC;Initial Catalog=Consulting_co;Integrated Security=True"             providerName="System.Data.SqlClient"<br />

//from app.config
as far as i know, datasource indicates the name of the sql server and this name must modify when the program is run from another place.
Please, help!

Re: Distribution of software

Colin Angus Mackay30-Mar-07 4:32

Colin Angus Mackay

30-Mar-07 4:32

drc_no1 wrote:
the connection string to the database. On my computer it is:
connectionString="Data Source=DRC;Initial Catalog=Consulting_co;Integrated Security=True" providerName="System.Data.SqlClient"
//from app.config
as far as i know, datasource indicates the name of the sql server and this name must modify when the program is run from another place.

Well, you only need to modify it is the database is in another place. In an organisation setting each computer will point to the same SQL Server on the network.

In your case you might want to just change Data Source=(local) instead. Specifying the server as (local), including the brackets, just means the default instance of SQL Server on this machine.

Back to attaching and detaching the database. I tend not to do this. Usually I take a backup and then restore the backup on the target machine. The other thing you have to watch out for is user accounts. Logins are stored in the master database, the user permissions are stored on a per-database level. Off the top of my head I don't recall how to match them up again (if they get mismatched), but it is something you will have to consider.

Upcoming events:
* Edinburgh: Web Security Conference Day for Windows Developers (12th April)
* Glasgow: Introduction to AJAX (2nd May), SQL Server, Mock Objects

My website

Urgent

Test27030730-Mar-07 2:51

Test270307

30-Mar-07 2:51

Hello friends,
I have a table..suppose having 3 columns.(Rate,FromQty,ToQty)
Now the Table have data on this format:

Rate FromQty ToQty
100 0 5
200 6 10
300 11 15

Now see for a particular product 0 to 5 have Rs.100.
Then 6 to 10 rate is Rs.200. and 11 to 15 is 300.

How can i get the rate of 2 qty.(i.e Rs.100 (0-5))

Pls can anybody send me the proper select statement..
thanks.

Re: Urgent

Christian Graus30-Mar-07 3:06

Christian Graus

30-Mar-07 3:06

select rate where FromQty >= 0 and ToQty <= 5 ?

I'm not sure that I understand the question.

Re: Urgent

Test27030730-Mar-07 3:44

Test270307

30-Mar-07 3:44

No. i can enter any value through store procedure.it may be 2/8/13/19.

suppose i enter 2 then the row will be 100 0 5.
if i enter 8 then the row will be 200 6 10.
if i enter 13 then the row will be 300 11 15.
like this.

now if i generate this statement "select * from mratedetail where productcategoryid=49 and (toqty<=14.0 or fromqty>=14.0)" i have got all rows.
but in case of i want just "300 11 15".

thanks

Re: Urgent

Christian Graus30-Mar-07 4:00

Christian Graus

30-Mar-07 4:00

Test270307 wrote:
(toqty<=14.0 or fromqty>=14.0

That obviously won't work. I think if you use AND instead of OR, it may well work.

Re: Urgent

Test27030730-Mar-07 4:02

Test270307

30-Mar-07 4:02

I have many fields and many rows.
so if i put or then it return so many records.
doesn't work for "or".
thanks

Re: Urgent

Christian Graus30-Mar-07 4:04

Christian Graus

30-Mar-07 4:04

Test270307 wrote:
doesn't work for "or".

That's why I said you need to use AND

Re: Urgent

Christian Graus30-Mar-07 4:04

Christian Graus

30-Mar-07 4:04

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

Re: Urgent

DQNOK30-Mar-07 4:09

DQNOK

30-Mar-07 4:09

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.

Re: Urgent

Test27030730-Mar-07 18:40

Test270307

30-Mar-07 18:40

thank u.it works.

Re: Urgent

Noman Aftab31-Mar-07 1:01

Noman Aftab

31-Mar-07 1:01

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

Pakistani Memon
Prime Minister
Post Meridian

server : error 18025

Navneet Hegde30-Mar-07 2:47

Navneet Hegde

30-Mar-07 2:47

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

Develop2Program & Program2Develop

Re: server : error 18025

kubben30-Mar-07 3:45

kubben

30-Mar-07 3:45

Have you set up your mail profile?

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

Hope that helps.
Ben

Re: server : error 18025

Navneet Hegde30-Mar-07 3:47

Navneet Hegde

30-Mar-07 3:47

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

Develop2Program & Program2Develop

Re: server : error 18025

kubben30-Mar-07 3:54

kubben

30-Mar-07 3:54

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

Ben

Re: server : error 18025

kubben30-Mar-07 3:56

kubben

30-Mar-07 3:56

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

Re: server : error 18025

Navneet Hegde30-Mar-07 3:58

Navneet Hegde

30-Mar-07 3:58

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

Develop2Program & Program2Develop

selecting primary key from grid view

govindkedia30-Mar-07 1:07

govindkedia

30-Mar-07 1:07

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...

Re: selecting primary key from grid view

gauthee30-Mar-07 1:47

gauthee

30-Mar-07 1:47

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

Gautham

Re: selecting primary key from grid view

Tirthadip30-Mar-07 1:54

Tirthadip

30-Mar-07 1:54

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

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