troubleshooting Question

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

Avatar of cmdolcet
cmdolcetFlag for United States of America asked on
Visual Basic.NETSQL
14 Comments2 Solutions84 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Kyle Abrahams
Director of Information Technology

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 14 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 14 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros