Solved

stored procedure stopped working

Posted on 2016-07-17
4
71 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 143

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

695 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