[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

stored procedure stopped working

Posted on 2016-07-17
4
Medium Priority
?
76 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 1000 total points
ID: 41716296
I would only change this
Like @wordvar + '%'
Into
Like @wordvar +N'%'
0
 
LVL 24

Assisted Solution

by:Bitsqueezer
Bitsqueezer earned 1000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

872 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