By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,496 Members | 1,528 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,496 IT Pros & Developers. It's quick & easy.

Full dataset into an empty SQL table

P: n/a
EMW
Hi,

I managed to create a SQL server database and a table in it. The table is
empty and that brings me to my next chalenge:

How can I get the info in the table in the dataset to go in an empty SQL
table?

Is there a short way like the FILL method to get data into the dataset or do
I have to read each datarow in the table and write it one at the time to the
database?

rg,
Eric
Nov 20 '05 #1
Share this Question
Share on Google+
22 Replies


P: n/a
Cor
Hi EMW,

The opposite from the Fill is the Update.
(And don't forget to look at "commandbuilder", otherwise you have a lot of
work to do).

I hope this helps?

Cor

I managed to create a SQL server database and a table in it. The table is
empty and that brings me to my next chalenge:
How can I get the info in the table in the dataset to go in an empty SQL
table?
Is there a short way like the FILL method to get data into the dataset or do I have to read each datarow in the table and write it one at the time to the database?

Nov 20 '05 #2

P: n/a
EMW
I'm currently trying things with update, but it seems to me I'm updating the
dataset (thus losing the info).

"Cor" <no*@non.com> schreef in bericht
news:O0**************@TK2MSFTNGP10.phx.gbl...
Hi EMW,

The opposite from the Fill is the Update.
(And don't forget to look at "commandbuilder", otherwise you have a lot of
work to do).

I hope this helps?

Cor

I managed to create a SQL server database and a table in it. The table is empty and that brings me to my next chalenge:
How can I get the info in the table in the dataset to go in an empty SQL
table?
Is there a short way like the FILL method to get data into the dataset
or do
I have to read each datarow in the table and write it one at the time to

the
database?


Nov 20 '05 #3

P: n/a

The update command will do you no good at this point. You
must use the Add function first to add data to your form
before you can update it.

Below is a snippet of code that shows you must have a
valid SQL Select statement first, open your recordset and
then add to it. Do not forget to declare your recordset
or you will have problems.

This is the code for an add button event to add a new
user to a table.

SQL = "SELECT * FROM tusers WHERE UserID = '" & Trim
(txtFName.Text) & Trim(txtLName.Text).Substring(0, 1)
& "'"
Rs.Open(SQL, Your_Connection.RETURN_CONNECTION,
ADODB.CursorTypeEnum.adOpenDynamic,
ADODB.LockTypeEnum.adLockBatchOptimistic)
If Rs.EOF Then
Rs.AddNew()
Call SetDataUser()
Rs.UpdateBatch()
MsgBox("Record has been added.")
Else
MsgBox("UserID has already been taken. Please
create a new one by changing the first name of the user.
This will help create a unique ID for that person.")
Rs.Close()
Exit Sub
End If
Rs.Close()
Always close your recordset after using it to make sure
all updates occur and you do not accidentally crash your
program.

The Call SetDataUser() calls a subroutine that assigns
each field to it's corresponding one in the table like
below:
Private Sub SetDataUser()

Dim myString As String = txtLName.Text
Dim myChar As Char
myChar = myString.Chars(0)

Rs.Fields(0).Value = txtFName.Text & myChar
Rs.Fields(1).Value = txtLName.Text
Rs.Fields(2).Value = txtFName.Text
Rs.Fields(4).Value = cmboBoxDept.SelectedItem
If cmboboxbackup.SelectedItem = "" Then
Rs.Fields(5).Value = "No back-up"
Else
Rs.Fields(5).Value =
cmboboxbackup.SelectedItem
End If

End Sub

Hope this helps
-----Original Message-----
I'm currently trying things with update, but it seems to me I'm updating thedataset (thus losing the info).

"Cor" <no*@non.com> schreef in bericht
news:O0**************@TK2MSFTNGP10.phx.gbl...
Hi EMW,

The opposite from the Fill is the Update.
(And don't forget to look at "commandbuilder", otherwise you have a lot of
work to do).

I hope this helps?

Cor
>
> I managed to create a SQL server database and a table in it. The table
is > empty and that brings me to my next chalenge:
> How can I get the info in the table in the dataset
to go in an empty SQL > table?
> Is there a short way like the FILL method to get
data into the datasetor
do
> I have to read each datarow in the table and write

it one at the time to the
> database?


.

Nov 20 '05 #4

P: n/a
Cor
Hi EMW,

I think know maybe what you mean. But I make a scenario.

You want to put the structure from one database table in your dataset, put
data in that dataset and write it in your database.

That is Fill, even if it is completly empty because in that way you get your
structure
with "Select * from database table" (Maybe a lazy methode, but for me it
works).

You want to add rows.

Most simple is
dataset.tables(0).rows.add(dataset.tables(0).newro w)

Or
dr as newrow = dataset.tables(0).newrow
dr("item1") = "something"
dr("item2") = "somethingelse"
dataset.tables(0).rows.add(dr)

To update that dataset use the Update with the same select.
This is real code but see it as pseudo because it is not the nicest

\\\
Dim da As New SqlDataAdapter
Dim cmd As New SqlCommand(sqlStr, Conn)
da.SelectCommand = cmd
Dim cb As SqlCommandBuilder = New SqlCommandBuilder(da)
da.Update(dataset)
////

This are of course samples, to bring you on the route, you have to check all
things yourself for the details.

If hope this brings you more on the route?

Cor




Nov 20 '05 #5

P: n/a
Cor
Two typos I see direct
with "Select * from database table" Select * from databasetable"
dr as newrow = dataset.tables(0).newrow

dr as datarow = dataset.tables(0).newrow
Nov 20 '05 #6

P: n/a
EMW
Hi Cor,

I got this before I read your reply, but it still doesn't work quite well.

Here is my code:

Dim con As New SqlConnection
Dim sqlDa As SqlDataAdapter
Dim dr As DataRow
Dim mycmd As SqlCommand
Dim sqlNewstr As String
con.ConnectionString = "Server=(local);Database=Sitelist;Integrated
Security=SSPI;"
sqlDa = New SqlDataAdapter
sqlDa.SelectCommand = New SqlCommand("SELECT * FROM sites", con)
Dim cb As SqlCommandBuilder = New SqlCommandBuilder(sqlDa)
con.Open()
Dim dstoo As New DataSet
sqlDa.Fill(dstoo, "Sites") 'connect the dataset to the empty table
Dim dsT As DataTable
dsT = dstoo.Tables(0) 'handle to the empty table
Dim ab
For ab = 1 To ds.Tables(0).Rows.Count - 1
dr = dsT.NewRow()
dr(1) = ds.Tables(0).Rows(ab)(1)
dr(2) = ds.Tables(0).Rows(ab)(2)
dr(3) = ds.Tables(0).Rows(ab)(3)
dr(4) = ds.Tables(0).Rows(ab)(4)
dr(5) = ds.Tables(0).Rows(ab)(5)
dr(6) = ds.Tables(0).Rows(ab)(6)
dr(7) = ds.Tables(0).Rows(ab)(7)
dr(8) = ds.Tables(0).Rows(ab)(8)
dr(9) = ds.Tables(0).Rows(ab)(9)
dr(0) = ds.Tables(0).Rows(ab)(0)
dsT.Rows.Add(dr) 'add the new rows to the table
Next
dstoo.Tables(0).AcceptChanges() 'save the changes to the table in the
dataset
Try
sqlDa.Update(dstoo, "sites") 'update the database with the contents of
the dataset
Catch ex As Exception
MsgBox(ex.ToString)
End Try
con.Close()
No exceptions or error, but the table in the database remains empty, so
there must be something wrong...

ds is the dataset holding the info and what I do here is copy each row into
the new dataset which is linked to the database.

I hope you can help me with this.

thanks in advance,
Eric

"Cor" <no*@non.com> schreef in bericht
news:uW**************@tk2msftngp13.phx.gbl...
Hi EMW,

I think know maybe what you mean. But I make a scenario.

You want to put the structure from one database table in your dataset, put
data in that dataset and write it in your database.

That is Fill, even if it is completly empty because in that way you get your structure
with "Select * from database table" (Maybe a lazy methode, but for me it
works).

You want to add rows.

Most simple is
dataset.tables(0).rows.add(dataset.tables(0).newro w)

Or
dr as newrow = dataset.tables(0).newrow
dr("item1") = "something"
dr("item2") = "somethingelse"
dataset.tables(0).rows.add(dr)

To update that dataset use the Update with the same select.
This is real code but see it as pseudo because it is not the nicest

\\\
Dim da As New SqlDataAdapter
Dim cmd As New SqlCommand(sqlStr, Conn)
da.SelectCommand = cmd
Dim cb As SqlCommandBuilder = New SqlCommandBuilder(da)
da.Update(dataset)
////

This are of course samples, to bring you on the route, you have to check all things yourself for the details.

If hope this brings you more on the route?

Cor



Nov 20 '05 #7

P: n/a
Cor
Hi EMW,

I did not want to answer anymore tonight, but you see I did, it is almost
ready I think.
Dim dsT As DataTable
dsT = dstoo.Tables(0) 'handle to the empty table

With that I thought you are doing nothing

if sqlDa.haschanges then
sqlDa.Update(dstoo.getchanges, "sites")
end if
dstoo.Tables(0).AcceptChanges()

'save the changes to the table in the dataset, so you can go on with the
data in the dataset and when you go on, the next time the updates are only
the changes after this accept changes :-))

Quick typed again
Nov 20 '05 #8

P: n/a
Cor
> if sqlDa.haschanges then
if dstoo.haschanges
Nov 20 '05 #9

P: n/a
EMW
Thanks!!

I won't keep you awake anymore.......tonight.. ;)

rg,
Eric
"Cor" <no*@non.com> schreef in bericht
news:%2****************@TK2MSFTNGP11.phx.gbl...
if sqlDa.haschanges then

if dstoo.haschanges

Nov 20 '05 #10

P: n/a
EMW
Sorry it doesn't work.

For some reason dstoo.Tables(0).AcceptChanges() needs to be done first,
before I can do the update command, otherwise I get an exception error.

Do you have other suggestions?

rg,
Eric
"Cor" <no*@non.com> schreef in bericht
news:%2****************@TK2MSFTNGP11.phx.gbl...
if sqlDa.haschanges then

if dstoo.haschanges

Nov 20 '05 #11

P: n/a
Cor
HI EMW,

This is from MSDN
When you call AcceptChanges on the DataSet, any DataRow objects still in
edit-mode successfully end their edits. The RowState property of each
DataRow also changes; Added and Modified rows become Unchanged, and Deleted
rows are removed.

The update does an update from all rows where the rowstate is changed. So
now you are updating null rows. Probably that gives no exception because you
are doing nothing.

My suggestion is to send some code again.

Cor
For some reason dstoo.Tables(0).AcceptChanges() needs to be done first,
before I can do the update command, otherwise I get an exception error.

Nov 20 '05 #12

P: n/a
EMW
The exception message I get is (translated from Dutch):

Wrong syntax with site.

And when the procedure is finished, I see a little red icon in my datagrid
with a white exclamation in it telling me the same thing.
So I guess the problem has to do with the datatype of a column in the
dataset.

The program reads first all the data from the access database (all field are
MEMO) and puts it in a dataset.
Then it creates the SQL server database where all the fields are set on
VARCHAR with a length of 500.
Then it connects a dataadapter to this table and it then fills a new dataset
with the info from the first dataset.
The new dataset is then updated to the database.

I don't have a clue anymore....

rg,
Eric
"Cor" <no*@non.com> schreef in bericht
news:eQ**************@TK2MSFTNGP10.phx.gbl...
HI EMW,

This is from MSDN
When you call AcceptChanges on the DataSet, any DataRow objects still in
edit-mode successfully end their edits. The RowState property of each
DataRow also changes; Added and Modified rows become Unchanged, and Deleted rows are removed.

The update does an update from all rows where the rowstate is changed. So
now you are updating null rows. Probably that gives no exception because you are doing nothing.

My suggestion is to send some code again.

Cor
For some reason dstoo.Tables(0).AcceptChanges() needs to be done first,
before I can do the update command, otherwise I get an exception error.


Nov 20 '05 #13

P: n/a
Cor
Hi Eric,

We start again new (That datagrid I never heard of from you and I did not
see it so I do if you did not tell that to me).

Now I understand, you want to collect data from your access database to an
SQL database. (Did you know there is a wizard to do that in one time?)

What to do using 2 dataset and lets call it an excersise.

All is written by hand withouth checking if names are good, typo's or other
things I maybe forgot.

Read the data from the access database using Oledb
And making a dataset with a fill
Select * from accesdb
(with all things like the connection, the command, the commandbuilder, the
dataadapter)

Than a dataset from the SQL server with a fill
Select * from selectdb
(with all things like the connection, the command, the commandbuilder, the
dataadapter)

That dsSQL an empty dataset I asume that all items in the SQL table are
exact the same as in the access database.

Then we can do something that I never did try totaly but should not know why
it not would go

dim i as integer
dim y as integer
for i is 0 to dsAccess.tables(0).rows.count - 1
dsSQL.tables(0).rows.add(dsSQL.tables(0).newrow)
(this sentence before I never tried in this place, but why not)
for y = 0 to dsAccess.tables(0).rows(i).itemArray.length-1
dsSQL.tables(0).rows(i).item(y) =
dsAccess.tables(0).rows(i).item(y)
next y
next i

and then the
daSQL.update(dsSql)
(with all things like the connection, the command, the commandbuilder, the
dataadapter)

I think it is not that much work so give it a try

Cor
Nov 20 '05 #14

P: n/a
EMW
Hi,

Basicly what you do below here is the same as I do only different.
And it works because I checked it with the datagrid.

This is were the datagrids come in, they are only at the form, for me to be
able to check what is in the table.
At startup the first one shows what is in the access database, thus in the
first dataset.
After copying the data to the new dataset, the first one shows then the
contents of that dataset.
When everything is finished, the second one shows the dataset filled by the
sql database so I can see if my data is in it. (which is not ...)

The reason I'm trying to write this program is
1) to understand more about SQL database, datagrid, dataset and
datatables
2) I'm rewriting a PocketPC program that makes use of this database. In
eVB I could just use the by activesync created database (CDB format) but now
I'm writing it in VB.NET which does not have any support for this database
format. Since one day I migth be leaving my employer, he must be able to
continue to use this software and must be able to generate the right
database.

I work at Orange (mobile network provider) and this database contains all
the addresses of our sites with the antenna's and stuff.
Later I actually have to adjust the software to send the X and Y positions
to the VdoDayton Navigation unit, to provide an even more acccurate site
location.

So back to my problem, here is my code again:
Dim con As New SqlConnection
Dim sqlDa As SqlDataAdapter
Dim dr As DataRow
Dim mycmd As SqlCommand
Dim sqlNewstr As String

con.ConnectionString = "Server=(local);Database=Sitelist;Integrated
Security=SSPI;"
sqlDa = New SqlDataAdapter
sqlDa.SelectCommand = New SqlCommand("SELECT * FROM sites", con)

Dim cb As SqlCommandBuilder = New SqlCommandBuilder(sqlDa)
con.Open()

Dim dstoo As New DataSet
sqlDa.Fill(dstoo, "Sites") 'connect the dataset to the empty table

Dim dsT As DataTable
dsT = dstoo.Tables(0) 'handle to the empty table

Dim ab
For ab = 0 To ds.Tables(0).Rows.Count - 1
dr = dsT.NewRow()
dr(0) = ds.Tables(0).Rows(ab)(0)
dr(1) = ds.Tables(0).Rows(ab)(1)
dr(2) = ds.Tables(0).Rows(ab)(2)
dr(3) = ds.Tables(0).Rows(ab)(3)
dr(4) = ds.Tables(0).Rows(ab)(4)
dr(5) = ds.Tables(0).Rows(ab)(5)
dr(6) = ds.Tables(0).Rows(ab)(6)
dr(7) = ds.Tables(0).Rows(ab)(7)
dr(8) = ds.Tables(0).Rows(ab)(8)
dr(9) = ds.Tables(0).Rows(ab)(9)
dsT.Rows.Add(dr) 'add the new rows to the table
Next
dgSec.DataSource = dstoo
dgSec.DataMember = dsT.TableName
dgSec.Refresh()
MsgBox("continue?") 'just to be able to look at the datagrid

If dstoo.HasChanges Then
Try
sqlDa.Update(dstoo, dstoo.Tables(0).TableName)
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End If
dstoo.Tables(0).AcceptChanges() 'make all changes permanent
dgMain.DataSource = dstoo
dgMain.DataMember = dstoo.Tables(0).TableName

sqlDa.Fill(ds, "Sites") 'connect the dataset to the table
dgSec.DataSource = ds
dgSec.DataMember = "Sites" 'check the table for new data

con.Close()

MsgBox("Ready") 'finished
endsub

The second datagrid is filled, but on the update command it gets an error:
"Wrong syntax with Site"

I can email jou the whole project, but not here in te newsgroup.
Have I said "thank you" yet?

rg,
Eric

"Cor" <no*@non.com> schreef in bericht
news:O4**************@TK2MSFTNGP12.phx.gbl...
Hi Eric,

We start again new (That datagrid I never heard of from you and I did not
see it so I do if you did not tell that to me).

Now I understand, you want to collect data from your access database to an
SQL database. (Did you know there is a wizard to do that in one time?)

What to do using 2 dataset and lets call it an excersise.

All is written by hand withouth checking if names are good, typo's or other things I maybe forgot.

Read the data from the access database using Oledb
And making a dataset with a fill
Select * from accesdb
(with all things like the connection, the command, the commandbuilder, the
dataadapter)

Than a dataset from the SQL server with a fill
Select * from selectdb
(with all things like the connection, the command, the commandbuilder, the
dataadapter)

That dsSQL an empty dataset I asume that all items in the SQL table are
exact the same as in the access database.

Then we can do something that I never did try totaly but should not know why it not would go

dim i as integer
dim y as integer
for i is 0 to dsAccess.tables(0).rows.count - 1
dsSQL.tables(0).rows.add(dsSQL.tables(0).newrow)
(this sentence before I never tried in this place, but why not)
for y = 0 to dsAccess.tables(0).rows(i).itemArray.length-1
dsSQL.tables(0).rows(i).item(y) =
dsAccess.tables(0).rows(i).item(y)
next y
next i

and then the
daSQL.update(dsSql)
(with all things like the connection, the command, the commandbuilder, the
dataadapter)

I think it is not that much work so give it a try

Cor

Nov 20 '05 #15

P: n/a
Cor
Hi Eric,.

To make the thread not to long I took some time to make a long but working
sample.
I get a message from you when you did succeed?

Cor

\\\\\\\
Option Strict On
Imports System.Data.SqlClient
----------Here is the normal form class and start

'Test by Cor Ligthert
'It makes an access database
'Fills that with 10 rows
'makes a SQL database
'transport the rows from the access database to the SQL server
'shows the last dataset in a dagagrid on a form
'-----------------------------------------------------------------------
'set a referentce to ADO ext 2.X for DLL and security

'drag a large datagrid on a form and name it "dg"
'make a directory c:\test1 or change the program
'check that c:\test1\eric.mdb it not is a real file and directory)
'there is no error or locking trapping at all except for the non
existing database "eric"
'check the connection strings
Private Sub Form1_Load(ByVal sender As Object, ByVal e As
System.EventArgs) Handles MyBase.Load
'creating test databases
Dim Conn As New SqlConnection("Server=(local);DataBase=;Integrated
Security=SSPI")
Dim cmd1 As New SqlCommand("DROP DATABASE eric", Conn)
Conn.Open()
Try
cmd1.ExecuteNonQuery()
Catch
MessageBox.Show("Correct if it is the first time")
End Try
Dim strSQL As String = "CREATE DATABASE eric"
Dim cmd2 As New SqlCommand(strSQL, Conn)
cmd2.ExecuteNonQuery()
cmd2.CommandText = _
"USE eric " & vbCrLf & _
"CREATE TABLE Sites ( " & _
"naam1 NVarChar(50)," & _
"naam2 NVarChar(50)," & _
"naam3 NVarChar(50)," & _
"naam4 NVarChar(50)," & _
"naam5 NVarChar(50)," & _
"CONSTRAINT [pk_identFT] PRIMARY KEY CLUSTERED(naam1))"
cmd2.ExecuteNonQuery()
Conn.Close()
Dim catNewDB As ADOX.Catalog
catNewDB = New ADOX.Catalog
If System.IO.File.Exists("C:\test1\eric.mdb") Then
System.IO.File.Delete("C:\test1\eric.mdb")
End If
catNewDB.Create("Provider=Microsoft.Jet.OLEDB.4.0; " & "Data
Source=C:\test1\eric.mdb")
catNewDB = Nothing
catNewDB = Nothing
Dim conn1 As OleDb.OleDbConnection = New OleDb.OleDbConnection
conn1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\test1\eric.mdb;User Id=admin;Password=;"
conn1.Open()
Dim cmdA As New OleDb.OleDbCommand( _
"CREATE TABLE Sites (naam1 char(50) NOT NULL," & _
"naam2 Char(20)," & _
"naam3 Char(20)," & _
"naam4 Char(20)," & _
"naam5 Char(20)," & _
"CONSTRAINT [pk_naam1] PRIMARY KEY (naam1))", conn1)
cmdA.ExecuteNonQuery()
For i As Integer = 1 To 9
cmdA.Parameters.Clear()
cmdA.CommandText = "INSERT INTO Sites
(naam1,naam2,naam3,naam4,naam5) VALUES (@naam1,@naam2,@naam3,@naam4,@naam5)"
cmdA.Parameters.Add(New OleDb.OleDbParameter("@naam1",
OleDb.OleDbType.Char, 50)).Value = i.ToString
cmdA.Parameters.Add(New OleDb.OleDbParameter("@naam2",
OleDb.OleDbType.Char, 50)).Value = Chr(64 + i)
cmdA.Parameters.Add(New OleDb.OleDbParameter("@naam3",
OleDb.OleDbType.Char, 50)).Value = Chr(65 + i)
cmdA.Parameters.Add(New OleDb.OleDbParameter("@naam4",
OleDb.OleDbType.Char, 50)).Value = Chr(66 + i)
cmdA.Parameters.Add(New OleDb.OleDbParameter("@naam5",
OleDb.OleDbType.Char, 50)).Value = Chr(67 + i)
cmdA.ExecuteNonQuery()
Next

'Here start the real program from Eric
'Start program
'read the access dataset
cmdA.CommandText = "Select * from Sites"
Dim da1 As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(cmdA)
Dim dsAcc As New DataSet
da1.Fill(dsAcc)
'read an empty sql dataset
Dim Con As New
SqlConnection("Server=(local);DataBase=Eric;Integr ated Security=SSPI")
Dim sqlDa As New SqlDataAdapter
sqlDa.SelectCommand = New SqlCommand("SELECT * FROM Sites", Con)
Dim cb As SqlCommandBuilder = New SqlCommandBuilder(sqlDa)
Dim dsSQL As New DataSet
sqlDa.Fill(dsSQL, "Sites") 'connect the dataset to the table
Dim ab As Integer
Dim dr As DataRow
For ab = 0 To dsAcc.Tables(0).Rows.Count - 1
dr = dsSQL.Tables(0).NewRow()
dr(0) = dsAcc.Tables(0).Rows(ab)(0)
dr(1) = dsAcc.Tables(0).Rows(ab)(1)
dr(2) = dsAcc.Tables(0).Rows(ab)(2)
dr(3) = dsAcc.Tables(0).Rows(ab)(3)
dr(4) = dsAcc.Tables(0).Rows(ab)(4)
dsSQL.Tables(0).Rows.Add(dr) 'add the new rows to the table
Next
If dsSQL.HasChanges Then
sqlDa.Update(dsSQL, "Sites")
End If
dsSQL.AcceptChanges()
dg.SetDataBinding(dsSQL, "Sites")
Conn.Close()
End Sub
/////////
Nov 20 '05 #16

P: n/a
EMW
of course.

thanks,
Eric

"Cor" <no*@non.com> schreef in bericht
news:eL**************@TK2MSFTNGP11.phx.gbl...
Hi Eric,.

To make the thread not to long I took some time to make a long but working
sample.
I get a message from you when you did succeed?

Cor

\\\\\\\
Option Strict On
Imports System.Data.SqlClient
----------Here is the normal form class and start

'Test by Cor Ligthert
'It makes an access database
'Fills that with 10 rows
'makes a SQL database
'transport the rows from the access database to the SQL server
'shows the last dataset in a dagagrid on a form
'-----------------------------------------------------------------------
'set a referentce to ADO ext 2.X for DLL and security

'drag a large datagrid on a form and name it "dg"
'make a directory c:\test1 or change the program
'check that c:\test1\eric.mdb it not is a real file and directory)
'there is no error or locking trapping at all except for the non
existing database "eric"
'check the connection strings
Private Sub Form1_Load(ByVal sender As Object, ByVal e As
System.EventArgs) Handles MyBase.Load
'creating test databases
Dim Conn As New SqlConnection("Server=(local);DataBase=;Integrated
Security=SSPI")
Dim cmd1 As New SqlCommand("DROP DATABASE eric", Conn)
Conn.Open()
Try
cmd1.ExecuteNonQuery()
Catch
MessageBox.Show("Correct if it is the first time")
End Try
Dim strSQL As String = "CREATE DATABASE eric"
Dim cmd2 As New SqlCommand(strSQL, Conn)
cmd2.ExecuteNonQuery()
cmd2.CommandText = _
"USE eric " & vbCrLf & _
"CREATE TABLE Sites ( " & _
"naam1 NVarChar(50)," & _
"naam2 NVarChar(50)," & _
"naam3 NVarChar(50)," & _
"naam4 NVarChar(50)," & _
"naam5 NVarChar(50)," & _
"CONSTRAINT [pk_identFT] PRIMARY KEY CLUSTERED(naam1))"
cmd2.ExecuteNonQuery()
Conn.Close()
Dim catNewDB As ADOX.Catalog
catNewDB = New ADOX.Catalog
If System.IO.File.Exists("C:\test1\eric.mdb") Then
System.IO.File.Delete("C:\test1\eric.mdb")
End If
catNewDB.Create("Provider=Microsoft.Jet.OLEDB.4.0; " & "Data
Source=C:\test1\eric.mdb")
catNewDB = Nothing
catNewDB = Nothing
Dim conn1 As OleDb.OleDbConnection = New OleDb.OleDbConnection
conn1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\test1\eric.mdb;User Id=admin;Password=;"
conn1.Open()
Dim cmdA As New OleDb.OleDbCommand( _
"CREATE TABLE Sites (naam1 char(50) NOT NULL," & _
"naam2 Char(20)," & _
"naam3 Char(20)," & _
"naam4 Char(20)," & _
"naam5 Char(20)," & _
"CONSTRAINT [pk_naam1] PRIMARY KEY (naam1))", conn1)
cmdA.ExecuteNonQuery()
For i As Integer = 1 To 9
cmdA.Parameters.Clear()
cmdA.CommandText = "INSERT INTO Sites
(naam1,naam2,naam3,naam4,naam5) VALUES (@naam1,@naam2,@naam3,@naam4,@naam5)" cmdA.Parameters.Add(New OleDb.OleDbParameter("@naam1",
OleDb.OleDbType.Char, 50)).Value = i.ToString
cmdA.Parameters.Add(New OleDb.OleDbParameter("@naam2",
OleDb.OleDbType.Char, 50)).Value = Chr(64 + i)
cmdA.Parameters.Add(New OleDb.OleDbParameter("@naam3",
OleDb.OleDbType.Char, 50)).Value = Chr(65 + i)
cmdA.Parameters.Add(New OleDb.OleDbParameter("@naam4",
OleDb.OleDbType.Char, 50)).Value = Chr(66 + i)
cmdA.Parameters.Add(New OleDb.OleDbParameter("@naam5",
OleDb.OleDbType.Char, 50)).Value = Chr(67 + i)
cmdA.ExecuteNonQuery()
Next

'Here start the real program from Eric
'Start program
'read the access dataset
cmdA.CommandText = "Select * from Sites"
Dim da1 As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(cmdA) Dim dsAcc As New DataSet
da1.Fill(dsAcc)
'read an empty sql dataset
Dim Con As New
SqlConnection("Server=(local);DataBase=Eric;Integr ated Security=SSPI")
Dim sqlDa As New SqlDataAdapter
sqlDa.SelectCommand = New SqlCommand("SELECT * FROM Sites", Con)
Dim cb As SqlCommandBuilder = New SqlCommandBuilder(sqlDa)
Dim dsSQL As New DataSet
sqlDa.Fill(dsSQL, "Sites") 'connect the dataset to the table
Dim ab As Integer
Dim dr As DataRow
For ab = 0 To dsAcc.Tables(0).Rows.Count - 1
dr = dsSQL.Tables(0).NewRow()
dr(0) = dsAcc.Tables(0).Rows(ab)(0)
dr(1) = dsAcc.Tables(0).Rows(ab)(1)
dr(2) = dsAcc.Tables(0).Rows(ab)(2)
dr(3) = dsAcc.Tables(0).Rows(ab)(3)
dr(4) = dsAcc.Tables(0).Rows(ab)(4)
dsSQL.Tables(0).Rows.Add(dr) 'add the new rows to the table
Next
If dsSQL.HasChanges Then
sqlDa.Update(dsSQL, "Sites")
End If
dsSQL.AcceptChanges()
dg.SetDataBinding(dsSQL, "Sites")
Conn.Close()
End Sub
/////////

Nov 20 '05 #17

P: n/a
Cor
Hi Eric,
'there is no error or locking trapping at all except for the non
existing database "eric"


This is a little bit cryptic, before it start it kills if it exist the
database "eric" and the file c:\test1\eric.mdb so before you start to check
that you not using that and otherwise rename it in the sample.

The trapping is for the first time because it trows otherwise an error and
stops the program if it not exist.


Nov 20 '05 #18

P: n/a
EMW
I didn't work, Cor and I'm now convinced it was not my code that is the
problem.
Just before the update command I bound the dataset to the datagrid, and
everything is in there.

I think, and the exception error seems to point it, that one of the fields
in the dataset has a different datatype than what I give it.

So that is what I'm now looking at.

Thanks a lot for your help.
Eric

"Cor" <no*@non.com> schreef in bericht
news:eO**************@tk2msftngp13.phx.gbl...
Hi Eric,
'there is no error or locking trapping at all except for the non
existing database "eric"

This is a little bit cryptic, before it start it kills if it exist the
database "eric" and the file c:\test1\eric.mdb so before you start to

check that you not using that and otherwise rename it in the sample.

The trapping is for the first time because it trows otherwise an error and
stops the program if it not exist.


Nov 20 '05 #19

P: n/a
Cor
Hi Eric,

You can write that dataset to disk and only have to use the command
ds.writexml("c:\test1\myxmltest.xml")

You see everything in XML style.

Cor
Nov 20 '05 #20

P: n/a
EMW
But I need it to be in SQL.

rg,
Eric

"Cor" <no*@non.com> schreef in bericht
news:Od*************@tk2msftngp13.phx.gbl...
Hi Eric,

You can write that dataset to disk and only have to use the command
ds.writexml("c:\test1\myxmltest.xml")

You see everything in XML style.

Cor

Nov 20 '05 #21

P: n/a
EMW
Since the SQl database doesn't want to work, what are my posibities with
XML?
To create an XML file is very easy, just one command, but is it fast.
When someone enters a number, can it be found really fast in a XML file and
can all the relevant data be read?

rg,
Eric
"Cor" <no*@non.com> schreef in bericht
news:Od*************@tk2msftngp13.phx.gbl...
Hi Eric,

You can write that dataset to disk and only have to use the command
ds.writexml("c:\test1\myxmltest.xml")

You see everything in XML style.

Cor

Nov 20 '05 #22

P: n/a
Cor
Just to see what is the format of that dataset not for real
Since the SQl database doesn't want to work, what are my posibities with
XML?
To create an XML file is very easy, just one command, but is it fast.
When someone enters a number, can it be found really fast in a XML file and can all the relevant data be read?

Nov 20 '05 #23

This discussion thread is closed

Replies have been disabled for this discussion.