Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2003
    Location
    Canberra, ACT Australia
    Posts
    53

    Unanswered: Run-time error ‘-2147217904(80040e10)’

    Error message

    Run-time error ‘-2147217904(80040e10)’
    No valuegiven for one or more required parameters

    Running VB6 under XP, attempting to add a record to the Access table ‘clients’ in the database ‘dummymdb’


    Code:

    Dim dcnClients As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sql As String


    Set dcnClients = New ADODB.Connection
    dcnClients.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\contacts2\dummymdb.mdb;"
    Set rs = New ADODB.Recordset
    sql = "INSERT INTO CLIENTS(clinum,title,fname,sname,prefname,street1, street2,suburb,pcode,state,homephoone,workphone,mo bile,fax,fullname) VALUES (Me.CClientNumber.Text,Me.CTitle.Text,Me.CFname.Te xt,Me.CSname.Text,Me.Cpreferredname.Text,Me.CStree t1.Text,Me.CStreet2.Text,Me.CSuburb.Text,Me.CPostc ode.Text,Me.CState.Text,Me.CHomephone.Text,Me.Cwor kphone.Text,Me.CMobilephone.Text,Me.CFax.Text,me.F ULLTEMPNAME.text)"

    dcnClients.Open

    dcnClients.Execute sql, , adCmdText + adExecuteNoRecords ‘this is the highlighted line in the debug

    dcnClients.close

    fieldnames, and number of fields match up in the code

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    adCmdText + adExecuteNoRecords
    Why are you adding these two values? Surely it's a comma not a plus?
    George
    Home | Blog

  3. #3
    Join Date
    Jul 2003
    Location
    Canberra, ACT Australia
    Posts
    53

    compile error:

    compile error:
    wrong number of arguments or invalid property assignment

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Ca you explain to me what that line is supposed to do then?
    George
    Home | Blog

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ...just a quickie... do you really have a column called "homephoone"?
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by rodmead
    compile error:
    wrong number of arguments or invalid property assignment
    Add another comma to your .Execute command:

    ...Execute SQL ,,,AdCmdText ...
    Back to Access ... ADO is not the way to go for speed ...

  7. #7
    Join Date
    Jul 2003
    Location
    Canberra, ACT Australia
    Posts
    53

    thanks guys- this is now my approach (not pretty but it works)

    Set rs = New ADODB.Recordset
    sql2 = "select * from clients"

    rs.Open sql2, dcnClients, adOpenStatic, adLockOptimistic

    rs.AddNew

    rs.Fields(0).Value = Me.CClientNumber.Text
    rs.Fields(1).Value = Me.CTitle.Text
    rs.Fields(2).Value = Me.CFname.Text
    rs.Fields(3).Value = Me.CSname.Text
    rs.Fields(4).Value = Me.Cpreferredname.Text
    rs.Fields(5).Value = Me.CStreet1.Text
    rs.Fields(6).Value = Me.CStreet2.Text
    rs.Fields(7).Value = Me.CSuburb.Text
    rs.Fields(8).Value = Me.CPostcode.Text
    rs.Fields(9).Value = Me.CState.Text
    rs.Fields(10).Value = Me.CHomephone.Text
    rs.Fields(11).Value = Me.Cworkphone.Text
    rs.Fields(12).Value = Me.CMobilephone.Text
    rs.Fields(13).Value = Me.CFax.Text
    rs.Fields(14).Value = Me.FULLTEMPNAME.Text


    rs.Update


    dcnClients.Close

  8. #8
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    BTW.

    Opening a recordset, especially with Access, can be an expensive operation. At the very least, the entire index must be copied from the data file to the client. At worst, the entire table must be copied to the client.

    Now, with your original technique:
    Code:
    sql = "INSERT INTO CLIENTS(clinum,title,fname,sname,prefname,street1, street2,suburb,pcode,state,homephoone,workphone,mo bile,fax,fullname) VALUES (Me.CClientNumber.Text,Me.CTitle.Text,Me.CFname.Te xt,Me.CSname.Text,Me.Cpreferredname.Text,Me.CStree t1.Text,Me.CStreet2.Text,Me.CSuburb.Text,Me.CPostc ode.Text,Me.CState.Text,Me.CHomephone.Text,Me.Cwor kphone.Text,Me.CMobilephone.Text,Me.CFax.Text,me.F ULLTEMPNAME.text)"
    you have two issues. First, you need to construct a string containing the values of the textboxes. Second, you MUST include appropriate field delimiters. A text type database field requires apostrophes as field delimiters. And, while Date/Time fields require apostrophe field delimiters in all other databases, Access date/Time field delimiters are the hash character (#)

    In other words,
    Code:
    sql = "Insert into TestTable (TextField, DateField) Values ('" & Me.Text1.Text & "', #" & Me.Text2.Text & "#)"

    If you include a list of textboxes in your SQL string as you did, the literal values that you entered in your code will be passed to the database at runtime - NOT the contents of the text boxes...

    When you have quite a few fields in the table, and you don't want to open a recordset use 'tags' in your SQL string, and replace the tags with "live" data at runtime.

    Code:
    sql = "Insert into TestTable (TextField, DateField) Values ('~TEXT~', #~DATE~#)"
    sql=Replace(sql, "~TEXT~", me.Text1.Text  ' replace the Text 'Tag'
    sql=Replace(sql, "~DATE~", me.Text2.Text  ' replace the Date 'Tag'
    debug.Print sql
    cn.Execute sql
    Last edited by loquin; 03-19-07 at 15:08.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •