Hey,
I think i have just fixed half of the problem.
the code was: oBuild = New SqlClient.SqlDataAdapter
(oAdapter)
So i changed it to the obvious (same as the update):
oBuild = New SqlClient.SqlCommandBuilder(oAdapter)
hehe silly me i should have picked that up.
But i still have one problem.
I still have not done all of the stuff that is required
to the DataAdd() procedure. i think i need to do this (as
it says on the site):
To add a new record to the table in the database, you
first need to add a new row to the DataTable in the
DataSet. You can do this by using the NewRow method to
create a new DataRow. Invoke the BeginEdit method on this
new DataRow so that you can place data into the
appropriate columns. When you have updated all of the
columns, invoke the EndEdit method. Add this new DataRow
to the DataTable in the DataSet by passing the DataRow to
the Add method of the Rows collection in the DataTable.
Use a Command Builder Object to Create SQL
Now that the data is in the DataSet, you can build a
DataAdapter to submit this new data to the database.
Create the DataAdapter by passing in the same SQL
statement that you used to load the DataSet and a
connection string. You then pass the DataAdapter object
to the constructor of the CommandBuilder class and it
creates a new Builder object for you. The
GetInsertCommand method can then be called on this
CommandBuilder object to retrieve a command object that
contains an INSERT statement for this table. The INSERT
statement uses question marks as placeholders for each
piece of data in the DataSet.
When using the SqlCommandBuilder, the INSERT statement
will look something like this:
INSERT
INTO "Products"( "ProductName" , "SupplierID" , "CategoryI
D" ,
"QuantityPerUnit" , "UnitPrice" , "UnitsInStock" , "UnitsO
nOrder" ,
"ReorderLevel" , "Discontinued" ) VALUES ( @ProductName ,
@SupplierID ,
@CategoryID , @QuantityPerUnit , @UnitPrice ,
@UnitsInStock ,
@UnitsOnOrder , @Reorderlevel , @Discontinued )
When using the OleDbCommandBuilder the INSERT statement
will look something like this:
INSERT
INTO "Products"( "ProductName" , "SupplierID" , "CategoryI
D" ,
"QuantityPerUnit" , "UnitPrice" , "UnitsInStock" , "UnitsO
nOrder" ,
"ReorderLevel" , "Discontinued" ) VALUES
( ? , ? , ? , ? , ? , ? , ? , ?
, ? )
Each parameter marker in the statement ("@<columnname>"
for the SqlCommand and "?" for the OleDbCommand)
represents where the data from the DataSet will be placed
when you submit this INSERT statement through the
DataAdapter. This replacement is done automatically by
the DataAdapter and requires no extra programming on your
part. You tell the DataAdapter to submit this INSERT
statement by passing in the DataSet object and the name
of the table in the DataSet that is to be updated to the
Update method of the DataAdapter. After this Update
method is completed, invoke the AcceptChanges method on
the DataSet. This informs the new DataRow that it has
been updated in the database.
-----Original Message-----
The code is the same as it is on the website that i
stated. I have pasted it below anyway
The code for the DataUpdate() works just fine.
Also there is the part on that website for the INSERT
SQLstatement. Do i need to put that in or does that code i
just created with the DataAdd() do all that for me?
If so where do i put it?
Here is what i have anyway:
Private Sub DataAdd()
Dim oAdapter As SqlClient.SqlDataAdapter
Dim oBuild As SqlClient.SqlCommandBuilder
Dim oDR As DataRow
Dim strSQL As String
Dim strConn As String
' Create New DataRow Object From DataSet
oDR = moDS.Tables("Products").NewRow()
oDR.BeginEdit()
' Load new data into row
oDR("ProductName") = txtName.Text
oDR("SupplierID") = CType
(cboSupplier.SelectedItem, _
PDSAListItemNumeric).ID
oDR("CategoryID") = CType
(cboCategory.SelectedItem, _
PDSAListItemNumeric).ID
oDR("QuantityPerUnit") = cboSupplier.Text
oDR("UnitPrice") = CDec(txtPrice.Text)
oDR("UnitsInStock") = CShort(txtInStock.Text)
oDR("UnitsOnOrder") = CShort(txtOnOrder.Text)
oDR("ReorderLevel") = CShort(txtReorder.Text)
oDR("Discontinued") = CBool(chkDisc.Checked)
' Tell DataRow you are done adding data
oDR.EndEdit()
' Add DataRow to DataSet
moDS.Tables("Products").Rows.Add(oDR)
Try
' Get Connection String
strConn = ConnectStringBuild()
' Build SQL String
strSQL = "SELECT * FROM Products "
' Create New DataAdapter
oAdapter = _
New SqlClient.SqlDataAdapter(strSQL,
strConn) ' Create CommandBuilder for Adapter
' This will build INSERT, UPDATE and DELETE
SQL
oBuild = New SqlClient.SqlDataAdapter
(oAdapter)
' Get Insert Command Object
oAdapter.InsertCommand =
oBuild.GetInsertCommand()
' Submit INSERT statement through Adapter
oAdapter.Update(moDS, "Products")
' Tell DataSet changes to data source are
complete
moDS.AcceptChanges()
' Reload the list box
ListLoad()
Catch oException As Exception
MessageBox.Show(oException.Message)
End Try
End Sub
-----Original Message-----
Please post your code as you have it.
"Trevor" <tj**@bigpond.net.au> wrote in message
news:0c****************************@phx.gbl... Ok so what do i do?
What do i use instead?
The code is microsofts.
>-----Original Message-----
>the error is b/c oBuild is of a different and
incompatible type than a
>sqldataadapter. it is a sqlcommand object. it's like
saying "this is an
>apple...make it into an orange."
>
>steve
>
>
>"Trevor" <tj**@bigpond.net.au> wrote in message
>news:06****************************@phx.gbl...
>> Hey,
>>
>> I am trying to do this tutorial on the microsoftsite : >> http://msdn.microsoft.com/library/default.asp?
>> url=/library/en-us/dndotnet/html/usingadonet.asp
>>
>> I can get everything to work up to the DataAdd part
>> (Section heading is Adding Rows to a DataSet)
>>
>> I dont really know what to do after i have added
all the
>> code for the DataAdd() procedure. Other than the
DataAdd
>> () for the button that i want it to use.
>>
>> I dont know where to put that INSERT SQL Statementor add
>> the new row to the datatable in the dataset.
>>
>> Also in that main code of the DataAdd() procedurethere >> is a line like this:
>>
>> oBuild = New SqlClient.SqlDataAdapter(oAdapter)
>>
>> Does anyone know why i get : Value of
>> type 'System.Data.SqlClient.SqlDataAdapter' cannotbe >> converted to 'System.Data.SqlClient.SqlCommand'.
>>
>> ???
>>
>>
>> Any help on this would be much appreciated.
>>
>> Thank you.
>>
>
>
>.
>
.
.
