Data set question

  • Thread starter Thread starter EMW
  • Start date Start date
Hi Eric,

This will not go.

As I wrote you need to do a fill.

If you have only additions and you are sure of than you can try what the
sqlAdapt.MissingSchemaAction = MissingSchemaAction.AddWithKey
sqlAdapt.FillSchema(dsWO, SchemaType.Mapped)

Can do for you,

Cor
 
According to mr. Harlow I don't need to do a FILL first, I can just do an
UPDATE.
This I didn't know, so I don't need the second dataset.
 
EMW,
Does the WOlist table have a primary key defined?

To use the SqlCommandBuilder you need to have a primary key defined on the
table. Also as Cor stated, you need to return at least one row.


However! Instead of using the SqlCommandBuilder I would simply define my own
Insert, Update & Delete commands and assigning them to the SqlDataAdapter.
sqlAdapt.UpdateCommand = New SqlClient.SqlCommand(strUpdateSql, con)
sqlAdapt.InsertCommand = New SqlClient.SqlCommand(strInsertSql, con)
sqlAdapt.DeleteCommand = New SqlClient.SqlCommand(strDeleteSql, con)
Click to expand...

Along with the respective parameters.

As I stated, I put the above objects on a Component so I can use the Visual
Designer at design time (greatly simplifies creating the objects as you have
drag & drop & the properties window).

Again Sceppa's book provides full examples & explanations.

Hope this helps
Jay

EMW said:
this is the code I use, but I still get that message:

con = New SqlClient.SqlConnection("Server=" + strServer + _
";database=" + strPMWOdatabase + _
";Integrated security=SSPI" + _
";Connect Timeout=" + strTimeOut)
Try
con.Open()
Catch ex As Exception
writeSiteGegevensUpdate = False
con = Nothing
Exit Function
End Try
strSql = "SELECT * FROM WOlist"
sqlAdapt = New SqlClient.SqlDataAdapter
sqlAdapt.SelectCommand = New SqlClient.SqlCommand(strSql, con)
Dim cb As SqlClient.SqlCommandBuilder = New
SqlClient.SqlCommandBuilder(sqlAdapt)
sqlAdapt.MissingSchemaAction = MissingSchemaAction.AddWithKey
Try
sqlAdapt.Update(dsWO, "WOlist")
Catch ex As Exception
writeSiteGegevensUpdate = False
MsgBox(ex.Message + vbCrLf + ex.ToString)
con.Close()
Exit Function
End Try


this is the message:
Additional information: Dynamic SQL generation for UpdateCommand is not
supported on the basis of a SelectCommand that does not provide information
on key columns.


thanks.
Eric



"Jay B. Harlow [MVP - Outlook]" <[email protected]> schreef in bericht
Then don't! You can open & close your Connection only when you do the
DataAdapter.Fill or DataAdapter.Update.

DataAdapter.Update on a single DataSet is a fast way to update a
Click to expand...
database.
I
would expect getting a second DataSet involved would slow things down (as
you now have 2 datasets & you are coping data).

You statement is how ADO.NET is designed:

1. Create DataSet ds
2. Connect
3. DataAdapter.Fill ds
4. Disconnect

work with dataset, making changes, additions, deletions

5. Connect
6. DataAdpater.Update ds
7. Disconnet

Again Sceppa's book covers all this in great detail.


That sounds normal ADO.NET. The dataset you pass to the procedure is the
exact same one you would pass to the DataAdapter.Update.
Click to expand...
everywhere
in
Normally I put all my "actions" in a Data Component (Project - Add
Component) that has my Connections, Commands, and DataAdapters on it. I then
have two methods: Fill & Update that call Fill & Update on each contained
DataAdapter. This way the procedure above will call the Update on my "Data
Component". The procedure would either create a new instance of the Data
Component or the DataComponent would be a singleton or a property on a
second higher level class...

I use a Component as it allows me to visually design my Connections,
Commands, and DataAdapters.

Remember that you do not need to use the same DataAdpater that Filled a
DataSet to Update the same Dataset.

Hope this helps
Jay
Click to expand...
everywhere
in
Click to expand...
Click to expand...
 
Would this be the correct way to do it?

Dim cmd As SqlClient.SqlCommand
Dim parm As SqlClient.SqlParameter
strSql = "SELECT * FROM WOlist"
sqlAdapt = New SqlClient.SqlDataAdapter
sqlAdapt.SelectCommand = New SqlClient.SqlCommand(strSql, con)

cmd = New SqlClient.SqlCommand("UPDATE wolist SET sitenum = @sitenum," + _
" plaats = @plaats " & _
" fse = @fse " & _
" VorigePmdatum = @VorigePmdatum " & _
" VorigeWOnum = @VorigeWOnum " & _
" NieuwWOnum = @NieuwWOnum " & _
" typeSite = @typeSite " & _
" online = @online " & _
"WHERE sitenum = @oldsitenum", con)
cmd.Parameters.Add("@sitenum", SqlDbType.NText, 5, "CustomerID")
cmd.Parameters.Add("@plaats", SqlDbType.NText, 50, "CustomerID")
cmd.Parameters.Add("@VorigePmdatum", SqlDbType.NText, 12, "CustomerID")
cmd.Parameters.Add("@VorigeWOnum", SqlDbType.NText, 12, "CustomerID")
cmd.Parameters.Add("@NieuwWOnum", SqlDbType.NText, 12, "CustomerID")
cmd.Parameters.Add("@typeSite", SqlDbType.NText, 50, "CustomerID")
cmd.Parameters.Add("@online", SqlDbType.NText, 12, "CustomerID")
parm = cmd.Parameters.Add("@oldsitenum", SqlDbType.NChar, 5, "CustomerID")
parm.SourceVersion = DataRowVersion.Original
sqlAdapt.UpdateCommand = cmd
cmd = New SqlClient.SqlCommand("INSERT INTO wolist SET sitenum = @sitenum,"
+ _
" plaats = @plaats " & _
" fse = @fse " & _
" VorigePmdatum = @VorigePmdatum " & _
" VorigeWOnum = @VorigeWOnum " & _
" NieuwWOnum = @NieuwWOnum " & _
" typeSite = @typeSite " & _
" online = @online " & _
"WHERE sitenum = @oldsitenum", con)
cmd.Parameters.Add("@sitenum", SqlDbType.NText, 5, "CustomerID")
cmd.Parameters.Add("@plaats", SqlDbType.NText, 50, "CustomerID")
cmd.Parameters.Add("@VorigePmdatum", SqlDbType.NText, 12, "CustomerID")
cmd.Parameters.Add("@VorigeWOnum", SqlDbType.NText, 12, "CustomerID")
cmd.Parameters.Add("@NieuwWOnum", SqlDbType.NText, 12, "CustomerID")
cmd.Parameters.Add("@typeSite", SqlDbType.NText, 50, "CustomerID")
cmd.Parameters.Add("@online", SqlDbType.NText, 12, "CustomerID")
parm = cmd.Parameters.Add("@oldsitenum", SqlDbType.NChar, 5, "CustomerID")
parm.SourceVersion = DataRowVersion.Original
sqlAdapt.InsertCommand = cmd
cmd = New SqlClient.SqlCommand("DELETE FROM wolist SET sitenum = @sitenum,"
+ _
" plaats = @plaats " & _
" fse = @fse " & _
" VorigePmdatum = @VorigePmdatum " & _
" VorigeWOnum = @VorigeWOnum " & _
" NieuwWOnum = @NieuwWOnum " & _
" typeSite = @typeSite " & _
" online = @online " & _
"WHERE sitenum = @oldsitenum", con)
cmd.Parameters.Add("@sitenum", SqlDbType.NText, 5, "CustomerID")
cmd.Parameters.Add("@plaats", SqlDbType.NText, 50, "CustomerID")
cmd.Parameters.Add("@VorigePmdatum", SqlDbType.NText, 12, "CustomerID")
cmd.Parameters.Add("@VorigeWOnum", SqlDbType.NText, 12, "CustomerID")
cmd.Parameters.Add("@NieuwWOnum", SqlDbType.NText, 12, "CustomerID")
cmd.Parameters.Add("@typeSite", SqlDbType.NText, 50, "CustomerID")
cmd.Parameters.Add("@online", SqlDbType.NText, 12, "CustomerID")
parm = cmd.Parameters.Add("@oldsitenum", SqlDbType.NChar, 5, "CustomerID")
parm.SourceVersion = DataRowVersion.Original
sqlAdapt.DeleteCommand = cmd


These are all the fields per row.

rg,
Eric






Jay B. Harlow said:
EMW,
Does the WOlist table have a primary key defined?

To use the SqlCommandBuilder you need to have a primary key defined on the
table. Also as Cor stated, you need to return at least one row.


However! Instead of using the SqlCommandBuilder I would simply define my own
Insert, Update & Delete commands and assigning them to the SqlDataAdapter.
sqlAdapt.UpdateCommand = New SqlClient.SqlCommand(strUpdateSql, con)
sqlAdapt.InsertCommand = New SqlClient.SqlCommand(strInsertSql, con)
sqlAdapt.DeleteCommand = New SqlClient.SqlCommand(strDeleteSql, con)
Click to expand...

Along with the respective parameters.

As I stated, I put the above objects on a Component so I can use the Visual
Designer at design time (greatly simplifies creating the objects as you have
drag & drop & the properties window).

Again Sceppa's book provides full examples & explanations.

Hope this helps
Jay

EMW said:
this is the code I use, but I still get that message:

con = New SqlClient.SqlConnection("Server=" + strServer + _
";database=" + strPMWOdatabase + _
";Integrated security=SSPI" + _
";Connect Timeout=" + strTimeOut)
Try
con.Open()
Catch ex As Exception
writeSiteGegevensUpdate = False
con = Nothing
Exit Function
End Try
strSql = "SELECT * FROM WOlist"
sqlAdapt = New SqlClient.SqlDataAdapter
sqlAdapt.SelectCommand = New SqlClient.SqlCommand(strSql, con)
Dim cb As SqlClient.SqlCommandBuilder = New
SqlClient.SqlCommandBuilder(sqlAdapt)
sqlAdapt.MissingSchemaAction = MissingSchemaAction.AddWithKey
Try
sqlAdapt.Update(dsWO, "WOlist")
Catch ex As Exception
writeSiteGegevensUpdate = False
MsgBox(ex.Message + vbCrLf + ex.ToString)
con.Close()
Exit Function
End Try


this is the message:
Additional information: Dynamic SQL generation for UpdateCommand is not
supported on the basis of a SelectCommand that does not provide information
on key columns.


thanks.
Eric



"Jay B. Harlow [MVP - Outlook]" <[email protected]> schreef in bericht
EMW,
I don't want to keep the database open for a long time.
The dataset is connected to a datagrid and people can fill in some extra
info.
Then don't! You can open & close your Connection only when you do the
DataAdapter.Fill or DataAdapter.Update.

I'm looking for a fast way to update the database.
DataAdapter.Update on a single DataSet is a fast way to update a
Click to expand...
database.
I
would expect getting a second DataSet involved would slow things down (as
you now have 2 datasets & you are coping data).

I don't think I can just connect to the database and do an Update without
some filling first....but if it is possible.....
You statement is how ADO.NET is designed:

1. Create DataSet ds
2. Connect
3. DataAdapter.Fill ds
4. Disconnect

work with dataset, making changes, additions, deletions

5. Connect
6. DataAdpater.Update ds
7. Disconnet

Again Sceppa's book covers all this in great detail.


So basicly I have a procedure with the changed dataset as an
Click to expand...
argument
and
inside the procedure I want to update the sql database.
That sounds normal ADO.NET. The dataset you pass to the procedure is the
exact same one you would pass to the DataAdapter.Update.

I like all my
database 'actions' to be put in one single module instead of
Click to expand...
everywhere
in
the program. To keep the programsize as small as possible.
Normally I put all my "actions" in a Data Component (Project - Add
Component) that has my Connections, Commands, and DataAdapters on it.
Click to expand...
I
then
have two methods: Fill & Update that call Fill & Update on each contained
DataAdapter. This way the procedure above will call the Update on my "Data
Component". The procedure would either create a new instance of the Data
Component or the DataComponent would be a singleton or a property on a
second higher level class...

I use a Component as it allows me to visually design my Connections,
Commands, and DataAdapters.

Remember that you do not need to use the same DataAdpater that Filled a
DataSet to Update the same Dataset.

Hope this helps
Jay

I don't want to keep the database open for a long time.
The dataset is connected to a datagrid and people can fill in some extra
info.

I'm looking for a fast way to update the database.
I don't think I can just connect to the database and do an Update without
some filling first....but if it is possible.....

So basicly I have a procedure with the changed dataset as an
Click to expand...
argument
and
inside the procedure I want to update the sql database. I like all my
database 'actions' to be put in one single module instead of
Click to expand...
everywhere
in
the program. To keep the programsize as small as possible.

Is it more clear now? ;)

rg,
Eric




"Cor" <[email protected]> schreef in bericht
Hi EMW,

I started to examine your problem again

And I do not understand why you do this, maybe you can explain it a
little
bit more.

How can I update to the database after it has been closed and
re-opened
You can always use dataset1 for that, but when you go do things
Click to expand...
with
it
you
will probably get constraints errors.

That key problem is easy to overcome by the way.

da.MissingSchemaAction = MissingSchemaAction.AddWithKey
This is for that.

So tell something more?

Cor
Click to expand...
Click to expand...
Click to expand...
 
it's full with errors....please remove....

EMW said:
Would this be the correct way to do it?

Dim cmd As SqlClient.SqlCommand
Dim parm As SqlClient.SqlParameter
strSql = "SELECT * FROM WOlist"
sqlAdapt = New SqlClient.SqlDataAdapter
sqlAdapt.SelectCommand = New SqlClient.SqlCommand(strSql, con)

cmd = New SqlClient.SqlCommand("UPDATE wolist SET sitenum = @sitenum," + _
" plaats = @plaats " & _
" fse = @fse " & _
" VorigePmdatum = @VorigePmdatum " & _
" VorigeWOnum = @VorigeWOnum " & _
" NieuwWOnum = @NieuwWOnum " & _
" typeSite = @typeSite " & _
" online = @online " & _
"WHERE sitenum = @oldsitenum", con)
cmd.Parameters.Add("@sitenum", SqlDbType.NText, 5, "CustomerID")
cmd.Parameters.Add("@plaats", SqlDbType.NText, 50, "CustomerID")
cmd.Parameters.Add("@VorigePmdatum", SqlDbType.NText, 12, "CustomerID")
cmd.Parameters.Add("@VorigeWOnum", SqlDbType.NText, 12, "CustomerID")
cmd.Parameters.Add("@NieuwWOnum", SqlDbType.NText, 12, "CustomerID")
cmd.Parameters.Add("@typeSite", SqlDbType.NText, 50, "CustomerID")
cmd.Parameters.Add("@online", SqlDbType.NText, 12, "CustomerID")
parm = cmd.Parameters.Add("@oldsitenum", SqlDbType.NChar, 5, "CustomerID")
parm.SourceVersion = DataRowVersion.Original
sqlAdapt.UpdateCommand = cmd
cmd = New SqlClient.SqlCommand("INSERT INTO wolist SET sitenum = @sitenum,"
+ _
" plaats = @plaats " & _
" fse = @fse " & _
" VorigePmdatum = @VorigePmdatum " & _
" VorigeWOnum = @VorigeWOnum " & _
" NieuwWOnum = @NieuwWOnum " & _
" typeSite = @typeSite " & _
" online = @online " & _
"WHERE sitenum = @oldsitenum", con)
cmd.Parameters.Add("@sitenum", SqlDbType.NText, 5, "CustomerID")
cmd.Parameters.Add("@plaats", SqlDbType.NText, 50, "CustomerID")
cmd.Parameters.Add("@VorigePmdatum", SqlDbType.NText, 12, "CustomerID")
cmd.Parameters.Add("@VorigeWOnum", SqlDbType.NText, 12, "CustomerID")
cmd.Parameters.Add("@NieuwWOnum", SqlDbType.NText, 12, "CustomerID")
cmd.Parameters.Add("@typeSite", SqlDbType.NText, 50, "CustomerID")
cmd.Parameters.Add("@online", SqlDbType.NText, 12, "CustomerID")
parm = cmd.Parameters.Add("@oldsitenum", SqlDbType.NChar, 5, "CustomerID")
parm.SourceVersion = DataRowVersion.Original
sqlAdapt.InsertCommand = cmd
cmd = New SqlClient.SqlCommand("DELETE FROM wolist SET sitenum = @sitenum,"
+ _
" plaats = @plaats " & _
" fse = @fse " & _
" VorigePmdatum = @VorigePmdatum " & _
" VorigeWOnum = @VorigeWOnum " & _
" NieuwWOnum = @NieuwWOnum " & _
" typeSite = @typeSite " & _
" online = @online " & _
"WHERE sitenum = @oldsitenum", con)
cmd.Parameters.Add("@sitenum", SqlDbType.NText, 5, "CustomerID")
cmd.Parameters.Add("@plaats", SqlDbType.NText, 50, "CustomerID")
cmd.Parameters.Add("@VorigePmdatum", SqlDbType.NText, 12, "CustomerID")
cmd.Parameters.Add("@VorigeWOnum", SqlDbType.NText, 12, "CustomerID")
cmd.Parameters.Add("@NieuwWOnum", SqlDbType.NText, 12, "CustomerID")
cmd.Parameters.Add("@typeSite", SqlDbType.NText, 50, "CustomerID")
cmd.Parameters.Add("@online", SqlDbType.NText, 12, "CustomerID")
parm = cmd.Parameters.Add("@oldsitenum", SqlDbType.NChar, 5, "CustomerID")
parm.SourceVersion = DataRowVersion.Original
sqlAdapt.DeleteCommand = cmd


These are all the fields per row.

rg,
Eric






"Jay B. Harlow [MVP - Outlook]" <[email protected]> schreef in bericht
EMW,
Does the WOlist table have a primary key defined?

To use the SqlCommandBuilder you need to have a primary key defined on the
table. Also as Cor stated, you need to return at least one row.


However! Instead of using the SqlCommandBuilder I would simply define my own
Insert, Update & Delete commands and assigning them to the SqlDataAdapter.
sqlAdapt.UpdateCommand = New SqlClient.SqlCommand(strUpdateSql, con)
sqlAdapt.InsertCommand = New SqlClient.SqlCommand(strInsertSql, con)
sqlAdapt.DeleteCommand = New SqlClient.SqlCommand(strDeleteSql, con)
Click to expand...

Along with the respective parameters.

As I stated, I put the above objects on a Component so I can use the Visual
Designer at design time (greatly simplifies creating the objects as you have
drag & drop & the properties window).

Again Sceppa's book provides full examples & explanations.

Hope this helps
Jay

EMW said:
this is the code I use, but I still get that message:

con = New SqlClient.SqlConnection("Server=" + strServer + _
";database=" + strPMWOdatabase + _
";Integrated security=SSPI" + _
";Connect Timeout=" + strTimeOut)
Try
con.Open()
Catch ex As Exception
writeSiteGegevensUpdate = False
con = Nothing
Exit Function
End Try
strSql = "SELECT * FROM WOlist"
sqlAdapt = New SqlClient.SqlDataAdapter
sqlAdapt.SelectCommand = New SqlClient.SqlCommand(strSql, con)
Dim cb As SqlClient.SqlCommandBuilder = New
SqlClient.SqlCommandBuilder(sqlAdapt)
sqlAdapt.MissingSchemaAction = MissingSchemaAction.AddWithKey
Try
sqlAdapt.Update(dsWO, "WOlist")
Catch ex As Exception
writeSiteGegevensUpdate = False
MsgBox(ex.Message + vbCrLf + ex.ToString)
con.Close()
Exit Function
End Try


this is the message:
Additional information: Dynamic SQL generation for UpdateCommand is not
supported on the basis of a SelectCommand that does not provide information
on key columns.


thanks.
Eric



"Jay B. Harlow [MVP - Outlook]" <[email protected]> schreef in bericht
EMW,
I don't want to keep the database open for a long time.
The dataset is connected to a datagrid and people can fill in some extra
info.
Then don't! You can open & close your Connection only when you do the
DataAdapter.Fill or DataAdapter.Update.

I'm looking for a fast way to update the database.
DataAdapter.Update on a single DataSet is a fast way to update a database.
I
would expect getting a second DataSet involved would slow things
Click to expand...
down
(as
you now have 2 datasets & you are coping data).

I don't think I can just connect to the database and do an Update
without
some filling first....but if it is possible.....
You statement is how ADO.NET is designed:

1. Create DataSet ds
2. Connect
3. DataAdapter.Fill ds
4. Disconnect

work with dataset, making changes, additions, deletions

5. Connect
6. DataAdpater.Update ds
7. Disconnet

Again Sceppa's book covers all this in great detail.


So basicly I have a procedure with the changed dataset as an argument
and
inside the procedure I want to update the sql database.
That sounds normal ADO.NET. The dataset you pass to the procedure is the
exact same one you would pass to the DataAdapter.Update.

I like all my
database 'actions' to be put in one single module instead of everywhere
in
the program. To keep the programsize as small as possible.
Normally I put all my "actions" in a Data Component (Project - Add
Component) that has my Connections, Commands, and DataAdapters on
Click to expand...
Click to expand...
it.
I
Click to expand...
Filled
a
Click to expand...
it
a
Click to expand...
Click to expand...
 
Got it working now!

Thanks everyone for helping.
Making my own INSERT, DELETE and UPDATE commands did the trick.

rg,
Eric
 
Back
Top