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.
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
Visual Basic.NET
Last Comment
Scott McDaniel (EE MVE )
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 ..........................
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 ..........................
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.
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?
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?
ASKER
I would need to add 50 columns
Date Time Barcode and 47 readings columns labeled 1-47
Date Time Barcode and 47 readings columns labeled 1-47
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
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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
ASKER
Running your code above I get the following issue
Capture.PNG
Capture.PNG
Obviously, you must change "VARCHARC" to "VARCHAR".
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 ?
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
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(YourConnecti
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 ...
Using con As New SQLConnection(YourConnecti
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 ...
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.
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()
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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))"