Solved

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

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Import a excel sheet in a grid 2 45
Help with Report Viewer in VS2013 6 33
sql server connection string in config file 4 41
Make borderless form movable by user 2 27
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…
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 …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

734 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