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

    Unanswered: runtime error 80040e37

    hi:

    i m new to VB6.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
    Amsterdam, Nederland
    Posts
    450

    Your SQL isnt the right format

    Try it like this
    I think it will go better

    INSERT INTO Employees (FirstName,LastName, Title) VALUES ('Harry', 'Washington', 'Trainee');



    So in your case :

    Insert into introduction (fname,lname) values (Fname ,Lname)

  3. #3
    Join Date
    Aug 2003
    Location
    NY
    Posts
    15

    Unhappy

    Thanks for ur reply.

    if i write it the way ur saying.it will be considered as string not the variable i m taking from the text boxes.

    any other suggestions!!!

  4. #4
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Exclamation use the functions provided

    What about

    CVAR(lastname)

  5. #5
    Join Date
    Apr 2003
    Location
    Bedfordshire
    Posts
    11
    Originally posted by aamirbabar
    Thanks for ur reply.

    if i write it the way ur saying.it will be considered as string not the variable i m taking from the text boxes.

    any other suggestions!!!
    Hi mate.

    You are right your insert statement is fine. Your problem is being caused by the: 'RecSet.Open SQLQuery, Conn ' statement. You should only use this statement when you are expecting a recordset to be returned (Like a 'Select' Statement, or storedproc).
    As you are trying to call an insert statement, use: 'Conn.Execute (SQLQuery)'. You uses this statement if you are just executing an SQL command (like your Insert or an Update statement).

    Hope this helps

    Eamon.
    Peace and hair grease.

  6. #6
    Join Date
    Aug 2003
    Location
    NY
    Posts
    15
    its not workin man.
    i dont know whats the freakin problem!!

  7. #7
    Join Date
    Aug 2003
    Location
    NY
    Posts
    15
    Thanks for ur Reply

    As a matter of fact, i have already changed that line into Conn.Execute SQLQuery.
    but still its not workin.do nt u think i m missing reference to some ADO library thats why its not reading/recognising the table.
    any help would be appreciated

  8. #8
    Join Date
    Apr 2003
    Location
    Bedfordshire
    Posts
    11
    Originally posted by aamirbabar
    its not workin man.
    i dont know whats the freakin problem!!
    Still not working? Is it failing with the same error?
    Your code should look like this...



    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.Execute (SQLQuery)

    Conn.Close
    'Set RecSet = Nothing

    End Sub


    If this does not work then you should check that the connection to the database has worked.

    Regards.
    Peace and hair grease.

  9. #9
    Join Date
    Apr 2003
    Location
    Bedfordshire
    Posts
    11
    Originally posted by aamirbabar
    Thanks for ur Reply

    As a matter of fact, i have already changed that line into Conn.Execute SQLQuery.
    but still its not workin.do nt u think i m missing reference to some ADO library thats why its not reading/recognising the table.
    any help would be appreciated
    Ok, Try these:

    1) Ensure that the table is called 'introduction' and does not have any silly spelling mistakes (don't mean any offence as I've made that sort of mistake myself all the time).

    2) Ensure that you have things the right way round.
    Initial Catalog= <The Database you are trying to access>
    Data Source= <The SQL server that the holds the database>

    3) Try stateing the full database qualifier:
    SQLQuery = "Insert into test.dbo.introduction (fname,lname) values ('" & Fname & "','" & Lname & "')" 'If this works then you have an SQL security issue.

    4) Try using Enterprize Manager with the same UserID and Password to update this table. If it fails it will give you a better error message.

    5) Try testing if the connection is close before closing it.
    If Conn.State <> 0 Then
    Conn.Close
    End If
    Set Conn= Nothing


    Don't think this will help much but I'll have a think...
    Peace and hair grease.

  10. #10
    Join Date
    Aug 2003
    Location
    NY
    Posts
    15
    Thanks man for ur help.

    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 that 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.

    i really appreciate ur help .net.Dude. and all others

    Thanks

Posting Permissions

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