Stone Coast Web Design's Weblog

I really like this example because it illustrates just how powerful client-side and server-side technology used in conjunction can be.  My javascript function, saveSelectedtoDatabase, passes a few parameters to my .aspx page saveSelected.aspx.  The string passed is in the form saveSelected.aspx?UserID=2447&BenchmarkName=Benchmark1&CompanyName=IBM*Ford Motor*AMD&CompID=14575*17411*41522

saveSelected.aspx takes the querystring value passed to it and splits the CompanyName and CompID values by * and then saves each record to a SQL Server database.

<script runat="server">

Sub Page_Load()
If Not IsPostBack Then

Dim intUserID As Integer
Dim strBenchmarkName As String
Dim strCompID As String
Dim strCompanyName As String

'These values are coming from the AJAX function savedSelectedtoDatabase()
intUserID = Request.QueryString("UserID")
strBenchmarkName = Request.QueryString("BenchmarkName")
strCompID = Request.QueryString("CompID")
strCompanyName = Request.QueryString("CompanyName")

'Remove trailing * characters
strCompID = strCompID.Substring(0, strCompID.Length - 1)
strCompanyName = strCompanyName.Substring(0, strCompanyName.Length - 1)

'Place these values into an array
Dim arrCompID As String() = Nothing
arrCompID = strCompID.Split("*")

Dim arrCompanyName As String() = Nothing
arrCompanyName = strCompanyName.Split("*")

'Get the length of one element of the array so we now how many times
'to loop through the database with updates/inserts
Dim i As Integer
Dim myArrayLength As Integer = (arrCompID.Length - 1)

'Insert new record into TBenchmarker and output parameter as BenchmarkID
'TBenchmarker requires UserID and BenchmarkName

Dim strSQLConn As SqlConnection
Dim cmd As SqlCommand
Dim SqlText As String

strSQLConn = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
            
Try    
strSQLConn.Open()
'Insert new record into TBenchmarker
SqlText = "benchmarkerAddRecord"
cmd = New SqlCommand(SqlText, strSQLConn)
cmd.CommandType = CommandType.StoredProcedure

'input parameters for the sproc
cmd.Parameters.AddWithValue("@UserID", intUserID)
cmd.Parameters.AddWithValue("@BenchmarkName", strBenchmarkName)

'Output parameter from sproc benchmarkerAddRecord which is SCOPE_IDENTITY
'Create a SqlParameter object to hold the output parameter value
Dim intNewBenchmarkID As New SqlParameter("@RETURN_VALUE", SqlDbType.Int)

'Set Direction as Output
intNewBenchmarkID.Direction = ParameterDirection.ReturnValue

'Finally, add the parameter to the Command's Parameters collection
cmd.Parameters.Add(intNewBenchmarkID)

'Execute the sproc
cmd.ExecuteScalar()

'Now you can grab the output parameter's value...
Dim intNewBenchmarkIDValue As Integer = Convert.ToInt32(intNewBenchmarkID.Value)

'Insert new record(s) into TBenchmarkerSaved
For i = 0 To myArrayLength
SqlText = "saveBenchmarkValues"
cmd = New SqlCommand(SqlText, strSQLConn)
cmd.CommandType = CommandType.StoredProcedure

'input parameters for the sproc
'intNewBenchmarkIDValue is the value returned from the sproc benchmarkerAddRecord
cmd.Parameters.AddWithValue("@BenchmarkID", intNewBenchmarkIDValue)
cmd.Parameters.AddWithValue("@CompID", arrCompID(i))
cmd.Parameters.AddWithValue("@CompanyName", arrCompanyName(i))
cmd.ExecuteNonQuery()
Next i
            
strSQLConn.Close()

'This value gets returned back to savedSelectedtoDatabase and added
'as an option to ddlSavedBenchmarks dropdown
Response.Write(strBenchmarkName & "#" & intNewBenchmarkIDValue)

Catch ex As Exception
Response.Write("There was an error when saving these companies. Please try again.")
Finally
        
End Try

End If        
End Sub

</script>