Using Stored Procedure

**sweatha** · May 16 '08, 08:35 AM

Originally posted by sweatha

Hi

I have created a stored procedure for registration form as

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFI ER ON
GO
-- =============== =============== ===============
-- Author: <Author,,Name >
-- Create date: <Create Date,,>
-- Description: <Description, ,>
-- =============== =============== ===============
CREATE PROCEDURE dbo.Pro_Login

@loginid varchar(50),
@password varchar(50),
@confirmpasswor d varchar(50),
@firstname varchar(50),
@lastname varchar(50),
@address varchar(200),
@address2 varchar(200),
@address3 varchar(200),
@city varchar(50),
@country varchar(50),
@province varchar(50),
@postalcode bigint,
@phone varchar(50),
@email varchar(50),
@how varchar(50)

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
insert into login
(loginid,passwo rd,confirmpassw ord,firstname,l astname,address ,address2,addre ss3,city,countr y,province,post alcode,phone,em ail,how)
values(@loginid ,@password,@con firmpassword,@f irstname,@lastn ame,@address,@a ddress2,@addres s3,@city,@count ry,@province,@p ostalcode,@phon e,@email,@how)

END

I have called the stored procedure in my front end as

Dim con As SqlConnection
Dim cmd As SqlCommand
Dim str As String
str = "user id=sa;password= cast;database=j sc;server=AUROR A-SERVER"
con = New SqlConnection(s tr)
Try
con.Open()
cmd.CommandType = CommandType.Sto redProcedure
cmd.CommandText = " Pro_Login "
cmd.Connection = con

Dim loginid As New SqlParameter("@ loginid", SqlDbType.VarCh ar, 50)
loginid.Value = txtLoginId2.Tex t.ToString()

Dim password As New SqlParameter("@ password", SqlDbType.VarCh ar, 50)
password.Value = txtPassword1.Te xt.ToString()

Dim confirmpassword As New SqlParameter("@ confirmpassword ", SqlDbType.VarCh ar, 50)
confirmpassword .Value = txtConfirmPassw ord.Text.ToStri ng()

Dim firstname As New SqlParameter("@ firstname", SqlDbType.VarCh ar, 50)
firstname.Value = txtFirstName.Te xt.ToString()

Dim lastname As New SqlParameter("@ lastname", SqlDbType.VarCh ar, 50)
lastname.Value = txtLastName.Tex t.ToString()

Dim address As New SqlParameter("@ address", SqlDbType.VarCh ar, 200)
address.Value = txtAddress.Text .ToString()

Dim address2 As New SqlParameter("@ address2", SqlDbType.VarCh ar, 200)
address2.Value = txtAddress2.Tex t.ToString()

Dim address3 As New SqlParameter("@ address3", SqlDbType.VarCh ar, 200)
address3.Value = txtAddress3.Tex t.ToString()

Dim city As New SqlParameter("@ city", SqlDbType.VarCh ar, 50)
city.Value = txtCity.Text.To String()

Dim country As New SqlParameter("@ country", SqlDbType.VarCh ar, 50)
country.Value = ddlCountry.Text .ToString()

Dim province As New SqlParameter("@ province", SqlDbType.VarCh ar, 50)
province.Value = txtProvince.Tex t.ToString()

Dim postalcode As New SqlParameter("@ postalcode", SqlDbType.BigIn t)
postalcode.Valu e = CInt(txtPostalC ode.Text.ToStri ng())

Dim phone As New SqlParameter("@ phone", SqlDbType.VarCh ar, 50)
phone.Value = txtPhone.Text.T oString()

Dim email As New SqlParameter("@ email", SqlDbType.VarCh ar, 50)
email.Value = txtEmail1.Text. ToString()

Dim how As New SqlParameter("@ how", SqlDbType.VarCh ar, 200)
how.Value = txtHow.Text.ToS tring()

cmd.Parameters. Add(loginid)
cmd.Parameters. Add(password)
cmd.Parameters. Add(confirmpass word)
cmd.Parameters. Add(firstname)
cmd.Parameters. Add(lastname)
cmd.Parameters. Add(address)
cmd.Parameters. Add(address2)
cmd.Parameters. Add(address3)
cmd.Parameters. Add(city)
cmd.Parameters. Add(country)
cmd.Parameters. Add(province)
cmd.Parameters. Add(postalcode)
cmd.Parameters. Add(phone)
cmd.Parameters. Add(email)
cmd.Parameters. Add(how)

cmd.ExecuteNonQ uery()

Catch
End Try
End Sub

Hi

I got the sol for above issue. The thing is I have changed the table name again in the above stored procedure and I have n't executed it. Thats why.

Now for Login form I created the stored procedure as

set ANSI_NULLS ON
set QUOTED_IDENTIFI ER ON
go

ALTER PROCEDURE [dbo].[Pro_Login1]

@loginid varchar(50),
@password varchar(50)

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

select count(loginid) from login where loginid=(@login id) and password=(@pass word)
END

In my front end I have given the coding as

Protected Sub imgLogin_Click( ByVal sender As Object, ByVal e As System.Web.UI.I mageClickEventA rgs) Handles imgLogin.Click

Dim con As SqlConnection
Dim cmd As New SqlCommand
Dim str As String
Dim rd As SqlDataReader
str = "user id=sa;password= cast;database=j sc;server=AUROR A-SERVER"
con = New SqlConnection(s tr)
Try
con.Open()
cmd.CommandType = CommandType.Sto redProcedure
cmd.CommandText = " Pro_Login1 "
cmd.Connection = con

Dim loginid As New SqlParameter("@ loginid", SqlDbType.VarCh ar, 50)
loginid.Value = txtLoginId.Text .ToString()

Dim password As New SqlParameter("@ password", SqlDbType.VarCh ar, 50)
password.Value = txtPassword.Tex t.ToString()

cmd.Parameters. Add(loginid)
cmd.Parameters. Add(password)
cmd.ExecuteNonQ uery()
Dim cnt As Int16
cnt = cmd.ExecuteScal ar()
If cnt <= 0 Then
Response.Write( "The login or password you entered does not match our records. Please check your entry and try again.")
Else
'Response.Write ("Authorise user")
Response.Redire ct("Default3.as px")
End If
Catch ex As Exception
End Try
End Sub

Using Stored Procedure

Using Stored Procedure

Comment