Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New Coder
    Join Date
    Jun 2010
    Posts
    42
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Could not find stored procedure

    Hi, I'm just going through an ASP.NET manual trying to learn VB, there are a couple of parts in the book that tell me to use stored procedures and I cant get them to work. I am definitely connected to my database, everything else works perfect except for stored procedures.

    VB Code
    Code:
    Code:
    Protected Sub UpdateItem(ByVal employeeId As Integer, ByVal newName As String, ByVal newUsername As String)
            Dim conn As SqlConnection
            Dim comm As SqlCommand
            Dim connectionString As String = _
                ConfigurationManager.ConnectionStrings( _
                    "Dorknozzle").ConnectionString
            conn = New SqlConnection(connectionString)
            comm = New SqlCommand("UpdateEmployee", conn)
            comm.CommandType = System.Data.CommandType.StoredProcedure
            comm.Parameters.Add("@EmployeeID", Data.SqlDbType.Int)
            comm.Parameters("@EmployeeID").Value = employeeId
            comm.Parameters.Add("@NewName", Data.SqlDbType.NVarChar, 50)
            comm.Parameters("@NewName").Value = newName
            comm.Parameters.Add("@NewUsername", Data.SqlDbType.NVarChar, 50)
            comm.Parameters("@NewUsername").Value = newUsername
            Try
                conn.Open()
                comm.ExecuteNonQuery()
            Finally
                conn.Close()
            End Try
        End Sub
    End Class
    Database .sql code

    Code:
    CREATE PROCEDURE UpdateEmployee
    (
    @EmployeeID Int,
    @NewName nvarchar(50),
    @NewUsername nvarchar(50)
    )
    AS
    UPDATE Employees
    SET Name = @NewName, Username = @NewUsername
    WHERE EmployeeID = @EmployeeID

  • #2
    Senior Coder alykins's Avatar
    Join Date
    Apr 2011
    Posts
    1,725
    Thanks
    41
    Thanked 191 Times in 190 Posts
    i might be able to help guide you (but I am not well versed in VB- C# is my forte) but I think it might be due to this line
    Code:
    comm.CommandType = System.Data.CommandType.StoredProcedure
    when I set up similar situation I set it as

    Code:
    SqlCommand cmd = new SqlCommand("myStoredProc", conn);
    cmd.CommandType = CommandType.StoredProcedure;
    I don't know if adding the "System.Data." is causing it (and loooking at it I doubt it)... what is the error you are getting? is SQL crashing on you? or is just "nothing happening"

    Have you tried it with a super simple proc that takes no params?
    Code:
    create proc usp_selectAll
    as
    select Name from tbl_YourTable
    and then tried that? maybe the proc is messed up?
    also if error is saying that it cannot find the proc have you literally gone in and clicked on the proc and clicked execute or modify or something with it?

    I code C hash-tag .Net
    Reference: W3C W3CWiki .Net Lib
    Validate: html CSS
    Debug: Chrome FireFox IE

  • #3
    New Coder
    Join Date
    Jun 2010
    Posts
    42
    Thanks
    2
    Thanked 0 Times in 0 Posts
    sorted it, it wasn't connected to it somehow, i just created a new .sql file in visual studio and it worked. thanks anyway


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •