Wrox Programmer Forums
Go Back   Wrox Programmer Forums > C# and C > C# 2005 > C# 2005
|
C# 2005 For discussion of Visual C# 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the C# 2005 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old April 21st, 2008, 09:02 AM
Authorized User
 
Join Date: Apr 2008
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default Help with C# and SQL query

Hello,

I am trying to make a SQL query in this code the query I am trying to do is a very simple query is "SELECT * FROM REV_REVISTAS". Getting all the data from the table REV_REVISTAS. The problem is that this code is not mine so I really dont understand where I am mistaking I tried to put the following code:


SqlConnection myConnection = new SqlConnection("Data Source=localhost;Initial Catalog=BDDatos;Persist Security Info=True;User ID=sa;Password=pass");


string SQL_QUERY = "select * from revista";
SqlCommand myCommand = new SqlCommand(SQL_QUERY, myConnection);


SqlDataAdapter myAdapter = new SqlDataAdapter(myCommand);

DataTable myTable = new DataTable();
myAdapter.Fill(myTable);

dgProducts.DataSource = myTable;
dgProducts.DataBind();
myConnection.Close();
-------------------------------------------------------------

But is does not work it gives me an error saying the following:

Details of the exception: System.Data.SqlClient.SqlException: The name of the object 'myTable' is not valid

Line 68: myAdapter.Fill(myTable);

-----------------------------------------------------------------

Is a website made on C# and ASP.NET the code is the following:


using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

namespace pap.paginas
{
    /// <summary>
    /// Descripción breve de IndiceCronologico.
    /// </summary>
    public class IndiceCronologico : System.Web.UI.Page
    {
        protected System.Web.UI.WebControls.Label lblError;
        protected System.Web.UI.WebControls.Label lblNumeroError;
        protected System.Web.UI.WebControls.Label lblOrigenError;
        protected System.Web.UI.WebControls.Label lblDescripcionError;
        protected System.Web.UI.WebControls.Button btnOtraSuscripcion;
        protected System.Web.UI.HtmlControls.HtmlGenericControl dvMensajesError;
        protected System.Web.UI.HtmlControls.HtmlGenericControl dvIndiceCronologico;
        protected System.Web.UI.WebControls.DataGrid dtgIndiceCronologicoRevistas;
        protected System.Web.UI.WebControls.DataGrid dtgIndiceCronologicoSuplementos;
        protected System.Web.UI.HtmlControls.HtmlTable tbContenido;
        protected System.Web.UI.WebControls.DataGrid myTable;



            private void Page_Load(object sender, System.EventArgs e)
            {

                this.dvIndiceCronologico.Visible = true;
                this.dvMensajesError.Visible = false;



                pap.Componentes.Revistas objRevistas = new pap.Componentes.Revistas();

                System.Data.DataSet dsIndiceCronologico = objRevistas.GetIndiceCronologico();

                if(dsIndiceCronologico == null)
                {
                    System.Data.DataSet dsError = objRevistas.GetDatosError();
                    MostrarMensajeError(dsError);
                    return;
                }

                //this.dtgIndiceCronologicoRevistas.DataSource = dsIndiceCronologico.Tables["REVISTAS"];
                //this.dtgIndiceCronologicoRevistas.DataBind();
                //------------------------------------------------------------------------------------------
                SqlConnection myConnection = new SqlConnection("Data Source=192.168.1.207 Catalog=PAPBD;Persist Security Info=True;User ID=pap;Password=pap");

                //System.Data.DataSet.Equals(objRevistas.GetCuerpoRe vista(string strNumeroRevista, string strIdioma, string strTipo) = objRevistas.GetIndiceCronologico();
                string SQL_QUERY = "select * from myTable";
                SqlCommand myCommand = new SqlCommand(SQL_QUERY, myConnection);


                SqlDataAdapter myAdapter = new SqlDataAdapter(myCommand);


                DataTable myTable = new DataTable();

                myAdapter.Fill(myTable);

                this.dtgIndiceCronologicoRevistas.DataSource = myTable;
                this.dtgIndiceCronologicoRevistas.DataBind();
                myConnection.Close();

                /*SqlDataAdapter myAdapter = new SqlDataAdapter(myCommand);

                DataTable myTable = new DataTable();
                myAdapter.Fill(myTable);

                dgProducts.DataSource = myTable;
                dgProducts.DataBind();
                myConnection.Close();*/

                //----------------------------------------------------------------------------------------
                //this.dtgIndiceCronologicoRevistas.DataSource = dsIndiceCronologico.Tables["REV_REVISTAS"];
                //this.dtgIndiceCronologicoRevistas.DataBind();
                //------------------------------------------------------------------------------------------
            }

            #region Código generado por el Diseñador de Web Forms
            override protected void OnInit(EventArgs e)
            {
                //
                // CODEGEN: llamada requerida por el Diseñador de Web Forms ASP.NET.
                //

                InitializeComponent();
                base.OnInit(e);
            }

            /// <summary>
            /// Método necesario para admitir el Diseñador. No se puede modificar
            /// el contenido del método con el editor de código.
            /// </summary>
            private void InitializeComponent()
            {
                this.Load += new System.EventHandler(this.Page_Load);

            }
            #endregion

            private void MostrarMensajeError(System.Data.DataSet dsError)
            {
                this.lblNumeroError.Text = "Numero: " + dsError.Tables[0].Rows[0]["Numero"].ToString().Trim();
                this.lblOrigenError.Text = "Origen: " + dsError.Tables[0].Rows[0]["Origen"].ToString().Trim();
                this.lblDescripcionError.Text = "Descripcion: " + dsError.Tables[0].Rows[0]["Descripcion"].ToString().Trim();
                setResultado(false);
            }

            private void setResultado(bool bolResultado)
            {
                if(!bolResultado)
                {
                    this.dvIndiceCronologico.Visible = false;
                    this.dvMensajesError.Visible = true;
                }
            }

        }
    }


Can anyone help me I am getting a little desperate.

Thank very much

Regards,

Codeman1234

 
Old April 21st, 2008, 09:18 AM
samjudson's Avatar
Friend of Wrox
 
Join Date: Aug 2007
Posts: 2,128
Thanks: 1
Thanked 189 Times in 188 Posts
Default

In your first code example your SQL is "select * from revista" whereas in the second it is "select * FROM mytable"

Perhaps that is your problem?

/- Sam Judson : Wrox Technical Editor -/
 
Old April 21st, 2008, 09:24 AM
Friend of Wrox
 
Join Date: Mar 2007
Posts: 432
Thanks: 0
Thanked 1 Time in 1 Post
Default

Hi Codeman,

From the look of things, your code looks OK. Does the connection string work OK though? I thought the Data Source element has to include the instance name?

For example, for my local SQLEXPRESS instance : "Data Source=(local)\SQLEXPRESS"...

Can you confirm if the connection opens OK with the connection string you are using?
You can test this easily by adding a explicit myConnection.Open() before the myDataAdapter.Fill(myDataTable).

Rob
http://robzyc.spaces.live.com
 
Old April 21st, 2008, 09:25 AM
Friend of Wrox
 
Join Date: Mar 2007
Posts: 432
Thanks: 0
Thanked 1 Time in 1 Post
Default

Good spot there Sam, I never read down that far! ;) that will be it! Kudos to thee!

Rob
http://robzyc.spaces.live.com
 
Old April 21st, 2008, 09:36 AM
Authorized User
 
Join Date: Apr 2008
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Looks that is fine because I put it before myAdapter.Fill(myTable); like you said and I got the same error in myAdapter.Fill(myTable); so it goes through perfectly. So I dont know really what is going on with this any thoughts??


By the way the Query with REV_REVISTAS is the table I want to get but, the one on the code is with myTable because all the SQL query and connection I am doing it with myTable. Is That wrong??

Thanks 4 your responses

Regards,

Codeman1234

 
Old April 21st, 2008, 09:47 AM
Authorized User
 
Join Date: Apr 2008
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I tried changing the query table from myTable to REV_REVISTAS at least gives me and other error this time is on ASP.NET code is the following:

<asp:TemplateColumn>
<ItemTemplate>
<a href="#" onMouseOut="MM_swapImgRestore()" onMouseOver="MM_swapImage('Image161','','../imagenes/Plantillas/<%# DataBinder.Eval(Container.DataItem, "ANNO") %>b.gif',1)">
<img name="Image161" border="0" src="../imagenes/Plantillas/<%# DataBinder.Eval(Container.DataItem, "ANNO") %>a.gif" width="64" height="35"></a>
</ItemTemplate>


The Code is the following:

<%@ Page language="c#" Codebehind="IndiceCronologico.aspx.cs" AutoEventWireup="false" Inherits="pap.paginas.IndiceCronologico" %>
<%@ Register TagPrefix="uc1" TagName="uscMenuPrincipal" Src="../controles/uscMenuPrincipal.ascx" %>
<%@ Register TagPrefix="uc1" TagName="uscMenuInferior" Src="../controles/uscMenuInferior.ascx" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<HTML>
    <HEAD>
        <title>Indice Cronologico</title>
        <meta content="Microsoft Visual Studio .NET 7.1" name="GENERATOR">
        <meta content="C#" name="CODE_LANGUAGE">
        <meta content="JavaScript" name="vs_defaultClientScript">
        <meta content="http://schemas.microsoft.com/intellisense/ie5" name="vs_targetSchema">
        <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
        <LINK href="../estilos/papstyles.css" type="text/css" rel="stylesheet">
        <script language="JavaScript">
            <!--
            function MM_swapImgRestore() { //v3.0
            var i,x,a=document.MM_sr; for(i=0;a&&i<a.length&&(x=a[i])&&x.oSrc;i++) x.src=x.oSrc;
            }

            function MM_preloadImages() { //v3.0
            var d=document; if(d.images){ if(!d.MM_p) d.MM_p=new Array();
                var i,j=d.MM_p.length,a=MM_preloadImages.arguments; for(i=0; i<a.length; i++)
                if (a[i].indexOf("#")!=0){ d.MM_p[j]=new Image; d.MM_p[j++].src=a[i];}}
            }

            function MM_findObj(n, d) { //v3.0
            var p,i,x; if(!d) d=document; if((p=n.indexOf("?"))>0&&parent.frames.length) {
                d=parent.frames[n.substring(p+1)].document; n=n.substring(0,p);}
            if(!(x=d[n])&&d.all) x=d.all[n]; for (i=0;!x&&i<d.forms.length;i++) x=d.forms[i][n];
            for(i=0;!x&&d.layers&&i<d.layers.length;i++) x=MM_findObj(n,d.layers[i].document); return x;
            }

            function MM_swapImage() { //v3.0
            var i,j=0,x,a=MM_swapImage.arguments; document.MM_sr=new Array; for(i=0;i<(a.length-2);i+=3)
            if ((x=MM_findObj(a[i]))!=null){document.MM_sr[j++]=x; if(!x.oSrc) x.oSrc=x.src; x.src=a[i+2];}
            }
        //-->
        </script>
    </HEAD>
    <body leftMargin="0" background="../imagenes/plantillas/bgpix.gif" topMargin="0">
        <form id="frmIndiceCronologico" method="post" runat="server">
            <table id="tbRevista" height="598" cellSpacing="0" cellPadding="0" width="1004px" border="0"
                runat="server">
                <tr>
                    <td vAlign="top" width="206" background="../imagenes/plantillas/bg_menu.gif" height="100%"
                        rowSpan="2"><uc1:uscmenuprincipal id="MenuPrincipal" runat="server"></uc1:uscmenuprincipal></td>
                    <td vAlign="top" align="right" background="../imagenes/plantillas/bg_contenido.gif"
                        height="546">
                        <table id="tbContenido" height="490" cellSpacing="0" cellPadding="0" width="780" border="0"
                            runat="server">
                            <tr>
                                <td align="center" height="65"><IMG height="48" src="../imagenes/plantillas/titindicrono.gif" width="258" border="0">
                                </td>
                            </tr>
                            <tr>
                                <td height="25px"></td>
                            </tr>
                            <tr>
                                <td>

                                    <div id="dvMensajesError" style="LEFT: 500px; BACKGROUND-IMAGE: url(../imagenes/Plantillas/bgnube.gif); VISIBILITY: visible; WIDTH: 350px; POSITION: absolute; TOP: 150px; HEIGHT: 205px"
                                        runat="server">
                                        <p style="PADDING-TOP: 75px" align="center"><asp:label id="lblError" Runat="server" CssClass="FormatoTextoError">Se ha producido una Error:</asp:label><BR>
                                            <asp:label id="lblNumeroError" Runat="server" CssClass="FormatoTextoError" Visible="False"></asp:label><asp:label id="lblOrigenError" Runat="server" CssClass="FormatoTextoError" Visible="False"></asp:label><asp:label id="lblDescripcionError" Runat="server" CssClass="FormatoTextoError"></asp:label><br>
                                            <asp:button id="btnOtraSuscripcion" Runat="server" CssClass="BotonComando" Text="Volver"></asp:button></p>
                                    </div>
                                    <div id="dvIndiceCronologico" style="PADDING-LEFT: 25px; OVERFLOW: auto; WIDTH: 758px; HEIGHT: 420px"
                                        runat="server"><asp:datagrid id="dtgIndiceCronologicoRevistas" runat="server" CssClass="FormatoTexto" AutoGenerateColumns="False"
                                            ShowHeader="False" Width="100%" BackColor="Transparent" BorderStyle="None" BorderWidth="0" GridLines="None" ItemStyle-Height="50px">
                                            <Columns>
                                                <asp:TemplateColumn>
                                                    <ItemTemplate>
                                                        <a href="#" onMouseOut="MM_swapImgRestore()" onMouseOver="MM_swapImage('Image161','','../imagenes/Plantillas/<%# DataBinder.Eval(Container.DataItem, "ANNO") %>b.gif',1)">
                                                            <img name="Image161" border="0" src="../imagenes/Plantillas/<%# DataBinder.Eval(Container.DataItem, "ANNO") %>a.gif" width="64" height="35"></a>
                                                    </ItemTemplate>
                                                </asp:TemplateColumn>
                                                <asp:TemplateColumn>
                                                    <ItemTemplate>
                                                        <div align="left">
                                                            <a href="Revista.aspx?an=id=<%# DataBinder.Eval(Container.DataItem, "1TNUMERO") %>&tipo=<%# DataBinder.Eval(Container.DataItem, "1TIPO") %>" class="EnlaceTituloApartado"><%# DataBinder.Eval(Container.DataItem, "1TNUMEROTEXTO") %><br><%# DataBinder.Eval(Container.DataItem, "1TTRIMESTRE") %></a>
                                                        </div>
                                                    </ItemTemplate>
                                                </asp:TemplateColumn>
                                                <asp:TemplateColumn>
                                                    <ItemTemplate>
                                                        <div align="left">
                                                            <a href="Revista.aspx?id=<%# DataBinder.Eval(Container.DataItem, "2TNUMERO") %>&tipo=<%# DataBinder.Eval(Container.DataItem, "2TIPO") %>" class="EnlaceTituloApartado"><%# DataBinder.Eval(Container.DataItem, "2TNUMEROTEXTO") %><br><%# DataBinder.Eval(Container.DataItem, "2TTRIMESTRE") %></a>
                                                        </div> </ItemTemplate>
                                                </asp:TemplateColumn>
                                                <asp:TemplateColumn>
                                                    <ItemTemplate>
                                                        <div align="left">
                                                            <a href="Revista.aspx?id=<%# DataBinder.Eval(Container.DataItem, "3TNUMERO") %>&tipo=<%# DataBinder.Eval(Container.DataItem, "3TIPO") %>" class="EnlaceTituloApartado"><%# DataBinder.Eval(Container.DataItem, "3TNUMEROTEXTO") %><br><%# DataBinder.Eval(Container.DataItem, "3TTRIMESTRE") %></a>
                                                        </div> </ItemTemplate>
                                                </asp:TemplateColumn>
                                                <asp:TemplateColumn>
                                                    <ItemTemplate>
                                                        <div align="left">
                                                            <a href="Revista.aspx?id=<%# DataBinder.Eval(Container.DataItem, "4TNUMERO") %>&tipo=<%# DataBinder.Eval(Container.DataItem, "4TIPO") %>" class="EnlaceTituloApartado"><%# DataBinder.Eval(Container.DataItem, "4TNUMEROTEXTO") %><br><%# DataBinder.Eval(Container.DataItem, "4TTRIMESTRE") %></a>
                                                        </div> </ItemTemplate>
                                                </asp:TemplateColumn>
                                            </Columns>
                                        </asp:datagrid>
                                        </div>
                                </td>
                            </tr>
                        </table>
                    </td>
                </tr>
                <tr>
                    <td height="50"><uc1:uscmenuinferior id="MenuInferior" runat="server"></uc1:uscmenuinferior></td>
                </tr>
            </table>
        </form>
    </body>
</HTML>


Any ideas??

Regards,

Codeman1234



 
Old April 21st, 2008, 09:48 AM
samjudson's Avatar
Friend of Wrox
 
Join Date: Aug 2007
Posts: 2,128
Thanks: 1
Thanked 189 Times in 188 Posts
Default

Code:
string SQL_QUERY = "select * from myTable";
This is a SQL statement executed on the server. If your table on the SQL server isn't called "myTable" then it will not execute. It doesn't CARE what the variable you want to store it in in your code is called.

/- Sam Judson : Wrox Technical Editor -/
 
Old April 21st, 2008, 09:56 AM
samjudson's Avatar
Friend of Wrox
 
Join Date: Aug 2007
Posts: 2,128
Thanks: 1
Thanked 189 Times in 188 Posts
Default

I don't see an error in your previous post? What error do you get? Has this table got a field called "ANNO"?

/- Sam Judson : Wrox Technical Editor -/
 
Old April 21st, 2008, 10:46 AM
Authorized User
 
Join Date: Apr 2008
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I am on it I need to know how to do with the DataGrid to show the result of the query in rows??

I am almost there u guys are the best :)

Regards,

Codeman1234

 
Old April 23rd, 2008, 03:06 AM
Authorized User
 
Join Date: Apr 2008
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I got the query working, :)

The thing I am trying to figure out is how I can do to use DataTable to use it and play with it like an array in C#.

Thank you very much,

Regards,

Codeman1234






Similar Threads
Thread Thread Starter Forum Replies Last Post
sql query i need seearam MySQL 7 November 30th, 2008 03:14 AM
Output Query to txt file from SQL Query everest SQL Server 2005 4 November 22nd, 2007 01:49 AM
SQL Query!! dpkbahuguna Beginning VB 6 5 October 12th, 2007 12:39 AM
Help with SQL query sattaluri Access 2 August 11th, 2006 09:26 AM
SQL query PinkyCat Classic ASP Databases 3 March 11th, 2005 01:41 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.