DataTable before DataAdapter possible?

  • Thread starter Thread starter randy
  • Start date Start date
R

randy

Hello all,

I have a DataTable which I am building column by column and adding rows
after each new column. The DataTable columns match the columns in my
database table. I'm building the DataTable first and I then want to roll
through the DataTable while in memory checking for errors and then commit
the rows to my database table (btw this is in ASP.NET). Is it possible to
have data in a datable before attaching at DataAdapter? I'm a little new to
this and know I need a SqlCommandBuilder, so some code example would be very
helpful. Below is what I know about using the DataAdapter/CommandBuilder,
but I don't need to SELECT anything because the DataTable already had new
rows (that why I put Id = 0 to get no data).

dim dt as New DataTable
dim da as DataAdapter
dim sqlCmdBuild as SqlCommandBuilder

da = New SqlDataAdapter("SELECT * FROM Table WHERE Id = 0", OpenDbConn)
da..Fill(dt)

sqlCmdBuild = New SqlCommandBuilder(da)

TIA,

-Randy
 
randy said:
Hello all,

I have a DataTable which I am building column by column and adding rows
after each new column. The DataTable columns match the columns in my
database table. I'm building the DataTable first and I then want to roll
through the DataTable while in memory checking for errors and then commit
the rows to my database table (btw this is in ASP.NET). Is it possible to
have data in a datable before attaching at DataAdapter?
Click to expand...

Yes, and you never technically attach a datatable to an adapter. An adapter
could care less what you send to it, it only cares in that it matches the
commands that you try to have it send the db.
I'm a little new to
this and know I need a SqlCommandBuilder,
Click to expand...

Not so, and in all likelihood, avoid the command builder. Check out Bill
Vaughn's article Weaning Developer's from the commandBUilder at
www.betav.com ->Articles ->MSDN
so some code example would be very
helpful. Below is what I know about using the DataAdapter/CommandBuilder,
but I don't need to SELECT anything because the DataTable already had new
rows (that why I put Id = 0 to get no data).
Click to expand...

If you use the command builder, you should be ok, but that query will
probably cause drama. The commanduilder infers update/insert/delete logic
based on athe Select Command so that's all it needs. You don't need to
write over your exisitn gdata. You also probably want to use Parameterized
queries... Where ID = @SomeValue"

then do mySelectCommand.Parameters.Add("@SomeValue", SqlDbType.Whatever,
someLength).Value = 0

Run through the dataadapter configuration wizard at least once and check out
the code it generates for you. It will have parameters and column mappings
as well. While I don't recommend using it too much b/c it becomes a crutch,
it's a superb learning too.

Also, you don't have to call Select First. You don't even have to call
update on a datatable that you called fill on, or even one whose data came
from the db. The adapter doesn't care. I know this may seem like a lot so
you may want to check out Bill's article, play with the wizard, and just get
a valid Update command. Then do what you are now, and just call update on
your datatable. Let me know if you have any problems, I'll do my best to
get you through them.



HTH,

Bill

www.devbuzz.com
www.knowdotnet.com
 
Thanks for you quick response. I went though the configuration wizdard and
looked over the example from betav.com. I mostly understand the code, but
I'm not making a connection between what I want/need to do and the example.

Simple example of what I'm doing. First I build my datatable and then add a
row to be inserted...

Dim dt as new datatable
Dim datCol as DataColumn
Dim DatRow as DataRow

datCol = new DataColumn("FName", System.Type.GetType("System.String"))
dt.Columns.Add(datCol)

datCol = new DataColumn("LName", System.Type.GetType("System.String"))
dt.Columns.Add(datCol)

datRow = dt.NewRow
datRow.Item("FName") = "Joe"
datRow.Item("LName") = "Smith"
dt.Rows.Add(datRow)

This is only one row, but I could have 500+ new rows. The "FName" and
"LName" are the same in my database table. Are you saying that I need to
loop though the rows of the datatable and assign them Paramerters? Could
please add some code below so I can better understand what I need next?
 
If the rowstate of the rows isn't added/deleted etc,the call to your update
statement is never made. If set up correctly, the adapter will walk through
the table for you and handle all of this, automatically assigning the
parameters in your quereis to the values in the datatable.

If you are building it manually like below, you just need a valid Insert
command but that command must map to the datatable in order for you to call
..Update and have it do anything.
 
INSERT INTO Employees
( LName, FName)
VALUES (@LName, @FName)


This will be the CommandText for the insert command. Add an update command
to dataAdapter1 for instance and use this text.

At the end of your code where you are adding your row (dt.Rows.Add(datRow);

call dataAdapter1.Update(dt)

I've just did this making the mods however I configured my dataadapter using
the wizard. Nonetlesss this is pretty much it (I used emplnum as a key but
you can just remove it)
Me.SqlInsertCommand2.CommandText = "INSERT INTO Employees(EmplNum, LastName,
FirstName) VALUES (@EmplNum, @LastName, " & _

"@FirstName); SELECT EmplNum, LastName, FirstName FROM Employees WHERE
(EmplNum =" & _

" @EmplNum)" THE SELECT STATEMENT CAN BE REMOVED TOO, IT'S ONLY USED TO
REFRESH THE DATASET

Me.SqlInsertCommand2.Connection = Me.cn

Me.SqlInsertCommand2.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@EmplNum", System.Data.SqlDbType.Int, 4,
"EmplNum")) 'REMOVE THIS LINE

Me.SqlInsertCommand2.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@LastName",
System.Data.SqlDbType.NVarChar, 20, "LastName"))

Me.SqlInsertCommand2.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@FirstName",
System.Data.SqlDbType.NVarChar, 20, "FirstName"))

'
 
Back
Top