UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> VBA / SQL Server Help!    
 
   
RchlE
post May 30 2016, 09:07 AM
Post#1



Posts: 3
Joined: 30-May 16



So i'm having a problem, i want to import an xlsx file into a datagridview and then save it in SQL server....
but it always says that the code as an error "An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

Additional information: An explicit value for the identity column in table 'MAILS' can only be specified when a column list is used and IDENTITY_INSERT is ON.
"
i just... i can't figure this out. this is my code so far:

CODE
Imports System.IO
Imports System.Data
Imports System.Data.OleDb
Imports System.Data.SqlClient
Public Class Importar
    Dim conn As OleDbConnection
    Dim dtr As OleDbDataReader
    Dim dta As OleDbDataAdapter
    Dim cmd As OleDbCommand
    Dim dts As DataSet
    Dim excel As String
    Dim counter As Integer


    Private Sub btnImportar_Click(sender As Object, e As EventArgs) Handles btnImportar.Click
        OpenFileDialog1.InitialDirectory = My.Computer.FileSystem.SpecialDirectories.MyDocuments '"C:\Users\Utilizador\Documents"
        OpenFileDialog1.Filter = "All Files (*.*)|*.*|Excel files (*.xlsx)|*.xlsx|CSV Files (*.csv)|*.csv|XLS Files (*.xls)|*xls"
        If (OpenFileDialog1.ShowDialog(Me) = System.Windows.Forms.DialogResult.OK) Then
            Dim fi As New FileInfo(OpenFileDialog1.FileName)
            Dim FileName As String = OpenFileDialog1.FileName
            excel = fi.FullName
            conn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excel + ";Extended Properties=Excel 12.0;") 'provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\vb.net-informations.xls';Extended Properties=Excel 8.0;")
            dta = New OleDbDataAdapter("Select * From [Folha1$]", conn)
            dts = New DataSet
            dta.Fill(dts, "[Folha1$]")
            dtgFicheiro.DataSource = dts
            dtgFicheiro.DataMember = "[Folha1$]"
        End If
        conn.Close()

        dtgFicheiro.Columns(0).Name = "ID"
        dtgFicheiro.Columns(1).Name = "Nome"
        dtgFicheiro.Columns(2).Name = "Email"

        For Each Row As DataGridViewRow In dtgFicheiro.Rows
            Dim constring As String = "Data Source=DESKTOP-NMOL9GQ\SQLEXPRESS;Initial Catalog=EMAILS;INTEGRATED SECURITY=SSPI"
            Using con As New SqlConnection(constring)
                Using cmd As New SqlCommand("INSERT INTO MAILS VALUES(@ID, @Nome, @Mails)", con)
                    cmd.Parameters.AddWithValue("@ID", Row.Cells("ID").Value)
                    cmd.Parameters.AddWithValue("@Nome", Row.Cells("Nome").Value)
                    cmd.Parameters.AddWithValue("@Mails", Row.Cells("Email").Value)
                    con.Open()
                    cmd.ExecuteNonQuery()
                    con.Close()
                End Using
            End Using
        Next
        MessageBox.Show("Registos guardados.")
    End Sub
End Class





can someone please help me?!
Go to the top of the page
 
GroverParkGeorge
post May 30 2016, 09:39 AM
Post#2


UA Admin
Posts: 31,239
Joined: 20-June 02
From: Newcastle, WA


The error message is telling you what the problem is, although it may be a bit obscure. "An explicit value for the identity column in table 'MAILS' can only be specified when a column list is used and IDENTITY_INSERT is ON."

There is a column in the destination table which is set up as an Identity field. You cannot specify your own value for that field. SQL Server must generate its OWN value using the Identity property.

If you want to override that, and insert a different value, you must first set IDENTITY_INSERT to ON. THis article is about SS 2000 but the process should be the same.
Go to the top of the page
 
jleach
post May 30 2016, 05:09 PM
Post#3


UtterAccess Editor
Posts: 9,814
Joined: 7-December 09
From: Staten Island, NY, USA


Hi, welcome to UA.

This is completely off topic to your question (George has that one covered), but is there any reason you're setting up your connection and commands inside a loop of grid rows instead of outside the loop? The way you have it now, it's creating a brand new connection/command pair for each row you're operating on... I don't see any particular need for it, and the overhead is pretty high on those operations. I'd guess a much better design would be to define the connection and command once, then enter the loop... the parameters will require being cleared/reset (or just having their values updated) each time, but that's easily enough accomplished.

Like so (aircode):

CODE
Using con As New SqlConnection(constring)
    Using cmd As New SqlCommand("INSERT INTO MAILS VALUES(@Nome, @Mails);"), con)
        For Each Row As DataGridViewRow In dtgFicheiro.Rows
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@Nome", Row.Cells("Nome").Value)
            cmd.Parameters.AddWithValue("@Mails", Row.Cells("Email").Value)
            con.Open()
            cmd.ExecuteNonQuery()
            con.Close()
        Next
    End Using
End Using



It may not make much a difference if you only have a small handful of rows in your gridview, but as a general matter of code cleanliness this is typically a much better design (and will be significantly faster when processing larger amounts of records)

Cheers,
Go to the top of the page
 
cheekybuddha
post May 30 2016, 05:13 PM
Post#4


UtterAccess VIP
Posts: 9,277
Joined: 6-December 03
From: Telegraph Hill


Presumably you'd want to move con.Open() and con.Close() outside the loop too, Jack?
Go to the top of the page
 
jleach
post May 30 2016, 05:16 PM
Post#5


UtterAccess Editor
Posts: 9,814
Joined: 7-December 09
From: Staten Island, NY, USA


I'm not sure actually, I'd have to dig around some to see. With ADO.NET's connection pooling, I think this isn't an issue, and I'm actually unsure offhand if you can set parameter values with an open connection. The general .NET practice is "open it just long enough to do what you need", and I don't know if this is good exception case or not.

Good question!
Go to the top of the page
 
RchlE
post May 31 2016, 04:53 AM
Post#6



Posts: 3
Joined: 30-May 16



Thank you all, i've got another question if you don't mind, what should i do to validate de column with the emails before i put it on SS?
Go to the top of the page
 
jleach
post May 31 2016, 05:14 AM
Post#7


UtterAccess Editor
Posts: 9,814
Joined: 7-December 09
From: Staten Island, NY, USA


SS is going to have whatever table/column constraints, but on the VB end you'll just write your own validation logic. This would include a basic check to make sure that the table/column constraints on SS are checked (e.g., don't allow empty strings, or perhaps make sure it's not null), as well as any additional logic you might want to check such as running the value through some regex validator to see if the syntax is value (actual, real validation of an email address can only be done by sending a test email to the address, which is not ideal for standard quick-validation routines).

Generally, your application logic would be split into various pieces (usually three): one for the display/presentation, one for the objects that make up the business logic and one for the data access (this is referred to as a 3-tier application design). In such a case, your presentation and business logic layers would ensure that the values are correct long before it becomes time to submit them to SQL Server (for more info on this setup see the latter portion of this article: http://www.dymeng.com/netcom/overview-of-technologies/)

hth
Go to the top of the page
 
RchlE
post May 31 2016, 05:35 AM
Post#8



Posts: 3
Joined: 30-May 16



Thanks a lot smile.gif
Go to the top of the page
 
jleach
post May 31 2016, 07:20 AM
Post#9


UtterAccess Editor
Posts: 9,814
Joined: 7-December 09
From: Staten Island, NY, USA


Glad to help, stop back with any other questions.

(as it turns out David was right about the connection open/close... this is better suited outside the loop as well, leaving a single open connection for you to loop on, but on the other hand ADO.NET's connection pooling will usually just return the same open connection anyway, so it may not make a practical difference).

Cheers,
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    16th December 2017 - 11:26 PM