I am having a problem with a VB app

pasky3366
pasky3366 used Ask the Experts™
on
, 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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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
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.

Author

Commented:
Interface and error are attached.
user-interface.PNG
error.PNG
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
mon tues, etc are columns, all columns are nvarchar(255) except ID.
By any chance is the value "lstCourseList.SelectedValue" a null? If so you need to set the parameter to DBNull
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

Author

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.

Author

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
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

Author

Commented:
Changed the code, now when I select the course in course list it does nothing.
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

Author

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
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
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

Author

Commented:
I changed the code, now the error is. Operator '=' is not defined for type 'DataRowView' and 'Nothing'.
Can you paste your modified code with information about which line produces the error (probably a screen shot)? Thanks.
Ronney

Author

Commented:
Got it fixed today, if you have any experience with web services I need help with that? Jim

Author

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

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

Ronney

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial