We help IT Professionals succeed at work.

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

78 Views
Last Modified: 2019-02-06
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

Kyle AbrahamsSenior .Net Developer
CERTIFIED EXPERT

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
CERTIFIED EXPERT

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

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
CERTIFIED EXPERT

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
CERTIFIED EXPERT

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
CERTIFIED EXPERT

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
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Kyle I am still returning "NULL"

See screen shot
Capture.PNG
Kyle AbrahamsSenior .Net Developer
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.