When trying to delete a row, I get a Login failure when trying to open connection

Question

I'm trying to create a simple gridview for a table in my database. I am trying to enable the delete feature of the gridview but i'm getting a strange error when I hit delete on one of my rows. Does anyone know why i'm getting this issue? I am connected to my Oracle database through the server explorer and the connection string is correct. Here is a screenshot of the error I get when I run the page and click on a delete link. Thanks in advance!

Web.config -

<configuration>
<connectionStrings>
<add name="ConnectionString3" connectionString="Data Source=obiwan;User ID=ac9555f;Password=*******"
providerName="System.Data.OracleClient" />

AvatarView Source Code -

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="AvatarView.aspx.cs" Inherits="DatabaseTest.AvatarView" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>

    <asp:GridView ID="GridView1" runat="server" OnRowDeleting="Gridview1_RowDeleting" AllowSorting="True" AutoGenerateColumns="False" BackColor="LightGoldenrodYellow" BorderColor="Tan" BorderWidth="1px" CellPadding="2" DataKeyNames="AVATARID" DataSourceID="SqlDataSource1" ForeColor="Black" GridLines="None" AllowPaging="True" Height="422px" Width="1020px">
        <AlternatingRowStyle BackColor="PaleGoldenrod" />
        <Columns>
            <asp:BoundField DataField="AVATARID" HeaderText="AVATARID" SortExpression="AVATARID" ReadOnly="True" />
            <asp:BoundField DataField="AVATARNAME" HeaderText="AVATARNAME" SortExpression="AVATARNAME" />
            <asp:BoundField DataField="DOB" HeaderText="DOB" SortExpression="DOB" />
            <asp:BoundField DataField="STRENGTH_CURR" HeaderText="STRENGTH_CURR" SortExpression="STRENGTH_CURR" />
            <asp:BoundField DataField="GENDER" HeaderText="GENDER" SortExpression="GENDER" />
            <asp:BoundField DataField="HOARD" HeaderText="HOARD" SortExpression="HOARD" />
            <asp:BoundField DataField="SPECIESID" HeaderText="SPECIESID" SortExpression="SPECIESID" />
            <asp:BoundField DataField="USERID" HeaderText="USERID" SortExpression="USERID" />             
            <asp:CommandField ShowDeleteButton="True" />


        </Columns>
        <FooterStyle BackColor="Tan" />
        <HeaderStyle BackColor="Tan" Font-Bold="True" />
        <PagerStyle BackColor="PaleGoldenrod" ForeColor="DarkSlateBlue" HorizontalAlign="Center" />
        <SelectedRowStyle BackColor="DarkSlateBlue" ForeColor="GhostWhite" />
        <SortedAscendingCellStyle BackColor="#FAFAE7" />
        <SortedAscendingHeaderStyle BackColor="#DAC09E" />
        <SortedDescendingCellStyle BackColor="#E1DB9C" />
        <SortedDescendingHeaderStyle BackColor="#C2A47B" />
    </asp:GridView>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString3 %>" ProviderName="<%$ ConnectionStrings:ConnectionString3.ProviderName %>" SelectCommand="SELECT * FROM &quot;AVATAR&quot;"></asp:SqlDataSource>

</div>
</form>
</body>
</html>

AvatarView Code Behind -

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using System.Configuration;

using System.Data.OracleClient;

namespace DatabaseTest
{
public partial class AvatarView : System.Web.UI.Page
{

    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void GridView1_SelectedIndexChanged(object sender, EventArgs e)
    {

    }      
    protected void Gridview1_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {
        string avatarID = GridView1.DataKeys[e.RowIndex].Value.ToString();
        string deleteSql = "DELETE FROM Avatar WHERE AvatarID = :AvatarID; ";

        using (var con = new OracleConnection(ConfigurationManager.ConnectionStrings["ConnectionString3"].ConnectionStr‌ing))
        using (var cmd = new OracleCommand(deleteSql, con))
        {
            cmd.Parameters.Add(":AvatarID", OracleType.VarChar).Value = avatarID;
            con.Open();
            int deleted = cmd.ExecuteNonQuery();
        }

        GridView1.DataSource = SqlDataSource1;
        GridView1.DataBind();
    }
}
}

check your username or pasword – CompEng Apr 03 '17 at 11:21 — CompEng, Apr 03 '17 at 11:21
@ErsinGülbahar they are correct.. – ACostea Apr 03 '17 at 11:22 — ACostea, Apr 03 '17 at 11:22

Cleptus · Accepted Answer · 2017-04-03T11:28:49.893

2

You are using SQL Server specific classes to connect to an Oracle server. Use the Oracle ones OracleConnection or a more generic approach IDbConnection

Example using Oracle clases:

using (var con = new OracleConnection(ConfigurationManager.ConnectionStrings["ConnectionString3"].ConnectionStr‌ing))

edited Apr 03 '17 at 11:28

answered Apr 03 '17 at 11:22

Cleptus

3,446
4
28
34

Thanks bradbury, where exactly would I put 'OracleConnection'? – ACostea Apr 03 '17 at 11:27
Thanks but when I try adding the namespace 'using.system.data.oracleclient' nothing shows up. Without using the right name space the oracleconnection line shows an error? – ACostea Apr 03 '17 at 11:36
Never mind, I added the reference to it and it allows me to use the namespace. But when I change the Sql classes to Oracle, they are underlined in green and say they're obsolete; has been deprecated! – ACostea Apr 03 '17 at 11:41
Since 4.0 they marked it as deprecated... "We strongly recommend customers use one of our partners’ ADO.NET Providers for Oracle instead of continuing to use Microsoft’s OracleClient for new application development" source: https://social.msdn.microsoft.com/Forums/en-US/b4a87818-a917-4cc8-a7e4-50dd28816385/what-should-i-use-instead-of-oracleconnection?forum=netfxgeneralprerelease – Cleptus Apr 03 '17 at 11:46
Ok, I have got around it. When trying to delete, I now get 'sql-error-ora-00933-sql-command-not-properly-ended' and from looking at other stackoverflow questions, it seems that Oracle doesn't allow for Joins in Update table. The thing is though, I am doing a Delete, not Update and i'm not even doing Joins, just checking for WHERE AvatarID=:AvatarID? – ACostea Apr 03 '17 at 11:49
I've edited my Code behind to show how it looks now! – ACostea Apr 03 '17 at 11:54
Now that you have solved this problem, you should mark it as answered, check for other questions related to your new problems and, if nothing found, start a new question. There are other questions with the update + join thing like this one http://stackoverflow.com/questions/2446764/update-statement-with-inner-join-on-oracle – Cleptus Apr 03 '17 at 11:54

When trying to delete a row, I get a Login failure when trying to open connection

1 Answers1