problem with stored procedure & jscript

Results 1 to 2 of 2

Thread: problem with stored procedure & jscript

  1. #1
    Join Date
    Dec 1969

    Default problem with stored procedure & jscript

    I&#039;m trying to use a stored procedure to insert data into a linked database; this is my first stored procedure in T-SQL. When I attempt to run the code, though, I get the following error:<BR><BR>Error Type:<BR>Microsoft JScript runtime (0x800A1390)<BR>Cannot assign to &#039;[number]&#039;<BR>/login/registration2.asp, line 34<BR><BR><BR>Here&#039;s the stored procedure, that I created via Query Analyzer:<BR><BR>ALTER PROCEDURE sp_insertInstructor<BR>@insID varchar(16), <BR>@insFirstName varchar(25), <BR>@insLastName varchar(25), <BR>@insFamiliar varchar(20), <BR>@insStreet varchar(40), <BR>@insAddExtra varchar(40), <BR>@insCity varchar(30), <BR>@insState varchar(20), <BR>@insCountry varchar(25), <BR>@insZip varchar(10), <BR>@insTel varchar(20), <BR>@insEmail varchar(50), <BR>@insEstCrsPerYr varchar(38), <BR>@insHowHeard varchar(20), <BR>@insHHOther varchar(50), <BR>@insBio varchar(8000), <BR>@insEBHowLong varchar(38), <BR>@insEBItemsB varchar(38), <BR>@insEBItemsS varchar(38), <BR>@insEBRatings varchar(38), <BR>@insPassword varchar(10)<BR>AS <BR>INSERT INTO Instructor (insID, insFirstName, insLastName, insFamiliar, insStreet, insAddExtra, insCity, insState, insCountry, insZip, insTel, insEmail, insEstCrsPerYr, insHowHeard, insHHOther, insBio, insEBHowLong, insEBItemsB, insEBItemsS, insEBRatings, insPassword) VALUES (@insID, @insFirstName, @insLastName, @insFamiliar, @insStreet, @insAddExtra, @insCity, @insState, @insCountry, @insZip, @insTel, @insEmail, @insEstCrsPerYr, @insHowHeard, @insHHOther, @insBio, @insEBHowLong, @insEBItemsB, @insEBItemsS, @insEBRatings, @insPassword)<BR>RETURN<BR><BR>(I know it seems a bit weird; it&#039;s a test db until the &#039;real&#039; one is delivered by the subcontractor.)<BR><BR><BR>Here&#039;s the call to the stored procedure in my ASP page; I&#039;ve tried replacing the variables with hard-coded values, but it didn&#039;t make a difference:<BR><BR> var conn = Server.CreateObject("ADODB.Connection");<BR> var connString = "Provider=SQLOLEDB.1;User ID=testLogin;Password=testPwd;Persist Security Info=True;Data Source=DSNTest;Initial Catalog=LoginTest";<BR>;<BR> <BR> var SQLQuery =/*@cc_on @*/ sp_insertInstructor (@insID = id, @insFirstName = firstname, @insLastName = lastname, @insFamiliar = familiar, @insStreet = street, @insAddExtra = addextra, @insCity = city, @insState = state, @insCountry = country, @insZip = zip, @insTel = tel, @insEmail = email, @insEstCrsPerYr = estcrsperyr, @insHowHeard = howheard, @insHHOther = hhother, @insBio = bio, @insEBHowLong = ebhowlong, @insEBItemsB = ebitemsb, @insEBItemsS = ebitemss, @insEBRatings = ebratings, @insPassword = password);<BR> var RS = conn.Execute(SQLQuery);<BR><BR>I&#039;ve been pulling all this info about how to call stored procedures in jscript into ASP from various references on the &#039;Net. If I&#039;m way off base on how to do this, I&#039;d be ever so appreciative if someone could set me straight! :)<BR><BR>valkyrie

  2. #2
    Join Date
    Dec 1969

    Default Yeah, you're way off...

    *OTHER* languages do NOT understand SQL. You build a *string* in the other language and then you pass the *string* to the ADO object which in turn passes it along to ODBC which in turn finally hands the *string* off to the database. *NOW* the database can treat that *string* as SQL.<BR><BR>But up to that point, it is nothing but a string to everything else that handles it.<BR><BR>So you must create a *string* in your JS coding:<BR><BR>var SQLQuery = "sp_insertInstructor " <BR> + "( @insID = " + id <BR> + ", @insFirstName = &#039;" + firstname "&#039;"<BR> + ", @insLastName = &#039;" + lastname + "&#039;"<BR> + ", @insFamiliar = &#039;" + familiar + "&#039;"<BR> ... etc. ...<BR> + ")";<BR><BR>Response.Write "DEBUG, SQL is :" + SQLQuery + "&#060;HR&#062;";<BR><BR>conn.Execute( SQLQuery ); // no recordset returned from INSERTS<BR><BR>...<BR><BR>**********<BR><BR>You know, there&#039;s not much value to the SP in this case. You could as easily have used a direct SQL INSERT from the ASP code.<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts