ASP <---> SQL Communication Problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Other Development Tools

ASP <---> SQL Communication Problem

Author

Topic

mellamokb
Starting Member

39 Posts

Posted - 2006-01-02 : 12:00:26

Hi,

I have a procedure that updates two tables on an SQL server. The procedure receives a long list of parameters from an ASP [vbscript] page and uses that data to update the two tables. Whenever I actually open the page, make changes, and try to save, the only changes made are for the first table. Any changes to any fields on the form related to the second table are not actually updated. However, when I used the SQL Query Analyzer and made up an 'intelligent' list of parameters, the update worked 100%. It made all of the changes I made in the fake parameters to both tables.

That is why I think my problem is ASP-related, not SQL-related, because the update procedure seems to work fine. Here is my ASP code:

---------------- CODE ------------------
spSC_updateContact.CommandText = "spSC_updateContact"
spSC_updateContact.CommandType = 4
spSC_updateContact.CommandTimeout = 30
spSC_updateContact.ActiveConnection = dbConn
spSC_updateContact.Parameters.Append spSC_updateContact.CreateParameter("RETURN_VALUE" ,3 ,&H0004)
spSC_updateContact.Parameters.Append spSC_updateContact.CreateParameter("@contactID",adInteger,&H0001,16,cDbl(contactID)) [ct]
spSC_updateContact.Parameters.Append spSC_updateContact.CreateParameter("@companyID",adInteger,&H0001,16,cDbl(companyID)) [cp]
spSC_updateContact.Parameters.Append spSC_updateContact.CreateParameter("@contact",advarchar,&H0001,100,ucase(contact)) [ct]
spSC_updateContact.Parameters.Append spSC_updateContact.CreateParameter("@company",advarchar,&H0001,150,ucase(company)) [cp]

spSC_updateContact.Parameters.Append spSC_updateContact.CreateParameter("@salutation",advarchar,&H0001,50,salutation) [ct]
spSC_updateContact.Parameters.Append spSC_updateContact.CreateParameter("@fName",advarchar,&H0001,50,fName) [ct]
spSC_updateContact.Parameters.Append spSC_updateContact.CreateParameter("@middleInitial",advarchar,&H0001,50,middleInitial) [ct]
spSC_updateContact.Parameters.Append spSC_updateContact.CreateParameter("@lName",advarchar,&H0001,50,lName) [ct]
spSC_updateContact.Parameters.Append spSC_updateContact.CreateParameter("@title",advarchar,&H0001,50,title) [ct]

spSC_updateContact.Parameters.Append spSC_updateContact.CreateParameter("@billToAddress1",advarchar,&H0001,150,ucase(billToAddress1)) [cp]
spSC_updateContact.Parameters.Append spSC_updateContact.CreateParameter("@billToAddress2",advarchar,&H0001,150,ucase(billToAddress2)) [cp]
spSC_updateContact.Parameters.Append spSC_updateContact.CreateParameter("@billToCity",advarchar,&H0001,50,ucase(billToCity)) [cp]
spSC_updateContact.Parameters.Append spSC_updateContact.CreateParameter("@billToState",advarchar,&H0001,50,ucase(billToState)) [cp]
spSC_updateContact.Parameters.Append spSC_updateContact.CreateParameter("@billToZip",advarchar,&H0001,50,billToZip) [cp]
spSC_updateContact.Parameters.Append spSC_updateContact.CreateParameter("@billToCounty",advarchar,&H0001,50,ucase(billToCounty)) [cp]

spSC_updateContact.Parameters.Append spSC_updateContact.CreateParameter("@phoneExt",advarchar,&H0001,50,phoneExt) [cp]
spSC_updateContact.Parameters.Append spSC_updateContact.CreateParameter("@phoneNum",advarchar,&H0001,50,phoneNum) [cp]
spSC_updateContact.Parameters.Append spSC_updateContact.CreateParameter("@faxNum",advarchar,&H0001,50,faxNum) [cp]
spSC_updateContact.Parameters.Append spSC_updateContact.CreateParameter("@minEmployees",adInteger,&H0001,16,cDbl(minEmployees)) [cp]
spSC_updateContact.Parameters.Append spSC_updateContact.CreateParameter("@maxEmployees",adInteger,&H0001,16,cDbl(maxEmployees)) [cp]
spSC_updateContact.Parameters.Append spSC_updateContact.CreateParameter("@numberOfEmployees",adInteger,&H0001,16,cDbl(numberOfEmployees)) [cp]

spSC_updateContact.Parameters.Append spSC_updateContact.CreateParameter("@repID",adInteger,&H0001,16,cDbl(repID)) [cp]
spSC_updateContact.Parameters.Append spSC_updateContact.CreateParameter("@statusID",adInteger,&H0001,16,cDbl(statusID)) [cp]
spSC_updateContact.Execute
-------------- END CODE ----------------

[ct] - this parameter deals with the contact list table
[cp] - this parameter deals with the company list table

When I run this code, no errors are generated at all! That's why I'm confused. None of my parameters seem to be of the wrong type, and I have all the parameters necessary. Just as a test, I displayed all of the parameters' values on the page, and they were all correct, down to the changes I made on the form. Yet every single time I try to change some of the fields and update, only the first of two tables is updated [the contacts table]. The company table never gets updated.

If anyone might have an idea for something I should check, it would be greatly appreciated! If you would like me to post the SQL code, just let me know.

thanks,
mellamokb

mellamokb
Starting Member

39 Posts

Posted - 2006-01-02 : 12:15:56

I've decided to just split the procedure into two procedures, where each one updates each table - this should work better and it will make for more efficient and simpler maintenance.

mellamokb

<-EDIT->
However, if anyone would has any ideas - I'm still curious as to why it doesn't work as I expected.

mellamokb
Starting Member

39 Posts

Posted - 2006-01-02 : 12:41:19

Sorry about the bother with this question. I figured out my problem. I accidentally assigned the value of the company ID to the contact ID, so the company ID value always remained at 0, which doesn't exist. I've taken care of that, and everything works fine.

mellamokb

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources