Insert Data into table using vb.net into SQL table.

I have code below that will create a DB and then add a datatable to it. The last bit of code on "button3" I want to insert data into the table and columns. I am getting a little confused with how how should structure my sQL command that will allow me to add in 53 values into the 53 columns. In total I have 56 columns so how can I include the data for the DATE,TIME,Barcode,Reading1....n....


Imports System.Data.SqlClient
Public Class Form1
    Private ServerName As String = "LMI167\DBTEST"
    Private DatabaseName As String = "D12345"
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim obj As SqlCommand
        Dim strSQL As String

        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()
    End Sub
    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        Dim conStr As String = "Server=" & ServerName & ";Database=" & DatabaseName & ";Trusted_Connection = yes"
        Dim objCon As New SqlConnection(conStr)
        Dim obj As SqlCommand
        Dim strSQL As String
        ' Create a table
        objCon = New SqlConnection(conStr)
        objCon.Open()
        obj = objCon.CreateCommand()
        strSQL = "CREATE TABLE  DATA3 (Date VARCHAR(30),Time VARCHAR(30),Barcode VARCHAR (30)"
        For i As Integer = 1 To 53
            strSQL = strSQL & ",Reading" & i.ToString & " VARCHAR(30)"
        Next i
        strSQL = strSQL & ")"
        ' Execute
        obj.CommandText = strSQL
        Try
            obj.ExecuteNonQuery()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
        objCon.Close()
        objCon = Nothing
    End Sub
    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        Dim conStr As String = "Server=" & ServerName & ";Database=" & DatabaseName & ";Trusted_Connection = yes"
        Dim objCon As New SqlConnection(conStr)
        Dim obj As SqlCommand
        Dim strSQL As String
        ' Inserts Data into Table
        objCon = New SqlConnection(conStr)
        objCon.Open()
        obj = objCon.CreateCommand()
        strSQL = "insert into DATA3 ([Date],[Time],[Barcode]"
        For i As Integer = 1 To 53
            strSQL = strSQL & ",[Reading" & i.ToString & "]"
        Next i
        strSQL = strSQL & ")"
        obj.CommandText = strSQL
        objCon.Close()
        objCon = Nothing
    End Sub
End Class

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.

Kyle AbrahamsSenior .Net DeveloperCommented:
You're only specifying the columns, you need to specify the values after that:

EG:
insert into DATA3 ([Date],[Time],[Barcode], [Reading1], [Reading2], ...)


ADD:
VALUES (Now, Now, Barcode, ActualValueForReading1, ActualValueForReading2, ...)

Also please close your other question if you got it working.
cmdolcetAuthor Commented:
Kyle,

So using the following code
strSQL = "insert into DATA3 ([Date],[Time],[Barcode]"
        For i As Integer = 1 To 53
            strSQL = strSQL & ",[Reading" & i.ToString & "]"
        Next i
        strSQL = strSQL & ")"

Open in new window


How can I do that. If I had only 4 fields to insert yes that would be easier but I have over 59 fields to add data to.
Kyle AbrahamsSenior .Net DeveloperCommented:
Where are you storing your values that need to be inserted?
Starting with Angular 5

Learn the essential features and functions of the popular JavaScript framework for building mobile, desktop and web applications.

cmdolcetAuthor Commented:
Kyle you mean like where Am I pulling my values from sorry.

I am storing them inside an array list.
Just as an example

arraylist1 = {1,2,3,4,5,6,7,8,9,.........}
Kyle AbrahamsSenior .Net DeveloperCommented:
Yep:

After you complete the loop the first time you would do:

'append the values to the insert.
strSql +=  " VALUES ( '" & Now & "','" & Now &  "', '" &  Barcode & "'," 
for each i as int in arraylist1
    strSql +=  i & ","
next
strSql += ")"

Open in new window

cmdolcetAuthor Commented:
what is the "int" declared as?
Kyle AbrahamsSenior .Net DeveloperCommented:
int may be integer . . . sorry used to C#.

for each i as integer in arraylist1
    strSql +=  i & ","
next

Open in new window


basically I'm looping over the arraylist.

For every value it's temporarily stored in i.

https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/statements/for-each-next-statement
cmdolcetAuthor Commented:
Yes sorry! I got it anyways. I have attached a screen shot of what is coming out. I checked the sqlExpress 2014 database and nothing is writing to the fields.

See screen shot.
Capture.PNG
cmdolcetAuthor Commented:
I changed the code above to the following to get rid of the "," For whateever reason it is still not writing values.

  Dim conStr As String = "Server=" & ServerName & ";Database=" & DatabaseName & ";Trusted_Connection = yes"
        Dim objCon As New SqlConnection(conStr)
        Dim obj As SqlCommand
        Dim strSQL As String
        Dim DateNow As Date = Date.Now
        Dim TimeNow As String = "12:00:00"
        Dim Barcode As String = "123456789"
        Dim intloop As Integer = 0
        Dim Arraylist1 As New ArrayList()

        For j As Integer = 1 To 53
            Arraylist1.Add(j)
        Next
        ' Inserts Data into Table
        objCon = New SqlConnection(conStr)
        objCon.Open()
        obj = objCon.CreateCommand()

        strSQL += " VALUES ( '" & Now & "','" & Now & "', '" & Barcode & "',"
        For Each i As Integer In Arraylist1
            strSQL += "," & i
        Next
        strSQL += ")"

        obj.CommandText = strSQL
        objCon.Close()
        objCon = Nothing

Open in new window

Capture.PNG
Kyle AbrahamsSenior .Net DeveloperCommented:
you need both:

insert into table (col1, col2, col3)
values (val1, val2, val3)
cmdolcetAuthor Commented:
Kyle what do you mean I need both? In the code example above how can I do that?
Kyle AbrahamsSenior .Net DeveloperCommented:
The first part creates the insert into table section:

strSQL = "insert into DATA3 ([Date],[Time],[Barcode]"
        For i As Integer = 1 To 53
            strSQL = strSQL & ",[Reading" & i.ToString & "]"
        Next i
        strSQL = strSQL & ")"

Open in new window


Then you need to append the values:


        strSQL += " VALUES ( '" & Now & "','" & Now & "', '" & Barcode & "',"
        For Each i As Integer In Arraylist1
            strSQL += "," & i
        Next
        strSQL += ")"

Open in new window


Full code below:

Dim conStr As String = "Server=" & ServerName & ";Database=" & DatabaseName & ";Trusted_Connection = yes"
        Dim objCon As New SqlConnection(conStr)
        Dim obj As SqlCommand
        Dim strSQL As String
        Dim DateNow As Date = Date.Now
        Dim TimeNow As String = "12:00:00"
        Dim Barcode As String = "123456789"
        Dim intloop As Integer = 0
        Dim Arraylist1 As New ArrayList()

        For j As Integer = 1 To 53
            Arraylist1.Add(j)
        Next
        ' Inserts Data into Table
        objCon = New SqlConnection(conStr)
        objCon.Open()
        obj = objCon.CreateCommand()

        strSQL = "insert into DATA3 ([Date],[Time],[Barcode]"
          For i As Integer = 1 To 53
            strSQL += ",[Reading" & i.ToString & "]"
        Next i
        strSQL +=  ")"

        strSQL += " VALUES ( '" & Now & "','" & Now & "', '" & Barcode & "',"
        For Each i As Integer In Arraylist1
            strSQL += "," & i
        Next
        strSQL += ")"

        obj.CommandText = strSQL
        objCon.Close()
        objCon = Nothing

Open in new window

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:
Kyle I am still returning "NULL"

See screen shot
Capture.PNG
Kyle AbrahamsSenior .Net DeveloperCommented:
obj.CommandText = strSQL
  obj.ExecuteNonQuery
        objCon.Close()
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.