Solved

stored procedure stopped working

Posted on 2016-07-17
4
59 Views
Last Modified: 2016-07-18
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
0
Comment
Question by:PeterBaileyUk
4 Comments
 
LVL 9

Expert Comment

by:Trideep Patel
ID: 41716288
I checked the query and it is working fine.

What is the value of 'OpTag.Checked' in the function GetMatrixData before SP is called?
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 41716296
I would only change this
Like @wordvar + '%'
Into
Like @wordvar +N'%'
0
 
LVL 24

Assisted Solution

by:Bitsqueezer
Bitsqueezer earned 250 total points
ID: 41716306
Hi,

if you move the mouse over the name of the SP a tooltip should popup showing you the cause of the error. If you are able to select "Modify" the SP still exists so maybe there is a problem with the objects inside the SP. If you have Redgate SQL Prompt installed, use "Show invalid objects", otherwise check the existence of the tables in the desired schema, maybe you've moved them to another schema.
I would also recommend to use aliases in the column list so you only need to change table/schema names in the FROM clause.

Cheers,

Christian
0
 

Author Comment

by:PeterBaileyUk
ID: 41716329
it is strange this morning it all looked and functions fine. ive made the enhancement as recommended by Guy
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now