[EndLessMind]

Hi. If this is the "advanced" area of the VB.net forum. then maybe someone in here can help me with a problem i have.
I posted is some days ago but people miss it or they can´t help me.
Here is the problem:

When i try do commit changes or add a new record i get "Syntax error in INSERT INTO-expression"
the thing is i´ve used the same code to my test project ( but with less columns in the database )
and in the test project it works fine. there is the code for the "save / commit changes "

Private Sub ToolStripButton1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton1.Click
		If My.Settings.chang = True Then
			Dim cb As New OleDb.OleDbCommandBuilder(da)
			ds.Tables("Database2").Rows(inc).Item(1) = txtfm.Text
			ds.Tables("Database2").Rows(inc).Item(2) = txtem.Text
			ds.Tables("Database2").Rows(inc).Item(3) = txtga.Text
			ds.Tables("Database2").Rows(inc).Item(4) = txtpn.Text
			ds.Tables("Database2").Rows(inc).Item(5) = txts.Text
			ds.Tables("Database2").Rows(inc).Item(6) = txttm.Text
			ds.Tables("Database2").Rows(inc).Item(7) = txtth.Text
			ds.Tables("Database2").Rows(inc).Item(8) = txtma.Text
			ds.Tables("Database2").Rows(inc).Item(9) = txta1.Text
			ds.Tables("Database2").Rows(inc).Item(10) = txtpc.Text
			ds.Tables("Database2").Rows(inc).Item(11) = txtgf.Text
			ds.Tables("Database2").Rows(inc).Item(12) = cmba.Text
			ds.Tables("Database2").Rows(inc).Item(13) = txthd.Text
			ds.Tables("Database2").Rows(inc).Item(14) = cmbmt.Text
			ds.Tables("Database2").Rows(inc).Item(15) = cmbop.Text
			ds.Tables("Database2").Rows(inc).Item(16) = cmbdl.Text
			ds.Tables("Database2").Rows(inc).Item(17) = txtn.Text
			ds.Tables("Database2").Rows(inc).Item(18) = txttmf.Text
			ds.Tables("Database2").Rows(inc).Item(19) = txtthf.Text
			ds.Tables("Database2").Rows(inc).Item(20) = txtgaf.Text
			ds.Tables("Database2").Rows(inc).Item(21) = txtpnf.Text
			ds.Tables("Database2").Rows(inc).Item(22) = txtsf.Text
			ds.Tables("Database2").Rows(inc).Item(23) = txta2.Text
			ds.Tables("Database2").Rows(inc).Item(24) = txtsb.Text
			'ds.Tables("Database").Rows(inc).Item(25) = BindingNavigatorPositionItem.Text
			ds.Tables("Database2").Rows(inc).Item(26) = txtp.Text
			ds.Tables("Database2").Rows(inc).Item(27) = txtep.Text
			da.Update(ds, "Database2")
			MsgBox("Data updated")
		ElseIf My.Settings.chang = False Then
			If inc <> -1 Then

				Dim cb As New OleDb.OleDbCommandBuilder(da)
				Dim dsNewRow As DataRow

				dsNewRow = ds.Tables("Database2").NewRow()


				dsNewRow.Item(1) = txtfm.Text
				dsNewRow.Item(2) = txtem.Text
				dsNewRow.Item(3) = txtga.Text
				dsNewRow.Item(4) = txtpn.Text
				dsNewRow.Item(5) = txts.Text
				dsNewRow.Item(6) = txttm.Text
				dsNewRow.Item(7) = txtth.Text
				dsNewRow.Item(8) = txtma.Text
				dsNewRow.Item(9) = txta1.Text
				dsNewRow.Item(10) = txtpc.Text
				dsNewRow.Item(11) = txtgf.Text
				dsNewRow.Item(12) = cmba.Text
				dsNewRow.Item(13) = txthd.Text
				dsNewRow.Item(14) = cmbmt.Text
				dsNewRow.Item(15) = cmbop.Text
				dsNewRow.Item(16) = cmbdl.Text
				dsNewRow.Item(17) = txtn.Text
				dsNewRow.Item(18) = txttmf.Text
				dsNewRow.Item(19) = txtthf.Text
				dsNewRow.Item(20) = txtgaf.Text
				dsNewRow.Item(21) = txtpnf.Text
				dsNewRow.Item(22) = txtsf.Text
				dsNewRow.Item(23) = txta2.Text
				dsNewRow.Item(24) = txtsb.Text
				'dsNewRow.Item(25) = BindingNavigatorPositionItem.Text
				dsNewRow.Item(26) = txtp.Text
				dsNewRow.Item(27) = txtep.Text

				ds.Tables("Database2").Rows.Add(dsNewRow)

				da.Update(ds, "Database2")

				MsgBox("New Record added to the Database")
				My.Settings.chang = True
				'btnCommit.Enabled = False
				'btnAddNew.Enabled = True
				'btnUpdate.Enabled = True
				'btnDelete.Enabled = True

			End If
		End If
	End Sub

Dim ds As New DataSet | Dim da As OleDb.OleDbDataAdapter

This post has been edited by EndLessMind: 02 February 2010 - 12:01 PM

[EndLessMind]

why was i moved?

[PsychoCoder]

Because the VB.NET Programmers section isn't really for asking for help, it's more for advanced VB.NET discussion (It even says so in the forum rules for that forum). While I'm not the one who moved it to the regular VB.NET forum, I would have had I seen it there

[EndLessMind]

PsychoCoder, on 1 Feb, 2010 - 02:47 PM, said:

Because the VB.NET Programmers section isn't really for asking for help, it's more for advanced VB.NET discussion (It even says so in the forum rules for that forum). While I'm not the one who moved it to the regular VB.NET forum, I would have had I seen it there

hehe.. but really.. i just wanted to discuss what the problem could be (A)

[ZRonZ]

EndLessMind, on 1 Feb, 2010 - 02:23 PM, said:

Hi. If this is the "advanced" area of the VB.net forum. then maybe someone in here can help me with a problem i have.
I posted is some days ago but people miss it or they can´t help me.
Here is the problem:

When i try do commit changes or add a new record i get "Syntax error in INSERT INTO-expression"
the thing is i´ve used the same code to my test project ( but with less columns in the database )
and in the test project it works fine. there is the code for the "save / commit changes "

I haven't done any Access but I have SQL but I don't have my reference book with me. So here are some thoughts:

How about showing the INSERT INTO expression?

When it was working earlier, what changed? Did you add fields to the table or did you just add data to fill fields that were already existing and just not being filled?

In your old post you mentioned that another command didn't work but you got it working. The two should basically be the same with the exception of "INSERT INTO" versus what the other command was. What did you do to fix that? Did you try copying it and changing it to "INSERT INTO"?

With Item(25) commented out I would look at the insert. Is it trying to insert 27 fields into the database when you're only giving it 26?

Are all of your database field data types text?

[EndLessMind]

in the test project i hade a other database.. but still ms access. the only different in the code is that now there is more columns/fields.

about the item(25)
i added it to the wrong textbox first but i added a new textbox and now that would be dsNewRow.Item(25) = txtmr.text
but still get the errors.

and what do you mean with INSERT INTO expression?
what´s that?

This post has been edited by EndLessMind: 01 February 2010 - 04:25 PM

[ZRonZ]

EndLessMind, on 1 Feb, 2010 - 03:24 PM, said:

in the test project i hade a other database.. but still ms access. the only different in the code is that now there is more columns/fields.

about the item(25)
i added it to the wrong textbox first but i added a new textbox and now that would be dsNewRow.Item(25) = txtmr.text
but still get the errors.

and what do you mean with INSERT INTO expression?
what´s that?

Quote

When i try do commit changes or add a new record i get "Syntax error in INSERT INTO-expression"

Your code is trying to insert a record into the MS Access database table. But, it's not being successful. Hence the syntax error.

Check to make sure that your database table's structure matches what you're trying to put in to the table. Check field by field. Normally you wouldn't expect that problem to cause a syntax error but I've seen it.

I'll see if I have any resources in my library that might shed some light on the topic.

[EndLessMind]

ZRonZ, on 01 February 2010 - 04:38 PM, said:

<!--quoteo(post=912297:date=1 Feb, 2010 - 03:24 PM:name=EndLessMind)--><div class='quotetop'>QUOTE(EndLessMind @ 1 Feb, 2010 - 03:24 PM) </div><div class='quotemain'><!--quotec-->
in the test project i hade a other database.. but still ms access. the only different in the code is that now there is more columns/fields.

about the item(25)
i added it to the wrong textbox first but i added a new textbox and now that would be dsNewRow.Item(25) = txtmr.text
but still get the errors.

and what do you mean with INSERT INTO expression?
what´s that?
<!--QuoteEnd--></div><!--QuoteEEnd-->

<!--quoteo--><div class='quotetop'>QUOTE</div><div class='quotemain'><!--quotec-->
When i try do commit changes or add a new record i get "Syntax error in INSERT INTO-expression"
<!--QuoteEnd--></div><!--QuoteEEnd-->

Your code is trying to insert a record into the MS Access database table. But, it's not being successful. Hence the syntax error.

Check to make sure that your database table's structure matches what you're trying to put in to the table. Check field by field. Normally you wouldn't expect that problem to cause a syntax error but I've seen it.

I'll see if I have any resources in my library that might shed some light on the topic.

yes i dose match.

[Quecumber256]

For what its worth I noticed this:
If My.Settings.chang = True Then

Shouldn't it be-> If My.Settings.change = True Then?

It would give a snytax error because the IF statement is incomplete.

[EndLessMind]

Quecumber256, on 02 February 2010 - 02:54 PM, said:

For what its worth I noticed this:
If My.Settings.chang = True Then

Shouldn't it be-> If My.Settings.change = True Then?

It would give a snytax error because the IF statement is incomplete.

no.. i just spell´d it wrong. i did type "chang" in "my.settings" and anyway. VB would correct it by it self.

and no. that would not give a Syntax error. i would just say "change s not declared in my.settings"

This post has been edited by EndLessMind: 03 February 2010 - 06:29 AM

[Sethro117]

I notice you have this line

'ds.Tables("Database").Rows(inc).Item(25) = BindingNavigatorPositionItem.Text

and this line

'dsNewRow.Item(25) = BindingNavigatorPositionItem.Text

commented out. In your database is that Field allowed to be Null?

[EndLessMind]

Sethro117, on 02 February 2010 - 11:19 PM, said:

I notice you have this line

'ds.Tables("Database").Rows(inc).Item(25) = BindingNavigatorPositionItem.Text

and this line

'dsNewRow.Item(25) = BindingNavigatorPositionItem.Text

commented out. In your database is that Field allowed to be Null?

no.. but those lines now look like

 dsNewRow.Item(25) = txtmr.Text 

and

ds.Tables("Database2").Rows(inc).Item(25) = txtmr.Text

but i still have the error i´ve described before.

This post has been edited by EndLessMind: 03 February 2010 - 06:23 AM

[CharlieMay]

Could you maybe post your project with an example database? (I assume it's Access since you're using OLEDB)

[EndLessMind]

CharlieMay, on 03 February 2010 - 05:37 AM, said:

Could you maybe post your project with an example database? (I assume it's Access since you're using OLEDB)

sure i can :)
some part may be in Swedish.


Username: Dreamincode
Password: dream

Download here
To logg on on the program: go to "fil --> Logga in"
To got the the function with the error: Logg on and then go to "visa -> medlems register"

[CharlieMay]

OK, The Swedish is really throwing me and I have not gotten the program to run

BUT!!!

I did find in Database2.mdb that you have a field "Anteckningar:" and I think the ":" is throwing the error you're receiving.