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;Integrated 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
/////////