vbscript to conditionally execute sql query

Hi All

I've got a couple of queries, SQLCheck, Insert and update.

if the following condition is met i(if rs("parentfk") = 0 then) n SQLCheck, then the other two should execute, otherwise an error message to display.


please tell me how this is possible, i'm a vb asp newbie.

Thanks in advance.
Dim strSQLcheck
	'do checks
	strSQLcheck = "SELECT * FROM tblStudent WHERE StudentNO IN ( " & studentfk & ");"
    'insert my self into parent table and update my childs field with my id
	strSQL_Update = "UPDATE tblStudent SET parentfk = '"& studentfk &"' WHERE StudentNO IN ( " & studentfk & ");"
    strSQL_Insert = "INSERT INTO tblParent ( FName, SurName, Email, ParentNo, Password, ChildrenID, Hnum, Street, Area, City, Postcode, Phone, Mobile, EntryTime)" & _
                   " VALUES ('" & strName & "','" & strLastName & "','" & strEmail & "','" & strUserName & "','" & strPassword & "','" & studentfk & "','" & strHnum & "','" & strStreet & "','" & strArea & "','" & strCity & "','" & strPC & "','" & strPhone & "','" & strMobile & "','" & strDate & "');"
    set rs = Server.CreateObject("ADODB.Recordset")
	set rs8 = Server.CreateObject("ADODB.Recordset")
	set rs9 = Server.CreateObject("ADODB.Recordset")
	rs.Open strSQLcheck, conn
	do while not rs.EOF
       if rs("parentfk") = 0 then
	   	  rs8.Open strSQL_Insert, conn   
		  rs8.Close
		  rs9.Open strSQL_Update, conn
		  rs9.Close
	   end if
	rs.MoveNext
    loop
    rs.Close

Open in new window

khuz01Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
ee_rleeConnect With a Mentor Commented:
you could just set the update query inside the loop
  dim txt,a,instud
  dim i
  dim strSQL_Update
  txt= studentfk
  insertTxt = Replace(txt,"'","")
  a=Split(txt,",")
  b=Split(insertTxt,",")
  'Do Until i = ubound(a)+1
  'response.write a(i)&"<br>"
  'i = i + 1
  'Loop
        'do checks to see if child already has a parent
        strSQLcheck = "SELECT * FROM tblStudent WHERE StudentNO IN (" & studentfk & ");"
 
    strSQL_Insert = "INSERT INTO tblParent ( FName, SurName, Email, ParentNo, Password, ChildrenID, Hnum, Street, Area, City, Postcode, Phone, Mobile, EntryTime)" & _
                   " VALUES ('" & strName & "','" & strLastName & "','" & strEmail & "','" & strUserName & "','" & strPassword & "','" & insertTxt & "','" & strHnum & "','" & strStreet & "','" & strArea & "','" & strCity & "','" & strPC & "','" & strPhone & "','" & strMobile & "','" & strDate & "');"
    set rs = Server.CreateObject("ADODB.Recordset")
        set rs8 = Server.CreateObject("ADODB.Recordset")
        set rs9 = Server.CreateObject("ADODB.Recordset")
        rs.Open strSQLcheck, conn
 
        do while not rs.EOF
         If IsNull(rs("parentfk")) then
         response.write "parent fk is null"
         rs8.Open strSQL_Insert, conn   
     ''rs8.Close   
	for i=0 to ubound(a)
          response.write a(i)&"<br>"
          strSQL_Update = "UPDATE tblStudent SET parentfk = '" & strUserName & "' WHERE StudentNO = '" & b(i) & "';"
          rs9.Open strSQL_Update, conn
	next
     ''rs9.Close     
         else
         inserterror = True
         strValid = strValid & "<p align=""left""><font face=""Verdana"" size=""4"" color=""#FF00000""><b>" & _
           "Registration is not ok..!!</b></font></p>" & vbCrLf & _
              "<ul><font face=""Verdana"" size=""2"" color=""#000080"">"
         end if
        rs.MoveNext
    loop
    rs.Close

Open in new window

0
 
Malik1947Commented:
try this code.
to replace your strSQLCheck
strSQLcheck = "SELECT parentfk FROM tblStudent WHERE StudentNO = " & studentfk & ";"

Open in new window

0
 
Malik1947Connect With a Mentor Commented:
There is no need to pull all the information from the table if all you want to check for is simply one field.

Also, I am assuming that the parentfk field contains a 0 and not null.

the code would be different if you were trying to find Null values and replace them.

instead of doing the rs("parentfk") = 0

you would do



If IsNull(rs("parentfk")) = true then
     rs8.Open strSQL_Insert, conn   
     rs8.Close
     rs9.Open strSQL_Update, conn
     rs9.Close   
end if

Open in new window

0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
khuz01Author Commented:
Hi Malik

thanks for your reply.

firstly, i'm using IN instead of = because StudentNO can be more than one value ie: 's2s030694','21041982', when i execute that query separately, it works fine.

i need help with the other syntax because i'm new to vbscript. here is pseudocode, please can i have the correct code to do this in vb.asp:



make connection to database
execute SQLcheck query
 
if parentfk is null
 
execute SQLinsert
and then execute SQLupdate

Open in new window

0
 
khuz01Author Commented:
currently i'm getting the following error, which is

rs.Open strSQLcheck, conn

error:
ADODB.Recordset error '800a0bb9'

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

/pts/addparent2.asp, line 163
0
 
Malik1947Commented:
the code would be the code i posted earlier



IsNull checks to see if the value is null and executes the code according to whether it is set to true or false based on logic.

0
 
Malik1947Commented:
the error is not in the logic it is in the code you're trying to execute.


try to run the strSQLCheck code alone and see if executes.
0
 
khuz01Author Commented:
when i execute it at the bottom of the page, it works fine. but when i put it within the if statements it fails.

i'm going to send you the the form file and the form action file, can you see if you can get it to work?

i'll increase points to maximum and please note that each file is .asp
psignup.txt
addparent2.txt
0
 
khuz01Author Commented:
OKAY

i've now made a bit of progress by passing the correct connection string but i now have a new problem with the conditional query.

i've pasted lines 140 to 161, and here is the error message:

parent fk is null
Microsoft OLE DB Provider for SQL Server error '80040e14'
Line 1: Incorrect syntax near 's2s030694'.
/pts/addparent2.asp, line 154

154 is when the update query is executed and i'm passing the studentfk into the query as 's2s030694','21041982'

so you can see where it is breaking.

if i was doing this in coldfusion, i'd change the list of two into an array, and then it would be easy to insert and update the data.

please help me achieve this in asp.

Thanks in advance.
  if blnValid = True then
	'do checks
	strSQLcheck = "SELECT * FROM tblStudent WHERE StudentNO IN ( " & studentfk & ");"
    'insert my self into parent table and update my childs field with my id
	strSQL_Update = "UPDATE tblStudent SET parentfk = '"& studentfk &"' WHERE StudentNO IN ( " & studentfk & ");"
    strSQL_Insert = "INSERT INTO tblParent ( FName, SurName, Email, ParentNo, Password, ChildrenID, Hnum, Street, Area, City, Postcode, Phone, Mobile, EntryTime)" & _
                   " VALUES ('" & strName & "','" & strLastName & "','" & strEmail & "','" & strUserName & "','" & strPassword & "','" & studentfk & "','" & strHnum & "','" & strStreet & "','" & strArea & "','" & strCity & "','" & strPC & "','" & strPhone & "','" & strMobile & "','" & strDate & "');"
    set rs = Server.CreateObject("ADODB.Recordset")
	set rs8 = Server.CreateObject("ADODB.Recordset")
	set rs9 = Server.CreateObject("ADODB.Recordset")
	rs.Open strSQLcheck, conn
	do while not rs.EOF
	 If IsNull(rs("parentfk")) = true then
	 response.write "parent fk is null"
     rs9.Open strSQL_Update, conn
     rs9.Close     
	 rs8.Open strSQL_Insert, conn   
     rs8.Close   
	 end if
	rs.MoveNext
    loop
    rs.Close

Open in new window

0
 
Malik1947Connect With a Mentor Commented:
remove the "=true" from the script and try to run it

like this

If IsNull(rs("parentfk")) then
     rs8.Open strSQL_Insert, conn   
     rs8.Close
     rs9.Open strSQL_Update, conn
     rs9.Close   
end if

Open in new window

0
 
khuz01Author Commented:
just tried that but i get the same error:

Microsoft OLE DB Provider for SQL Server error '80040e14'
Line 1: Incorrect syntax near 's2s030694'.
/pts/addparent2.asp, line 154

this shows that the error is on the update query, after "parent fk is null" is printed.

so the problem is to do with how i pass in the studentfk's to the update query. how can i improve that?
0
 
khuz01Author Commented:
also it is worth noting that i get the same error no matter if i execute insert first or last.
0
 
ee_rleeCommented:
hi

try if this works.
strSQL_Update = "UPDATE tblStudent SET parentfk = "& studentfk &" WHERE StudentNO IN ( " & studentfk & ");"

btw, what is the value of studentfk? and what type is parentfk & StudentN0?


0
 
khuz01Author Commented:
studentfk is in this format:
's2s030694','21041982'

both parentfk and studentno are varchars.

the strange thing is that it breaks on the same line no matter if it's executing update or insert.

if i was doing this in coldfusion, i'd change the list of two into an array, and then it would be easy to insert and update the data.

is that where the problem lies?
0
 
ee_rleeCommented:
do you mean that studentfk= 's2s030694','21041982' ?

if yes, then your query will look like this

"UPDATE tblStudent SET parentfk = ''s2s030694','21041982'' WHERE StudentNO IN ('s2s030694','21041982');"

which will have an invalid value set to parentfk.
what is the value that you want to set the parentfk to?
0
 
khuz01Author Commented:
let me give you some background info. i'm building a simple app for a tutoring service that lets student, parents and teachers to signup and login to update/insert or view student progress.

one parent can have more than one child at the center. this is the functionality i'm trying to achieve.

so i do a check to see if the student already has a parent assigned to them or not, if not, i want to update the student table with the childid, and insert the form data into the parent table.

yes, currently studentfk is set to 's2s030694','21041982' this is the format the user is asked to enter it in.  maybe i need to change the way this is done, do you have any suggestions?



0
 
ee_rleeCommented:
can you set the studentfk to s2s030694,21041982 (without quotes)?

then change
WHERE StudentNO IN ( " & studentfk & ");"

to
WHERE "," + StudentNO + "," LIKE ( '%," & studentfk & ",%');"
0
 
ee_rleeCommented:
or you can use charindex in the where

"WHERE CHARINDEX(StudentNO,'" & studentfk & "') > 0"
0
 
khuz01Author Commented:
ee_rlee i tried WHERE "," + StudentNO + "," LIKE ( '%," & studentfk & ",%');"

without the commas ofcourse and am able to insert and update ONE parent and assign that parent with a student.

the problem is that in my original post i was using studentno IN ('sdadasasd','asdasdaa') because a PARENT CAN HAVE MORE THAN ONE CHILD so i was trying to get it to work with the above format.

how would i achieve what i stated above with your code? ( i thought maybe by turning list into array and looping until all students are updated) - what would be the syntax for this?

your help much appreciated.
0
 
khuz01Author Commented:
so in essence my question is how can i use s2s2001,s2s2002 put it into an array so i can access s2s2001 as my first element, and s2s2002 as my second element etc.

this would then loop something like this:

For Each present In myArray
 UPDATE student table WITH current value
Next
0
 
ee_rleeCommented:
made a mistake

should be

"WHERE " & studentfk & " LIKE '%' + StudentNO + '%'"

this should work like your original IN.
0
 
khuz01Author Commented:
that may work if there are two comma delimeted studentfk's but what if there are more?

is it not best to change the comma delimeted list into an array and then loop through that updating on each iteration?
0
 
ee_rleeCommented:
it will still work even when there are more commas in studentfk. that will depend on your app. if you just want to update all the records where studentno is in it, then you do not need to loop. just execute the update query and it will replace all values that satisfy  the where condition.
0
 
khuz01Author Commented:
i've tried that but it only works when i give ONE studentfk, when i comma separate more than one, nothing updates or inserts into their table, this is the query:

strSQL_Update = "UPDATE tblStudent SET parentfk = '"& strUserName & "'WHERE StudentNO LIKE '%"&  studentfk & "%';"

how can i define studentfk for it to update more than one record???? what string manipulation in VB can i do to separate the values and update?
0
 
ee_rleeConnect With a Mentor Commented:
did you also try my prev post (20847809)?

i made a mistake and it should have been

strSQL_Update = "UPDATE tblStudent SET parentfk = '" & strUserName & "' WHERE " & studentfk & " LIKE '%' + StudentNO + '%';"

pls try again

if you want to separate the values using VB, you could use the split function.

i.e.
dim t() as string
t=split(studentfk,",")

but you should be able to update all the records using just a single query
0
 
khuz01Author Commented:
thanks for the split function, that has sorted it out almost completely. the code i have now is below but how can i loop through a and call the appropriate update query?

we're so close, i can smell the end!!

  dim txt,a,instud
  dim i
  txt= studentfk
  insertTxt = Replace(txt,"'","")
  a=Split(txt,",")
  b=Split(insertTxt,",")
  'Do Until i = ubound(a)+1
  'response.write a(i)&"<br>"
  'i = i + 1
  'Loop
	'do checks to see if child already has a parent
	strSQLcheck = "SELECT * FROM tblStudent WHERE StudentNO IN (" & studentfk & ");"
 
    'insert my self into parent table and update my childs field with my id
	strSQL_Update1 = "UPDATE tblStudent SET parentfk = '" & strUserName & "' WHERE StudentNO = '" & b(0) & "';"
	strSQL_Update2 = "UPDATE tblStudent SET parentfk = '" & strUserName & "' WHERE StudentNO = '" & b(1) & "';"
	strSQL_Update3 = "UPDATE tblStudent SET parentfk = '" & strUserName & "' WHERE StudentNO = '" & b(2) & "';"
	strSQL_Update4 = "UPDATE tblStudent SET parentfk = '" & strUserName & "' WHERE StudentNO = '" & b(3) & "';"
	strSQL_Update5 = "UPDATE tblStudent SET parentfk = '" & strUserName & "' WHERE StudentNO = '" & b(4) & "';"
	strSQL_Update6 = "UPDATE tblStudent SET parentfk = '" & strUserName & "' WHERE StudentNO = '" & b(5) & "';"
	strSQL_Update7 = "UPDATE tblStudent SET parentfk = '" & strUserName & "' WHERE StudentNO = '" & b(6) & "';"
	strSQL_Update8 = "UPDATE tblStudent SET parentfk = '" & strUserName & "' WHERE StudentNO = '" & b(7) & "';"
    strSQL_Insert = "INSERT INTO tblParent ( FName, SurName, Email, ParentNo, Password, ChildrenID, Hnum, Street, Area, City, Postcode, Phone, Mobile, EntryTime)" & _
                   " VALUES ('" & strName & "','" & strLastName & "','" & strEmail & "','" & strUserName & "','" & strPassword & "','" & insertTxt & "','" & strHnum & "','" & strStreet & "','" & strArea & "','" & strCity & "','" & strPC & "','" & strPhone & "','" & strMobile & "','" & strDate & "');"
    set rs = Server.CreateObject("ADODB.Recordset")
	set rs8 = Server.CreateObject("ADODB.Recordset")
	set rs9 = Server.CreateObject("ADODB.Recordset")
	rs.Open strSQLcheck, conn
 
	do while not rs.EOF
	 If IsNull(rs("parentfk")) then
	 response.write "parent fk is null"
	 rs8.Open strSQL_Insert, conn   
     ''rs8.Close   
	 Do Until i = ubound(a)+1
	 response.write a(i)&"<br>"
     rs9.Open strSQL_Update&i&, conn
	 i = i + 1
     Loop
     ''rs9.Close     
	 else
	 inserterror = True
	 strValid = strValid & "<p align=""left""><font face=""Verdana"" size=""4"" color=""#FF00000""><b>" & _
           "Registration is not ok..!!</b></font></p>" & vbCrLf & _
              "<ul><font face=""Verdana"" size=""2"" color=""#000080"">"
	 end if
	rs.MoveNext
    loop
    rs.Close

Open in new window

0
 
khuz01Author Commented:
Thanks alot guys. i distributed the points with regards to how close they were to my solution.

Thanks again.
0
All Courses

From novice to tech pro — start learning today.