Avatar of PeterBaileyUk
PeterBaileyUk
 asked on

stored procedure stopped working

I have created the sp but something is going on with the schema i think as its underlining red. i selected modify here so you could see the sp code. I manually ran select query to check rows exist

use Dictionary
select [word]
from [TblWords]
where [Word] = 'bioflex'

but when i call the visual basic code the object is returning no data the data is from a combo and the message box shows that looks ok but my call might not be:
    Private Sub CBCWSelectedItems_SelectedIndexChanged(sender As Object, e As EventArgs) Handles CBCWSelectedItems.SelectedIndexChanged
        Dim selectedIndex As Integer
        selectedIndex = CBCWSelectedItems.SelectedIndex
        Dim selectedItem As Object
        selectedItem = CBCWSelectedItems.SelectedItem

        MessageBox.Show("Selected Item Text: " & selectedItem.ToString() & Microsoft.VisualBasic.Constants.vbCrLf &
                        "Index: " & selectedIndex.ToString())
        ListMatrix.DataSource = GetMatrixData(selectedItem.ToString()).AsEnumerable().Select(Function(r) r.Field(Of String)("Word")).ToList()

        ListMatrix.Refresh()
    End Sub

Open in new window



 Public Function GetMatrixData(ByVal StrWordIn As String) As DataTable
        Dim StrProcName As String
        Dim StrWord As String
        StrWord = StrWordIn

        dtMatrix = New DataTable
        Dim connectionString As String = ConfigurationManager.ConnectionStrings("Dictionary").ConnectionString

        If OpTag.Checked = True Then
            StrProcName = "usp_GetMatrix"


            Using connMatrix As New SqlConnection(connectionString)

                Using cmdMatrix As New SqlCommand(StrProcName, connMatrix)
                    cmdMatrix.CommandType = CommandType.StoredProcedure
                    connMatrix.Open()
                    cmdMatrix.Parameters.AddWithValue("@WordVar", StrWord)
                    Dim readerMatrix As SqlDataReader = cmdMatrix.ExecuteReader()

                    dtMatrix.Load(readerMatrix)

                End Using
            End Using
            Return dtMatrix

        Else

        End If
    End Function

Open in new window

ee3.JPG
Visual Basic.NETMicrosoft SQL Server

Avatar of undefined
Last Comment
PeterBaileyUk

8/22/2022 - Mon
Trideep Patel

I checked the query and it is working fine.

What is the value of 'OpTag.Checked' in the function GetMatrixData before SP is called?
ASKER CERTIFIED SOLUTION
Guy Hengel [angelIII / a3]

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Bitsqueezer

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
PeterBaileyUk

ASKER
it is strange this morning it all looked and functions fine. ive made the enhancement as recommended by Guy
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck