|
The number of results it will return is dependent on the difference between the start and end values you pass it.
eg: if you pass the stored procedure a start value of 10 and an end value of 25, it will return 16 records (end value - start value + 1)
Are you understanding why the stored procedure is doing what it's doing?
-------------------------------------------
Don't walk in front of me, I may not follow;
Don't walk behind me, I may not lead;
Just bugger off and leave me alone!!
|
|
|
|
|
Hi, please correct me if I'm wrong. I think that @nStartValue and @nEndValues are used as reference points. You Display the file in the temporary table starting with the one whose ID is equal to @nStartValue and stop at the one whose ID is equal to @nEndValue. So if 50 is @nStartValue and @nEndValue is 60, and the total number of files retrieved is 100. Only 10 files will be displayed starting with the one whose ID is 50 and ending with the one whose ID is 60. The rest will be discarded, and if that is the case then it will not solve my problem. Does that mean that if I want to display every file that is retrieved, I would have to know ahead of time how many files will be retrieved by my query? Thank you for your time.
-- modified at 3:19 Thursday 2nd August, 2007
|
|
|
|
|
Yes, your description is fairly accurate. The point of the stored procedure it would seem is to set up a paging system, so that the N records between X and Y are returned. eg: the first page of records might return 1-10, the second page 11-20 etc.
That's pretty much would it would be useful for. What are you trying to achieve?
-------------------------------------------
Don't walk in front of me, I may not follow;
Don't walk behind me, I may not lead;
Just bugger off and leave me alone!!
|
|
|
|
|
cn.ConnectionString = ConfigurationSettings.AppSettings("ConnectionString")
this code for Access how i can transfer it to SQL????????
hi
|
|
|
|
|
1 - write sensible subjects for your posts
2 - this is a ASP.NET/VB.NET question, it doesn't actually relate to SQL.
3 - SQL Server and SQL are two different things. I assume you want to transfer to SQL Server ?
3 - just change your connection string to one that connects to SQL Server, in your web.config file.
Christian Graus - Microsoft MVP - C++
"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 )
|
|
|
|
|
Set the connection string to a SQL connection string in your app.config. http://www.connectionstrings.com[^] is a great site for reference if you haven't already found it 
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
Hi, how do I convert select statement and the connection variable in the following line into a stored procedure.
Dim AD as New OdbcDAtaAdapter("SELECT ResumeID, Resumes From Professions", myConnection)
Thank you in advance for your help.
-- modified at 17:35 Wednesday 1st August, 2007
|
|
|
|
|
CREATE PROCEDURE procname ( parameters ) AS batch of statements
|
|
|
|
|
See the below C# snippet that's itterating over a SqlDataReader:
while (reader.Read())
{
c.ID = reader.GetInt32(0);
c.Category.ID = reader.GetInt32(1);
c.Subject = reader.GetString(2).Trim();
c.Body = reader.GetString(3).Trim();
c.SourceVideoClip = reader.GetString(4).Trim();
c.StillImage = reader.GetString(5).Trim();
c.Notes = reader.GetString(6).Trim();
c.UploadTime = reader.GetDateTime(7);
c.AdID = reader.GetInt32(8);
c.ValidAfter = reader.GetDateTime(9);
c.InvalidAfter = reader.GetDateTime(10);
c.BillingCode = reader.GetInt32(11);
c.Name= reader.GetString(12).Trim();
c.Birth = reader.GetDateTime(13);
c.Category.Name = reader.GetString(14);
}
Everything runs fine until it gets to reader.GetString(4).Trim();, which is null, so it throws an SqlNullValueException exception.
How can I gracefully handle these null values. It seems GetString & GetInt just throw exceptions when they encounter null values. I guess the solution I'm looking for would return null or 0 for a db null value.
How am I supposed to deal with this. Do I have to put if statements around each GetXX to check if it's null?
/\ |_ E X E GG
|
|
|
|
|
I tend to use a set of wrappers to handle these. For instance:
public static string GetValue(IDataReader reader, string columnName)
{
int pos = reader.GetOrdinal(columnName);
if (reader.IsDBNull(pos))
return string.Empty;
return reader.GetString(columnName).Trim();
}This would make your code easier to maintain because you could do:
c.Notes = Utility.GetValue(reader, "Notes");
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Another way to do this is to use a generic function:
public static T GetData<T>(IDataReader reader, string column)
{
if (reader.IsDBNull(reader.GetOrdinal(column)))
return default(T);
return (T)reader[reader.GetOrdinal(column)];
}Then, call it using
c.Category.Name = Utility.GetData<string>(reader, "categoryName");
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
I like this!
/\ |_ E X E GG
|
|
|
|
|
If you can't handle NULL values in the client code, don't let them get into the databases. Set the column's nullable property to false (if creating the table in SQL, use columnname datatype NOT NULL).
I tend to use a cast with the indexed property which returns an object, which will leave a reference variable (such as a string) set to null, but cause a NullReferenceException if casting to a value type like int.
In general I prefer not to allow NULLs in the columns, at least in part to ensure that you can detect an outer join that didn't have a match (useful for doing a multi-column NOT IN equivalent).
|
|
|
|
|
The issue isn't so much with the OP being unable to handle nulls in his code, but more to do with him looking for a simple way to handle them in the minimum amount of code.
Now, nulls definitely cause some contention - they almost seem to cause religious hysteria among developers. At their most basic, they are useful for identifying the absence of a value. Suppose that you wanted to capture if a person was married or not, it is valid to have three conditions; Yes, No and Unkown (or null). This could be because somebody didn't ask the question so you don't know whether or not they are. Oh well, that's enough rambling about nulls. It's time for me to go get a life again.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
hi all,
i am trying to install sql server 2005 standard edition but an error message is still coming on which says:
SQL server cannot install files to the compressed or encrepted folder: c:\\..............
...........
does any one has an idea
10x
costavo
-- modified at 8:49 Wednesday 1st August, 2007
for sure i am an Admin
i dont know which and what folder is encrepted. i copied the installation files from the dvd to the hard disk
10x agian
|
|
|
|
|
Is the folder compressed or encrypted? Are you running as administrator?
|
|
|
|
|
in my system, SQL Server 2000 and SQL Server 2005 are there.
But I couldnt connect to SQL Server 2000.
Does anybody know why and how can i resolve this??
Thanks,
Saran
|
|
|
|
|
If you have both sql 2000 and sql 2005 loaded on the same server one of them probably has an instances name. So if you sql server name is sqlserver1, only one of the sql servers can use that name the other one has to be an instance name. So I am guessing you know the name to one of the serves, but you need to figure out the name to the other one.
Hope that helps.
Ben
|
|
|
|
|
hi
is there any one help me to run a query in MS Sqlserver periodical for example delete some data every 24hour that be independent from asp or php and etc...
i want that MS Sqlserver run it itself.
thanks
|
|
|
|
|
Sql Server has the ability to schedule tasks inside and run them itself. If you are using SQL Server 2000, you will need to expand the Management node under your database, then expand SQL Server Agent and select Jobs. Right click and select New Job...
In the General tab enter a name. Select the Steps tab and New... to create a new task. Suppose that you want to run a particular delete (delete from myTab where @created > dateadd(dd, getdate(), 2)). Give the step a name and leave the type as Transact-SQL Script (TSQL). Choose the database that you want to run the task against and enter the SQL in the Command textbox. Click OK.
Next, go to the Schedules tab and create a new schedule. To change the times when it runs, select the Change button and set your schedule as appropriate.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
We have been using an Access 2000 mdb database with a VB.NET Windows application. We have recently ported from .NET 1.1 to .NET 2.0 and also upgraded to Office 2007.
Due to some of the users of our software, we have used Access for data storage instead of SQL Server, most of our clients did not want the server and liked having lots of smaller Access databases which they can look at outside of our software.
We are now looking at the option of either changing our database to an Access 2007 accdb format or even using SQL Server express which can be used on a users own machine or their server.
I have been looking on the Internet to find some stats on the performance benefits of either of these routes, particluarly to have a justified argument to change. Does anyone know what the performance benefits of an accdb database over an mdb database in terms of concurrent users, number of records that can be stored and general speed at higher volumes?
Also it would be good to know what else we gain over and above an accdb database using SQL Server express.
Any information or links to some stats would be much appreciated.
Jason
|
|
|
|
|
I don't know about the stats, but a great plus is that you have access to using Sql Server Management objects for working with database level requirements, such as creating tables and databases. Another plus that might not be useful immediately is the ability to create CLR stored procedures that can be debugged in VS2005 and use more common language functions.
We were using Access and ran into issues with performance and updatability of data within the database. We have since moved to Sql Express and I can tell you it is great being able to work with it instead of access.
Hope this helps at least a little...
Aaron
_____________________________________________________________________
Our developers never release
code. Rather, it tends to escape, pillaging the countryside all around.
The Enlightenment Project (paraphrased comment)
Visit Me at GISDevCafe
|
|
|
|
|
Hi, Is it possible to create a stored procedure using a text editor like note pad? I have three more questions for you.
If I can create it with a text editor, what extension do I give it and what folder must I put it in to make it work? <br />
<br />
If I can create it with notepad and I make changes to it, will SQL Server give error messages, or must I create a new stored proc? <br />
<br />
Is the syntax for MySQL be compatible with SQL?
The reason I ask is because if I can create a stored proc with a text editor, then it would really be convenient. If you know of a good tutorial on this please point me to it, thank you in advance for your help.
-- modified at 3:50 Wednesday 1st August, 2007
|
|
|
|
|
ASPnoob wrote: If I can create it with a text editor, what extension do I give it and what folder must I put it in to make it work?
Stored Procedures are stored internally inside SQL Server.
You should use the Sql Server Management Studio for 2005 or the Enterprise Manager for 2000.
You can create the SQL code in a text file and save it as a .sql file then import it into whichever sql management app you use but you might as well use the editor provided in the management app as it'll have more features than notepad :P.
ASPnoob wrote: Is the syntax for MySQL be compatible with SQL?
I assume you mean Sql Server ... SQL is a language, Sql Server is a database engine.
And kinda, if you use just the most basic of the Sql Syntax then it'll be ok but each database engine has it's own extentions. Sql Server uses T-SQL and MySql uses something I don't know about :P
|
|
|
|
|
Hi all
I have an issue with select statement. Like in one form i have 5 fields all combo box and after selecting all we have search option so in a data grid i am displaying the records which satisfies all criteria in the combo box. It works fine.
But now i want is if user select only one combo box and press search the grid should show the related records which satisfies the combo box condition. I have tried with AND OR condition in query but not getting the proper records.
Pl help
Regards
Prakash Mishra(Banglore,India)
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
