|
I have a WebForm that will display paging data to customers.My purpose is
to let the customers can select any page number.The WebForm calls a stored
procedure in SQL2000:
ALTER PROCEDURE MyProc<br />
(@RecordPerPage int,<br />
@PageNumber int<br />
)<br />
AS<br />
select top @RecordPerPage *<br />
from MyTable<br />
where KeyField><br />
select Max(KeyField) from <br />
(select top @RecordPerPage*(@PageNumber-1) KeyField<br />
from MyTable<br />
order by KeyField) a<br />
order by KeyField
Unfortunately,this procedure has a syntax error.It seems that the 'TOP'
keyword can not be followed by an Expression.
How could I do?
I do not want to dynamicaly generate SQL string in client side(there are a dozen of reasons) and --
This is a large table,I will not use DataAdapter.Fill and DataGrid.AllowPaging.
Need your help!
Thanks a lot!
|
|
|
|
|
You should build your SQL statement as a nvarchar and then use sp_executesql:
<br />
DECLARE @query NVARCHAR(500)<br />
<br />
SET @query = N'select top ' + CAST(@RecordPerPage AS NVARCHAR(5)) + N'* from MyTable...<br />
<br />
sp_executesql @query <br />
look at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ea-ez_2h7w.asp
--> Nick
|
|
|
|
|
 hi
I am having problem in procedure i am getting error as 'Scope_Identity' is not a recognized function name.I read an article that @@IDENTITY should be used is it helpful.The procedure is for the inserting the details.
thanks
kal2na2
|
|
|
|
|
Sounds like you're using SQL Server v7 which does not support SCOPE_IDENTITY() - you must use @@identity however be careful, triggers will alter @@identity to reflect the result of the trigger and not your proc.
--> Nick
|
|
|
|
|
In queries I use the function, NVL quite a bit.
,nvl(some_column,'DefaultText')
Whenever some_column has a NULL value, the query always returns DefaultText for the column's value. Is there a smilar function for a varchar2 type column that would allow me to obtain DefaultText if some_column contained a string of all space characters of any length. I'm trying to develop a work around to a bug with a library that treats a column with all space characters as an empty string, regardless of how many space characters have been stored.
Thanks.
Chris Meech
I am Canadian. [heard in a local bar]
Remember that in Texas, Gun Control is hitting what you aim at. [Richard Stringer]
Nice sig! [Tim Deveaux on Matt Newman's sig with a quote from me]
|
|
|
|
|
We might end up with a large whack of code in stored procedures in our SQL2005 database and I was wondering if anyone knew of a code-coverage tool that could work against SPs? The SPs will be T-SQL and also use CLR.
I haven't looked into it that deeply but I am assuming the code-coverage tool in VS2005 can't do this.
regards,
Paul Watson
Ireland
Colib and ilikecameras.
K(arl) wrote:
oh, and BTW, CHRISTIAN ISN'T A PARADOX, HE IS A TASMANIAN!
|
|
|
|
|
Excuse my ignorance, Paul, but what is a code-coverage tool? Are you looking for version control, or a SQL editor, or some other animal?
Chris Meech
I am Canadian. [heard in a local bar]
Remember that in Texas, Gun Control is hitting what you aim at. [Richard Stringer]
Nice sig! [Tim Deveaux on Matt Newman's sig with a quote from me]
|
|
|
|
|
OK let me see if I can explain this without looking it up again 
Code coverage presents metrics on how much of your code is covered by tests (e.g. unit tests.) The idea is that with a large code base you can't be sure the unit tests you have are testing all aspects of the code. So you run the code coverage tool against your code, including your tests, and it returns metrics on what is and what is not covered.
Within that you get all sorts of variations such as block coverage, conditional coverage, line coverage, method coverage etc.
I currently only know of code coverage tools that work against code in assemblies, not in SQL databases.
regards,
Paul Watson
Ireland
Colib and ilikecameras.
K(arl) wrote:
oh, and BTW, CHRISTIAN ISN'T A PARADOX, HE IS A TASMANIAN!
|
|
|
|
|
Hi all:
I'm pretty new at db programming, but over the past year have a come to a decent understanding of connections, data adapters, and datasets. Here's something though that I just can't think through.
Here's what I'd like to do.
1. My music collection and audio file information are in a MSDE database with several tables containing workid (PK), artist information, style, filename (location of the sound file), etc.
2. I created XML file to use as a playlist, something like
<playlist>
<workid>2</workid>
<workid>15</workid>
<workid>4</workid>
<workid>47</workid>
</playlist>
3. I saved the file something like saturdaynightmusic.xml
4. Using the Build Schema tool in VS Studio, I created a schema that joins the Music table with the XML playlist with workid as the key.
5. Now I have a .xsd file which contains the relationship, an .xml file to control the order of play, and a database table with fields pointing to the audio files, album and artist info.
However, I can't for the life of me, figure out how to make them work together.
Conceptually, I've got it - Read each element in the XML file, put them into a list (such as a list box), select first song in the list, it populates the fields with the artist info, title, then plays, the next song is selected, played .... until the end.
Here's how I think it could be done...
1. The XML playlist workid is stored as a variable.
2. The variable is added to a select command which is then run against a view or stored procedure
3. The database returns the information which is bound to textboxes, lists, and the filename variable to play the audio file.
4. Loop through the above steps until the end of the list
Am I on the right track???
Thanks for any help in clarifying this for me.
Norm
-- modified at 17:58 Tuesday 15th November, 2005
|
|
|
|
|
Hi,
I have two objects, both point to each other in OO.
e.g.
Class Father
{
private int id; //id of father
private Son son;
}
Class Son
{
private int id; //id of Son
private Father father;
}
so I need to have two tables in database as follow,
Table Father :
(id, SonId)
Table Son :
(id, FatherId)
The problem is, should I eliminate the one of the reference in one of the table, e.g.
Table Father :
(id)
Table Son :
(id, FatherId)
so I just have the fatherID in the son table.
But if I remove the sonID from the father table,
I need to join the two tables when I retrieve fathers
from the father table.
So what are the pros and cons of the two schema design ?
Thanks
|
|
|
|
|
Analyze a bit...
Without going into much trouble.. the relationship between father and son tables is as follows: A Father can have 1 or more sons, but a Son can only have a father at any one time. If you follow this, then only having idFather in the son table it's more than enough to make the searches you need.
Anything else you need to add?
daniero
|
|
|
|
|
daniero wrote: Father can have 1 or more sons
i would say so, but looking at his class definitions, a father have only one child
ppp001 wrote :
Class Father {
private int id;
private Son son; <-- see what i mean
}
Class Son {
private int id;
private Father father;
}
maybe a modeling mistake, but he wrote it !
TOXCCT >>> GEII power
[toxcct][VisualCalc]
-- modified at 11:29 Wednesday 16th November, 2005
|
|
|
|
|
i would like to know how that i can do something like this:
this is my query: select a.col2, a.col3, b.col2 from tableA a
INNER JOIN tableB
On a.col1 = b.col1
now if i use a fill from a data adapter i get a dataset with one table with my results.
what i want is a dataset: with 2 tables one with the nfo from table a, and one with the info from table b
how can i do this? or isn't this possible?
|
|
|
|
|
Yes, query tablea, and fill a 'tableA' in a dataset, and then query tableb, and fill a 'tableB' in the dataset, you can have a preset dataset with the relationship or generate it in code.
daniero
|
|
|
|
|
how do you mean a preset dataset with relationship?
the thing i want to avoid is: query table a and fill it in the dataset, than query table b and fill the same dataset, and then put the relationships on it.
I want to do this with one query to the db, is this possible? to execute your query, so your adapter will fill your dataset with different tables and relationships.
|
|
|
|
|
Unless someone else has a better Idea, that's the way I would do it.
By preset I mean an xsd file representing a dataset (I think it's called typed dataset).
I don't think you can have 2 tables out of one query, unless you make the query as you did, and manually adding the rows in the datatables... but that's even worse :P.
daniero
-- modified at 18:21 Tuesday 15th November, 2005
|
|
|
|
|
Hi.
I'm running SQL Server 2000.
I have a little problem, and I do not know how to solve it. I have a News tables, a Category table, and a Publisher table. When inserting a new news article, the user can select the news category and the news publisher from drop downlists. A category is required, but not a publisher. So if no publisher is selected, then I make this foreign key in the News table NULL.
My problem comes in when I want to retrieve a news article that has a publisher ID of NULL. The reason for this is because it goes and looks for an ID of NULL in the publishers table and finds none. But what I actually want is for it to bring back the complete news article, and make put and empty string in publisher name, and 0 in publisher ID, or something like this. My SQL statement in my stored procedure looks like this:
SELECT
PKNewsID,
FKNewsCategoryID,
LinkTitle,
NewsTitle,
NewsBodyCopy,
NewsDatePublished,
Publish,
FKNewsPublisherID,
NewsPublisher,
NewsCategory
FROM News
INNER JOIN NewsPublisher ON News.FKNewsPublisherID = NewsPublisher.PKNewsPublisherID
INNER JOIN NewsCategory ON News.FKNewsCategoryID = NewsCategory.PKNewsCategoryID
WHERE PKNewsID = @NewsID;
Please can someone help me with this.
Regards,
ma se 
|
|
|
|
|
Then an INNER JOIN isn't going to work as it requires that all rows exist.
Change this line:
INNER JOIN NewsPublisher ON News.FKNewsPublisherID = NewsPublisher.PKNewsPublisherID to
LEFT OUTER JOIN NewsPublisher ON News.FKNewsPublisherID = NewsPublisher.PKNewsPublisherID
You will get nulls in the columns that came from NewsPublisher when there is no join.
Does this help?
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
how do i connect to a database and pick elements from it at random to fill and array..
first i need to know how to connect to it in the first place and be able to read from it.. please your quick help is needed..
thanks in advance
Nab
|
|
|
|
|
This thing is very strange.I have been using the following connection string all these days
data source=server;initial catalog=NIA;password=tpassword;persist security info=True;user id=sa
But now all of my .net application is not getting connected to sqlserver
[SqlException: SQL Server does not exist or access denied.]
System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) +472
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) +372
System.Data.SqlClient.SqlConnection.Open() +384
NewNia.WebForm2.Page_Load(Object sender, EventArgs e) in C:\Inetpub\wwwroot\NewNia\WebForm2.aspx.vb:40
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +731
Though i can acces my server through query analyzer.Please help
|
|
|
|
|
since your datasource is "server" I assume it's a different machine?
if that's the case, have you recently installed SP2 or SP1 (in Server 2003)?
They change the security model and as a result you won't be able to connect to sql server over a network connection unless you tweak some settings. Search for SP2 in Microsoft and then SQL Server and you will find a work around.
If it's not SP 2, then there is some problem with your connection, or SQL server is not running, or the database NIA is not started, there are a number of reasons why this can happen.
|
|
|
|
|
Hello All,
Whenever I try to execute a Query in Analyzer, I get the below error.
Server: Msg 8623, Level 16, State 1, Line 1
Internal Query Processor Error: The query processor could not produce a query plan. Contact your primary support provider for more information.
I went Microsoft support site where they have mentioned that the above issue is fixed in service pack 4. Even after downloading the service pack 4, I receive the same error. I have been held up since a week because of this issue.
Please someone get me out of this situation.
For your information, I am using SQL Server 2000 with SP4.
Thanks a lot in advance
Looney
|
|
|
|
|
i want to know the differences betwween ado and ado.net
|
|
|
|
|
i think (but i'm not sure) that ADO.NET is designed for .net languages such as C#, VB.NET, MC++, and so...
TOXCCT >>> GEII power
[toxcct][VisualCalc]
|
|
|
|
|
hi guys..
can anyone tell me what might be the error at the following code ?
public void getCounterValue(string countername,string tablename,string email)
{
SqlConnection conn = null;
SqlDataReader reader = null;
int result = 0;
try
{
conn = new SqlConnection("Server=(local);Integrated security=SSPI;database=halkdanis");
conn.Open();
SqlCommand cmd = new SqlCommand("select @ctr from @table where @mail = @mail", conn);
SqlParameter param = new SqlParameter();
param.ParameterName = "@ctr";
param.Value = countername;
SqlParameter param2 = new SqlParameter();
param2.ParameterName = "@table";
param2.Value = tablename;
SqlParameter param3 = new SqlParameter();
param3.ParameterName = "@mail";
param3.Value = email;
cmd.Parameters.Add(param);
cmd.Parameters.Add(param2);
cmd.Parameters.Add(param3);
reader = cmd.ExecuteReader();
while(reader.Read())
{
result = Convert.ToInt32(reader[0]);
}
}
catch(Exception x)
{
MessageBox.Show(x.ToString());
}
finally
{
reader.Close();
conn.Close();
}
}
it throws the exception @table doesn`t have a value,
but it has the value tablename from the method construction..
any thougts ?
thx !
good coding !
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
