Adding data to table

  • Thread starter Thread starter Trevor
  • Start date Start date
T

Trevor

Hey,

I am trying to do this tutorial on the microsoft site :
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 Statement or add
the new row to the datatable in the dataset.

Also in that main code of the DataAdd() procedure there
is a line like this:

oBuild = New SqlClient.SqlDataAdapter(oAdapter)

Does anyone know why i get : Value of
type 'System.Data.SqlClient.SqlDataAdapter' cannot be
converted to 'System.Data.SqlClient.SqlCommand'.

???


Any help on this would be much appreciated.

Thank you.
 
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
 
i don't know if i've the time to look at the site's example but i may.

as far as it being from microsoft...most people would scoff and say that
that's just ms. most of the coding examples are snippets of example
projects. the code is either wrong, or they didn't fully explain it, or
omitted the inclusion of other pertanent information...that's what i'll say
about it.

steve
 
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 SQL
statement. 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
 
Yeh i know its typical of microsoft, but i just want to
know how i can get around it so i can add data to the sql
database.

Thanks
-----Original Message-----
i don't know if i've the time to look at the site's example but i may.

as far as it being from microsoft...most people would scoff and say that
that's just ms. most of the coding examples are snippets of example
projects. the code is either wrong, or they didn't fully explain it, or
omitted the inclusion of other pertanent
Click to expand...
information...that's what i'll say
 
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.
 
let's digress...can you describe a scenario where you need data interaction?
i can either pull some existing code (that i *know* works) or come up w/
some in a snap.

steve
 
I should probably say that i dont have any idea how to do
that, they explain everything else great except or that
part.

Where and how do i put all that?
 
Ok,

I have put in the DataAdd() part and it loads fine (i
have now fixed the problem with the so the code is ok,
but i think i need to put in more code to add a row into
the table before i can write to it (as the tutorial says
on that site). The reason i figure this is when i press
the add button i get this error....


An unhandled exception of
type 'System.Data.NoNullAllowedException' occurred in
system.data.dll

Additional information: Column 'ProductID' does not allow
nulls.
 
Back
Top