asked on

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

Scott McDaniel (EE MVE )

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))"

cmdolcet

ASKER

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 (EE MVE )

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.

cmdolcet

ASKER

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 (EE MVE )

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?

cmdolcet

ASKER

I would need to add 50 columns
Date Time Barcode and 47 readings columns labeled 1-47

cmdolcet

ASKER

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

ASKER CERTIFIED SOLUTION

Scott McDaniel (EE MVE )

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

cmdolcet

ASKER

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

cmdolcet

ASKER

Running your code above I get the following issue
Capture.PNG

Scott McDaniel (EE MVE )

Obviously, you must change "VARCHARC" to "VARCHAR".

cmdolcet

ASKER

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 (EE MVE )

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 ...

cmdolcet

ASKER

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

SOLUTION

Scott McDaniel (EE MVE )

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial