[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 259
  • Last Modified:

Learning Sql Stroed Procedures by example

Hi,

I am trying to learn SqlServer stored procedures, and, for me, the best way to learn is by example.

I use the following query from an asp.net form to select a name and a password from SqlServer table (students). Will you give me the code for stored procedure that would do the same?

Thanks:

comm = New SqlCommand("Select userName,userPassword from Students where userName=@userName And userPassword=@userPassword", con)
        comm.Parameters.AddWithValue("@userName", System.Data.SqlDbType.NVarChar)
        comm.Parameters("@userName").Value = myUser
        comm.Parameters.AddWithValue("@userPassword", System.Data.SqlDbType.NVarChar)
        comm.Parameters("@userPassword").Value = myPassword

myUser and myPassword are two string variables carrying the name and password of a user trying to login.
0
adamtrask
Asked:
adamtrask
  • 3
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
ASP.net code updated:

comm = New SqlCommand("your_example_proc", con)
comm.CommandType = CommandType.StoredProc
        comm.Parameters.AddWithValue("@userName", System.Data.SqlDbType.NVarChar)
        comm.Parameters("@userName").Value = myUser
        comm.Parameters.AddWithValue("@userPassword", System.Data.SqlDbType.NVarChar)
        comm.Parameters("@userPassword").Value = myPassword

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
procedure code:
create procedure your_example_proc
 @userName nvarchar(100)
,@userPassword nvarchar(100)
as
begin
 Select userName,userPassword 
   from Students 
  where userName=@userName 
    And userPassword=@userPassword
end

Open in new window

0
 
adamtraskAuthor Commented:
Getting the following error:

Could not find stored procedure 'spUserPasswrod'.

I made a little change to the second line of the asp.net code :
 comm.CommandType = System.Data.CommandType.StoredProcedure

and gave the procedure the name spUserPasswrod.

When I executed the command on the server I got a success message at the end.
I don't know what is wrong
0
 
adamtraskAuthor Commented:
Thank you many, many times.....

It's working now.... the problem was I did not select the proper database when I created the procedure.

Thanks a lot
0
 
adamtraskAuthor Commented:
Very helpful.... thanks again.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now