CODEWITHSUNDEEP
asp-classicadodb
EglCode
EglCode

Reputation: 149

Creating dynamic ADODB connections in Classic ASP

I have a list of Database connection strings, Database Name. These databases have the same table structure. What I am trying to do is dynamically create a connection to each one, add/delete/modify a table, however, if an error pops up anywhere, then RollbackTrans, else, CommitTrans.

My basic question to get my on the correct path is this:

Is this code possible in Classic ASP to make Dynamically named connections?

'create the dynamic object
execute("Set Con" & index & " = Server.CreateObject(""ADODB.connection"")")
'connect to the dynamic object
execute("Con" & index & ".Open " & DBString(index))

The error I get is 'Expected end of statement' on the .open line (the last one)

Upvotes: 0

Views: 2251

Answers (2)

alpc
alpc

Reputation: 610

Mysql Dynamic Connection String , sample for t=1 to 4 , four different database connection conns(t)


dim conns(4)

Set Conns(1)=Server.Createobject("ADODB.Connection")

Conns(1).Open "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=localhost;port=3306;DATABASE=dbname;UID=root;PASSWORD=pass;OPTION=3"
Conns(1).Execute "SET NAMES 'latin5'"
Conns(1).Execute "SET CHARACTER SET latin5"
Conns(1).Execute "SET COLLATION_CONNECTION = 'latin5_turkish_ci'"

Upvotes: 0

schudel
schudel

Reputation: 1225

This might do the trick: Just use an array of connection strings. From this you create an array of connections. Then you can iterate over this array and send your commands to the separate databases.

  dim connectionStrings(1)
  dim connections(1)
  dim curConn

  connectionStrings(0) = "Provider=sqloledb;Server=.\EXPRESS2012;Database=master;uid=youruser;pwd=yourpwd"
  connectionStrings(1) = "Provider=sqloledb;Server=.\EXPRESS2012;Database=model;uid=youruser;pwd=yourpwd"

  for curConn = 0 to ubound( connectionStrings)
    set connections(curConn) = Server.CreateObject("ADODB.Connection")
    connections(curConn).Open connectionStrings(curConn)
  next

  dim cmd : cmd = "select @@servername, db_name()"
  for curConn = 0 to ubound( connectionStrings)
    dim rs
    set rs = connections(curConn).Execute( cmd)
    Response.write( rs( 0) & ":" & rs(1) & "<br />")
    rs.close
    set rs = nothing
  next


  for curConn = 0 to ubound( connectionStrings)
    call connections(curConn).Close
    set connections(curConn) = nothing
  next

Upvotes: 1

Related Questions