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!!!!

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)

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!!!

What about

CVAR(lastname)

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.

its not workin man.
i dont know whats the freakin problem!!

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

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.

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

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