Hello Shalni
There are a few ways to do it, I'll show you a function that returns an ADO
Command object with parameters populated for a stored procedure:
Public Function InsertPersonCMD(ByVal MyData As DataTable) As
SqlClient.SqlCommand
Dim MyCMD As New SqlClient.SqlCommand
Dim MyParam As SqlClient.SqlParameter
Try
With MyCMD
..CommandText = "InsertPerson"
..CommandType = CommandType.StoredProcedure
'=================================================
'Parameter Name
FirstName
'=================================================
MyParam = .CreateParameter()
MyParam.ParameterName = "
@Firstname"
MyParam.Direction = ParameterDirection.Input
MyParam.DbType = DbType.String
MyParam.Size = 64
MyParam.Value = MyData.Rows(0).Item("FirstName")
MyCMD.Parameters.Add(MyParam)
'=================================================
'Parameter Name
MiddleName
'=================================================
MyParam = .CreateParameter()
MyParam.ParameterName = "@MiddleName"
MyParam.Direction = ParameterDirection.Input
MyParam.DbType = DbType.String
MyParam.Size = 64
MyParam.Value = MyData.Rows(0).Item("MiddleName")
MyCMD.Parameters.Add(MyParam)
'=================================================
'Parameter Name
LastName
'=================================================
MyParam = .CreateParameter()
MyParam.ParameterName = "@LastName"
MyParam.Direction = ParameterDirection.Input
MyParam.DbType = DbType.String
MyParam.Size = 64
MyParam.Value = MyData.Rows(0).Item("LastName")
MyCMD.Parameters.Add(MyParam)
'=================================================
'Parameter Name
EmailAddress
'=================================================
MyParam = .CreateParameter()
MyParam.ParameterName = "@EmailAddress"
MyParam.Direction = ParameterDirection.Input
MyParam.DbType = DbType.String
MyParam.Size = 64
MyParam.Value = MyData.Rows(0).Item("EmailAddress")
MyCMD.Parameters.Add(MyParam)
'=================================================
'Parameter Name
BusinessID
'=================================================
MyParam = .CreateParameter()
MyParam.ParameterName = "@PersonID"
MyParam.Direction = ParameterDirection.Output
MyParam.DbType = DbType.Int32
MyCMD.Parameters.Add(MyParam)
End With
Return MyCMD
Catch erx As Exception
'=========================
'simple throw. add more
'complex error handling
'as required
'=========================
Throw erx
Finally
'???
End Try
End Function
and here is a function that uses it:
Public Overloads Function InsertPerson(ByVal Person As clsPerson) As
clsPerson
Dim objCommand As SqlClient.SqlCommand
Dim MyTable As DataTable
Dim NewPersonID As Integer
'===========================================================================
'after geting a command object populated with parameters the procedure
'executes the command. If there where no errors from the Insert procedure
'then a new person object populated with the Inserted person data is
returned
'===========================================================================
Try
MyTable = Person.DataSource.Tables(0)
objCommand = Me._objDataCMDs.InsertPersonCMD(MyTable)
objCommand.Connection = Me._objConnect
If Me._objConnect.State = ConnectionState.Closed Then
Me._objConnect.Open()
End If
objCommand.ExecuteNonQuery()
'========================================================
'if a valid NewPersonID (greater than zero) is returned
'then we return a person object populated with the data
'for that new person that was inserted
'========================================================
NewPersonID = objCommand.Parameters("@PersonID").Value
If NewPersonID > 0 Then
Return Me.GetPerson(NewPersonID)
Else
Return Nothing
End If
Catch ex As Exception
Throw ex
Finally
objCommand.Dispose()
objCommand = Nothing
End Try
End Function
Hope this helps.
Ibrahim Malluf
http://www.malluf.com