cookie data into SQL 7 table..no work

[kbrown2974]

I have a funky problem going on with this one. I have a cookie that has multiple keys that I pull from to put into a SQL 7 table. I am having problems with 3 of the keys. The cookie data is being put into a temp variable then being inserted into the table. When I do a response.write, the data prints fine. Data type with TYPENAME is string, table field type is nvarchar. Some of the code is below. Any suggestions??


tmcounty = request.cookies("customer")("meetcounty")
tmplace = request.cookies("customer")("meetplace")
tmaddr1 = request.cookies("customer")("meetaddr1")
tmaddr2 = request.cookies("customer")("meetaddr2")
tmcity = request.cookies("customer")("meetcity")
tmstate = request.cookies("customer")("meetstate")
tmzip = request.cookies("customer")("meetzip")
tmtime = request.cookies("customer")("meettime")
tmattend = request.cookies("customer")("meetpeople")
retdate = SPLITDATE(tmtime,"D")
rettime = SPLITDATE(tmtime,"T")

cmd = "insert into highibls(inboundno,controlno,ac,number,wkac,wknumber,opid,dispn,reason,date,name,first,last,gender,a ddr1,addr2, " _
& "city,state,zip,zip4,county,ssn,birthday,age,maritalst,enrollsp,spfirst,splast,spssn,spbirthday,spage ,dependents, " _
& "elig4all,elig4medi,e_mail,p2001,f2001,meetarea,meetdate,meettime,meetcounty,meetpeople,speccare,clas sic, " _
& "prefcare,compcare,dirblue,sec65,medgap,secblue,resend,keymu,prefblue,freedom,hctc,sec65cust,heard,se c65plan, " _
& "juliandt1,juliandt2,insurer,planclate,addr165,addr265,city65,state65,zip65,zip465) values (" _
& "'" & t800 & "'," _
& "'" & tcontrol & "'," _
& "'" & thomeac & "'," _
& "'" & thomenumber & "'," _
& "'" & twkac & "'," _
& "'" & twknumber & "', " _
& "'" & topid & "', " _
& "'" & request.form("DISPO") & "', " _
& "'" & request.form("REASON") & "', " _
& "GETDATE()" & ", " _
& "'" & tname & "', " _
& "'" & tfirst & "', " _
& "'" & tlast & "', " _
& "'" & tgender & "', " _
& "'" & taddr1 & "', " _
& "'" & taddr2 & "', " _
& "'" & tcity & "', " _
& "'" & tstate & "', " _
& "'" & tzip5 & "', " _
& "'" & tzip4 & "', " _
& "'" & tcounty & "', " _
& "'" & tssn & "', " _
& "'" & tbday & "', " _
& "'" & tage & "', " _
& "'" & tstatus & "', " _
& "'" & tenroll & "', " _
& "'" & tspfirst & "', " _
& "'" & tsplast & "', " _
& "'" & tspssn & "', " _
& "'" & tspbday & "', " _
& "'" & tspage & "', " _
& "'" & tdep & "', " _
& "'" & tall & "', " _
& "'" & tmed & "', " _
& "'" & temail & "', " _
& "'" & tind & "', " _
& "'" & tfree & "', " _
& "'" & tmplace & "', " _
& "'" & CSTR(ltrim(rtrim(retdate))) & "', " _
& "'" & CSTR(ltrim(rtrim(rettime))) & "', " _
& "'" & tmcounty & "', " _
& "'" & tmattend & "', " _
& "'" & tspeccare & "', " _
& "'" & tclassic & "', " _
& "'" & tprefcare & "', " _
& "'" & tcompcare & "', " _
& "'" & tdirblue & "', " _
& "'" & tsec65 & "', " _
& "'" & tmedgap & "', " _
& "'" & tsecblue & "', " _
& "'" & tresend & "', " _
& "'" & tkeymu & "', " _
& "'" & tprefblue & "', " _
& "'" & tfreedom & "', " _
& "'" & thctc & "', " _
& "'" & tcust65 & "', " _
& "'" & trefer & "', " _
& "'" & tplantype & "', " _
& "'" & tmeda & "', " _
& "'" & tmedb & "', " _
& "'" & tinsure & "', " _
& "'" & tplanc & "', " _
& "'" & taddr165 & "', " _
& "'" & taddr265 & "', " _
& "'" & tcity65 & "', " _
& "'" & tstate65 & "', " _
& "'" & tzip565 & "', " _
& "'" & tzip465 & "')"

Function Splitdate(pvalue,pind)
DIM tval
DIM tlen
DIM colpos
DIM atpos
DIM space1
DIM space2
DIM space3
DIM plus2
DIM temp
DIM temp2
DIM temp3
DIM ampm

If pind = "D" Then
tval = LEFT(pvalue,10)
End If

If pind = "T" Then
colpos = Instr(1,pvalue,":")
atpos = Instr(1,pvalue,"@")
space1 = Instr(1,pvalue," ")
space2 = Instr(space1+1,pvalue," ")
space3 = Instr(space2+1,pvalue," ")
plus2 = space2 + 1
temp = Mid(pvalue,space2+1,space3-space2)
tlen = Len(temp)
ampm = Mid(pvalue,space3+1,1)
temp2 = "0"
temp3 = "00"

If colpos = 0 Then
tval = temp + ampm
Else
tval = Mid(pvalue,space2+1,colpos-plus2)+Mid(pvalue,colpos+1,2)+Mid(pvalue,space3+1,1)
End If
End If
Splitdate = CSTR(ltrim(rtrim(tval)))
End Function

[CardboardHammer]

Tell us what 3 keys you're having a problem with as well as what the problem actually is.

[kbrown2974]

The keys I am having problems with are meettime and meetpeople. Meettime is being split into 2 parts. The data is 01/05/2004 @ 10:00 AM. I am splitting it into 01/05/2004 and 1000A in the splitdate function. This is working fine. The problem is that it will not go into the table. Any suggestions?

[CardboardHammer]

What are the types of the columns in the db into which you are trying to put that data?

[kbrown2974]

all of them are nvarchar

[CardboardHammer]

DOH! That was in the original post... oops...

Show us a Response.Write of cmd as well as the db code that's using cmd.

[kbrown2974]

resulting cmd....

insert into highibls(inboundno,controlno,ac,number,wkac,wknumber,opid,dispn,reason,date,name,first,last,gender,a ddr1,addr2, city,state,zip,zip4,county,ssn,birthday,age,maritalst,enrollsp,spfirst,splast,spssn,spbirthday,spage ,dependents, elig4all,elig4medi,e_mail,p2001,f2001,meetarea,meetdate,meettime,meetcounty,meetpeople,speccare,clas sic, prefcare,compcare,dirblue,sec65,medgap,secblue,resend,keymu,prefblue,freedom,hctc,sec65cust,heard,se c65plan, juliandt1,juliandt2,insurer,planclate,addr165,addr265,city65,state65,zip65,zip465) values ('8007912583','88jkkjhh','888','9999999','444','7888888', 'b5536', '', '', GETDATE(), 'me you', 'me', 'you', 'M', '66 green street', 'apt 66', 'philly', 'PA', '18445', '4887', 'chester', '787845455', '05/05/1905', '88', 'M', 'N', '', '', '', '', '', '3', 'Y', 'Y', 'junk@junk.com', 'Y', 'Y', 'THE VILLAGE LIBRARY', '03/26/2004', '1000A', 'BERKS', '3', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', '', '', '', '', '', '', '', '', '', '', '', '', '')

[CardboardHammer]

What error message do you get when you try the insert? And show the code for the db access.

[kbrown2974]

I dont get an error. The data just doesnt go into the table. Just those 3 fields(meetdate, meettime, meetpeople).

[CardboardHammer]

What happens if you execute the cmd string in Query Analyzer? Does it insert those fields then or does it still fail?

[kbrown2974]

Hmm..thats odd! It works fine in the query analyser. Any ideas why it works there, but the same statement doesnt work in the script?

[CardboardHammer]

Hard code that value for cmd into your page. Does it do the insert correctly then?


An alternative course of action: Using stored procedures instead of query string yields better performance. You'd also be able to do Splitdate within the stored procedure (or if you use it in other queries, you could make Splitdate itself a stored procedure and call it from any stored procedure that needs it) instead of within script. Personally, I only use stored procedures for db access in my applications.

[kbrown2974]

I havent tried using any stored procedures yet. What kinda syntax do ya use for that? I assume the connection has to open to do the call.

[CardboardHammer]

I use ASP.NET now instead of classic ASP, but I'll go troll for some code I worked on before.... be back with that in a bit...

If you haven't written stored procedures at all, SQL Server Books Online is a good resource for info on how to write stored procedures, plus I can provide other examples myself.

[CardboardHammer]

Damn. It seems that what I did in the past, someone else came along and undid. I can't find any examples as done by me, but there are examples in MSDN.

http://support.microsoft.com/default.aspx?scid=kb;en-us;164485

[kbrown2974]

Cool..thanks! I'll throw it in there and see what happens!

[kbrown2974]

I put in the stored procedure and I am STILL having those fields put empty values in the table!!

[CardboardHammer]

"Hard code that value for cmd into your page. Does it do the insert correctly then?"

Did you try that yet?

Another idea: Do you REALLY need to use nvarchar vs. varchar? Note that nvarchar gives you only HALF as many characters as it's size. Is that causing the problem?

[kbrown2974]

I changed them to varchar..that didnt do it either. I have been going over this with another guy I work with..we are both STUMPED!! Curious as to why it would work hard-coded, and in the query analyser and not in the code. Response.write displays the info correctly, variable type comes up as string.

[CardboardHammer]

Where are you doing the Response.Write? Immediately before the insert? And do you do BOTH the Response.Write AND the insert?

This is a table you're trying to insert into, not a view?

Are you SURE that what you're inserting isn't being modified before you go into the db and check?

Show code for the stored procedure, so I can suggest modifications for testing purposes.

[kbrown2974]

The response.write is happening right before the insert. There is an IF, then the insert. It is being inserted into a table. Code is below:

splitdate code
<%
Function Splitdate(pvalue,pind)
DIM tval
DIM tlen
DIM colpos
DIM atpos
DIM space1
DIM space2
DIM space3
DIM plus2
DIM temp
DIM temp2
DIM temp3
DIM ampm

If pind = "D" Then
tval = LEFT(pvalue,10)
End If

If pind = "T" Then
colpos = Instr(1,pvalue,":")
atpos = Instr(1,pvalue,"@")
space1 = Instr(1,pvalue," ")
space2 = Instr(space1+1,pvalue," ")
space3 = Instr(space2+1,pvalue," ")
plus2 = space2 + 1
temp = Mid(pvalue,space2+1,space3-space2)
tlen = Len(temp)
ampm = Mid(pvalue,space3+1,1)
temp2 = "0"
temp3 = "00"

If colpos = 0 Then
tval = temp + ampm
Else
tval = Mid(pvalue,space2+1,colpos-plus2)+Mid(pvalue,colpos+1,2)+Mid(pvalue,space3+1,1)
End If
End If
Splitdate = CSTR(ltrim(rtrim(tval)))
End Function

insert code - the sp is just the original insert above..works fine..its in here somewhere
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open Application("OpenStr")

IF request.form("Dispo") <> "" Then
' Need to put customer into the daily file
set cn = Server.CreateObject("ADODB.Connection")
cn.Open Application("OpenStr")
cmd = "exec sp_highibls_insert " _
& "'" & t800 & "'," _
& "'" & tcontrol & "'," _
& "'" & thomeac & "'," _
& "'" & thomenumber & "'," _
& "'" & twkac & "'," _
& "'" & twknumber & "', " _
& "'" & topid & "', " _
& "'" & request.form("DISPO") & "', " _
& "'" & request.form("REASON") & "', " _
& "'" & tname & "', " _
& "'" & tfirst & "', " _
& "'" & tlast & "', " _
& "'" & tgender & "', " _
& "'" & taddr1 & "', " _
& "'" & taddr2 & "', " _
& "'" & tcity & "', " _
& "'" & tstate & "', " _
& "'" & tzip5 & "', " _
& "'" & tzip4 & "', " _
& "'" & tcounty & "', " _
& "'" & tssn & "', " _
& "'" & tbday & "', " _
& "'" & tage & "', " _
& "'" & tstatus & "', " _
& "'" & tenroll & "', " _
& "'" & tspfirst & "', " _
& "'" & tsplast & "', " _
& "'" & tspssn & "', " _
& "'" & tspbday & "', " _
& "'" & tspage & "', " _
& "'" & tdep & "', " _
& "'" & tall & "', " _
& "'" & tmed & "', " _
& "'" & temail & "', " _
& "'" & tind & "', " _
& "'" & tfree & "', " _
& "'" & tmplace & "', " _
& "'" & retdate & "', " _
& "'" & rettime & "', " _
& "'" & tmcounty & "', " _
& "'" & tmattend & "', " _
& "'" & tspeccare & "', " _
& "'" & tclassic & "', " _
& "'" & tprefcare & "', " _
& "'" & tcompcare & "', " _
& "'" & tdirblue & "', " _
& "'" & tsec65 & "', " _
& "'" & tmedgap & "', " _
& "'" & tsecblue & "', " _
& "'" & tresend & "', " _
& "'" & tkeymu & "', " _
& "'" & tprefblue & "', " _
& "'" & tfreedom & "', " _
& "'" & thctc & "', " _
& "'" & tcust65 & "', " _
& "'" & trefer & "', " _
& "'" & tplantype & "', " _
& "'" & tmeda & "', " _
& "'" & tmedb & "', " _
& "'" & tinsure & "', " _
& "'" & tplanc & "', " _
& "'" & taddr165 & "', " _
& "'" & taddr265 & "', " _
& "'" & tcity65 & "', " _
& "'" & tstate65 & "', " _
& "'" & tzip565 & "', " _
& "'" & tzip465 & "'"
set rs = cn.Execute ( cmd )
response.redirect "main.asp"
END IF

[CardboardHammer]

OK... Humor me a moment...

Create another table in your db with one column having type = varchar and sufficient size.

In your stored procedure, add an insert into this new table consisting of the 3 parameters that aren't showing up after insertion into the other table concatonated, separated by commas. After running your page, check the value inserted into this new table and see if it actually includes the values which are not being inserted into the table that actually matters. If they aren't there, the problem is with the page code. If the values ARE there, your problem is either within the db or the record is getting updated later on the page (or another page) in such a way as to erase the content of those fields.