Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Exec sql stored Proc from vb Expand / Collapse
Author
Message
Posted Friday, March 1, 2013 5:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 1, 2013 11:19 AM
Points: 4, Visits: 9
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?
Post #1425451
Posted Friday, March 1, 2013 6:38 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:46 AM
Points: 7,089, Visits: 6,898
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.

Post #1425472
Posted Friday, March 1, 2013 11:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 1, 2013 11:19 AM
Points: 4, Visits: 9
Hi David Burrows
Thank u so much, it seems that it works
Post #1425664
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse