SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Exec sql stored Proc from vb


Exec sql stored Proc from vb

Author
Message
sweniosSqlSeeker
sweniosSqlSeeker
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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?
David Burrows
David Burrows
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16127 Visits: 10103
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.


sweniosSqlSeeker
sweniosSqlSeeker
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 9
Hi David Burrows
Thank u so much, it seems that it works :-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search