Please help.....

[EMW]

Hi,

Could you please take a look and tell me what this means?
Cor has tried to help me, but we haven't found it yet.

Everytime I run this code, I get an exception error.

Here is my code:

Dim connStr As String = "Provider=Microsoft.JET.OLEDB.4.0;data source=" &
Me.txtFile.Text
Dim SQLstr As String = "SELECT * FROM sitelist"
Dim cn As New OleDb.OleDbConnection(connStr)
Dim da As New OleDb.OleDbDataAdapter(SQLstr, cn)
Dim ds As New DataSet
pgBar.Value = 0
pgBar.Refresh()
da.Fill(ds, "sitelist")
pgBar.Value += 1
pgBar.Refresh()
dgMain.DataSource = ds
dgMain.DataMember = "Sitelist"
dgMain.Refresh()
cn.Close()
'FileNaam = FileNaam.Replace("sitelijst.mdb", "sitelist.mdf")
Dim con As New SqlConnection("Server=(local);database=;Integrated
Security=SSPI")
Dim cmd1 As New SqlCommand("DROP DATABASE sitelist", con)
con.Open()
Try
cmd1.ExecuteNonQuery()
Catch ex As Exception
MsgBox(ex.ToString)
End Try
Dim strSQL As String = "CREATE DATABASE sitelist"
Dim cmd2 As New SqlCommand(strSQL, con)
Try
cmd2.ExecuteNonQuery()
Catch ex As Exception
MsgBox(ex.ToString)
End Try
cmd2.CommandText = "USE sitelist " & vbCrLf & _
"CREATE TABLE sites (sitenummer NVARCHAR(10), " & _
"adres NVARCHAR(255), gemeente NVARCHAR(25), " & _
"[Type site] NVARCHAR(25), bsc NVARCHAR(35), " & _
"Safety NVARCHAR(25), [X pos] NVARCHAR(15), " & _
"[Y pos] NVARCHAR(15), verantwoordelijke NVARCHAR(25), " & _
"online NVARCHAR(15), CONSTRAINT [pk_indentFT] PRIMARY " & _
"KEY CLUSTERED(sitenummer))"
Try
cmd2.ExecuteNonQuery()
Catch ex As Exception
'do nothing
End Try
con.Close()
Dim sqlNewstr As String
con = New SqlConnection("Server=(local);Database=sitelist;In tegrated
Security=SSPI;")
Dim sqlDa = New SqlDataAdapter
sqlDa.SelectCommand = New SqlCommand("SELECT * FROM sites", con)
Dim cb As SqlCommandBuilder = New SqlCommandBuilder(sqlDa)
cb.QuoteSuffix = "["
cb.QuotePrefix = "]"
Dim dstoo As New DataSet
sqlDa.Fill(dstoo, "Sites") 'connect the dataset to the empty table
Dim ab As Integer
Dim dr As DataRow
For ab = 0 To ds.Tables(0).Rows.Count - 1
dr = dstoo.Tables(0).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)
dstoo.Tables(0).Rows.Add(dr) 'add the new rows to the table
Next
dgSec.DataSource = dstoo
dgSec.DataMember = dstoo.Tables(0).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.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
End Sub

and this is the message I get on the line
"sqlDa.Update(dstoo,dstoo.Tables(0).TableName) "

System.Data.SqlClient.SqlException: Line 1: wrong syntaxis at ].
at
Microsoft.VisualBasic.CompilerServices.LateBinding .InternalLateCall(Object
o, Type objType, String name, Object[] args, String[] paramnames, Boolean[]
CopyBack, Boolean IgnoreReturn)
at Microsoft.VisualBasic.CompilerServices.LateBinding .LateCall(Object o,
Type objType, String name, Object[] args, String[] paramnames, Boolean[]
CopyBack)
at MDBtoSDF.Form1.btConvert_Click(Object sender, EventArgs e) in
D:\VB.NET projecten\PPC\MDBtoSDF\Form1.vb:line 264

I don't have a clue what it all means...

rg,
Eric

[Miha Markic]

Hi,

It seems to me that commandbuilder generated invalid insert sql statament.
Can you extract it? (sqlDa.InsertCommand.CommandText)

--
Miha Markic - RightHand .NET consulting & development
miha at rthand com

"EMW" <so*****@microsoft.com> wrote in message
news:3f**********************@dreader2.news.tiscal i.nl...

Hi,

Could you please take a look and tell me what this means?
Cor has tried to help me, but we haven't found it yet.

Everytime I run this code, I get an exception error.

Here is my code:

Dim connStr As String = "Provider=Microsoft.JET.OLEDB.4.0;data source=" &
Me.txtFile.Text
Dim SQLstr As String = "SELECT * FROM sitelist"
Dim cn As New OleDb.OleDbConnection(connStr)
Dim da As New OleDb.OleDbDataAdapter(SQLstr, cn)
Dim ds As New DataSet
pgBar.Value = 0
pgBar.Refresh()
da.Fill(ds, "sitelist")
pgBar.Value += 1
pgBar.Refresh()
dgMain.DataSource = ds
dgMain.DataMember = "Sitelist"
dgMain.Refresh()
cn.Close()
'FileNaam = FileNaam.Replace("sitelijst.mdb", "sitelist.mdf")
Dim con As New SqlConnection("Server=(local);database=;Integrated
Security=SSPI")
Dim cmd1 As New SqlCommand("DROP DATABASE sitelist", con)
con.Open()
Try
cmd1.ExecuteNonQuery()
Catch ex As Exception
MsgBox(ex.ToString)
End Try
Dim strSQL As String = "CREATE DATABASE sitelist"
Dim cmd2 As New SqlCommand(strSQL, con)
Try
cmd2.ExecuteNonQuery()
Catch ex As Exception
MsgBox(ex.ToString)
End Try
cmd2.CommandText = "USE sitelist " & vbCrLf & _
"CREATE TABLE sites (sitenummer NVARCHAR(10), " & _
"adres NVARCHAR(255), gemeente NVARCHAR(25), " & _
"[Type site] NVARCHAR(25), bsc NVARCHAR(35), " & _
"Safety NVARCHAR(25), [X pos] NVARCHAR(15), " & _
"[Y pos] NVARCHAR(15), verantwoordelijke NVARCHAR(25), " & _
"online NVARCHAR(15), CONSTRAINT [pk_indentFT] PRIMARY " & _
"KEY CLUSTERED(sitenummer))"
Try
cmd2.ExecuteNonQuery()
Catch ex As Exception
'do nothing
End Try
con.Close()
Dim sqlNewstr As String
con = New SqlConnection("Server=(local);Database=sitelist;In tegrated
Security=SSPI;")
Dim sqlDa = New SqlDataAdapter
sqlDa.SelectCommand = New SqlCommand("SELECT * FROM sites", con)
Dim cb As SqlCommandBuilder = New SqlCommandBuilder(sqlDa)
cb.QuoteSuffix = "["
cb.QuotePrefix = "]"
Dim dstoo As New DataSet
sqlDa.Fill(dstoo, "Sites") 'connect the dataset to the empty table
Dim ab As Integer
Dim dr As DataRow
For ab = 0 To ds.Tables(0).Rows.Count - 1
dr = dstoo.Tables(0).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)
dstoo.Tables(0).Rows.Add(dr) 'add the new rows to the table
Next
dgSec.DataSource = dstoo
dgSec.DataMember = dstoo.Tables(0).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.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
End Sub

and this is the message I get on the line
"sqlDa.Update(dstoo,dstoo.Tables(0).TableName) "

System.Data.SqlClient.SqlException: Line 1: wrong syntaxis at ].
at
Microsoft.VisualBasic.CompilerServices.LateBinding .InternalLateCall(Object
o, Type objType, String name, Object[] args, String[] paramnames, Boolean[] CopyBack, Boolean IgnoreReturn)
at Microsoft.VisualBasic.CompilerServices.LateBinding .LateCall(Object o, Type objType, String name, Object[] args, String[] paramnames, Boolean[]
CopyBack)
at MDBtoSDF.Form1.btConvert_Click(Object sender, EventArgs e) in
D:\VB.NET projecten\PPC\MDBtoSDF\Form1.vb:line 264

I don't have a clue what it all means...

rg,
Eric

[EMW]

Sorry, what do you mean?
"Miha Markic" <miha at rthand com> schreef in bericht
news:Oi**************@TK2MSFTNGP11.phx.gbl...

Hi,

It seems to me that commandbuilder generated invalid insert sql statament.
Can you extract it? (sqlDa.InsertCommand.CommandText)

--
Miha Markic - RightHand .NET consulting & development
miha at rthand com

"EMW" <so*****@microsoft.com> wrote in message
news:3f**********************@dreader2.news.tiscal i.nl...

Hi,

Could you please take a look and tell me what this means?
Cor has tried to help me, but we haven't found it yet.

Everytime I run this code, I get an exception error.

Here is my code:

Dim connStr As String = "Provider=Microsoft.JET.OLEDB.4.0;data source=" & Me.txtFile.Text
Dim SQLstr As String = "SELECT * FROM sitelist"
Dim cn As New OleDb.OleDbConnection(connStr)
Dim da As New OleDb.OleDbDataAdapter(SQLstr, cn)
Dim ds As New DataSet
pgBar.Value = 0
pgBar.Refresh()
da.Fill(ds, "sitelist")
pgBar.Value += 1
pgBar.Refresh()
dgMain.DataSource = ds
dgMain.DataMember = "Sitelist"
dgMain.Refresh()
cn.Close()
'FileNaam = FileNaam.Replace("sitelijst.mdb", "sitelist.mdf")
Dim con As New SqlConnection("Server=(local);database=;Integrated
Security=SSPI")
Dim cmd1 As New SqlCommand("DROP DATABASE sitelist", con)
con.Open()
Try
cmd1.ExecuteNonQuery()
Catch ex As Exception
MsgBox(ex.ToString)
End Try
Dim strSQL As String = "CREATE DATABASE sitelist"
Dim cmd2 As New SqlCommand(strSQL, con)
Try
cmd2.ExecuteNonQuery()
Catch ex As Exception
MsgBox(ex.ToString)
End Try
cmd2.CommandText = "USE sitelist " & vbCrLf & _
"CREATE TABLE sites (sitenummer NVARCHAR(10), " & _
"adres NVARCHAR(255), gemeente NVARCHAR(25), " & _
"[Type site] NVARCHAR(25), bsc NVARCHAR(35), " & _
"Safety NVARCHAR(25), [X pos] NVARCHAR(15), " & _
"[Y pos] NVARCHAR(15), verantwoordelijke NVARCHAR(25), " & _
"online NVARCHAR(15), CONSTRAINT [pk_indentFT] PRIMARY " & _
"KEY CLUSTERED(sitenummer))"
Try
cmd2.ExecuteNonQuery()
Catch ex As Exception
'do nothing
End Try
con.Close()
Dim sqlNewstr As String
con = New SqlConnection("Server=(local);Database=sitelist;In tegrated
Security=SSPI;")
Dim sqlDa = New SqlDataAdapter
sqlDa.SelectCommand = New SqlCommand("SELECT * FROM sites", con)
Dim cb As SqlCommandBuilder = New SqlCommandBuilder(sqlDa)
cb.QuoteSuffix = "["
cb.QuotePrefix = "]"
Dim dstoo As New DataSet
sqlDa.Fill(dstoo, "Sites") 'connect the dataset to the empty table
Dim ab As Integer
Dim dr As DataRow
For ab = 0 To ds.Tables(0).Rows.Count - 1
dr = dstoo.Tables(0).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)
dstoo.Tables(0).Rows.Add(dr) 'add the new rows to the table
Next
dgSec.DataSource = dstoo
dgSec.DataMember = dstoo.Tables(0).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.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
End Sub

and this is the message I get on the line
"sqlDa.Update(dstoo,dstoo.Tables(0).TableName) "

System.Data.SqlClient.SqlException: Line 1: wrong syntaxis at ].
at
Microsoft.VisualBasic.CompilerServices.LateBinding .InternalLateCall(Object o, Type objType, String name, Object[] args, String[] paramnames, Boolean[]

CopyBack, Boolean IgnoreReturn)
at

Microsoft.VisualBasic.CompilerServices.LateBinding .LateCall(Object o,

Type objType, String name, Object[] args, String[] paramnames, Boolean[]
CopyBack)
at MDBtoSDF.Form1.btConvert_Click(Object sender, EventArgs e) in
D:\VB.NET projecten\PPC\MDBtoSDF\Form1.vb:line 264

I don't have a clue what it all means...

rg,
Eric

[Cor]

Hi Eric,

You took a big part of my example, but some things you did not and when you
compare it it is easy to see. I tested my example and it is full working.

I see direct this small ones change them, test and message again and take a
look at the last sentence of this message.

"> Dim sqlDa = New SqlDataAdapter
This is not in my example it should give an error it is
Dim sqlDA as New SqlDataAdapter

sqlDa.Update(dstoo, dstoo.Tables(0).TableName)

And this one I know you had it in your first code also (translated to yours
it should be)
sqlDa.Update(dstoo, "Sites")

but better is (not for your program, but as example)
sqlDa.Update(dstoo.getchanges, "Sites")

change this first and look than again.

And this part I would not take in a real program, it was only for the
example. This deletes every time the database "sitelist" automaticly in your
SQL server, I dont think you want that, or maybe you want and delete it
afterwards direct.
\\\\.
Dim con As New SqlConnection("Server=(local);database=;Integrated
Security=SSPI")
Dim cmd1 As New SqlCommand("DROP DATABASE sitelist", con)
con.Open()
Try
cmd1.ExecuteNonQuery()
Catch ex As Exception
MsgBox(ex.ToString)
End Try
////

Cor

[EMW]

Thanks, Cor. I didn't see that first typo....

As for the second part, "sites" is the tablename so it should work, but I
changed it.
Unforunatly, stil the same error message...

As for the DROP DATABASE part, that is just what I want. Every month the
list is renewed so the table has to be created again.

As for the error messsage, I still don't have a clue of what it means...

rg,
Eric

"Cor" <no*@non.com> schreef in bericht
news:%2****************@TK2MSFTNGP09.phx.gbl...

Hi Eric,

You took a big part of my example, but some things you did not and when you compare it it is easy to see. I tested my example and it is full working.

I see direct this small ones change them, test and message again and take a look at the last sentence of this message.

"> Dim sqlDa = New SqlDataAdapter
This is not in my example it should give an error it is
Dim sqlDA as New SqlDataAdapter

sqlDa.Update(dstoo, dstoo.Tables(0).TableName) And this one I know you had it in your first code also (translated to

yours it should be)
sqlDa.Update(dstoo, "Sites")

but better is (not for your program, but as example)
sqlDa.Update(dstoo.getchanges, "Sites")

change this first and look than again.

And this part I would not take in a real program, it was only for the
example. This deletes every time the database "sitelist" automaticly in your SQL server, I dont think you want that, or maybe you want and delete it
afterwards direct.
\\\\.
Dim con As New SqlConnection("Server=(local);database=;Integrated
Security=SSPI")
Dim cmd1 As New SqlCommand("DROP DATABASE sitelist", con)
con.Open()
Try
cmd1.ExecuteNonQuery()
Catch ex As Exception
MsgBox(ex.ToString)
End Try
////

Cor

[Cor]

>

As for the second part, "sites" is the tablename so it should work, but I
changed it.
Unforunatly, stil the same error message...

You are right about that tablename "Sites", only never did it that way so it
did look strange to me. I hope you did it with an uppercase or changed it
back as it was so we are now not looking to an error because of a lowercase.

Do you see that datagrid before the update?

[Cor]

Eric,

I oversaw this in your program,

When I add this in my sample program also, I get almost the same error as
you,
cb.QuoteSuffix = "["
cb.QuotePrefix = "]"
I don't know why you added it, but try it once with removing it.

Cor

[EMW]

It is absolutly amazing, but I got it working.

It was those lines with the quotesuffix/prefix AND the names of the columns
in the sql database.
One of the names was "Type Site" and the exception came because Site is used
for something else.
I made it one word "TypeSite" and the problem was fixed.

Thanks for all your help and time!!!!
rg,
Eric

"Cor" <no*@non.com> schreef in bericht
news:eQ**************@TK2MSFTNGP09.phx.gbl...

Eric,

I oversaw this in your program,

When I add this in my sample program also, I get almost the same error as
you,
cb.QuoteSuffix = "["
cb.QuotePrefix = "]"
I don't know why you added it, but try it once with removing it.

Cor

[Cor]

Hi Eric,

That is the second time this happens to me, the last time it was with the
word Action, I hate SQL.

But I am glad you solved it, don't forget to add all things as error and
lock trapping.

It did cost me PSV but I am happy it is not with Ajax tonight

Cor

It was those lines with the quotesuffix/prefix AND the names of the columns in the sql database.
One of the names was "Type Site" and the exception came because Site is used for something else.
I made it one word "TypeSite" and the problem was fixed.