INSERT INTO syntax error using commandbuilder

Commandbuilder creates INSERT INTO statement but puts ?'s for the

Page 1 of 1

2 Replies - 8011 Views - Last Post: 15 July 2009 - 04:29 PM Rate Topic: -----

#1 Smokinghand  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 3
  • Joined: 11-June 09

INSERT INTO syntax error using commandbuilder

Post icon  Posted 15 July 2009 - 11:08 AM

I am having trouble with a commandbuilder not creating the proper syntax for an INSERT INTO statement. This is the code I am having trouble with:
		con.Open()

		DA.Fill(ds, "Passwords")

		If Trim(txtUrl.Text) <> "" Then
			Dim cb As New OleDb.OleDbCommandBuilder(DA)
			Dim dsNewRow As DataRow

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

			dsNewRow.Item("address") = txtUrl.Text
			dsNewRow.Item("user") = txtUserNew.Text
			dsNewRow.Item("pass") = txtPassNew.Text

			ds.Tables("Passwords").Rows.Add(dsNewRow)
			SQL = cb.GetInsertCommand.CommandText
			MsgBox("insertcommand = " & SQL)

			DA.Update(ds, "Passwords")
			MsgBox("Password saved.")
		Else
			MsgBox("Please enter a website for the username and password.")

		End If
		con.Close()



I set a breakpoint and checked the insert command.txt and this is what I found:

SQL insert command "INSERT INTO passwords (Address, User, Pass) VALUES (?, ?, ?)"

I get the error "Syntax error in INSERT INTO statement" on this line:

DA.Update(ds, "Passwords")

I also checked the values in the new datarow (dsNewRow) and found the info there however it was in the item array and not in the separate items.

Could it be that it is placing it in the wrong place or is there something else in my code I am missing?

Is This A Good Question/Topic? 0
  • +

Replies To: INSERT INTO syntax error using commandbuilder

#2 fixo  Icon User is offline

  • D.I.C Regular

Reputation: 85
  • View blog
  • Posts: 335
  • Joined: 10-May 09

Re: INSERT INTO syntax error using commandbuilder

Posted 15 July 2009 - 01:56 PM

View PostSmokinghand, on 15 Jul, 2009 - 10:08 AM, said:

I am having trouble with a commandbuilder not creating the proper syntax for an INSERT INTO statement. This is the code I am having trouble with:
		con.Open()

		DA.Fill(ds, "Passwords")

		If Trim(txtUrl.Text) <> "" Then
			Dim cb As New OleDb.OleDbCommandBuilder(DA)
			Dim dsNewRow As DataRow

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

			dsNewRow.Item("address") = txtUrl.Text
			dsNewRow.Item("user") = txtUserNew.Text
			dsNewRow.Item("pass") = txtPassNew.Text

			ds.Tables("Passwords").Rows.Add(dsNewRow)
			SQL = cb.GetInsertCommand.CommandText
			MsgBox("insertcommand = " & SQL)

			DA.Update(ds, "Passwords")
			MsgBox("Password saved.")
		Else
			MsgBox("Please enter a website for the username and password.")

		End If
		con.Close()



I set a breakpoint and checked the insert command.txt and this is what I found:

SQL insert command "INSERT INTO passwords (Address, User, Pass) VALUES (?, ?, ?)"

I get the error "Syntax error in INSERT INTO statement" on this line:

DA.Update(ds, "Passwords")

I also checked the values in the new datarow (dsNewRow) and found the info there however it was in the item array and not in the separate items.

Could it be that it is placing it in the wrong place or is there something else in my code I am missing?

You need to intialize insert command before this line
Something like
da.InsertCommand = cb.GetInsertCommand (or New OleDbCommand)
da.InsertCommand.Connection=con
da.InsertCommand.CommandText=sql
da.Updat(ds,"Passwords")


~'J'~
Was This Post Helpful? 0
  • +
  • -

#3 Smokinghand  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 3
  • Joined: 11-June 09

Re: INSERT INTO syntax error using commandbuilder

Posted 15 July 2009 - 04:29 PM

View Postfixo, on 15 Jul, 2009 - 12:56 PM, said:

View PostSmokinghand, on 15 Jul, 2009 - 10:08 AM, said:

I am having trouble with a commandbuilder not creating the proper syntax for an INSERT INTO statement. This is the code I am having trouble with:
		con.Open()

		DA.Fill(ds, "Passwords")

		If Trim(txtUrl.Text) <> "" Then
			Dim cb As New OleDb.OleDbCommandBuilder(DA)
			Dim dsNewRow As DataRow

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

			dsNewRow.Item("address") = txtUrl.Text
			dsNewRow.Item("user") = txtUserNew.Text
			dsNewRow.Item("pass") = txtPassNew.Text

			ds.Tables("Passwords").Rows.Add(dsNewRow)
			SQL = cb.GetInsertCommand.CommandText
			MsgBox("insertcommand = " & SQL)

			DA.Update(ds, "Passwords")
			MsgBox("Password saved.")
		Else
			MsgBox("Please enter a website for the username and password.")

		End If
		con.Close()



I set a breakpoint and checked the insert command.txt and this is what I found:

SQL insert command "INSERT INTO passwords (Address, User, Pass) VALUES (?, ?, ?)"

I get the error "Syntax error in INSERT INTO statement" on this line:

DA.Update(ds, "Passwords")

I also checked the values in the new datarow (dsNewRow) and found the info there however it was in the item array and not in the separate items.

Could it be that it is placing it in the wrong place or is there something else in my code I am missing?

You need to intialize insert command before this line
Something like
da.InsertCommand = cb.GetInsertCommand (or New OleDbCommand)
da.InsertCommand.Connection=con
da.InsertCommand.CommandText=sql
da.Updat(ds,"Passwords")


~'J'~


I changed the fields that this routine uses to make it a little more secure (i.e changed the words passwords and user to something a little less obvious). This seems to have corrected itself. However I think if I do it the way you suggest that it will be more reliable.

Thanks

p.s. It wouldn't let me send you a thank you. It said that I couldn't send thank yous to this member. So I give you my thanks here.

This post has been edited by Smokinghand: 15 July 2009 - 04:38 PM

Was This Post Helpful? 1

Page 1 of 1