请教问题：如何正确调用存储过程及返回参数，集！解决立刻给分！

Question

下面有一段调用的代码，你自己参考一下吧！Dim sql As String = System.Configuration.ConfigurationSettings.AppSettings("server1")
                    Dim cn As New System.Data.SqlClient.SqlConnection(sql)
                    Dim cm As New System.Data.SqlClient.SqlCommand("stock_adj_add", cn)
                    cm.CommandType = CommandType.StoredProcedure
                    Dim str_aj_no As New SqlParameter("@aj_no", SqlDbType.VarChar, 9)
                    str_aj_no.Direction = ParameterDirection.Output
                    cm.Parameters.Add(str_aj_no)
                    Dim str_center_code As New SqlParameter("@center_code", SqlDbType.VarChar, 2)
                    str_center_code.Direction = ParameterDirection.Input
                    cm.Parameters.Add(str_center_code)
                    Dim str_aj_date As New SqlParameter("@aj_date", SqlDbType.DateTime)
                    str_aj_date.Direction = ParameterDirection.Input
                    cm.Parameters.Add(str_aj_date)
                    Dim str_pa_part_no As New SqlParameter("@pa_part_no", SqlDbType.VarChar, 40)
                    str_pa_part_no.Direction = ParameterDirection.Input
                    cm.Parameters.Add(str_pa_part_no)
                    Dim str_aj_onhand_bf As New SqlParameter("@aj_onhand_bf", SqlDbType.Int)
                    str_aj_onhand_bf.Direction = ParameterDirection.Input
                    cm.Parameters.Add(str_aj_onhand_bf)
                    Dim str_aj_defect_bf As New SqlParameter("@aj_defect_bf", SqlDbType.Int)
                    str_aj_defect_bf.Direction = ParameterDirection.Input
                    cm.Parameters.Add(str_aj_defect_bf)
                    Dim str_aj_onhand_af As New SqlParameter("@aj_onhand_af", SqlDbType.Int)
                    str_aj_onhand_af.Direction = ParameterDirection.Input
                    cm.Parameters.Add(str_aj_onhand_af)
                    Dim str_aj_defect_af As New SqlParameter("@aj_defect_af", SqlDbType.Int)
                    str_aj_defect_af.Direction = ParameterDirection.Input
                    cm.Parameters.Add(str_aj_defect_af)
                    Dim str_aj_reason As New SqlParameter("@aj_reason", SqlDbType.VarChar, 100)
                    str_aj_reason.Direction = ParameterDirection.Input
                    cm.Parameters.Add(str_aj_reason)
                    Dim str_aj_part_key As New SqlParameter("@aj_part_key", SqlDbType.VarChar, 10)
                    str_aj_part_key.Direction = ParameterDirection.Input
                    cm.Parameters.Add(str_aj_part_key)
                    Dim str_resort As New SqlParameter("@resort", SqlDbType.Int)
                    str_resort.Direction = ParameterDirection.Output
                    cm.Parameters.Add(str_resort)
                    str_center_code.Value = Request.Cookies("gccenter").Value.ToString
                    str_aj_date.Value = Format(System.DateTime.Now, "yyyy-MM-dd hh:mm")
                    str_pa_part_no.Value = pa_part_no.Text
                    str_aj_onhand_bf.Value = aj_onhand_bf.Text
                    str_aj_defect_bf.Value = aj_defective_bf.Text
                    str_aj_onhand_af.Value = aj_onhand_af.Text
                    str_aj_defect_af.Value = aj_defective_af.Text
                    str_aj_reason.Value = aj_reason.Text
                    str_aj_part_key.Value = aj_part_key.Text
                    cm.Connection.Open()
                    cm.ExecuteNonQuery()
                    aj_no.Text = cm.Parameters("@aj_no").Value.ToString  '返回的参数

Answer 1

seeUsing Stored Procedures with a Command
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconusingstoredprocedureswithcommand.asp
Dim sampleCMD As SqlCommand = New SqlCommand("SampleProc", nwindConn)
sampleCMD.CommandType = CommandType.StoredProcedureDim sampParm As SqlParameter = sampleCMD.Parameters.Add("RETURN_VALUE", SqlDbType.Int)
sampParm.Direction = ParameterDirection.ReturnValuesampParm = sampleCMD.Parameters.Add("@InputParm", SqlDbType.NVarChar, 12)
sampParm.Value = "Sample Value"sampParm = sampleCMD.Parameters.Add("@OutputParm", SqlDbType.NVarChar, 28)
sampParm.Direction = ParameterDirection.OutputnwindConn.Open()Dim sampReader As SqlDataReader = sampleCMD.ExecuteReader()Console.WriteLine("{0}, {1}", sampReader.GetName(0), sampReader.GetName(1))Do While sampReader.Read()
  Console.WriteLine("{0}, {1}", sampReader.GetInt32(0), sampReader.GetString(1))
LoopsampReader.Close()
nwindConn.Close()Console.WriteLine(" @OutputParm: {0}", sampleCMD.Parameters("@OutputParm").Value)
Console.WriteLine("RETURN_VALUE: {0}", sampleCMD.Parameters("RETURN_VALUE").Value)

Answer 2

wei131420(我为.net狂) 请问一下
Dim sql As String = System.Configuration.ConfigurationSettings.AppSettings("server1")
中的server1是否连接数据库字符串修改程序如下
        Dim conn_string As String = "Provider=MSDAORA.1;User ID=fprod; PassWord=fprod;Data Source=ORCL.XTGISSVR;Persist Security Info=False"
        Dim sql As String = System.Configuration.ConfigurationSettings.AppSettings("conn_string")
        Dim cn As New System.Data.SqlClient.SqlConnection(sql)
        Dim cm As New System.Data.SqlClient.SqlCommand("pr_jyzlhlb", cn)
        cm.CommandType = CommandType.StoredProcedure
        Dim p_ps As New SqlParameter("@p_ps", SqlDbType.VarChar, 9)
        p_ps.Direction = ParameterDirection.Output
        cm.Parameters.Add(p_ps)
        cm.Connection.Open()
        cm.ExecuteNonQuery()
        test.Text = cm.Parameters("@p_ps").Value.ToString  '返回的参数报错如下：ConnectionString 属性尚未初始化。 
说明: 执行当前 Web 请求期间，出现未处理的异常。请检查堆栈跟踪信息，以了解有关该错误以及代码中导致错误的出处的详细信息。 异常详细信息: System.InvalidOperationException: ConnectionString 属性尚未初始化。
源错误: 
行 39：         p_ps.Direction = ParameterDirection.Output
行 40：         cm.Parameters.Add(p_ps)
行 41：         cm.Connection.Open() 

Answer 3

這樣寫:Dim cn As New System.Data.SqlClient.SqlConnection(conn_string)
他是把連接字符串寫在WEB.CONFIG中的
順便問一句,樓主用的oracle嘛?

Answer 4

Dim conn_string As String = "Provider=MSDAORA.1;User ID=fprod; PassWord=fprod;Data Source=ORCL.XTGISSVR;Persist Security Info=False"
        Dim cn As New System.Data.SqlClient.SqlConnection(conn_string )
        Dim cm As New System.Data.SqlClient.SqlCommand("pr_jyzlhlb", cn)
        cm.CommandType = CommandType.StoredProcedure
        Dim p_ps As New SqlParameter("@p_ps", SqlDbType.VarChar, 9)
        p_ps.Direction = ParameterDirection.Output
        cm.Parameters.Add(p_ps)
        cm.Connection.Open()
        cm.ExecuteNonQuery()
  cm.Connection.close()
        test.Text = cm.Parameters("@p_ps").Value.ToString

Answer 5

CREATE PROCEDURE pr_jyzlhlb 
@p_ps varchar(50) output
 AS
begin
    set @p_ps='aaa'
end 
go