Link to home
Start Free TrialLog in
Avatar of cmdolcet
cmdolcetFlag 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

Avatar of Kyle Abrahams, PMP
Kyle Abrahams, PMP
Flag of United States of America image

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.
Avatar of 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.
Where are you storing your values that need to be inserted?
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,.........}
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

what is the "int" declared as?
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
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
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
you need both:

insert into table (col1, col2, col3)
values (val1, val2, val3)
Kyle what do you mean I need both? In the code example above how can I do that?
ASKER CERTIFIED SOLUTION
Avatar of Kyle Abrahams, PMP
Kyle Abrahams, PMP
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Kyle I am still returning "NULL"

See screen shot
Capture.PNG
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial