Stored Procedure

[sam]

I want to convert standard SQL update syntax into calling stored procedure.


Normal Coding
-------------
Dim constr As String = "server='SQLSVR'; user id='USER';
password='password'; Database='ERP'"
Dim sqlcon As System.Data.SqlClient.sqlconnection = New
System.Data.SqlClient.sqlconnection(constr)
Dim sqlcmd As System.Data.SqlClient.SqlCommand = New SqlCommand()

sqlcmd.CommandText = "update F0411A set docnum = 0, doctype = ' ' where
docnum is null and doctype is null"

sqlcmd.Connection = sqlcon
Try
sqlcon.Open()
sqlcmd.ExecuteNonQuery()
Finally
sqlcon.Close()
sqlcon.Dispose()
End Try

constr = Nothing
sqlcmd = Nothing


Coding Calling Stored Procedure
-------------------------------
Try
sqlcmd = sqlcon.CreateCommand
sqlcmd.CommandText = "[Update_F0411A]"
sqlcmd.CommandType = CommandType.StoredProcedure
Finally
sqlcon.Close()
sqlcon.Dispose()
End Try


Stored Procedure
----------------
Create Procedure "Update_F0411A" as
update F0411A set docnum = 0,
doctype = ' '
where docnum is null and doctype is null

Please advise the coding whether I do correctly or not.

Many thanks.

 

[Guest]

It looks fine to me. Since you do not have procedure parameters, simply
moving the SQL statement into the stored proc should work, as you have done.


--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

 

[Guest]

You would need to call one of the execute methods still, ExecuteNonQuery
since this returns no rows.

I do not see that in your new routine.

This is where I found documentaion on the matter.

http://msdn.microsoft.com/library/d...asqlclientsqlcommandclasscommandtypetopic.asp