VS2008 VB.net Excel.csv load into sql2000 assistance

janviero
janviero used Ask the Experts™
on
Hi Pros!

I am a newbie that is trying to insert a .csv into sql 2000 using a web page I created. I was able to make it work in vb6 and Access, but am lost in vs2008/Sql. Could you look at my code and tell me what I am missing? I see so many different methods on the web I do not know what is what any more. One particular issue I am having with this no is the spaces in the names: [Parameter 1].

Is there no easy wizard to set all this up? Your help is realllllly appreciated!
Imports Microsoft.Office.Interop
Imports System.Data.SqlClient
Imports System.IO
Imports Microsoft.Office.core
Imports System.Web.DynamicData
Imports System.Data
Imports System.Web.UI.HtmlControls
Imports System.Data.OleDb
Imports System.Configuration




Partial Public Class _Default
    Inherits System.Web.UI.Page
    Protected WithEvents FileName As System.Web.UI.HtmlControls.HtmlGenericControl
    Protected WithEvents FileContent As System.Web.UI.HtmlControls.HtmlGenericControl
    Protected WithEvents FileSize As System.Web.UI.HtmlControls.HtmlGenericControl
    Protected WithEvents postedFile As System.Web.UI.HtmlControls.HtmlInputFile
    '    Protected WithEvents Span1 As System.Web.UI.HtmlControls.HtmlGenericControl
    '    Protected WithEvents Span2 As System.Web.UI.HtmlControls.HtmlGenericControl
    '    Protected WithEvents UploadDetails As System.Web.UI.HtmlControls.HtmlGenericControl
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    End Sub
    Protected Severity As String
    Protected Computer As String
    Protected Source As String
    Protected [Alert Name] As Char
    Protected [Date] As DateTime
    Protected Time As DateTime
    Protected [Parameter 1] As string
    Protected [Parameter 2] As string
    Protected [Parameter 3] As string
    Protected [Parameter 4] As string
    Protected [Parameter 5] As string
    Protected [Parameter 6] As string


    Public Sub btnload_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnLoad.Click



        If FileUpload1.HasFile Then

            Dim postedFile As System.Web.HttpPostedFile = FileUpload1.PostedFile
            Dim FileName As String = Path.GetFileName(postedFile.FileName)
            Dim FileContent As String = postedFile.ContentType
            Dim FileSize As Integer = postedFile.ContentLength

        End If






        Dim strSQL As String
        strSQL = "INSERT INTO tblVirusScan(Severity, Computer, Source, [Alert Name], [Date], [Time], [Parameter 1], [Parameter 2], [Parameter 3], [Parameter 4], [Parameter 5], [Parameter 6])" & _
             "VALUES(" & Severity & ", '" & Computer & "', '" & Source & "', '" & "Alert Name" & "', '" & [Date] & "', '" & [Time] & "', '" & "Parameter 1" & "', '" & "Parameter 2" & "', '" & "Parameter 3" & "', '" & "Parameter 4" & "', '" & "Parameter 5" & "', '" & "Parameter 6" & "','" & FileUpload1.PostedFile.FileName & "','" & FileUpload1.PostedFile.ContentType & "', '" & FileUpload1.PostedFile.ContentLength & "')"


        Dim myConnection As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("VirusScanConnectionString2").ConnectionString)
        myConnection.Open()
        Dim myCommand As SqlCommand = New SqlCommand(strSQL, myConnection)
        myCommand.CommandType = Data.CommandType.Text
        myCommand.ExecuteNonQuery()
        myConnection.Close()

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Reza RadConsultant, Trainer
Commented:
>>Is there no easy wizard to set all this up? Your help is realllllly appreciated!
there is !
use SSIS (sql server integration service)
with ssis you can transfer data from any source to any destination
for example from .csv source to sql server destination.
you only need integration service of sql server installed on the machine
then create a package with wizards.
and then run it from your application
Sr. System Analyst
Commented:
here is the issue

you provide 12 columns, but trying to insert more than 12 in the values (....) part...
        strSQL = "INSERT INTO tblVirusScan(Severity, Computer, Source, [Alert Name], [Date], [Time], [Parameter 1], [Parameter 2], [Parameter 3], [Parameter 4], [Parameter 5], [Parameter 6])" & _
                 "VALUES(" & Severity & ", '" & Computer & "', '" & Source & "', '" & "Alert Name" & "', '" & [Date] & "', '" & [Time] & "', '" & "Parameter 1" & "', '" & "Parameter 2" & "', '" & "Parameter 3" & "', '" & "Parameter 4" & "', '" & "Parameter 5" & "', '" & "Parameter 6" & "','" & FileUpload1.PostedFile.FileName & "','" & FileUpload1.PostedFile.ContentType & "', '" & FileUpload1.PostedFile.ContentLength & "')"

Open in new window

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