Strange difference between SQL from .Net and SQL within stored proc

Hi, I am trying to fill a DataGrid with random records from my database. It thought I had found a way to randomly select records using SQL. So I built a stored procedure in Access and sure enough, it returns a random set of records.

Whe I tried to call this from my app, it kept returning the same set of records. So I copied and pasted the SQL into the app (code below) but it made no difference. Could anyone tell me why this wouldn't work?

<%@ Page Language="VB" ContentType="text/html" ResponseEncoding="iso-8859-1" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDB" %>

<script runat="server">
Public objConn as new OleDbConnection (System.Configuration.ConfigurationSettings.AppSettings("ConnectionString"))

Sub GetTeam(s as Object, E as EventArgs)
 
 Dim objCmd As New OleDbCommand("SELECT TOP 4 PlayerID FROM tblPlayers WHERE PositionID='defender' ORDER BY rnd(isnull(PlayerID)*0+1);", objConn)
     
 Try
   objConn.Open()
   dgDef.DataSource = objCmd.ExecuteReader
   dgDef.DataBind()
   objConn.Close()
 Catch ex as OleDbException
   throw ex
 End try

End Sub
</script>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="
http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
<style type="text/css">
<!--
.style1 {font-weight: bold}
-->
</style>
</head>
<body>
<form runat="server">
  <p>
    <asp:Button ID="btn33" runat="server" OnClick="GetTeam" Text="Get Defenders" />
  </p>
  <table width="100%" border="0" cellspacing="10" cellpadding="0">
    <tr>
      <td></td>
        Defenders
          <asp:DataGrid runat="server" ID="dgDef" EnableViewState="false" />       
    </tr>
  </table>
  <p>&nbsp;</p>
</form>
</body>
</html>

0
shauns1
4/18/2006 11:36:33 AM
asp.net.object-datasource 16182 articles. 0 followers. Follow

5 Replies
745 Views

Similar Articles

[PageSpeed] 57

Have you tried: "SELECT TOP 4 PlayerID FROM tblPlayers WHERE PositionID='defender' ORDER BY newid()".

This seems to work pretty good and I have found sites on the web that discuss using newid() versus rnd()Idea [Idea] (Such as MSDN).

0
ITphoenix
4/18/2006 3:37:42 PM

Yeah, I had seen the NewID option in my web searches on the problem - tried it but came up with the following error:

Undefined function 'newid' in expression.

Any ideas where to go from there?

Thanks

Shaun

0
shauns1
4/18/2006 6:55:01 PM

Are you using an Access Project? If so, what was your query this time?  What version of SQL Server are you using? OS?

0
ITphoenix
4/19/2006 1:13:51 PM

Sorry, that is the whole query.

 

I'm just using Access as my DB for my ASP.Net website. I build what Access calls 'Queries' and use them like Stored Procedures from my .Net pages.

OS WinXP Pro

Access 2000

0
shauns1
4/19/2006 7:35:41 PM

Well,  Access as a data store is a whole new ballgame and not my cup of tea.  I use it only as a thin client.  I'm sorry. I was confused by 'Stored Procedures'.  That makes newId() useless.

Try looking here http://www.fontstuff.com/vba/vbatut02.htm  This seems a bit ridiculous, but so is everything else in Access (For someone familiar with SQL2000)

My guess is that you are not setting the seed value for the random function.  This would casue the same order every time you run the query (computers compute a random number.  Therefore, if the seed is the same, you will get the same 'random' output every time!)  Try seeing if you can set the seed value.

Hope this helps.

0
ITphoenix
4/19/2006 8:24:40 PM
Reply:

Similar Artilces:

SQL Server Reporting Services for SQL SERVER 2000 AND Visual Studio.NET 2005 .NET 2.0
Hi, Which version of SQl Server Reporting Services will work with SQL SERVER 2000 in pararrel with ASP.NET 2.0 (.NET 2.0 framework)?  Thanks, Azam HighOnCodingWanna get high! Hi, Okay I got the answer. Yes, we can use the SQL SERVER 2005 Reporting Services with SQL SERVER 2000. HighOnCodingWanna get high! ...

How to check if SQL Stored Proc was modified using C#.NET
Hi, I'm writing a code in C#.NET to minimize the sql server round trips, to get the sql parameters and set the values. The problem is I need to check if the stored procedures we're modified or not using C#.NET. That way I dont have to retrieve the parameters everytime my app needs to update certain data. So that the sql parameters are already on a cache whenever needed. The sql parameters in cache will be update whenever the sql stored procedures were modified. I just need a code to check if the sql store procedure was modified based from the last modified dat...

Problem accessing SQL Stored Procedure From .Net using hibernate object
Hi, I am calling a SQL Stored procedure from .net application using Hibernate object, but somehow its telling me "The 'callable' attribute is not declared.", I am not sure what's wrong I have done here. Will really appreciate if someone can help me for this. Herewith I am providing my Stored Procedure, my .hbm file and code to call it. can someone pls tell me what's wrong in this? Also, if possible, pls tell me how to set result in a list object. Thanks in advance, ######################################### MY STORED PROCEDURE : ALTER PROCEDURE [dbo].[RulesDetails_...

A .NET Framework error occurred during execution of user-defined routine or aggregate -While creating a SQL SERVER 2005 Stored prodecure in VS.NET 2005
 Running [dbo].[insertlogin] ( @log = hiten, @pas = hiten ).A .NET Framework error occurred during execution of user-defined routine or aggregate "insertlogin": System.Data.SqlClient.SqlException: Must declare the scalar variable "@Log".System.Data.SqlClient.SqlException:    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)   at System.Data.SqlClient.SqlInternalConnectionSmi.EventSink.Pro...

DW.NET support for SQL Anywhere .NET provider
Hello, we are trying to get DW.NET (v. 2.5) working with Sybase SQL Anywhere .NET provider for database access. Does anybody know if that is possible, and if so, then how? The reason for this question is that we are evaluating DW.NET as a solution for document printing from our existing C# application which uses SQL Anywhere .NET provider. I would expect that these two products should work together, as they are both developed by Sybase and targeted for the same .NET platform. Linas ...

Differences between .net 1, .net 1.1, .net 2.0 and .net 3.0
Hi, This seems to be a common question, but i havent got an answer yet:(Can, any one please explain me the differences between these versions.If you keep your feet firmly on the ground, you'll have trouble putting on your pants! Have a look into this links http://en.wikipedia.org/wiki/.NET_Framework http://blogs.msdn.com/mohammadakif/archive/2006/12/03/net-3-0-different-versions-of-the-net-framework.aspx http://www.codeproject.com/aspnet/ComparisonASP1xASP20.aspAshok Rajawww.iGold.inDon't forget to click "Mark as Answer" on the post that helped you. This credits that m...

Dynamic sql within a stored proc
i am using Adaptive Server Anaywhere 6.03. i am trying to find a way to create a cursor within a Stored Proc based on a 'Where' clause passed to the SP as a parameter. The reason that i need a 'where' clause is that; if i pass parameters for dealling with seperate fields, i have to create many cursors depending on which fields i wish to filter and fields that i don't wish to filter. furthermore, i have to repeat the main part of my SP in order to deal with each and every cursor. while this is workable, it leads to SPs that are ridiculously long and difficult to main...

using stored proc in sql datasource
I've created a simple stored procedure in oracle. because i am still on the learning stage. below is the code create or replace PROCEDURE TEST1 (var_date OUT qa_claim.DATE_EFFECT%Type) IS BEGIN  SELECT to_char(DATE_EFFECT) INTO var_date FROM QA_CLAIM; END;  I have compile it, & there are no errors. but when I use a sqldatasource to a grid, it displays an error ORA-06550: line 1, column 7:PLS-00306: wrong number or types of arguments in call to 'TEST1'ORA-06550: line 1, column 7:PL/SQL: Statement ignored what seems to be the problem?ez The error means ...

Store Existing SQL Database through .Net
  Hi Anyone Knows I want to store SQL 2005 Existing Database into SQL Server Through .Net.if i give Database Name in .Net and that Database automatically stored in SQL Server 2005.P(including that Existing Database Tables and Stored Procedures).Please Help me.   Tamil  Do you mean, create a database from .net code? or maybe restore an existing database from a backup? Sorry, could you clarify.--Roberto Hernández-Pouhttp://www.rhpconsulting.net-----------------------------Don't forget to click "Mark as Answer" on the post that helped you.This credits t...

Differences between .net 1, .net 1.1, .net 2.0 and .net 3.0 #2
Hi, This seems to be a common question, but i havent got an answer yet:(Can, any one please explain me the differences between these versions.If you keep your feet firmly on the ground, you'll have trouble putting on your pants! There are too many differences for one email - - from 1.0 to 1.1 (not a whole lot of real change, other than fixes, at least compared to 1.1 to 2.0) With 2.0, there were many new declarative controls, with many new ideas added in With 3.0, it's a superset of 2.0 - instead of replacing the installation completely, it just 'added on' new functionality - I would...

Attach SQL DB to SQL Server 2005 using VB.Net
Is there any way to attach a SQL DB to SQL Server 2005 using VB.Net?  If so can you also set security? Can you rephrase your question? Attach SQL DB from which version of SQL server ? Look up books online for sp_attachdb and sp_detachdb.***********************Dinakar NethiLife is short. Enjoy it.*********************** I have a DB that was created in SQL Server 2005.  I have a website that displays data from systems all over the world.  Each system sends it's data to it's own DB.  The customer can then go to a website and see the data at real time.  What ...

T-SQL TRIGGER causes crash in .Net, Interactive SQL works...?
Hi All I am fairly new to Sybase databases. I have a database with a single table (AdressenStorage) to that table I added (T-SQL)triggers for INSERT, UPDATE and DELETE. Everything works fine with InteractiveSQL. But as soon as I execute the statements from my .Net application, it crashes. (The program hangs at cmd.ExecuteNonQuery() ). I removed all triggers and everything worked fine. Right now I have just the (reduced) INSERT trigger (which still crashes): CREATE TRIGGER "AdressenStorage_Insert" on DBA.AdressenStorage for insert as begin -- Handle RevIsCurrent ...

Return vals from MS-SQL-Server stored procs in embedded SQL
hi ... how do i get the return value from executing a stored procedure declared in a MS-SQL-Server database within Powerbuilder 5.0.04 embedded SQL statements? the return value i want is *not* the result-set nor an output parameter -- it is the return value specified by [return xxx] within the stored proc in the database. any help is appreciated! thanks, andrew The return value is returned as the first value in the SECOND result set. Any output args are returned, in order, after the return value in the same (second) result set. In embedded SQL, after you execute the ...

Sql Server Script File(.sql) Execution by Vb.net Code
I have a problem. I m working on "light weight sql server"  project.   and i want to execute .sql file through vb.net code with the help of sqldmo library and sqlns namespace. but i donot know any method to directly execute the .sql file. i am successfully making the full script of select database of sql server.   please help me.... thanks.... Hi, You can use SQL-DMO object in your .net projects by referencing Sqldmo.dll  file which is in  C:\Program Files\Microsoft SQL Server\80\Tools\Binn  folder is default installation parameters are used. I...

Web resources about - Strange difference between SQL from .Net and SQL within stored proc - asp.net.object-datasource

Difference - Wikipedia, the free encyclopedia
Text is available under the Creative Commons Attribution-ShareAlike License ;additional terms may apply. By using this site, you agree to the ...

3Q EARNINGS: Mark Zuckerberg on the Differences Between Messenger and WhatsApp
When Facebook announced its intent to acquire cross-platform messaging application WhatsApp in February, questions surfaced on whether WhatsApp ...

Facebook Tips: What’s the Difference between Top News and Most Recent?
The following is part of our series, Facebook Tips, which answers some of the most commonly asked questions about using Facebook. While we hope ...

Cultural differences fade when Westerners and Chinese fall in love
One in three Australian marriages are now intercultural. Researchers have found out a key ingredient for success.

What’s the Difference Between Gen Y and Gen Z?
... in relation to purchasing habits, top brands, or preferred networks, download the report and check out the infographic below for key differences ...

Vive la difference - has France's National Front changed?
France's National Front is different from that founded by Jean Marie Le Pen, but just how much has it changed, asks the BBC's Gabriel Gatehouse. ...

"There’s been a lot of poorly thought-out stuff written about the differences between men’s and women’s ...
... But if you can get past all of this pseudoscience, there’s some legitimately illuminating, serious medical research on sex-based brain differences ...

The 11 most important differences between Macs and PCs
... and Microsoft's rivalry has kicked into high gear — it's even spilled over into the tablet market . We put together some of the key differences ...

No Difference? Really?
Lawyers Guns & Money's Scott Lemieux takes on the notion put forth in Doug Henwood's new book that there's no real difference between Democrats ...

What's the difference in Vance this year? 'He's winning' matchups
... Search form Search 52° Navigation Home Giants A's Sharks Warriors Kings 49ers Raiders Quakes Insiders More Tickets Shop Watch What's the difference ...

Resources last updated: 12/9/2015 3:20:11 AM