Jump to content

SQL Insert Help Pls

Whether you're a seasoned veteran or a struggling beginner, Web Radiance is the web development and web design forum for you. You'll find answers to all your HTML, CSS, SEO, and Programming needs. Pull up a chair and stay awhile.

  • (3 Pages)
  • +
  • 1
  • 2
  • 3
  • You cannot start a new topic
  • This topic is locked

SQL Insert Help Pls Rate Topic: -----

#41 User is offline   moojoo 

  • Argh!
  • Group: Members
  • Posts: 449
  • Joined: 03-August 06
  • Location:Texas

Posted 08 November 2006 - 09:26 AM

Ok more info below.

This post has been edited by moojoo: 08 November 2006 - 02:59 PM

Its not girly, its web 2.0!
0

#42 User is offline   moojoo 

  • Argh!
  • Group: Members
  • Posts: 449
  • Joined: 03-August 06
  • Location:Texas

Posted 08 November 2006 - 10:31 AM

I added this to visually see the output and where it chokes.

do until c = numEmployees
c = c + 1
Randomize
max=75
min=1 

freebox = empName & " " & empName2 & "<br />" & theCurrentDate

table = "<table><thead><th>B</th><th>I</th><th>N</th><th>G</th><th>O</th></thead>"
table = table & newrow(1) & newrow(2)
table = table & "<tr><td>" & num("B",3) & "</td><td>" & num("I",3) & "</td><td class='freebox'>" & _
freebox & "</td><td>" & num("G",3) & "</td><td>" & num("O",3) & "</td></tr>"
table = table & newrow(4) & newrow(5) & "</table>"
response.write(table)
Loop


It is generating all the tables most of the time, if it doesn't generate a BOF or EOF error. For some reason I can't get it to move to the next employee id etc and it chokes after 11.5 tables as far as inserting the numbers into the db and table.. the problem I feel is that each card inserts 24 row per employee multiply that by 268 employees you have 6,432 rows being inserted to the DB per game which is a lot. Not sure if MSSQL can handle that let alone VBScript and .NET bugs... Ugh such a headache! lol. It chokes if there is say a matching B9 even if it is inserted under a different employee. i.e. it does not duplicate a col/number for the same employee which is good.

Full Page Code Below:

<%@ language="VBSCRIPT" %>

<!-- #include file="connections/db_conn.asp" -->

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 transitional//EN" "http://www.w3.org/td/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head>
<title>OCIW - Bingo Card Generator</title>
<link rel="stylesheet" href="css/print.css" media="print" type="text/css" />
<link rel="stylesheet" href="css/global.css" media="screen" type="text/css" />
</head>

<body>
<%
numEmployees = iCode("Total")

empName = employeeInfo("EmployeeFirstName")
empName2 = employeeInfo("EmployeeLastName")

sSQL = ""

Dim theCurrentDate, strDateStamp

strDateStamp = FormatDateTime(Now(), vbShortDate)

theCurrentDate = year(date) &_ 
left("00",2-len(month(date))) & month(date) &_ 
left("00",2-len(day(date))) & day(date)

Response.Write "<ul id='linklist'>"
Response.Write "<li id='current'><a href='index.asp'>New Game</a></li>"
Response.Write "<li><a href='print_cards.asp'>Print Cards</a></li>"
Response.Write "<li><a href='search_employee.asp'>Search by Employee</a></li>"
Response.Write "<li><a href='master_card.asp'>View Master Game Card</a></li>"
Response.Write "</ul>"
Response.Write "<div class='clearing'>&nbsp;</div>"
Response.Write "<p>" & numEmployees & " bingo cards have been generated and stored to the database on " & strDateStamp & ".</p>"
Response.Write "<p><strong>Game Number: " & theCurrentDate & "</strong></p>"
Response.Write "<p><strong>Start Date: " & strDateStamp & "</strong></p>"
%>

<%
do until c = numEmployees
c = c + 1
Randomize
max=75
min=1 

freebox = empName & " " & empName2 & "<br />" & theCurrentDate

table = "<table><thead><th>B</th><th>I</th><th>N</th><th>G</th><th>O</th></thead>"
table = table & newrow(1) & newrow(2)
table = table & "<tr><td>" & num("B",3) & "</td><td>" & num("I",3) & "</td><td class='freebox'>" & _
freebox & "</td><td>" & num("G",3) & "</td><td>" & num("O",3) & "</td></tr>"
table = table & newrow(4) & newrow(5) & "</table>"
response.write(table)
Loop
%>
</body>
</html>
<%
function num(col,row)
If NOT employeeInfo.EOF then
empcode = employeeInfo("EmployeeCode")
Do
num = (Int((max-min+1)*Rnd+min))
cell = col & num & "|"

Loop Until (instr(carddata,cell)=0)

'DO SQL INSERT HERE
sSQL = "INSERT INTO Foo (CompanyCode, EmployeeID, GameNumber, Col, Row) VALUES ('OCIT', '" & empCode & "', '" & theCurrentDate & "', '" & col & "', '" & num & "')"

objConn.Execute(sSQL)
carddata = carddata & cell
employeeInfo.MoveNext
else
Response.Write ""
End If
end function

function newrow(row)
newrow = "<tr><td>" & num("B",row) & "</td><td>" & num("I",row) & "</td><td>" & num("N",row) & "</td><td>" & num("G",row) & "</td><td>" & num("O",row) & "</td></tr>"
end function
%>

This post has been edited by moojoo: 08 November 2006 - 10:36 AM

Its not girly, its web 2.0!
0

#43 User is offline   Catalyst 

  • Codesmith
  • Group: Administrators
  • Posts: 1,049
  • Joined: 04-April 06
  • Gender:Male
  • Location:San Diego

Posted 08 November 2006 - 02:48 PM

Yeah, your script is just timing out from all the db communication. You can increase the time it has to run by putting:

Server.ScriptTimeout = 100

At the start of the page, replacing 100 with the number of seconds you want to allow. It's a shame the db is poorly structured, with some changes you could probably get this thing running many many times faster.
0

#44 User is offline   moojoo 

  • Argh!
  • Group: Members
  • Posts: 449
  • Joined: 03-August 06
  • Location:Texas

Posted 08 November 2006 - 03:12 PM

What changes would you recommend?
Its not girly, its web 2.0!
0

#45 User is offline   Catalyst 

  • Codesmith
  • Group: Administrators
  • Posts: 1,049
  • Joined: 04-April 06
  • Gender:Male
  • Location:San Diego

Posted 08 November 2006 - 04:03 PM

Well, your duplicates problem sound like it's related to that field being a primary key. For this you'd only want the primary key to be a database generated identity column.

As for performance, you'd probably want just one record per card as I'd discussed earlier in this post. That'll give you 24 times less inserts.

For the best performance I'd say to write some custom functions in SQL server itself to generate the cards. Then you'd get much better database performance and you wouldn't have all that data going back and forth between ASP and SQL server. Of course to write this you'd need control over the database.
0

#46 User is offline   moojoo 

  • Argh!
  • Group: Members
  • Posts: 449
  • Joined: 03-August 06
  • Location:Texas

Posted 09 November 2006 - 10:06 AM

Ok we are restructuring the database to be

[CompanyCode] [EmployeeID] [GameNumber] [b1] [b2].... [i1][i2]... etc so it will be one insert vs 24.

This post has been edited by moojoo: 09 November 2006 - 12:05 PM

Its not girly, its web 2.0!
0

#47 User is offline   Catalyst 

  • Codesmith
  • Group: Administrators
  • Posts: 1,049
  • Joined: 04-April 06
  • Gender:Male
  • Location:San Diego

Posted 09 November 2006 - 02:08 PM

That'll work, but it's going to be a pain to search for matches as you'll have to test 24 fields.

My recommendation is
[CompanyCode] [EmployeeID] [GameNumber] [CardData]...

By putting all the data in one field you can search it with a single LIKE statement.
0

#48 User is offline   moojoo 

  • Argh!
  • Group: Members
  • Posts: 449
  • Joined: 03-August 06
  • Location:Texas

Posted 09 November 2006 - 02:39 PM

I hear what you are saying and that makes sense but for now we have it working mostly and thats good! It should be relatively simple to pull the data and display it and have the "master card" check for matches no? At this point we are only dealing with 1 row per employee now vs 24 rows.

Ahh its not so bad this way.. one more thing though because now I have it almost working! It is inserting for each employee as it should but it is only inserting 1 number per row.

i.e. employee 002 would get 12, 12, 12, 12.... etc... Trying to figure out how to get num to generate a different number for each field b1, b2, b3, b4 etc.....

So would I place num in a loop or some how have it modify.. but not sure how to do that in VBS... Perhaps set it to an array and call the array in the insert?

Do Until i = numEmployees
i = i + 1

num = (Int((max-min+1)*Rnd+min))

empcode = employeeInfo("EmployeeCode")

sSQL = "INSERT INTO OCIW_EmployeeBingo (CompanyCode, EmployeeID, GameNumber, B1, B2, B3, B4, B5, I1, I2, I3, I4, I5, N1, N2, N3, N4, N5, G1, G2, G3, G4, G5, O1, O2, O3, O4, O5) VALUES ('OCIT', '" & empCode & "', '" & theCurrentDate & "', '" & num & "', '" & num & "', '" & num & "', '" & num & "', '" & num & "', '" & num & "', '" & num & "', '" & num & "', '" & num & "', '" & num & "', '" & num & "', '" & num & "', '" & num & "', '" & num & "', '" & num & "', '" & num & "', '" & num & "', '" & num & "', '" & num & "', '" & num & "', '" & num & "', '" & num & "', '" & num & "', '" & num & "', '" & num & "')"

objConn.Execute(sSQL)
employeeInfo.MoveNext
Loop

This post has been edited by moojoo: 09 November 2006 - 02:42 PM

Its not girly, its web 2.0!
0

#49 User is offline   Catalyst 

  • Codesmith
  • Group: Administrators
  • Posts: 1,049
  • Joined: 04-April 06
  • Gender:Male
  • Location:San Diego

Posted 09 November 2006 - 03:29 PM

You'd want to still use the num function from before so that it will check for duplicates and create a new number each time it's called. Just take out the INSERT part from the num function.
0

#50 User is offline   moojoo 

  • Argh!
  • Group: Members
  • Posts: 449
  • Joined: 03-August 06
  • Location:Texas

Posted 13 November 2006 - 11:37 AM

Fixed the problem.

This post has been edited by moojoo: 15 November 2006 - 01:41 PM

Its not girly, its web 2.0!
0

#51 User is offline   moojoo 

  • Argh!
  • Group: Members
  • Posts: 449
  • Joined: 03-August 06
  • Location:Texas

Posted 16 November 2006 - 01:53 PM

Ok one last thing because this needs to be done on Friday.. And I am a tard. I know previously we had a function to check for duplicates but is there a way I could do the same thing with what we have here?

Do Until i = numEmployees
i = i + 1
empName = employeeInfo("EmployeeFirstName")
empName2 = employeeInfo("EmployeeLastName")

Randomize
Max=75
Min=1

Do
num = (Int((max-min+1)*Rnd+min))
num2 = (Int((max-min+1)*Rnd+min))
num3 = (Int((max-min+1)*Rnd+min))
num4 = (Int((max-min+1)*Rnd+min))
num5 = (Int((max-min+1)*Rnd+min))
num6 = (Int((max-min+1)*Rnd+min))
num7 = (Int((max-min+1)*Rnd+min))
num8 = (Int((max-min+1)*Rnd+min))
num9 = (Int((max-min+1)*Rnd+min))
num10 = (Int((max-min+1)*Rnd+min))
num11 = (Int((max-min+1)*Rnd+min))
num12 = (Int((max-min+1)*Rnd+min))
num13 = (Int((max-min+1)*Rnd+min))
num14 = (Int((max-min+1)*Rnd+min))
num15 = (Int((max-min+1)*Rnd+min))
num16 = (Int((max-min+1)*Rnd+min))
num17 = (Int((max-min+1)*Rnd+min))
num18 = (Int((max-min+1)*Rnd+min))
num19 = (Int((max-min+1)*Rnd+min))
num20 = (Int((max-min+1)*Rnd+min))
num21 = (Int((max-min+1)*Rnd+min))
num22 = (Int((max-min+1)*Rnd+min))
num23 = (Int((max-min+1)*Rnd+min))
num24 = (Int((max-min+1)*Rnd+min))
Loop Until (instr(carddata,cell)=0)

freebox = empName & " " & empName2 & "<br />" & theCurrentDate
empcode = employeeInfo("EmployeeCode")

sSQL = "INSERT INTO Foo_Table (CompanyCode, EmployeeID, GameNumber, B1, B2, B3, B4, B5, I1, I2, I3, I4, I5, N1, N2, N3, N4, N5, G1, G2, G3, G4, G5, O1, O2, O3, O4, O5) VALUES ('OCIT', '" & empCode & "', '" & theCurrentDate & "', '" & num & "', '" & num2 & "', '" & num3 & "', '" & num4 & "', '" & num5 & "', '" & num6 & "', '" & num7 & "', '" & num8 & "', '" & num9 & "', '" & num10 & "', '" & num11 & "', '" & num12 & "', '" & freebox & "', '" & num13 & "', '" & num14 & "', '" & num15 & "', '" & num16 & "', '" & num17 & "', '" & num18 & "', '" & num19 & "', '" & num20 & "', '" & num21 & "', '" & num22 & "', '" & num23 & "', '" & num24 & "')"

objConn.Execute(sSQL)
employeeInfo.MoveNext
Loop

This post has been edited by moojoo: 16 November 2006 - 01:53 PM

Its not girly, its web 2.0!
0

#52 User is offline   Catalyst 

  • Codesmith
  • Group: Administrators
  • Posts: 1,049
  • Joined: 04-April 06
  • Gender:Male
  • Location:San Diego

Posted 16 November 2006 - 03:31 PM 

dim carddata
Max=75
Min=1
    
Do Until i = numEmployees
    carddata = ""
    i = i + 1
    
    empName = employeeInfo("EmployeeFirstName")
    empName2 = employeeInfo("EmployeeLastName")
    
    Randomize

    freebox = empName & " " & empName2 & "<br />" & theCurrentDate
    empcode = employeeInfo("EmployeeCode")
    
    sSQL = "INSERT INTO Foo_Table (CompanyCode, EmployeeID, GameNumber, B1, B2, B3, B4, B5, I1, I2, I3, I4, I5, N1, N2, N3, " & _
        "N4, N5, G1, G2, G3, G4, G5, O1, O2, O3, O4, O5) VALUES " & _
        "('OCIT', '" & empCode & "', '" & theCurrentDate & "', '" & _
        num("B") & "', '" & num("B") & "', '" & num("B") & "', '" & num("B") & "', '" & num("B") & "', '" & _
        num("I") & "', '" & num("I") & "', '" & num("I") & "', '" & num("I") & "', '" & num("I") & "', '" & _
        num("N") & "', '" & num("N") & "', '" & freebox & "', '" & num("N") & "', '" & num("N") & "', '" & _
        num("G") & "', '" & num("G") & "', '" & num("G") & "', '" & num("G") & "', '" & num("G") & "', '" & _
        num("O") & "', '" & num("O") & "', '" & num("O") & "', '" & num("O") & "', '" & num("O") & "')"
    objConn.Execute(sSQL)
    employeeInfo.MoveNext
Loop

function num(col)

    Do
    num = (Int((max-min+1)*Rnd+min))
    cell = col & num & "|"

    Loop Until (instr(carddata,cell)=0)
    carddata = carddata & cell
end function

0

#53 User is offline   moojoo 

  • Argh!
  • Group: Members
  • Posts: 449
  • Joined: 03-August 06
  • Location:Texas

Posted 16 November 2006 - 03:34 PM

Whoa, can you break down what you did exactly so I can learn from your excellent example? I see the changes but would like info on why/how they do the trick if you can spare the time.

This post has been edited by moojoo: 16 November 2006 - 03:35 PM

Its not girly, its web 2.0!
0

#54 User is offline   Catalyst 

  • Codesmith
  • Group: Administrators
  • Posts: 1,049
  • Joined: 04-April 06
  • Gender:Male
  • Location:San Diego

Posted 17 November 2006 - 02:22 AM

Well, then num# variables all had the same code which to me meant it should be replaced by a function, so that got moved into the num() function. And since the num# variables don't do anything but hold a value to be used in the SQL there wasn't really a need for them, so I replaced the variables in the SQL string with calls to the num() function.

I set up the carddata variable to clear itself for each card, but during each card it builds up a string of all the numbers used in the card so far, so you can check for duplicates when generating a new number (that's the instr function checking the cardnum string for the new random number, the "loop until" part repeating until the new number isn't found). Because duplicates on the card are ok, just not in the column I had to pass which column the number was going to be used for to the num() function.

Other than that I just used the line continuation character _ to format the SQL string over a few lines so it was easier to read.

For the num() function in detail:
function num(col) '<- column letter is passed
	Do '<- Begin the number generating loop
	num = (Int((max-min+1)*Rnd+min)) '<- Set the function name to a new number to be returned
	cell = col & num & "|" ' <- Combines the letter with the number and a delimiter, like "B23|"
	Loop Until (instr(carddata,cell)=0) ' <- Check for "B23|" in the carddata string, if it's found loop back to make a new number
	carddata = carddata & cell ' <- Now you've got a unique number, add "B23|" to the string to be checked on the next num() function call
end function ' <- return the new number


The carddata string would look like "B23|B15|B7|I11|I3|I55|..." as it builds, but num() just returns the number part, not the value of cell which has the letter and delimiter in it too.
0

#55 User is offline   moojoo 

  • Argh!
  • Group: Members
  • Posts: 449
  • Joined: 03-August 06
  • Location:Texas

Posted 17 November 2006 - 10:27 AM

Awesome.
Its not girly, its web 2.0!
0

Share this topic:


  • (3 Pages)
  • +
  • 1
  • 2
  • 3
  • You cannot start a new topic
  • This topic is locked

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users