INSERT confusion.... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

INSERT confusion....

Author

Topic

Dorffius
Starting Member

36 Posts

Posted - 2002-03-05 : 13:26:50

I have this code but I can't figure out what order to put it all in. Basically it is a looping statement that grabs a field from a form and inserts it into a DB. I can't seem to get it to work. Any ideas?

<%
set Recordset2 = Server.CreateObject("ADODB.Recordset")
Recordset2.ActiveConnection = MM_eartagtable_STRING
for i=0 to recordCount
rb = Request.Form("rb" & i)
select case rb
case 1
RecordSource = "INSERT INTO ETATEMP (CCS, BIO, QUE, CCIA) VALUES (i,1,0,0)"
case 2
RecordSource = "INSERT INTO ETATEMP (CCS, BIO, QUE, CCIA) VALUES (i,0,1,0)"
case 3
RecordSource = "INSERT INTO ETATEMP (CCS, BIO, QUE, CCIA) VALUES (i,0,0,1)"
end select
Recordset2.Source = RecordSource
Next
Recordset2.CursorType = 0
Recordset2.CursorLocation = 2
Recordset2.LockType = 3
Recordset2.Open()
Recordset2_numRows = 0
%>

Thanks in advance.

Ryan

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2002-03-05 : 13:54:07

To begin with your loop/RecordSet will only execute the last RecordSource variable computed.
The syntax for the open command is
recordset.Open Source, ActiveConnection, CursorType, LockType,
Options


Set Recordset2 = Server.CreateObject("ADODB.Recordset")
Recordset2.ActiveConnection = MM_eartagtable_STRING
Recordset2.CursorType = 0
Recordset2.CursorLocation = 2
Recordset2.LockType = 3

RecordSource = ""
for i=0 to recordCount
     rb = Request.Form("rb" & i)
     select case rb
          case 1
               RecordSource = RecordSource & "INSERT INTO ETATEMP (CCS, BIO, QUE, CCIA) VALUES (i,1,0,0);"
          case 2
               RecordSource = RecordSource & "INSERT INTO ETATEMP (CCS, BIO, QUE, CCIA) VALUES (i,0,1,0);"
          case 3
               RecordSource = RecordSource & "INSERT INTO ETATEMP (CCS, BIO, QUE, CCIA) VALUES (i,0,0,1);"
     end select 
next
Recordset2.Source = RecordSource
Recordset2.Open()

Another way to do it


Set Recordset2 = Server.CreateObject("ADODB.Recordset")
RecordSource = ""
for i=0 to recordCount
     rb = Request.Form("rb" & i)
     select case rb
          case 1
               RecordSource = RecordSource & "INSERT INTO ETATEMP (CCS, BIO, QUE, CCIA) VALUES (i,1,0,0);"
          case 2
               RecordSource = RecordSource & "INSERT INTO ETATEMP (CCS, BIO, QUE, CCIA) VALUES (i,0,1,0);"
          case 3
               RecordSource = RecordSource & "INSERT INTO ETATEMP (CCS, BIO, QUE, CCIA) VALUES (i,0,0,1);"
     end select 
next
Recordset2.Open RecordSource, MM_eartagtable_STRING, adOpenKeyset, adLockPessimistic, adCmdText

if your going to use the ado variables make sure you get a copy of adovbs.inc

Dorffius
Starting Member

36 Posts

Posted - 2002-03-05 : 14:04:16

Thanks for the code. I tried both but unfortunately neither worked. The first one gave the error message:

Command text was not set for the command object.

The line number points to the recordset .open line.

The second one gave this error message:

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

Line number points to the final line in your code.

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2002-03-05 : 14:20:19

For the first error output the result of RecordSource and see what the value is.

For the second error, are you including the ADO variables? If not make sure you grab a copy of it. Take a look at http://www.4guysfromrolla.com/aspfaqs/ShowFAQ.asp?FAQID=123

Dorffius
Starting Member

36 Posts

Posted - 2002-03-05 : 14:37:00

#1 - The variable is completely empty.

#2 - There is no such file on the IIS server.

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2002-03-05 : 14:40:28

what are you trying to do exactly.

#1. The only reason I can think of that RecordSource is still blank is becuase the value you are using for recordCount is either blank or 0. What are you doing to determine this value.

#2. adovbs.inc doesnt come with IIS. Take a look at the previous link I gave you and upload it to your webserver like you would any other file. Then just simply include it. Look at the link, it explains it all. but simply stated . Download the file, upload it to your server, and in you asp code use

Dorffius
Starting Member

36 Posts

Posted - 2002-03-05 : 14:48:08

Ok, here is what I am doing.

The page that this code is running on is receiving the information from a form on the previous page. This previous page has a table generated from a recordset. The user can choose with radio buttons one of three catagories to put each record into. This current page is supposed to insert the records into the proper catagories and then display three tables, one of each catagory.

yakoo
Constraint Violating Yak Guru

312 Posts

Posted - 2002-03-05 : 14:58:37

ok... you still need to populate the recordCount variable so the loop knows how many times to loop. If you don't then the RecordSource variable will never be populated.

Since you already have the number of records displayed on the previous page you can send this variable along with the others to your processing page. Include it as a hidden varible in the form and pass it along. The for loop will then become

for i = 0 to Request("recordCount")

if you dont want to pass the variable on then create another recordset and grab the count of records. run the loop and then execute all of you insert statements. Run another recordset to then display all three categories.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources