Exec sql stored Proc from vb

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Exec sql stored Proc from vb

Exec sql stored Proc from vb

Rate Topic

Display Mode

Topic Options

Author

Message

sweniosSqlSeeker

Posted Friday, March 01, 2013 5:49 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, March 01, 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

David Burrows

Posted Friday, March 01, 2013 6:38 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 1:54 AM
Points: 6,350, Visits: 5,356

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

sweniosSqlSeeker

Posted Friday, March 01, 2013 11:38 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, March 01, 2013 11:19 AM
Points: 4, Visits: 9

Hi David Burrows
Thank u so much, it seems that it works Smile

Post #1425664

« Prev Topic | Next Topic »

Permissions