473,287 Members | 1,708 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

Full dataset into an empty SQL table

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

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
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
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
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
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
Cor
> if sqlDa.haschanges then
if dstoo.haschanges
Nov 20 '05 #9
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
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
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
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
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
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
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
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
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
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
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Bill C. | last post by:
Hi, I've got a simple console app that just reads an XML file into a DataSet then prints out a description of each table in the DataSet, including column names and row values for each column. ...
1
by: John | last post by:
Hi All, When I delete the last record from my dataset and then WriteXML() to the file; the Table itself, in the xml file, is removed. I need to prevent the table structure from being deleted if...
6
by: Mike P | last post by:
I have written a simple web service that basically takes a value input by a user and returns a dataset with all related data found in a database. In my client app use this code to get the data...
3
by: martin | last post by:
Hi, I am having trouble determining if my dataset is empty. I have function that populates a dataset from a database and then retuens it. If the database contains no data then the dataset will...
4
by: dtblankenship | last post by:
Hello everyone, I know this question has been asked many times in the forums, and after spending a few days reading, I am still confused as to the answer. I have a ListBox (lstBox),...
1
by: Mirna | last post by:
Hello, My problem is the following: If one of the tables in the Dataset is empty, the command "Page.Databind" is giving me this error : "INDEX 0 IS NOT NON-NEGATIVE AND BELOW TOTAL ROWS...
5
by: Roy Lawson | last post by:
I am having no problems connecting to a DB, creating a DataAdapter, and creating a dataset...and connecting to the data. Using the builtin data objects to do all this. My only problem now is...
5
by: John | last post by:
Hi all, I'm sorry I'm reposting this but the original was urgent and I do need closure on this. I'm calling a stored proc which does 4 "selects" and then I populate a dataset looping through...
2
by: Shum | last post by:
Hi! i have a question.. I'm filling a dataset from a table in which some rows are empty, i dont want those empty records to be filled in the dataset.. Does any one know how to restrict it to only...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.