Stored Procedure

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.

NoSpamMgbworld
Tue May 31 09:29:36 CDT 2005

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!
***************************


"sam" wrote:

> 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.
>
>
>
>

ThorKornbrek
Tue May 31 18:22:13 CDT 2005

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/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqlclientsqlcommandclasscommandtypetopic.asp
--
Thor Kornbrek
.Net Developer



"sam" wrote:

> 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.
>
>
>
>