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:

#1 moojoo

Argh!

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

Posted 12 October 2006 - 08:53 AM

Ok here is my code, everything works but I am getting stuck on the SQL insert. My first VBS thing so bear with me. Maybe there is a better way for me to organize this and structure it? Any input/help is appreciated. Basically I need each cell to insert the number into a database associated with each employee already in the database and of course the date stamp etc so the "Master Card" which pulls a random number daily can check against it etc.. Currently without the sql stuff it works as expected, is print ready blah blah.. So what would be the best/correct way to insert all the data?

<%@ language="VBSCRIPT" %>

<!-- #include file="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>Bingo Card Generator</title>
	<link rel="stylesheet" href="print.css" media="print" type="text/css" />
	<link rel="stylesheet" href="global.css" media="screen" type="text/css" />
</head>
<body>
<%
' Gets the row count from the employees field
numEmployees = iCode("Total")
%>

<%
' Get the current date and convert the string to a date object
Dim theCurrentDate
theDate = FormatDateTime(Date(), vbShortDate)
theCurrentDate = (CDate(theDate))

dim cell1, cell2, cell3, cell4, cell5, cell6, cell7, cell8, cell9, cell10, cell11, cell12, cell13, cell14
dim cell15, cell16, cell17, cell18, cell19, cell20, cell21, cell22, cell23, cell24

' set the numbers to choose from
dim emCard
dim min, max
Randomize
max=75
min=1

' Write out the tables based on the number of entries in the employee field
Do Until i = numEmployees
i = i + 1

' Move to next record in the employee field
employeeInfo.MoveNext

' Checks to see if employeeInfo is EOF or not
if employeeInfo.EOF then
Response.Write ""
Else
' Output the tables
Response.Write "<table>"
Response.Write "<thead><th>B</th><th>I</th><th>N</th><th>G</th><th>O</th></thead>"
Response.Write "<tr>"
Response.Write "<td>" 
cell1 = Response.Write (Int((max-min+1)*Rnd+min)) 
Response.Write "</td>"
Response.Write "<td>" 
cell2 = Response.Write (Int((max-min+1)*Rnd+min)) 
Response.Write "</td>"
Response.Write "<td>" 
cell3 = Response.Write (Int((max-min+1)*Rnd+min)) 
Response.Write "</td>"
Response.Write "<td>" 
cell4 = Response.Write (Int((max-min+1)*Rnd+min)) 
Response.Write "</td>"
Response.Write "<td>" 
cell5 = Response.Write (Int((max-min+1)*Rnd+min)) 
Response.Write "</td>"
Response.Write "</tr>"
Response.Write "<tr>"
Response.Write "<td>" 
cell6 = Response.Write (Int((max-min+1)*Rnd+min)) 
Response.Write "</td>"
Response.Write "<td>" 
cell7 = Response.Write (Int((max-min+1)*Rnd+min)) 
Response.Write "</td>"
Response.Write "<td>" 
cell8 = Response.Write (Int((max-min+1)*Rnd+min)) 
Response.Write "</td>"
Response.Write "<td>" 
cell9 = Response.Write (Int((max-min+1)*Rnd+min)) 
Response.Write "</td>"
Response.Write "<td>" 
cell10 = Response.Write (Int((max-min+1)*Rnd+min)) 
Response.Write "</td>"
Response.Write "</tr>"
Response.Write "<tr>"
Response.Write "<td>" 
cel11 = Response.Write (Int((max-min+1)*Rnd+min)) 
Response.Write "</td>"
Response.Write "<td>" 
cell12 = Response.Write (Int((max-min+1)*Rnd+min)) 
Response.Write "</td>"
Response.Write "<td class='freebox'>"
' Inserts the employee name and game start date to the free box
Response.Write theCurrentDate
' Displays the employees name in the free box on each card
Response.Write employeeInfo.Fields("EmployeeFullName")
' Sets the Employee Code to a hidden field
Response.Write "<input type='hidden' id='employeecode' value='"
Response.Write employeeInfo.Fields("EmployeeCode")
Response.Write "' />"
Response.Write "</td>"
Response.Write "<td>" 
cell13 = Response.Write (Int((max-min+1)*Rnd+min)) 
Response.Write "</td>"
Response.Write "<td>" 
cell14 = Response.Write (Int((max-min+1)*Rnd+min)) 
Response.Write "</td>"
Response.Write "</tr>"
Response.Write "<tr>"
Response.Write "<td>" 
cell15 = Response.Write (Int((max-min+1)*Rnd+min)) 
Response.Write "</td>"
Response.Write "<td>" 
cell16 = Response.Write (Int((max-min+1)*Rnd+min))
Response.Write "</td>"
Response.Write "<td>" 
cell17 = Response.Write (Int((max-min+1)*Rnd+min)) 
Response.Write "</td>"
Response.Write "<td>" 
cell18 = Response.Write (Int((max-min+1)*Rnd+min)) 
Response.Write "</td>"
Response.Write "<td>"
cell19 = Response.Write (Int((max-min+1)*Rnd+min)) 
Response.Write "</td>"
Response.Write "</tr>"
Response.Write "<tr>"
Response.Write "<td>" 
cell20 = Response.Write (Int((max-min+1)*Rnd+min)) 
Response.Write "</td>"
Response.Write "<td>" 
cell21 = Response.Write (Int((max-min+1)*Rnd+min)) 
Response.Write "</td>"
Response.Write "<td>" 
cell22 = Response.Write (Int((max-min+1)*Rnd+min)) 
Response.Write "</td>"
Response.Write "<td>" 
cell23 = Response.Write (Int((max-min+1)*Rnd+min)) 
Response.Write "</td>"
Response.Write "<td>" 
cell24 = Response.Write (Int((max-min+1)*Rnd+min)) 
Response.Write "</td>"
Response.Write "</tr>"
Response.Write "</table>"

' Insert data into the database set
dim connection
dim sSQL, sConnString

'declare SQL statement that will query the database
sSQL = "INSERT INTO OCIW_EmployeeBingo (CompanyCode, EmployeeID, GameNumber, Col, Row, tstamp) VALUES ('', '', '', '', '', '" + theCurrentDate + "')"

'define the connection string, specify database
'driver and the location of database
sConnString="Driver={SQL Server};" & _
"Server=*************; Uid=*****; Pwd=*****;"

'create an ADO connection object
Set connection = Server.CreateObject("ADODB.Connection")

'Open the connection to the database
connection.Open(sConnString)

'execute the SQL
connection.execute(sSQL)

' Ends the check if employeeInfo is .EOF or not, eliminating the last error table due to no record found
End If
Loop
%>
</body>
</html>

<%Response.End%>

<%
' Close it all up
employeeInfo.Close
result.Close
emResult.Close
emCard.Close
Connection.Close
Set Connection = Nothing
set employeecode = Nothing
set cell1 = Nothing
set cell2 = Nothing
set cell3 = Nothing
set cell4 = Nothing
set cell5 = Nothing
set cell6 = Nothing
set cell7 = Nothing
set cell8 = Nothing
set cell9 = Nothing
set cell10 = Nothing
set cell11 = Nothing
set cell12 = Nothing
set cell13 = Nothing
set cell14 = Nothing
set cell15 = Nothing
set cell16 = Nothing
set cell17 = Nothing
set cell18 = Nothing
set cell19 = Nothing
set cell20 = Nothing
set cell21 = Nothing
set cell22 = Nothing
set cell23 = Nothing
set cell24 = Nothing
%>

This post has been edited by herkalees: 17 October 2006 - 07:55 AM

Its not girly, its web 2.0!

Back to top
Back to top of the page up there ^
MultiQuote
Reply

#2 Catalyst

Codesmith

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

Posted 14 October 2006 - 01:37 AM

Hmm, there's a lot you could do to shorten the amount of code in use but it works so I'll just address a couple important things.

You should move creating and opening the connection to before the loop. Right now you're creating a database connection everytime you loop and that eats up resources, you just want to open it once.

You could use an array instead of 25 different cell variables, but I'd say instead of creating 25 entries in the database you might want to just store the whole card in one field. That way you only have 1 INSERT instead of 25. So build the whole table up in a variable, Response.write it to the page and INSERT it to the database.

When you have to seach for a matching number just use WHERE fieldname LIKE '%<td>' + num + '</td>%'

On a side note, I edited your post to remove the SQL server address, username, and password cause I'm sure you don't want everyone to have that...

Back to top
Back to top of the page up there ^
MultiQuote
Reply

#3 moojoo

Argh!

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

Posted 16 October 2006 - 08:20 AM

Woops, though I removed that, either way its a local thing which is pretty tight But thanks! I know the code could be more streamlined and I appreciate your input. My first VBScript project really. So how woul dyou recommend placing the entire table into one variable?

The simplicity of VBscript oddly enough confuses the hell out of me. I much prefer PHP/MySQL to MSSQL and ASP, VB etc.. Although they are very similar in many regards. with PHP I could output the whole table no problem in one variable but VBScript seems to spit out errors on occasion partly due to Bugs in IE. I tell yah.

I totally missed placing the connection in the loop too lol.

This post has been edited by moojoo: 16 October 2006 - 08:28 AM

Its not girly, its web 2.0!

Back to top
Back to top of the page up there ^
MultiQuote
Reply

#4 moojoo

Argh!

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

Posted 16 October 2006 - 12:24 PM

Ok I got it all working. Thanks for your input though.

<%@ language="VBSCRIPT" %>

<!-- #include file="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>Bingo Card Generator</title>
	<link rel="stylesheet" href="print.css" media="print" type="text/css" />
	<link rel="stylesheet" href="global.css" media="screen" type="text/css" />
</head>
<body>
<%
' Gets the row count from the employees field
numEmployees = iCode("Total")
%>

<%
' Get the current date and convert the string to a date object
Dim theCurrentDate, dteDay, dteMonth, dteYear, theDate, myDateFunc

theDate = Date()
dteDay = Day(dteCurrent)
dteMonth = Month(dteCurrent)
dteYear = Year(dteCurrent)

theCurrentDate = dteYear & dteMonth & dteDay

dim cell1, cell2, cell3, cell4, cell5, cell6, cell7, cell8, cell9, cell10, cell11, cell12, cell13, cell14
dim cell15, cell16, cell17, cell18, cell19, cell20, cell21, cell22, cell23, cell24

' set the numbers to choose from
dim emCard
dim min, max
Randomize
max=75
min=1

dim empCode

' Insert data into the database set
dim connection
dim sSQL, sConnString

'define the connection string, specify database
'driver and the location of database
sConnString="Driver={SQL Server};" & _
"Server=foo.foo.com; Uid=userid; Pwd=userpassword;"

'create an ADO connection object
Set connection = Server.CreateObject("ADODB.Connection")

'Open the connection to the database
connection.Open(sConnString)

' Write out the tables based on the number of entries in the employee field
Do Until i = numEmployees
i = i + 1

' Move to next record in the employee field
employeeInfo.MoveNext

' Checks to see if employeeInfo is EOF or not
if employeeInfo.EOF then
Response.Write ""
Else
' Output the tables
Response.Write "<table>"
Response.Write "<thead><th>B</th><th>I</th><th>N</th><th>G</th><th>O</th></thead>"
Response.Write "<tr>"
Response.Write "<td>" 
Response.Write (Int((max-min+1)*Rnd+min)) 
Response.Write "</td>"
Response.Write "<td>" 
Response.Write (Int((max-min+1)*Rnd+min)) 
Response.Write "</td>"
Response.Write "<td>" 
Response.Write (Int((max-min+1)*Rnd+min)) 
Response.Write "</td>"
Response.Write "<td>" 
Response.Write (Int((max-min+1)*Rnd+min)) 
Response.Write "</td>"
Response.Write "<td>" 
Response.Write (Int((max-min+1)*Rnd+min)) 
Response.Write "</td>"
Response.Write "</tr>"
Response.Write "<tr>"
Response.Write "<td>" 
Response.Write (Int((max-min+1)*Rnd+min)) 
Response.Write "</td>"
Response.Write "<td>" 
Response.Write (Int((max-min+1)*Rnd+min)) 
Response.Write "</td>"
Response.Write "<td>" 
Response.Write (Int((max-min+1)*Rnd+min)) 
Response.Write "</td>"
Response.Write "<td>" 
Response.Write (Int((max-min+1)*Rnd+min)) 
Response.Write "</td>"
Response.Write "<td>" 
Response.Write (Int((max-min+1)*Rnd+min)) 
Response.Write "</td>"
Response.Write "</tr>"
Response.Write "<tr>"
Response.Write "<td>" 
Response.Write (Int((max-min+1)*Rnd+min)) 
Response.Write "</td>"
Response.Write "<td>" 
Response.Write (Int((max-min+1)*Rnd+min)) 
Response.Write "</td>"
Response.Write "<td class='freebox'>"
' Inserts the employee name and game start date to the free box
Response.Write theCurrentDate
Response.Write "<br />"
' Displays the employees name in the free box on each card
Response.Write employeeInfo.Fields("EmployeeFullName")
' Sets the Employee Code to a hidden field
empCode = employeeInfo.Fields("EmployeeCode")
Response.Write "<input type='hidden' id='employeecode' value='"
Response.Write empCode
Response.Write "' />"
Response.Write "</td>"
Response.Write "<td>" 
Response.Write (Int((max-min+1)*Rnd+min)) 
Response.Write "</td>"
Response.Write "<td>" 
Response.Write (Int((max-min+1)*Rnd+min)) 
Response.Write "</td>"
Response.Write "</tr>"
Response.Write "<tr>"
Response.Write "<td>" 
Response.Write (Int((max-min+1)*Rnd+min)) 
Response.Write "</td>"
Response.Write "<td>" 
Response.Write (Int((max-min+1)*Rnd+min))
Response.Write "</td>"
Response.Write "<td>" 
Response.Write (Int((max-min+1)*Rnd+min)) 
Response.Write "</td>"
Response.Write "<td>" 
Response.Write (Int((max-min+1)*Rnd+min)) 
Response.Write "</td>"
Response.Write "<td>"
Response.Write (Int((max-min+1)*Rnd+min)) 
Response.Write "</td>"
Response.Write "</tr>"
Response.Write "<tr>"
Response.Write "<td>" 
Response.Write (Int((max-min+1)*Rnd+min)) 
Response.Write "</td>"
Response.Write "<td>" 
Response.Write (Int((max-min+1)*Rnd+min)) 
Response.Write "</td>"
Response.Write "<td>" 
Response.Write (Int((max-min+1)*Rnd+min)) 
Response.Write "</td>"
Response.Write "<td>" 
Response.Write (Int((max-min+1)*Rnd+min)) 
Response.Write "</td>"
Response.Write "<td>" 
Response.Write (Int((max-min+1)*Rnd+min)) 
Response.Write "</td>"
Response.Write "</tr>"
Response.Write "</table>"

'declare SQL statement that will query the database
sSQL = "INSERT INTO OCIW_EmployeeBingo (CompanyCode, EmployeeID, GameNumber, Col, Row) VALUES ('OCIT', '"+ empCode +"', '"+ theCurrentDate +"', '', '')"

'execute the SQL
connection.execute(sSQL)

' Ends the check if employeeInfo is .EOF or not, eliminating the last error table due to no record found
End If
Loop
%>
</body>
</html>

<%Response.End%>

<%
' Close it all up
employeeInfo.Close
result.Close
emResult.Close
emCard.Close
Connection.Close
Set Connection = Nothing
set employeecode = Nothing
%>

Its not girly, its web 2.0!

Back to top
Back to top of the page up there ^
MultiQuote
Reply

#5 Catalyst

Codesmith

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

Posted 17 October 2006 - 03:41 AM

So I rewrote this code to clean up the vbscript. I figured since you're learning it it might be helpful. The only functional change is that I made it store the whole table in one field in the database (Card) which should be a varchar(600). I can whip up some other code for grabbing the matching record from the db for a number if you like. You could probably clean it up more, since you're not using OPTION EXPLICIT you don't really need to DIM your variables for example.

<%@ language="VBSCRIPT" %>

<!-- #include file="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>Bingo Card Generator</title>
	<link rel="stylesheet" href="print.css" media="print" type="text/css" />
	<link rel="stylesheet" href="global.css" media="screen" type="text/css" />
</head>
<body>

<%
' Get the current date and convert the string to a date object
Dim theCurrentDate, dteDay, dteMonth, dteYear, theDate, myDateFunc

theDate = Date()
dteDay = Day(dteCurrent)
dteMonth = Month(dteCurrent)
dteYear = Year(dteCurrent)

theCurrentDate = dteYear & dteMonth & dteDay

' set the numbers to choose from
dim emCard
dim min, max
Randomize
max=75
min=1

dim empCode

' Insert data into the database set
dim connection
dim sSQL, sConnString

'define the connection string, specify database
'driver and the location of database
sConnString="Driver={SQL Server};" & _
"Server=foo.foo.com; Uid=userid; Pwd=userpassword;"

'create an ADO connection object
Set connection = Server.CreateObject("ADODB.Connection")

'Open the connection to the database
connection.Open(sConnString)

' Write out the tables based on the number of entries in the employee field

dim table

do while not employeeInfo.eof

empcode = employeeInfo("EmployeeCode")

freebox = theCurrentDate & "<br />" & employeeInfo("EmployeeFullName") & _
	"<input type='hidden' id='employeecode' value='" & empcode & "' />"

table =  "<table><thead><th>B</th><th>I</th><th>N</th><th>G</th><th>O</th></thead>"
table = table & newrow & newrow
table = table & "<tr><td>" & num & "</td><td>" & num & "</td><td class='freebox'>" & _
		freebox & "</td><td>" & num & "</td><td>" & num & "</td></tr>"
table = table & newrow & newrow & "</table>"

response.write(table)

'declare SQL statement that will query the database
sSQL = "INSERT INTO OCIW_EmployeeBingo (CompanyCode, EmployeeID, GameNumber, Card) VALUES ('OCIT', '" & empCode & "', '" & theCurrentDate & "', '" & table & "')"

'execute the SQL
connection.execute(sSQL)

' Move to next record in the employee field
employeeInfo.MoveNext

Loop
%>
</body>
</html>
<%
' Close it all up
employeeInfo.Close
Connection.Close
Set Connection = Nothing

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

function newrow()
	newrow = "<tr><td>" & num & "</td><td>" & num & "</td><td>" & num & "</td><td>" & num & "</td><td>" & num & "</td></tr>"
end function
%>

Back to top
Back to top of the page up there ^
MultiQuote
Reply

#6 moojoo

Argh!

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

Posted 17 October 2006 - 08:25 AM

Wow, that is bad ass. Unfortunately the table itself can't be stored, only the randomly generated numbers can. However this is awesome and I am learning as I push through. I hate .NET but eh it pays the bills and won't hurt to know.

so what is the difference between "+ variable +" and "& variable &"? I am used to using ampersands with other languages but a lot of the examples I see in VBS use + signs which is more like javascript no?

This post has been edited by moojoo: 17 October 2006 - 08:43 AM

Its not girly, its web 2.0!

Back to top
Back to top of the page up there ^
MultiQuote
Reply

#7 moojoo

Argh!

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

Post icon Posted 17 October 2006 - 09:54 AM

So this is what I have now which so far so good is working awesome! I took your advice and slimmed it up with your functions. Much cleaner!

<%@ language="VBSCRIPT" %>

<!-- #include file="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>Bingo Card Generator</title>
	<link rel="stylesheet" href="print.css" media="print" type="text/css" />
	<link rel="stylesheet" href="global.css" media="screen" type="text/css" />
</head>
<body>
<%
' Gets the row count from the employees field
numEmployees = iCode("Total")
%>

<%
' Get the current date and convert the string to a date object
Dim theCurrentDate, strDateStamp

strDateStamp = FormatDateTime(Now(), vbShortDate)
theCurrentDate = (Replace(strDateStamp,"/",""))

dim cell1, cell2, cell3, cell4, cell5, cell6, cell7, cell8, cell9, cell10, cell11, cell12, cell13, cell14
dim cell15, cell16, cell17, cell18, cell19, cell20, cell21, cell22, cell23, cell24

' set the numbers to choose from
dim emCard
dim min, max
Randomize
max=75
min=1

dim empCode

' Insert data into the database set
dim connection
dim sSQL, sConnString

'define the connection string, specify database
'driver and the location of database
sConnString="Driver={SQL Server};" & _
"Server=foo.foo.com; Uid=userid; Pwd=userpassword;"

'create an ADO connection object
Set connection = Server.CreateObject("ADODB.Connection")

'Open the connection to the database
connection.Open(sConnString)

' Write out the tables based on the number of entries in the employee field
Do Until i = numEmployees
i = i + 1

' Move to next record in the employee field
employeeInfo.MoveNext

' Checks to see if employeeInfo is EOF or not
if employeeInfo.EOF then
Response.Write ""
Else
' Output the tables
dim table

empcode = employeeInfo("EmployeeCode")

freebox = theCurrentDate & "<br />" & employeeInfo("EmployeeFullName") & _
"<input type='hidden' id='employeecode' value='" & empcode & "' />"

table = "<table><thead><th>B</th><th>I</th><th>N</th><th>G</th><th>O</th></thead>"
table = table & newrow & newrow
table = table & "<tr><td>" & num & "</td><td>" & num & "</td><td class='freebox'>" & _
freebox & "</td><td>" & num & "</td><td>" & num & "</td></tr>"
table = table & newrow & newrow & "</table>"

response.write(table)

'declare SQL statement that will query the database
sSQL = "INSERT INTO OCIW_EmployeeBingo (CompanyCode, EmployeeID, GameNumber, Col, Row) VALUES ('OCIT', '"& empCode &"', '"& theCurrentDate &"', '" & num & "', '')"

'execute the SQL insert
connection.execute(sSQL)

' Ends the check if employeeInfo is .EOF or not, eliminating the last error table due to no record found
End If
Loop
%>
</body>
</html>

<%
function num()
num = (Int((max-min+1)*Rnd+min))
end function

function newrow()
newrow = "<tr><td>" & num & "</td><td>" & num & "</td><td>" & num & "</td><td>" & num & "</td><td>" & num & "</td></tr>"
end function
%>

<%Response.End%>

<%
' Close it all up
employeeInfo.Close
result.Close
emResult.Close
emCard.Close
Connection.Close
Set Connection = Nothing
set employeecode = Nothing
%>

Its not girly, its web 2.0!

Back to top
Back to top of the page up there ^
MultiQuote
Reply

#8 moojoo

Argh!

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

Posted 17 October 2006 - 02:48 PM

Another question.. Would your method prove more effective in I guess storing each tables numbers into a recordset, inserting that recordset and moving to the next? i.e. I need to be able to tell Column B, I, N, G, O and then the numbers in each column which would then be stored col and row.... Make sense?

Its not girly, its web 2.0!

Back to top
Back to top of the page up there ^
MultiQuote
Reply

#9 Catalyst

Codesmith

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

Posted 17 October 2006 - 04:36 PM

Most everything I've seen in vbs uses & for string building. If you were in .NET using C# it would be +

Do you need to know the column and row for each number or do you just need to know that the number is on a card for the employee?

The reason I suggest storing the full table (or at least all the numbers of a card in one record) is that you're only making 1 database call per card rather than 25. With a few cards and a decent number of employees you could end up making hundreds or even into the thousands of INSERTS (5 cards each for 20 employees is 2500 INSERT statements for example).

Back to top
Back to top of the page up there ^
MultiQuote
Reply

#10 moojoo

Argh!

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

Posted 18 October 2006 - 01:31 PM

yeah I need to know each number and the column it is in. Where it gets complicated. And then of course check to make sure there are no duplicates in each column. e.g B 2 can't be in the same card twice.

At the moment I have it inserting "& num &" into the database the problem however with that is it appears to be inserting random numbers instead of what is being output to the tables.. heh I feel like such a tard mucking with this. Learning curves suck.

This post has been edited by moojoo: 18 October 2006 - 02:11 PM

Its not girly, its web 2.0!

Back to top
Back to top of the page up there ^
MultiQuote
Reply

#11 Catalyst

Codesmith

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

Posted 18 October 2006 - 03:28 PM

Ok, cool. I know what you've got going on now. With the slimmed down code it'll be easier to get what you want. I've got a little work to do then I'll post some tweaked code.

Back to top
Back to top of the page up there ^
MultiQuote
Reply

#12 Catalyst

Codesmith

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

Posted 18 October 2006 - 11:21 PM

Ok, so I tweaked the code a bit. Now it stores a smaller, more streamline data set in one record using one INSERT per card along with the column info. It also prevents having the same number in a column more than once.

The database will be easy to search for a match, I can provide a sample for that.

I stripped out a bunch of stuff for the example, but mostly you just have to update 8 or so lines from my previous post.

Running sample: http://www.spark9.co...mple/moojoo.asp

Source code:

<%@ language="VBSCRIPT" %>

<!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" >

<body>

<%
' Get the current date and convert the string to a date object

theDate = Date()
dteDay = Day(dteCurrent)
dteMonth = Month(dteCurrent)
dteYear = Year(dteCurrent)

theCurrentDate = dteYear & dteMonth & dteDay

Randomize
max=75
min=1


empcode = "x"

freebox = "y"

carddata = ""

table =  "<table><thead><th>B</th><th>I</th><th>N</th><th>G</th><th>O</th></thead>"
table = table & newrow & newrow
table = table & "<tr><td>" & num("B") & "</td><td>" & num("I") & "</td><td class='freebox'>" & _
		freebox & "</td><td>" & num("G") & "</td><td>" & num("O") & "</td></tr>"
table = table & newrow & newrow & "</table>"

response.write(table)

'declare SQL statement that will query the database
sSQL = "INSERT INTO OCIW_EmployeeBingo (CompanyCode, EmployeeID, GameNumber, Card) VALUES ('OCIT', '" & empCode & "', '" & theCurrentDate & "', '" & carddata & "')"

response.write("<br /><br />" & sSQL)

%>
</body>
</html>
<%

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

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

Let me know if you have questions about how any part of it works... I didn't put any comments in the code

Back to top
Back to top of the page up there ^
MultiQuote
Reply

#13 Catalyst

Codesmith

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

Posted 20 October 2006 - 03:12 PM

Any progress with this?

Back to top
Back to top of the page up there ^
MultiQuote
Reply

#14 moojoo

Argh!

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

Posted 23 October 2006 - 09:47 AM

Catalyst, on Oct 20 2006, 08:12 PM, said:

Any progress with this?

Been making good headway on it though.

Basically what needs to happen is this:

1. Loop through the employee DB which we have

2. a second loop for each card where it does a loop for each column i.e. B, I, N, G, O so 5 loops in effect?

I don't know if this makes sense lol.Basically then it will insert

B into Col and the numbers into Row keeping them seperate but lined up..
1 b
2 b
3 b
4 b
5 b

I into Col
1 i
2 i
3 i
4 i
5 i

etc etc etc...

<%@ 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>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>

<%
' Gets the row count from the employees field
numEmployees = iCode("Total")
%>

<%
Dim theCurrentDate, strDateStamp

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

<%  
' set the numbers to choose from
dim min, max
Randomize
max=75
min=1

dim empCode

' Insert data into the database set
dim connection
dim sSQL, sConnString

'define the connection string, specify database
'driver and the location of database
sConnString="Driver={SQL Server};" & _
"Server=foo.foo.com; Uid=userid; Pwd=password;"

'create an ADO connection object
Set connection = Server.CreateObject("ADODB.Connection") 

'Open the connection to the database
connection.Open(sConnString)

' Write out the tables based on the number of entries in the employee field
Do Until i = numEmployees
i = i + 1

' Move to next record in the employee field
employeeInfo.MoveNext

' Checks to see if employeeInfo is EOF or not
if employeeInfo.EOF then
Response.Write ""
Else
' Output the tables
dim table

empcode = employeeInfo("EmployeeCode")

'table = "<table><thead><th>B</th><th>I</th><th>N</th><th>G</th><th>O</th></thead>"
'table = table & newrow & newrow
'table = table & "<tr><td>" & num("B") & "</td><td>" & num("I") & "</td><td class='freebox'>" & _
'freebox & "</td><td>" & num("G") & "</td><td>" & num("O") & "</td></tr>"
'table = table & newrow & newrow & "</table>"

'response.write(table)

'declare SQL statement that will query the database
sSQL = "INSERT INTO OCIW_EmployeeBingo (CompanyCode, EmployeeID, GameNumber, Col, Row) VALUES ('OCIT', '" & empCode & "', '" & theCurrentDate & "', '" & col & "', '" & num &"')"

'execute the SQL insert
connection.execute(sSQL)

' Ends the check if employeeInfo is .EOF or not, eliminating the last error table due to no record found
End If
Loop
Response.Write "<ul id='linklist'>"
Response.Write "<li><a href=''>Print Cards</a></li>"
Response.Write "<li><a href=''>Search by Employee</a></li>"
Response.Write "<li><a href=''>View Master Card</a></li>"
Response.Write "<li><a href=''>New Game</a></li>"
Response.Write "</ul>"
Response.Write "<p>" & numEmployees & " Cards have been generated and stored to the database.</p>"
%>
</body>
</html>
<%
function num() 
Do
num = (Int((max-min+1)*Rnd+min))
cell = col & num

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

carddata = carddata & cell
end function

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

<%Response.End%>

<%
' Close it all up
employeeInfo.Close
result.Close
emResult.Close
emCard.Close
Connection.Close
cardNumbers.Close
Set Connection = Nothing
set employeecode = Nothing
%>

This post has been edited by moojoo: 23 October 2006 - 12:29 PM

Its not girly, its web 2.0!

Back to top
Back to top of the page up there ^
MultiQuote
Reply

#15 Catalyst

Codesmith

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

Posted 23 October 2006 - 12:59 PM

Did you check out the code from a couple posts back? It does all of that, not that exact way, but functionally.

Back to top
Back to top of the page up there ^
MultiQuote
Reply

#16 moojoo

Argh!

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

Posted 23 October 2006 - 01:10 PM

Yeah I have been playing around with it. Just trying to get everything sorted. I saw how that one worked and yeah it is mostly what I need, the problem is two columns Row and Col so instead of it just inserting the 24 numbers with the letter I need to insert the letter "Row" and the "Col" would be the number but have it do it 5 times for each letter per employee which is mucking it up for me. Although maybe I am just over complicating it. Iam working with one of your examples to try to break it up. Hopefully I am not as stupid as I feel about now heh.

Loop 1

Row Col

B - Number
B - Number
B - Number
B - Number
B - Number

Loop 2

I - Number
I - Number
I - Number
I - Number
I - Number

This post has been edited by moojoo: 23 October 2006 - 01:42 PM

Its not girly, its web 2.0!

Back to top
Back to top of the page up there ^
MultiQuote
Reply

#17 Catalyst

Codesmith

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

Posted 23 October 2006 - 02:17 PM

I guess i'm still not sure why you need 25 database entries per card. It's much better for you application and database performance and maintaining to go the 1 record route.

Back to top
Back to top of the page up there ^
MultiQuote
Reply

#18 moojoo

Argh!

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

Posted 23 October 2006 - 03:14 PM

I agree however it is set up like that. I prefer just taking the 24 numbers with the letter like you have it and insert that then pull it out later but can't do it that way... =/

Its not girly, its web 2.0!

Back to top
Back to top of the page up there ^
MultiQuote
Reply

#19 Catalyst

Codesmith

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

Posted 23 October 2006 - 03:37 PM

Ah ok. I know what it's like when you don't have full control over something.

In that case I'd put the INSERT statement in the num() function and pass the row and column to the function, rather than just the column like now. That should give you everything you need with just changing 4 or 5 lines of code from the sample. (So you'd change the insertrow to accept a row number as a parameter and pass that along to the num function).

This way you save trying to loop through the table a second time since you can use the original loop where the table is being generated for your INSERTs.

Lemme know if you want to see it in action and I'll update the sample.

Back to top
Back to top of the page up there ^
MultiQuote
Reply

#20 moojoo

Argh!

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

Posted 24 October 2006 - 08:42 AM

I get what you are saying, a snippet would help though. I am a visual kind of person and I usually learn by example. Sad truth lol. But thank you so much for your help. Suprised I haven't been fired yet he.

Its not girly, its web 2.0!