trying to insert records into SQL database

[scootsah]

I am new to VS 2005/SQL. Am trying to insert records into a new SQL database from a flat file. The code below executes, but nothing actually gets put into the table. I'm sure that I am missing something obvious. Thanks in advance for your help. Here's the code...

        Dim AllStamps As New DataSet("StampOrganizer")
        Dim workTable As DataTable = AllStamps.Tables("StampStaticInfo")
        Dim Fields() As String
        Dim InputFile As FileIO.TextFieldParser
        Dim ConnectionString As String
        Dim n%

        ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Stamps.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
        Dim conn As New SqlConnection(ConnectionString)

        Dim cmd As New SqlCommand
        cmd.Connection = conn

        ''''''''''''''''''''''''''''''''''''''''''
        ' Add rows to Table via flat .txt file
        ''''''''''''''''''''''''''''''''''''''''''
        InputFile = My.Computer.FileSystem.OpenTextFieldParser(InFile$, vbTab)

        Do While Not InputFile.EndOfData
            Fields = InputFile.ReadFields
            For n% = 0 To 2
                Fields(n%) = Replace(Fields(n%), "'", "`")
            Next
            cmd.CommandText = "INSERT INTO StampStaticInfo (ScottNo,Denomination,Description)" & _
                " VALUES ('" & Fields(0) & "'," & Fields(1) & "," & "'" & Fields(2) & "')"
            conn.Open()
            n% = cmd.ExecuteNonQuery()
            conn.Close()
        Loop

        InputFile.Close()

[FunkyDexter]

I had a similar problem and Si pointed me at a post of JMcIlhinney's. That might be your problem


edit> also, as an aside, you don't need to open and close your connection each time you run the insert. It would be more efficient to open it before the loop and close it afterwards.

[scootsah]

Thanks for the suggestion. I found that setting & changed it as described, but still no luck. I don't think this could be the problem anyway, as I was able to manually add a record into the table & that one persisted. Since this is my first time around, perhaps it is something more basic, like perhaps I'm not connected to the correct table or some such thing.

Here's an example of what one of the cmd.CommandText looks like:

"INSERT INTO StampStaticInfo (ScottNo,Denomination,Description) VALUES ('2',0.10,'Washington - black')"

Any suggestions?

[GaryMazzone]

Is ScottNo an AutoNumber Primary Key? IF so you do not include that in the insert statement. Is there an error number provided in feedback. Have you set brakepoints and walk trough the code a line at a time looking for errors?