insert data into SQL server database 1

[peterve]

Hi,
I started developping an application that uses SQL Server
(VB6)
I can connect, retrieve data using SQL queries, ...
I use ADO (msado15.dll) ActiveX to connect (DSN-less) so I don't have to create a ODBC connection...

How can I add data into the tables of my database ?
I've tried it with

objconn.execute "insert into mytable (field1, field2) values (1,'text')"

but it doesn't work ?

Is this way of adding data correct ? Or is there an easier way ? ---------------------------------------------------------------------
I have not failed, I've just found 10,000 ways that don't work
---------------------------------------------------------------------
Peter Van Eeckhoutte
peter.ve@pandora.be
*:->* Did this post help? Click below to let me know !

 

[stoolpigeon]

What happens when you run the query above? The SQL is valid so it has to be something else.

I'd like to see how you set up the whole connection if you don't mind. You are using a method that I am unfamiliar with- and curious about.

Thanks.

 

[scorpio66]

Also, what error are you getting?

Chaz

 

[peterve]

This is how I create the connection

First, I add msdao15.dll as a reference, then :

Dim objconn As New ADODB.Connection
Dim objrs As New ADODB.Recordset
objconn.Open "Driver=SQL Server;Server=SQLServer1;Database=Name_of_Database"

If objconn.State = adStateOpen Then
    objrs.Open "select * from tbl_table1", objconn, adOpenForwardOnly, adLockReadOnly, adCmdText
end if

-> I use objconn.execute "sqlquery"
to execute the SQL Query

But I found out what the problem is : I accidentally mixed up a field name with a table name... so inserting into a field instead of a table produced the error!

I have another question :

Is there another way of connecting to a SQL server, and can you show me some basic code to retrieve data, to add, update and delete records
(or is it just plain SQL ?)

Thank you for your time

---------------------------------------------------------------------
I have not failed, I've just found 10,000 ways that don't work
---------------------------------------------------------------------
Peter Van Eeckhoutte
peter.ve@pandora.be
*:->* Did this post help? Click below to let me know !

 

[stoolpigeon]

99% of the work I do is connecting GUIs to SQL Server 6.5-- usually this is how I do it (and any time anyone can suggest something better, I am all for hearing about it)

I start the same as above- declaring an ADO connection and recordset(s).

Then you open the connection, and open the recordset.

If you want to modify a record you open the recordset with a query that pulls the record you want. Then you just change the fields you want and use the update method. Like this:

sql="Select * from AccountTable where accountnumber = " & account

recordset.open sql,cnnMain,etc.,etc.(all that good stuff)

recordset!name = newnamevariable
recordset!date = today
recordset.update

To add a new record I still use a query to open the table but then you just fill in the fields you want w/a addnew and update on each end.

recordset.addnew
recordset!name = newnamevariable
recordset!date = today
recordset.update

One other way that is handy when you want to loop through a recordset and hit all the fields would be using the index for the field instead of the name. Like this:

recordset.fields(indexnumber) (index starts at 0)

Basically the connection connects- you use it to open the recordset and then you do the work through the properties and methods of the recordset.

 

[peterve]

ok, I know this stuff when I was programming on Access databases...

is this fast enough when I have to add or update for example 10000 records ? ---------------------------------------------------------------------
I have not failed, I've just found 10,000 ways that don't work
---------------------------------------------------------------------
Peter Van Eeckhoutte
peter.ve@pandora.be
*:->* Did this post help? Click below to let me know !