14 Replies - 1642 Views - Last Post: 29 July 2014 - 04:52 PM

#1 pharaon  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 83
  • Joined: 18-October 07

syntax error in query. incomplete query clause

Posted 29 July 2014 - 04:02 AM

Public Sub savetxt(ByVal tblname As String, ByVal filedname As String, ByVal value As string)
        If con.State = ConnectionState.Closed Then con.Open()
        Dim saveinto As New OleDb.OleDbCommand("insert into'" & tblname & "'('" & filedname & "') VALUES ('" & value & "')", con)
        saveinto.ExecuteNonQuery()
        con.Close()
    End Sub

this is my command
what's wrong about it

Is This A Good Question/Topic? 0
  • +

Replies To: syntax error in query. incomplete query clause

#2 andrewsw  Icon User is offline

  • It's just been revoked!
  • member icon

Reputation: 3738
  • View blog
  • Posts: 13,067
  • Joined: 12-December 12

Re: syntax error in query. incomplete query clause

Posted 29 July 2014 - 04:39 AM

Access table and field-name delimiters are square brackets, not apostrophes, but you only need them if you are using reserved keywords as identifiers, or have some unusual characters in these names.

You should also be using parameterized queries.
Was This Post Helpful? 0
  • +
  • -

#3 pharaon  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 83
  • Joined: 18-October 07

Re: syntax error in query. incomplete query clause

Posted 29 July 2014 - 05:15 AM

it's code in class that i'll be calling in the form
Was This Post Helpful? 0
  • +
  • -

#4 pharaon  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 83
  • Joined: 18-October 07

Re: syntax error in query. incomplete query clause

Posted 29 July 2014 - 05:27 AM

done this code this time and still the same error
Public Sub savetxt(ByVal tblname As String, ByVal filedname As String, ByVal value As string)
        If con.State = ConnectionState.Closed Then con.Open()
        Dim saveinto As New OleDb.OleDbCommand("insert into @tblname(@filedname) VALUES (@value)", con)
        saveinto.Parameters.AddWithValue("@tblname", tblname)
        saveinto.Parameters.AddWithValue("@filedname", filedname)
        saveinto.Parameters.AddWithValue("@value", value)
        saveinto.ExecuteNonQuery()
        con.Close()
    End Sub


and that's is how I call it from the class into the form

Dim obj As New classcon
If Not String.IsNullOrEmpty(TextBox1.Text) Then
            Try
                obj.savetxt("Family", "Family_n", TextBox1.Text.Trim)
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
        Else

Was This Post Helpful? 0
  • +
  • -

#5 andrewsw  Icon User is offline

  • It's just been revoked!
  • member icon

Reputation: 3738
  • View blog
  • Posts: 13,067
  • Joined: 12-December 12

Re: syntax error in query. incomplete query clause

Posted 29 July 2014 - 06:28 AM

Oledb doesn't support named-parameters, although (oddly) you can supply them by name as long as you do so in the right order. Try this:
Public Sub savetxt(ByVal tblname As String, ByVal filedname As String, ByVal value As string)
        If con.State = ConnectionState.Closed Then con.Open()
        Dim saveinto As New OleDb.OleDbCommand("insert into ? (?) VALUES (?)", con)
        saveinto.Parameters.AddWithValue("@tblname", tblname)
        saveinto.Parameters.AddWithValue("@filedname", filedname)
        saveinto.Parameters.AddWithValue("@value", value)
        saveinto.ExecuteNonQuery()
        con.Close()
    End Sub


Was This Post Helpful? 0
  • +
  • -

#6 pharaon  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 83
  • Joined: 18-October 07

Re: syntax error in query. incomplete query clause

Posted 29 July 2014 - 06:32 AM

syntax error in insert into statement
I got this error now
Was This Post Helpful? 0
  • +
  • -

#7 andrewsw  Icon User is offline

  • It's just been revoked!
  • member icon

Reputation: 3738
  • View blog
  • Posts: 13,067
  • Joined: 12-December 12

Re: syntax error in query. incomplete query clause

Posted 29 July 2014 - 07:02 AM

Doh, I missed this for ages!! You cannot supply the table or field-names as parameters.

This post has been edited by andrewsw: 29 July 2014 - 07:05 AM

Was This Post Helpful? 0
  • +
  • -

#8 andrewsw  Icon User is offline

  • It's just been revoked!
  • member icon

Reputation: 3738
  • View blog
  • Posts: 13,067
  • Joined: 12-December 12

Re: syntax error in query. incomplete query clause

Posted 29 July 2014 - 07:10 AM

If you really want to supply the table and field-names as variables - although it is not a good practice - you could concatenate them to the sql-string:
Dim sSql As String = "insert into " & tblname & " (" & filedname & ") values (?)"

then supply the single parameter for @value.

Doing so defeats the purpose, and security, of using parameterized queries.

This post has been edited by andrewsw: 29 July 2014 - 07:13 AM

Was This Post Helpful? 0
  • +
  • -

#9 pharaon  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 83
  • Joined: 18-October 07

Re: syntax error in query. incomplete query clause

Posted 29 July 2014 - 07:27 AM

it work now thanks

there's something else I want help in
I'm making database to store family's data..so I make two tables one table is for the main family information and the second table is for the members of each family ( husband, wife, kids, ...etc.)
I'll connect the two tables with primary key family ID

well what I want to do is to find a way to determine the family id value in programmatically to be the same in the both tables because the user can put different values when he fill the family table data and the members table data

is there a good way

This post has been edited by pharaon: 29 July 2014 - 07:36 AM

Was This Post Helpful? 0
  • +
  • -

#10 alapee  Icon User is offline

  • Stressed Out Programmer

Reputation: 168
  • View blog
  • Posts: 2,003
  • Joined: 24-October 13

Re: syntax error in query. incomplete query clause

Posted 29 July 2014 - 11:17 AM

You might want to check into Normalization of your Database :Link

I would suggest that each person would have a unique key associated with them, then link them by a family key.
Was This Post Helpful? 0
  • +
  • -

#11 andrewsw  Icon User is offline

  • It's just been revoked!
  • member icon

Reputation: 3738
  • View blog
  • Posts: 13,067
  • Joined: 12-December 12

Re: syntax error in query. incomplete query clause

Posted 29 July 2014 - 12:30 PM

Once you've studied normalization you will also need to know how to create joins between tables in Access.

BTW Creating a Families database can quickly become very tricky, unless you keep it deliberately simply (for study purposes). People are members of more than one family, and you will have more than one family-Smith. I'm not trying to put you off creating this, just letting you know of possible challenges.

This post has been edited by andrewsw: 29 July 2014 - 12:35 PM

Was This Post Helpful? 0
  • +
  • -

#12 alapee  Icon User is offline

  • Stressed Out Programmer

Reputation: 168
  • View blog
  • Posts: 2,003
  • Joined: 24-October 13

Re: syntax error in query. incomplete query clause

Posted 29 July 2014 - 01:01 PM

Joins, Normalization, and knowing about Relationships of the tables go a long way in databases.

@andrewsw : I agree that it can become complicated but if the tables and members are set correctly it can be easy.
Was This Post Helpful? 0
  • +
  • -

#13 pharaon  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 83
  • Joined: 18-October 07

Re: syntax error in query. incomplete query clause

Posted 29 July 2014 - 02:13 PM

i really don't need joins since it all will be with vb.net sql statement which will be "inner join" that way vb will join the tables with the id key

what about using the auto number for the family id first then assign that number for each member of that family ?
or is there a better way

This post has been edited by pharaon: 29 July 2014 - 02:14 PM

Was This Post Helpful? 0
  • +
  • -

#14 alapee  Icon User is offline

  • Stressed Out Programmer

Reputation: 168
  • View blog
  • Posts: 2,003
  • Joined: 24-October 13

Re: syntax error in query. incomplete query clause

Posted 29 July 2014 - 02:26 PM

You might want to think of it as this, now remember this is just a suggestion there are many ways to tackle it.

In Table 1: You place Individual Information ie FirstName,MiddleName,DOB,BirthPlace,DOD,etc. with a primary key.
In Table 2 to x: You place the Family Information so you would have a Primary Key, With Several Foreign Keys (Primary Keys from Table 1)

Then you can inner join on the Family Tables with Individual Tables Information linked in.

Possibly you might want a Relationship Table. Though this could be done with other fields in the Individual Information as : Person's Father (with the ID for the father from the Individual Table), Person's Mother (With the ID for the Mother from the Individual Table).

It really just depends on how you lay out the tables.

Confused yet?
Was This Post Helpful? 0
  • +
  • -

#15 pharaon  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 83
  • Joined: 18-October 07

Re: syntax error in query. incomplete query clause

Posted 29 July 2014 - 04:52 PM

well what I'll do is like this
family table will continue family name, address, number of members, etc.
and of course the id of that family which will properly will be auto number ( or any thing better than auto number if some one got an idea)

now another table with information of each member in the family with the same ID if it's own family
so when I search for a person it will show up the person information from members table and it's own family information from the other table depending on the id

or searching for the family and show all the members of it by the id

that's what in my mind
if any one got something better please share
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1