I just installed sql server 2005 on my machine. I've been using Access for some time and have created my stored procedures easily in it. However, this sql server 2005 is so complicated. In Access, all I had to do was "Create a New Query", write my sql statement, name and save it.
However, I am having so much trouble with this sql server. For example, when I create a stored procedure in sql server, it has a .sql extension. I am assuming the stored procedure is a file. However, after saving it, I expand my database, expand programmability, and expand stored procedures, yet my new stored procedure is not there. It's placed in a file called projects. I need to access this stored procedure from code, but in visual studio, I get an error message, "Cannot find stored procedure sp_Roster".
Here is the code I used to access my stored procedure from vb.net. I thought it would work but it didnt
Dim comm As New SqlCommand
Dim strsql As String
Dim strconn As String
strsql = "sp_Roster"
strconn = "server=Home; user=sa; pwd=juwar74; database=Book;"
With comm
.Connection = New SqlConnection(strconn)
.CommandText = strsql
.CommandType = CommandType.StoredProcedure
With .Parameters.Add("TeacherID", SqlDbType.Char)
.Value = "DawsMark@.aol.com"
End With
With .Parameters.Add("ClassID", SqlDbType.Int)
.Value = CInt(classid)
End With
With .Parameters.Add("sID", SqlDbType.Int)
.Value = ssID
End With
With .Parameters.Add("sLastName", SqlDbType.Char)
.Value = lastname
End With
With .Parameters.Add("sFirstName", SqlDbType.Char)
.Value = firstname
End With
With .Parameters.Add("sMiddleName", SqlDbType.Char)
.Value = middlename
End With
With .Parameters.Add("Student", SqlDbType.Char)
.Value = fullname
End With
With .Parameters.Add("Password", SqlDbType.Char)
.Value = password
End With
.Connection.Open()
.ExecuteNonQuery()
With comm.Connection
If .State = ConnectionState.Open Then
.Close()
End If
End With
End With
Here is my procedure that I created and that was saved as sp_Roster.sql in sql server
CREATE PROCEDURE sp_Roster
AS
BEGIN
SET NOCOUNT ON;
-- Insert statements for procedure here
INSERT INTO Roster (TeacherID, ClassID, sID, sLastName, sFirstName, sMiddleName, Student, Password) VALUES (@.TeacherID, @.ClassID, @.sID, @.sLastName, @.sFirstName, @.sMiddleName, @.Student, @.Password)
END
GO
Why isn't ado.net finding it in sql server. Is the sqlstr correct?
In order to save a stored procedure to the DB in SQL 2005 you need to execute this code instead of saving the text as a file. Just click the "Execute" button and you should be ok. Another thing however, is that you have not declared your variables. You will not be able to execute/save your stored procedure without this.
CREATEPROCEDURE sp_Roster
@.TeacherIDint,
@.ClassIDint,
@.sIDint,
@.sLastNamenvarchar(100),
@.sFirstNamenvarchar(100),
@.sMiddleNamenvarchar(50),
@.Studentnvarchar(100),
@.Passwordnvarchar(100)
AS
BEGIN
SETNOCOUNTON;
-- Insert statements for procedure here
INSERTINTO Roster(TeacherID, ClassID,sID, sLastName, sFirstName, sMiddleName, Student,Password)VALUES(@.TeacherID, @.ClassID, @.sID, @.sLastName, @.sFirstName, @.sMiddleName, @.Student, @.Password)
END
GO
|||
Unfortunately, Sql Server doesn't work the same as Access in this way. Wehn you save a query in Sql Server, you are saving the query, but no the query to the database. The script has to be run to actually create your stored procedure. This can be done by pressing F5. However, your query itself needs some work as you have no input parameters to your stored procedure.
sql
