sqlcommand

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
.

Relevant Pages

  • Re: Multiple table in dataset query
    ... Thanks Kerry for the pointers on the combobox, I will look into this in the ... Update command and its parameters, but I am a bit lost in actually ... Dim UpdateStaffCmd As New OleDb.OleDbCommand ... Private Sub frmStaff_Load(ByVal sender As System.Object, ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: sqlcommand
    ... Hitchhiker's Guide to Visual Studio and SQL Server ... widh command builer then additional parameter to the insert command so I can ... looking for a way to make the changes without needing a second sqlcommand ... Dim cn As New SqlConnection("Data Source=localhost;Integrated ...
    (microsoft.public.dotnet.framework.adonet)
  • RE: Run a Stored Procedure from Excel
    ... You also want to find out if you have a rsDW variable defined. ... Your code had a semicolon between the 2nd command and the ... Dim cnnDW As ADODB.Connection ... Set cnnDW = New ADODB.Connection ...
    (microsoft.public.excel.programming)
  • Re: Frustrated with learning add command code...
    ... Add the following code to the command button ... Private Sub CommandButton1_Click ... Dim wb as Workbook ... 'find first empty row in database ...
    (microsoft.public.excel.programming)
  • Re: Using CommandBuilder to update an Access table from a DataGrid
    ... update command properly. ... Private Sub Form1_Load(ByVal sender As Object, ... Dim myDA = New OleDbDataAdapter("SELECT * FROM ...
    (microsoft.public.dotnet.framework.adonet)
Loading