how to insert sql stored procedure into vb.net code??
I am trying to create a paged web site from my database file. for that i found an appropriate sql code. the only thing i don't know is how to declare this sql inside the vb. is it supposed to be in the same .aspx file or in another one?? pls help if you know. thank you so much.
here's the sql stored procedure:
------------------------------------------------------------
CREATE PROCEDURE sp_PagedItems
(
@Page int,
@RecsPerPage int
)
AS
-- We don't want to return the # of rows inserted
-- into our temporary table, so turn NOCOUNT ON
SET NOCOUNT ON
--Create a temporary table
CREATE TABLE #TempItems
(
ID int IDENTITY,
Name varchar(50),
Price currency
)
-- Insert the rows from tblItems into the temp. table
INSERT INTO #TempItems (Name, Price)
SELECT Name,Price FROM tblItem ORDER BY Price
-- Find out the first and last record we want
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)
-- Now, return the set of paged records, plus, an indiciation of we
-- have more records or not!
SELECT *,
MoreRecords =
(
SELECT COUNT(*)
FROM #TempItems TI
WHERE TI.ID >= @LastRec
)
FROM #TempItems
WHERE ID > @FirstRec AND ID < @LastRec
-- Turn NOCOUNT back OFF
SET NOCOUNT OFF
-----------------------------------------------------------
and here's the vb and html code:
-----------------------------------------------------------
<!-- This assumes you already have a recordset object
explicitly created name objRS -->
<%
'How many records per page do we want to show?
Const iRecordsPerPage = 10
Dim currentPage 'what page are we on??
Dim bolLastPage 'are we on the last page?
if len(Request.QueryString("page")) = 0 then
currentPage = 1
else
currentPage = CInt(Request.QueryString("page"))
end if
'Show the paged results
strSQL = "sp_PagedItems " & currentPage & "," & iRecordsPerPage
objRS.Open strSQL, objConn
'See if we're on the last page
if Not objRS.EOF then
if CInt(objRS("MoreRecords")) > 0 then
bolLastPage = False
else
bolLastPage = True
end if
end if
%>
<P>
<TABLE BORDER=0 CELLSPACING=1 CELLPADDING=4 ALIGN=CENTER>
<TR><TH COLSPAN=2 BGCOLOR=NAVY>
<FONT SIZE=+1 COLOR=WHITE>
List of Items
</FONT>
</TH></TR>
<%
Do While Not objRS.EOF %>
<TR><TD ALIGN=LEFT BGCOLOR=GRAY>
<%=objRS("Name")%>
</TD><TD ALIGN=CENTER BGCOLOR=GRAY>
<%=FormatCurrency(objRS("Price"))%>
</TD></TR>
<%objRS.MoveNext
Loop %>
</TABLE>
<P>
<CENTER>
<%
'Only show the previous button if we are NOT on the first page
if currentPage > 1 then %>
<INPUT TYPE=BUTTON VALUE="<< Previous <%=iMaxRecords%> Records"
ONCLICK="document.location.href='thispage.asp?page=<%=currentPage-1%>'">
<% end if
'Only show the next button if we are NOT on the last page
if Not bolLastPage then %>
<INPUT TYPE=BUTTON VALUE="Next <%=iMaxRecords%> Records >>"
ONCLICK="document.location.href='thispage.asp?page=<%=currentPage+1%>'">
<% end if %>
</CENTER>
----------------------------------------------------------
thanks alot again.
|
0 |
|
niaher
3/17/2005 4:02:28 AM
asp.net.mysql
1929 articles. 
0 followers.
0 Replies
1183 Views
Similar Artilces:
VB.NET SQL stored procedure: procedure has no parameters and arguments were supplied
VB.NET SQL stored procedure: procedure has no parameters and arguments were supplied Please assist me:This erorr message is produce when calling the stored procedure in vb.netProcedure AutomateMatterNumber has no parameters and arguments were supplied."MS SQL 2000stored procedure:*/CREATE PROCEDURE dbo.AutomateMatterNumber ASDECLARE @nextMtr AS BIGINTDECLARE @dtToday AS DATETIMEIF NOT EXISTS(SELECT * FROM tempMatter WHERE DATEDIFF(dd,DateSet,GETDATE())=0 )BEGINDELETE FROM tempMatter-- incase there are some old recordsSELECT TOP 1 @nextMtr= CONVERT(BIGINT, MatterNumber) + 1 ,@dtTod...
SQL Server Stored Procedures/VB.Net
IS there a way inside code for vb.net to programmatically change sorting in a stored procedure???Reason is I need a two way sort and did not want to have to write a SP for each way ....If so Do you have an example..... Most likely the easiest way, is, when you retrieve the data from the sProc, put it in a DataView.. That way, you can sort The DataView by any field, ASC or DESC that you wantDavid WierMCP/ASPInsiderASPNet101.com - where to look first!Control Grouper - easily control properties for multiple controls with one control!Calendar Express - The Best HTML Calendar Generator on th...
Translate c#.net code to vb.net code
Hi I can usually do the translation myself, but this time I am a bit stumped Let us consider to rewrite Urls (more than 16000 links) using a table. Save original url and rewritten url in a table. Now add a class file in App_Code folder. class UrlRewriteModule : IHttpModule{ public void Dispose() { // Nothing to dispose } public void Init(HttpApplication context) { context.BeginRequest += new EventHandler(context...
How to convert C#.Net code to VB.Net code ???...
HI, i was searching something new, then i suddenly find some tips to convert C#.Net code to VB.Net but i did not do well. i am puttint its url here plz tell me how to convert C#.Net code to VB.Net. http://www.dotnetspider.com/code/C-273-Convert-C-VB-NET.aspx Jasim...Please remember to click “Mark as Answer” on the post that helps youJasim AkhtarNew Delhi ( INDIA ) It would be better if you write the problem you having, while converting from C# to VB.NET. You cannot completely rely on online converters. ThanksMark post(s) as "Answer" that helped youElectronic ScrewWebsite||Bl...
use VB.NET and C#.NET code in the same C#.NET project
All-- Here is a sample that is "off the beaten path", (at least for me). Is it possible, in an ASP.NET application, using the code-behind page building technique, to have both pages written in VB.NET and pages written C#.NET?At http://www.WebLogicArts.com/DemoList.aspx there is a sample that shows that, (contrary to popular belief), it IS possible to mix ASP.NET pages built with C#.NET with ASP.NET pages built with VB.NET in the same VS.NET 2003 project. Note that this is just a "fun" sample to see if it can be done and I do not recommend this practice as a "standard" way of develo...
VB.net connect to SQL Express Stored Procedure
Dear Experts, i am writing a VB.net window application. I created a SQL Express database inside the project. Inside the SQLExpress database, i created a stored procedure. I would like to know how to call that SP. Below is what i did and it raised a error "Missing provider" I tried to add "'Provider=SQLOLEDB;'", the missing provider error message gone but another error raised as below.. No error message available, result code: DB_E_ERRORSOCCURRED(0x80040E21). Sub PushtoDB() Dim con As OleDbConnection Dim cmd As OleDbCommand = New OleD...
please help with vb.net code and stored procedure
I have the following stored procedure in sql server 2000: (@nsn varchar 15 int, @item_nam varchar 255 ouput ) AS declare @stockquantity int select @stockquantity = stockquantity from inventory where stockquantity = 0 select @item_nam = item_nam from inventory where nsn = @nsn delete from inventory where nsn = @nsn and stockquantity = 0 GO what would the vb.net code be to display the item_nam if the query was successful in deleting a record, but would then retutrn a different mesage if the item was not deleted becasuse the stockquantity was not equ...
Stored Procedures
Hi all, if have problem to display error message in vb.net that comes from a stored procedure. IF ...... then msgbox "ERROR at Update" returnvalue = SUCCES ELSE Msgbox "Successfull Updated" returnvalue = ERROREND IFHow can I return values from Stored Procedure to VB.NET and then give to User a Message that the Update was successful or not.Thanks to all You can always use output variables and check the value http://www.codeguru.com/vb/gen/vb_database/sqls...
Problem with Oracle Stored Procedures using ODP.NET in VB.NET (VS 2003)
This is the stored procedure,Procedure RetrieveReservations(pProperty In Varchar2,pResHeaderId In Varchar2,pEmail In Varchar2,pPasswd In Varchar2,ResultSet In Out ResCursor);This is the old .asp code (part of it)objConn.ConnectionString = Application("BackEndConnection_ConnectionString")objConn.Open objComm.ActiveConnection = objConn objComm.CommandText = "internet_package.RetrieveReservations"objComm.CommandType = adCmdStoredProc objComm.Parameters.Append objComm.CreateParameter("pProperty", adVarChar , adParamInput, 8, Request.QueryString("propid"))objComm.Parameters.Append objComm.Crea...
Sending SMS using vb.net or C#.net using vb.net or c#.net
Hi My requirement is I hav one csv file with these fields id,mobilenum,messgae,status.intiallu staus is 0. once i read the all fileds and take that mobile number.using tat mobile number i need to send sms .after sending sms i shuld change status as 1 How to send sms thru coding (please dont provide any links.if it is provide also please give working links becox i checked codeproject .i didnt get any nice link. and also provide the how to update the status field im csv file Thank ssandhya To send SMS, you need some third party SMS providers. if you consul...
Using sql Stored procedure from vb.net to output data
Can using a stored procedure from vb.net I get a report converted to xml for printing purposes.....ANy coding samples...
Can somebody help
I Seem to be going nuts ! I know it has to be simple but I am missing the link. I haven't used crystal reports with asp.net before. So pardom me if my problem sounds silly. My requirement is very simple actually. I am using asp.net 1.1 with vb.net in vs.net 2003 with the inbuilt crystal reports control. I have to connect to a sql server 2000 database to extract rows from a stored procedure which accepts a parameter and the report has to be in Mail label format. I referred various books and web sites and according to that , added a crystal reports file to the project and selected the store...
How to execute stored procedure from vb.net CodeBehind instead of a sql statement?
I've learned how to exeucte a simple sql statement from my vb.net codebehind (see sample below), but I want to go a step further and learn how to execute a stored procedure from my code behind. I've already written the stored proc, just don't know how to reference it and the parameters in my codebehind..Dim conn As SqlConnectionDim comm As SqlCommandDim reader As SqlDataReaderDim connectionString As String = ConfigurationManager.ConnectionStrings("xxx").ConnectionStringconn = New SqlConnection(connectionString) comm = New SqlCommand("INSERT into xxx (a...
a stupid problem in accessing the stored procedure from vb.net code :d
Edited by SomeNewKid. Please post code between <code> and </code> tags. Hi all knights of .NET Order.....plzz help a damsel in distress :d, I have written a lil Stored procedure that updates a field (called I_Status) in a table (called DCM_T_MessageHistory). I just need to know how to access this stored procedure through my vb.net code. The way that I can go is that I need to pass the information (there is just one input to the stored procedure called @p_I_MID. This input is actually the Employeed ID, a primary key field in DCM_T_MessageHistory table. ...
VB.NET SQL stored procedure: procedure has no parameters and arguments were supplied Please assist me:This erorr message is produce when calling the stored procedure in vb.netProcedure AutomateMatterNumber has no parameters and arguments were supplied."MS SQL 2000stored procedure:*/CREATE PROCEDURE dbo.AutomateMatterNumber ASDECLARE @nextMtr AS BIGINTDECLARE @dtToday AS DATETIMEIF NOT EXISTS(SELECT * FROM tempMatter WHERE DATEDIFF(dd,DateSet,GETDATE())=0 )BEGINDELETE FROM tempMatter-- incase there are some old recordsSELECT TOP 1 @nextMtr= CONVERT(BIGINT, MatterNumber) + 1 ,@dtTod...
SQL Server Stored Procedures/VB.Net
IS there a way inside code for vb.net to programmatically change sorting in a stored procedure???Reason is I need a two way sort and did not want to have to write a SP for each way ....If so Do you have an example..... Most likely the easiest way, is, when you retrieve the data from the sProc, put it in a DataView.. That way, you can sort The DataView by any field, ASC or DESC that you wantDavid WierMCP/ASPInsiderASPNet101.com - where to look first!Control Grouper - easily control properties for multiple controls with one control!Calendar Express - The Best HTML Calendar Generator on th...
Translate c#.net code to vb.net code
Hi I can usually do the translation myself, but this time I am a bit stumped Let us consider to rewrite Urls (more than 16000 links) using a table. Save original url and rewritten url in a table. Now add a class file in App_Code folder. class UrlRewriteModule : IHttpModule{ public void Dispose() { // Nothing to dispose } public void Init(HttpApplication context) { context.BeginRequest += new EventHandler(context...
How to convert C#.Net code to VB.Net code ???...
HI, i was searching something new, then i suddenly find some tips to convert C#.Net code to VB.Net but i did not do well. i am puttint its url here plz tell me how to convert C#.Net code to VB.Net. http://www.dotnetspider.com/code/C-273-Convert-C-VB-NET.aspx Jasim...Please remember to click “Mark as Answer” on the post that helps youJasim AkhtarNew Delhi ( INDIA ) It would be better if you write the problem you having, while converting from C# to VB.NET. You cannot completely rely on online converters. ThanksMark post(s) as "Answer" that helped youElectronic ScrewWebsite||Bl...
use VB.NET and C#.NET code in the same C#.NET project
All-- Here is a sample that is "off the beaten path", (at least for me). Is it possible, in an ASP.NET application, using the code-behind page building technique, to have both pages written in VB.NET and pages written C#.NET?At http://www.WebLogicArts.com/DemoList.aspx there is a sample that shows that, (contrary to popular belief), it IS possible to mix ASP.NET pages built with C#.NET with ASP.NET pages built with VB.NET in the same VS.NET 2003 project. Note that this is just a "fun" sample to see if it can be done and I do not recommend this practice as a "standard" way of develo...
VB.net connect to SQL Express Stored Procedure
Dear Experts, i am writing a VB.net window application. I created a SQL Express database inside the project. Inside the SQLExpress database, i created a stored procedure. I would like to know how to call that SP. Below is what i did and it raised a error "Missing provider" I tried to add "'Provider=SQLOLEDB;'", the missing provider error message gone but another error raised as below.. No error message available, result code: DB_E_ERRORSOCCURRED(0x80040E21). Sub PushtoDB() Dim con As OleDbConnection Dim cmd As OleDbCommand = New OleD...
please help with vb.net code and stored procedure
I have the following stored procedure in sql server 2000: (@nsn varchar 15 int, @item_nam varchar 255 ouput ) AS declare @stockquantity int select @stockquantity = stockquantity from inventory where stockquantity = 0 select @item_nam = item_nam from inventory where nsn = @nsn delete from inventory where nsn = @nsn and stockquantity = 0 GO what would the vb.net code be to display the item_nam if the query was successful in deleting a record, but would then retutrn a different mesage if the item was not deleted becasuse the stockquantity was not equ...
Stored Procedures
Hi all, if have problem to display error message in vb.net that comes from a stored procedure. IF ...... then msgbox "ERROR at Update" returnvalue = SUCCES ELSE Msgbox "Successfull Updated" returnvalue = ERROREND IFHow can I return values from Stored Procedure to VB.NET and then give to User a Message that the Update was successful or not.Thanks to all You can always use output variables and check the value http://www.codeguru.com/vb/gen/vb_database/sqls...
Problem with Oracle Stored Procedures using ODP.NET in VB.NET (VS 2003)
This is the stored procedure,Procedure RetrieveReservations(pProperty In Varchar2,pResHeaderId In Varchar2,pEmail In Varchar2,pPasswd In Varchar2,ResultSet In Out ResCursor);This is the old .asp code (part of it)objConn.ConnectionString = Application("BackEndConnection_ConnectionString")objConn.Open objComm.ActiveConnection = objConn objComm.CommandText = "internet_package.RetrieveReservations"objComm.CommandType = adCmdStoredProc objComm.Parameters.Append objComm.CreateParameter("pProperty", adVarChar , adParamInput, 8, Request.QueryString("propid"))objComm.Parameters.Append objComm.Crea...
Sending SMS using vb.net or C#.net using vb.net or c#.net
Hi My requirement is I hav one csv file with these fields id,mobilenum,messgae,status.intiallu staus is 0. once i read the all fileds and take that mobile number.using tat mobile number i need to send sms .after sending sms i shuld change status as 1 How to send sms thru coding (please dont provide any links.if it is provide also please give working links becox i checked codeproject .i didnt get any nice link. and also provide the how to update the status field im csv file Thank ssandhya To send SMS, you need some third party SMS providers. if you consul...
Using sql Stored procedure from vb.net to output data
Can using a stored procedure from vb.net I get a report converted to xml for printing purposes.....ANy coding samples...
Can somebody help
I Seem to be going nuts ! I know it has to be simple but I am missing the link. I haven't used crystal reports with asp.net before. So pardom me if my problem sounds silly. My requirement is very simple actually. I am using asp.net 1.1 with vb.net in vs.net 2003 with the inbuilt crystal reports control. I have to connect to a sql server 2000 database to extract rows from a stored procedure which accepts a parameter and the report has to be in Mail label format. I referred various books and web sites and according to that , added a crystal reports file to the project and selected the store...
How to execute stored procedure from vb.net CodeBehind instead of a sql statement?
I've learned how to exeucte a simple sql statement from my vb.net codebehind (see sample below), but I want to go a step further and learn how to execute a stored procedure from my code behind. I've already written the stored proc, just don't know how to reference it and the parameters in my codebehind..Dim conn As SqlConnectionDim comm As SqlCommandDim reader As SqlDataReaderDim connectionString As String = ConfigurationManager.ConnectionStrings("xxx").ConnectionStringconn = New SqlConnection(connectionString) comm = New SqlCommand("INSERT into xxx (a...
a stupid problem in accessing the stored procedure from vb.net code :d
Edited by SomeNewKid. Please post code between <code> and </code> tags. Hi all knights of .NET Order.....plzz help a damsel in distress :d, I have written a lil Stored procedure that updates a field (called I_Status) in a table (called DCM_T_MessageHistory). I just need to know how to access this stored procedure through my vb.net code. The way that I can go is that I need to pass the information (there is just one input to the stored procedure called @p_I_MID. This input is actually the Employeed ID, a primary key field in DCM_T_MessageHistory table. ...
Web resources about - how to insert sql stored procedure into vb.net code?? - asp.net.mysql
Parliamentary procedure - Wikipedia, the free encyclopedia... of the House of Commons of the Parliament of the United Kingdom , from which it derives its name. In the United States, parliamentary procedure ...
Procedure is more a snap than a snipA QUEENSLAND doctor is bidding to set an unusual world record by performing the highest number of vasectomies in one day, with the help of fellow ...
Rushed cosmetic procedures a 'recipe for disaster'​When Chanelle O'Hare went searching online for a deal on potential cosmetic procedures, she could not have imagined that what she ended ...
Jetstar procedures under investigation after planes took off too heavyJetstar's procedures for calculating the weight of its aircraft are under review by the Australian Transport Safety Bureau after two of its planes ...
Old Lady Lawyer: Uncivil Procedure
What is some of the worst behavior you've witnesses by attorneys?
What is some of the worst behavior you've witnesses by attorneys?
NFL Announces Changes to Officiating Procedures for Playoffs - Bleacher ReportThe NFL formally approved changes to its postseason officiating procedures to allow referees the opportunity to consult Vice President of Officiating ...
We need to reform the culture of law enforcement, not just the proceduresWe need to reform the culture of law enforcement, not just the procedures by digby I have a new piece up at Salon this morning about police ...
Will Paul Ryan Make His Mark As Speaker By Instituting Impeachment Procedures Against President Obama ...Wisconsin Ayn Rand devotee Paul Ryan just started his new job as Speaker of the dysfunctional House Republicans. And he's already headed for ...
Catholic hospital allowed to deny sterilization procedure, judge says
Hospital in California argued that the procedure known as tubal ligation would violate its religious freedom
Hospital in California argued that the procedure known as tubal ligation would violate its religious freedom
TSA Updates Screening Procedure, Will Mandate Some Passengers Use Full-Body Scanners... Imaging Technologies, or AIT, in favor of full-body pat-downs by TSA agents. Under the new mandate, not everyone can opt for the pat-down procedure. ...
Resources last updated: 1/18/2016 10:33:00 AM