Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2003
    Location
    NY
    Posts
    15

    Unanswered: runtime error 80040e37

    hi:

    i m new to VB.thatswhy i have some problem in connecting VB with sql server.actually i m able to connect but it would nt run the query,giving me the run time error "invalid object name <tablename>".
    in the begining i thought i have probelm with the name but i changed the name.
    then i thought it might have something to do with database permissions.i did checke that but for some reason the error would nt go away.
    here is the code

    Private Sub btnSubmit_Click()

    Dim Conn As New ADODB.Connection
    Dim RecSet As New ADODB.Recordset
    Dim SQLQuery

    Fname = txtFname.Text 'getting var from text box
    Lname = txtLname.Text

    'The database is on romote server

    SQLQuery = "Insert into introduction (fname,lname) values ('" & Fname & "','" & Lname & "')"
    Conn.Open "Provider=SQLOLEDB.1;UID=someID;PWD=myPWD;Inti tial Catalog=test;Data Source=MYserver"
    RecSet.Open SQLQuery, Conn

    Conn.Close
    Set RecSet = Nothing

    End Sub

    This code runs under a command button click.i m getting the values from the text boxes and try to insert them in the database.but i m getting this weird error.
    Please help!!!!

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Who's the schema owner of your INTRODUCTION table? I suspect it's someone other than DBO.

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    ...pressed on the button too early

    Preceed the table name with the schema owner in your INSERT:

    insert into user_name.introduction ...

  4. #4
    Join Date
    Aug 2003
    Location
    NY
    Posts
    15
    thanks for prompt reply.but the problem still persists.
    i checked on ownership of the table .it is DBO.
    any ideas!!

  5. #5
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    The problem is, that your SQL statements isn't a query, but a DML statement. How do you expect to get a recordset by issueing an INSERT statement?!

    Change your line
    RecSet.Open SQLQuery, Conn

    into
    Conn.Execute SQLQuery

    and it will work fine.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  6. #6
    Join Date
    Aug 2003
    Location
    NY
    Posts
    15
    Thanks for ur reply.
    i did try that ,change the line to "Conn.Execute SQLQuery",and still its giving me the same error

    any other suggestions...

  7. #7
    Join Date
    Aug 2003
    Location
    NY
    Posts
    15
    hi

    Actually i solved the problem.it was not working with DSN less connection as i was trying to do.
    so i created DSN,test the connection,and then used it in my code,and it worked fine.

    although i still dont know why it was not working with DSN less connection. i used the same parameters as i was doing it in DSN less,and it worked.
    but hey as long it works i happy.

    thanks guys

Posting Permissions

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