Please help.....

  • Thread starter Thread starter EMW
  • Start date Start date
E

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;Integrated
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
 
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 said:
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;Integrated
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
Click to expand...
 
Sorry, what do you mean?


Miha Markic said:
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 said:
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;Integrated
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
Click to expand...
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
Click to expand...
Click to expand...
 
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)
Click to expand...
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
 
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
 
As for the second part, "sites" is the tablename so it should work, but I
changed it.
Unforunatly, stil the same error message...
Click to expand...

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?
 
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
 
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
 
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
 
Back
Top