add more record into table

[petertran123]

I'm writting this code to insert data into table, but somehow i can't get record to add up..

in my scenerio i wanted to be able to add more than one record into table. For an example : when i enter

ID EFIN # BegCkNo1 EndCkNo1
1 123456 1000 2000
2 123456 5000 7000
3 222222 15000 17000
4 222222 800 1000

Is there anyway i can use more than one EFIN to the table, because my code is only work for one and it did not take a duplicate EFIN

strSQL = "Select * from ERO_CHECK_NUM where OFFICE_EFIN ='" & strEFIN & "'"
Set conn = GetDBConnection(DATABASE_CONNECT_STRING)
Set rsData = conn.Execute( strSQL )



If rsData.eof then
strSQL = "insert into ERO_CHECK_NUM (OFFICE_EFIN, BEGCKNO1, BEGCKNO2, BEGCKNO3, BEGCKNO4, ENDCKNO1, ENDCKNO2, ENDCKNO3, ENDCKNO4) values ('" & _
strEFIN & "', '" & strBegCkNo1 & "', '" & strBegCkNo2 & "', '" & strBegCkNo3 & "', '" & strBegCkNo4 & "', '" & _
strEndCkNo1 & "', '" & strEndCkNo2 & "', '" & strEndCkNo3 & "', '" & strEndCkNo4 & "')"
conn.Execute(strSQL)
end if

' Done. Close the connection
conn.Close
Set conn = Nothing

Thanks

[Spookster]

Well assuming you are using either msSQL or MS Access as your database then you should check the EFIN # column in your database an make sure you don't have that column set to not allow duplicates.

[petertran123]

this is an insert code, how do i updated after i insert data into table .. Right now it seems to work only first time when i insert. I still have a hard time to insert the second data into table. Please help me thanks

[Roy Sinclair]

Originally posted by petertran123
this is an insert code, how do i updated after i insert data into table .. Right now it seems to work only first time when i insert. I still have a hard time to insert the second data into table. Please help me thanks

If you're trying to update data after you've entered it once then you use the SQL UPDATE verb instead of INSERT. INSERT is used to add new records, UPDATE is used to change records once they are already present.

If you want to remove all the existing records and then add them back again use TRUNCATE TABLE ERO_CHECK_NUM before your first new INSERT.

[Spookster]

Ok first you asked how to allow multiple records with the same EFIN # and now you asked how to only update the existing ones? What are you trying to do?

Im lost.

[eggman]

Are my eyes finally giving out? The following line doesn't seem right to me:

strSQL = "Select * from ERO_CHECK_NUM where OFFICE_EFIN ='" & strEFIN & "'"

Aren't the ampersands used in this case a VB convention? Shouldn't they be plus (+) signs? Otherwise, you are bitwise ANDing the string and will most likely arrive at an incorrect SQL string.


OOPS!!! Ignore this. The END IF gave the VBScript away. Never mind... :o

[whammy]

Heh, ignoring eggman's post (which is forgivable), I agree with spookster.

I'm a bit confused as well.

Do you want to update records, or insert multiple records?

[petertran123]

First i want to sorry for making such a confusion for all of you. I'm using SQL 7 and trying to insert data into table. For some reason i only be able to insert data into table as one time. The table only allow me to insert the first time, then second time or third time nothing happen. meanwhile, i'm thinking maybe i can use update statement after data has being inserted then i will be able to insert more data into table. According to the code above i only be able to insert one. Is there anything i have to do with SQL server to allow me to insert more record into table ...I'm not a SQL guy, i don't know what's kind of permission i should give to Table. Sorry for confusion

[Spudhead]

Hold on.

You firstly SELECT everything from your table matching a particular EFIN ID.

If nothing is returned, you INSERT a row with that EFIN ID.

Then you do it again? With the same EFIN ID? And it won't add another one?

It won't add another one because your code only does the "adding another row" bit if the select statement in front of it doesn't return any rows. And if you've just added a row, then it's going to return it.

Take the INSERT statement out from inside the "If rsData.eof then" condition, and you'll be able to add as many identical rows as you like.

[petertran123]

You're right, thanks i got it working...Is there anyway i can insert multiple values into 2 fields?

on my entry form i have 2 pairs which 4 fields total: i called them

strBegCkNo1 = Request.Form("BegCkNo1")
strEndCkNo1 = Request.Form("EndCkNo1")
strBegCkNo2 = Request.Form("BegCkNo2")
strEndCkNo2 = Request.Form("EndCkNo2")



strSQL = "Select * from ERO_CHECK_NUM where OFFICE_EFIN ='" & strEFIN & "'"
strSQL = "insert into ERO_CHECK_NUM (START_NUM, END_NUM) values ('" & _
strBegCkNo1 & "', '" & strEndCkNo1 & "'" & strBegCkNo2 & "', '" & strEndCkNo2 & "')"

I knew this is incorrect syntax of using code above. Can you help me to find another way to loop through. Thanks