VB_Wannabe


  1. I created a stored procedure (see snippet below) and the owner is "dbo".
  2. I created a data connection (slcbathena.SLCPrint.AdamsK) using Windows authentication.
  3. I added a new datasource to my application in VS 2005 which created a dataset (slcprintDataSet.xsd).
  4. I opened up the dataset in Designer so I could get to the table adapter.
  5. I selected the table adapter and went to the properties panel.
  6. I changed the DeleteCommand as follows: CommandType = StoredProcedure; CommandText = usp_OpConDeleteDirectory. When I clicked on the Parameters Collection to pull up the Parameters Collection Editor, there was no parameters listed to edit even though there is one defined in the stored procedure. Why

If I create the stored procedure as "AdamsK.usp_OpConDeleteDirectory", the parameters show up correctly in the Parameters Collection Editor. Is there a relationship between the owner name of the stored procedure and the data connection name If so, how can I create a data connection that uses "dbo" instead of "AdamsK" so I can use the stored procedure under owner "dbo"

Any help will be greatly appreciated!

Code Snippet
CREATE PROCEDURE dbo.usp_OpConDeleteDirectory
(
@DirectoryID int
)
AS
SET NOCOUNT ON
DELETE FROM OpConDirectories WHERE (DirectoryID = @DirectoryID)




Re: Trouble accessing SQL Server 2005 stored procedure parameters

Baba urf Sivaji


I dont want to work with the dataadapter when command object is there

u can try like this

cmd = New SqlCommand

cmd.Connection = connectionname

cmd.CommandText = proc_name

cmd.CommandType = CommandType.StoredProcedure

p1 = cmd.CreateParameter

p1 = cmd.Parameters.Add("@DirectoryID", SqlDbType.Int)

p1.Direction = ParameterDirection.Input

p1.Value = control

Try

con.Open()

cmd.ExecuteNonQuery()

Response.Write(p1.Value)

con.Close()

Catch ex As Exception

Response.Write(ex.Message)

con.Close()

End Try