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