Solved

stored procedure stopped working

Posted on 2016-07-17
4
64 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 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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQl Query to find x consecutive Nbrs in a Table 30 94
SQL Server / Update DB? 22 36
sql, case when & top 1 14 24
SQL Server Configuration Manager WMI Error 11 16
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

791 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