Question

VS2008环境下VB调用SQL存储过程

命令如下，显示“ExecuteNonQuery 要求已打开且可用的连接。连接的当前状态为已关闭。”请问是怎么回事情，任务比较紧急，请大侠们多多帮忙，谢谢
Dim conn As New SqlClient.SqlConnection
Dim cmd As New SqlClient.SqlCommand
'cmd.CommandType = CommandType.StoredProcedure

'输入参数
Dim ArbitrageVariety As String
With cmd
.Connection = conn
.CommandType = CommandType.StoredProcedure
.Parameters.Add("@tableName1", SqlDbType.VarChar, 50).Value = FirsTableName
.Parameters.Add("@tableName2", SqlDbType.VarChar, 50).Value = SecTableName
.Parameters.Add("@ContractMonth1", SqlDbType.VarChar, 4).Value = " ContractMonthFirs.Text "
.Parameters.Add("@ContractMonth2", SqlDbType.VarChar, 4).Value = " ContractMonthSec.Text "
.Parameters.Add("@TradeVolume", SqlDbType.VarChar, 10).Value = " VolumeLow.Text "
.Parameters.Add("@StartDate", SqlDbType.VarChar, 8).Value = FirsMonthDay
.Parameters.Add("@EndDate", SqlDbType.VarChar, 8).Value = SecMonthDay
.Parameters.Add("@ArbitrageVariety", SqlDbType.VarChar, 1).Value = ArbitrageVariety
.ExecuteNonQuery()
End With

Answer 1

Dim conn As New SqlClient.SqlConnection
Dim cmd As New SqlClient.SqlCommand
'cmd.CommandType = CommandType.StoredProcedure

'输入参数
Dim ArbitrageVariety As String
With cmd
.Connection = conn
.CommandType = CommandType.StoredProcedure
.Parameters.Add("@tableName1", SqlDbType.VarChar, 50).Value = FirsTableName
.Parameters.Add("@tableName2", SqlDbType.VarChar, 50).Value = SecTableName
.Parameters.Add("@ContractMonth1", SqlDbType.VarChar, 4).Value = " ContractMonthFirs.Text "
.Parameters.Add("@ContractMonth2", SqlDbType.VarChar, 4).Value = " ContractMonthSec.Text "
.Parameters.Add("@TradeVolume", SqlDbType.VarChar, 10).Value = " VolumeLow.Text "
.Parameters.Add("@StartDate", SqlDbType.VarChar, 8).Value = FirsMonthDay
.Parameters.Add("@EndDate", SqlDbType.VarChar, 8).Value = SecMonthDay
.Parameters.Add("@ArbitrageVariety", SqlDbType.VarChar, 1).Value = ArbitrageVariety

在此之前打开数据库连接
.Connection.Open()

.ExecuteNonQuery()
End With

Answer 2

楼上同学没理解楼主的问题.

给参考代码:

SqlConnection conndb= new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["conn"]);
conndb.Open();

SqlDataAdapter strselect = new SqlDataAdapter("存储过程的名字",conndb);

strselect.SelectCommand.CommandType = CommandType.StoredProcedure;

DataSet ds = new DataSet();

strselect.Fill(ds);

DlstBuycatalog.DataSource =ds;

DlstBuycatalog.DataBind();

conndb.Close();