how to write to a database using vb.net and multiple columns.

I am trying to write to a database using the following code. The code below works just fine, but how can I write an unknown amount of columns to a table?

So after the barcode I would need to write up to 47 new columns.

 Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim conStr As String = "SERVER=PC167\DBTEST;Database =ORKA Test; trusted_connection= yes"
        Dim objcon As New SqlConnection(conStr)
        Dim obj As SqlCommand
        Dim strSQL As String
        Dim DoesTableExist As Boolean
        objcon.Open()
        If (objcon.State = ConnectionState.Open) Then
            MessageBox.Show("Open")
        End If
        If objcon.Database = "ORKA Test" Then
            MessageBox.Show("Database Exists")
        Else
        End If
        obj = objcon.CreateCommand()
        strSQL = "CREATE TABLE  DATA3" + "(Time VARCHAR(30), Date VARCHAR(30),Barcode VARCHAR (30))"
        obj.CommandText = strSQL
        obj.ExecuteNonQuery()
    End Sub

Open in new window

cmdolcetAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You're not really writing data, you're creating a table. Is that what you intend to do?

If so, then do you want to add other columns to the CREATE TABLE statement? You can do that by just adding the other Column information, like:

strSQL = "CREATE TABLE  DATA3 (Time VARCHAR(30), Date VARCHAR(30),Barcode VARCHAR (30), SomeColumn VARCHAR(50))"
cmdolcetAuthor Commented:
OK I thought the Data3 table was creating the Table and then inside that table (Data3) I thought I was creating columns called "Date, Time Barcode, SomeColumn (depending on how much data I am writing.

In reality I would  like the following format

Date                Time       Barcode      Reading 1   Reading 2 ..............................................
01/28/2019     9:00am  123456           0.10             0.20            ........................................
01/28/2019     9:01am   343565          0.11             0.22 .............................................
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
OK I thought the Data3 table was creating the Table and then inside that table (Data3) I thought I was creating columns called "Date, Time Barcode, SomeColumn (depending on how much data I am writing.
That's basically what you're doing - creating a table. You're NOT inserting data into that table, however.

With that said:

Do you mean you want to CREATE a table with multiple READING columns? If so, I'd suggest you not do that, and instead create a Header table, and a Detail table:

tHeader
-----------------
HeaderID
EntryDate
EntryTime
Barcode

tDetail
-------------
DetailID
HeaderID
Reading

You could create that structure ahead of time, and then just use INSERT statements to add records based on your barcode data.
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

cmdolcetAuthor Commented:
Yes Scott how I understand I am not inserting data yet, however how could I create a table and the columns dynamically based on how much data is collected?
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Depends on how the data is collected, and in what format. You can add as many columns to a table as needed (to the limit imposed by the database engine, anyway), but first you'd have to determine how many columns you need. Do you have a sample of the data you're receiving?
cmdolcetAuthor Commented:
I would need to add 50 columns
Date  Time Barcode and 47 readings columns labeled 1-47
cmdolcetAuthor Commented:
How do I write the multiple reading values on the same line?

 Dim conStr As String = "SERVER=PC167\DBTEST;Database =ORKA Test; trusted_connection= yes"
 Private Sub BtnReadVariable_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnReadVariable.Click
Dim command as new SqlCommand ("insert into DATA3( Date,Time,Barcode,Reading) values('"date.now &"','" &"Time.now"' &"','" & barcode),Connection)

end sub

Open in new window

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
If you need to add 50 VARCHAR columns, you could do this:

Dim strSQL = "CREATE TABLE  DATA3 (Time VARCHAR(30), Date VARCHAR(30),Barcode VARCHAR (30)"
For i As Integer = 1 to 50
  strSQL = strSQL & ",Col" & i.ToString & " VARCHARC(30)"
Next i

strsql = strSQL & ")"

If you ran that SQL command, you'd create a table named DATA# with 53 columns, but NO DATA. You can't really add Columns and Data at the same time - you have to create the Columns first, then insert the Data.

Your second comment has to do with INSERTING data. But - again - you haven't told us how you're getting that data, where it's coming from, what it looks like, etc, so we really can't give you much to go on.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cmdolcetAuthor Commented:
I am using a combination of the following below. I am appending strExportText each loop. The loop is setup based on the

 For intLoop = 0 To export.ExportItems.Count - 1
 Dim exportItems As String = CType(export.ExportItems(intLoop), Array).GetValue(0)
                            Select Case exportItems
                                Case "Field - <Start Repeater>"
                                    boolRepeater = True
                                Case "Field - <End Repeater>"
                                    boolRepeater = False
                                Case "Field - <Start Header>"
                                    intLoop = FindHeaderInfo(export, strExportHeader)
                                    strExportHeader += vbCrLf
                                    If Not boolAddHeaderInfo Then
                                        strExportHeader = String.Empty
                                    End If
                                Case ""
                                    common.AddDelimeterToString(strExportText, CType(export.ExportItems(intLoop), Array).GetValue(1))
                                Case Else
                                    Dim index As Integer = intLoop
                                    Dim pfIndex As Integer = 0
                                    Dim tmpArrList As New ArrayList
                                    'Finds the component name
                                    Dim compName As String() = CStr(CType(export.ExportItems(intLoop), Array).GetValue(0)).Split("'")
                                    If compName.Length > 1 Then
                                        tmpArrList = common.GetListOfGagesPerComponent(pFile, compName(1))
                                    Else
                                        tmpArrList = common.GetListOfGagesPerComponent(pFile, compName(0))
                                    End If
                                    If boolRepeater Then
                                        While index < export.ExportItems.Count - 1 And Not CType(export.ExportItems(intLoop), Array).GetValue(0) = "Field - <End Repeater>"
                                            If Not pFile.Gages(pfIndex).GageType = "Null Indicator" Then
                                                If CStr(CType(export.ExportItems(index), Array).GetValue(0)).StartsWith("Field - All") Then
                                                    strExportText += ConvertExportStringToData(CType(export.ExportItems(index), Array).GetValue(0), intCurrentPartNumber, pfIndex, common.GetListOfComponents(pFile), tmpArrList)
                                                    common.AddDelimeterToString(strExportText, CType(export.ExportItems(index), Array).GetValue(1))
                                                    'Only for DFQ Export Format
                                                ElseIf CStr(CType(export.ExportItems(index), Array).GetValue(0)).Contains("K2") Then
                                                    strExportText += ConvertExportStringToData(CType(export.ExportItems(index), Array).GetValue(0), intCurrentPartNumber, pfIndex, common.GetListOfComponents(pFile), tmpArrList) & "/" & DFQ_PointCounter
                                                    common.AddDelimeterToString(strExportText, CType(export.ExportItems(index), Array).GetValue(1))
                                                    'Only for DFQ Export Format
                                                ElseIf CStr(CType(export.ExportItems(index), Array).GetValue(0)).Contains("K85") Then
                                                    strExportText += ConvertExportStringToData(CType(export.ExportItems(index), Array).GetValue(0), intCurrentPartNumber, pfIndex, common.GetListOfComponents(pFile), tmpArrList) & "/" & DFQ_PointCounter
                                                    common.AddDelimeterToString(strExportText, CType(export.ExportItems(index), Array).GetValue(1))
                                                Else
                                                    If exportFile.Extension = ".DFQ" Then
                                                        If CType(export.ExportItems(index), Array).GetValue(0) = "Field - Date Time" Then
                                                            If DFQDateCheck = False And DFQDateCount = 0 Then
                                                                strExportText += ConvertExportStringToData(CType(export.ExportItems(index), Array).GetValue(0), intCurrentPartNumber, pfIndex, common.GetListOfComponents(pFile), tmpArrList)
                                                                common.AddDelimeterToString(strExportText, CType(export.ExportItems(index), Array).GetValue(1))
                                                                DFQDateCheck = True
                                                                DFQDateCount = DFQDateCount + 1
                                                            End If
                                                        Else
                                                            strExportText += ConvertExportStringToData(CType(export.ExportItems(index), Array).GetValue(0), intCurrentPartNumber, pfIndex, common.GetListOfComponents(pFile), tmpArrList)
                                                            common.AddDelimeterToString(strExportText, CType(export.ExportItems(index), Array).GetValue(1))
                                                            DFQDateCheck = False
                                                        End If
                                                    Else
                                                        strExportText += ConvertExportStringToData(CType(export.ExportItems(index), Array).GetValue(0), intCurrentPartNumber, pfIndex, common.GetListOfComponents(pFile), tmpArrList)
                                                        common.AddDelimeterToString(strExportText, CType(export.ExportItems(index), Array).GetValue(1))
                                                    End If
                                                End If
                                                'If end of Characteristic Line increment +1 only for DFQ format.
                                                If CStr(CType(export.ExportItems(index), Array).GetValue(0)).Contains("K8501") Then
                                                    DFQ_PointCounter = DFQ_PointCounter + 1
                                                End If
                                                index += 1
                                                'exit the while loop
                                                If index = export.ExportItems.Count Or CType(export.ExportItems(index), Array).GetValue(0) = "Field - <End Repeater>" Then
                                                    pfIndex += 1
                                                    'If pfIndex = pFile.Gages.Count - 1 Then
                                                    If pfIndex = pFile.Gages.Count Then
                                                        intLoop = index - 1
                                                        Exit While
                                                    Else
                                                        index = intLoop
                                                    End If
                                                End If
                                            End If
                                        End While
                                    Else
                                        If CStr(CType(export.ExportItems(intLoop), Array).GetValue(0)).StartsWith("Field - All") Then
                                            For intLoop2 = 0 To pFile.Gages.Count - 1
                                                If Not pFile.Gages(intLoop2).GageType = "Null Indicator" Then
                                                    strExportText += ConvertExportStringToData(CType(export.ExportItems(intLoop), Array).GetValue(0), intCurrentPartNumber, intLoop2, common.GetListOfComponents(pFile), tmpArrList)
                                                    If Not intLoop2 = pFile.Gages.Count - 1 Then
                                                        common.AddDelimeterToString(strExportText, CType(export.ExportItems(intLoop), Array).GetValue(1))
                                                    Else
                                                        If Not intLoop = export.ExportItems.Count - 1 Then
                                                            common.AddDelimeterToString(strExportText, CType(export.ExportItems(intLoop), Array).GetValue(1))
                                                        End If
                                                    End If
                                                End If
                                            Next
                                        ElseIf CStr(CType(export.ExportItems(intLoop), Array).GetValue(0)).StartsWith("Field - Component") And _
                                               CStr(CType(export.ExportItems(intLoop), Array).GetValue(0)).EndsWith("Readings") Then
                                            Dim tmpSavedData As cSavedData
                                            For intLoop2 = 0 To tmpArrList.Count - 1
                                                For Each tmpSavedData In tmpSavedInfo.savedDataArrList
                                                    If tmpSavedData.Characteristic = CType(tmpArrList(intLoop2), PartfileLibrary.Gages).Characteristic And CType(tmpArrList(intLoop2), PartfileLibrary.Gages).Active Then
                                                        strExportText += tmpSavedData.Value
                                                        common.AddDelimeterToString(strExportText, CType(export.ExportItems(index), Array).GetValue(1))
                                                    End If
                                                Next
                                            Next
                                        Else
                                            If masterSettings.masterRuntime.masterCheckSequenceLogic = True And Already_in_Loop = False Then
                                                If Sensor_Final_Quality1 = "Go \No Go Fail 1" And Sensor_Final_Quality2 = "Go \No Go Fail 2" Then
                                                    strExportText += ConvertExportStringToData(CType(export.ExportItems(intLoop), Array).GetValue(0), intCurrentPartNumber, intLoop2, common.GetListOfComponents(pFile), tmpArrList) & "," & "Sensor Port Fail" & "," & "Inlet Fitting Fail" & ","
                                                    Sensor_Final_Quality1 = ""
                                                    Sensor_Final_Quality2 = ""
                                                    Already_in_Loop = True
                                                ElseIf Sensor_Final_Quality1 = "" And Sensor_Final_Quality2 = "" Then
                                                    strExportText += ConvertExportStringToData(CType(export.ExportItems(intLoop), Array).GetValue(0), intCurrentPartNumber, intLoop2, common.GetListOfComponents(pFile), tmpArrList) & "," & "Sensor Port Pass" & "," & "Inlet Fitting Pass" & ","
                                                    Sensor_Final_Quality1 = ""
                                                    Sensor_Final_Quality2 = ""
                                                    Already_in_Loop = True
                                                ElseIf Sensor_Final_Quality1 = "" And Sensor_Final_Quality2 = "Go \No Go Fail 2" Then
                                                    strExportText += ConvertExportStringToData(CType(export.ExportItems(intLoop), Array).GetValue(0), intCurrentPartNumber, intLoop2, common.GetListOfComponents(pFile), tmpArrList) & "," & "Sensor Port Pass" & "," & "Inlet Fitting Fail" & ","
                                                    Sensor_Final_Quality1 = ""
                                                    Sensor_Final_Quality2 = ""
                                                    Already_in_Loop = True
                                                ElseIf Sensor_Final_Quality1 = "Go \No Go Fail 1" And Sensor_Final_Quality2 = "" Then
                                                    strExportText += ConvertExportStringToData(CType(export.ExportItems(intLoop), Array).GetValue(0), intCurrentPartNumber, intLoop2, common.GetListOfComponents(pFile), tmpArrList) & "," & "Sensor Port Fail" & "," & "Inlet Fitting Pass" & ","
                                                    Sensor_Final_Quality1 = ""
                                                    Sensor_Final_Quality2 = ""
                                                    Already_in_Loop = True
                                                End If
                                            ElseIf masterSettings.MasterSequenceSettings.MasterEnableChkSequence = True And masterSettings.MasterSequenceSettings.MasterSequenceFlag1 = "Line 1" And masterSettings.MasterSequenceSettings.MasterSequenceFlag2 = "Line 2" Then
                                                If SequenceInput_Export1 = True Then
                                                    strExportText += ConvertExportStringToData(CType(export.ExportItems(intLoop), Array).GetValue(0), intCurrentPartNumber, intLoop2, common.GetListOfComponents(pFile), tmpArrList) & ","
                                                End If
                                                If SequenceInput_Export2 = True Then
                                                    strExportText += ConvertExportStringToData(CType(export.ExportItems(intLoop), Array).GetValue(0), intCurrentPartNumber, intLoop2, common.GetListOfComponents(pFile), tmpArrList) & ","
                                                End If
                                            Else
                                                If pFile.Gages(0).GageType.StartsWith("G-300") Then
                                                    If AISBoxWriteData = True Then
                                                        strExportText += ConvertExportStringToData(CType(export.ExportItems(intLoop), Array).GetValue(0), intCurrentPartNumber, intLoop2, common.GetListOfComponents(pFile), tmpArrList) '& ","
                                                        AISBoxWriteData = False
                                                    Else
                                                        strExportText += ConvertExportStringToData(CType(export.ExportItems(intLoop), Array).GetValue(0), intCurrentPartNumber, intLoop2, common.GetListOfComponents(pFile), tmpArrList) '& "," & "BUMPERABORT"
                                                    End If
                                                    'strExportText += ConvertExportStringToData(CType(export.ExportItems(intLoop), Array).GetValue(0), intCurrentPartNumber, intLoop2, common.GetListOfComponents(pFile), tmpArrList) & "," & "BUMPERABORT"
                                                Else
                                                    strExportText += ConvertExportStringToData(CType(export.ExportItems(intLoop), Array).GetValue(0), intCurrentPartNumber, intLoop2, common.GetListOfComponents(pFile), tmpArrList) '& ","
                                                End If
                                                If Not intLoop = export.ExportItems.Count - 1 Then
                                                    common.AddDelimeterToString(strExportText, CType(export.ExportItems(intLoop), Array).GetValue(1))
                                                End If
                                            End If
                                        End If
                                    End If
                            End Select
                        Next

Open in new window

cmdolcetAuthor Commented:
Running your code above I get the following issue
Capture.PNG
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Obviously, you must change "VARCHARC" to "VARCHAR".
cmdolcetAuthor Commented:
The code below creates a database. I am not able to create a table and the columns in the code below, however no errors were triggered.
What am I doing wrong ?

  Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim obj As SqlCommand
        Dim strSQL As String
        Dim ServerName As String = "LMI167\DBTEST"
        Dim DatabaseName As String = "Hello123A2233445454534543"
        Dim conStr As String = "Server=" & ServerName & ";Database=;Trusted_Connection = yes"
        Dim objCon As New SqlConnection(conStr)
        ' Create the database
        objCon.Open()
        obj = objCon.CreateCommand()
        strSQL = "CREATE DATABASE " & DatabaseName
        ' Execute
        obj.CommandText = strSQL
        obj.ExecuteNonQuery()
        objCon.Close()

        objCon.Open()
        obj = objCon.CreateCommand()

        strSQL = "CREATE TABLE  DATA3 (Time VARCHAR(30), Date VARCHAR(30),Barcode VARCHAR (30)"
        For i As Integer = 1 To 53
            strSQL = strSQL & ",Col" & i.ToString & " VARCHAR(30)"
        Next i
        strSQL = strSQL & ")"
        ' Execute
        obj.CommandText = strSQL
        obj.ExecuteNonQuery()
        objCon.Close()
    End Sub

Open in new window

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I don't use the syntax you're using, but it doesn't appear that you've assigned the connection to your SQLCommand object. Try this instead:

Using con As New SQLConnection(YourConnectionStringHere)
  con.Open
  Using cmd As New SQLCommand
    cmd.Connection = con
    cmd.CommandText = "CREATE DATABASE " & YourDatabaseName
  End Using
End Using

After that you'd open new connection that actually connects to the new Database, and use that connection to issue the CREATE TABLE statements.

Also use a Try - Catch around your code to catch any errors you may have ...
cmdolcetAuthor Commented:
Scott,

The database is created just fine. Its the code below that does not create the table.It throws no errors that my issue.
  objCon.Open()
        obj = objCon.CreateCommand()

        strSQL = "CREATE TABLE  DATA3 (Time VARCHAR(30), Date VARCHAR(30),Barcode VARCHAR (30)"
        For i As Integer = 1 To 53
            strSQL = strSQL & ",Col" & i.ToString & " VARCHAR(30)"
        Next i
        strSQL = strSQL & ")"
        ' Execute
        obj.CommandText = strSQL
        obj.ExecuteNonQuery()
        objCon.Close()

Open in new window

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Refer to my earlier comment:

After that you'd open new connection that actually connects to the new Database, and use that connection to issue the CREATE TABLE statements.

You must use a different connection string and connect directly to the new database, something like this:

Using con As New SQLConnection("Server=" & ServerName & ";Database=" & DatabaseName & ";Trusted_Connection=True;
  con.Open
  Using cmd As New SQLCommand
    cmd.Connection = con
    strSQL = <Create your CommandText here>
    cmd.CommandText = strSQL
    cmd.ExecuteNonQuery
End Using
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.