Avatar of cmdolcet
cmdolcet
Flag for United States of America asked on

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

Visual Basic.NETSQL

Avatar of undefined
Last Comment
Kyle Abrahams

8/22/2022 - Mon
Kyle Abrahams

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

ASKER
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 Abrahams

Where are you storing your values that need to be inserted?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
cmdolcet

ASKER
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 Abrahams

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

cmdolcet

ASKER
what is the "int" declared as?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Kyle Abrahams

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
cmdolcet

ASKER
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
cmdolcet

ASKER
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
Your help has saved me hundreds of hours of internet surfing.
fblack61
Kyle Abrahams

you need both:

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

ASKER
Kyle what do you mean I need both? In the code example above how can I do that?
ASKER CERTIFIED SOLUTION
Kyle Abrahams

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
cmdolcet

ASKER
Kyle I am still returning "NULL"

See screen shot
Capture.PNG
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.