Posts by hjdoran

Hi everyone,
I'm trying to make the autocomplete work on a list of items entered into a textbox separated by semicolons. I have a list of authors the user can choose from and it autocompletes as they type the name no problem, but what I'd like to do is after the user types a name followed by a semicolon, the autocomplete works on the next name and so on.

e.g., Smith, John; Eastwood, Clint

Any suggestions?
cheers
Harry

I've changed Yes/No to BIT and I still get the syntax error in the field definition, but when i run the sql statement in Access it creates the table...ugh. I'm missing something so simple it is driving me nuts.

Hi everyone,

I'm at my witts end and need some advice. I need to create a new table in MS Access from a datatable and I just can't get it to work. I have tried SQL with no luck, but I've been spinning my wheels in the mud. Any advice would be greatly appreciated.

cheers

Private Sub CreateTable(ByRef dsDataSet As DataSet, ByVal strTableName As String, ByVal strPath As String)

        Dim strConnection As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath & ";"
        Dim conn As New OleDbConnection(strConnection)
        Dim objCmd As New OleDbCommand

        conn.Open()


        Dim dt As DataTable = dsDataSet.Tables(strTableName)
        Dim dc As DataColumn
        Dim strSQL As String = "CREATE TABLE [" & strTableName & "] ("
        Dim strTemp As String = String.Empty

        For Each dc In dt.Columns
            With dc
                strTemp += SQL_Helper(dc) & ","
                Debug.Print(strTemp)
            End With
        Next
        'remove the last comma and replace it with a bracket
        strTemp = Microsoft.VisualBasic.Left(strTemp, (strTemp.Length - 1))
        Dim strTemp2 As String = strSQL & strTemp & ")"

        Debug.Print(strTemp)

        objCmd = New OleDbCommand(strTemp2, conn)
        objCmd.ExecuteNonQuery()
        conn.Close()



    End Sub

    Private Function SQL_Helper(ByVal dcColumn As DataColumn) As String
        'return the field portion of SQL statement
        Dim intUnderscore As Integer = InStr(dcColumn.ColumnName.ToString, " ")
        Dim strFieldName As String
        Dim strDataType As String

        If intUnderscore > 0 Then
            'field name …

Hi everyone,

I'm at my witts end and need some advice. I need to create a new table in MS Access from a datatable and I just can't get it to work. I have tried SQL with no luck, but I've been spinning my wheels in the mud. Any advice would be greatly appreciated.

cheers

Private Sub CreateTable(ByRef dsDataSet As DataSet, ByVal strTableName As String, ByVal strPath As String)

        Dim strConnection As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath & ";"
        Dim conn As New OleDbConnection(strConnection)
        Dim objCmd As New OleDbCommand

        conn.Open()


        Dim dt As DataTable = dsDataSet.Tables(strTableName)
        Dim dc As DataColumn
        Dim strSQL As String = "CREATE TABLE [" & strTableName & "] ("
        Dim strTemp As String = String.Empty

        For Each dc In dt.Columns
            With dc
                strTemp += SQL_Helper(dc) & ","
                Debug.Print(strTemp)
            End With
        Next
        'remove the last comma and replace it with a bracket
        strTemp = Microsoft.VisualBasic.Left(strTemp, (strTemp.Length - 1))
        Dim strTemp2 As String = strSQL & strTemp & ")"

        Debug.Print(strTemp)

        objCmd = New OleDbCommand(strTemp2, conn)
        objCmd.ExecuteNonQuery()
        conn.Close()



    End Sub

    Private Function SQL_Helper(ByVal dcColumn As DataColumn) As String
        'return the field portion of SQL statement
        Dim intUnderscore As Integer = InStr(dcColumn.ColumnName.ToString, " ")
        Dim strFieldName As String
        Dim strDataType As String

        If intUnderscore > 0 Then
            'field name has a blank space so enclose in []
            strFieldName = "[" & dcColumn.ColumnName.ToString & "]"
        Else
            strFieldName = dcColumn.ColumnName.ToString
        End If

        Select Case (dcColumn.DataType.ToString)
            Case "System.String"
                strDataType = "TEXT(50)"    'hard wire size
            Case "System.Int16"
                strDataType = "INTEGER" …

Hi everyone,

I've got an interesting problem that has me stumped and I just cannot seem to find the right answer in the help files.

Background: My project requires that I establish more than a few relationships amongst datatables in a dataset so I decided to create a generic routine that would create the table relationships that I could call on as needed. All the relationship require multiple datacolumns so I pass arrays of datacolumns for the parent and child tables.

Problem: My problem stems from trying to populate an array of datacolumns. I pass in a list of fields to be used in the datatable relation, parse out each field name into an array, create a datacolumn object, iterate through the list of fields and try to copy the datacolumn from the parent/child table to the appropriate datacolumn array. First time through the loop it works fine, but on the second trip through the loop it is unable to find the datacolumn in the parent/child table and it assigns nothing to the array element.

I've stared at this code for too long and I know I'm missing something, but I cannot seem to find the answer so any help would be greatly appreciated.

cheers

Private Sub CreateRelation(ByRef dsDataset As DataSet, ByVal strParentTable As String, ByVal strChildTable As String, _
                               ByVal strParentFields As String, ByVal strChildFields As String, ByVal strRelationName As String)
        'Purpose is to create a generic sub routine that will join tables …

Hi everyone,

I'm looking for thoughts on how to copy fields between databases and I just can't seem to get pointed in the right direction. My problem is that I have two databases, one is newer than the other and has been modified (fields added over time). There is an existing append process that requires that both databases are identical in structure so what I'd like to do is: automatically scan the databases to identify the differences in fields; and, copy those fields that are missing from one database to the other. I'd appreciate any thoughts/ideas on which object to use etc.

cheers

I think I'm spending too much time at the keyboard, but I've figured out the answer to my question...use the form.paint event to fire the refresh listbox method.

Thanks again mikiurban...you got me on the right track to solving the problem.

cheers

Thanks mikiurban...that was the line of thinking for getting information from one form to the other and that worked, but I'm still trying to figure out how to fire an event on one form when another is closed down.

Here's the sequence of events generalized: in form1 I create a second form2 and let the user rename a list box item. What I am trying to do is fire a refresh listboxes event when form2 closes and set the focus back to the listbox (selecteditem) on form1. My logic may be flawed as I'm sure there is a better way to let the user rename an item in a listbox so any suggestions would be greatly appreciated.

cheers,

Thanks mikiurban...that was the line of thinking for getting information from one form to the other and that worked, but I'm still trying to figure out how to fire an event on one form when another is closed down.

Here's the sequence of events generalized: in form1 I create a second form2 and let the user rename a list box item. What I am trying to do is fire a refresh listboxes event when form2 closes and set the focus back to the listbox (selecteditem) on form1. My logic may be flawed as I'm sure there is a better way to let the user rename an item in a listbox so any suggestions would be greatly appreciated.

cheers,

Whenever I need to pass info between dialogs, I make public properties on the 2nd dialog. For example, if I had a dialog with a textbox that I preset the text, then let the user change, I would add this to my 2nd dialog (assuming the textbox ID was "myTextBox")

Public Property Usertext() As String
        Get
            Return myTextBox.Text
        End Get
        Set(ByVal Value As String)
            myTextBox.Text = Value
        End Set
    End Property

Then, when I want to use this:

Dim myDialog as New MyDialog()
myDialog.UserText = "change me!"
If myDialog.Show() = DialogResult.OK
Print("You entered: " & myDialog.UserText)
End If

(warning: i typed all this from memory, and didn't try compiling it for errors, but I hope my point is made. Also, I may be doing it wrong in general, but this usually …

Hi gang,

I have a unique challenge that I just cannot seem to get by...I have a form with a series of listboxes that allows the user to rename items in the listbox (I probably should be using a listview control instead). When the user right-clicks and selects rename from the context menu I show a simple form with two labels & text boxes showing the selected list box item in one textbox...the other is blank waiting for the user to enter a new name. What I'd like to do is when the user closes the rename form, the information is passed back to the calling form and a listbox refresh method is fired to show the changed name. Couple question:

First, is there an easier method to implement the renaming concept in a listbox (probably should be using a list view) i.e., have I overcomplicated things?

Second, does anyone have any suggestions or code examples to share that would shed some light on my challenge.

Any thoughts/suggestions would be greatly appreciated.

cheers