• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 897
  • Last Modified:

How do you debug an update statement with an ExecuteNonQuery() command

I am updating an sql table with a .csv file.  The program runs but none of the fields ever get updated.  I think it is having a problem with finding the correct studentId.  How can I trap and debug this problem.  Here is my current code.

Thanks
Kenny





Imports System.Data.SqlClient
Imports System.IO

Module Module1

    Sub Main()
        Dim objStreamReader As StreamReader
        Dim strLine As String
        Dim myConnString As String
        Dim lname As String
        Dim fname As String
        Dim studentId As String
        Dim teacherIdPd1 As String
        Dim teacherIdPd2 As String
        Dim teacherIdPd3 As String
        Dim teacherIdPd4 As String
        Dim teacherIdPd5 As String
        Dim teacherIdPd6 As String

        objStreamReader = New StreamReader("C:\fbisd\5-12adm.csv")
        strLine = objStreamReader.ReadLine
        Do While Not strLine Is Nothing
            studentId = Val(Mid(strLine, 1, 9))
            lname = Mid(strLine, 11, 20)
            fname = Mid(strLine, 32, 20)
            teacherIdPd1 = Mid(strLine, 68, 4)
            teacherIdPd2 = Mid(strLine, 89, 4)
            teacherIdPd3 = Mid(strLine, 131, 4)
            teacherIdPd4 = Mid(strLine, 152, 4)
           
            Try

                Dim objConnection6 As SqlConnection = New SqlConnection("server=(local);database=FBISD-TAKS;user id=sa;password=pa55word")
                Dim objCommand6 As SqlCommand = New SqlCommand
                objCommand6.Connection = objConnection6
                objCommand6.CommandText = "UPDATE studentInfo SET teacherIdPd1='" & teacherIdPd1 & "',teacherIdPd2='" & teacherIdPd2 & "',teacherIdPd3='" & teacherIdPd3 & "',teacherIdPd4='" & teacherIdPd4 & "' WHERE StudentID=" & studentId
               
                objConnection6.Open()
                objCommand6.ExecuteNonQuery().ToString()
                objConnection6.Close()

            Catch ex As Exception
                MsgBox(ex.ToString())

            End Try
        Loop



    End Sub

End Module
0
kwh3856
Asked:
kwh3856
1 Solution
 
Arthur_WoodCommented:
are the ID fields in your table TEXT fields, or Number fields?

teacherIdPd1
teacherIdPd2
teacherIdPd3
teacherIdPd4

Also try this:

                Dim strSQL as String = "UPDATE studentInfo SET teacherIdPd1='" & teacherIdPd1 & "',teacherIdPd2='" & teacherIdPd2 & "',teacherIdPd3='" & teacherIdPd3 & "',teacherIdPd4='" & teacherIdPd4 & "' WHERE StudentID=" & studentId

                MessageBox.Show("SQL = " & strSQL)
                objCommand6.Connection = objConnection6
                objCommand6.CommandText = strSQL                
                objConnection6.Open()
                objCommand6.ExecuteNonQuery()

or you can place a BreakPoint on the list Dim strSQL as String = ...


and then single step the code, and examine the contents of the variable strSQL.

AW
0
 
ramesh12Commented:
I am not sure if you are using this correct here

Do While Not strLine Is Nothing
0
 
ramesh12Commented:
use like this

Dim objReader As StreamReader
objReader = New StreamReader(filePath)

 While objReader.Peek <> -1        
strLine = objReader.ReadLine

.....
End While
0
 
DotNetLover_BaanCommented:
Try outputting those values... see if they are correct..
            studentId = Val(Mid(strLine, 1, 9))
            lname = Mid(strLine, 11, 20)
            fname = Mid(strLine, 32, 20)
            teacherIdPd1 = Mid(strLine, 68, 4)
            teacherIdPd2 = Mid(strLine, 89, 4)
            teacherIdPd3 = Mid(strLine, 131, 4)
            teacherIdPd4 = Mid(strLine, 152, 4)
-Baan
0
 
kwh3856Author Commented:
Thanks to all but when I out putted the variables I saw that the data stayed the same and never changed.  This told me I was not moving to the next line in my text file.  To fix the problem I put

strLine = objStreamReader.ReadLine

right before my loop command.  This read the rest of the text file in and updated my student records.


Thanks again to everyone for all your suggestions.

Kenny
0
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.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now