0

I have an Object (Results_DataColumn) that holds column names for each Survey on the data provider server. Now I have to create a Table with this column information so if the Results_DataColumn Object holds (1)Name and (2)City I want to create a table on my own Sql Server with the Columns 1 ID, 2 Name, 3 City. The code I have written is:

Public Class TableCreator
    Public Shared Sub CreateTable(ByRef myColumnInformation As Results_DataColumn, ByVal tableName As String)

        Dim conStr As String = "Server=NB01035;Database=DB_Import;Trusted_Connection=true"

        Using con As SqlConnection = New SqlConnection(conStr)
            Using sqlCom As New SqlCommand
                With sqlCom
                    .Connection = con
                    .CommandType = CommandType.Text
                    .CommandText = queryBuilder(myColumnInformation, tableName)
                End With
                Try
                    con.Open()
                    sqlCom.ExecuteNonQuery()
                Catch ex As SqlException
                    Console.WriteLine(ex.Message.ToString(), "Error Message")
                End Try
            End Using
        End Using
    End Sub

    Public Shared Function queryBuilder(ByRef myColumnInformation As Results_DataColumn, ByVal tableName As String) As String
        Dim query As StringBuilder = New StringBuilder
        query.Append("IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = [")
        query.Append(tableName)
        query.Append("] DROP [")
        query.Append(tableName)
        query.Append("] BEGIN CREATE TABLE [")
        query.Append(tableName)
        query.Append("] ( ID int identity(1,1) not null, ")

        For i As Integer = 0 To myColumnInformation.results.Count - 1
            query.Append(myColumnInformation.results(i).questionCode.ToString)
            query.Append(" VARCHAR(MAX)")
            query.Append(", ")
        Next

        If myColumnInformation.results.Count > 1 Then
            query.Length -= 2
        End If
        Console.WriteLine(query.ToString)

        query.Append(")")
        Console.WriteLine(query.ToString)
        Return query.ToString
    End Function
End Class

It runs through but does not create a table on the server and I just have no idea why. The created SQL-Query looks fine. Anyone who can help me?

ruedi
  • 5,365
  • 15
  • 52
  • 88
  • Are you sure this isn't producing an error message? The query to check for the existence of the table seems little off. Unless your string has 'TableName' it is missing the single quotes. Personally I would move the try/catch block to wrap every statement in your method, not just some of them. – Sean Lange Dec 12 '18 at 15:18
  • ...and query.Append("] DROP [") has to be query.Append("]) DROP TABLE [") – Denis Rubashkin Dec 12 '18 at 15:27
  • Looks like something is wrong with the connection. Now that I did a Step-by-Step workthrough and wrapped everything in Try...Catch I get the Error A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) – ruedi Dec 12 '18 at 15:30
  • I corrected the Connection String and the query and now it is working. I also had to correct this: https://stackoverflow.com/questions/7887011/how-to-drop-a-table-if-it-exists-in-sql-server – ruedi Dec 12 '18 at 15:55

0 Answers0