DataTable before DataAdapter possible?

[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

[William Ryan eMVP]

"randy" <ra***@someplace.com> wrote in message
news:uJ**************@TK2MSFTNGP09.phx.gbl...

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

[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" <do********@comcast.nospam.net> wrote in message
news:%2****************@TK2MSFTNGP11.phx.gbl...

"randy" <ra***@someplace.com> wrote in message
news:uJ**************@TK2MSFTNGP09.phx.gbl...

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

[William Ryan eMVP]

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" <rs****@portfoliorecovery.nospam.com> wrote in message
news:ef**************@TK2MSFTNGP12.phx.gbl...

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" <do********@comcast.nospam.net> wrote in message
news:%2****************@TK2MSFTNGP11.phx.gbl...

"randy" <ra***@someplace.com> wrote in message
news:uJ**************@TK2MSFTNGP09.phx.gbl...

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

[randy]

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

Thanks,

-Randy

"William Ryan eMVP" <do********@comcast.nospam.net> wrote in message
news:ei*************@TK2MSFTNGP09.phx.gbl...

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" <rs****@portfoliorecovery.nospam.com> wrote in message
news:ef**************@TK2MSFTNGP12.phx.gbl...

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" <do********@comcast.nospam.net> wrote in message
news:%2****************@TK2MSFTNGP11.phx.gbl...

"randy" <ra***@someplace.com> wrote in message
news:uJ**************@TK2MSFTNGP09.phx.gbl...
> 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
>
>

[William Ryan eMVP]

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" <rs****@portfoliorecovery.nospam.com> wrote
in message news:#v**************@tk2msftngp13.phx.gbl...

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

Thanks,

-Randy

"William Ryan eMVP" <do********@comcast.nospam.net> wrote in message
news:ei*************@TK2MSFTNGP09.phx.gbl...

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" <rs****@portfoliorecovery.nospam.com> wrote in message
news:ef**************@TK2MSFTNGP12.phx.gbl...

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" <do********@comcast.nospam.net> wrote in message
news:%2****************@TK2MSFTNGP11.phx.gbl...
>
> "randy" <ra***@someplace.com> wrote in message
> news:uJ**************@TK2MSFTNGP09.phx.gbl...
> > 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
> >
> >
>
>