Tilted Forum Project Discussion Community - inserting into a MSSQL data base with VB6.0

- Tilted Technology (https://thetfp.com/tfp/tilted-technology/)

- - inserting into a MSSQL data base with VB6.0 (https://thetfp.com/tfp/tilted-technology/79949-inserting-into-mssql-data-base-vb6-0-a.html)

inserting into a MSSQL data base with VB6.0

I need to insert into 2 tables with visual basic, I have very little knowledge with data connections, but my SQL is pretty good.

How do I make a connection to my MSSQL (its remote) and how do I pass a SQL command to it.

~dil

OK, there are a number of different methods, but the most used one is via ODBC.

Here is some sample code from a program I use to connect to a database - you will need to reference the Microsoft AcitveX Data Objects Library (I'm using version 2.7) to provide access to the appropriate classes though:

Code:

Set myConnection = CreateObject("ADODB.Connection")



If DSN <> "" Then DSNbit = "Data Source=" & DSN

If DB <> "" Then DBbit = "database=" & DB

If user <> "" Then USERbit = "User ID=" & user

If pwd <> "" Then PWDbit = "Password=" & pwd



myConnection.ConnectionString = DSNbit & ";" & USERbit & ";" & PWDbit & ";" & DBbit & ";"



myConnection.Open



'Now the connection has been established, you can execute SQL commands using the following syntax.



sqlstring = "insert into mytable (name, number) values ('Chris', 4)"

myConnection.Execute sqlString



'To pull data from the database, use a recordset object



sqlString = "Select foo, bar from mytable"

set rs = new RecordSet

rs.open sqlString, myConnection

That's the code stuff, the actuall connection is made by setting up an ODBC driver (sometimes known as a DSN) on your client machine that points to the appropriate SQL Server. Setting up the connection is done via the Start...Settings...Control Panel...Administrative Tools...Data Sources (ODBC) - then decide whether you want a User DSN or a System One and carry on from there.

Once that's setup - your code should be pretty straight-forward.

should
Set mvarConnection = CreateObject("ADODB.Connection")
be
Set myConnection = CreateObject("ADODB.Connection")

ok i got it, thanks for the help.

You can bypass the DSN crap (they make everything slower) by with another connection string.

adodb.connectionstring = "server=<servername>;uid=<uname>;pwd=<pass>;database=<dbname>"

Quote:

should
Set mvarConnection = CreateObject("ADODB.Connection")
be
Set myConnection = CreateObject("ADODB.Connection")

Yes it should - Sorry, I lifted the code from a class I use to do all of this and didn't edit it properly. - Asshopo - not having to set up a DSN would be most handy - I'm going to try that out, thanks!

Do you know if it works for any other databases, or only for MSSQL? Or I suppose, it should work as long as you've got an ODBC driver right?

As long as there is a driver for it on your system, you can use it without a DSN. An example for MySQL would be:

connectionstring = "ODBC;DRIVER={MySQL ODBC 3.51 Driver};SERVER=127.0.0.1;DATABASE=redpath-lims;UID=[uname];PWD=[pword]"

the database is not local, it is on a remote sever.

it should still work as long as you've got the client (ODBC driver) installed locally and you enter the server's IP in the parameter SERVER=xxx.xxx.xxx.xxx

i switched to a DNS less connection string

myconnection.ConnectionString = "Provider=SQLOLEDB.1;Network Library=DBMSSOCN;Data Source=abaddon,1433;User ID=user;password=pass;Initial Catalog=Spyware;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096"

it works for my system (2k) but not on my roomates system (xp)

It might be that your roomate doesn't have the same libraries as you do - I remember there used to be a thing called MDAC (Miscrosoft Data Access Components) that you could download from Microsoft that often solved these kinds of problems - I've no idea if it's still current now, or if it would work, but maybe worth a try?

yes that should be in thanks a bundle