Hello Everyone,
I am desingning new page for Excel Upload to Sql. I have three button basically upload file, View and Import. Same as http://aspnet.4guysfromrolla.com/articles/022708-1.aspx explained on this link.
This uploading tool using Dataset and Table Adapter. This code only check if record exist it wont insert.
In my case I need to check if new record insert without any condition, If record exist need to check 3 field . In that one field status of the Application(The Field has different data like Application Submitted, Start for Review, Review, Decition Pending, Approved, Reject) and remaining two date field related to satus field. If the record exist but the status field is highest level(I have the level for status in another field 1,2,3,4.5&6) need to add not update. B'cz the i need all records need to store. In reports i can display most updated status for that record, if user searching history need provide stages with date-(like when submitted, when reviewed, etc.,)
If the all status field same no records inserted.
Every Application has unique number, in my case CDEApplicationNo(Also GBCode-This is another Unique number link with CDEApplicationNo) .
My question is how to add if condition in my dataset and table adapter(Pls explain i am not familier with this). If not anyother method or example are appriciated.
Pls check my code.
aspx page:
<form id="form1" runat="server">
<div>
<table>
<tr>
<td>
<asp:Button ID="ButtonUpload" runat="server" Text="Upload Excel Spreadsheet" />
</td>
<td>
<asp:Button ID="ButtonView" runat="server" Text="View Excel Data" />
</td>
<td>
<asp:Button ID="ButtonImport" runat="server" Text="Import Excel Data" />
</td>
</tr>
</table>
<asp:Panel ID="PanelUpload" runat="server" Visible="False">
<asp:FileUpload ID="FileUploadExcel" runat="server" />
<br />
Please select an Excel file to import:<br />
<asp:Button ID="ButtonUploadFile" runat="server" Text="Upload File" /><br />
<asp:Label ID="LabelUpload" runat="server" Text=""></asp:Label>
</asp:Panel>
<asp:Panel ID="PanelView" runat="server" Visible="False">
<asp:Label ID="LabelGrid" runat="server" Text=""></asp:Label>
<asp:GridView ID="GridViewExcel" runat="server" BackColor="White" BorderColor="#999999"
BorderStyle="None" BorderWidth="1px" CellPadding="3" GridLines="Vertical">
<FooterStyle BackColor="#CCCCCC" ForeColor="Black" />
<RowStyle BackColor="#EEEEEE" ForeColor="Black" />
<PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" />
<SelectedRowStyle BackColor="#008A8C" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#000084" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="#DCDCDC" />
</asp:GridView>
</asp:Panel>
<asp:Panel ID="PanelImport" runat="server" Visible="False">
<asp:Label ID="LabelImport" runat="server" Text=""></asp:Label>
</asp:Panel>
</div>
</form>
Code Behind:
Imports System.Data
Imports System.Data.OleDb
Imports System.Data.SqlClient
Partial Class _Default2
Inherits System.Web.UI.Page
Protected Sub ButtonUpload_Click(ByVal sender As Object, ByVal e As System.EventArgs) _
Handles ButtonUpload.Click
PanelUpload.Visible = True
PanelView.Visible = False
PanelImport.Visible = False
End Sub
Protected Function ExcelConnection() As OleDbCommand
' Connect to the Excel Spreadsheet
Dim xConnStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("~/ExcelImport.xls") & ";" & _
"Extended Properties=Excel 8.0;"
' create your excel connection object using the connection string
Dim objXConn As New OleDbConnection(xConnStr)
objXConn.Open()
' use a SQL Select command to retrieve the data from the Excel Spreadsheet
' the "table name" is the name of the worksheet within the spreadsheet
' in this case, the worksheet name is "newchange" and is coded as: [newchange$]
Dim objCommand As New OleDbCommand("SELECT * FROM [newchange$]", objXConn)
Return objCommand
End Function
Protected Sub ButtonView_Click(ByVal sender As Object, ByVal e As System.EventArgs) _
Handles ButtonView.Click
PanelUpload.Visible = False
PanelView.Visible = True
PanelImport.Visible = False
' Create a new Adapter
Dim objDataAdapter1 As New OleDbDataAdapter
' retrieve the Select command for the Spreadsheet
objDataAdapter1.SelectCommand = ExcelConnection()
' Create a DataSet
Dim ObjDataSet2 As New DataSet()
' Populate the DataSet with the spreadsheet worksheet data
objDataAdapter1.Fill(ObjDataSet2)
' Bind the data to the GridView
GridViewExcel.DataSource = ObjDataSet2.Tables(0).DefaultView
GridViewExcel.DataBind()
End Sub
Protected Sub ButtonImport_Click(ByVal sender As Object, ByVal e As System.EventArgs) _
Handles ButtonImport.Click
PanelUpload.Visible = False
PanelView.Visible = False
PanelImport.Visible = True
LabelImport.Text = "" ' reset to blank
' retrieve the Select Command for the worksheet data
Dim objCommand As New OleDbCommand()
objCommand = ExcelConnection()
' create a DataReader
Dim reader As OleDbDataReader
reader = objCommand.ExecuteReader()
Dim counter As Integer = 0 ' used for import in smaller increments
While reader.Read()
counter = counter + 1 ' counter to exit early for testing...
' set default values for loop
Dim Recordid As Integer = 1
Dim AppTypeID As Integer = 1
Dim CDEApplicationNo As String = Convert.ToString(reader("CDEApplicationNo"))
Dim GBCode As String = Convert.ToString(reader("GBCode"))
Dim Candidatename As String = Convert.ToString(reader("Candidatename"))
Dim CandidatenameEN As String = Convert.ToString(reader("CandidatenameEN"))
Dim Formulation As String = Convert.ToString(reader("Formulation"))
Dim Candidatetype As String = Convert.ToString(reader("Candidatetype"))
Dim ApplicationType As String = Convert.ToString(reader("ApplicationType"))
Dim Registrationtype As String = Convert.ToString(reader("Registrationtype"))
Dim CDEstartsreview As Date = Convert.ToDateTime(reader("CDEstartsreview"))
Dim OriginalApplicationPhase As String = Convert.ToString(reader("OriginalApplicationPhase"))
Dim ApplicationPhaseNew As String = Convert.ToString(reader("ApplicationPhaseNew"))
Dim Applicationnumber As String = Convert.ToString(reader("Applicationnumber"))
Dim Govtbodyreceived As String = Convert.ToString(reader("Govtbodyreceived"))
Dim DeveloperCN As String = Convert.ToString(reader("DeveloperCN"))
Dim DeveloperEN As String = Convert.ToString(reader("DeveloperEN"))
Dim B4StandardCurrentStatus As String = Convert.ToString(reader("B4StandardCurrentStatus"))
Dim CurrentStatusCN As String = Convert.ToString(reader("CurrentStatusCN"))
Dim CurrentStatusEN As String = Convert.ToString(reader("CurrentStatusEN"))
Dim ApprovalDocumentNo As String = Convert.ToString(reader("ApprovalDocumentNo"))
Dim Statusstartdate As Date = Convert.ToDateTime(reader("Statusstartdate"))
Dim ApplicationacceptedbylocalFDAorSFDA As String = Convert.ToString(reader("ApplicationacceptedbylocalFDAorSFDA"))
Dim AcceptedbyCDE As String = Convert.ToString(reader("AcceptedbyCDE"))
Dim CDEstartsreview1 As String = Convert.ToString(reader("CDEstartsreview1"))
Dim CDEgivesopiniontoSFDA As String = Convert.ToString(reader("CDEgivesopiniontoSFDA"))
Dim SFDAstartsreview As Date = Convert.ToDateTime(reader("SFDAstartsreview"))
Dim SFDAapprovesrejectsorrequestssupplementalmaterials As Date = Convert.ToDateTime(reader("SFDAapprovesrejectsorrequestssupplementalmaterials"))
Dim CDEacceptancepending As String = Convert.ToString(reader("CDEacceptancepending"))
' Insert any required validations here...
AppTypeID = GetAppTypeID(ApplicationType) 'retrieve the Recordid
Recordid = ImportIntoPipeline2(CDEApplicationNo, GBCode, Candidatename, CandidatenameEN, Formulation, Candidatetype, ApplicationType, Registrationtype, CDEstartsreview, OriginalApplicationPhase, ApplicationPhaseNew, Applicationnumber, Govtbodyreceived, DeveloperCN, DeveloperEN, B4StandardCurrentStatus, CurrentStatusCN, CurrentStatusEN, ApprovalDocumentNo, Statusstartdate, ApplicationacceptedbylocalFDAorSFDA, AcceptedbyCDE, CDEstartsreview1, CDEgivesopiniontoSFDA, SFDAstartsreview, SFDAapprovesrejectsorrequestssupplementalmaterials, CDEacceptancepending)
LabelImport.Text &= Recordid & CDEApplicationNo & _
" " & GBCode & " " & Candidatename & " " & CandidatenameEN & _
" " & Formulation & " " & Candidatetype & " " & ApplicationType & " " & Registrationtype & " " & CDEstartsreview & " " & OriginalApplicationPhase & " " & ApplicationPhaseNew & " " & Applicationnumber & " " & Govtbodyreceived & " " & DeveloperCN & " " & DeveloperEN & _
B4StandardCurrentStatus & " " & CurrentStatusCN & " " & CurrentStatusEN & " " & ApprovalDocumentNo & " " & Statusstartdate & " " & ApplicationacceptedbylocalFDAorSFDA & " " & AcceptedbyCDE & " " & CDEstartsreview1 & " " & " " & CDEgivesopiniontoSFDA & " " & SFDAapprovesrejectsorrequestssupplementalmaterials & " " & CDEacceptancepending & "<br>"
'If counter > 2 Then ' exit early for testing, comment later...
' Exit While
'End If
End While
reader.Close()
End Sub
Protected Sub ButtonUploadFile_Click(ByVal sender As Object, ByVal e As System.EventArgs) _
Handles ButtonUploadFile.Click
If FileUploadExcel.HasFile Then
Try
' alter path for your project
FileUploadExcel.SaveAs(Server.MapPath("~/ExcelImport.xls"))
LabelUpload.Text = "Upload File Name: " & _
FileUploadExcel.PostedFile.FileName & "<br>" & _
"Type: " & _
FileUploadExcel.PostedFile.ContentType & _
" File Size: " & _
FileUploadExcel.PostedFile.ContentLength & " kb<br>"
Catch ex As Exception
LabelUpload.Text = "Error: " & ex.Message.ToString
End Try
Else
LabelUpload.Text = "Please select a file to upload."
End If
End Sub
Protected Function GetAppTypeID(ByVal ApplicationType As String) As Integer
Dim AppTypeID As Integer = 0
Try
Dim SSAdapter As New DataSet2TableAdapters.AppTypeTableAdapter
Dim SSDataTable As DataSet2.AppTypeDataTable = Nothing
SSDataTable = SSAdapter.GetAppByType(ApplicationType)
' see if the category already exists in the table, if not insert it
If Not SSDataTable Is Nothing Then
If SSDataTable.Rows.Count > 0 Then
If Not SSDataTable(0).AppTypeID = Nothing Then
AppTypeID = SSDataTable(0).AppTypeID
End If
End If
End If
If AppTypeID = 0 Then ' if it is still 0, then insert it into the table
' retrieve the identity key Apptypeid from the insert
AppTypeID = _
Convert.ToInt32(SSAdapter.InsertAppType(ApplicationType))
' if this fails to return the proper Apptypeid, make sure to
' set the InsertApptypeQuery ExecuteMode Property to Scalar
End If
Return AppTypeID
Catch ex As Exception
LabelImport.Text &= ex.ToString
Return 0
End Try
End Function
Protected Function ImportIntoPipeline2(ByVal CDEApplicationNo As String, ByVal GBCode As String, ByVal Candidatename As String, _
ByVal CandidatenameEN As String, ByVal Formulation As String, ByVal Candidatetype As String, _
ByVal ApplicationType As String, ByVal RegistrationType As String, ByVal CDEstartsreview As Date, _
ByVal OriginalApplicationPhase As String, ByVal ApplicationPhaseNew As String, ByVal Applicationnumber As String, ByVal Govtbodyreceived As String, _
ByVal DeveloperCN As String, ByVal DeveloperEN As String, ByVal B4StandardCurrentStatus As String, _
ByVal CurrentStatusCN As String, ByVal CurrentStatusEN As String, ByVal ApprovalDocumentNo As String, _
ByVal Statusstartdate As String, ByVal ApplicationacceptedbylocalFDAorSFDA As String, ByVal AcceptedbyCDE As String, _
ByVal CDEstartsreview1 As Date, ByVal CDEgivesopiniontoSFDA As String, ByVal SFDAstartsreview As Date, _
ByVal SFDAapprovesrejectsorrequestssupplementalmaterials As Date, ByVal CDEacceptancepending As String) As Integer
' make sure values don't exceed column limits
CDEApplicationNo = Left(CDEApplicationNo, 50)
GBCode = Left(GBCode, 50)
Candidatename = Left(Candidatename, 50)
CandidatenameEN = Left(CandidatenameEN, 50)
Formulation = Left(Formulation, 50)
Candidatetype = Left(Candidatetype, 50)
ApplicationType = Left(ApplicationType, 50)
RegistrationType = Left(RegistrationType, 50)
CDEstartsreview = Left(CDEstartsreview, 8)
OriginalApplicationPhase = Left(OriginalApplicationPhase, 50)
ApplicationPhaseNew = Left(ApplicationPhaseNew, 50)
Applicationnumber = Left(Applicationnumber, 50)
Govtbodyreceived = Left(Govtbodyreceived, 50)
DeveloperCN = Left(DeveloperCN, 50)
DeveloperEN = Left(DeveloperEN, 50)
B4StandardCurrentStatus = Left(B4StandardCurrentStatus, 50)
CurrentStatusCN = Left(CurrentStatusCN, 50)
CurrentStatusEN = Left(CurrentStatusEN, 50)
ApprovalDocumentNo = Left(ApprovalDocumentNo, 50)
Statusstartdate = Left(Statusstartdate, 8)
ApplicationacceptedbylocalFDAorSFDA = Left(ApplicationacceptedbylocalFDAorSFDA, 50)
AcceptedbyCDE = Left(AcceptedbyCDE, 50)
CDEstartsreview1 = Left(CDEstartsreview1, 8)
CDEgivesopiniontoSFDA = Left(CDEgivesopiniontoSFDA, 50)
SFDAstartsreview = Left(SFDAstartsreview, 8)
SFDAapprovesrejectsorrequestssupplementalmaterials = Left(SFDAapprovesrejectsorrequestssupplementalmaterials, 8)
CDEacceptancepending = Left(CDEacceptancepending, 50)
Dim RecordID As Integer = 0
Try
Dim SSAdapter As New DataSet2TableAdapters.Pipeline2TableAdapter
Dim SSDataTable As DataSet2.Pipeline2DataTable = Nothing
SSDataTable = SSAdapter.GetDataByCDEApp_(CDEApplicationNo, GBCode, CDEstartsreview)
' see if the A already exists in the table, if not insert it
If Not SSDataTable Is Nothing Then
If SSDataTable.Rows.Count > 0 Then
If Not SSDataTable(0).RecordId = Nothing Then
RecordID = SSDataTable(0).RecordId
LabelImport.Text &= "<font color=blue>CDEAPPLicationNo Found, Not Imported: " & " ID: " & RecordID & " " & CDEApplicationNo & " " & GBCode & ".</font><br>"
End If
End If
End If
If RecordID = 0 Then ' if it is still 0, then insert it into the table
' retrieve the identity key RecordID from the insert
RecordID = _
Convert.ToInt32(SSAdapter.InsertPipeline2(CDEApplicationNo, GBCode, Candidatename, CandidatenameEN, Formulation, _
Candidatetype, ApplicationType, RegistrationType, CDEstartsreview, _
OriginalApplicationPhase, ApplicationPhaseNew, Applicationnumber, Govtbodyreceived, _
DeveloperCN, DeveloperEN, B4StandardCurrentStatus, CurrentStatusCN, _
CurrentStatusEN, ApprovalDocumentNo, Statusstartdate, ApplicationacceptedbylocalFDAorSFDA, _
AcceptedbyCDE, CDEstartsreview1, CDEgivesopiniontoSFDA, SFDAstartsreview, _
SFDAapprovesrejectsorrequestssupplementalmaterials, CDEacceptancepending))
LabelImport.Text &= "<font color=green>PipelineData Imported: .</font><br>"
End If
Return RecordID
Catch ex As Exception
LabelImport.Text &= "<font color=red>" & ex.ToString & "</font><br>"
Return 0
End Try
End Function
End Class
----------
I have made Store procedure. Just want to know how to call from this code. Pls check this SP. Is that my SP is correct?
-----------------------
CREATE PROCEDURE SpPipelineInsert As
declare @CDEApplicationNo nvarchar(50)
declare @GBCode nvarchar(50)
declare @Candidatename nvarchar(50)
declare @CandidatenameEN nvarchar(50)
declare @Formulation nvarchar(50)
declare @Candidatetype nvarchar(50)
declare @ApplicationType nvarchar(50)
declare @RegistrationType nvarchar(50)
declare @CDEstartsreview datetime
declare @OriginalApplicationPhase nvarchar(50)
declare @ApplicationPhaseNew nvarchar(50)
declare @Applicationnumber nvarchar(50)
declare @Govtbodyreceived nvarchar(50)
declare @DeveloperCN nvarchar(50)
declare @DeveloperEN nvarchar(50)
declare @B4StandardCurrentStatus nvarchar(50)
declare @CurrentStatusCN nvarchar(50)
declare @CurrentStatusEN nvarchar(50)
declare @ApprovalDocumentNo nvarchar(50)
declare @Statusstartdate datetime
declare @ApplicationacceptedbylocalFDAorSFDA nvarchar(50)
declare @AcceptedbyCDE nvarchar(50)
declare @CDEstartsreview1 datetime
declare @CDEgivesopiniontoSFDA nvarchar(50)
declare @SFDAstartsreview datetime
declare @SFDAapprovesrejectsorrequestssupplementalmaterials datetime
declare @CDEacceptancepending nvarchar(50)
IF EXISTS (SELECT 1 FROM pipeline2 WHERE CDEApplicationNo=@CDEApplicationNo and GBCode=@GBCode and CurrentStatusEN<>@CurrentStatusEN)
BEGIN
INSERT INTO Pipeline2
(CDEApplicationNo, GBCode, Candidatename, CandidatenameEN, Formulation, Candidatetype, ApplicationType, Registrationtype, CDEstartsreview,
OriginalApplicationPhase, ApplicationPhaseNew, Applicationnumber, Govtbodyreceived, DeveloperCN, DeveloperEN, B4StandardCurrentStatus,
CurrentStatusCN, CurrentStatusEN, ApprovalDocumentNo, Statusstartdate, ApplicationacceptedbylocalFDAorSFDA, AcceptedbyCDE, CDEstartsreview1,
CDEgivesopiniontoSFDA, SFDAstartsreview, SFDAapprovesrejectsorrequestssupplementalmaterials, CDEacceptancepending)
VALUES (@CDEApplicationNo,@GBCode,@Candidatename,@CandidatenameEN,@Formulation,@Candidatetype,@ApplicationType,@Registrationtype,
@CDEstartsreview,@OriginalApplicationPhase,@ApplicationPhaseNew,@Applicationnumber,@Govtbodyreceived,@DeveloperCN,@DeveloperEN,@B4StandardCurrentStatus,
@CurrentStatusCN,@CurrentStatusEN,@ApprovalDocumentNo,@Statusstartdate,@ApplicationacceptedbylocalFDAorSFDA,@AcceptedbyCDE,@CDEstartsreview1,
@CDEgivesopiniontoSFDA,@SFDAstartsreview,@SFDAapprovesrejectsorrequestssupplementalmaterials,@CDEacceptancepending)
END
ELSE
BEGIN
IF Not EXISTS (SELECT 1 FROM pipeline2 WHERE CDEApplicationNo=@CDEApplicationNo and GBCode=@GBCode and CurrentStatusEN=@CurrentStatusEN)
BEGIN
INSERT INTO Pipeline2
(CDEApplicationNo, GBCode, Candidatename, CandidatenameEN, Formulation, Candidatetype, ApplicationType, Registrationtype, CDEstartsreview,
OriginalApplicationPhase, ApplicationPhaseNew, Applicationnumber, Govtbodyreceived, DeveloperCN, DeveloperEN, B4StandardCurrentStatus,
CurrentStatusCN, CurrentStatusEN, ApprovalDocumentNo, Statusstartdate, ApplicationacceptedbylocalFDAorSFDA, AcceptedbyCDE, CDEstartsreview1,
CDEgivesopiniontoSFDA, SFDAstartsreview, SFDAapprovesrejectsorrequestssupplementalmaterials, CDEacceptancepending)
VALUES (@CDEApplicationNo,@GBCode,@Candidatename,@CandidatenameEN,@Formulation,@Candidatetype,@ApplicationType,@Registrationtype,@CDEstartsreview,@OriginalApplicationPhase,@ApplicationPhaseNew,@Applicationnumber,@Govtbodyreceived,@DeveloperCN,@DeveloperEN,@B4StandardCurrentStatus,@CurrentStatusCN,@CurrentStatusEN,@ApprovalDocumentNo,@Statusstartdate,@ApplicationacceptedbylocalFDAorSFDA,@AcceptedbyCDE,@CDEstartsreview1,@CDEgivesopiniontoSFDA,@SFDAstartsreview,@SFDAapprovesrejectsorrequestssupplementalmaterials,@CDEacceptancepending)
END
END
GO--------------------------------------------
Thanks
Mohammed