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

Dilbert1234567 · 01-01-2005, 11:48 PM

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

01-02-2005, 08:30 AM

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.

Dilbert1234567 · 01-02-2005, 10:44 AM

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

Dilbert1234567 · 01-02-2005, 10:52 AM

ok i got it, thanks for the help.

asshopo · 01-03-2005, 06:04 AM

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

01-03-2005, 10:22 AM

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?

asshopo · 01-03-2005, 11:34 AM

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]"

Dilbert1234567 · 01-03-2005, 07:17 PM

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

01-04-2005, 08:06 AM

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

Dilbert1234567 · 01-08-2005, 09:42 AM

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)

01-08-2005, 11:45 AM

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?

Dilbert1234567 · 01-08-2005, 04:14 PM

yes that should be in thanks a bundle

01-01-2005, 11:48 PM	#1 (permalink)
Dilbert1234567 Devils Cabana Boy Location: Central Coast CA	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 __________________ Donate Blood! "Love is not finding the perfect person, but learning to see an imperfect person perfectly." -Sam Keen

01-02-2005, 08:30 AM	#2 (permalink)
zen_tom Guest	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. Last edited by zen_tom; 01-03-2005 at 10:21 AM..

01-02-2005, 10:44 AM	#3 (permalink)
Dilbert1234567 Devils Cabana Boy Location: Central Coast CA	should Set mvarConnection = CreateObject("ADODB.Connection") be Set myConnection = CreateObject("ADODB.Connection") __________________ Donate Blood! "Love is not finding the perfect person, but learning to see an imperfect person perfectly." -Sam Keen

01-02-2005, 10:52 AM	#4 (permalink)
Dilbert1234567 Devils Cabana Boy Location: Central Coast CA	ok i got it, thanks for the help. __________________ Donate Blood! "Love is not finding the perfect person, but learning to see an imperfect person perfectly." -Sam Keen

01-03-2005, 07:17 PM	#8 (permalink)
Dilbert1234567 Devils Cabana Boy Location: Central Coast CA	the database is not local, it is on a remote sever. __________________ Donate Blood! "Love is not finding the perfect person, but learning to see an imperfect person perfectly." -Sam Keen

01-03-2005, 06:04 AM	#5 (permalink)
asshopo Insane Location: Michigan	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>"

01-03-2005, 11:34 AM	#7 (permalink)
asshopo Insane Location: Michigan	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]"

01-04-2005, 08:06 AM	#9 (permalink)
zen_tom Guest	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

01-08-2005, 09:42 AM	#10 (permalink)
Dilbert1234567 Devils Cabana Boy Location: Central Coast CA	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) __________________ Donate Blood! "Love is not finding the perfect person, but learning to see an imperfect person perfectly." -Sam Keen

01-08-2005, 11:45 AM	#11 (permalink)
zen_tom Guest	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?

01-08-2005, 04:14 PM	#12 (permalink)
Dilbert1234567 Devils Cabana Boy Location: Central Coast CA	yes that should be in thanks a bundle __________________ Donate Blood! "Love is not finding the perfect person, but learning to see an imperfect person perfectly." -Sam Keen