Search Results

Search found 204 results on 9 pages for 'sqldatareader'.

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

Page 3/9 | < Previous Page | 1 2 3 4 5 6 7 8 9  | Next Page >

  • Dropdownlist and Datareader

    - by salvationishere
    After trying many solutions listed on the internet I am very confused now. I have a C#/SQL web application for which I am simply trying to bind an ExecuteReader command to a Dropdownlist so the user can select a value. This is a VS2008 project on an XP OS. How it works is after the user selects a table, I use this selection as an input parameter to a method from my Datamatch.aspx.cs file. Then this Datamatch.aspx.cs file calls a method from my ADONET.cs class file. Finally this method executes a SQL procedure to return the list of columns from that table. (These are all tables in Adventureworks DB). I know that this method returns successfully the list of columns if I execute this SP in SSMS. However, I'm not sure how to tell if it works in VS or not. This should be simple. How can I do this? Here is some of my code. The T-sQL stored proc: CREATE PROCEDURE [dbo].[getColumnNames] @TableName VarChar(50) AS BEGIN SET NOCOUNT ON; SELECT col.name 'COLUMN_NAME' FROM sysobjects obj INNER JOIN syscolumns col ON obj.id = col.id WHERE obj.name = @TableName END It gives me desired output when I execute following from SSMS: exec getColumnNames 'AddressType' And the code from Datamatch.aspx.cs file currently is: // Add DropDownList Control to Placeholder private void CreateDropDownLists() { SqlDataReader dr2 = ADONET_methods.DisplayTableColumns(targettable); int NumControls = targettable.Length; DropDownList ddl = new DropDownList(); } Where ADONET_methods.DisplayTableColumns(targettable) is: public static SqlDataReader DisplayTableColumns(string tt) { SqlDataReader dr = null; string TableName = tt; string connString = "Server=(local);Database=AdventureWorks;Integrated Security = SSPI"; string errorMsg; SqlConnection conn2 = new SqlConnection(connString); SqlCommand cmd = new SqlCommand("getColumnNames"); //conn2.CreateCommand(); try { cmd.CommandType = CommandType.StoredProcedure; cmd.Connection = conn2; SqlParameter parm = new SqlParameter("@TableName", SqlDbType.VarChar); parm.Value = "Person." + TableName.Trim(); parm.Direction = ParameterDirection.Input; cmd.Parameters.Add(parm); conn2.Open(); dr = cmd.ExecuteReader(); } catch (Exception ex) { errorMsg = ex.Message; } return dr; }

    Read the article

  • how to create a DataAccessLayer ?

    - by NIGHIL DAS
    hi, i am creating a database applicatin in .Net. I am using a DataAccessLayer for communicating .net objects with database but i am not sure that this class is correct or not Can anyone cross check it and rectify any mistakes namespace IDataaccess { #region Collection Class public class SPParamCollection : List<SPParams> { } public class SPParamReturnCollection : List<SPParams> { } #endregion #region struct public struct SPParams { public string Name { get; set; } public object Value { get; set; } public ParameterDirection ParamDirection { get; set; } public SqlDbType Type { get; set; } public int Size { get; set; } public string TypeName { get; set; } // public string datatype; } #endregion /// <summary> /// Interface DataAccess Layer implimentation New version /// </summary> public interface IDataAccess { DataTable getDataUsingSP(string spName); DataTable getDataUsingSP(string spName, SPParamCollection spParamCollection); DataSet getDataSetUsingSP(string spName); DataSet getDataSetUsingSP(string spName, SPParamCollection spParamCollection); SqlDataReader getDataReaderUsingSP(string spName); SqlDataReader getDataReaderUsingSP(string spName, SPParamCollection spParamCollection); int executeSP(string spName); int executeSP(string spName, SPParamCollection spParamCollection, bool addExtraParmas); int executeSP(string spName, SPParamCollection spParamCollection); DataTable getDataUsingSqlQuery(string strSqlQuery); int executeSqlQuery(string strSqlQuery); SPParamReturnCollection executeSPReturnParam(string spName, SPParamReturnCollection spParamReturnCollection); SPParamReturnCollection executeSPReturnParam(string spName, SPParamCollection spParamCollection, SPParamReturnCollection spParamReturnCollection); SPParamReturnCollection executeSPReturnParam(string spName, SPParamCollection spParamCollection, SPParamReturnCollection spParamReturnCollection, bool addExtraParmas); int executeSPReturnParam(string spName, SPParamCollection spParamCollection, ref SPParamReturnCollection spParamReturnCollection); object getScalarUsingSP(string spName); object getScalarUsingSP(string spName, SPParamCollection spParamCollection); } } using IDataaccess; namespace Dataaccess { /// <summary> /// Class DataAccess Layer implimentation New version /// </summary> public class DataAccess : IDataaccess.IDataAccess { #region Public variables static string Strcon; DataSet dts = new DataSet(); public DataAccess() { Strcon = sReadConnectionString(); } private string sReadConnectionString() { try { //dts.ReadXml("C:\\cnn.config"); //Strcon = dts.Tables[0].Rows[0][0].ToString(); //System.Configuration.Configuration config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None); //Strcon = config.ConnectionStrings.ConnectionStrings["connectionString"].ConnectionString; // Add an Application Setting. //Strcon = "Data Source=192.168.50.103;Initial Catalog=erpDB;User ID=ipixerp1;Password=NogoXVc3"; Strcon = System.Configuration.ConfigurationManager.AppSettings["connection"]; //Strcon = System.Configuration.ConfigurationSettings.AppSettings[0].ToString(); } catch (Exception) { } return Strcon; } public SqlConnection connection; public SqlCommand cmd; public SqlDataAdapter adpt; public DataTable dt; public int intresult; public SqlDataReader sqdr; #endregion #region Public Methods public DataTable getDataUsingSP(string spName) { return getDataUsingSP(spName, null); } public DataTable getDataUsingSP(string spName, SPParamCollection spParamCollection) { try { using (connection = new SqlConnection(Strcon)) { connection.Open(); using (cmd = new SqlCommand(spName, connection)) { int count, param = 0; if (spParamCollection == null) { param = -1; } else { param = spParamCollection.Count; } for (count = 0; count < param; count++) { cmd.Parameters.AddWithValue(spParamCollection[count].Name, spParamCollection[count].Value); } cmd.CommandType = CommandType.StoredProcedure; cmd.CommandTimeout = 60; adpt = new SqlDataAdapter(cmd); dt = new DataTable(); adpt.Fill(dt); return (dt); } } } finally { connection.Close(); } } public DataSet getDataSetUsingSP(string spName) { return getDataSetUsingSP(spName, null); } public DataSet getDataSetUsingSP(string spName, SPParamCollection spParamCollection) { try { using (connection = new SqlConnection(Strcon)) { connection.Open(); using (cmd = new SqlCommand(spName, connection)) { int count, param = 0; if (spParamCollection == null) { param = -1; } else { param = spParamCollection.Count; } for (count = 0; count < param; count++) { cmd.Parameters.AddWithValue(spParamCollection[count].Name, spParamCollection[count].Value); } cmd.CommandType = CommandType.StoredProcedure; cmd.CommandTimeout = 60; adpt = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); adpt.Fill(ds); return ds; } } } finally { connection.Close(); } } public SqlDataReader getDataReaderUsingSP(string spName) { return getDataReaderUsingSP(spName, null); } public SqlDataReader getDataReaderUsingSP(string spName, SPParamCollection spParamCollection) { try { using (connection = new SqlConnection(Strcon)) { connection.Open(); using (cmd = new SqlCommand(spName, connection)) { int count, param = 0; if (spParamCollection == null) { param = -1; } else { param = spParamCollection.Count; } for (count = 0; count < param; count++) { cmd.Parameters.AddWithValue(spParamCollection[count].Name, spParamCollection[count].Value); } cmd.CommandType = CommandType.StoredProcedure; cmd.CommandTimeout = 60; sqdr = cmd.ExecuteReader(); return (sqdr); } } } finally { connection.Close(); } } public int executeSP(string spName) { return executeSP(spName, null); } public int executeSP(string spName, SPParamCollection spParamCollection, bool addExtraParmas) { try { using (connection = new SqlConnection(Strcon)) { connection.Open(); using (cmd = new SqlCommand(spName, connection)) { int count, param = 0; if (spParamCollection == null) { param = -1; } else { param = spParamCollection.Count; } for (count = 0; count < param; count++) { SqlParameter par = new SqlParameter(spParamCollection[count].Name, spParamCollection[count].Value); if (addExtraParmas) { par.TypeName = spParamCollection[count].TypeName; par.SqlDbType = spParamCollection[count].Type; } cmd.Parameters.Add(par); } cmd.CommandType = CommandType.StoredProcedure; cmd.CommandTimeout = 60; return (cmd.ExecuteNonQuery()); } } } finally { connection.Close(); } } public int executeSP(string spName, SPParamCollection spParamCollection) { return executeSP(spName, spParamCollection, false); } public DataTable getDataUsingSqlQuery(string strSqlQuery) { try { using (connection = new SqlConnection(Strcon)) connection.Open(); { using (cmd = new SqlCommand(strSqlQuery, connection)) { cmd.CommandType = CommandType.Text; cmd.CommandTimeout = 60; adpt = new SqlDataAdapter(cmd); dt = new DataTable(); adpt.Fill(dt); return (dt); } } } finally { connection.Close(); } } public int executeSqlQuery(string strSqlQuery) { try { using (connection = new SqlConnection(Strcon)) { connection.Open(); using (cmd = new SqlCommand(strSqlQuery, connection)) { cmd.CommandType = CommandType.Text; cmd.CommandTimeout = 60; intresult = cmd.ExecuteNonQuery(); return (intresult); } } } finally { connection.Close(); } } public SPParamReturnCollection executeSPReturnParam(string spName, SPParamReturnCollection spParamReturnCollection) { return executeSPReturnParam(spName, null, spParamReturnCollection); } public int executeSPReturnParam() { return 0; } public int executeSPReturnParam(string spName, SPParamCollection spParamCollection, ref SPParamReturnCollection spParamReturnCollection) { try { SPParamReturnCollection spParamReturned = new SPParamReturnCollection(); using (connection = new SqlConnection(Strcon)) { connection.Open(); using (cmd = new SqlCommand(spName, connection)) { int count, param = 0; if (spParamCollection == null) { param = -1; } else { param = spParamCollection.Count; } for (count = 0; count < param; count++) { cmd.Parameters.AddWithValue(spParamCollection[count].Name, spParamCollection[count].Value); } cmd.CommandType = CommandType.StoredProcedure; foreach (SPParams paramReturn in spParamReturnCollection) { SqlParameter _parmReturn = new SqlParameter(paramReturn.Name, paramReturn.Size); _parmReturn.Direction = paramReturn.ParamDirection; if (paramReturn.Size > 0) _parmReturn.Size = paramReturn.Size; else _parmReturn.Size = 32; _parmReturn.SqlDbType = paramReturn.Type; cmd.Parameters.Add(_parmReturn); } cmd.CommandTimeout = 60; intresult = cmd.ExecuteNonQuery(); connection.Close(); //for (int i = 0; i < spParamReturnCollection.Count; i++) //{ // spParamReturned.Add(new SPParams // { // Name = spParamReturnCollection[i].Name, // Value = cmd.Parameters[spParamReturnCollection[i].Name].Value // }); //} } } return intresult; } finally { connection.Close(); } } public SPParamReturnCollection executeSPReturnParam(string spName, SPParamCollection spParamCollection, SPParamReturnCollection spParamReturnCollection) { return executeSPReturnParam(spName, spParamCollection, spParamReturnCollection, false); } public SPParamReturnCollection executeSPReturnParam(string spName, SPParamCollection spParamCollection, SPParamReturnCollection spParamReturnCollection, bool addExtraParmas) { try { SPParamReturnCollection spParamReturned = new SPParamReturnCollection(); using (connection = new SqlConnection(Strcon)) { connection.Open(); using (cmd = new SqlCommand(spName, connection)) { int count, param = 0; if (spParamCollection == null) { param = -1; } else { param = spParamCollection.Count; } for (count = 0; count < param; count++) { //cmd.Parameters.AddWithValue(spParamCollection[count].Name, spParamCollection[count].Value); SqlParameter par = new SqlParameter(spParamCollection[count].Name, spParamCollection[count].Value); if (addExtraParmas) { par.TypeName = spParamCollection[count].TypeName; par.SqlDbType = spParamCollection[count].Type; } cmd.Parameters.Add(par); } cmd.CommandType = CommandType.StoredProcedure; foreach (SPParams paramReturn in spParamReturnCollection) { SqlParameter _parmReturn = new SqlParameter(paramReturn.Name, paramReturn.Value); _parmReturn.Direction = paramReturn.ParamDirection; if (paramReturn.Size > 0) _parmReturn.Size = paramReturn.Size; else _parmReturn.Size = 32; _parmReturn.SqlDbType = paramReturn.Type; cmd.Parameters.Add(_parmReturn); } cmd.CommandTimeout = 60; cmd.ExecuteNonQuery(); connection.Close(); for (int i = 0; i < spParamReturnCollection.Count; i++) { spParamReturned.Add(new SPParams { Name = spParamReturnCollection[i].Name, Value = cmd.Parameters[spParamReturnCollection[i].Name].Value }); } } } return spParamReturned; } catch (Exception ex) { return null; } finally { connection.Close(); } } public object getScalarUsingSP(string spName) { return getScalarUsingSP(spName, null); } public object getScalarUsingSP(string spName, SPParamCollection spParamCollection) { try { using (connection = new SqlConnection(Strcon)) { connection.Open(); using (cmd = new SqlCommand(spName, connection)) { int count, param = 0; if (spParamCollection == null) { param = -1; } else { param = spParamCollection.Count; } for (count = 0; count < param; count++) { cmd.Parameters.AddWithValue(spParamCollection[count].Name, spParamCollection[count].Value); cmd.CommandTimeout = 60; } cmd.CommandType = CommandType.StoredProcedure; return cmd.ExecuteScalar(); } } } finally { connection.Close(); cmd.Dispose(); } } #endregion } }

    Read the article

  • sql and pooled connection error

    - by user319075
    Dear all, Kindly look at the following code as this sample code gives an error when i hosted it on Hostexcellence , but locally it runs perfect, and the error is as the following: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached SqlDataSource1.SelectCommand = "Select Top (3) * from News Order by NewsID Desc"; SqlDataSource1.DataSourceMode = SqlDataSourceMode.DataReader; SqlDataReader r_News = (SqlDataReader)SqlDataSource1.Select(DataSourceSelectArguments.Empty); DataGrid_News.DataSource = r_News; r_News.Close(); DataGrid_News.DataBind(); So What's wrong with that code ??

    Read the article

  • ASP.NET (VB) - Close an opened SQL connection inside Function

    - by B1GB0Y
    Can anyone tell me how I close an opened SQL connection inside a Function? I call a Select Function like this: Function Selec(ByVal SQLStr As String) As SqlDataReader Dim SQLConn As New SqlConnection() Dim SQLCmd As New SqlCommand() SQLConn.ConnectionString = Session("bd") SQLConn.Open() SQLCmd.Connection = SQLConn SQLCmd.CommandText = SQLStr Selec = SQLCmd.ExecuteReader End Function And in another page I do a While method to retrieve me the data like this: (Note: BDcon.BD is the name of the Class that have Functions) Dim write as New BDcon.BD Dim menu As SqlDataReader = writeBD.Selec("SELECT something from Table") While menu.Read 'Do something End While menu.Close 'This close just the DataReader and not the SqlConnection Finally I want to Close my SQL Connection by Function like this: Function Close() As SqlConnection Dim SQLConn As New SqlConnection() SQLConn.ConnectionString = Session("bd") SQLConn.Close() End Function I think that the problem is on the Close() Function, I want to close the connection but I don't know how to call my Opened Conneciton. Please anyone can help me? Thanks in advance :)

    Read the article

  • Showing an image after loading it from sql database

    - by user330075
    I have a problem showing the image form database in a view Details and a ImageController. Inside the view I have: img src=Url.Action("GetFile","Image", new {id= Model.id}) and in controller: public FileContentResult GetFile(int idl) { //int idl = 32; SqlDataReader rdr; byte[] fileContent = null; ........... return File(,,); } When the view is called, function GetFile it just won't work. But if I cut out the parameter int id1 and I instantiate it as a variable it does work. public FileContentResult GetFile() { int idl = 32; SqlDataReader rdr; byte[] fileContent = null; ........... return File(,,); } Why?

    Read the article

  • Populating a WPF listbox with items from an SQL (SDF) database

    - by xplinux557
    I have been searching on how to do this for a very long time, and I have not managed to get a straight answer on the subject, so hopefully one of you StackOverflow users will be able to help me here. I have a WPF ListBox named CategoryList and a SDF database called ProgramsList.sdf (with two tables called CategoryList and ProgramsList). What I wish my program to do is get the category names from the CategoryList table and list them in the ListBox control called CategoryList. Here's the code that I tried, but it only caused my program to crash. SqlConnection myConnection = new SqlConnection("Data Source=" + AppDomain.CurrentDomain.BaseDirectory + "ProgramsList.sdf"); SqlDataReader myReader = null; myConnection.Open(); CategoryList.Items.Clear(); SqlDataReader dr = new SqlCommand("SELECT Name FROM CategoryList ORDER BY Name DESC", myConnection).ExecuteReader(); while (myReader.Read()) { CategoryList.Items.Add(dr.GetInt32(0)); } myConnection.Close(); Can anyone help me? Thanks in advance!

    Read the article

  • Showing an image after laoding it from sql database

    - by user330075
    I have a problem showing the image form database in a view Details and a ImageController. Inside the view i have: img src=Url.Action("GetFile","Image", new {id= Model.id}) and in controller: public FileContentResult GetFile(int idl) { //int idl = 32; SqlDataReader rdr; byte[] fileContent = null; ........... return File(,,); } When the view is called, function GetFile it just wont work,but if i cut out the parameter int id1 and i instantiate it as a variable it'l work. public FileContentResult GetFile() { int idl = 32; SqlDataReader rdr; byte[] fileContent = null; ........... return File(,,); } Why?

    Read the article

  • Right code to retrieve data from sql server database

    - by HasanGursoy
    Hi, I have some problems in database connection and wonder if I have something wrong in my code. Please review. This question is related: Switch between databases, use two databases simultaneously question. cs="Data Source=mywebsite.com;Initial Catalog=database;User Id=root;Password=toor;Connect Timeout=10;Pooling='true';" using (SqlConnection cnn = new SqlConnection(WebConfigurationManager.ConnectionStrings["cs"].ConnectionString)) { using (SqlCommand cmmnd = new SqlCommand("", cnn)) { try { cnn.Open(); #region Header & Description cmmnd.Parameters.Add("@CatID", SqlDbType.Int).Value = catId; cmmnd.CommandText = "SELECT UpperID, Title, Description FROM Categories WHERE CatID=@CatID;"; string mainCat = String.Empty, rootCat = String.Empty; using (SqlDataReader rdr = cmmnd.ExecuteReader()) { if (rdr.Read()) { mainCat = rdr["Title"].ToString(); upperId = Convert.ToInt32(rdr["UpperID"]); description = rdr["Title"]; } else { Response.Redirect("/", false); } } if (upperId > 0) //If upper category exists add its name { cmmnd.Parameters["@CatID"].Value = upperId; cmmnd.CommandText = "SELECT Title FROM Categories WHERE CatID=@CatID;"; using (SqlDataReader rdr = cmmnd.ExecuteReader()) { if (rdr.Read()) { rootCat = "<a href='x.aspx'>" + rdr["Title"] + "</a> &raquo; "; } } } #endregion #region Sub-Categories if (upperId == 0) //show only at root categories { cmmnd.Parameters["@CatID"].Value = catId; cmmnd.CommandText = "SELECT Count(CatID) FROM Categories WHERE UpperID=@CatID;"; if (Convert.ToInt32(cmmnd.ExecuteScalar()) > 0) { cmmnd.CommandText = "SELECT CatID, Title FROM Categories WHERE UpperID=@CatID ORDER BY Title;"; using (SqlDataReader rdr = cmmnd.ExecuteReader()) { while (rdr.Read()) { subcat.InnerHtml += "<a href='x.aspx'>" + rdr["Title"].ToString().ToLower() + "</a>\n"; description += rdr["Title"] + ", "; } } } } #endregion } catch (Exception ex) { HasanG.LogException(ex, Request.RawUrl, HttpContext.Current); Response.Redirect("/", false); } finally { cnn.Close(); } } } The random errors I'm receiving are: A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.) A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached. Cannot open database "db" requested by the login. The login failed. Login failed for user 'root'.

    Read the article

  • Ajax Autocomplete Extender

    - by Jason Ulloa
    El objetivo de este post es preparar un ejemplo sobre un tema que es planteado muy frecuentemente en los Foros de MSDN, como realizar un Autocomplete contra una base de datos. Qué requerimos? Antes de poder realizar un Autocomplete debemos tener en cuenta los elementos principales que requerimos para poder hacerlo funcionar, descritos de la siguiente manera: 1. Textbox: Nuestro grandioso amigo Textbox, que será donde el usuario ingresará los datos a buscar. 2. Un Webservice: que contendrá el método que se conectara a la base de datos y devolverá una lista con la información encontrada. 3. Ajax Autocomplete Extender: este es por decirlo así, el elemento más importante. Nos servirá como medio de enlace entre el webservice que expone el método y el textbox recuperando y mostrando los datos en forma de lista desplegable. La implementación Si bien parecierá complicado, crear un autocomplete extender es bastante sencillo. Empezaremos creando un nuevo sitio asp.net, en este sitio agregaremos un textbox y dos controles muy importantes de Ajax el ToolkitScriptManager para controlar el rende rizado de los script de ajax y el AutocompleteExtender que, como mencione anteriormente, será el medio de enlace. Antes de mostrar como quedará el código de lo anterior, explicaré algunas propiedades del AutocompleteExtender para que se entienda de mejor manera: 1. El ServicePath: contiene la ruta relativa al webservice que utilizaremos. 2. MinimumPrefixLength: se refiere al número de caracteres que deben ser digitados antes de iniciar la búsqueda. 3. ServiceMethod: el nombre del metodo de nuestro webservice que se encargará de devolver los datos. 4. EnableCaching: para mantener en cache los datos consultados, obteniendo mayor velocidad. 5. TargetControlID: una de las propiedades más importantes, acá se coloca el nombre del textbox al cual se unirá el Autocomplete 6. CompletionInterval: tiempo que debe transcurrir antes de iniciar con el trabajo de los datos. Una vez, explicadas las propiedades básicas, veamos como queda implementada la primer parte de nuestro autocomplete: <form id="form1" runat="server"> <div> <asp:ToolkitScriptManager ID="manager" runat="server" /> <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox> <asp:AutoCompleteExtender ID="AutoCompleteExtender1" runat="server" ServicePath="WebService.asmx" MinimumPrefixLength="1" ServiceMethod="PersonasInfo " EnableCaching="true" TargetControlID="TextBox1" UseContextKey="True" CompletionSetCount="10" CompletionInterval="0"> </asp:AutoCompleteExtender> </div> </form>   Ahora que nuestro código html está completo, es hora de trabajar directamente con nuestro webservice, este deberá contener un método que devuelva una lista o arreglo de datos, los cuales por supuesto, serán traídos desde la base de datos. Antes de implementar este método, debemos asegurarnos de que nuestra clase del webservice tiene habilitados los espacios para ser utilizada [System.Web.Script.Services.ScriptService()] [WebService(Namespace = "http://tempuri.org/")] [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)] public class WebService : System.Web.Services.WebService {}   Ahora si, nuestro metodo principal [WebMethod()] [System.Web.Script.Services.ScriptMethod()] public string[] PersonasInfo(string prefixText, int count) { string connstring = ConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString;   using (SqlConnection conn = new SqlConnection(connstring)) { SqlCommand comando = new SqlCommand("select nombre from personas where nombre LIKE '%' + @param + '%' ", conn); comando.Parameters.AddWithValue("@param", prefixText); SqlDataReader dr = default(SqlDataReader); comando.Connection.Open(); dr = comando.ExecuteReader(); List<string> items = new List<string>();   while (dr.Read()) { items.Add(dr["nombre"].ToString()); } comando.Connection.Close(); return items.ToArray(); } }   Del método anterior no explicaré en profundidad, pues es bastante sencillo. Una consulta a la base de datos utilizando un datareader y devolviendo los datos en una lista como arreglo. Lo más importante serían las 2 primeras líneas [WebMethod()] y el [ScriptMethod()] las cuales habilitan nuestro método para poder ser accedido y utilizado. Por último, el código de ejemplo en C# (VB Autcomplete):

    Read the article

  • Getting Error System.Runtime.InteropServices.COMException

    - by Savan Parmar
    Hey All, I am using Vb.Net to Create Labels in Microsoft. For that i am using below mantioend Code. Public Sub CreateLabel(ByVal StrFilter As String, ByVal Path As String) WordApp = CreateObject("Word.Application") ''Add a new document. WordDoc = WordApp.Documents.Add() Dim oConn As SqlConnection = New SqlConnection(connSTR) oConn.Open() Dim oCmd As SqlCommand Dim oDR As SqlDataReader oCmd = New SqlCommand(StrFilter, oConn) oDR = oCmd.ExecuteReader Dim intI As Integer Dim FilePath As String = "" With WordDoc.MailMerge With .Fields Do While oDR.Read For intI = 0 To oDR.FieldCount - 1 .Add(WordApp.Selection.Range, oDR.Item(intI)) Next Loop End With Dim objAutoText As Word.AutoTextEntry = WordApp.NormalTemplate.AutoTextEntries.Add("MyLabelLayout", WordDoc.Content) WordDoc.Content.Delete() .MainDocumentType = Word.WdMailMergeMainDocType.wdMailingLabels FilePath = CreateSource(StrFilter) .OpenDataSource(FilePath) Dim NewLabel As Word.CustomLabel = WordApp.MailingLabel.CustomLabels.Add("MyLabel", False) WordApp.MailingLabel.CreateNewDocument(Name:="MyLabel", Address:="", AutoText:="MyLabelLayout") objAutoText.Delete() .Destination = Word.WdMailMergeDestination.wdSendToNewDocument WordApp.Visible = True .Execute() End With oConn.Close() WordDoc.Close() End Sub Private Function CreateSource(ByVal StrFilter As String) As String Dim CnnUser As SqlConnection = New SqlConnection(connSTR) Dim sw As StreamWriter = File.CreateText("C:\Mail.Txt") Dim Path As String = "C:\Mail.Txt" Dim StrHeader As String = "" Try Dim SelectCMD As SqlCommand = New SqlCommand(StrFilter, CnnUser) Dim oDR As SqlDataReader Dim IntI As Integer SelectCMD.CommandType = CommandType.Text CnnUser.Open() oDR = SelectCMD.ExecuteReader For IntI = 0 To oDR.FieldCount - 1 StrHeader &= oDR.GetName(IntI) & " ," Next StrHeader = Mid(StrHeader, 1, Len(StrHeader) - 2) sw.WriteLine(StrHeader) sw.Flush() sw.Close() StrHeader = "" Do While oDR.Read For IntJ As Integer = 0 To oDR.FieldCount - 1 StrHeader &= oDR.GetString(IntJ) & " ," Next Loop StrHeader = Mid(StrHeader, 1, Len(StrHeader) - 2) sw = File.AppendText(Path) sw.WriteLine(StrHeader) CnnUser.Close() sw.Flush() sw.Close() Catch ex As Exception MessageBox.Show(ex.Message, "TempID", MessageBoxButtons.OK, MessageBoxIcon.Error) End Try Return Path End Function Now when i am running the programm i am getting this error.I tried hard but not able to locate what could be the problem the error is:- System.Runtime.InteropServices.COMException --Horizontal and vertical pitch must be greater than or equal to the label width and height, respectively. Even though i tried to set the Horizontal and vertical pitch programatically but it gives same err. Plz if any one can help

    Read the article

  • Creating generic list of instances of a class.

    - by Jim Branson
    I have several projects where I build a dictionary from a small class. I'm using C# 2008, Visual studio 2008 and .net 3.5 This is the code: namespace ReportsTest { class Junk { public static Dictionary<string, string> getPlatKeys() { Dictionary<string, string> retPlatKeys = new Dictionary<string, string>(); SqlConnection conn = new SqlConnection("Data Source=JB55LTARL;Initial Catalog=HldiReports;Integrated Security=True"); SqlDataReader Dr = null; conn.Open(); SqlCommand cmnd = new SqlCommand("SELECT Make, Series, RedesignYear, SeriesName FROM CompPlatformKeys", conn); Dr = cmnd.ExecuteReader(); while (Dr.Read()) { utypPlatKeys rec = new utypPlatKeys(Dr); retPlatKeys.Add(rec.Make + rec.Series + rec.RedesignYear, rec.SeriesName); } conn = null; Dr = null; return retPlatKeys; } } public class utypPlatKeys { public string Make { get; set; } public string Series { get; set; } public string RedesignYear { get; set; } public string SeriesName { get; set; } public utypPlatKeys(SqlDataReader dr) { this.Make = dr.GetInt16(dr.GetOrdinal("Make")).ToString("D3"); this.Series = dr.GetInt16(dr.GetOrdinal("Series")).ToString("D3"); this.RedesignYear = dr.GetInt16(dr.GetOrdinal("RedesignYear")).ToString(); this.SeriesName = dr["SeriesName"].ToString(); } } } The immediate window shows all of the entries in retPlatKeys and if you hover over retPlatKeys after loading it indicates the number of elements like this: "retPlatKeys| Count = 923 which is correct. I went to create a new project using this pattern only now the immediate window says retPlatKeys is out of scope and hovering over retPlatKeys after loading I get something like retPlatKeys|0x0000000002578900. Any help is greatly appreciated.

    Read the article

  • weird result with c# winForms array of Lists

    - by jello
    so I'm trying to store values in an array of Lists in C# winForms. In the for loop in which I make the sql statment, everything works fine: the message box outputs a different medication name each time. for (int i = 0; i < numberOfMeds; i++) { queryStr = "select * from biological where medication_name = '" + med_names[i] + "' and patient_id = " + patientID.patient_id; using (var conn = new SqlConnection(connStr)) using (var cmd = new SqlCommand(queryStr, conn)) { conn.Open(); using (SqlDataReader rdr = cmd.ExecuteReader()) { while (rdr.Read()) { medObject.medication_date = (DateTime)rdr["patient_history_date_bio"]; medObject.medication_name = rdr["medication_name"].ToString(); medObject.medication_dose = Convert.ToInt32(rdr["medication_dose"]); medsList[i].Add(medObject); } } conn.Close(); MedicationTimelineClass medObjectx = medsList[i][0] as MedicationTimelineClass; MessageBox.Show(medObjectx.medication_name); } } but then, when I take the message box code out of the loop, meaning that the array of Lists is supposed to be populated, I always get the same value: the last value entered. the same medication name, no matter what number I put between those brackets. It's like if the whole array of Lists is populated with the same data. for (int i = 0; i < numberOfMeds; i++) { queryStr = "select * from biological where medication_name = '" + med_names[i] + "' and patient_id = " + patientID.patient_id; using (var conn = new SqlConnection(connStr)) using (var cmd = new SqlCommand(queryStr, conn)) { conn.Open(); using (SqlDataReader rdr = cmd.ExecuteReader()) { while (rdr.Read()) { medObject.medication_date = (DateTime)rdr["patient_history_date_bio"]; medObject.medication_name = rdr["medication_name"].ToString(); medObject.medication_dose = Convert.ToInt32(rdr["medication_dose"]); medsList[i].Add(medObject); } } conn.Close(); } } MedicationTimelineClass medObjectx = medsList[0][0] as MedicationTimelineClass; MessageBox.Show(medObjectx.medication_name); what's going on here?

    Read the article

  • ADO.NET Batch Insert with over 2000 parameters

    - by Liming
    Hello all, I'm using Enterprise library, but the idea is the same. I have a SqlStringCommand and the sql is constructed using StringBuilder in the forms of "insert into table (column1, column2, column3) values (@param1-X, @param2-X, @parm3-X)"+" " where "X" represents a "for loop" about 700 rows StringBuilder sb = new StringBuilder(); for(int i=0; i<700; i++) { sb.Append("insert into table (column1, column2, column3) values (@param1-"+i+", @param2-"+i, +",@parm3-"+i+") " ); } followed by constructing a command object injecting all the parameters w/ values into it. Essentially, 700 rows with 3 parameters, I ended up with 2100 parameters for this "one sql" Statement. It ran fine for about a few days and suddenly I got this error =============================================================== A severe error occurred on the current command. The results, if any, should be discarded. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNon Any pointers are greatly appreciated.

    Read the article

  • label as dynamic navigation

    - by KareemSaad
    I Made label to had value from database and linked it . but the problem was it had only one value and i want it keep value when navigation thought site as. xyz.... this is my code < if (Request.QueryString["Category_Id"] != null) { Banar.ImageUrl = "Handlers/Banner.ashx?Category_Id=" + Request.QueryString["Category_Id"] + ""; using (SqlConnection conn = Connection.GetConnection()) { SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "Navcategory"; cmd.Parameters.Add(Parameter.NewInt("@Category_Id", Request.QueryString["Category_Id"])); SqlDataReader dr = cmd.ExecuteReader(); if (dr.Read()) { LblNavigaton.Visible = true; LblNavigaton.Text = dr[i].ToString(); NavHref.HRef = "ListView.aspx?Category_Id=" + Request.QueryString["Category_Id"] + ""; } } } else if (Request.QueryString["ProductCategory_Id"] != null) { Banar.ImageUrl = "Handlers/ProCatBanner.ashx?ProductCategory_Id=" + Request.QueryString["ProductCategory_Id"] + ""; using (SqlConnection conn = Connection.GetConnection()) { SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "NavProductcategory"; cmd.Parameters.Add(Parameter.NewInt("@ProductCategory_Id", Request.QueryString["ProductCategory_Id"])); SqlDataReader dr = cmd.ExecuteReader(); if (dr.Read()) { LblNavigaton.Visible = true; LblNavigaton.Text = dr["Name"].ToString(); NavHref.HRef = "ListView.aspx?ProductCategory_Id=" + Request.QueryString["ProductCategory_Id"] + ""; } else { LblNavigaton.Visible = true; LblNavigaton.Text = Page.Title; } }

    Read the article

  • fill dropdown list by querystring

    - by KareemSaad
    I had drop down list and I want to fill it with data by specific condition i used this code but it was,t worked well <cs> protected void Page_Load(object sender, EventArgs e) { Page.Title = "ThumbnailViewPage"; if (Request.QueryString["Category_Id"] != null) { using (SqlConnection Con = Connection.GetConnection()) { SqlCommand Com = new SqlCommand("GetProducFamilyTP2", Con); Com.CommandType = CommandType.StoredProcedure; Com.Parameters.Add(Parameter.NewInt("@Category_Id", Request.QueryString["Category_Id"])); SqlDataReader DR = Com.ExecuteReader(); if (DR.Read()) { DDlProductFamily.DataTextField = DR["Name"].ToString(); DDlProductFamily.DataValueField = DR["ProductCategory_Id"].ToString(); } } } else if (Request.QueryString["ProductCategory_Id"] != null) { using (SqlConnection Con = Connection.GetConnection()) { SqlCommand Com = new SqlCommand("GetProducFamilyTP3", Con); Com.CommandType = CommandType.StoredProcedure; Com.Parameters.Add(Parameter.NewInt("@ProductCategory_Id", Request.QueryString["ProductCategory_Id"])); SqlDataReader DR = Com.ExecuteReader(); if (DR.Read()) { DDlProductFamily.DataTextField = DR["Name"].ToString(); DDlProductFamily.DataValueField = DR["ProductCategory_Id"].ToString(); } } } } <asp:UpdatePanel ID="UpdatePanel3" runat="server"> <ContentTemplate> <asp:DropDownList ID="DDlProductFamily" runat="server" ondatabound="DDlProductFamily_DataBound" onload="DDlProductFamily_Load" onselectedindexchanged="DDlProductFamily_SelectedIndexChanged"> </asp:DropDownList> </ContentTemplate> <Triggers> <asp:AsyncPostBackTrigger ControlID="DDlProductFamily" EventName="SelectedIndexChanged" /> </Triggers> </asp:UpdatePanel>

    Read the article

  • MS Dynamics CRM trapping .NET error before I can handle it

    - by clifgriffin
    This is a fun one. I have written a custom search page that provides faster, more user friendly searches than the default Contacts view and also allows searching of Leads and Contacts simultaneously. It uses GridViews bound to SqlDataSources that query filtered views. I'm sure someone will complain that I'm not using the web services for this, but this is just the design decision we made. These GridViews live in UpdatePanels to enable very slick AJAX updates upon search. It's all working great. Nearly ready to be deployed, except for one thing: Some long running searches are triggering an uncatchable SQL timeout exception. [SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.] at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) at System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) at System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) at System.Web.UI.WebControls.DataBoundControl.PerformSelect() at System.Web.UI.WebControls.BaseDataBoundControl.DataBind() at System.Web.UI.WebControls.GridView.DataBind() at System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() at System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() at System.Web.UI.Control.EnsureChildControls() at System.Web.UI.Control.PreRenderRecursiveInternal() at System.Web.UI.Control.PreRenderRecursiveInternal() at System.Web.UI.Control.PreRenderRecursiveInternal() at System.Web.UI.Control.PreRenderRecursiveInternal() at System.Web.UI.Control.PreRenderRecursiveInternal() at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) I found that CRM is doing a server.transfer to capture this error because my UpdatePanels started throwing JavaSript errors when this error would occur. I was only able to get the full error message by using the JavaScript debugger in IE. Having found this error, I thought the solution would be simple. I just needed to wrap my databind calls in try/catch blocks to capture any errors. Unfortunately it seems CRM's IIS configuration has the magic ability to capture this error before it ever gets back to my code. Using the debugger I never see it. It never gets to my catch blocks, but it's clearly happening in the SQL Data Source which is clearly (by the stack trace) being triggered by my GridView bind. Any ideas on this? It's driving me crazy. Code Behind (with some irrelevant functions omitted): protected void Page_Load(object sender, EventArgs e) { //Initialize some stuff this.bannerOracle = new OdbcConnection(ConfigurationManager.ConnectionStrings["OracleConnectionString"].ConnectionString); //Prospect default HideProspects(); HideProspectAddressColumn(); //Contacts default HideContactAddressColumn(); //Default error messages gvContacts.EmptyDataText = "Sad day. Your search returned no contacts."; gvProspects.EmptyDataText = "Sad day. Your search returned no prospects."; //New search try { SearchContact(null, -1); } catch { gvContacts.EmptyDataText = "Oops! An error occured. This may have been a timeout. Please try your search again."; gvContacts.DataSource = null; gvContacts.DataBind(); } } protected void txtSearchString_TextChanged(object sender, EventArgs e) { if(!String.IsNullOrEmpty(txtSearchString.Text)) { try { SearchContact(txtSearchString.Text, Convert.ToInt16(lstSearchType.SelectedValue)); } catch { gvContacts.EmptyDataText = "Oops! An error occured. This may have been a timeout. Please try your search again."; gvContacts.DataSource = null; gvContacts.DataBind(); } if (chkProspects.Checked == true) { try { SearchProspect(txtSearchString.Text, Convert.ToInt16(lstSearchType.SelectedValue)); } catch { gvProspects.EmptyDataText = "Oops! An error occured. This may have been a timeout. Please try your search again."; gvProspects.DataSource = null; gvProspects.DataBind(); } finally { ShowProspects(); } } else { HideProspects(); } } } protected void SearchContact(string search, int type) { SqlCRM_Contact.ConnectionString = ConfigurationManager.ConnectionStrings["MSSQLConnectionString"].ConnectionString; gvContacts.DataSourceID = "SqlCRM_Contact"; string strQuery = ""; string baseQuery = @"SELECT filteredcontact.contactid, filteredcontact.new_libertyid, filteredcontact.fullname, 'none' AS line1, filteredcontact.emailaddress1, filteredcontact.telephone1, filteredcontact.birthdateutc AS birthdate, filteredcontact.gendercodename FROM filteredcontact "; switch(type) { case LASTFIRST: strQuery = baseQuery + "WHERE fullname LIKE @value AND filteredcontact.statecode = 0"; SqlCRM_Contact.SelectCommand = strQuery; SqlCRM_Contact.SelectParameters.Add("value", DbType.String, search.Trim() + "%"); break; case LAST: strQuery = baseQuery + "WHERE lastname LIKE @value AND filteredcontact.statecode = 0"; SqlCRM_Contact.SelectCommand = strQuery; SqlCRM_Contact.SelectParameters.Add("value", DbType.String, search.Trim() + "%"); break; case FIRST: strQuery = baseQuery + "WHERE firstname LIKE @value AND filteredcontact.statecode = 0"; SqlCRM_Contact.SelectCommand = strQuery; SqlCRM_Contact.SelectParameters.Add("value", DbType.String, search.Trim() + "%"); break; case LIBERTYID: strQuery = baseQuery + "WHERE new_libertyid LIKE @value AND filteredcontact.statecode = 0"; SqlCRM_Contact.SelectCommand = strQuery; SqlCRM_Contact.SelectParameters.Add("value", DbType.String, search.Trim() + "%"); break; case EMAIL: strQuery = baseQuery + "WHERE emailaddress1 LIKE @value AND filteredcontact.statecode = 0"; SqlCRM_Contact.SelectCommand = strQuery; SqlCRM_Contact.SelectParameters.Add("value", DbType.String, search.Trim() + "%"); break; case TELEPHONE: strQuery = baseQuery + "WHERE telephone1 LIKE @value AND filteredcontact.statecode = 0"; SqlCRM_Contact.SelectCommand = strQuery; SqlCRM_Contact.SelectParameters.Add("value", DbType.String, search.Trim() + "%"); break; case BIRTHDAY: strQuery = baseQuery + "WHERE filteredcontact.birthdateutc BETWEEN @dateStart AND @dateEnd AND filteredcontact.statecode = 0"; try { DateTime temp = DateTime.Parse(search); if (temp.Year < 1753 || temp.Year > 9999) { search = string.Empty; } else { search = temp.ToString("yyyy-MM-dd"); } } catch { search = string.Empty; } SqlCRM_Contact.SelectCommand = strQuery; SqlCRM_Contact.SelectParameters.Add("dateStart", DbType.String, search.Trim() + " 00:00:00.000"); SqlCRM_Contact.SelectParameters.Add("dateEnd", DbType.String, search.Trim() + " 23:59:59.999"); break; case SSN: //Do something break; case ADDRESS: strQuery = @"SELECT contactid, new_libertyid, fullname, line1, emailaddress1, telephone1, birthdate, gendercodename FROM (SELECT FC.contactid, FC.new_libertyid, FC.fullname, FA.line1, FC.emailaddress1, FC.telephone1, FC.birthdateutc AS birthdate, FC.gendercodename, ROW_NUMBER() OVER(PARTITION BY FC.contactid ORDER BY FC.contactid DESC) AS rn FROM filteredcontact FC INNER JOIN FilteredCustomerAddress FA ON FC.contactid = FA.parentid WHERE FA.line1 LIKE @value AND FA.addressnumber <> 1 AND FC.statecode = 0 ) AS RESULTS WHERE rn = 1"; SqlCRM_Contact.SelectCommand = strQuery; SqlCRM_Contact.SelectParameters.Add("value", DbType.String, search.Trim() + "%"); ShowContactAddressColumn(); break; default: strQuery = @"SELECT TOP 500 filteredcontact.contactid, filteredcontact.new_libertyid, filteredcontact.fullname, 'none' AS line1, filteredcontact.emailaddress1, filteredcontact.telephone1, filteredcontact.birthdateutc AS birthdate, filteredcontact.gendercodename FROM filteredcontact WHERE filteredcontact.statecode = 0"; SqlCRM_Contact.SelectCommand = strQuery; break; } if (type != ADDRESS) { HideContactAddressColumn(); } gvContacts.PageIndex = 0; //try //{ // SqlCRM_Contact.DataBind(); //} //catch //{ // SqlCRM_Contact.DataBind(); //} gvContacts.DataBind(); } protected void SearchProspect(string search, int type) { SqlCRM_Prospect.ConnectionString = ConfigurationManager.ConnectionStrings["MSSQLConnectionString"].ConnectionString; gvProspects.DataSourceID = "SqlCRM_Prospect"; string strQuery = ""; string baseQuery = @"SELECT filteredlead.leadid, filteredlead.fullname, 'none' AS address1_line1, filteredlead.emailaddress1, filteredlead.telephone1, filteredlead.lu_dateofbirthutc AS lu_dateofbirth, filteredlead.lu_gendername FROM filteredlead "; switch (type) { case LASTFIRST: strQuery = baseQuery + "WHERE fullname LIKE @value AND filteredlead.statecode = 0"; SqlCRM_Prospect.SelectCommand = strQuery; SqlCRM_Prospect.SelectParameters.Add("value", DbType.String, search.Trim() + "%"); break; case LAST: strQuery = baseQuery + "WHERE lastname LIKE @value AND filteredlead.statecode = 0"; SqlCRM_Prospect.SelectCommand = strQuery; SqlCRM_Prospect.SelectParameters.Add("value", DbType.String, search.Trim() + "%"); break; case FIRST: strQuery = baseQuery + "WHERE firstname LIKE @value AND filteredlead.statecode = 0"; SqlCRM_Prospect.SelectCommand = strQuery; SqlCRM_Prospect.SelectParameters.Add("value", DbType.String, search.Trim() + "%"); break; case LIBERTYID: strQuery = baseQuery + "WHERE new_libertyid LIKE @value AND filteredlead.statecode = 0"; SqlCRM_Prospect.SelectCommand = strQuery; SqlCRM_Prospect.SelectParameters.Add("value", DbType.String, search.Trim() + "%"); break; case EMAIL: strQuery = baseQuery + "WHERE emailaddress1 LIKE @value AND filteredlead.statecode = 0"; SqlCRM_Prospect.SelectCommand = strQuery; SqlCRM_Prospect.SelectParameters.Add("value", DbType.String, search.Trim() + "%"); break; case TELEPHONE: strQuery = baseQuery + "WHERE telephone1 LIKE @value AND filteredlead.statecode = 0"; SqlCRM_Prospect.SelectCommand = strQuery; SqlCRM_Prospect.SelectParameters.Add("value", DbType.String, search.Trim() + "%"); break; case BIRTHDAY: strQuery = baseQuery + "WHERE filteredlead.lu_dateofbirth BETWEEN @dateStart AND @dateEnd AND filteredlead.statecode = 0"; try { DateTime temp = DateTime.Parse(search); if (temp.Year < 1753 || temp.Year > 9999) { search = string.Empty; } else { search = temp.ToString("yyyy-MM-dd"); } } catch { search = string.Empty; } SqlCRM_Prospect.SelectCommand = strQuery; SqlCRM_Prospect.SelectParameters.Add("dateStart", DbType.String, search.Trim() + " 00:00:00.000"); SqlCRM_Prospect.SelectParameters.Add("dateEnd", DbType.String, search.Trim() + " 23:59:59.999"); break; case SSN: //Do nothing break; case ADDRESS: strQuery = @"SELECT filteredlead.leadid, filteredlead.fullname, filteredlead.address1_line1, filteredlead.emailaddress1, filteredlead.telephone1, filteredlead.lu_dateofbirthutc AS lu_dateofbirth, filteredlead.lu_gendername FROM filteredlead WHERE address1_line1 LIKE @value AND filteredlead.statecode = 0"; SqlCRM_Prospect.SelectCommand = strQuery; SqlCRM_Prospect.SelectParameters.Add("value", DbType.String, search.Trim() + "%"); ShowProspectAddressColumn(); break; default: strQuery = @"SELECT TOP 500 filteredlead.leadid, filteredlead.fullname, 'none' AS address1_line1 filteredlead.emailaddress1, filteredlead.telephone1, filteredlead.lu_dateofbirthutc AS lu_dateofbirth, filteredlead.lu_gendername FROM filteredlead WHERE filteredlead.statecode = 0"; SqlCRM_Prospect.SelectCommand = strQuery; break; } if (type != ADDRESS) { HideProspectAddressColumn(); } gvProspects.PageIndex = 0; //try //{ // SqlCRM_Prospect.DataBind(); //} //catch (Exception ex) //{ // SqlCRM_Prospect.DataBind(); //} gvProspects.DataBind(); }

    Read the article

  • Getting table schema from a query

    - by Appu
    As per MSDN, SqlDataReader.GetSchemaTable returns column metadata for the query executed. I am wondering is there a similar method that will give table metadata for the given query? I mean what tables are involved and what aliases it has got. In my application, I get the query and I need to append the where clause programically. Using GetSchemaTable(), I can get the column metadata and the table it belongs to. But even though table has aliases, it still return the real table name. Is there a way to get the aliase name for that table? Following code shows getting the column metadata. const string connectionString = "your_connection_string"; string sql = "select c.id as s,c.firstname from contact as c"; using(SqlConnection connection = new SqlConnection(connectionString)) using(SqlCommand command = new SqlCommand(sql, connection)) { connection.Open(); SqlDataReader reader = command.ExecuteReader(CommandBehavior.KeyInfo); DataTable schema = reader.GetSchemaTable(); foreach (DataRow row in schema.Rows) { foreach (DataColumn column in schema.Columns) { Console.WriteLine(column.ColumnName + " = " + row[column]); } Console.WriteLine("----------------------------------------"); } Console.Read(); } This will give me details of columns correctly. But when I see BaseTableName for column Id, it is giving contact rather than the alias name c. Is there any way to get the table schema and aliases from a query like the above? Any help would be great!

    Read the article

  • Sql Exception: Error converting data type numeric to numeric

    - by Lucifer
    Hello We have a very strange issue with a database that has been moved from staging to production. The first time the database was moved it was by detaching, copying and reattaching, the second time we tried restoring from a backup of the staging. Both SQL Servers are the same version of MS SQL 2008, running on 64 bit hardware. The code accessing the database is the same build, built using the .net 2.0 framework. Here is the error message and some of the stack trace: Exception Details: System.Data.SqlClient.SqlException: Error converting data type numeric to numeric. Stack Trace: [SqlException (0x80131904): Error converting data type numeric to numeric.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +1953274 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4849707 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2392 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +204 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +954 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +175 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +137 Version Information: Microsoft .NET Framework Version:2.0.50727.4200; ASP.NET Version:2.0.50727.4016

    Read the article

  • Postback Removing Styling from Page

    - by Roy
    Hi, Currently I've created a ASP.Net page that has a dropdown control with autopostback set to true. I've also added color backgrounds for individual listitems. Whenever an item is selected in the dropdown control the styling is completely removed from all of the list items. How can I prevent this from happening? I need the postback to pull data based on the dropdown item that is selected. Here is my code. aspx file: <asp:DropDownList ID="EmpDropDown" AutoPostBack="True" OnSelectedIndexChanged="EmpDropDown_SelectedIndexChanged" runat="server"> </asp:DropDownList> <asp:TextBox ID="MessageTextBox" TextMode="MultiLine" Width="550" Height="100px" runat="server"></asp:TextBox> aspx.cs code behind: protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { GetEmpList(); } } protected void EmpDropDown_SelectedIndexChanged(object sender, EventArgs e) { GetEmpDetails(); } private void GetEmpList() { SqlDataReader dr = ToolsLayer.GetEmpList(); int currentIndex = 0; while (dr.Read()) { EmpDropDown.Items.Add(new ListItem(dr["Title"].ToString(), dr["EmpKey"].ToString())); if (dr["Status"].ToString() == "disabled") { EmpDropDown.Items[currentIndex].Attributes.Add("style", "background-color:red;"); } currentIndex++; } dr.Close(); } private void GetEmpDetails() { SqlDataReader dr = ToolsLayer.GetEmpDetails(EmpDropDown.SelectedValue); while (dr.Read()) { MessageTextBox.Text = dr["Message"].ToString(); } dr.Close(); } Thank You

    Read the article

  • datareader.close is called in if - else branching. How to validate datareader is actually closed usi

    - by tanmay
    Hi, I have written couple of custom rules in for fxcop 1.36. I have written a code to find weather opened datareader is closed or not. But it does not check which datareader object is calling the close() method so I can't be sure if all opened datareader objs are closed!! 2nd: if I am using data reader in IF else like if 1=2 dr = cmd.executeReader(); else dr = cmd2.execureReader(); end if in this case it will search for 2 datareader objects to be closed.. I am putting my code for more clarity. public override ProblemCollection Check(Member member) { Method method = member as Method; int countCatch =0; int countErrLog = 0; Instruction objInstr = null; if (method != null) { for (int i = 0; i < method.Instructions.Count; i++) { objInstr = method.Instructions[i]; if (objInstr.Value != null) { if (objInstr.Value.ToString().Contains("System.Data.SqlClient.SqlDataReader")) { countCatch += 1; } if (countCatch>0) { if (objInstr.Value.ToString().Contains("System.Data.SqlClient.SqlDataReader.Close")) { countErrLog += 1; } } } } } if (countErrLog!=countCatch) { Resolution resolu = GetResolution(new string[] { method.ToString() }); Problems.Add(new Problem(resolu)); } return Problems; Thanks and regards, Tanmay.

    Read the article

  • using a stored procedure for login in c#

    - by Jin Yim
    Hi all, If I run a store procedure with two parameter values (admin, admin) (parameters : admin, admin) I get the following message : Session_UID User_Group_Name Sys_User_Name NULLAdministratorsNTMSAdmin No rows affected. (1 row(s) returned) @RETURN_VALUE = 0 Finished running [dbo].[p_SYS_Login]. -- To get the same message in c# I used the code following : string strConnection = Settings.Default.ConnectionString; using (SqlConnection conn = new SqlConnection(strConnection)) { using (SqlCommand cmd = new SqlCommand()) { SqlDataReader rdr = null; cmd.Connection = conn; cmd.CommandText = "p_SYS_Login"; cmd.CommandType = CommandType.StoredProcedure; SqlParameter paramReturnValue = new SqlParameter(); paramReturnValue.ParameterName = "@RETURN_VALUE"; paramReturnValue.SqlDbType = SqlDbType.Int; paramReturnValue.SourceColumn = null; paramReturnValue.Direction = ParameterDirection.ReturnValue; cmd.Parameters.Add(paramReturnValue); cmd.Parameters.Add(paramGroupName); cmd.Parameters.Add(paramUserName); cmd.Parameters.AddWithValue("@Sys_Login", "admin"); cmd.Parameters.AddWithValue("@Sys_Password", "admin"); try { conn.Open(); rdr = cmd.ExecuteReader(); string test = (string)cmd.Parameters["@RETURN_VALUE"].Value; while (rdr.Read()) { Console.WriteLine("test : " + rdr[0]); } } catch (Exception ex) { string message = ex.Message; string caption = "MAVIS Exception"; MessageBoxButtons buttons = MessageBoxButtons.OK; MessageBox.Show( message, caption, buttons, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button1); } finally { cmd.Dispose(); conn.Close(); } } } but I get nothing in SqlDataReader rdr ; is there something I am missing ? Thanks

    Read the article

  • How to validate DataReader is actually closed using FxCop custom rule?

    - by tanmay
    I have written couple of custom rules in for FxCop 1.36. I have written code to find weather an opened DataReader is closed or not. But it does not check which DataReader object is calling the Close() method so I can't be sure if all opened DataReader objects are closed!! 2nd: If I am a DataReader in an 'if/else' like if 1=2 dr = cmd.ExecuteReader(); else dr = cmd2.ExecuteReader(); end if In this case it will search for 2 DataReader objects to be closed. I am putting my code for more clarity. public override ProblemCollection Check(Member member) { Method method = member as Method; int countCatch =0; int countErrLog = 0; Instruction objInstr = null; if (method != null) { for (int i = 0; i < method.Instructions.Count; i++) { objInstr = method.Instructions[i]; if (objInstr.Value != null) { if (objInstr.Value.ToString() .Contains("System.Data.SqlClient.SqlDataReader")) { countCatch += 1; } if (countCatch>0) { if (objInstr.Value.ToString().Contains( "System.Data.SqlClient.SqlDataReader.Close")) { countErrLog += 1; } } } } } if (countErrLog!=countCatch) { Resolution resolu = GetResolution(new string[] { method.ToString() }); Problems.Add(new Problem(resolu)); } return Problems; }

    Read the article

  • Conversion failed when converting datetime from character string

    - by salvationishere
    I am developing a C# VS 2008 / SQL Server 2005 Express website application. I have tried some of the fixes for this problem but my call stack differs from others. And these fixes did not fix my problem. What steps can I take to troubleshoot this? Here is my error: System.Data.SqlClient.SqlException was caught Message="Conversion failed when converting datetime from character string." Source=".Net SqlClient Data Provider" ErrorCode=-2146232060 LineNumber=10 Number=241 Procedure="AppendDataCT" Server="\\\\.\\pipe\\772EF469-84F1-43\\tsql\\query" State=1 StackTrace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at ADONET_namespace.ADONET_methods.AppendDataCT(DataTable dt, Dictionary`2 dic) in c:\Documents and Settings\Admin\My Documents\Visual Studio 2008\WebSites\Jerry\App_Code\ADONET methods.cs:line 102 And here is the related code. When I debugged this code, "dic" only looped through the 3 column names, but did not look into row values which are stored in "dt", the Data Table. public static string AppendDataCT(DataTable dt, Dictionary<string, string> dic) { if (dic.Count != 3) throw new ArgumentOutOfRangeException("dic can only have 3 parameters"); string connString = ConfigurationManager.ConnectionStrings["AW3_string"].ConnectionString; string errorMsg; try { using (SqlConnection conn2 = new SqlConnection(connString)) { using (SqlCommand cmd = conn2.CreateCommand()) { cmd.CommandText = "dbo.AppendDataCT"; cmd.CommandType = CommandType.StoredProcedure; cmd.Connection = conn2; foreach (string s in dic.Keys) { SqlParameter p = cmd.Parameters.AddWithValue(s, dic[s]); p.SqlDbType = SqlDbType.VarChar; } conn2.Open(); cmd.ExecuteNonQuery(); conn2.Close(); errorMsg = "The Person.ContactType table was successfully updated!"; } } }

    Read the article

  • Database Programming in C#, returning output from Stored Proc

    - by jpavlov
    I am working at gaining an understanding at how to interface stored procedures with applications. My example is simple, but it doesn't display my columns and rows in the command prompt, instead it display System.Data.SqlClient.SqlDataReader. How do I display the rows from my stored procudure? ----Stored Proc-- ALTER PROCEDURE dbo.SelectID AS SELECT * FROM tb_User; ----- Below is the code: using System; using System.Data.SqlClient; using System.IO; namespace ExecuteStoredProc { class Program { static void Main(string[] args) { SqlConnection cnnUserMan; SqlCommand cmmUser; //SqlDataReader drdUser; //Instantiate and open the connection cnnUserMan = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=c:\\Program Files\\Microsoft SQL Server\\MSSQL10.SQLEXPRESS\\MSSQL\\DATA\\UserDB.mdf; Integrated Security=True;Connect Timeout=30;User Instance=True"); cnnUserMan.Open(); //Instantiate and initialize command cmmUser = new SqlCommand("SelectID", cnnUserMan); cmmUser.CommandType = System.Data.CommandType.StoredProcedure; //drdUser = cmmUser.ExecuteReader(); Console.WriteLine(cmmUser.ExecuteReader()); Console.ReadLine(); } } } Thanks.

    Read the article

  • fetching the label text from database in C#

    - by Yilmaz Paçariz
    private void button5_Click(object sender, EventArgs e) { SqlConnection conn = new SqlConnection("Data Source=MAZI-PC\\PROJECTACC;Initial Catalog=programDB;Integrated Security=True"); SqlCommand cmd = new SqlCommand("select label_sh from label_text where label_form='2' and label_form_labelID='1'", conn); conn.Open(); label1.Text = cmd.ExecuteReader().ToString(); conn.Close(); SqlConnection conn1 = new SqlConnection("Data Source=MAZI-PC\\PROJECTACC;Initial Catalog=programDB;Integrated Security=True"); SqlCommand cmd1 = new SqlCommand("select label_sh from label_text where label_form='2' and label_form_labelID='2'", conn1); conn1.Open(); label2.Text = cmd1.ExecuteReader().ToString(); conn1.Close(); SqlConnection conn2 = new SqlConnection("Data Source=MAZI-PC\\PROJECTACC;Initial Catalog=programDB;Integrated Security=True"); SqlCommand cmd2 = new SqlCommand("select label_sh from label_text where label_form='2' and label_form_labelID='3'", conn2); conn2.Open(); label3.Text = cmd2.ExecuteReader().ToString(); conn2.Close(); } I am developing a small project in C#... Using Visiual Studio 2010... I want to fetch the label texts from database in order to change the user interface language with a button... I wrote this code but there is a problem in SQLDATAREADER in label text parts it shows System.Data.SqlClient.SqlDataReader I cant fix, could you help me?

    Read the article

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

< Previous Page | 1 2 3 4 5 6 7 8 9  | Next Page >