?
Solved

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

Posted on 2004-10-12
5
Medium Priority
?
881 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 44

Expert Comment

by:Arthur_Wood
ID: 12292697
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
ID: 12293533
I am not sure if you are using this correct here

Do While Not strLine Is Nothing
0
 
LVL 7

Expert Comment

by:ramesh12
ID: 12293556
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 2000 total points
ID: 12294483
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
ID: 12294975
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
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 …
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses

770 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