Hello, I hope someone could help me out here,

I have the following stored procedure in an SQL server database:

CREATE PROCEDURE sp_deleteEvent (
@eventID SmallInt,
@lastUpdatedOn SmallDateTime,
@lastUpdatedBy nVarChar(15)
)
AS
begin
    update event
    set
       lastUpdateOn=@lastUpdatedOn,
       lastUpdateBy= @lastUpdatedBy,
       rowStatus= 0
    where eventID = @eventID
end
GO

I want to pass the three parameters @eventID, @lastUpdatedOn and @lastUpdatedBy to it from a vb.net script that is attached as ‘code behind’ from an ASP.net page.

Just now my VB script simply shows the required data on screen for testing:

Imports System.Data
Imports System.Data.SqlClient
Partial Class deleteEvent
    Inherits spc_masterclass
    Private Sub deleteEvent(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Selectid.Click
        Dim intEventID As String
        Dim dteUpdatedOn As String = Date.Now
        Dim txtUpdatedBy As String = "testusr"
        intEventID = ddl_Event.SelectedValue
        dteUpdatedOn = dteUpdatedOn.Substring(0, 10)
        If Page.IsValid = True Then
            EventID.Text = intEventID
            UpdatedOn.Text = dteUpdatedOn
        UpdatedBy.Text = txtUpdatedBy
            'display temporary test text
            EventID.Visible = True
            UpdatedOn.Visible = True
            UpdatedBy.Visible = True
        End If
    End Sub
End Class

The ASP simply asked the user to select an item from a list:
The VB then finds the values that are required to be sent to the stored procedure.
Unfortunately I have no idea how to do this!
Is this a simple thing?
I would appreciate it if somebody could help out or point me at some tutorials explaining how to do this…


Thanks.

Look into the sqlcommand object or sqldataadapter object. Then use ExecuteNonQuery()

Jim

Thanks, I managed to get this to work by using the following...

        If Page.IsValid = True Then
            'display temporary test text
            EventID.Text = intEventID
            UpdatedOn.Text = dteUpdatedOn
            UpdatedBy.Text = txtUpdatedBy
            EventID.Visible = True
            UpdatedOn.Visible = True
            UpdatedBy.Visible = True

            'Create a connection to the SQL Server and run the stored procedure
            Dim sql As String = "exec sp_deleteEvent @eventID, @UpdatedOn, @UpdatedBy "
            MyConnection = New SqlConnection("Data Source=160.160.10.14;Initial Catalog=CAT_SYSTEM;Persist Security Info=True;User ID=test;Password=test;Pooling=False")
            Dim myCommand As New SqlCommand(sql, MyConnection)
            myCommand.Parameters.Add(New SqlParameter("@eventID", intEventID))
            myCommand.Parameters.Add(New SqlParameter("@UpdatedOn", dteUpdatedOn))
            myCommand.Parameters.Add(New SqlParameter("@UpdatedBy", txtUpdatedBy))
            MyConnection.Open()
            myCommand.ExecuteNonQuery()
            myCommand.Dispose()
            MyConnection.Close()
        End If

But I was looking for a way to pass paramiters directly to the stored procedure rather than running it from an SQL script!

Thanks for the reply though!

Not sure what you mean by that statement..

You are not running a sql script, you are using the .NET framework to call a SP. There is no shorter way, if that is what you mean.