problem with loading data to database

R

regwood79

Member
Joined
Jul 20, 2005
Messages
13
Programming Experience
3-5
I have a problem loading my data to my database. I am using mysql. I noticed the problem when i executed my code and it kept giving me a "Argument 'Prompt' cannot be converted to type 'String'" error. But when i checked the db it had some the information in there it would not have the last 3 or 4 data entries in the db. I went back to check the string that was providing the command and I saw that the program was running most of the commands correctly however close to the bottom of the script it was cutting the command off and so the db could process the rest of the information. Can anyone tell me why the data is getting cut off. here is my code it is for a winform app. :confused:
VB.NET: 
Imports System.Data
Imports MySql.Data.MySqlClient
Imports System
Imports System.IO


Public Class Form1
    Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "

    Public Sub New()
        MyBase.New()

        'This call is required by the Windows Form Designer.
        InitializeComponent()

        'Add any initialization after the InitializeComponent() call

    End Sub

    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    Friend WithEvents Label2 As System.Windows.Forms.Label
    Friend WithEvents Submit_Btn As System.Windows.Forms.Button
    Friend WithEvents Browse_btn As System.Windows.Forms.Button
    Friend WithEvents Label3 As System.Windows.Forms.Label
    Friend WithEvents cur_date As System.Windows.Forms.Label
    Friend WithEvents cur_time As System.Windows.Forms.Label
    Friend WithEvents Timer1 As System.Windows.Forms.Timer
    Friend WithEvents FolderBrowserDialog1 As System.Windows.Forms.FolderBrowserDialog
    Friend WithEvents filebox As System.Windows.Forms.TextBox
    Friend WithEvents OpenFileDialog1 As System.Windows.Forms.OpenFileDialog
    Friend WithEvents StatusBar1 As System.Windows.Forms.StatusBar
    Friend WithEvents StatusBarPanel1 As System.Windows.Forms.StatusBarPanel
    Friend WithEvents StatusBarPanel2 As System.Windows.Forms.StatusBarPanel
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.components = New System.ComponentModel.Container
        Dim configurationAppSettings As System.Configuration.AppSettingsReader = New System.Configuration.AppSettingsReader
        Me.Label2 = New System.Windows.Forms.Label
        Me.Browse_btn = New System.Windows.Forms.Button
        Me.filebox = New System.Windows.Forms.TextBox
        Me.Submit_Btn = New System.Windows.Forms.Button
        Me.Label3 = New System.Windows.Forms.Label
        Me.cur_date = New System.Windows.Forms.Label
        Me.cur_time = New System.Windows.Forms.Label
        Me.Timer1 = New System.Windows.Forms.Timer(Me.components)
        Me.FolderBrowserDialog1 = New System.Windows.Forms.FolderBrowserDialog
        Me.OpenFileDialog1 = New System.Windows.Forms.OpenFileDialog
        Me.StatusBar1 = New System.Windows.Forms.StatusBar
        Me.StatusBarPanel1 = New System.Windows.Forms.StatusBarPanel
        Me.StatusBarPanel2 = New System.Windows.Forms.StatusBarPanel
        CType(Me.StatusBarPanel1, System.ComponentModel.ISupportInitialize).BeginInit()
        CType(Me.StatusBarPanel2, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.SuspendLayout()
        '
        'Label2
        '
        Me.Label2.BackColor = System.Drawing.Color.Transparent
        Me.Label2.FlatStyle = System.Windows.Forms.FlatStyle.Popup
        Me.Label2.ForeColor = System.Drawing.Color.Black
        Me.Label2.Location = New System.Drawing.Point(0, 8)
        Me.Label2.Name = "Label2"
        Me.Label2.Size = New System.Drawing.Size(168, 16)
        Me.Label2.TabIndex = 1
        Me.Label2.Text = "File to UPLoad:"
        '
        'Browse_btn
        '
        Me.Browse_btn.ForeColor = System.Drawing.Color.Black
        Me.Browse_btn.Location = New System.Drawing.Point(384, 48)
        Me.Browse_btn.Name = "Browse_btn"
        Me.Browse_btn.Size = New System.Drawing.Size(104, 24)
        Me.Browse_btn.TabIndex = 3
        Me.Browse_btn.Text = "Browse"
        '
        'filebox
        '
        Me.filebox.Location = New System.Drawing.Point(0, 48)
        Me.filebox.Name = "filebox"
        Me.filebox.Size = New System.Drawing.Size(376, 20)
        Me.filebox.TabIndex = 2
        Me.filebox.Text = ""
        '
        'Submit_Btn
        '
        Me.Submit_Btn.ForeColor = System.Drawing.Color.Black
        Me.Submit_Btn.Location = New System.Drawing.Point(0, 120)
        Me.Submit_Btn.Name = "Submit_Btn"
        Me.Submit_Btn.Size = New System.Drawing.Size(104, 24)
        Me.Submit_Btn.TabIndex = 3
        Me.Submit_Btn.Text = "Submit"
        '
        'Label3
        '
        Me.Label3.Font = New System.Drawing.Font("Microsoft Sans Serif", 9.75!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.Label3.ForeColor = System.Drawing.Color.Black
        Me.Label3.Location = New System.Drawing.Point(0, 216)
        Me.Label3.Name = "Label3"
        Me.Label3.Size = New System.Drawing.Size(312, 23)
        Me.Label3.TabIndex = 4
        '
        'cur_date
        '
        Me.cur_date.Font = New System.Drawing.Font("Microsoft Sans Serif", 9.75!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.cur_date.ForeColor = System.Drawing.Color.Black
        Me.cur_date.Location = New System.Drawing.Point(480, 0)
        Me.cur_date.Name = "cur_date"
        Me.cur_date.Size = New System.Drawing.Size(136, 16)
        Me.cur_date.TabIndex = 5
        '
        'cur_time
        '
        Me.cur_time.Font = New System.Drawing.Font("Microsoft Sans Serif", 9.75!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.cur_time.ForeColor = System.Drawing.Color.Black
        Me.cur_time.Location = New System.Drawing.Point(480, 24)
        Me.cur_time.Name = "cur_time"
        Me.cur_time.Size = New System.Drawing.Size(136, 16)
        Me.cur_time.TabIndex = 5
        '
        'Timer1
        '
        Me.Timer1.Enabled = True
        '
        'FolderBrowserDialog1
        '
        Me.FolderBrowserDialog1.SelectedPath = "C:\Documents and Settings\rwoodard.MSBML_REGGIE\Desktop"
        '
        'StatusBar1
        '
        Me.StatusBar1.Location = New System.Drawing.Point(0, 254)
        Me.StatusBar1.Name = "StatusBar1"
        Me.StatusBar1.Panels.AddRange(New System.Windows.Forms.StatusBarPanel() {Me.StatusBarPanel1, Me.StatusBarPanel2})
        Me.StatusBar1.ShowPanels = True
        Me.StatusBar1.Size = New System.Drawing.Size(624, 16)
        Me.StatusBar1.TabIndex = 6
        Me.StatusBar1.Text = "StatusBar1"
        '
        'StatusBarPanel1
        '
        Me.StatusBarPanel1.Text = "StatusBarPanel1"
        Me.StatusBarPanel1.ToolTipText = CType(configurationAppSettings.GetValue("connection status", GetType(System.String)), String)
        Me.StatusBarPanel1.Width = 225
        '
        'StatusBarPanel2
        '
        Me.StatusBarPanel2.Text = "StatusBarPanel2"
        Me.StatusBarPanel2.Width = 200
        '
        'Form1
        '
        Me.AllowDrop = True
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.BackColor = System.Drawing.SystemColors.ActiveBorder
        Me.ClientSize = New System.Drawing.Size(624, 270)
        Me.Controls.Add(Me.StatusBar1)
        Me.Controls.Add(Me.cur_date)
        Me.Controls.Add(Me.Label3)
        Me.Controls.Add(Me.Label2)
        Me.Controls.Add(Me.Browse_btn)
        Me.Controls.Add(Me.filebox)
        Me.Controls.Add(Me.Submit_Btn)
        Me.Controls.Add(Me.cur_time)
        Me.ForeColor = System.Drawing.Color.White
        Me.FormBorderStyle = System.Windows.Forms.FormBorderStyle.Fixed3D
        Me.Name = "Form1"
        Me.Text = "MSBML DataBaseImporter"
        CType(Me.StatusBarPanel1, System.ComponentModel.ISupportInitialize).EndInit()
        CType(Me.StatusBarPanel2, System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)

    End Sub

#End Region

    'Dim db_conn As MySqlConnection
    'Dim mysql_cmd As MySqlCommand
    Dim qry, dbf_holder As String
    Dim file, file2 As String
    Dim ndx, ndx2, j, c As Integer
    Dim dbf, dbf2 As ArrayList
    Dim manip_dbf() As String
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        


        'Open Connection to Database if connection fails process dies
        'Try
            
            'db_conn.Open()
            'If (db_conn.State() = 1) Then
                'StatusBarPanel1.Text = "Connected"
            'End If

        'Catch ex As Exception
            'StatusBarPanel1.Text = "Connection Failed"
            'MsgBox(ex)
        'End Try






    End Sub


    Private Sub Browse_btn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Browse_btn.Click
        'If FolderBrowserDialog1.ShowDialog() = DialogResult.OK Then
        'filebox.Text = FolderBrowserDialog1.SelectedPath
        ' End If
        If OpenFileDialog1.ShowDialog() = DialogResult.OK Then
            filebox.Text = OpenFileDialog1.FileName
        ElseIf (OpenFileDialog1.ShowDialog() = DialogResult.Cancel) Then
            Return
        End If

    End Sub



    Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick
        cur_date.Text = Format(Now, "dd-MMM-yyyy")
        cur_time.Text = Format(Now, "HH:mm:ss")
    End Sub


    Private Sub Submit_Btn_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Submit_Btn.Click
        Dim objreader As New StreamReader(filebox.Text)  ' read from text file
        Dim objwriter As New StreamWriter("c:\datafiles\scripts.txt")
        ndx = 1
        ndx2 = 1
        dbf = New ArrayList 'internal holder for data from textfile

        While objreader.Peek <> -1
            dbf.Add(objreader.ReadLine())
        End While

        While ndx2 < dbf.Count  'Data Formatter
            dbf_holder = dbf(ndx2)

            dbf_holder = dbf_holder.Replace(",,", ",' ',") 'formatting data for entrance in to database
            dbf_holder = dbf_holder.Replace(",,", ",' ',")
            dbf(ndx2) = dbf_holder


            manip_dbf = dbf(ndx2).split(",") 'divides text string in order to manipulate individual datafields

            j = 0
            While j <= manip_dbf.GetUpperBound(0)
                c = String.Compare("' '", manip_dbf(j))
                If c = 0 Then
                    j += 1
                ElseIf IsNumeric(manip_dbf(j)) = True Then
                    j += 1
                Else
                    manip_dbf(j) = "'" & manip_dbf(j) & "'"
                    j += 1
                End If

            End While

            dbf(ndx2) = String.Join(",", manip_dbf)


            ndx2 += 1
        End While
        'End of Data Formatting Section


        While ndx < dbf.Count
            qry = "Insert into profile (identifier,bin,version_number,transaction_Code,farm_num,misc_ID," _
            & "zip_shrt,DOB, Gender, date_Filled, subx_num, new_Refill, qty, total_supply, compound_code, " _
            & "NDC_Num, driver_License, account_DEA, date_Written, num_Refills_Auth, origin_code, customer_loc, diagnostic_code," _
            & "alternate_Prescriber, pat_Lname, pat_Fname, pat_Address, pat_State, pat_Zip, triplicate_Serial, payment_meth, date_entered" _
            & ")" _
            & " Values (" & dbf(ndx) & ", Now()" & ");"

            'mysql_cmd = New MySqlCommand(qry, db_conn)
            Try
                'Dim inserter As Integer = mysql_cmd.ExecuteNonQuery()
                objwriter.WriteLine(dbf.Count)
                objwriter.WriteLine(ndx)
                objwriter.WriteLine(qry)
            Catch ex As Exception
                MsgBox(ex)

            End Try
            ndx += 1
        End While


        'db_conn.Close()
        'db_conn.Dispose()
        'If (db_conn.State < 1) Then
        'StatusBarPanel1.Text = "Database Connection Closed"
        'End If

    End Sub

    
    
End Class

hear is a sample of the text file i am using:
VB.NET: 
ABS,0,1,1,2506270,,386,19710713,2,20060115,1057435,3,30,30,0,63402019110,BD7774424,,20060115,5,0,0,,,KAMMAN,NICOLE,3621 LYLES DR.,MS,38655,,
ABS,0,1,1,2506270,,386,19661003,2,20060115,1063434,1,100,30,0,254501238,AH2675431,,20060115,2,0,0,,,HOLLINGER,PAM,515 CR 103 APT 1,MS,38601,,
ABS,0,1,1,2506270,4,386,19630709,1,20060115,1082885,1,30,30,0,24542131,BW2802836,,20060115,1,0,0,,,PHILLIPS,ANDY,9 HIGHLAND PLACE,MS,38655,,
ABS,0,1,1,2506270,3,386,19131105,2,20060115,1087839,0,150,7,0,406037516,BB8859273,,20060115,0,0,0,,,SMITH,FRANCES T,100 AZALEA DRIVE,MS,38655,,
ABS,0,1,1,2506270,,386,19190802,1,20060115,1087844,0,30,30,0,63402019110,BD7774424,,20060115,0,0,0,,,WILLISTON,HAMLIN,362 A HWY 7 S,MS,38655,,
ABS,0,1,1,2506270,,386,19220920,2,20060115,1087852,0,20,3,0,406177205,AL0246632,,20060115,0,0,0,,,IVY,AVIE,404 CO RD 202,MS,38601,,
 
Sort by date Sort by votes
Not sure but do notice something

I did notice that the file that you are reading is comma delimited however when you are comma delimitting something you should also qualify it. It could be possible that this data is getting split and messed up for that reason. It is good habbit to qualify comma delimited text files with quotations. That is why when I am creating index files I like to use a | (pipe) or a ~ (tilde) because it does not leave room for error when splitting comma delimited files which i find myself doing often in the work place.
 
Upvote 0 Downvote
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sr As New System.IO.StreamReader("c:\testfile.text")
Dim aStr() As StringCollection = Nothing
Dim intCounter As Integer = 0
Dim line As String = sr.ReadLine
Do Until line Is Nothing
ReDim Preserve aStr(intCounter)
For Each str As String In line.Split(",")
aStr(intCounter).Add(str)
Next
line = sr.ReadLine
intCounter += 1
Loop
Dim blFirstPass As Boolean = True
Dim intLength As Integer = aStr(0).Count
Dim count As Integer = 0
For Each strCol As StringCollection In aStr
If blFirstPass Then
count = strCol.Count
blFirstPass = False
End If
If strCol.Count = count = False Then
MsgBox(strCol.ToStringEachItemToLine() & " is an invalid data set")
End If
Next
End Sub
End Class

Public Class StringCollection
Inherits CollectionBase
Default Property item(ByVal index As Integer) As String
Get
Return CType(InnerList.Item(index), String)
End Get
Set(ByVal Value As String)
InnerList.Item(index) = Value
End Set
End Property
Sub Add(ByVal Value As String)
InnerList.Add(Value)
End Sub
Public Function IsInternedWith(ByVal strValue As String) As Boolean
Dim blResult As Boolean = False
For Each str As String In Me
If str.ToUpper = strValue.ToUpper Then
blResult = True
End If
Next
Return blResult
End Function
Public Function ToStringEachItemToLine() As String
Dim strReturn As String = ""
For Each str As String In Me
strReturn &= str & ControlChars.CrLf
Next
Return strReturn
End Function
This is code that may be useful to test if all the data in the text file is good or not. Just place the file path in constructor for the streamreader. This will test that when split at the commas that all fields will have equal values this is a good test for comma delimited files that are not qualified with quotations.


p.s.

I just wrote it up so tell me if you thought it was useful or not because I am trying to get involved on these forums because it helps me learn because I am just beginning to finsih my degree in programming up in about 18 mo's online and plan to get for MCSD/MCAD type certification.

Sincerely,

Steve-0
 
Upvote 0 Downvote
sorry for taking so long ss7. I believe the program is reading the text file properly. it is when it goes to the qry variable to create the command string it is cutting off there. Still haven't figured out what's the problem though.
 
Upvote 0 Downvote
Another Suggestion

Check the values that are being passed in that ArrayList I am not too familiar with this object but it seemed like something funky was going on there with the join statement I did not get a chance to run the code because I am at work but the first thing that I would do to determine what was wrong would be to print out that dbf variable to check that you are passing it valid data and because it seemed like that join may have been causing you to pass to many values but if not another suggestion would be to check the syntax of the SQL statement because you are supposed to have single quotations around stuff in MySQL but not sure if that matters because a lot of programmers set it up to add that stuff outside that code so try these things out and ill try to run it if you are still having trouble.....
 
Upvote 0 Downvote
I am still trying to figure out why the program is cutting the qry variable string. that is the problem i found. I sent the qry variable's output to a text file and it will run all of the text strings i specify in a loop except the last 3 or 4 lines. for example let's say qry is equal to: ->"This is a test of the emergency broadcast system". <-:

In my program if i looped this statement let's say 18 times it would print out the statement about 16 times and then cut the next line and not even show the 18th line like this
16: this is a test of the emergency broadcast system
17: this is a test of the
18:
this the reason the mysql is throwing the error because it is not getting a full command. I am trying to figure out why my program is cutting the lines off. thanks for any help you can give ss7 i really do appreciate it.
Help from anyone would be appreciated.
 
Upvote 0 Downvote
Try something else....

Maybe you should try another type of variable rather than an array list that seems like an older command try comething like a collection or maybe just a good old string and accumulate the text kind of like you are doing. If you want I always this string collection class that I wrote try it out instead of the array list because i think that may be what is causing your problem.


Public Class StringCollection
Inherits CollectionBase
Default Property item(ByVal index As Integer) As String
Get
Return CType(InnerList.Item(index), String)
End Get
Set(ByVal Value As String)
InnerList.Item(index) = Value
End Set
End Property
Sub Add(ByVal Value As String)
InnerList.Add(Value)
End Sub
Public Function IsInternedWith(ByVal strValue As String) As Boolean
Dim blResult As Boolean = False
For Each str As String In Me
If str.ToUpper = strValue.ToUpper Then
blResult = True
End If
Next
Return blResult
End Function
Public Function ToStringEachItemToLine() As String
Dim strReturn As String = ""
For Each str As String In Me
strReturn &= str & ControlChars.CrLf
Next
Return strReturn
End Function
Public Sub New()
End Sub
End Class

I just find this easier to use even if you are going to try to have an array of StringCollections but this is just better than arrays in my opinion and I can easily whip one up for each data type and write various functions for it. Even if this doesn't work for this program it may be useful elsewhere.
 
Upvote 0 Downvote

Similar threads

M
  • Question
Question I am using VB2019 I want to create a database, I got problems
Replies
1
Views
1K
jmcilhinney
jmcilhinney
Q
  • Question
http generic handler to show image from database
Replies
8
Views
2K
JohnH
JohnH
Deva
  • Question
Question when i import data from excel and dump to sqlite database Database locked error occured
Replies
8
Views
6K
JohnH
JohnH
R
  • Question
Transfer data from combobox to columns with condition
Replies
1
Views
3K
jmcilhinney
jmcilhinney
Poppa Mintin
  • Question
I have a problem with backcolor.
Replies
2
Views
2K
Poppa Mintin
Poppa Mintin

Share this page

Latest posts

Back
Top