G
Guest
I am confused, What is the deal with ADONET parameters with stored
procedures??. In the 'old' ADO using stored proc. with SQL server, you
simply...
Set Conn1 = New ADODB.Connection
Set Cmd1 = New ADODB.Command
Conn1.Open "Provider=sqloledb;Data Source=(local);Initial Catalog=db;User
ID=sa;Password=;"
Cmd1.ActiveConnection = Conn1
Conn1.CursorLocation = adUseServer
Cmd1.CommandType = adCmdStoredProc
Cmd1.CommandText = "myStoredProc"
Cmd1.Parameters(1).value = somevalue ****ADO 'KNOWS'
about this parameter
Cmd1.Execute ..... simple, it worked *****
With ADONET, it seems not to 'know' about the parameter
Does ADONET force you you to define the parameters twice, once in the stored
proc(like normal) and once in code like:
Dim parameterItemId As New SqlParameter("@ItemId", SqlDbType.Int, 4) '
Parameter name and type here
or does it not force you to do it? It seems very foolish to remove that
awareness ADO used to have.
I can't seemt to get a good answer whether yes or no, and can't find any
documentation on it. I tried this code as a test and sure enough,
it throws up without a design time defined param:
In and ASP.NET project (VB) I have a SQLconnection called SQLDB and a
SQLCommand called SQLCmd
placed on a component called SQLComponent... I have a test Stored Proc on
SQL server that accepts one param for test...
I have a webform that has the code in a class
Imports System.data.Sqlclient
class def..
Protected tmpSQL As New SQLComponent
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
tmpSQL.SQLDB.Open() ********This Works
tmpSQL.SQLcmd.CommandType = CommandType.StoredProcedure ******This Works
tmpSQL.SQLcmd.CommandText = "mp_GetTestData" ****This Works
tmpSQL.SQLcmd.Parameters(1).Value = "%"
*****BOOM***** says parameter not in SQLparameter collection ???? HUNH?
IFI leave it out though, it pukes saying parameter is missing???
Dim myreader As SqlDataReader = tmpSQL.SQLcmd.ExecuteReader
End sub
end class def...
It sure would be time consuming to RE-CREATE every parameter in code again
so the engine knows what the params are. Am I doing something
wrong, or did Microsoft remove the parameter awareness from the new ADONET
framework?
Anybody have an answer for this, would be appreciated.. K
procedures??. In the 'old' ADO using stored proc. with SQL server, you
simply...
Set Conn1 = New ADODB.Connection
Set Cmd1 = New ADODB.Command
Conn1.Open "Provider=sqloledb;Data Source=(local);Initial Catalog=db;User
ID=sa;Password=;"
Cmd1.ActiveConnection = Conn1
Conn1.CursorLocation = adUseServer
Cmd1.CommandType = adCmdStoredProc
Cmd1.CommandText = "myStoredProc"
Cmd1.Parameters(1).value = somevalue ****ADO 'KNOWS'
about this parameter
Cmd1.Execute ..... simple, it worked *****
With ADONET, it seems not to 'know' about the parameter
Does ADONET force you you to define the parameters twice, once in the stored
proc(like normal) and once in code like:
Dim parameterItemId As New SqlParameter("@ItemId", SqlDbType.Int, 4) '
Parameter name and type here
or does it not force you to do it? It seems very foolish to remove that
awareness ADO used to have.
I can't seemt to get a good answer whether yes or no, and can't find any
documentation on it. I tried this code as a test and sure enough,
it throws up without a design time defined param:
In and ASP.NET project (VB) I have a SQLconnection called SQLDB and a
SQLCommand called SQLCmd
placed on a component called SQLComponent... I have a test Stored Proc on
SQL server that accepts one param for test...
I have a webform that has the code in a class
Imports System.data.Sqlclient
class def..
Protected tmpSQL As New SQLComponent
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
tmpSQL.SQLDB.Open() ********This Works
tmpSQL.SQLcmd.CommandType = CommandType.StoredProcedure ******This Works
tmpSQL.SQLcmd.CommandText = "mp_GetTestData" ****This Works
tmpSQL.SQLcmd.Parameters(1).Value = "%"
*****BOOM***** says parameter not in SQLparameter collection ???? HUNH?
IFI leave it out though, it pukes saying parameter is missing???
Dim myreader As SqlDataReader = tmpSQL.SQLcmd.ExecuteReader
End sub
end class def...
It sure would be time consuming to RE-CREATE every parameter in code again
so the engine knows what the params are. Am I doing something
wrong, or did Microsoft remove the parameter awareness from the new ADONET
framework?
Anybody have an answer for this, would be appreciated.. K