combine Insert statements

[DirtDawg]

Hello all,

I have some information that I would like to combine into one submit I appreciat the help
<%

strwkn = Request.form(&quot;wkn&quot
strsrv = request.form (&quot;srv&quot
strvslvoy = Request.form(&quot;vslvoy&quot
stretd = Request.form(&quot;etd&quot

SET db = Server.CreateObject(&quot;ADODB.Connection&quot
db.Open &quot;DSN=dsn&quot;, &quot;id&quot;,&quot;pass&quot;
SQL = &quot;Insert into vsl (wkn, vslvoy, srv, etd,Values ('&quot; & strwkn & &quot;', '&quot; & strvslvoy & &quot;', '&quot; & strsrv & &quot;', '&quot; & stretd & &quot;')&quot;
db.execute(SQL)

next statement that I would like to add in is

CheckboxStr = &quot;ckbox&quot;

Inputs = Split(&quot;jpn,kor,twn.khh,hkg&quot;,&quot;,&quot
InputReference = Split(&quot;ga&quot;,&quot;,&quot

for each element in request.form
If request(element) = &quot;ON&quot; Then

DBStr1=&quot;&quot;
DBStr2=&quot;&quot;

For InputVar = 0 To Ubound(Inputs)
DBStr1 = DBStr1 & InputReference(InputVar)
DBStr2 = DBStr2 & &quot;'&quot; & Replace(Request(Inputs(InputVar) & Replace(element,CheckboxStr,&quot;&quot),&quot;'&quot;,&quot;''&quot & &quot;'&quot;

If InputVar < Ubound(Inputs) Then DBStr1 = DBStr1 & &quot;, &quot;
If InputVar < Ubound(Inputs) Then DBStr2 = DBStr2 & &quot;, &quot;
Next

SQLStr = &quot;Insert INTO apup(&quot; & DBStr1 & &quot VALUES(&quot; & DBStr2 & &quot&quot;
conn.Execute(SQLStr)


End if

Next

 

[Chopstik]

If I understand correctly, you wish to commit both INSERT statements with only a single .execute statement. The easy solution to this would be to combine your SQL statements into a single statement and then run it. For example, you create the first one and then later append the second one to the first. Example:

SQL = "Insert into vsl (wkn, vslvoy, srv, etd,Values ('" & strwkn & "', '" & strvslvoy & "', '" & strsrv & "', '" & stretd & "'); "
SQL = SQL & "Insert INTO apup(" & DBStr1 & ") VALUES(" & DBStr2 & ");"

Now, your complete SQL statement should read as follows:

&quot;Insert into vsl (wkn, vslvoy, srv, etd,Values ('&quot; & strwkn & &quot;', '&quot; & strvslvoy & &quot;', '&quot; & strsrv & &quot;', '&quot; & stretd & &quot;'); Insert INTO apup(&quot; & DBStr1 & &quot VALUES(&quot; & DBStr2 & &quot;&quot;

Notice the &quot;;&quot; at the end of each statement which separates each of the INSERT statements. One final thought to bear in mind is that if these are related inserts, then it would be best to combine them into a single transaction so that if one fails, neither is committed. For additional help on transactions in SQL, you can look up info in BOL or, if that is not helpful, then forum183 may also be useful. Hope this helps.

-----------------------------------------------------------------------------------------------------
&quot;If you can't explain something to a six-year-old, you really don't understand it yourself.&quot;
-- Albert Einstein

 

[DirtDawg]

what I have is 2 combo boxes and two text boxes that the user inputs some information this is the first Insert Statement SQL = &quot;Insert into vsl (wkn, vslvoy, srv, etd) Values ('&quot; & strwkn & &quot;', '&quot; & strvslvoy & &quot;', '&quot; & strsrv & &quot;', '&quot; & stretd & &quot;') This works fine.

What I would like to add to this is a table with Three columns 1.checkbox 2.city 3. amount When the user ticks the checkbox it sends information of 3.amount to the database. Insert INTO vsl(&quot; & DBStr1 & &quot VALUES(&quot; & DBStr2 & &quot Not working.

The fully comnined is below can you please help me out.



<%

strwkn = Request.form(&quot;wkn&quot
strsrv = request.form (&quot;srv&quot
strvslvoy = Request.form(&quot;vslvoy&quot
stretd = Request.form(&quot;etd&quot

CheckboxStr = &quot;ckbox&quot;

Inputs = Split(&quot;jpn,kor,twn,khh,hkg,sha,tao,shk,ytn,ngb,dlc,txg,xmn,ncn,&quot;,&quot;,&quot
InputReference = (&quot;jpn,kor,twn,khh,hkg,sha,tao,shk,ytn,ngb,dlc,txg,xmn,ncn,&quot;,&quot;,&quot

for each element in request.form
If request(element) = &quot;ON&quot; Then

DBStr1=&quot;&quot;
DBStr2=&quot;&quot;

For InputVar = 0 To Ubound(Inputs)
DBStr1 = DBStr1 & InputReference(InputVar)
DBStr2 = DBStr2 & &quot;'&quot; & Replace(Request(Inputs(InputVar) & Replace(element,CheckboxStr,&quot;&quot),&quot;'&quot;,&quot;''&quot & &quot;'&quot;

If InputVar < Ubound(Inputs) Then DBStr1 = DBStr1 & &quot;, &quot;
If InputVar < Ubound(Inputs) Then DBStr2 = DBStr2 & &quot;, &quot;
Next

SET db = Server.CreateObject(&quot;ADODB.Connection&quot
db.Open &quot;DSN=utilmrkt&quot;, &quot;mrkt&quot;,&quot;ut1l1mrkt&quot;
SQL = &quot;Insert into vsl (wkn, vslvoy, srv, etd) Values ('&quot; & strwkn & &quot;', '&quot; & strvslvoy & &quot;', '&quot; & strsrv & &quot;', '&quot; & stretd & &quot;'); Insert INTO vsl(&quot; & DBStr1 & &quot VALUES(&quot; & DBStr2 & &quot;&quot;
db.execute(SQL)


End if

Next


%>

 

[Chopstik]

I had attempted to post a response but there were some technical difficulties. Anyway, the gist of my answer was that I think you should try to response.write your SQL statement to the screen and then copy and paste that into Query Analyzer (if you are using SQL Server) or whatever is the equivalent for the database that you are using. You do not specify whether you are receiving an error message or it simply isn't working, so this might give you somewhere to start as far as seeing where to go next.

One other thing, unless your DBStr1 is the name of your db field in the second insert statement, then that may also be the cause of your problem. I'd examine those two possibilities and, if you still encounter problems, post back with the specific error message(s) and see if you/we can resolve it.

-----------------------------------------------------------------------------------------------------
&quot;If you can't explain something to a six-year-old, you really don't understand it yourself.&quot;
-- Albert Einstein

 

[DirtDawg]

Chopstik,

I ahve played with the coding a little bit to get the statement to work. But it si inserting new rows for each of the checkboxes. See Code below

CheckboxStr = &quot;ckbox&quot;

strwkn = Request.form(&quot;wkn&quot
strsrv = request.form (&quot;srv&quot
strvslvoy = Request.form(&quot;vslvoy&quot
stretd = Request.form(&quot;etd&quot

Inputs = split(&quot;ga&quot;,&quot;,&quot
InputReference = split(&quot;jpn,kor,twn,khh,hkg,sha,tao,shk,ytn,ngb,dlc,txg,xmn,ncn&quot;,&quot;,&quot

for each element in request.form
If request(element) = &quot;ON&quot; Then


DBStr1=&quot;&quot;
DBStr2=&quot;&quot;

For InputVar = 0 To Ubound(Inputs)
DBStr1 = DBStr1 & InputReference(InputVar)
DBStr2 = DBStr2 & &quot;'&quot; & Replace(Request(Inputs(InputVar) & Replace(element,CheckboxStr,&quot;&quot),&quot;'&quot;,&quot;''&quot & &quot;'&quot;

If InputVar < Ubound(Inputs) Then DBStr1 = DBStr1 & &quot;, &quot;
If InputVar < Ubound(Inputs) Then DBStr2 = DBStr2 & &quot;, &quot;
Next

SQLStr = &quot;Insert INTO vsl(wkn, vslvoy, srv, etd,&quot; & DBStr1 & &quot VALUES('&quot; & strwkn & &quot;', '&quot; & strvslvoy & &quot;', '&quot; & strsrv & &quot;', '&quot; & stretd & &quot;',&quot; & DBStr2 & &quot&quot;
conn.Execute(SQLStr)


End if

Next

What I want is if the check box is ticked that it links to the column in the table and stores the information. So it would all be one record not 14 different records. Can you help me out on this one.

 

[Chopstik]

I am sorry, but I am having a hard time trying to read the code you are utilizing to build your SQL statement. I think it has to do with the fact that I've not normally used the same methodology that you are using and thus it is just my own unfamiliarity. Unfortunately, I do not have a great deal of time to work on this at the moment, but I will try to review again tomorrow, time permitting. Perhaps someone else can offer some advice?

-----------------------------------------------------------------------------------------------------
&quot;If you can't explain something to a six-year-old, you really don't understand it yourself.&quot;
-- Albert Einstein

 

[DirtDawg]

Chopstik,

it is ok I have figured it out myself. After playing with the table on the ASP and then playing with the coding for the SQl I have acquired what I have wanted for the time being I appreciate your help though.