Morning All
I have an application which allows the user to select a range of records based on a search criteria. I need the ability to update all of those records based on single or multiple field value changes. The form has c40 fields that are amendable.
I have have determined which fields have been amended but need to be able to update an SQL database only with the fields that have been amended! As far as I can determine, I need to be able to create a stored procedure dynamically within the application, apply the update and then remove the stored procedure.
I have found an example of how to do this directly from Microsoft but it's creating some errors which I cannot resolve! The code is as below:
'Connect to the local, default instance of SQL Server.
Dim srv As Server
srv = New Server
'Reference the AdventureWorks2012 2008R2 database.
Dim db As Database
db = srv.Databases("AdventureWorks2012")
'Define a StoredProcedure object variable by supplying the parent database and name arguments in the constructor.
Dim sp As StoredProcedure
sp = New StoredProcedure(db, "GetLastNameByEmployeeID")
'Set the TextMode property to false and then set the other object properties.
sp.TextMode = False
sp.AnsiNullsStatus = False
sp.QuotedIdentifierStatus = False
'Add two parameters.
Dim param As StoredProcedureParameter
param = New StoredProcedureParameter(sp, "@empval", DataType.Int)
sp.Parameters.Add(param)
Dim param2 As StoredProcedureParameter
param2 = New StoredProcedureParameter(sp, "@retval", DataType.NVarChar(50))
param2.IsOutputParameter = True
sp.Parameters.Add(param2)
'Set the TextBody property to define the stored procedure.
Dim stmt As String
stmt = " SELECT @retval = (SELECT LastName FROM Person.Person AS p JOIN HumanResources.Employee AS e ON p.BusinessEntityID = e.BusinessEntityID AND e.BusinessEntityID = @empval )"
sp.TextBody = stmt
'Create the stored procedure on the instance of SQL Server.
sp.Create()
'Modify a property and run the Alter method to make the change on the instance of SQL Server.
sp.QuotedIdentifierStatus = True
sp.Alter()
'Remove the stored procedure.
sp.Drop()
I have imported the Microsoft.SQLServer namespace but the first line containing 'Server' generates a BC30182 type expected error. Similarly, the database and storedprocedure lines are also causing issues!
Can someone please advise what I am missing, are there further namespaces I need or references I need to include....I've spent hours on this and not getting anywhere!
Many, many thanks
Steve
I have an application which allows the user to select a range of records based on a search criteria. I need the ability to update all of those records based on single or multiple field value changes. The form has c40 fields that are amendable.
I have have determined which fields have been amended but need to be able to update an SQL database only with the fields that have been amended! As far as I can determine, I need to be able to create a stored procedure dynamically within the application, apply the update and then remove the stored procedure.
I have found an example of how to do this directly from Microsoft but it's creating some errors which I cannot resolve! The code is as below:
'Connect to the local, default instance of SQL Server.
Dim srv As Server
srv = New Server
'Reference the AdventureWorks2012 2008R2 database.
Dim db As Database
db = srv.Databases("AdventureWorks2012")
'Define a StoredProcedure object variable by supplying the parent database and name arguments in the constructor.
Dim sp As StoredProcedure
sp = New StoredProcedure(db, "GetLastNameByEmployeeID")
'Set the TextMode property to false and then set the other object properties.
sp.TextMode = False
sp.AnsiNullsStatus = False
sp.QuotedIdentifierStatus = False
'Add two parameters.
Dim param As StoredProcedureParameter
param = New StoredProcedureParameter(sp, "@empval", DataType.Int)
sp.Parameters.Add(param)
Dim param2 As StoredProcedureParameter
param2 = New StoredProcedureParameter(sp, "@retval", DataType.NVarChar(50))
param2.IsOutputParameter = True
sp.Parameters.Add(param2)
'Set the TextBody property to define the stored procedure.
Dim stmt As String
stmt = " SELECT @retval = (SELECT LastName FROM Person.Person AS p JOIN HumanResources.Employee AS e ON p.BusinessEntityID = e.BusinessEntityID AND e.BusinessEntityID = @empval )"
sp.TextBody = stmt
'Create the stored procedure on the instance of SQL Server.
sp.Create()
'Modify a property and run the Alter method to make the change on the instance of SQL Server.
sp.QuotedIdentifierStatus = True
sp.Alter()
'Remove the stored procedure.
sp.Drop()
I have imported the Microsoft.SQLServer namespace but the first line containing 'Server' generates a BC30182 type expected error. Similarly, the database and storedprocedure lines are also causing issues!
Can someone please advise what I am missing, are there further namespaces I need or references I need to include....I've spent hours on this and not getting anywhere!
Many, many thanks
Steve