...

View Full Version : Could not find stored procedure



fleewood
12-07-2011, 11:39 PM
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:
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



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

alykins
12-08-2011, 12:14 AM
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


comm.CommandType = System.Data.CommandType.StoredProcedure


when I set up similar situation I set it as



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?


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?

fleewood
12-08-2011, 04:11 PM
sorted it, it wasn't connected to it somehow, i just created a new .sql file in visual studio and it worked. thanks anyway



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum