Make Table Query Issue

[ggunter]

Hi,

This is my first try at Ado and I have run into an issue. I am trying to run a maketable query in one Access database to create a table in a different Access database. When I run my code, I don't receive any errors but the table isn't created either. And before you ask; yes, I am looking in the database in the \bin\Debug folder.

This is my code in the form:

Private Sub btnAssoc_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnAssoc.Click

    ggAdo.ConnectionString = dbsAc <database connection string>

    ggAdo.SqlStatement = "SELECT tblAssocInfo.Acid, tblAssocInfo.FirstName, tblAssocInfo.LastName, tblAssocInfo.MgrFull, " & _
                         "tblAssocInfo.Bucket, tblAssocInfo.Department " & _
                         "INTO tblAssoc " & _
                         "IN 'C:\z_GG Project\_Library\z_Test\Adox_1\bin\Debug\ggTest.mdb' " & _
                         "FROM tblAssocInfo;"

    ggAdo.RunSqlStatement()  <below>

    MessageBox.Show("Complete", "Done", MessageBoxButtons.OK, MessageBoxIcon.Information)

End Sub

This is my code to open the database:

Private Function OpenDatabase() As Boolean

    Try
        adoConn = New ADODB.Connection

        adoConn.Open(ConnectionString)

    Catch ex As Exception
        MessageBox.Show("Dbs Open Failed: " & ex.ToString, "Dbs Open Fail", MessageBoxButtons.OK, MessageBoxIcon.Error)

        OpenDatabase = False

        Exit Function
    End Try

End Function

And here is my code to run the sql statement:

Public Function RunSqlStatement() As Boolean
Dim runFound As Boolean = False
Dim runProc As ADOX.Procedure

    If OpenDatabase() = True Then  <open database above>

        adoCat = New ADOX.Catalog
        adoCat.ActiveConnection = adoConn

        adoCmd = New ADODB.Command
        adoCmd.CommandText = SqlStatement (class property)

        For Each runProc In adoCat.Procedures
            If runProc.Name = "ggSql" Then
                runFound = True
                Exit For
            End If
        Next

        If runFound = False Then
            adoCat.Procedures.Append("ggSql", adoCmd)
        End If

        adoCmd = CType(adoCat.Procedures("ggSql").Command, Command)

        adoCat.Procedures("ggSql").Command = adoCmd

        adoCat = Nothing
        adoCmd = Nothing
        adoConn = Nothing
    End If

End Function

I got almost all of this code from Microsoft Help but I'm thinking the examples left out something that I missed. As I said, when I run this I get no errors but I get no table either.

ANY help would be greatly appreciated.

 

[cjard]

er. where do you actually run the sql? and what's with all this adodb stuff?

 

[ggunter]

cjard,

I apologize for the entire posting. This is my first foray into Ado and I expected it to be more complicated. I was trying to follow an example from Help. Apparently, I COMPLETELY misunderstood what was going on.

Anyway, I did more research on my weekend and found it was SO easy.

Dim <ConnectionObject> As ADODB.Connection = New ADODB.Connection

<ConnectionObject>.Open(<ConnectionString>)

<ConnectionObject>.Execute(<SqlStatement>)

<ConnectionObject>.Close
<ConnectionObject> = Nothing

I appreciate you replying and I apologize for the post.

 

[cjard]

No need to apologize, i just couldnt see where you actually ran the SQL.

I want to point out that ADODB is really old, and we don't really use it in .net. Is there a specific reason why youre using it?

 

[ggunter]

I am importing an Excel worksheet with variable columns. Once imported, I need to get the field names from that table.

When I googled it, all the responses I looked at indicated I needed to use ADOX to get the field names.

Are you saying I should use something more like this? Or do I still have it wrong?

Public Function GetFieldNames(ByVal tableName As String) As Int32
        Dim fldCnt As Int32 = 0

        'verify property values
        If Verify() = 1 Then
            MessageBox.Show("No connection string to the database was detected.  _
                   Please set connection string property.", "Missing Connection", MessageBoxButtons.OK, MessageBoxIcon.Error)
            GetFieldNames = 1
            Exit Function
        End If

        'initialize connection object
        dbsConn = New OleDbConnection(ConnectionString)

        'open the connection
        Try
            dbsConn.Open()
        Catch ex As Exception
            MessageBox.Show("Dbs Error: " & ex.Message, "Dbs Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            GetFieldNames = 2
            Exit Function
        End Try

        'capture schema column info
        Dim schemaTable As DataTable = dbsConn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, _
                       New Object() {Nothing, Nothing, tableName, Nothing})

        'capture the number of fields in the table
        maxFlds = (schemaTable.Rows.Count - 1)

        'begin loop
        For fldCnt = 0 To maxFlds
            fieldNames.Add(schemaTable.Rows(fldCnt)!COLUMN_NAME.ToString)
        Next

        'close the connection
        dbsConn.Close()

        'clean up
        schemaTable.Dispose()
        schemaTable = Nothing

        dbsConn.Dispose()
        dbsConn = Nothing

        'set return value
        GetFieldNames = 0

    End Function

 

[cjard]

How does Excel have field names?

All the excel files i've ever worked on have had columns (fields) of A B C D .. and when read in using a database reader like Jet, the columns become called F1 F2 F3 F4..

If the column names are in the data e.g. as the first line then you read them out as ordinary values. You can then assign the name of the relevant datacolumn (if you read the excel file into a datatable) to the text therein..

ps; your code example doesnt seem to be ADOX. Calling GetSchema is the ADO.NET way of getting schema info

pps; i'd hate to have to work on your code. You write a method called GetFieldNames and then return an integer from it? I'd expect an array of strings. When Microsoft write a method called GetSchemaTable, you get a table back, containing a schema. If you really must have this concept of magic numbers lying around in your code (what does a Verify() result of 1 even mean? It failed? It succeeded? ) use Enums to give your magic numbers some meaning.. Rename your methods to represent what they do, and return. Verify() -> DataIsValid() 'implies it returns a boolean, and indicates what it does. Finally, don't put message boxes in code like this - this is utility code that is not supposed to interact with the user. Can you imagine if microsoft put a messagebox in the middle of GetSchemaTable? You call it from a server service and that's it; your service hangs waiting for an answer to a dialog box that noone will ever see. If you have a problem in utility code, throw an exception, and catch it in the GUI code that calls the utility, then deal with it

 

[ggunter]

How does Excel have field names?

The Excel file doesn't have field names, it has column headers. When I open the table, after running the make table query, the database has taken the column headers and created field names.

ps; your code example doesnt seem to be ADOX. Calling GetSchema is the ADO.NET way of getting schema info.

I know. Your previous posting seemed to indicate I was going in the wrong direction so I did further research and found this method. Am I headed in the wrong direction again? If so, could you point me in the right direction? I'm honestly trying to learn here.

i'd hate to have to work on your code. You write a method called GetFieldNames and then return an integer from it? I'd expect an array of strings.

You're completely correct and I appreciate your feedback. I should have returned the array directly instead of pass/reason failed integers. The code I'm providing is merely a small test program to get the coding down for one small piece of the overall program. Still, I need to get into the habit of treating it like it was live code so I can reinforce good coding skills.

 

[cjard]

If your connection string has HDR=Yes in the extended properties, then the first few lines of the file will be taken as the column names. Beware using this though because I see references that IMEX=1 doesnt work if HDR=Yes

Use an OleDbDataAdapter to Fill() a datatable
With HDR=No, the dt.Rows(0) row has your headers in, if you care what they are, or if your columns are going to shift around then establish what they are first:

For i as Integer = 0 To dt.Columns.Count
  dt.Column(i).ColumnName = dt.Rows(0)(i).ToString()
Next i
'caveat: untested/pseudocode theory only

Then dump your headers:

dt.Rows.RemoveAt(0)


Then do your data:

ForEach ro as DataRow in dt.Rows
  Messagebox.Show(ro("OrderID").ToString())

  ... etc