I am having a problem with a VB app

, I can get my search to work but when I select the search output in my listbox  i get a parameter error even after I declared the command parameters. the error comes in the lstCourseListDisplay function.
Imports System.Data
Imports System.Data.SqlClient

Public Class Form1

    Dim strConnectionString As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\database\Fall2010.mdf;Integrated Security=True;User Instance=True"
    Dim dtDataTable As New DataTable

    Private Sub txtKeyword_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtKeyword.TextChanged
        lstCourseList.Items.Clear()
        Dim conn As New SqlConnection(strConnectionString)
        Dim strCommand As String = "SELECT     catalog_nbr + '  '+ Subject + '  ' + Descr + '  '+ '('+ class_nbr +')' as course, id, subject, class_nbr, descr, catalog_nbr from Fall2010 where subject like  @search"
        Dim command As New SqlCommand(strCommand, conn)
        command.Parameters.AddWithValue("@search", txtKeyword.Text & "%")
        Dim drDataReader As SqlDataReader



        conn.Open()
        drDataReader = command.ExecuteReader()
        lstCourses.Items.Clear()
        Do While drDataReader.Read()
            lstCourseList.Items.Add(drDataReader("Course"))
        Loop
        conn.Close()
    End Sub


    Private Sub lstCourseListdisplay(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lstCourseList.MouseClick, lstCourseList.KeyDown, lstCourseList.KeyUp
        'If lstCourseList.SelectedIndex <> -1 Then
        'Dim "class_NBR" As NullReferenceException

        Dim conn As New SqlConnection(strConnectionString)
        Dim StrCommand As String = "SELECT id , MON + ' ,' + TUES + ' ,' + WED+ ' ,' + THURS + ', ' + FRI as 'Days', MEETING_TIME_START,MEETING_TIME_END, FIRST_NAME + ''+ LAST_NAME as 'Name',CREDITS, GEStatus,CLASS_NBR,MON, TUES, WED, THURS, FRI, FIRST_NAME, LAST_NAME, class_NBR   FROM  Fall2010 Where id=@id"
        Dim command As New SqlCommand(StrCommand, conn)
        command.Parameters.AddWithValue("@id", lstCourseList.SelectedValue)
        'command.Parameters.AddWithValue("@ID", lstCourseList.SelectedIndex.ToString())

        Dim drdatareader As SqlDataReader

        conn.Open()

        drdatareader = command.ExecuteReader()
        If drdatareader.Read Then

            txtDate.Text = drdatareader("Days").ToString
            txtStart.Text = drdatareader("Meeting_Time_Start").ToString
            txtEnd.Text = drdatareader("Meeting_Time_End").ToString
            txtInstructor.Text = drdatareader("Name").ToString
            txtCredits.Text = drdatareader("Credits").ToString
            txtCourseNum.Text = drdatareader("Class_NBR").ToString
            txtEnrollment.Text = drdatareader("Enrl_cap").ToString
            txtGEStatus.Text = drdatareader("GEStatus").ToString


        End If
        conn.Close()
        'End If
    End Sub
End Class

Open in new window

pasky3366Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ronney_leslieCommented:
Are MON, TUES etc columns in the database? Also can you paste an example output that you are expecting from the query or the database schema that you are running this query against?

Ronney
0
ronney_leslieCommented:
Can you also post the error message that you get? It could be a datatype mismatch issue. If id is defined as an integer in the DB then you need to type cast lstCourseList.SelectedValue to int.
0
pasky3366Author Commented:
Interface and error are attached.
user-interface.PNG
error.PNG
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

pasky3366Author Commented:
mon tues, etc are columns, all columns are nvarchar(255) except ID.
0
ronney_leslieCommented:
By any chance is the value "lstCourseList.SelectedValue" a null? If so you need to set the parameter to DBNull
0
ronney_leslieCommented:
In short:
1. Can you make sure that a null is not passed?
2. Type cast the id value so that an integer is sent and not a string.

Let me know if that does not work.

Ronney
0
pasky3366Author Commented:
Ran the select statement in a query, returned the proper response, returned the same values when I cast ID as int set to dbnull and still got same error.
0
pasky3366Author Commented:
id    exp1      days             start time  end time      name       credits   class_NBR  
4      4      N ,N ,N ,Y, N      10:00 AM      12:00 PM      LizbethMatz      0             38059      

query output
0
ronney_leslieCommented:
This is definitely to do with a null value being passed from the list

Can you please replace the line

lstCourseList.SelectedValue

with the following lines?

If lstCourseList.SelectedValue = Nothing Then
            command.Parameters.AddWithValue("@id", DBNull.Value)
        Else
            command.Parameters.AddWithValue("@id", lstCourseList.SelectedValue)
End If

If you still get the error post the new code

Ronney
0
pasky3366Author Commented:
Changed the code, now when I select the course in course list it does nothing.
0
ronney_leslieCommented:
Yes that is because you have not assigned values for the list.

Can you replace the following code

Do While drDataReader.Read()
            lstCourseList.Items.Add(drDataReader("Course"))
        Loop

with the following?


Dim dt As New DataTable
        dt.Load(drdatareader)
lstCourseList.DataSource = dt
lstCourseList.DisplayMember = "Course"
lstCourseList.ValueMember = "id"


Ronney
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pasky3366Author Commented:
The listcourselistfunction works and fills the courselistbox it is when I select a row in the courselistbox to get the data to load in the text boxes it fails.
Imports System.Data
Imports System.Data.SqlClient

Public Class Form1

    Dim strConnectionString As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\database\Fall2010.mdf;Integrated Security=True;User Instance=True"
    Dim dtDataTable As New DataTable

    Private Sub txtKeyword_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtKeyword.TextChanged
        lstCourseList.Items.Clear()
        Dim conn As New SqlConnection(strConnectionString)
        Dim strCommand As String = "SELECT     catalog_nbr + '  '+ Subject + '  ' + Descr + '  '+ '('+ class_nbr +')' as course, id, subject, class_nbr, descr, catalog_nbr from Fall2010 where subject like  @search"
        Dim command As New SqlCommand(strCommand, conn)
        command.Parameters.AddWithValue("@search", txtKeyword.Text & "%")
        Dim drDataReader As SqlDataReader



        conn.Open()
        drDataReader = command.ExecuteReader()
        lstCourses.Items.Clear()
        Do While drDataReader.Read()
            lstCourseList.Items.Add(drDataReader("Course"))
        Loop
        conn.Close()
    End Sub

    Private Sub lstCourseList_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lstCourseList.SelectedIndexChanged
        
    End Sub


    Private Sub btnUpload_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpload.Click
        Dim courses As New Courses.ServiceSoapClient("servicesoap")
        lstMyCourses.Text = courses.UploadCourses(lstCourseList.Text, lstMyCourses.Text)
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        'Dim courses As New Courses.ServiceSoapClient("servicesoap")
        'Dim myWeatherData As New WeatherService.WeatherData
    End Sub


    Private Sub lstCourseListdisplay(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lstCourseList.MouseClick, lstCourseList.KeyDown, lstCourseList.KeyUp
        If lstCourseList.SelectedIndex <> -1 Then


            Dim conn As New SqlConnection(strConnectionString)
            Dim StrCommand As String = "SELECT id , MON + ' ,' + TUES + ' ,' + WED+ ' ,' + THURS + ', ' + FRI as 'Days', MEETING_TIME_START,MEETING_TIME_END, FIRST_NAME + ''+ LAST_NAME as 'Name',CREDITS, GEStatus,CLASS_NBR,MON, TUES, WED, THURS, FRI, FIRST_NAME, LAST_NAME, class_NBR   FROM  Fall2010 Where id=@id"
            Dim command As New SqlCommand(StrCommand, conn)
            If lstCourseList.SelectedValue = Nothing Then
                command.Parameters.AddWithValue("@id", DBNull.Value)
            Else
                command.Parameters.AddWithValue("@id", lstCourseList.SelectedIndex.ToString())
            End If
            command.Parameters.AddWithValue("@ID", lstCourseList.SelectedIndex.ToString())

            Dim drdatareader As SqlDataReader

            conn.Open()

            drdatareader = command.ExecuteReader()
            If drdatareader.Read Then

                txtDate.Text = drdatareader("Days").ToString
                txtStart.Text = drdatareader("Meeting_Time_Start").ToString
                txtEnd.Text = drdatareader("Meeting_Time_End").ToString
                txtInstructor.Text = drdatareader("Name").ToString
                txtCredits.Text = drdatareader("Credits").ToString
                txtCourseNum.Text = drdatareader("Class_NBR").ToString
                txtEnrollment.Text = drdatareader("Enrl_cap").ToString
                txtGEStatus.Text = drdatareader("GEStatus").ToString


            End If
            conn.Close()
        End If
    End Sub
End Class

Open in new window

design.PNG
0
ronney_leslieCommented:
I know what you are talking about. I would appreciate if you try what I have told you before reporting.

Here is the summary of your problem. What you have done is filled the list but only with the display value but the data value is empty and is returning null. If you had done the correction I asked you to do in my previous post you would have fixed your problem already.

Ronney
0
ronney_leslieCommented:
BTW:
Hope you will give me A grade when you close this question since I had to spent more time and effort on your question taking the extra step to create the database and vb project from scratch to recreate and fix your error.

Ronney
0
pasky3366Author Commented:
I changed the code, now the error is. Operator '=' is not defined for type 'DataRowView' and 'Nothing'.
0
ronney_leslieCommented:
Can you paste your modified code with information about which line produces the error (probably a screen shot)? Thanks.
Ronney
0
pasky3366Author Commented:
Got it fixed today, if you have any experience with web services I need help with that? Jim
0
pasky3366Author Commented:

Imports System.Data
Imports System.Data.SqlClient


Public Class Form1

    Dim strConnectionString As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\database\Fall2010.mdf;Integrated Security=True;User Instance=True"
    Dim dtDataTable As New DataTable

    Private Sub txtKeyword_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtKeyword.TextChanged

        Dim conn As New SqlConnection(strConnectionString)
        Dim strCommand As String = "SELECT        CATALOG_NBR + '  ' + SUBJECT + '  ' + DESCR + '  ' + '(' + CLASS_NBR + ')' AS course, ID, SUBJECT, CLASS_NBR, DESCR, LAST_NAME, GESTATUS, CATALOG_NBR, Mon, Tues, Thurs, Fri FROM Fall2010 WHERE SUBJECT LIKE @subject OR LAST_NAME LIKE @last_name OR GESTATUS LIKE @GEstatus"
        Dim command As New SqlCommand(strCommand, conn)
        command.Parameters.AddWithValue("@subject", "%" & txtKeyword.Text & "%")
        command.Parameters.AddWithValue("@last_name", "%" & txtKeyword.Text & "%")
        command.Parameters.AddWithValue("@GEstatus", "%" & txtKeyword.Text & "%")
        Dim drDataReader As SqlDataReader

        conn.Open()

        drDataReader = command.ExecuteReader
        dtDataTable.Clear()
        dtDataTable.Load(drDataReader)
        lstCourseList.DataSource = dtDataTable
        lstCourseList.DisplayMember = "Course"
        lstCourseList.ValueMember = "id"

        conn.Close()


        conn.Close()
    End Sub

    Private Sub lstCourseList_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lstCourseList.SelectedIndexChanged
        If lstCourseList.SelectedIndex = -1 Then
            lstCourseList.SelectedIndex = 0

        End If

        Dim conn As New SqlConnection(strConnectionString)
        Dim StrCommand As String = "SELECT id , MON + ' ,' + TUES + ' ,' + WED+ ' ,' + THURS + ', ' + FRI as 'Days', MEETING_TIME_START,MEETING_TIME_END, FIRST_NAME + ''+ LAST_NAME as 'Name',CREDITS, GEStatus,CLASS_NBR,MON, TUES, WED, THURS, FRI, enrl_Cap+ '   Seats Taken:  '+ enrl_Tot as Enrollment, FIRST_NAME, LAST_NAME, class_NBR   FROM  Fall2010 Where id=@id"
        Dim command As New SqlCommand(StrCommand, conn)

        command.Parameters.AddWithValue("@id", lstCourseList.SelectedIndex.ToString())
        Dim drdatareader As SqlDataReader
        Dim Days As String = ""

        conn.Open()
        drdatareader = command.ExecuteReader()
        If drdatareader.Read Then


            If drdatareader("Mon") = "Y" Then
                Days = "Mon"
            End If
            If drdatareader("Tues") = "Y" Then
                Days = Days + "Tues"
            End If
            If drdatareader("Wed") = "Y" Then
                Days = Days + "Wed"
            End If
            If drdatareader("Thurs") = "Y" Then
                Days = Days + "Thurs"
            End If
            If drdatareader("Fri") = "Y" Then
                Days = Days + "Fri"
            End If


            txtDate.Text = Days
            txtStart.Text = drdatareader("Meeting_Time_Start").ToString
            txtEnd.Text = drdatareader("Meeting_Time_End").ToString
            txtInstructor.Text = drdatareader("Name").ToString
            txtCredits.Text = drdatareader("Credits").ToString
            txtCourseNum.Text = drdatareader("Class_NBR").ToString
            txtEnrollment.Text = "Total:" & drdatareader("Enrollment")

            txtGEStatus.Text = drdatareader("GEStatus").ToString


        End If
        conn.Close()

    End Sub


    Private Sub btnUpload_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpload.Click
        Dim rs As String

        Dim servicecourses As New Courses.ServiceSoapClient("ServiceSoap")
        rs = servicecourses.UploadCourses(txtStudentName.Text, lstMyCourses.SelectedItem)
        lblmessage.Text = rs

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim rs As String()

        Dim servicecourses As New Courses.ServiceSoapClient("ServiceSoap")
        rs = servicecourses.DownloadCourses(txtStudentName.Text)
        For i = 1 To rs.Length
            'lstCourseList.Items.Add(rs(i - 1))
            Dim a As String
            a = rs(i - 1)
            lstCourses.Items.Add(a)
        Next
    End Sub

    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click

        Dim conn As New SqlConnection(strConnectionString)
        Dim strCommand As String = "SELECt CATALOG_NBR + '  ' + SUBJECT + '  ' + DESCR + '  ' + '(' + CLASS_NBR + ')' + ''+ Last_name + ', ' + First_name AS course from fall2010 where id = @id"
        Dim command As New SqlCommand(strCommand, conn)
        command.Parameters.AddWithValue("@id", lstCourseList.SelectedIndex.ToString())
        Dim drdatareader As SqlDataReader

        conn.Open()
        dtDataTable.Clear()
        drdatareader = command.ExecuteReader
        Do While drdatareader.Read()

            lstMyCourses.Items.Add(drdatareader("Course"))
        Loop
        conn.Close()

    End Sub

    Private Sub lstCourses_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lstCourses.SelectedIndexChanged

    End Sub

    Private Sub lstMyCourses_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lstMyCourses.SelectedIndexChanged
        If lstMyCourses.SelectedIndex = -1 Then
            lstMyCourses.SelectedIndex = 0

        End If
    End Sub
End Class

Open in new window

0
ronney_leslieCommented:
Great that your code is working. Let me know what type of help you need in webservices.

Ronney
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.