Solved

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

Posted on 2004-10-12
5
826 Views
Last Modified: 2010-08-05
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
Comment
Question by:kwh3856
5 Comments
 
LVL 44

Expert Comment

by:Arthur_Wood
Comment Utility
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
 
LVL 7

Expert Comment

by:ramesh12
Comment Utility
I am not sure if you are using this correct here

Do While Not strLine Is Nothing
0
 
LVL 7

Expert Comment

by:ramesh12
Comment Utility
use like this

Dim objReader As StreamReader
objReader = New StreamReader(filePath)

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

.....
End While
0
 
LVL 18

Accepted Solution

by:
DotNetLover_Baan earned 500 total points
Comment Utility
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
 

Author Comment

by:kwh3856
Comment Utility
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

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
This video discusses moving either the default database or any database to a new volume.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

762 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now