database problem still standing.

  • (3 Pages)
  • +
  • 1
  • 2
  • 3

35 Replies - 1792 Views - Last Post: 05 February 2010 - 06:41 PM Rate Topic: -----

#1 EndLessMind  Icon User is offline

  • These are the droids you're looking for
  • member icon

Reputation: 194
  • View blog
  • Posts: 1,099
  • Joined: 13-March 09

database problem still standing.

Posted 01 February 2010 - 03:23 PM

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


Is This A Good Question/Topic? 0
  • +

Replies To: database problem still standing.

#2 EndLessMind  Icon User is offline

  • These are the droids you're looking for
  • member icon

Reputation: 194
  • View blog
  • Posts: 1,099
  • Joined: 13-March 09

Re: database problem still standing.

Posted 01 February 2010 - 03:45 PM

why was i moved?
Was This Post Helpful? 0
  • +
  • -

#3 PsychoCoder  Icon User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1641
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: database problem still standing.

Posted 01 February 2010 - 03:47 PM

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 :)
Was This Post Helpful? 0
  • +
  • -

#4 EndLessMind  Icon User is offline

  • These are the droids you're looking for
  • member icon

Reputation: 194
  • View blog
  • Posts: 1,099
  • Joined: 13-March 09

Re: database problem still standing.

Posted 01 February 2010 - 03:50 PM

View PostPsychoCoder, 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)
Was This Post Helpful? 0
  • +
  • -

#5 ZRonZ  Icon User is offline

  • D.I.C Head

Reputation: 28
  • View blog
  • Posts: 206
  • Joined: 09-January 09

Re: database problem still standing.

Posted 01 February 2010 - 04:12 PM

View PostEndLessMind, 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?
Was This Post Helpful? 0
  • +
  • -

#6 EndLessMind  Icon User is offline

  • These are the droids you're looking for
  • member icon

Reputation: 194
  • View blog
  • Posts: 1,099
  • Joined: 13-March 09

Re: database problem still standing.

Posted 01 February 2010 - 04:24 PM

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

Was This Post Helpful? 0
  • +
  • -

#7 ZRonZ  Icon User is offline

  • D.I.C Head

Reputation: 28
  • View blog
  • Posts: 206
  • Joined: 09-January 09

Re: database problem still standing.

Posted 01 February 2010 - 05:38 PM

View PostEndLessMind, 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.
Was This Post Helpful? 0
  • +
  • -

#8 EndLessMind  Icon User is offline

  • These are the droids you're looking for
  • member icon

Reputation: 194
  • View blog
  • Posts: 1,099
  • Joined: 13-March 09

Re: database problem still standing.

Posted 02 February 2010 - 11:52 AM

View PostZRonZ, 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) View Post</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.
Was This Post Helpful? 0
  • +
  • -

#9 Quecumber256  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 02-February 10

Re: database problem still standing.

Posted 02 February 2010 - 03:54 PM

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.
Was This Post Helpful? 0
  • +
  • -

#10 EndLessMind  Icon User is offline

  • These are the droids you're looking for
  • member icon

Reputation: 194
  • View blog
  • Posts: 1,099
  • Joined: 13-March 09

Re: database problem still standing.

Posted 02 February 2010 - 11:35 PM

View PostQuecumber256, 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

Was This Post Helpful? 0
  • +
  • -

#11 Sethro117  Icon User is offline

  • Still the sexiest mofo.
  • member icon

Reputation: 236
  • View blog
  • Posts: 2,378
  • Joined: 14-January 09

Re: database problem still standing.

Posted 03 February 2010 - 12:19 AM

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?
Was This Post Helpful? 0
  • +
  • -

#12 EndLessMind  Icon User is offline

  • These are the droids you're looking for
  • member icon

Reputation: 194
  • View blog
  • Posts: 1,099
  • Joined: 13-March 09

Re: database problem still standing.

Posted 03 February 2010 - 06:15 AM

View PostSethro117, 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

Was This Post Helpful? 0
  • +
  • -

#13 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: database problem still standing.

Posted 03 February 2010 - 06:37 AM

Could you maybe post your project with an example database? (I assume it's Access since you're using OLEDB)
Was This Post Helpful? 0
  • +
  • -

#14 EndLessMind  Icon User is offline

  • These are the droids you're looking for
  • member icon

Reputation: 194
  • View blog
  • Posts: 1,099
  • Joined: 13-March 09

Re: database problem still standing.

Posted 03 February 2010 - 07:01 AM

View PostCharlieMay, 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"
Was This Post Helpful? 0
  • +
  • -

#15 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: database problem still standing.

Posted 03 February 2010 - 08:09 AM

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.
Was This Post Helpful? 0
  • +
  • -

  • (3 Pages)
  • +
  • 1
  • 2
  • 3