Importing Excel data into SQL Server database

Question

I want to import Excel chart into my SQL Server database by select it by dialog box and automatically save it to the SQL Server table + I want to generate the record ID by the sub that I made.

The sub that generates the MembersID is:

Public MyNewMembersID As Integer
Public Sub Code_Members()
    Dim dt As New DataTable
    Dim da As New SqlDataAdapter("select max(MembersID) from Members", Con)
    da.Fill(dt)
    If IsDBNull(dt(0)(0)) = True Then
        MyNewMembersID = 1
    Else
        MyNewMembersID = dt(0)(0) + 1
    End If

This is my code that errors with a message that MembersID must not be NULL:

Imports System.IO
Imports System.Data
Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports System.Configuration

Public Class MembersImport
    Private Sub MembersImport_FilePath_Button_Click(sender As Object, e As EventArgs) Handles MembersImport_FilePath_Button.Click
        MembersImport_OpenFileDialog.ShowDialog()
    End Sub

    Private Sub MembersImport_ImportFile_Button_Click(sender As Object, e As EventArgs) Handles MembersImport_ImportFile_Button.Click
        If MembersImport_FilePath_TextBox.Text = "" Then
            MsgBox("Please Select File Excel", MsgBoxStyle.RetryCancel, "File Not Found")
            MembersImport_FilePath_Button.PerformClick()
        Else
            Dim con_excel As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & MembersImport_FilePath_TextBox.Text & "';Extended Properties=""Excel 12.0 Xml;HDR=Yes""")
            con_excel.Open()
            Dim query_excel As String = "Select * from [Sheet1$]"
            Dim cmd As OleDbCommand = New OleDbCommand(query_excel, con_excel)
            Dim rd As OleDbDataReader
            Dim con_sql As New SqlConnection()
            Dim con_sqlDB As String = "Data Source=(localdb)\ProjectsV13;Initial Catalog=Euro_SQL_Server;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=True;ApplicationIntent=ReadWrite;MultiSubnetFailover=False"
            con_sql.ConnectionString = con_sqlDB
            con_sql.Open()
            Using BulkCopy As SqlBulkCopy = New SqlBulkCopy(con_sql)
                BulkCopy.DestinationTableName = "dbo.Members"
                Try
                    rd = cmd.ExecuteReader
                    BulkCopy.WriteToServer(rd)
                    rd.Close()
                    con_sql.Close()
                    MsgBox("تم الاسترداد بنجاح")
                    MembersImport_FilePath_TextBox.Text = ""
                Catch ex As Exception
                    MsgBox(ex.ToString)
                End Try
            End Using
            Load_Members()
            Me.Close()
        End If
    End Sub

    Private Sub MembersImport_OpenFileDialog_FileOk(sender As Object, e As System.ComponentModel.CancelEventArgs) Handles MembersImport_OpenFileDialog.FileOk
        MembersImport_FilePath_TextBox.Text = MembersImport_OpenFileDialog.FileName
    End Sub
End Class

And this is my table structure:

CREATE TABLE [dbo].[Members] 
(
    [MembersID] INT NOT NULL,
    [MembersName] NVARCHAR(MAX) NULL,
    [MembersGender] NVARCHAR(MAX) NULL,
    [MembersPhone] NVARCHAR(MAX) NULL,
    [MembersAddress] NVARCHAR(MAX) NULL,
    [MembersMembershiping] BIT NULL,
    [MembersMembershipNum] NVARCHAR (MAX) NULL,
    [MembersMembershipValidityFrom] DATE NULL,
    [MembersMembershipValidityTo] DATE NULL,
    [MembersSubscriberSystem] NVARCHAR(MAX) NULL,
    [MembersCarBrand] NVARCHAR(MAX) NULL,
    [MembersCarModel] NVARCHAR(MAX) NULL,
    [MembersCarManufacturingYear] NVARCHAR(MAX) NULL,
    [MembersCarNum] NVARCHAR(MAX) NULL,
    [MembersChassisNum] NVARCHAR(MAX) NULL,
    [MembersCarColor] NVARCHAR(MAX) NULL,
    [MembersNotes] NVARCHAR(MAX) NULL,
    [ActionBy] NVARCHAR(MAX) NULL,

    PRIMARY KEY CLUSTERED ([MembersID] ASC)
);

So all I need is to import the data that is in the Excel with generated MembersID into the SQL Server database

Answer 1

You don't generate the ID in the code. You generate the ID by making the ID field an identity field.

Answer 2

That's a lot of MAX! I guess you are taking it to the MAX. Anyway, can you try it like this? The script below should do what you want.

Public Sub ImportDataFromExcel(excelFilePath As String) 
        'declare variables - edit these based on your particular situation 
        Dim ssqltable As String = "Table1" 
        ' make sure your sheet name is correct, here sheet name is sheet1, so you can change your sheet name if have    different 
        Dim myexceldataquery As String = "select student,rollno,course from [sheet1$]" 
        Try 
            'create our connection strings 
            Dim sexcelconnectionstring As String = (Convert.ToString("provider=microsoft.jet.oledb.4.0;data source=") & excelFilePath) + ";extended properties=" + """excel 8.0;hdr=yes;""" 
            Dim ssqlconnectionstring As String = "Data Source=SAYYED;Initial Catalog=SyncDB;Integrated Security=True" 
            'execute a query to erase any previous data from our destination table 
            Dim sclearsql As String = Convert.ToString("delete from ") & ssqltable 
            Dim sqlconn As New SqlConnection(ssqlconnectionstring) 
            Dim sqlcmd As New SqlCommand(sclearsql, sqlconn) 
            sqlconn.Open() 
            sqlcmd.ExecuteNonQuery() 
            sqlconn.Close() 
            'series of commands to bulk copy data from the excel file into our sql table 
            Dim oledbconn As New OleDbConnection(sexcelconnectionstring) 
            Dim oledbcmd As New OleDbCommand(myexceldataquery, oledbconn) 
            oledbconn.Open() 
            Dim dr As OleDbDataReader = oledbcmd.ExecuteReader() 
            Dim bulkcopy As New SqlBulkCopy(ssqlconnectionstring) 
            bulkcopy.DestinationTableName = ssqltable 
            While dr.Read() 
                bulkcopy.WriteToServer(dr) 
            End While 
            dr.Close() 
            oledbconn.Close() 
            Label1.Text = "File imported into sql server." 
            'handle exception 
        Catch ex As Exception 
        End Try 
    End Sub