Please help.....

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
 
M

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 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...
 
E

EMW

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...
 
C

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)
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
 
E

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
 
C

Cor

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?
 
C

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
 
E

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
 
C

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Error Message :-Update requires a valid InsertCommand when passed DataRow collection with new rows. 0
Column 'column' does not belong to table Table 0
System.Data.DataSet.Tables' is a 'property' but is used like a 'method' 3
deleting rows with SQLCommandBuilder 2
Probem: No size set for variable length data type: String, using OracleDataAdapter 4
getting error on record duplication 18
Add Record to MS Access Database 11
Return data to listbox 2

Top