DataSet Anomaly - Going Insane

[DixieDean]

Please, can anyone help me?

I am a newbie to SQL Server Express 2005 and VWD Express, and I’m currently working on a personal project that consists of a relational database back-end (SQL SE 2005) that holds sports event data (i.e. number of participants, winner and date of event, etc), which I am trying to allow users to access certain information based on selections made in a list box control situated on a web form (VWDE).

I have a stored procedure that accepts a User Code (say ‘1’) and a Named Items string (say ‘01/04/2007 Superbowl,,10/22/2006 Kentucky Derby’) as two input parameters.

The stored procedure retrieves information about these sports and events, and, what I was hoping for, would return the relevant rows, via a sqlDataAdapter and Data Set combination, subsequently feeding a DataGrid (or GridView to use the VWDE terminology) for displaying this information via a web form.

The whole process, including some exception and error trapping, and a couple of data checking and message mechanisms, is shown below (Visual Basic).


Protected Sub ResultSetToGrid()

'Initialize a variable that holds the row count of the 'DataSet
Dim intDSRowCount As Integer = 0

'Initialize a variable that holds the column count of the 'DataSet
Dim intDSColumnCount As Integer = 0

'Initialize User Code Parameter String
Dim strUserCode As String

'Initialize selected Named Items from List Box String
Dim strSelectedEventParameter As String

'Assign '1' as value for User Code Parameter
strUserCode = "'1'"

'Set Named Items List parameter to blank
strSelectedEventParameter = ""

'Initialize counter for enumeration through selected items 'in Named Items List Box
Dim intSelectedItemCount As Integer

'Loop through each selected Named Item in List Box and add 'each one to selected Named Items list parameter
For intSelectedItemCount = 0 To Me.NamedItemsList.Items.Count - 1
If Me.NamedItemsList.Items(intSelectedItemCount).Selected Then
'If the number of selected items exceeds the index of the 'last item
'in the list box, exit the loop
If intSelectedItemCount = Me.NamedItemsList.Items.Count - 1 Then
Exit For
End If
strSelectedEventParameter = strSelectedEventParameter & Me.NamedItemsList.Items(intSelectedItemCount).Value.ToString & ","
End If
Next
'Trim the Named Items List Parameter so that it conforms
'to the appropriate format for the SP Parameter (i.e. ''Named_Item1,Named_Item2')
strSelectedEventParameter = "'" & strSelectedEventParameter.TrimEnd(",") & "'"

' Declare and initalise the Sql connection
Dim sqlConnection1 As New Data.SqlClient.SqlConnection("Data Source=HOMEOFFICE\SQLEXPRESS;Initial Catalog=SportsAlmanac;Integrated Security=True")
' Open the connection.
sqlConnection1.Open()

Try
' Declare and initalise a new SQL Command
Dim cmd As New Data.SqlClient.SqlCommand

With cmd
' Set Command text to stored procedure name
.CommandText = "sp_EvNIInList"
' Set the command type to Stored procedure
.CommandType = Data.CommandType.StoredProcedure
' Add the required SP parameters to the command.
.Parameters.Add("@UserCode", Data.SqlDbType.NChar, 10).Value = strUserCode
.Parameters("@UserCode").Direction = Data.ParameterDirection.Input
.Parameters.Add("@NIString", Data.SqlDbType.NVarChar).Value = strSelectedEventParameter
.Parameters("@NIString").Direction = Data.ParameterDirection.Input
'Set the command connection
.Connection = sqlConnection1
End With

'Declare a new SQL data adapter, passing it the stored 'procedure name, its required parameters
'and the SQL connection variable
Dim sda As New Data.SqlClient.SqlDataAdapter(cmd)

' Declare a new dataset
Dim sqlDataSet As New Data.DataSet

Try

'Fill the dataset with data from adapter
sda.Fill(sqlDataSet, "EventStats")

'Check if there are any rows in data set and if so
'Fill the form's datagrid view with data from the Dataset
'If not, throw up an error message saying zero rows returned
If sqlDataSet.Tables("EventStats").Rows.Count > 0 Then
Me.ResultSetGrid.DataSource = sqlDataSet.Tables("EventStats")
Me.ResultSetGrid.DataBind()
MsgBox("Data Found", MsgBoxStyle.OkOnly, "Data Found")
Else
MsgBox("Zero Rows Returned", MsgBoxStyle.OkOnly, "No Data Found")
End If

intDSRowCount = sqlDataSet.Tables("EventStats").Rows.Count

intDSColumnCount = sqlDataSet.Tables("EventStats").Columns.Count

Catch ex1 As Exception
' Display error message
MsgBox("Unable to retrieve Events Data (Type One). " + ex1.Message)
End Try

Catch ex2 As Exception
' Display error message
MsgBox("Unable to retrieve Events Data (Type Two). " + ex2.Message)
Throw
Finally

' Close the connection
sqlConnection1.Close()

End Try

'Check whether Named Items parameter conforms to correct 'format
Me.EventLabel.Text = strSelectedEventParameter

'Check whether User Code parameter conforms to correct ;format and assigned value is correct
Me.UserLabel.Text = strUserCode

'Count number of rows in dataset
Me.DSRowCountLabel.Text = intDSRowCount

'Count number of columns in dataset
Me.DSColumnCountLabel.Text = intDSColumnCount

End Sub

The problem is that the message box indicating that no data are in the data set (and, therefore, none are being processed through to the GridView) keeps alerting me to this fact.

The strings populating the parameters are fine, as I have checked these using the labels on the web form (i.e. the EventLabel and the UserLabel)

Also, I have added a couple of counting mechanisms (i.e. intDSRowCount and intDSColumnCount) in order to see whether there are any rows or columns existing within the “EventStats” datatable. There are no rows of data coming through, but the correct amount of columns related to the stored procedure (10) is being shown in the DSColumnCountLabel.

This would indicate to me that the dataset and stored procedure are talking to each other, but no rows of data are being returned.

I have also run the SP (called sp_EvNIInList) at source using the strings shown in the relative labels on the web form, and this runs perfectly well.

It has been suggested previously that I should use a sqlDataSource control on the web form and refer to manipulating the related GridView’s .selecting event, but the code above is a small portion of what I need in the end result, as the full caboodle will depend on a number of user selections in various other list box controls and conditional outcomes referring to numerous stored procedures and their inherent input parameters. I just need to understand why the data rows aren’t being sent through to the dataset, and how to remedy the malady.

Could some helpful soul out there please help me with this problem? I’ve been cracking up for days now. I just can’t see what I’m doing wrong.

Many thanks in advance.

DixieDean

 

[jshurst]

First of all don't open and close your sql connection. The DataAdapter will handle that for you. I'll take a closer look at the code when I get a chance - but I'm guessing it's something to do with the parameters.

 

[DixieDean]

Hi jshurst

I've done as advised (i.e. removed the connection open and close elements).

I eagerly await your help with this. It's driving me crazy.

Thanks very much for your assistance.

DixieDean

 

[DixieDean]

Hi jsHurst

Sorry to keep bothering you with this, but I've just ran a similar routine without any parameters and .... yep, you were right. It works fine. It's the darn parameters that are causing the problem.

However, due to the need for these parameters, as any hard coding of search criteria is totally out of the question because of the multitude of petential permutations involved from user selections, I'm aftaid that they are totally necessary, so any advice you could offer on sorting this problem out would be extremely appreciated.

Many, many thanks for your help.

DixieDean

 

[ca8msm]

.Parameters.Add("@UserCode", Data.SqlDbType.NChar, 10).Value = strUserCode

You are setting the Value property incorrectly as you can't just append this onto the end of the Add method. Instead, try creating a Parameter object, set it's properties and then add that Parameter.


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[DixieDean]

Hi ca8msm

Thanks for your response. I'm afraid I'm a real novice at this. I would really appreciate it if you could provide an example of creating a parameter object and setting its properties accordingly, particularly if you could place it in the context of this problem.

Many thanks in advance.

DixieDean

 

[jshurst]

Try something like this

 Dim MyConnection As New SqlConnection("whatever")

 Dim sqlCommand1 As New SqlCommand("cspSelectSearchedTitlesCode", MyConnection)

                With sqlCommand1
                    .CommandType = CommandType.StoredProcedure
                    .Parameters.Add("@typeDescription", SqlDbType.VarChar).Value = typeDescription
                    .Parameters.Add("@codeTitle", SqlDbType.VarChar).Value = SearchString
                End With


                Dim dataAdapter1 As New SqlDataAdapter
                dataAdapter1.SelectCommand = sqlCommand1

                dataAdapter1.Fill(SearchedCodeMasterDataTable)

Not too much different than yours. Also make sure that your data types are correct and step through the code to make sure that the values are being set correctly.

J

 

[DixieDean]

Hi jshurst

Thanks for your last response. Your help is most welcome and appreciated.

I tried applying your suggestion to resolving this problem, but, I’m afraid, to no avail.

However, I have made some assumptions with regards to the code you posted.

1. I assume that the two parameters you refer to in your code refer directly to my particular parameters (i.e. @typeDescription relates to @NIString and @codeTitle to @UserCode, or vise versa).
2. The variables you use to assign the parameters values are equivalent to variables that I use in the following code for assigning values to my parameters (i.e. typeDescription relates to strSelectedEventParameter and seasrchString to strUserCode).
3. There needs to be a new data table object created and assigned to the fill method of the data adapater (i.e. SearchedCodeMasterDataTable in your code and EventStats in mine)
4. There is no need for a dataset in this revised procedure.


Working on this basis, I produced the following procedure:

Protected Sub ResultSetToGridjshurst()
' Initialize a new client code parameter string
Dim strUserCode As String
strUserCode = "'1'"
'Initialise variable to store selected list of Named Items from corresponding control
Dim strSelectedEventParameter As String
strSelectedEventParameter = ""

'Initialize counter for enumeration through selected items in Named Items List Box
Dim intSelectedItemCount As Integer
'Loop through each selected Named Item in List Box and add each one to selected Named Items list parameter
For intSelectedItemCount = 0 To Me.NamedItemsList.Items.Count - 1
If Me.NamedItemsList.Items(intSelectedItemCount).Selected Then
'If the number of selected items exceeds the index of the last item
'in the list box, exit the loop
If intSelectedItemCount = Me.NamedItemsList.Items.Count - 1 Then
Exit For
End If
strSelectedEventParameter = strSelectedEventParameter & Me.NamedItemsList.Items(intSelectedItemCount).Value.ToString & ","
End If
Next
'Trim the Named Items List Parameter so that it conforms
'to the appropriate format for the SP Parameter (i.e. 'Named_Item1,Named_Item2')
strSelectedEventParameter = "'" & strSelectedEventParameter.TrimEnd(",") & "'"

' Declare and initalise the Sql connection
Dim sqlConnection1 As New Data.SqlClient.SqlConnection("Data Source=HOMEOFFICE\SQLEXPRESS;Initial Catalog=SportsAlmanac;Integrated Security=True")

' Declare and initalise a new SQL Command
Dim cmd As New Data.SqlClient.SqlCommand("sp_EvNIInList", sqlConnection1)

With cmd
' Set the command type to Stored procedure
.CommandType = Data.CommandType.StoredProcedure
' Add the required SP parameters to the command.
.Parameters.Add("@UserCode", Data.SqlDbType.NChar, 10).Value = strUserCode
.Parameters.Add("@NIString", Data.SqlDbType.NVarChar, 4000).Value = strSelectedEventParameter
End With

' Declare a new SQL data adapter, passing it the stored procedure name, its required parameters
' and the SQL connection variable
Dim sda As New Data.SqlClient.SqlDataAdapter
sda.SelectCommand = cmd

Dim EventStats As New Data.DataTable

'Fill the data table with data from adapter
sda.Fill(EventStats)
Me.ResultSetGrid.DataSource = EventStats
Me.ResultSetGrid.DataBind()
Me.DSRowCountLabel.Text = EventStats.Rows.Count
Me.DSColumnCountLabel.Text = EventStats.Columns.Count
End Sub

Alas, as I explained to c8msm (above), I can see from the DSColumn and DSRow count labels’ text that I am retrieving the correct amount of columns relative to the underlying SP (i.e. 10), but no rows are returned.

I have checked that the SP is running OK, as I have hard coded the search criterai at source, and this procedure works fine, so it is definitely something to do with the passing of parameter values to the underpinning data source (i.e. the SP)

Forgive me for persisting with this issue, but I would be very grateful if you could take a look at the code I tried and pass on any diagnosis or further advice.

A thousand thank yous for all of you help with this matter.

DixieDean

 

[DixieDean]

Hi c8msm

Thnks a lot for helping me out with this quandary.

I tried to apply your advice to this problem. I used the following code.

Please note that I removed the initial Parameters.Add method and replaced it by creating two parameter objects and assigning their properties as suggested.

Unfortunately, I appear to be getting the same results.

Protected Sub ResultSetToGridc8msm()
'Initialize a variable that holds the row count of the DataSet
Dim intDSRowCount As Integer = 0
'Initialize a variable that holds the column count of the DataSet
Dim intDSColumnCount As Integer = 0

' Initialize a new User code parameter
Dim strUserCode As String
strUserCode = "'1'"

'Initialise variable to store selected list of Named Items from corresponding control
Dim strSelectedEventParameter As String
strSelectedEventParameter = ""

'Initialize counter for enumeration through selected items in Named Items List Box
Dim intSelectedItemCount As Integer
'Loop through each selected Named Item in List Box and add each one to selected Named Items list parameter
For intSelectedItemCount = 0 To Me.NamedItemsList.Items.Count - 1
If Me.NamedItemsList.Items(intSelectedItemCount).Selected Then
'If the number of selected items exceeds the index of the last item
'in the list box, exit the loop
If intSelectedItemCount = Me.NamedItemsList.Items.Count - 1 Then
Exit For
End If
strSelectedEventParameter = strSelectedEventParameter & Me.NamedItemsList.Items(intSelectedItemCount).Value.ToString & ","
End If
Next
'Trim the Named Items List Parameter so that it conforms
'to the appropriate format for the SP Parameter (i.e. 'Named_Item1,Named_Item2')
strSelectedEventParameter = "'" & strSelectedEventParameter.TrimEnd(",") & "'"

Dim UserCodeParam As New Data.SqlClient.SqlParameter
'Set UserCodeParam's properties
With UserCodeParam
.SqlDbType = Data.SqlDbType.NChar
.Size = 10
.ParameterName = "@UserCode"
.Direction = Data.ParameterDirection.Input
.Value = strUserCode
End With

'Initialize Named Items List parameter
Dim NIListParam As New Data.SqlClient.SqlParameter
With NIListParam
.SqlDbType = Data.SqlDbType.NVarChar
.Size = 4000
.ParameterName = "@NIString"
.Direction = Data.ParameterDirection.Input
.Value = strSelectedEventParameter
End With

' Declare and initalise the Sql connection
Dim sqlConnection1 As New Data.SqlClient.SqlConnection("Data Source=HOMEOFFICE\SQLEXPRESS;Initial Catalog=SportsAlamanac;Integrated Security=True")

Try
' Declare and initalise a new SQL Command
Dim cmd As New Data.SqlClient.SqlCommand

With cmd
' Set Command text to stored procedure name
.CommandText = "sp_EvNIInList"
' Set the command type to Stored procedure
.CommandType = Data.CommandType.StoredProcedure
' Add the required SP parameters to the command.
.Parameters.Add(UserCodeParam)
.Parameters.Add(NIListParam)
'Set the command connection
.Connection = sqlConnection1
End With

' Declare a new SQL data adapter, passing it the stored procedure name, its required parameters
' and the SQL connection variable
Dim sda As New Data.SqlClient.SqlDataAdapter(cmd)

' Declare a new dataset
Dim sqlDataSet As New Data.DataSet()

Try
'Fill the dataset with data from adapter
sda.Fill(sqlDataSet, "EventStats")
'Check if there are any rows in data set and if so
'Fill the form's datagrid view with data from the Dataset
'If not, throw up an error message calling the system a bugger
If sqlDataSet.Tables("EventStats").Rows.Count > 0 Then
Me.ResultSetGrid.DataSource = sqlDataSet.Tables("EventStats")
Me.ResultSetGrid.DataBind()
MsgBox("Data Found", MsgBoxStyle.OkOnly, "Data Found")
Else
MsgBox("Zero Rows Returned", MsgBoxStyle.OkOnly, "No Data Found")
End If

intDSRowCount = sqlDataSet.Tables("EventStats").Rows.Count
intDSColumnCount = sqlDataSet.Tables("EventStats").Columns.Count

Catch ex1 As Exception
' Display error message
MsgBox("Unable to retrieve Events Data (Type One). " + ex1.Message)
End Try
Catch ex2 As Exception
' Display error message
MsgBox("Unable to retrieve Events Data (Type Two). " + ex2.Message)
Throw
End Try

'Check whether Named Items parameter conforms to correct format
Me.EventLabel.Text = NIListParam.Value
'Check whether Client Code parameter conforms to correct format and assigned value is correct
Me.UserLabel.Text = UserCodeParam.Value
'Count number of rows in dataset
Me.DSRowCountLabel.Text = intDSRowCount
'Count number of columns in dataset
Me.DSColumnCountLabel.Text = intDSColumnCount
End Sub

I can see from the DSColumn and DSRow count labels’ text that I am retrieving the correct amount of columns relative to the underlying SP (i.e. 10), but no rows are returned.

I have checked that the SP is running OK, as I have hard coded the search criterai at source, and this procedure works fine, so it is definitely something to do with the passing of parameter values to the underpinning data source (i.e. the SP)

I would be most obliged if you could take a look through this (in particular, to have a look at the way I have created the parameters and assigned them their properties) to see if you can uncover what is going wrong.

Once again, many thanks for your help and patience with my general ignorance.

DixieDean

 

[jshurst]

I would suggest posting the SP as well.

 

[DixieDean]

Hi jshurst

Many thanks for looking at this for me.

Here is the SP (sp_EvNIInList) and also a function that I ripped from another web site. This function (called “Split”) receives a string of delimited values and “splits” them into individual search criteria, which are then placed in the IN clause of the SP.

I’ve tried this function out many times and it works perfectly well.

This allows users to select numerous items from a list box, which are then passed to the SP as one parameter (i.e. “@NIString”). For example, should the user select:

[Event Names]

16/05/2004 – FA Cup Final (English Football)
23/06/2004 – 15:30 Lingfield (UK Flat Horse Racing)
07/06/2004 – 14:30 Epsom (UK Flat Horse Racing)

From the list box of events, then the resultant string passed by the @NIString parameter would be:

‘16/05/2004 – FA Cup Final (English Football), 23/06/2004 – 15:30 Lingfield (UK Flat Horse Racing), /06/2004 – 14:30 Epsom (UK Flat Horse Racing)’

As treated by the code:

'Initialise variable to store selected list of Named Items from corresponding control
Dim strSelectedEventParameter As String
strSelectedEventParameter = ""

'Initialize counter for enumeration through selected items in Named Items List Box
Dim intSelectedItemCount As Integer
'Loop through each selected Named Item in List Box and add each one to selected Named Items list parameter
For intSelectedItemCount = 0 To Me.NamedItemsList.Items.Count - 1
If Me.NamedItemsList.Items(intSelectedItemCount).Selected Then
'If the number of selected items exceeds the index of the last item
'in the list box, exit the loop
If intSelectedItemCount = Me.NamedItemsList.Items.Count - 1 Then
Exit For
End If
strSelectedEventParameter = strSelectedEventParameter & Me.NamedItemsList.Items(intSelectedItemCount).Value.ToString & ","
End If
Next
'Trim the Named Items List Parameter so that it conforms
'to the appropriate format for the SP Parameter (i.e. 'Named_Item1,Named_Item2')
strSelectedEventParameter = "'" & strSelectedEventParameter.TrimEnd(",") & "'”
Which would then be processed by the Split function and made suitable for application in the IN clause of the SP.

Here is the SP:

SELECT dbo.Events.Name,
dbo.Events.Type,
dbo.Events.Competition,
dbo.Events.Winner,
sbo.Events.RunnerUp,
dbo.Events.NumberofParticipants,
dbo.Events.Grade,
dbo.Events.Venue,
dbo.Events.Conditions,
dbo. EventSponsor.SponsorName
FROM dbo.Events, dbo.EventSponsor
WHERE dbo.Events.SponsorName = EventSponsor.SponsorName
AND dbo.Events.Name IN (SELECT Item FROM dbo.Split(@NIString,','))
AND dbo.Events.UserCode = @UserCode

….. and here is the Split function:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER Function [dbo].[Split](
@InputText Varchar(max), -- The text to be split into rows
@Delimiter Varchar(10)) -- The delimiter that separates tokens.
-- Can be multiple characters, or empty

RETURNS @Array TABLE (
--TokenID Int PRIMARY KEY IDENTITY(1,1), --Comment out this line if
-- you don't want the
-- identity column
Item Varchar(max))

AS

-----------------------------------------------------------
-- Function Split --
-- • Returns a Varchar rowset from a delimited string --
-----------------------------------------------------------

BEGIN

DECLARE
@Pos Int, -- Start of token or character
@End Int, -- End of token
@TextLength Int, -- Length of input text
@DelimLength Int -- Length of delimiter

-- Len ignores trailing spaces, thus the use of DataLength.
-- Note: if you switch to NVarchar input and output, you'll need to divide by 2.
SET @TextLength = DataLength(@InputText)

-- Exit function if no text is passed in
IF @TextLength = 0 RETURN

SET @Pos = 1
SET @DelimLength = DataLength(@Delimiter)

IF @DelimLength = 0 BEGIN -- Each character in its own row
WHILE @Pos <= @TextLength BEGIN
INSERT @Array (Item) VALUES (SubString(@InputText,@Pos,1))
SET @Pos = @Pos + 1
END
END
ELSE BEGIN
-- Tack on delimiter to 'see' the last token
SET @InputText = @InputText + @Delimiter
-- Find the end character of the first token
SET @End = CharIndex(@Delimiter, @InputText)
WHILE @End > 0 BEGIN
-- End > 0, a delimiter was found: there is a(nother) token
INSERT @Array (Item) VALUES (SubString(@InputText, @Pos, @End - @Pos))
-- Set next search to start after the previous token
SET @Pos = @End + @DelimLength
-- Find the end character of the next token
SET @End = CharIndex(@Delimiter, @InputText, @Pos)
END
END

RETURN

END

I hope this clarifies things. Apologies for being such a dumb limey, but I feel compelled to reiterate that I am no master at this.

I really appreciate any help that you give.

Many thanks

DixieDean

 

[DixieDean]

Hi jshurst

Just to keep you abreast of efforts at my end.

I undertook a number of alternative approaches to try to ascertain if there were any particular anomalies occurring when certain aspects of the procedure were amended.

1. I removed the UserCode parameter from both the SP and VB Procedure to see if this made any difference to the outcome – Result: Zero Rows Returned
2. I removed the NIString parameter from both elements for the same reason – Result: Zero Rows Returned.
3. I hard coded the parameters at the source level (i.e. typed them directly into the SP WHERE Clause in the correct format and with all of the requisite delimiters) and altered the VB Procedure to reflect the removal of parameters, then ran the corresponding VB Procedure – Result: Data Found and GridView populated.
4. I executed all of the relevant SP’s in the SQL interface using hard coded strings to assign values to the SP parameters (for example:

EXECUTE sp_EvNIInList
@UserCode = '1'
@NIString = '01/01/2003 - 10:59 Hackney (UK Greyhound Racing),02/01/2003 - 14:06 Wimbledon (UK Greyhound Racing),03/01/2003 - Austrailia v South Africa (International Cricket)'
GO
)

Result: All expected rows returned in the Results Pane
(The @NIString value conforms to the string produced in the corresponding procedure)

This would lead me to conclude that there is a deficiency between the front-end’s functionality when passing the parameters to the SQL SP, as all of the generated values are consistent with the required formatting for the SP parameters.

When bearing in mind that the correct amount of columns from the SP are returned and their number (10) is displayed in the DSColumnCountLabel on the web form, but zero is displayed in the DSRowCountLabel, it would appear that the only contention rests with the actual retrieval of rows relating to search criteria.

This situation is driving me to destruction, and I am really grateful for the time you are spending on its resolution.

If there are any other ways in which I can contribute, please do not hesitate to let me know.

Again, I am very much obliged to you for your help with this.

DixieDean

 

[DixieDean]

Hi jshusrt and c8msm

Just to let you know I've cracked the problem.

It was parameter-oriented, but nothing to do with how the front-end was compiling them or how they were being sent through to SQL.

At the risk of being branded an imbacile, but with the intention of helping others to avoid this problem, here's what was wrong.

The piece of code assigning values to the UserCode and NUString parameters was adding an apostrophe to the beginning and end of these values (e.g. '1' as UserCode and 'NamedItem1, NamedItem2...'.

Unbeknownst to me, you don't have to add these apostrophes when sending parameters to SQL, as they are added by this application. Ergo, the solution is to omit these from the following code, thus

strSelectedEventParameter = "'" & strSelectedEventParameter.TrimEnd(",") & "'"

should just be

strSelectedEventParameter = strSelectedEventParameter.TrimEnd(",")

That's it!!!

A week and a half I spent on this.

Wouldn't you think there would be some sort of exception message generated (either by SQL or VWD) to let you know that there was an extra apostrophe in the parameter?

Never mind.

Now, I'm going to get drunk.

Thanks for your help with this though.

Regards

DixieDean

 

[jshurst]

SP's accept apostrophe's so that you can search on things like "O'Brien". This is good because you can avoid sql injection attacks because they don't cause the query to get closed early - which is what a sql injection attack does.

Glad you found the solution.

J

 

[jbenson001]

Wouldn't you think there would be some sort of exception message generated (either by SQL or VWD) to let you know that there was an extra apostrophe in the parameter?

Nope, because SQLServer will do implicite convertions between some datatypes. Also in your case, it just tooke the extra "'"s as part of the string.

 

[DixieDean]

Thanks jshurst

Another snippet of knowledge that I shall add to my growing arsenal. Also, thanks for5 your help throughout what has been a torrid time for me.

And thanks to jbenson

I stand corrected....

said the man with the othopaedic shoes.

DixieDean