insertcommand, updatecommand, deletecommand SQL

[EDN Admin]

im trying to write insertcommand, updatecommand, deletecommand for 2 joined tables. i managed the SELECT statement but thats as far as i can get. the statements in the example are from a single table, but i need them to work with Table1 + Table2:

<div style="color:Black;background-color:White; <pre>
<span style="color:Blue; Public <span style="color:Blue; Shared <span style="color:Blue; Function getJoinedTable() <span style="color:Blue; As DataTable
connect()
updatableAdapter(1) = <span style="color:Blue; New OleDb.OleDbDataAdapter(<span style="color:#A31515; "SELECT a.id, a.number, a.subjectName, a.years, a.birthYear, b.note FROM Table1 AS a INNER JOIN Table2 AS b ON a.id = b.id", connection)

<span style="color:Green; Create the InsertCommand.
<span style="color:Blue; Dim command <span style="color:Blue; As <span style="color:Blue; New OleDb.OleDbCommand(<span style="color:#A31515; "INSERT INTO Table1 VALUES(@id, @number, @subjectName, @years, @birthYear)", connection)

<span style="color:Green; Add the parameters for the InsertCommand.
command.Parameters.Add(<span style="color:#A31515; "@id", OleDb.OleDbType.<span style="color:Blue; Integer, 5, <span style="color:#A31515; "id")
command.Parameters.Add(<span style="color:#A31515; "@number", OleDb.OleDbType.VarChar, 50, <span style="color:#A31515; "number")
command.Parameters.Add(<span style="color:#A31515; "@subjectName", OleDb.OleDbType.VarChar, 50, <span style="color:#A31515; "subjectName")
command.Parameters.Add(<span style="color:#A31515; "@years", OleDb.OleDbType.<span style="color:Blue; Integer, 3, <span style="color:#A31515; "years")
command.Parameters.Add(<span style="color:#A31515; "@birthYear", OleDb.OleDbType.<span style="color:Blue; Integer, 4, <span style="color:#A31515; "birthYear")
command.Parameters.Add(<span style="color:#A31515; "@note", OleDb.OleDbType.VarChar, 100, <span style="color:#A31515; "note")

updatableAdapter(1).InsertCommand = command

<span style="color:Green; Create the UpdateCommand.
command = <span style="color:Blue; New OleDb.OleDbCommand( _
<span style="color:#A31515; "UPDATE Table1 SET id = @id, [number] = @number, subjectName = @subjectName, years = @years, " & _
<span style="color:#A31515; "birthYear = @birthYear WHERE id = @oldID", connection)

<span style="color:Green; Add the parameters for the UpdateCommand.
command.Parameters.Add(<span style="color:#A31515; "@id", OleDb.OleDbType.<span style="color:Blue; Integer, 5, <span style="color:#A31515; "id")
command.Parameters.Add(<span style="color:#A31515; "@number", OleDb.OleDbType.VarChar, 50, <span style="color:#A31515; "number")
command.Parameters.Add(<span style="color:#A31515; "@subjectName", OleDb.OleDbType.VarChar, 50, <span style="color:#A31515; "subjectName")
command.Parameters.Add(<span style="color:#A31515; "@years", OleDb.OleDbType.<span style="color:Blue; Integer, 3, <span style="color:#A31515; "years")
command.Parameters.Add(<span style="color:#A31515; "@birthYear", OleDb.OleDbType.<span style="color:Blue; Integer, 4, <span style="color:#A31515; "birthYear")
<span style="color:Blue; Dim parameter <span style="color:Blue; As OleDb.OleDbParameter = command.Parameters.Add( _
<span style="color:#A31515; "@oldID", OleDb.OleDbType.<span style="color:Blue; Integer, 5, <span style="color:#A31515; "id")
parameter.SourceVersion = DataRowVersion.Original

updatableAdapter(1).UpdateCommand = command

<span style="color:Green; Create the DeleteCommand.
command = <span style="color:Blue; New OleDb.OleDbCommand( _
<span style="color:#A31515; "DELETE FROM Table1 WHERE id = @id", connection)

<span style="color:Green; Add the parameters for the DeleteCommand.
parameter = command.Parameters.Add( _
<span style="color:#A31515; "@id", OleDb.OleDbType.<span style="color:Blue; Integer, 5, <span style="color:#A31515; "id")
parameter.SourceVersion = DataRowVersion.Original

updatableAdapter(1).DeleteCommand = command

<span style="color:Blue; Dim dt <span style="color:Blue; As <span style="color:Blue; New DataTable
updatableAdapter(1).Fill(dt)

<span style="color:Blue; Return dt

<span style="color:Blue; End <span style="color:Blue; Function
[/code] <hr class="sig thanks for any help

View the full article