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

cmdolcet
cmdolcet used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Kyle AbrahamsSenior .Net Developer

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

Author

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 Developer

Commented:
Where are you storing your values that need to be inserted?
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Author

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 Developer

Commented:
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

Author

Commented:
what is the "int" declared as?
Kyle AbrahamsSenior .Net Developer

Commented:
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

Author

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

Author

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 Developer

Commented:
you need both:

insert into table (col1, col2, col3)
values (val1, val2, val3)

Author

Commented:
Kyle what do you mean I need both? In the code example above how can I do that?
Senior .Net Developer
Commented:
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

Author

Commented:
Kyle I am still returning "NULL"

See screen shot
Capture.PNG
Kyle AbrahamsSenior .Net Developer
Commented:
obj.CommandText = strSQL
  obj.ExecuteNonQuery
        objCon.Close()

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial