Solved

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

Posted on 2004-10-12
5
854 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
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 500 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

773 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