sqlcommand

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to come up with a generic way of generating an insert statement
widh command builer then additional parameter to the insert command so I can
get the id of new records back. Once I get the inserrt command from sql
command builder any changes I make do not get reflected when the intert
command runs. i have to copy the information to a new sqlcommand and use
it. If I query the commandtext it has been updated but the updated version
does not run. Any ideas why the changes to not get updated. I am
updating a sql 2005 server with this. The following code works but I am
looking for a way to make the changes without needing a second sqlcommand

private sub updrec()

Dim cn As New SqlConnection("Data Source=localhost;Integrated
Security=SSPI;Initial Catalog=northwind")

Dim sql As String = "select CategoryId,CategoryName,Description from
categories where CategoryID='105'"
Dim sqlData As New SqlDataAdapter(sql, cn)
Dim prm As SqlParameter

Dim sqlBld As New SqlCommandBuilder(sqlData)
Dim OsqlCmd As SqlCommand = sqlBld.GetInsertCommand

cn.Open()

Dim nSqlCmd As New SqlCommand
OsqlCmd.CommandText += " set @CategoryID = SCOPE_IDENTITY()"

prm = New SqlParameter("@CategoryID", SqlDbType.Int, 0, "CategoryID")
prm.Direction = ParameterDirection.Output
OsqlCmd.Parameters.Add(prm)

nSqlCmd = OsqlCmd.Clone

Dim sqlCmd As New SqlCommand(OsqlCmd.CommandText, cn)
CopyParams(OsqlCmd, sqlcmd) '
sqlData.InsertCommand = sqlCmd
sqlData.UpdateCommand = sqlBld.GetUpdateCommand
sqlData.DeleteCommand = sqlBld.GetDeleteCommand


TextBox2.Text = sqlData.InsertCommand.CommandText

Dim myDs As New DataSet
sqlData.Fill(myDs, "Categories")

myDs.Tables(0).Rows(0).Item("Description") = "freddy"

Dim mYrow As DataRow
mYrow = myDs.Tables(0).NewRow
mYrow.Item("categoryname") = "cat1"
mYrow.Item("description") = "Desc1"
myDs.Tables(0).Rows.Add(mYrow)

mYrow = myDs.Tables(0).NewRow
mYrow.Item("categoryname") = "cat1"
mYrow.Item("description") = "Desc1"
myDs.Tables(0).Rows.Add(mYrow)

sqlData.Update(myDs, "Categories")
'myDs.GetChanges()


end sub
Private Sub CopyParams(ByVal oldCmd As SqlCommand, ByRef NewSqlCmd As
SqlCommand)

Dim oParam As SqlParameter
Dim NPrm As SqlParameter
For Each oParam In oldCmd.Parameters
NPrm = New SqlParameter(oParam.ParameterName, oParam.SqlDbType,
oParam.Size, oParam.SourceColumn)
NPrm.Direction = oParam.Direction
NewSqlCmd.Parameters.Add(NPrm)

Next

End Sub


Thank You
 
The SqlCommandBuilder is a class intended to be used by the IDE tools--it
has been abandoned by most developers at this point due to numerous
issues--such as the issue you describe.
Consider that the CommandBuilder does not build its commands until the last
possible moment and then does not rebuild them until the instance is
rebuilt.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------
 
Back
Top