Conditional INSERTing / UPDATEing an Access db

Results 1 to 2 of 2

Thread: Conditional INSERTing / UPDATEing an Access db

  1. #1
    Pokie Guest

    Default Conditional INSERTing / UPDATEing an Access db

    Hi - I am familiar with how to INSERT a new record and how to UPDATE an existing one, but I&#039ve never tried something like this and I am unsure how to start (pseudo-code follows):<BR><BR>get recordkey from web form &#039no problems<BR>read corresponding record from database1 &#039no problems<BR>if (corresonding record exists in database2)<BR> read it - do stuff - update it<BR>else <BR> create fields - insert new record<BR>endif<BR><BR>Up until now I&#039ve only had the need to work with one database at a time, so I guess I really have two questions:<BR>1) How can I tell when a record exists in a table (based upon a key) and then INSERT/UPDATE appropriately<BR>2) How do I set up a connection for a 2nd db in the same script?<BR><BR>Thanks in advance!<BR><BR>(and thanks 4guysfromrolla for a great site!)<BR>

  2. #2
    Jimmy Wang Guest

    Default RE: Conditional INSERTing / UPDATEing an Access db

    If you need to look at each record by record (because yourKey in second Db table is Indexed primary key, i.e. no duplicates) try the following approach:<BR>Note: the code assumes yourKey, field1, field2 are text-type data type.<BR>I&#039am sure someone else has better approach! <BR>&#060;%<BR>dim db1Conn,db2Conn<BR>dim ConnStr1,ConnStr2<BR>ConnStr1=yourFirsDbConnection String<BR>ConnStr2=yourSecondDbConnectionString<BR ><BR>set db1Conn=server.creatreobject("adodb.connection")<B R>db1Conn.Open ConnStr1<BR>set db1Conn=server.creatreobject("adodb.connection")<B R>db1Conn.Open ConnStr1<BR><BR>sqltemp1="select yourKey,[Db1Field2],[Db1Field3] From yourDb1Table;"<BR>rsTemp1=Db1Conn.Execute(sqltemp1 )<BR>set rsTemp=nothing<BR><BR>myDb1Data=rsTemp1.getRows<BR >howManyRows=Ubound(myDb1Data,2)<BR>howManyCols=Ub ound(myDb1Data,1)<BR><BR>For thisRow=0 to howManyRows<BR>for thisCol=0 to howManyCols<BR>thisValue=myDb1Data(thisCol,thisRow )<BR>select Case thisCol<BR>case 0<BR>sqlTemp2="select yourKey From yourDb2Table Where yourKey=&#039"&thisValue&"&#039;"<BR>set rsTemp2=db2Conn.Execute(sqlTemp2)<BR>If Not(rsTemp2.BOF and rsTemp2.EOF) Then <BR>&#039&#039sql to update your db2 table<BR>sqlTemp3="Update yourDb2Table Set Db2field=&#039"&myDb1Data(1,thisRow)&"&#039,DbFiel d3=&#039"&myDb1Data(2,thisRow)&"&#039 Where YourKey=&#039"&thisValue&"&#039;"<BR>Else<BR>&#039 &#039sql to insert into db2 table<BR>sqlTemp3="Insert into YourDb2Table (yourKey,Db2Field2,dbField3) " _<BR>& " Value (&#039"&thisValue&"&#039,&#039"&&myDb1Data(1,thisR ow)&"&#039,&#039"&&myDb1Data(2,thisRow)&"&#039);"< BR>End If<BR>End Select<BR>set rsTemp3=Db2Conn.Execute(sqltemp3)<BR>set rsTemp3=nothing<BR>Next<BR>next<BR>set db1Conn=nothing<BR>set db2Conn=nothing<BR>%&#062;<BR><BR><BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts