Update Multiple Rows with a Stored Procedure

Question

Post reply

Update Multiple Rows with a Stored Procedure

C.D Martin

SSC Enthusiast

Points: 197
More actions
November 29, 2005 at 9:32 pm

#364334

I am trying to create a stored procedure in SQL Server 2000 that updates multiple rows in a database table. I have two web pages. On the first page I use a form to submit multiple rows of data to a database table. On the second page I receive that form data and excute a stored procedure. It works perfectly if I submit one row at a time from the form and submit one row of data with the stored procedure. I need to modify the sp so that it can perform multiple record updates. I have figured out that I need some looping logic in the stored procedure like a WHILE statement but I can't quite get it. I know this is likely a simple task to a pure database programmer and I want to thank any of you for your help in advance.
The following is the stored procedure and error messages below:
set
ANSI_NULLS ON
set
QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[updateweek]
@rosterID int,
@memberID VarChar (6),
@wk1 VarChar (1),
@powk1 VarChar (1)

AS
UPDATE dbo.memberPlayWeek
SET powk1 = @powk1
where rosterID = @rosterID
AND memberID = @memberID
AND wk1 = @wk1
Error Type:
ADODB.Command (0x800A0D5D)
Application uses a value of the wrong type for the current operation.
/UPDATEscorecard.asp, line 31
Browser Type:
Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322; .NET CLR 2.0.50727)
Page:
POST 181 bytes to /UPDATEscorecard.asp
POST Data:
powk1=1&rosterID=23&memberID=55-551&wk1=1&powk1=2&rosterID=23&memberID=55-552&wk1=1&powk1=3&rosterID=23&memberID=55-553&wk1=1&powk1=4&rosterID=23&memberID=55-554&wk1=1&Submit=Submit

David A. Long SSCrazy Eights Points: 8421 More actions · Answer 1

Change:

@wk1 VarChar (1), @powk1 VarChar (1)

To:

@wk1 int, @powk1 int

The error tells you that there is a datatype mismatch and from your query string these two values look numeric so are probably int in your table. You should always match the parameters with the fields for datatype.

The stored procedure will not accept an array of parameters if that is what you are trying to accomplish, since your query string shows 3 rows of data.

Provide your code that processes the query string if you are still having troubles.

I suspect that you are also having troubles parsing the query string as it expects unique parameter naming and you have 3 sets of duplicate parameters.

Andy

C.D Martin SSC Enthusiast Points: 197 More actions · Answer 2

Thank you Andy and you are right about parsing out the data.

I have a form that is repeating the data rows. So, instead of passing form values like the following:

rosterID = 23

memberID = 55-551

wk1 = 1

powk1 = 1

and my Sp updates where those values are true. Works great with the above but when I repeat the data rows like the following:

rosterID = 23 memberID = 55-551 wk1 = 1 powk1 = 1

rosterID = 23 memberID = 55-552 wk1 = 1 powk1 = 1

rosterID = 23 memberID = 55-553 wk1 = 1 powk1 = 2

rosterID = 23 memberID = 55-551 wk1 = 1 powk1 = 2

I changed wk1 and powk1 to int but it still fails. The only field I am trying to update is powk1 which would represent the poition a member played on a team during the week one playoff matches. I wrongly assumed that if you repeat the row of data that the stored procedure would automatically pick up the repeated rows. Is there a way to update each row of data that the form is passing to the stored procedure?

C.D Martin SSC Enthusiast Points: 197 More actions · Answer 3

Andy this is the asp page that post the form with the form fields. I receive the values as Request.Form values.

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>

<%

Dim ROSTERLIST

Dim ROSTERLIST_numRows

Set ROSTERLIST = Server.CreateObject("ADODB.Recordset")

ROSTERLIST.ActiveConnection = MM_TwoOneGender_STRING

ROSTERLIST.Source = "SELECT * FROM dbo.memberPlayWeek WHERE rosterID = 23"

ROSTERLIST.CursorType = 0

ROSTERLIST.CursorLocation = 2

ROSTERLIST.LockType = 1

ROSTERLIST.Open()

ROSTERLIST_numRows = 0

%>

<%

Dim Repeat1__numRows

Dim Repeat1__index

Repeat1__numRows = -1

Repeat1__index = 0

ROSTERLIST_numRows = ROSTERLIST_numRows + Repeat1__numRows

%>

<%

Dim MM_paramName

%>

<%

' *** Go To Record and Move To Record: create strings for maintaining URL and Form parameters

Dim MM_keepNone

Dim MM_keepURL

Dim MM_keepForm

Dim MM_keepBoth

Dim MM_removeList

Dim MM_item

Dim MM_nextItem

' create the list of parameters which should not be maintained

MM_removeList = "&index="

If (MM_paramName <> "") Then

MM_removeList = MM_removeList & "&" & MM_paramName & "="

End If

MM_keepURL=""

MM_keepForm=""

MM_keepBoth=""

MM_keepNone=""

' add the URL parameters to the MM_keepURL string

For Each MM_item In Request.QueryString

MM_nextItem = "&" & MM_item & "="

If (InStr(1,MM_removeList,MM_nextItem,1) = 0) Then

MM_keepURL = MM_keepURL & MM_nextItem & Server.URLencode(Request.QueryString(MM_item))

End If

For Each MM_item In Request.Form

MM_nextItem = "&" & MM_item & "="

If (InStr(1,MM_removeList,MM_nextItem,1) = 0) Then

MM_keepForm = MM_keepForm & MM_nextItem & Server.URLencode(Request.Form(MM_item))

End If

MM_keepBoth = MM_keepURL & MM_keepForm

If (MM_keepBoth <> "") Then

MM_keepBoth = Right(MM_keepBoth, Len(MM_keepBoth) - 1)

End If

If (MM_keepURL <> "") Then

MM_keepURL = Right(MM_keepURL, Len(MM_keepURL) - 1)

End If

If (MM_keepForm <> "") Then

MM_keepForm = Right(MM_keepForm, Len(MM_keepForm) - 1)

End If

' a utility function used for adding additional parameters to these strings

Function MM_joinChar(firstItem)

If (firstItem <> "") Then

MM_joinChar = "&"

Else

MM_joinChar = ""

End If

End Function

%>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<head>

<title>Untitled Document</title>

<!--

body,td,th {

font-family: Arial, Helvetica, sans-serif;

font-size: 9pt;

color: 232323;

}

.style1 {font-size: 8pt}

-->

</style></head>

<body>

<tr>

<tr>

<tr>

</tr>

<tr>

<td width="147"><div align="right"><a href="UPDATEscorecardOld.asp?rosterID=<%=(ROSTERLIST.Fields.Item("rosterID").Value)%>&memberID=<%=(ROSTERLIST.Fields.Item("memberID").Value)%>"></a></div></td>

</tr>

<tr>

</tr>

<%

While ((Repeat1__numRows <> 0) AND (NOT ROSTERLIST.EOF))

%>

<td><span class="style1">Enter Weekly Position --> </span></td>

</div></td>

</tr>

<%

Repeat1__index=Repeat1__index+1

Repeat1__numRows=Repeat1__numRows-1

ROSTERLIST.MoveNext()

Wend

%>

<tr>

</tr>

<tr>

</tr>

<tr>

</tr>

<tr>

</div></td>

</tr>

</table>

</form>

</td>

</tr>

</table></td>

</tr>

</table>

</body>

</html>

<%

ROSTERLIST.Close()

Set ROSTERLIST = Nothing

%>

Update Multiple Rows with a Stored Procedure

Cookies on SQLServerCentral