March 1, 2013 at 5:49 am
Hi all
Im trying to execute a stored procedure that doesn't return any value, from vb, and it doesn't work.
this is the code I wrote:
Dim pTargetDB As New SqlClient.SqlParameter
Dim pSchema As New SqlClient.SqlParameter
Dim pTargetTbl As New SqlClient.SqlParameter
Dim pTargetColumns As New SqlClient.SqlParameter
Dim pValues As New SqlClient.SqlParameter
Dim cmdExecProc As New SqlClient.SqlCommand
Dim pError As New SqlClient.SqlParameter
Try
cmdExecProc.CommandText = "dbo.sp_InsertFromTblToAnother"
cmdExecProc.CommandType = CommandType.StoredProcedure
'@strTargetDB
pTargetDB.Value = frmDisplayTargetDatabases.strDBName
pTargetDB.Direction = ParameterDirection.Input
pTargetDB.ParameterName = "@strTargetDB"
pTargetDB.SqlDbType = SqlDbType.NVarChar
cmdExecProc.Parameters.Add(pTargetDB)
'@strTargetSchema
pSchema.Direction = ParameterDirection.Input
pSchema.ParameterName = "@strTargetSchema"
pSchema.SqlDbType = SqlDbType.NVarChar
pSchema.Value = "dbo"
cmdExecProc.Parameters.Add(pSchema)
'@strTargetTable
pTargetTbl.Direction = ParameterDirection.Input
pTargetTbl.ParameterName = "@strTargetTable"
pTargetTbl.SqlDbType = SqlDbType.NVarChar
pTargetTbl.Value = frmDisplayTargetDatabases.strTagetTable
cmdExecProc.Parameters.Add(pTargetTbl)
'@strTargetColumns
pTargetColumns.Direction = ParameterDirection.Input
pTargetColumns.ParameterName = "@strTargetColumns"
pTargetColumns.SqlDbType = SqlDbType.NVarChar
pTargetColumns.Value = frmDisplayTargetDatabases.strTargetColumns
cmdExecProc.Parameters.Add(pTargetColumns)
'@strValues
pValues.Direction = ParameterDirection.Input
pValues.ParameterName = "@strValues"
pValues.SqlDbType = SqlDbType.NText
pValues.Value = "1,''abcdefg''"
cmdExecProc.Parameters.Add(pValues)
'
pError.Direction = ParameterDirection.ReturnValue
pError.ParameterName = "@strError"
pError.SqlDbType = SqlDbType.NVarChar
cmdExecProc.Parameters.Add(pError)
cmdExecProc.Connection = Form1.cnSqlConnection
'Dim sqlTransaction As SqlClient.SqlTransaction = Form1.cnSqlConnection.BeginTransaction()
'cmdExecProc.Transaction = sqlTransaction
'cmdExecProc.ExecuteNonQuery()
'sqlTransaction.Commit()
If cmdExecProc.ExecuteScalar.Equals(True) Then
MsgBox("executed")
Else
MsgBox("notexecuted")
End If
Catch ex As Exception
MsgBox(ex.Message)
MsgBox(cmdExecProc.Parameters.Count.ToString)
MsgBox(pTargetDB.Value)
End Try
------------------------------------------------------------
The Result Was the following message: "notexecuted"
with no returned errors, can any body help?
March 1, 2013 at 6:38 am
Your code
If cmdExecProc.ExecuteScalar.Equals(True) Then
is comparing two objects, in this case the first column of the first row returned (in you case nothing) and the value True (Boolean) and will always be false and therefore you get the result you stated.
ExecuteNonQuery will throw an exception if an error occurs. No exception = successful execution.
Note you can test the value of your error parameter after ExecuteNonQuery if the procedure returns success/fail via this parameter.
Far away is close at hand in the images of elsewhere.
Anon.
March 1, 2013 at 11:38 am
Hi David Burrows
Thank u so much, it seems that it works 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply