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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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?

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,

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).

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

>
> 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
>
>

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?








"William Ryan eMVP" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
>
> "randy" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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?
>
> 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,
>
> 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).
>
> 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
>
> >
> > 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
> >
> >
>
>

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.
"randy" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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?
>
>
>
>
>
>
>
>
> "William Ryan eMVP" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
> >
> > "randy" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > 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?
> >
> > 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,
> >
> > 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).
> >
> > 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
> >
> > >
> > > 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
> > >
> > >
> >
> >
>
>

Could you PLEASE supply a little code for my example???

Thanks,

-Randy

"William Ryan eMVP" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.
> "randy" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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?
> >
> >
> >
> >
> >
> >
> >
> >
> > "William Ryan eMVP" <(E-Mail Removed)> wrote in message
> > news:%(E-Mail Removed)...
> > >
> > > "randy" <(E-Mail Removed)> wrote in message
> > > news:(E-Mail Removed)...
> > > > 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?
> > >
> > > 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,
> > >
> > > 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).
> > >
> > > 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
> > >
> > > >
> > > > 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
> > > >
> > > >
> > >
> > >
> >
> >
>
>

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"))

'


"randy" <(E-Mail Removed)> wrote
in message news:#(E-Mail Removed)...
> Could you PLEASE supply a little code for my example???
>
> Thanks,
>
> -Randy
>
> "William Ryan eMVP" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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.
> > "randy" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > 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?
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > > "William Ryan eMVP" <(E-Mail Removed)> wrote in message
> > > news:%(E-Mail Removed)...
> > > >
> > > > "randy" <(E-Mail Removed)> wrote in message
> > > > news:(E-Mail Removed)...
> > > > > 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?
> > > >
> > > > 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,
> > > >
> > > > 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).
> > > >
> > > > 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
> > > >
> > > > >
> > > > > 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
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>