Date: 10/10/05 (Asp Dot Net)    Keywords: browser, database, sql

Okay this has been bothing me for some time now
whenever i am working with the database (ms-sql 2k)
i keep running into problems with nulls and empty
values, I am passing an empty string into the db
and its fine i pass it through my string cleaner
function and it can't find the value its trying to
enter also with number field unless theres a value
in it the it crashes with the same error my sting
cleanner causes ... what am i doing wrong





### String cleaner function ###
Function Strip_SQL(ByVal strValue as String)
Dim ModStrValue As String
' the values replacing the char values are actually ascii and are rended by the browser here
Dim ModStrValue1 As String = replace(strValue,vbCrLf,"[br/]")
Dim ModStrValue2 As String = replace(ModStrValue1, chr(34),""")
Dim ModStrValue3 As String = replace(ModStrValue2, chr(9),"    ")
Dim ModStrValue4 As String = replace(ModStrValue3, chr(13),"[BR/]")
Dim ModStrValue5 As String = replace(ModStrValue4, chr(39),"'")
Dim ModStrValue6 As String = replace(ModStrValue5, chr(40),"(")
Dim ModStrValue7 As String = replace(ModStrValue6, chr(41),")")
Dim ModStrValue8 As String = replace(ModStrValue7, chr(61),"=")
Dim ModStrValue9 As String = replace(ModStrValue8, chr(64),"@")

Return ModStrValue9

End Function

### how the function is places on the request ###
Dim editorial As String = Strip_SQL(Request.Form("editorial"))

### passing the variable to codebehind ###
Dim EditAdminResort As New Devotion2Motion.AdminComp()
EditAdminResort.EditResort(editorial )


### The function that passes the variable to the query ###
Public Function EditResort(ByVal editorial As String)
' Create Instance of Connection and Command Object
Dim myConnection As New SqlConnection(ConfigurationSettings.AppSettings("strConn"))
Dim myCommand As New SqlDataAdapter("sp_call_Resort_Update", myConnection)
' Mark the Command as a SPROC
myCommand.SelectCommand.CommandType = CommandType.StoredProcedure

' Add Parameters to SPROC
Dim parametereditorial As New SqlParameter("@editorial ", SqlDbType.nVarChar, 4000)
parametereditorial .Value = editorial
myCommand.SelectCommand.Parameters.Add(parametereditorial )


' Create and Fill the DataSet
Dim myDataSet As New DataSet()
myCommand.Fill(myDataSet)

' Return the DataSet
Return myDataSet

End Function


### the error ###
System.Data.SqlClient.SqlException: Procedure 'sp_call_Resort_Update' expects parameter '@editorial ', which was not supplied.

Source: http://www.livejournal.com/community/aspdotnet/44011.html