I had written a code in C# that I need to convert to VB. My C# code works perfectly fine but some glitch in my VB code.What actually is happening, my c# code reads through the excel sheet records and inserts them into the DB. But my VB code is inserting it twice. Can somebody figure out why?
this is my VB code:
Protected Sub btnUpload_Click(sender As Object, e As EventArgs) Handles btnUpload.Click
If FileUpload1.HasFile Then
Dim path As String = String.Concat((Server.MapPath("~/Uploads/" + FileUpload1.FileName)))
FileUpload1.PostedFile.SaveAs(path)
Dim oleCon As New OleDbConnection((Convert.ToString("Provider=Microsoft.Ace.OLEDB.12.0;Data Source=") & path) + ";Extended Properties = Excel 12.0;")
Dim Olecmd As New OleDbCommand("select * from [Sheet1$]", oleCon)
Dim dtap As New OleDbDataAdapter(Olecmd)
Dim ds As New DataSet()
dtap.Fill(ds)
GridView1.DataSource = ds
GridView1.DataBind()
GridView1.Visible = False
Dim dt As New DataTable()
dt.Columns.AddRange(New DataColumn(41) {New DataColumn("Template", GetType(String)), New DataColumn("Cust_Name", GetType(String)), New DataColumn("Invoice_No", GetType(Integer)), New DataColumn("InvoiceDate", GetType(DateTime)), New DataColumn("SR_No", GetType(Integer)), New DataColumn("Description1", GetType(String)), _
New DataColumn("Description2", GetType(String)), New DataColumn("Description3", GetType(String)), New DataColumn("Description4", GetType(String)), New DataColumn("Description5", GetType(String)), New DataColumn("CurrencyCode", GetType(String)), New DataColumn("Amount", GetType(Decimal)), _
New DataColumn("Subject", GetType(String)), New DataColumn("Reference", GetType(String)), New DataColumn("CustomerAddress1", GetType(String)), New DataColumn("CustomerAddress2", GetType(String)), New DataColumn("CustomerAddress3", GetType(String)), New DataColumn("CustomerAddress4", GetType(String)), _
New DataColumn("CustomerAddress5", GetType(String)), New DataColumn("CustomerAddress6", GetType(String)), New DataColumn("CustomerTelephone", GetType(String)), New DataColumn("EmailIdTo", GetType(String)), New DataColumn("EmailIDCC", GetType(String)), New DataColumn("BankName", GetType(String)), _
New DataColumn("AccountTitle", GetType(String)), New DataColumn("AccountNo", GetType(String)), New DataColumn("CurrencyCode1", GetType(String)), New DataColumn("BankAddress1", GetType(String)), New DataColumn("BankAddress2", GetType(String)), New DataColumn("BankAddress3", GetType(String)), _
New DataColumn("BankAddress4", GetType(String)), New DataColumn("SwiftCode", GetType(String)), New DataColumn("IBAN", GetType(String)), New DataColumn("ContactName1", GetType(String)), New DataColumn("ContactEmail1", GetType(String)), New DataColumn("ContactTel1", GetType(String)), _
New DataColumn("ContactName2", GetType(String)), New DataColumn("ContactEmail2", GetType(String)), New DataColumn("ContactTel2", GetType(String)), New DataColumn("ContactName3", GetType(String)), New DataColumn("ContactEmail3", GetType(String)), New DataColumn("ContactTel3", GetType(String))})
For Each row As GridViewRow In GridView1.Rows
Dim rowIndex As Integer = row.RowIndex
If rowIndex > 0 Then
Dim Template As String = row.Cells(0).Text
Dim Cust_Name As String = row.Cells(1).Text
Dim Invoice_No As Integer = Integer.Parse(row.Cells(2).Text)
'DateTime InvoiceDate = DateTime.ParseExact(row.Cells[3].Text, "d-MMM-yy", CultureInfo.InvariantCulture);
Dim InvoiceDate As String = (row.Cells(3).Text)
Dim Sr_No As Integer = Integer.Parse(row.Cells(4).Text)
Dim Description1 As String = (row.Cells(5).Text)
Dim Description2 As String = (row.Cells(6).Text)
Dim Description3 As String = (row.Cells(7).Text)
Dim Description4 As String = (row.Cells(8).Text)
Dim Description5 As String = (row.Cells(9).Text)
Dim CurrencyCode As String = (row.Cells(10).Text)
Dim Amount As Decimal = Decimal.Parse(row.Cells(11).Text)
Dim Subject As String = (row.Cells(12).Text)
Dim Reference As String = (row.Cells(13).Text)
Dim CustomerAddress1 As String = (row.Cells(14).Text)
Dim CustomerAddress2 As String = (row.Cells(15).Text)
Dim CustomerAddress3 As String = (row.Cells(16).Text)
Dim CustomerAddress4 As String = (row.Cells(17).Text)
Dim CustomerAddress5 As String = (row.Cells(18).Text)
Dim CustomerAddress6 As String = (row.Cells(19).Text)
Dim CustomerTelephone As String = (row.Cells(20).Text)
Dim EmailIdTo As String = (row.Cells(21).Text)
Dim EmailIDCC As String = (row.Cells(22).Text)
Dim BankName As String = (row.Cells(23).Text)
Dim AccountTitle As String = (row.Cells(24).Text)
Dim AccountNo As String = (row.Cells(25).Text)
Dim CurrencyCode1 As String = (row.Cells(26).Text)
Dim BankAddress1 As String = (row.Cells(27).Text)
Dim BankAddress2 As String = (row.Cells(28).Text)
Dim BankAddress3 As String = (row.Cells(29).Text)
Dim BankAddress4 As String = (row.Cells(30).Text)
Dim SwiftCode As String = (row.Cells(31).Text)
Dim IBAN As String = (row.Cells(32).Text)
Dim ContactName1 As String = (row.Cells(33).Text)
Dim ContactEmail1 As String = (row.Cells(34).Text)
Dim ContactTel1 As String = (row.Cells(35).Text)
Dim ContactName2 As String = (row.Cells(36).Text)
Dim ContactEmail2 As String = (row.Cells(37).Text)
Dim ContactTel2 As String = (row.Cells(38).Text)
Dim ContactName3 As String = (row.Cells(39).Text)
Dim ContactEmail3 As String = (row.Cells(40).Text)
Dim ContactTel3 As String = (row.Cells(41).Text)
dt.Rows.Add(Template, Cust_Name, Invoice_No, InvoiceDate, Sr_No, Description1, _
Description2, Description3, Description4, Description5, CurrencyCode, Amount, _
Subject, Reference, CustomerAddress1, CustomerAddress2, CustomerAddress3, CustomerAddress4, _
CustomerAddress5, CustomerAddress6, CustomerTelephone, EmailIdTo, EmailIDCC, BankName, _
AccountTitle, AccountNo, CurrencyCode1, BankAddress1, BankAddress2, BankAddress3, _
BankAddress4, SwiftCode, IBAN, ContactName1, ContactEmail1, ContactTel1, _
ContactName2, ContactEmail2, ContactTel2, ContactName3, ContactEmail3, ContactTel3)
End If
Next
If dt.Rows.Count > 0 Then
Try
Dim consString As String = ConfigurationManager.ConnectionStrings("InvoiceSystemConnectionString").ConnectionString
Dim con As New SqlConnection(consString)
Using cmd As New SqlCommand("[spInsertExcel]")
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = con
cmd.Parameters.AddWithValue("@tblInvoice", dt)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
End Using
Response.Write("<script>alert('Inserted')</script>")
Catch ex As Exception
Response.Write("<script>alert('" + Server.HtmlEncode(ex.Message) + "')</script>")
End Try
End If
Else
Response.Write("<script>alert('Insertion failed')</script>")
End If
End Sub
EDITED Here is my c# code
protected void btnUpload_Click(object sender, EventArgs e)
{
{
if (FileUpload1.HasFile)
{
string path = string.Concat((Server.MapPath("~/Uploads/" + FileUpload1.FileName)));
FileUpload1.PostedFile.SaveAs(path);
OleDbConnection oleCon = new OleDbConnection("Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" + path + ";Extended Properties = Excel 12.0;");
OleDbCommand Olecmd = new OleDbCommand("select * from [Sheet1$]", oleCon);
OleDbDataAdapter dtap = new OleDbDataAdapter(Olecmd);
DataSet ds = new DataSet();
dtap.Fill(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
GridView1.Visible = false;
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[42] { new DataColumn("Template", typeof(string)),
new DataColumn("Cust_Name", typeof(string)),
new DataColumn("Invoice_No", typeof(int)),
new DataColumn("InvoiceDate",typeof(DateTime)),
new DataColumn("SR_No", typeof(int)),
new DataColumn("Description1", typeof(string)),
new DataColumn("Description2", typeof(string)),
new DataColumn("Description3", typeof(string)),
new DataColumn("Description4", typeof(string)),
new DataColumn("Description5", typeof(string)),
new DataColumn("CurrencyCode", typeof(string)),
new DataColumn("Amount", typeof(decimal)),
new DataColumn("Subject", typeof(string)),
new DataColumn("Reference", typeof(string)),
new DataColumn("CustomerAddress1", typeof(string)),
new DataColumn("CustomerAddress2", typeof(string)),
new DataColumn("CustomerAddress3", typeof(string)),
new DataColumn("CustomerAddress4", typeof(string)),
new DataColumn("CustomerAddress5", typeof(string)),
new DataColumn("CustomerAddress6", typeof(string)),
new DataColumn("CustomerTelephone", typeof(string)),
new DataColumn("EmailIdTo", typeof(string)),
new DataColumn("EmailIDCC", typeof(string)),
new DataColumn("BankName", typeof(string)),
new DataColumn("AccountTitle", typeof(string)),
new DataColumn("AccountNo", typeof(string)),
new DataColumn("CurrencyCode1", typeof(string)),
new DataColumn("BankAddress1", typeof(string)),
new DataColumn("BankAddress2", typeof(string)),
new DataColumn("BankAddress3", typeof(string)),
new DataColumn("BankAddress4", typeof(string)),
new DataColumn("SwiftCode", typeof(string)),
new DataColumn("IBAN", typeof(string)),
new DataColumn("ContactName1", typeof(string)),
new DataColumn("ContactEmail1", typeof(string)),
new DataColumn("ContactTel1", typeof(string)),
new DataColumn("ContactName2", typeof(string)),
new DataColumn("ContactEmail2", typeof(string)),
new DataColumn("ContactTel2", typeof(string)),
new DataColumn("ContactName3", typeof(string)),
new DataColumn("ContactEmail3", typeof(string)),
new DataColumn("ContactTel3", typeof(string))});
foreach (GridViewRow row in GridView1.Rows)
{
int rowIndex = row.RowIndex;
if (rowIndex > 0)
{
string Template = row.Cells[0].Text;
string Cust_Name = row.Cells[1].Text;
int Invoice_No = int.Parse(row.Cells[2].Text);
//DateTime InvoiceDate = DateTime.ParseExact(row.Cells[3].Text, "d-MMM-yy", CultureInfo.InvariantCulture);
string InvoiceDate = (row.Cells[3].Text);
int Sr_No = int.Parse(row.Cells[4].Text);
string Description1 = (row.Cells[5].Text);
string Description2 = (row.Cells[6].Text);
string Description3 = (row.Cells[7].Text);
string Description4 = (row.Cells[8].Text);
string Description5 = (row.Cells[9].Text);
string CurrencyCode = (row.Cells[10].Text);
decimal Amount = decimal.Parse(row.Cells[11].Text);
string Subject = (row.Cells[12].Text);
string Reference = (row.Cells[13].Text);
string CustomerAddress1 = (row.Cells[14].Text);
string CustomerAddress2 = (row.Cells[15].Text);
string CustomerAddress3 = (row.Cells[16].Text);
string CustomerAddress4 = (row.Cells[17].Text);
string CustomerAddress5 = (row.Cells[18].Text);
string CustomerAddress6 = (row.Cells[19].Text);
string CustomerTelephone = (row.Cells[20].Text);
string EmailIdTo = (row.Cells[21].Text);
string EmailIDCC = (row.Cells[22].Text);
string BankName = (row.Cells[23].Text);
string AccountTitle = (row.Cells[24].Text);
string AccountNo = (row.Cells[25].Text);
string CurrencyCode1 = (row.Cells[26].Text);
string BankAddress1 = (row.Cells[27].Text);
string BankAddress2 = (row.Cells[28].Text);
string BankAddress3 = (row.Cells[29].Text);
string BankAddress4 = (row.Cells[30].Text);
string SwiftCode = (row.Cells[31].Text);
string IBAN = (row.Cells[32].Text);
string ContactName1 = (row.Cells[33].Text);
string ContactEmail1 = (row.Cells[34].Text);
string ContactTel1 = (row.Cells[35].Text);
string ContactName2 = (row.Cells[36].Text);
string ContactEmail2 = (row.Cells[37].Text);
string ContactTel2 = (row.Cells[38].Text);
string ContactName3 = (row.Cells[39].Text);
string ContactEmail3 = (row.Cells[40].Text);
string ContactTel3 = (row.Cells[41].Text);
dt.Rows.Add(Template, Cust_Name, Invoice_No, InvoiceDate, Sr_No, Description1, Description2, Description3, Description4, Description5,
CurrencyCode, Amount, Subject, Reference, CustomerAddress1, CustomerAddress2, CustomerAddress3, CustomerAddress4, CustomerAddress5,
CustomerAddress6, CustomerTelephone, EmailIdTo, EmailIDCC, BankName, AccountTitle, AccountNo, CurrencyCode1, BankAddress1,
BankAddress2, BankAddress3, BankAddress4, SwiftCode, IBAN, ContactName1, ContactEmail1, ContactTel1, ContactName2, ContactEmail2,
ContactTel2, ContactName3, ContactEmail3, ContactTel3);
}
}
if (dt.Rows.Count > 0)
{
try
{
string consString = ConfigurationManager.ConnectionStrings["InvoiceSystemConnectionString"].ConnectionString;
SqlConnection con = new SqlConnection(consString);
using (SqlCommand cmd = new SqlCommand("[spInsertExcel]"))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = con;
cmd.Parameters.AddWithValue("@tblInvoice", dt);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
Response.Write("<script>alert('Inserted')</script>");
}
catch (Exception ex)
{
Response.Write("<script>alert('" + Server.HtmlEncode(ex.Message) + "')</script>");
}
}
}
else
{
Response.Write("<script>alert('Insertion failed')</script>");
}
}
}
You should try with this free online tool:
http://www.developerfusion.com/tools/convert/csharp-to-vb/
It converts C# to VB.net (and vice versa) and has served me well in the past, apart from some rare unhandled cases it has always done a correct conversion for me.
As for analyzing your code: if you don't include the original C# to make a comparison it's quite pointless...
EDIT: after seing the original code.
I see your VB method Handles btnUpload.Click explicitly, something your C# method doesn't seem to do.
I'm assuming this is an ASP.net website. Does your button declaration in the ASP file look something like this:
<asp:Button runat="server" ID="btnUpload" OnClick="btnUpload_Click"/>
?
In that case, it's normal that it gets called twice: one time because the button declares explicitly that its OnClick event should be handled by the btnUpload_Click method, the second time because the method itself declares statically that it handles the button's OnClick event.
Either remove the OnClick="btnUpload_Click" from your ASP code or remove Handles btnUpload.Click from the method signature (I would suggest the second option, as it makes the backend code more reusable)