I writte a stored procedure for username , password ....
How can i call that stored procedure to verify the username & password
then if both match in the database redirected to next page..? in asp with c#.net back end programming
Hi All... I'm calling a stored procedure from C#. I'm sending it input parameters as follows:SqlCommand c = new SqlCommand("AddAuthor", myConnection);c.CommandType = CommandType.StoredProcedure;
c.Parameters.Add(new SqlParameter("@LastName", SqlDbType.VarChar, 100));c.Parameters["@LastName"].Value = "Last";
c.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.VarChar, 100));c.Parameters["@FirstName"].Value = "First";
In my opinion, that works nice - and it's easy. The reference I'm using says I can also specify "output" parameters - instead of supplying a value, one needs to change a property called direction.
But my stored procedure doesnt return any output parameters per se, but it does return a value. That is the last statement in the stored procedure is "RETURN @@IDENTITY" - it returns the identity field after an INSERT... So how do I get that value back in my C# code?
Thanks for the help in advance. Happy 4th to all! : ) -- Curt
Hi,
I was wondering if someone can tell me why my sub for calling stored procedures doesn't work.The string strFinFileId is not null.
I keep getting the following error System.IndexOutOfRangeException: Total
Here's my sub.
'//get GrandTotal Protected Sub GetGrandTotal(ByVal finfileid As String) Dim myConnection As SqlConnection Dim connString As String Dim strFinFileId As String Dim strGrandTotal As Decimal
strFinFileId = finfileid connString = ConfigurationManager.ConnectionStrings("PMOConnectionString1").ConnectionString
myConnection = New SqlConnection(connString) 'you need to provide password for sql server myConnection.Open()
Dim sql1 As String
sql1 = "GetGrandTotal " & Convert.ToInt32(strFinFileId)
Dim myCommand As SqlCommand
'Response.Write(sql1) 'Response.End()
myCommand = New SqlCommand(sql1, myConnection)
Dim reader As SqlDataReader = myCommand.ExecuteReader() While reader.Read() strGrandTotal = reader.Item("Total") LabelGrandTotal.Text = strGrandTotal 'Response.Write(strFinFileId) 'Response.End()
End While reader.Close() myConnection.Close() End Sub
Cheers
Mark :)
I have a stored procedure written to update a table:
The stored procedure has the following parameters:
@Original_TypeID int,
@Type_Desc varchar(35),
@Contact_Name varchar(20),
@Contact_Ad1 varchar(25),
@Contact_Ad2 varchar(25),
@Contact_City varchar(10),
@Contact_Phone varchar(12),
@Contact_Fax varchar(12),
@Contact_Email varchar(35)
And I want to call this procedure when the "update" button is clicked, my code is:Protected Sub UpdateButton_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Dim myConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("myConnectionString").ConnectionString)Dim myCommand As SqlCommandDim UpdteParam As New SqlParameter()
myConnection.Open()myCommand = New SqlCommand("[dbo].[sp_Update_Types]", myConnection)
myCommand.CommandType = CommandType.StoredProcedure
????????????????????????myCommand.Parameters.Add(UpdateParam)
???????????myCommand.ExecuteNonQuery()
myConnection.Close()
End Sub
Can you please help me with the ????? areas? I don't know how to pass the parameters into the procedure, and also the second set of ??? s, I'm not sure if I am executing the command correctly.
Thank you.
I'm relatively new to SQL Server and I was wondering if there is any way to call a DLL from a regular stored procedure? I believe I've read that the extended stored procedure is a DLL and therefore should be able to call another DLL. Can this also be verified? We're looking to speed up some sections of Visual Basic code which does some heavy I/O work. I've also asked this once before but didn't get an absolute answer - can you write the extended stored procedures in Visual Basic or is C/C++ the only language supported right now?
Thanks.
Caroline Kaplonski
ckaplonski@buckconsultants.com
Hi AllI was wondering if there is a way to call a stored procedure from insideanother stored procedure. So for example my first procedure will call asecond stored procedure which when executed will return one record and iwant to use this data in the calling stored procedure. Is this possible ?Thanks in advance
View 18 Replies View RelatedIs it possible to have a stored procedure in database A while callingit from database B and have it manipulate the tables in database B(whatever the calling database happens to be)?We have a large-scale app that uses many complex stored procedures,and as of now, we're copying the SPs to every new database that iscreated, and it will soon become a nightmare for propagating updatesand fixes. We'd like to keep a master set of the SPs in one DB and"use" them from other DBs so that they only query data and manipulatetables in the calling DB. I hope someone has some suggestions. Thanks.
View 1 Replies View RelatedHow can we call Stored Procedure inside any SQL Statement
For Example.
If we have procedure name sprocCurrentPriority
select * from tablename where colmunname = exec sprocCurrentPriority
I'm using reporting services to build a report and I plan on using a stored procedure to gather my data for the report. My question is:
I know it's possible to call a stored procedure from a stored procedure, both within the same dB and in different dB's, but is it possible to call a SP that's on a different server? My gut says "definitely not. at least not easily." But I wanted to see if anyone had any thoughts on this before I pursue a different course of action.
can anyone tell me how to call SQL stored procedures in MS Access97...
appreciate any help
I am currently developing web pages based on data pulled from SQL Server with ASP. I've mastered the art of SELECTing, INSERTing, etc, but want to know if it is possible to run STored Procedures from a page by clicking on a button on a particualr web page.
View 1 Replies View Related
I have a simple stored procedure:
create procedure sp_testres
@mult1 int,
@mult2 int,
@result int output
as
select @result = (10*@mult1) + @mult2
go
When I call it
declare @result int
exec sp_testres 5, 6, @result output
print @result
(Result is correctly shown as 56).
I then in C# wrote the following:
m_cmd.CommandText = "sp_testres";
SqlParameter param2 = new SqlParameter("@mult2", SqlDbType.Int);
param2.Value = 6;
SqlParameter param1 = new SqlParameter("@mult1", SqlDbType.Int);
param1.Value = 5;
SqlParameter param3 = new SqlParameter("@result", SqlDbType.Int);
param3.Direction = ParameterDirection.Output;
m_cmd.CommandType = CommandType.StoredProcedure;
m_cmd.Parameters.Add(param1);
m_cmd.Parameters.Add(param2);
m_cmd.Parameters.Add(param3);
m_cmd.ExecuteNonQuery();
This works and param3.Value holds the result value.
I also notice that I can supply the parameters in any order, and things work fine.
What I want to know is: can I call the stored procedure with parameters, where I haven't supplied the parameter name, and just rely on the parameter order matching instead?
Hello All,
We are trying to figure out how to make a stored procedure call and pass some inputs using C# and plain ADO. We are able to call an empty stored procedure but cannot pass parameters into a stored procedure.
Does anyone have sample code in C# whereby we can open a connection and pass inputs into a stored procedure? The following is a sample code we are using:
ADODB.Parameter param = new ADODB.Parameter();
param=cmd.CreateParameter("@StoreID", ADODB.DataTypeEnum.adInteger, ADODB.ParameterDirectionEnum.adParamInput,sizeof(int) , 9);
cmd.Parameters.Append(param);
ADODB.Recordset rsInv = cmd.Execute(out objAffected, ref objAffected,-1 );
In the above example, we create a parameter of type integer and try to add it to the ADO Command object. It is supposed to return a recordset. It returns a recordset with record count -1.
Sincerely,
Dwight Kulkarni
I am writing a set of store procedures (around 30), most of them require the same basic logic to get an ID, I was thinking to add this logic into an stored procedure.
The question is: Would calling an stored procedure from within an stored procedure affect performance? I mean, would it need to create a separate db connection? am I better off copying and pasting the logic into all the store procedures (in terms of performance)?
Thanks in advance
John
I have created a cursor using the following type of syntax:
DECLARE MyCursor CURSOR FOR
SELECT ID FROM tblEmployees
OPEN MyCursor
BEGIN
FETCH NEXT FROM MyCursor
END
CLOSE MyCursor
Instead of the SELECT statement (SELECT ID FROM tblEmployees), I actually have a very complex select statement. I have created a stored procedure to handle this select. However, I cannot find a way to call a stored procedure in place of the SELECT statement in the cursor. Is this possible? Thanks.
I am trying to call a stored procedure inside a SQL SELECT statement. Has anybody had to do this in the past? I have a SELECT statement in a Microsoft Access database and I need that SELECT statement to call the stored procedure in the SQL server. Any help would be appreciated
View 4 Replies View RelatedI know its a weird request, but we have created an application with sql server but our client wants a version which can be put onto disk.
We decided to create the stored procedures into queries, would this be the best idea and if so does anyone know if there is a freeware software that can do this or will I have to painstakingly re-create the queries?
Any advice would be greatly appreciated.
Many thanks
Smilla
Hi guys, hoping one of you may be able to help me out. I am using VS 2005, and VB.net for a Windows application.
I have a table in SQL that has a list of Storedprocedures: Sprocs Table: SPID - PK (int), ID (int), NAME (string), TYPE (string)The ID is a Foreign key (corresponding to a Company ID), the name is the stored procedure name, and Type (is the type of SP).
On my application I need to a certain SP depending on the company selected and what page you are on. I have a seperate SP that passes in parameters for both Company, and Type and should output the Name value:
ALTER PROCEDURE [dbo].[S_SPROC] ( @ID int, @TYPE CHAR(10), @NAME CHAR(20) OUTPUT )AS
SELECT @NAME = NAME FROM SPROCSWHERE [ID] = @IDAND [TYPE] = @TYPE
Unfortunately I dont seem to be able to get the output in .Net, or then be able to fill my dataset with the Stored Procedure.Has anyone done something similar before, or could point me in the right direction to solving this problem.
ThanksPhil
Hi, all
I'm using Sql server 2000
I want to make select statement dynamically and return table using function.
in sp, I've done this but, in function I don't know how to do so.
(I have to create as function since our existing API..)
Following is my tials...
1.
alter Function fnTest
( @fromTime datetime, @toTime datetime)
RETURNS Table
AS
RETURN Exec spTest @from, @to
GO
Yes, it give syntax error..
2. So, I found the following
From Sql Server Books Online, Remark section of CREATE FUNCTION page of Transact-SQL Reference , it says following..
"The following statements are allowed in the body of a multi-statement function. Statements not in this list are not allowed in the body of a function: "
.....
* EXECUTE statements calling an extended stored procedures.
So, I tried.
alter Function fnTest
( @fromTime datetime, @toTime datetime)
RETURNS Table
AS
RETURN Exec master..xp_msver
GO
It doesn't work... syntax err...
Here I have quick question.. How to execute statements calling an extended stored procedures. any examples?
Now, I'm stuck.. how can I create dynamic select statement using function?
I want to know if it's possible or not..
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie
exec dbo.DeriveStatusID 'Created'
returns an int value as 1
(performed by "SELECT statusID FROM statusList WHERE statusName= 'Created')
but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie:
exec dbo.AddProduct_Insert 'widget1'
which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
I want to simply the insert to perform (in one sproc):
SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example).
My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert).
Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
Hi all,
I try to learn "How to Access Stored Procedures with ADO.NET 2.0 - VB 2005 Express: (1) Handling the Input and Output Parameters and (2) Reporting their Values in VB Forms". I found a good article "Calling Stored Procedures from ADO.NET" by John Paul Cook in http://www.dbzine.com/sql/sql-artices/cook6. I downloaded the source code into my VB 2005 Express:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Public Class Form_Cook
Inherits System.Windows.Form.Form
#Region " Windows Form Designer generated code "
Public Sub New()
MyBase.New()
'This call is required by the Windows Form Designer.
InitializeComponent()
'Add any initialization after the InitializeComponent() call
End Sub
'Form overrides dispose to clean up the component list.
Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub
'Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer
'NOTE: The following procedure is required by the Windows Form Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
Friend WithEvents GroupBox1 As System.Windows.Forms.GroupBox
Friend WithEvents labelPAF As System.Windows.Forms.Label
Friend WithEvents labelNbrPrices As System.Windows.Forms.Label
Friend WithEvents UpdatePrices As System.Windows.Forms.Button
Friend WithEvents textBoxPAF As System.Windows.Forms.TextBox
Friend WithEvents TenMostExpensive As System.Windows.Forms.Button
Friend WithEvents grdNorthwind As System.Windows.Forms.DataGrid
Friend WithEvents groupBox2 As System.Windows.Forms.GroupBox
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
Me.GroupBox1 = New System.Windows.Forms.GroupBox()
Me.labelPAF = New System.Windows.Forms.Label()
Me.labelNbrPrices = New System.Windows.Forms.Label()
Me.textBoxPAF = New System.Windows.Forms.TextBox()
Me.UpdatePrices = New System.Windows.Forms.Button()
Me.groupBox2 = New System.Windows.Forms.GroupBox()
Me.TenMostExpensive = New System.Windows.Forms.Button()
Me.grdNorthwind = New System.Windows.Forms.DataGrid()
Me.GroupBox1.SuspendLayout()
Me.groupBox2.SuspendLayout()
CType(Me.grdNorthwind, System.ComponentModel.ISupportInitialize).BeginInit()
Me.SuspendLayout()
'
'GroupBox1
'
Me.GroupBox1.Controls.AddRange(New System.Windows.Forms.Control() {Me.labelPAF, Me.labelNbrPrices, Me.textBoxPAF, Me.UpdatePrices})
Me.GroupBox1.Location = New System.Drawing.Point(8, 8)
Me.GroupBox1.Name = "GroupBox1"
Me.GroupBox1.Size = New System.Drawing.Size(240, 112)
Me.GroupBox1.TabIndex = 9
Me.GroupBox1.TabStop = False
'
'labelPAF
'
Me.labelPAF.Location = New System.Drawing.Point(8, 16)
Me.labelPAF.Name = "labelPAF"
Me.labelPAF.Size = New System.Drawing.Size(112, 32)
Me.labelPAF.TabIndex = 2
Me.labelPAF.Text = "Enter Price Adjustment Factor"
'
'labelNbrPrices
'
Me.labelNbrPrices.Location = New System.Drawing.Point(8, 80)
Me.labelNbrPrices.Name = "labelNbrPrices"
Me.labelNbrPrices.Size = New System.Drawing.Size(216, 16)
Me.labelNbrPrices.TabIndex = 5
'
'textBoxPAF
'
Me.textBoxPAF.Location = New System.Drawing.Point(120, 16)
Me.textBoxPAF.Name = "textBoxPAF"
Me.textBoxPAF.TabIndex = 0
Me.textBoxPAF.Text = ""
'
'UpdatePrices
'
Me.UpdatePrices.Location = New System.Drawing.Point(8, 48)
Me.UpdatePrices.Name = "UpdatePrices"
Me.UpdatePrices.Size = New System.Drawing.Size(88, 23)
Me.UpdatePrices.TabIndex = 6
Me.UpdatePrices.Text = "Update Prices"
'
'groupBox2
'
Me.groupBox2.Controls.AddRange(New System.Windows.Forms.Control() {Me.TenMostExpensive, Me.grdNorthwind})
<Part 1----To be continued due to the length of this post>
I was wondering if there was a way to call a stored procedure within a stored procedure, and after that's done, it goes back where the other stored procedure left off? I was also hoping to return a value also. Thank you.
View 3 Replies View RelatedI'm using an Access2K front end on a SQL Server2K backend.I use Scope_Identity() in a lot of stored procedures to pass thenewwly inserted record's unique ID to the next select statement in thesame stored procedure.What I'm wondering is how I can pass the Scope_Identity back to thecalling application.I'm hoping someone can show me the SP code and the aceess code neededto accomplish the following:I insert a new record in a table with a stored procedure. The SPpasses the uniqueID (Scope_Identity) back to MS Access, then MS Accessuses the uniqueID when calling another function...thanks
View 1 Replies View RelatedHi.
I am wondering if somebody out there wouldnt mind
looking over my stored procedure?
The idea behind this procedure is (was) that,
(they are numbered...)
1) the procedure checks for the existence of data in the database and
2) if no data exists (if the previous statement throws an error), then execute the insert statement
3) otherwise (data exists), execute the update
I also have "sub" error handling for each statement so, if it is executing the insert, return 99 for fail, 0 for success. the same for update.
problem is that its returning the values for the first select statement and i have no idea what is going on fir the Insert or Update statements as i cant set break points in a stored procedure.
thanks for any feedback.
Code:
ALTER PROCEDURE dbo.sp_UpdateEditProfile
(
@headline VARCHAR(50) = NULL,
@about_me TEXT = NULL,
@email_address VARCHAR(50) = NULL,
@edit_date VARCHAR(50) = NULL
)
AS
BEGIN
SELECT @edit_date = CONVERT(DATETIME,convert(char(26), getdate(), 109))
END
BEGIN
--check to see if user has data there already
1)SELECT headline, about_me FROM EditProfile WHERE email_address=@email_address
--if he doesn't,
IF @@ERROR <> 0
--this is an insert statement
BEGIN
SET ANSI_WARNINGS OFF --ansi errors on text insert
2)INSERT INTO EditProfile
(headline, about_me, email_address, edit_date)
VALUES (@headline, @about_me, @email_address, @edit_date)
SET ANSI_WARNINGS ON
IF @@ERROR <> 0
BEGIN
RETURN(99)
END
ELSE
BEGIN
RETURN(0)
END
END
--if he does
ELSE
--update the data
BEGIN
SET ANSI_WARNINGS OFF --ansi errors
3) UPDATE EditProfile
SET headline=@headline, about_me=@about_me, edit_date=@edit_date WHERE email_address=@email_address
SET ANSI_WARNINGS ON
IF @@ERROR <> 0
BEGIN
RETURN(99)
END
ELSE
BEGIN
RETURN(0)
END
END
END
Hi all!
Is there a possibility to call (and receive records) SQL Server (or other databases) procedure from MS Access?
I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!
View 11 Replies View RelatedI am trying to call DB2 stroe procedure from within SQL server 2000 using DTS. I have the IBM odbc driver installed on the server. I have created an ACtiveX script to run in DTS and it fails staing it could not findor load the DB2 store procedure.
Has anyone come across doing this and how they did it?
THanks for the help....
hai,
i want to create a table in a existing database in sql and i have to move data to the table and after my process i have to delete it .This is should be done thro codings.please help me.
Thanks in advance
Viswa
I want to know about the how to connect with asp.net 2005 and sqlexpress through codings using parameters
import System.Data.SqlClient;
related update delete insert codings ....
How to know about the make the new connection with different databases sqlserver, oracle mysql....
Hi,
This Might be a really simple thing, however we have just installed SQL server 2005 on a new server, and are having difficulties with the set up of the Store Procedures. Every time we try to modify an existing stored procedure it attempts to save it as an SQL file, unlike in 2000 where it saved it as part of the database itself.
Thank you in advance for any help on this matter
Using SQL 2005, SP2. All of a sudden, whenever I create any stored procedures in the master database, they get created as system stored procedures. Doesn't matter what I name them, and what they do.
For example, even this simple little guy:
CREATE PROCEDURE BOB
AS
PRINT 'BOB'
GO
Gets created as a system stored procedure.
Any ideas what would cause that and/or how to fix it?
Thanks,
Jason
Hi Peeps
I have a SP that returns xml
I have writen another stored proc in which I want to do something like this:Select FieldOne, FieldTwo, ( exec sp_that_returns_xml ( @a, @b) ), FieldThree from TableName
But it seems that I cant call the proc from within a select.
I have also tried
declare @v xml
set @v = exec sp_that_returns_xml ( @a, @b)
But this again doesn't work
I have tried changing the statements syntax i.e. brackets and no brackets etc...,
The only way Ive got it to work is to create a temp table, insert the result from the xml proc into it and then set @v as a select from the temp table -
Which to be frank is god awful way to do it.
Any and all help appreciated.
Kal
Hi all,I have a stored procedure that return a resultsete.g. stored proc: get_employee_detailsselect emp_id, emp_name, emp_salary, emp_positionfrom empoloyeeI would like to write another stored procedure that executes the abovestored procedure - returning the same number of records but it willonly show 2 columnse.g. new stored proc: get_employee_pay -- executesget_employee_detailsI only need to know emp_id, emp_salary.How can this be done in sql stored procedure?Thanks,June Moore.
View 2 Replies View Related