Use Ado.net to update individual cells in Excel

TheMSsForum.com: The Microsoft Software Forum

The MSS Forum ‹ Ado
- Archive
  - Biz
  - MCSE
  - CRM
  - Drivers
  - Framework
  - ADO
  - ASP
  - Compact
  - Forms
  - Dotnet
  - C#
  - VB
  - FontpageGen
  - Excel
  - WorkSheet
  - Exchange
  - Setup
  - Fox
  - Fontpage
  - ASP
  - IIS
  - Entourage
  - Money
  - Messanger
  - PocketPC
  - Powerpoint
  - Project
  - Publisher
  - Excel
  - VB
  - Security
  - Portal
  - Services
  - SQLServerDev
  - SVCS
  - SQLServer
  - VB
  - VC
  - MFC
  - ExcelGen

- Previous
  - 1
    - How to update CSV file by OleDBDataAdapter? Hi ALL, there a lot of examples how to read data from CSV file, but I could not find any about how to save changed or newly inserted data back to the file. I tried to call method Update on OleDbDataAdapter which filled respective table, but debugger jumped out of the called function back to the caller without any exception, while no chaged data were saved into the CSV file! Why oDataAdapter.Update( oDataTable ) does not work and is it possible to save changes "automatically" (not "manually" - row by row, field by field...)? Thanks Tag: Use Ado.net to update individual cells in Excel Tag: 92819
  - 2
    - OracleClient.OracleDataReader dataGrid Is there a way to load a DataGrid using an OracleClient.OracleDataReader? I've seen code where a SqlDataReader is used, but I get an error when trying to use an OracleDataReader. Tag: Use Ado.net to update individual cells in Excel Tag: 92816
  - 3
    - Build new Access database from .NET application. Hi all, I'm currently working on a new solution that entails extracting key data from MS SQL Server (2000) and essentially export a dataset to an Access database. I may have been overthinking this but here's what I'd like to do: I have an XML representation of the MDB structure I need to build each time called tblClosing.XSD 1. Once I obtain a valid set of data as approved from the user, the source data will be in a .NET datagrid in an ASP.NET app. 2. When the user clicks a button to export the data, I want to build a new MDB file off of the XSD structure. 3. After the structure is built, I want to export the data from the datagrid into the MDB file and close it off. Again, I'm probably over thinking this so there may a much easier way, however, the XSD contains the exact structure of the MDB that I need to send off. HELP?!!? TIA -Rich Tag: Use Ado.net to update individual cells in Excel Tag: 92807
  - 4
    - Determining Field Size I am trying to find a simple way to determine the field size of a field in an Access table. I am writing in VB.NET Windows application. I would like a code snippet that I can get to work. I found some example on the Web but I couldnâ??t get them to work. I will be grateful if you can help me with this. Tag: Use Ado.net to update individual cells in Excel Tag: 92804
  - 5
    - Determining Field Size I am trying to find a simple way to determine the field size of a field in an Access table. I am writing in VB.NET Windows application. I would like a code snippet that I can get to work. I found some example on the Web but I couldnâ??t get them to work. I will be grateful if you can help me with this. Tag: Use Ado.net to update individual cells in Excel Tag: 92803
  - 6
    - Determining Field Size I am trying to find a simple way to determine the field size of a field in an Access table. I am writing in VB.NET Windows application. I would like a code snippet that I can get to work. I found some example on the Web but I couldnâ??t get them to work. I will be grateful if you can help me with this. Tag: Use Ado.net to update individual cells in Excel Tag: 92802
  - 7
    - DataView RowFilter This is a multi-part message in MIME format. ------=_NextPart_000_0204_01C47431.D406C710 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, I have the following code, which should allow me to only show a certain = number of items in a listbox: DataView dv =3D new DataView(dataSet1.Tables[ISSUE_TYPE_TABLE], = "IssueAreaDesc=3D'" + txt + "'","",DataViewRowState.ModifiedCurrent); types.DataSource =3D dv.Table; types.DisplayMember =3D "Issue Type"; types.DataMember =3D "IssueType"; types.ValueMember =3D "IssueTypeDesc"; types.DataBind(); Instead, my listbox does contain the full table's data. Any idea what = might be wrong? Thanks Mike ------=_NextPart_000_0204_01C47431.D406C710 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META http-equiv=3DContent-Type content=3D"text/html; = charset=3Diso-8859-1"> <META content=3D"MSHTML 6.00.3790.118" name=3DGENERATOR> <STYLE></STYLE> </HEAD> <BODY bgColor=3D#ffffff> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>Hi,</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>I have the following code, which should = allow me to=20 only show a certain number of items in a listbox:</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT size=3D2> <P>    DataView dv =3D </FONT><FONT color=3D#0000ff=20 size=3D2>new</FONT><FONT size=3D2> = DataView(dataSet1.Tables[ISSUE_TYPE_TABLE],=20 "IssueAreaDesc=3D'" + txt +=20 "'","",DataViewRowState.ModifiedCurrent);<BR>    = types.DataSource=20 =3D dv.Table;<BR>    types.DisplayMember =3D "Issue=20 Type";<BR>    types.DataMember =3D=20 "IssueType";<BR>    types.ValueMember =3D=20 "IssueTypeDesc";<BR>    = types.DataBind();</P></FONT></DIV> <DIV><FONT face=3DArial size=3D2>Instead, my listbox does contain the = full table's=20 data. Any idea what might be wrong?</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>Thanks</FONT></DIV> <DIV><FONT face=3DArial size=3D2>Mike</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV> </DIV></BODY></HTML> ------=_NextPart_000_0204_01C47431.D406C710-- Tag: Use Ado.net to update individual cells in Excel Tag: 92801
  - 8
    - Strange new concurrency issue My Update logic worked .. and now it doesn't. I went back, examined the stored procedure carefully, examined the table structure carefully, and examined the parameters collection carefully. All names, datatypes, and sizes match up. In this particular table, all values can be set to Null except for the primary key, SalesID. So as part of my testing to ensure the data itself wasn't the cause of the error, I tried Updating all DBNull values to every column except for the primary key ... still no success. The dataset is global, and the datatable is dimmed Private to the form class. The datatable shows a count of 1 row right before I try to update. The dataset returns True on HasChanges right before I try to submit the update. All parameters in the Update function match perfectly with the stored procedure. I've used similar logic in more than a dozen forms in the same app, but I'm stumped -- everything looks correct, but I still get a "Concurrency error, no records have been updated" when it hits the da.Update(dsChanges, "dtSales") command. Anyone see anything? Try-catch removed for readability. ********************************************** Dim strSQLServer As New SqlConnection(strConnString) strSQLServer.Open() dtSales = ds.Tables("dtSales") Dim m_intSalesID As Int32 = CInt(dtSales.Rows(0)("SalesID")) dtSales.Rows(0)("SalesID") = m_intSalesID .... '(all remaining fields set to DbNull) Dim dsChanges As DataSet dsChanges = ds.GetChanges Dim da As New SqlDataAdapter ' CreateSaleUpdate() is a function that returns a command with all parameters da.UpdateCommand = CreateSaleUpdate() da.Update(dsChanges, "dtSales") ds.AcceptChanges() strSQLServer.Close() Tag: Use Ado.net to update individual cells in Excel Tag: 92796
  - 9
    - data relations question I have three tables: clubListing, clubCollection and eventDetails. Being a newbie I am having problems in setting the relations. The clubListing table contains the fields clubID (primary key) and clubName. clubCollection contains no primary keys. It contains two columns, clubID (there can be many) and clubColl. The first table is related to the second using clubID (one-to-many). I then throw in the third table which is eventDetails. This table does have a primary key which is eventID, however this is how I was the tables related in the dataset: clubID to clubID to clubColl In the primary table there can only be one club ID. This is unique. In the second table there can be many of the same clubIDs containing many clubColl. Each clubColl can contain many records from the eventDetail table. I have a data adapter created for each table. I then use the generate dataset to add the three tables to the dataset. Opening the XML editor, I create the relations and check the box that says Create Foreign Key Restraint only. When the form opens (using a datagrid to display the data), I fill each table however when it gets to the second fill (for the clubcoll) I get an error stating "Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints." So what am I doing wrong? Ultimately I want the user to be able to see the original table with the club names, select the + to show that club's collection, select the + next to a particular collection and then finally show the details of the event. Thanks for the information. Brad Allison UKC Dog Events Tag: Use Ado.net to update individual cells in Excel Tag: 92787
  - 10
    - DataSet ReadXmlSchema fails on a schema with (xsd:)imported schema Hi, When I try to assign a schema to a dataset using the ReadXmlSchema method, that schema does not import any schemas included with xs:import. Both schema's are stored in the assembly as embedded resources (in the same namespace).: The Code: templateDocument = new XmlDataDocument(); Assembly classLibrary = Assembly.GetExecutingAssembly(); Stream schemaStream = classLibrary.GetManifestResourceStream("My.Namespace.Schema.xsd"); templateDocument.DataSet.ReadXmlSchema(schemaStream); The Schema: <?xml version="1.0" encoding="utf-8" ?> <xs:schema id="template" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:import="urn-dummy-state">  <xs:import namespace="urn-dummy-state" schemaLocation="import.xsd" /> <xs:element name="template" msdata:IsDataSet="true" msdata:Locale="nl-NL" msdata:EnforceConstraints="False"> <xs:complexType> <xs:choice maxOccurs="unbounded"> <xs:element name="random"> <xs:complexType> <xs:sequence> <xs:element name="li" nillable="true" minOccurs="0" maxOccurs="unbounded"> <xs:complexType> <xs:simpleContent msdata:ColumnName="li_Text" msdata:Ordinal="1"> <xs:extension base="xs:string"> <xs:attribute ref="import:condition" /> </xs:extension> </xs:simpleContent> </xs:complexType> </xs:element> </xs:sequence> </xs:complexType> </xs:element> </xs:choice> </xs:complexType> </xs:element> </xs:schema> What do I do wrong (if any)? Thx in adv. Eric Tag: Use Ado.net to update individual cells in Excel Tag: 92786
  - 11
    - Data Access Block and Identity Column Return... I am using the SqlHelper class from the Microsoft Data Application Block and had a question about returning an Identity Column. Basically, I have a form with a grid bound to a DataSet. The user can, for example, Add a new record. When they do this, and press Save, I call the ExecuteDataSet method. I am using Stored Procedures, and in the procedure, at the end of the insert, I do the following: select @id = scope_identity() @id is set as an OUTPUT parameter on the procedure. When I check the value of the @id column on the dataset that gets returned, the value is null. I am just wondering if I am doing this correctly. I will continue to debug the code, but thought I would see if anybody else has done this successfully. THanks, Mark Tag: Use Ado.net to update individual cells in Excel Tag: 92784
  - 12
    - Sql Command Paramaters I'm building a dynamic command like this. "SELECT * FROM Clients WHERE FirstName LIKE @FirstName" and I set the paramater like this. sqlCMD.Paramaters.Add("@FirstName", "%" & fnameVariable & "%") The reason I'm doing this is I need it to search the whole field to see if it contains any of the text in "fnameVariable". My question is am I going to run into any problems if the user enters in any special characters for the "fnameVariable" (i.e. %, *, etc)? I was under the impression that using the SqlCommand with paramaters was supposed to take care of that sort of stuff. Tag: Use Ado.net to update individual cells in Excel Tag: 92770
  - 13
    - Runtime Client to UDB V 4.5 Hello, I'm building an ASPX page for my client. The .NET resides in a Windows 2003 Web Server with Runtime Client V 8.1. The AS/400 has a really old DB2 version (I think its V 4.5). Will there be any connection problems between the Runtime Client and the DB2? Thanks Tag: Use Ado.net to update individual cells in Excel Tag: 92766
  - 14
    - BLOB,and storing duplicate documents to create document version system I am trying to create a document version management system using ADO.net and SQL server. I am storing the document in an Image field in the database. I could sucessfully implement check in , check out, undo check out functions but i am having one problem. When i check out a document , make some changes to it and do check in all previous versions of the document are also changes in such a way that they also have the same document. to chekin a document i first create a new database row..copy thw basic infor from previous row..and then load the new document in the row.. please see the code below //Get values from data row associated with the grid and make changes in the master dataset table private bool CheckInRow(DataRow dr, String sFile) { double fVersionNo ; DataTable dt =null; DataRow drNew = null; fVersionNo = Convert.ToDouble(dr[m_strVersionCol].ToString()); dt = m_dsMaster.Tables[m_strTable]; drNew = dt.NewRow(); drNew[CUtil.GetColumn(m_strIDCol)] = new TnrData.CUtil().GetTempID(); drNew[CUtil.GetColumn(m_strIDParentCol)] = dr[m_strIDParentCol]; drNew[CUtil.GetColumn(m_strShortCutCol)] = dr[m_strShortCutCol]; drNew[CUtil.GetColumn(m_strDocNameCol)] = dr[m_strDocNameCol]; drNew[CUtil.GetColumn(m_strDateRecievedCol)] = DateTime.Now; drNew[CUtil.GetColumn(m_strAddedByCol)] = dr[m_strAddedByCol]; drNew[CUtil.GetColumn(m_strCommentCol)] = dr[m_strCommentCol]; drNew[CUtil.GetColumn(m_strIDDocTypeCol)] = dr[m_strIDDocTypeCol]; drNew[CUtil.GetColumn(m_strFileNameCol)] = dr[m_strFileNameCol]; drNew[CUtil.GetColumn(m_strSizeCol)] = CLGN.GetFileSize(sFile) / 1000; drNew[CUtil.GetColumn(m_strLiveLinkCol)] = dr[m_strLiveLinkCol]; drNew[CUtil.GetColumn(m_strDocTypeCol)] = dr[m_strDocTypeCol]; drNew[CUtil.GetColumn(m_strCurrentCol)] = true; drNew[CUtil.GetColumn(m_strCheckedOutCol)] = false; drNew[CUtil.GetColumn(m_strVersionCol)] = fVersionNo + 1.0; //CLGN.SetFileToRow(drNew, sFile, CUtil.GetColumn(m_strDocImageCol)); try { //Create a temporary file for check in as SQL server needs to have unique blob file StringBuilder sb = new StringBuilder(sFile); sb.Append("Tnr");sb.Append(drNew[CUtil.GetColumn(m_strVersionCol)].ToString()); sb.Append(".");sb.Append(drNew[CUtil.GetColumn(m_strDocTypeCol)].ToString()); String strCheckInFileName = sb.ToString(); //Make sure that file does not exist and then copy to create a temporary file File.Delete(strCheckInFileName); File.Copy(sFile,strCheckInFileName); FileStream fs = new FileStream(strCheckInFileName,FileMode.Open,FileAccess.Read); Byte[] bt = new Byte[(int)(fs.Length)]; fs.Read(bt, 0, (int)(fs.Length)); fs.Close(); drNew[CUtil.GetColumn(m_strDocImageCol)] = bt; File.Delete(strCheckInFileName); } catch(System.IO.IOException ioe) { MessageBox.Show("Can Not Read File! \nThe Document File '" + dr[m_strFileNameCol].ToString() +"' Does Not Exist In Checked Out Folder Or Is Locked By Some Other Application.\n Please Close All Aplications Using The File Before Checking In" , "", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); return false; } dt.Rows.Add(drNew); return true; Could somebody please help and explain this behavior Thanks, Vikas Tag: Use Ado.net to update individual cells in Excel Tag: 92762
  - 15
    - generic ADO.NET classes? Is there any discussion on the availability of generic classes for ADO.NET classes to create strongly typed versions? We were able to create typed datasets easily but it was not easy to create a typed datatable "that could give a typed DataView" since DataView is what is mostly used for databinding. Tag: Use Ado.net to update individual cells in Excel Tag: 92754
  - 16
    - Databinding problem Hello, I have problem that was described under the link below http://support.microsoft.com/default.aspx?scid=kb;EN-US;321504 I did follow the steps described in the artice with no success. Does anyone know a solution for the problem listed or should I just give up and follow a different method? Thank you for your help. Tag: Use Ado.net to update individual cells in Excel Tag: 92750
  - 17
    - How to use SQLBinary? I have a varbinary field in my table. (It has the potential of being rather large.) I need to read this value, change it and write it back. I have gotten as far as using the GetSQLBinary method of the SqlDataReader. What I don't know is what do I actually have at that point. I would assume that somewhere this information is going to be presented as an array of something or perhaps as a BitArray? How do I actually manipulate the contents of the varbinary column in my application code? Thanks ------------------------------------------- Roy Chastain KMSystems, Inc. Tag: Use Ado.net to update individual cells in Excel Tag: 92742
  - 18
    - Getting data in class hierarchy (C#) I have a class hierarchy that is, for simplicity sake, two levels deep. User -> MoreSpecificUser In most cases it will only be necessary for me to retrieve information for one or the other. I figure if I dont need all the data that the parrent has to offer why get it. So here was my solution, I have an interface that all classes must implement, this exposes Save, Load and Delete. If I want just the MoreSpecficUser data then I just call its Load() method. If I want the parent info I call base.Load(). Fine the coupling is low and maintenance is easy. Here's my only quibble. If I wanted to do this: public class User: IMyInterface { public void Load() { //open db connection //fill dataset //unpack dataset and fill load object members //close connection } } public class MoreSpecficUser : User { //other interface implementations here public override void Load() { base.Load(); //open db connection //fill dataset //unpack dataset and fill load object members //close connection } } As you acn see I've simply called the base Load method to get all the info available to this user. However it involves TWO database connections. Now this make life easy in terms of code maintenance and flexibility in the app but not sure how comfortable I am about makng two connections to fill and object. Tag: Use Ado.net to update individual cells in Excel Tag: 92737
  - 19
    - Update in DataGrid Hi everyone, how could i update in datagrid & then update in the database??? Thx & Regards begin 666 smiley_doh.gif M1TE&.#EA$ `0`.8``/GRZ_;NY/GMWN[N[OGKV?GHT_CFS_CDR>3DY-[FWOGA MPOG?OOG;M?G7K?G4I=C8V.+2OO_,F?/0H_C+D?+,F_C*C]_1OOC)C?C&A<S, MS/''D_#"A?2_?/F^<_:Y:[#,T/&]>=>^GO>U:_FW8OFT6Z["O[V\N_*R7^"W M@OBN3ZV]O?BM2_BJ1;2TM-NJ;/FH0/FD-ZNVK/FB,[^MEYFRM<RE=?F>*?F; M(JBLF_F9'+:CC*^HGJ>GI:6AG-.93OF7&?B4$=J7/\Z52O2-!LZ10>Z+"KZ6 M8:"9@9F9F9F9F:J5>XV=G["6<]"--J:2>)R4A)Z7?HJ7F8^5EJJ*8-:#&->! M$IJ(<(:(B)N':Z"&9):#;)Q\49YZ2X]^:(Y]7X%[=(=[:WQ\?(IT5Y5R1(ER M5:%N+85O4W9W=HAM2(1K4HIH/'UH2XEB+WME27]E1&9F9F9F9G%B37-;.VY8 M.V%2/U=54UQ00%)+0DM(1 ```````````````````````````"'Y! 44`!\` M+ `````0`! ```?C@!^"@C%07EH\/6%2-(.")61X=&MO&1D#)E=+@SAW9A8" M!3LF"!D]`RU1'R5W2@$"!@HA/0]G`V\(4A]B9@"P"@P2.DI6/7$S4B5X$ 2Q M# X1&!PN1CYU9T=T! <+7]Y.3&!=4RQV1UEKW T4&ALU6"<I+#!R7EYM# T. M$Q@='O$O8-B04R9;@P@5^GD@D>*%C!L_S,7 @^)"OQ$D5CB\`80*GA(?T+@! MT0'C"B%$F@`I,H<-H3MC1LA[L64+ER%R[H 4U$E.$($Y@%3)B</1*C62Y,RQ 0@X?-3J.KCI3Q<B2&T4 `.P`` ` end Tag: Use Ado.net to update individual cells in Excel Tag: 92734
  - 20
    - how to use default value in sqlCommand In Sql Server, I can use syntax to insert a record with default value: insert table1(a,b,c)values('A','B',default) But in .Net, if I want to use default value in Adapter.InsertCommand,for example, SqlAdapObj.InsertCommand.Parameters.Add(New SqlClient.SqlParameter("@testField")) SqlAdapObj.InsertCommand.Parameters("@testField").Value=default There is no "default" in C# language, so it says error occurred,what const variable should I use to do so Tag: Use Ado.net to update individual cells in Excel Tag: 92733
  - 21
    - Please install MDAC error When I try to connect to an Access db, I get the following error: The .Net data OLE DB Provider(System.Data.OleDb) requires MDAC version 2.6 or later ... The code is: OleDbConnection JetCon; string sMyConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + fnDB + ";Jet OLEDB:System Database="; JetCon = new OleDbConnection(sMyConnection); JetCon.Open(); I have msjet40.dll installed and the registry reports that I have MDAC v2.71.9030.4 installed. What have I missed? --- Posted using Wimdows.net NntpNews Component - Post Made from http://www.DotNetJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching. Tag: Use Ado.net to update individual cells in Excel Tag: 92732
  - 22
    - Trouble accessing db while using ASP.Net Group, I read Jeff Prosise's article: "An Introductory Guide to Building and Deploying More Secure Sites with ASP.NET and IIS, Part 2" as well as part 1. I think that articles are great. I had no problems running the example code in the first article, but I am having problems with the example as outlined in Figure 4. Specifically, I seem to be having troubles opening a connection to the database, and that's why I am posting to this group. When I try to open the connection ("connection.Open ();"), I am receiving this exception: "Exception Message: Login failed for user 'FormUser'. Reason: Not associated with a trusted SQL Server connection. Stack Trace: at System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) at System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) at System.Data.SqlClient.SqlConnection.Open() at ASP.LoginPage_aspx.CustomAuthenticate(String username, String password) Invalid login User name: jeff" [end of exception] The user "FormUser" was created by using this script: EXEC sp_addlogin 'FormUser', 'formuser', 'WebLogin' EXEC sp_grantlogin 'BILLDESKTOP\FormUser' EXEC sp_grantdbaccess 'BILLDESKTOP\FormUser', 'formuser' I have attached a copy of my LoginPage.aspx as well as the associated web.config file. Can you tell me what is going wrong here? I have tried to do research on this error, and as far as I can see, I have taken care of all of the isses. Also, if I turn impersonation on, I get the same error, but complaining about a differant user. I am using MSDE and Windows XP Pro. Also, I did send a similar EMail to the author, but I am sure that he gets a lot of bulk email from confused people like me, so I hope that one of you could help me. Thanks, Bill _________________________________________ login.aspx: <%@ Import NameSpace="System.Data.SqlClient" %> <html> <body> <h1>Please Log In</h1> <hr> <form runat="server"> <table cellpadding="8"> <tr> <td> User Name: </td> <td> <asp:TextBox ID="UserName" RunAt="server" /> </td> </tr> <tr> <td> Password: </td> <td> <asp:TextBox ID="Password" TextMode="password" RunAt="server" /> </td> </tr> <tr> <td> <asp:Button Text="Log In" OnClick="OnLogIn" RunAt="server" /> </td> <td> <asp:CheckBox Text="Keep me signed in" ID="Persistent" RunAt="server" /> </td> </tr> </table> </form> <hr> <h3><asp:Label ID="Output" RunAt="server" /></h3> </body> </html> <script language="C#" runat="server"> void OnLogIn (Object sender, EventArgs e) { if (CustomAuthenticate (UserName.Text, Password.Text)) FormsAuthentication.RedirectFromLoginPage (UserName.Text, Persistent.Checked); else Output.Text += "Invalid login" + "<br>" + "User name: " + UserName.Text; } bool CustomAuthenticate (string username, string password) { SqlConnection connection = new SqlConnection ("server=BILLDESKTOP\\JMSDEV1;database=weblogin;Integrated Security=SSPI;uid=FormUser;pwd=formuser"); try { connection.Open (); //this is where the excpetion is thrown StringBuilder builder = new StringBuilder (); builder.Append ("select count (*) from users " + "where username = \'"); builder.Append (username); builder.Append ("\' and cast (rtrim (password) as " + "varbinary) = cast (\'"); builder.Append (password); builder.Append ("\' as varbinary)"); SqlCommand command = new SqlCommand (builder.ToString (), connection); int count = (int) command.ExecuteScalar (); return (count > 0); } catch (SqlException se) {//this is where the exception is caught Output.Text += "Exception Message: " + se.Message + "<BR>"; Output.Text += "Stack Trace: " + se.StackTrace + "<BR>"; return false; } finally { connection.Close (); } } </script> ____________________________ web.config: <configuration> <system.web> <authentication mode="Forms"> <forms loginUrl="LoginPage.aspx" /> </authentication> </system.web> </configuration> Tag: Use Ado.net to update individual cells in Excel Tag: 92731
  - 23
    - How do I change the size of a boundcolumn when in edit mode? Hi, I want to change the size of my datagrid fields when I'm editing the record, how can I do this? Is there a property that I can use? Moe <asp:BoundColumn HeaderText="First Name" DataField="firstname" /> Tag: Use Ado.net to update individual cells in Excel Tag: 92728
  - 24
    - Use of radio buttons Hey fellows, I feel stupid asking this question because I'm sure the answer is so simple but it eludes me. In VB6 a standard practice was to but multiple radio buttons into a groupbox (called a frame) and assign each of the bottons a value. The frame then had the value of the button that was clicked and it's value could be bound to a data source. How the heck do you do that in VB7? There is a GroupBox control and a Panel control that will allow only one radio button to be the one selected but how does one bind that value into a database? I have an application involving persons and I want to have a GroupBox that has two option buttons on it, Male and Female and have the result be stored in as database as Male=0, Female = 1 in a bit column. Can anyone point me in the right direction as to how to do this simple thing? Many thanks. Spence Tag: Use Ado.net to update individual cells in Excel Tag: 92721
  - 25
    - memory leak when quering from SQL server Hi I have a query that gets binary info from the SQL Server. The problem is that the query is very large and the process that performs it consumes a lot of memory. Actually it consumes the amount of memory as defined in the SQL Server properties (memory tab). The problem is even bigger, because the process does not release the consumed memory and if I decrease it then i get a lot of page faults. Any help would be appreciated Uzi Tag: Use Ado.net to update individual cells in Excel Tag: 92719
- Next
  - 1
    - XSD From SQL Table Hi, What would be the shortest way to generate an XSD for a SQL table? Thanks, Alan Tag: Use Ado.net to update individual cells in Excel Tag: 92714
  - 2
    - SqlCommandBuilder unable to Update a dataset I am using SqlCommandBuilder to update,insert,delete my data. I can do for insert and delete. My code is string sql=" select * from "+dbname; SqlDataAdapter sdata=new SqlDataAdapter(); sdata.SelectCommand=new SqlCommand(sql,scon); SqlCommandBuilder scom=new SqlCommandBuilder(sdata); sdata.UpdateCommand=scom.GetUpdateCommand(); sdata.RowUpdating+=new SqlRowUpdatingEventHandler(sdata_RowUpdating); sdata.RowUpdated+=new SqlRowUpdatedEventHandler(sdata_RowUpdated); sdata.Update(ds,dbname); but when I reach the SqlRowUpdating Event my event argument command is undefined. But my dataadapter take the update command correctly. How can I solve this problem Thanks for all. Tag: Use Ado.net to update individual cells in Excel Tag: 92710
  - 3
    - HOWTO: NEWBIE: Query Data from Access Using ADO.NET? Hi There! How to query Access DB using ADO.NET and get Recordsets? e.g. I want the following sql... SELECT * FROM Authors query Access DB using ADO.NET and returned records will be displayed in a drop-down box in ASP.NET. Please help! Thanks in advance! Don Tag: Use Ado.net to update individual cells in Excel Tag: 92706
  - 4
    - Oracle connection string with IP address With SQL Server, I can create a connection string to a remote server using an IP address such as: "Data Source=190.190.200.100,1433;..." where 1433 is the port (1433 is the default) This works just fine. Is it possible to use the "Microsoft Managed Provider for Oracle" and connect via an IP address using something like the string below to connect to a remote Oracle 8i or 9i database server: "Data Source=190.190.200.100,1521;..." -- it is my understanding that 1521 or 1526 are the ports that are similar to 1433 If that is not the correct syntax, could you please identify how to do this. I have searched the web and discussion groups and can find nothing on connecting to a remote Oracle database server. Any help or advice would be much appreciated. Thanks. Ron _____________________________________ Ron Fluegge ronflueggeATswbellDOTnet Power corrupts, and absolute power is actually pretty neat... Tag: Use Ado.net to update individual cells in Excel Tag: 92705
  - 5
    - why can I set grid's DataSource to a DataSet but not to a table in the DataSet? I have a DataGrid on a WebForm. When I select that DataGrid and go to the "DataSource" property in the properties box, the drop-down lets me select dataSet11, which is the name of an object instance of class DataSet1, which VS.Net created for me derived from DataSet. However, DataSet1 has a schema with two tables in it -- if I right-click dataSet11 and pick "View Schema" it shows two tables, wbuser and BennettEmployee. Now, as soon as I set the DataSource property of my DataGrid to dataSet11, the layout of the DataGrid on the WebForm changes to display the columns "myusername", "mynickname", and "ID" -- these are the columns in the wbuser table from the DataSet1 schema. So, two questions: - Does this mean that "wbuser" is somehow the "default" table in the DataSet1 schema, so that if the DataSource is set to dataSet11, it automatically maps to the "wbuser" table of that DataSet, as opposed to the "BennettEmployee" table? Is there a way to change the default table to "BennettEmployee"? - More importantly, why does the DataSource dropdown in the IDE only let me set the DataSource to a *DataSet*, instead of a table within that DataSet? Since the data in the DataGrid is being pulled from a table, why won't it let me specify a table? Am I looking at this in some fundamentally wrong way? -Bennett Tag: Use Ado.net to update individual cells in Excel Tag: 92704
  - 6
    - Oracle connection string with IP address I can connect to a remote SQL Server database via an IP address by using a connection string such as: "Data Source=190.190.200.100,1433;..." where 1433 is the port to use (1433 is the default). If I use the Microsoft Managed Provider for Oracle, is it possible to connect to a remote Oracle database via an IP address by using a similar connection string such as: "Data Source=190.190.200.100,1521;..." where 1521 is the port? If I understand correctly, 1521 and 1526 are the Oracle listener counterparts to the 1433. If not, what is the proper connection string syntax to connect to a remote Oracle database? Thanks ... Tag: Use Ado.net to update individual cells in Excel Tag: 92703
  - 7
    - Oracle connection via an IP Address With SQL Server, you can enter an ADO.NET connection string using an IP address such as "Data Source=190.190.200.100,1433;...." to connect to a remote server. Can you do something similar with the Microsoft managed provider for Oracle such as "Data Source=190.190.200.100,1521;.." attaching to a remote Oracle database using either ports 1521 or 1526 similar to the 1433 for SQL Server? Tag: Use Ado.net to update individual cells in Excel Tag: 92702
  - 8
    - Switch to GUIDs - changing the way I code? When using Integer as PK, I would often have functions that would return the Integer when searching for a specific row in a datatable. I am trying to switch to GUID. Before, if the function returned a zero, I would assume the PK didn't exist. Now, if I make a call to lookup the GUID, and that row doesn't exist yet, I can't seem to figure out the simplest way - without doing a redundant function to check if exists first - causing two loops. Simple Example of my Integer based Code: Table1 - TableID(Int), Description(Nvarchar) Private Function GetTableIDByDescription(sDescription as String) as Integer Private iID as integer = 0 For each row If Description = sDescription Then iID = Row.TableID Return iID Private i as integer = GetTableIDByDescription(id) If i = 0 then ' New Record - insert Else ' Use the ID With GUID?: Table1 - TableID(GUID), Description(Nvarchar) Private Function GetTableIDByDescription(sDescription as String) as GUID Private iID as New Guid ' ?? For each row If Description = sDescription Then iID = Row.TableID Return iID Now, without two functions (one for Exists, one for GetID), is there a way that I can rewrite all these code sections? Masa Tag: Use Ado.net to update individual cells in Excel Tag: 92693
  - 9
    - Binding a datareader to a grid Is there a Winforms grid to which I can readily bind a datareader (purely for read-only visualisation of the datareader data and using the 1.1 framework)? Or is this in some way not regarded as a legitimate use for the datareader? JGD Tag: Use Ado.net to update individual cells in Excel Tag: 92683
  - 10
    - xml and csv with ado.net Hello everybody out there, I got a problem with a db-format, the metadata is written in XML (such things like tables names, paths, fieldnames and delimiter etc.), the data is written in .csv-files. How can I put this two thinmgs together in one dataset? Thanx Lars Tag: Use Ado.net to update individual cells in Excel Tag: 92677
  - 11
    - FillDataset only fills the first two tables. All, I have a typed dataset DS contains 4 tables (say TA, TB, TC,TD). I have one stored procedure contains 4 select (no relationship) SELECT * FROM TA SELECT * FROM TB SELECT * FROM TC SELECT * FROM TD I use MS Application block FillDataset as follow DS ds= new DS(); sp = "sp_SelectFour"; string[] tables = {"TA", "TB", "TC", "TD" }; SqlHelper.FillDataset(connString, CommandType.StoredProcedure, sp, ds, tables); but i only see TA and TB are filled with data. I checked sp using SQL Query Analyzer, it does return 4 result sets. To make sure I can get TC and TD filled: I change sp to only select TC and TD. and change tables = {"TC", "TD"}; I am able to fill TC and TB. So, Is there a limit that only two result sets can be fetched at a time? TIA. John Tag: Use Ado.net to update individual cells in Excel Tag: 92668
  - 12
    - Can't find primary key I have a dataset that I first do a FillSchema(dataset, SchemaType.Mapped, "Table") on, and yet it still throws System.Data.MissingPrimaryKeyException when I do a dataset.tables("Table").Rows.Find(key). The column is marked as a primary key in the table and the sql statement is just "SELECT * FROM tableName" Anybody have any idea why this can happen? P.S. Sorry if I didn't give enough info, but I am patching together someone else's mess, so I haven't gotten a chance to read it all yet. -Thanks Tag: Use Ado.net to update individual cells in Excel Tag: 92662
  - 13
    - How to merge two DataView objects Hi, I have two DataView objects sharing the same DataTable. I want to merge these two. Ideally the DataView class would have a Merge(DataView dv) method, but it doesn't. Does anyone know how to do this? Here is what Im trying to accomplish. Say I am using the Northwind database and I want to display 2 DataGrids, one representing the Customer table and one for OrderDetails table. Everytime I select a customer in the Customers table I would like to show all the order details in the Orders table for all the orders for that customer. So say the DataRowView representing the customer is customerDataRowView, then I will get all the children in the Orders table for that customer by ordersDataView = customerDataRowView.CreateChildView(CustomerToOrdersDataRelation); Then I want to say: orderDetailsDataView = new DataView(); foreach(DataRowView drv in ordersDataView.Rows) { DataView dv = drv.CreateChildView(OrdersToOrderDetailsDataRelation); orderDetailsDataView.Merge(dv); } Thanks in advance for all your help. Nima Tag: Use Ado.net to update individual cells in Excel Tag: 92661
  - 14
    - Inserting string into SQL Server image column I have a stored procedure in SQL Server that inserts a row into a table. One of the columns of the table has the "image" data type, which maps to a byte array in ADO SqlClient. The problem I have is that the data that I'm adding as a parameter when calling the stored procedure comes from a TextBox on the web page, which is of type string. As a result, I keep getting errors because it can't convert a string to a byte array. I cannot seem to find any kind of method that would do that for me. Does anyone know what would be the correct procedure to pass data for inserting into a column of type image, when the data is coming as a string from a text box? Or, if nothing else, how could I convert a string to a byte array? Thanks Tag: Use Ado.net to update individual cells in Excel Tag: 92660
  - 15
    - Problem with DatatableMapping I have a number of tables in my dataset and each is mapped as follows With mSQLDA .TableMappings.Add("tblClassificationResource", "tblClassificationResource") .TableMappings.Add("tblResourceProperty", "tblResourceProperty") End With but when i set the insert/update and delete commands for the dataadapter and run an update nothing happens, unless i clear the tablemapings and add a new one as follows mSQLDA.TableMappings.Clear() mSQLDA.TableMappings.Add("Table", "tblResourceProperty") then the update works Can someone please explain what i'm missing here? Thanks John Tag: Use Ado.net to update individual cells in Excel Tag: 92653
  - 16
    - No error information available: DB_SEC_E_AUTH_FAILED(0x80040E4D)??? I have recieved this error message when trying to open a connection to an access database. What I don't understand is why earlier in the submit_click method the connection opened properly, yet in the loadTable method it throws the error "No error information available: DB_SEC_E_AUTH_FAILED(0x80040E4D)". I have included my code to help visualize what I am tryin to to. 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.OleDb; namespace dynamicAccess { /// <summary> /// Summary description for WebForm1. /// </summary> public class WebForm1 : System.Web.UI.Page { //declares the object that are in the web application protected System.Data.OleDb.OleDbCommand oleDbCommand1; protected System.Web.UI.WebControls.Button Submit; protected System.Data.OleDb.OleDbConnection myConnection; protected System.Web.UI.WebControls.Button LoadTable; protected System.Web.UI.WebControls.DropDownList DropDownList; protected System.Web.UI.HtmlControls.HtmlInputFile File1; protected System.Web.UI.WebControls.DataGrid DataGrid1; private void Page_Load(object sender, System.EventArgs e) { // Put user code to initialize the page here } #region Web Form Designer generated code override protected void OnInit(EventArgs e) { // // CODEGEN: This call is required by the ASP.NET Web Form Designer. // InitializeComponent(); base.OnInit(e); } /// <summary> /// Required method for Designer support - do not modify /// the contents of this method with the code editor. /// </summary> private void InitializeComponent() { this.myConnection = new System.Data.OleDb.OleDbConnection(); this.oleDbCommand1 = new System.Data.OleDb.OleDbCommand(); this.Submit.Click += new System.EventHandler(this.Submit_Click); this.LoadTable.Click += new System.EventHandler(this.LoadTable_Click); // // myConnection // this.myConnection.ConnectionString = @"Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database Locking Mode=1;Mode=Share Deny None;Jet OLEDB:Engine Type=5;Provider=""Microsoft.Jet.OLEDB.4.0"";Jet OLEDB:System database=;Jet OLEDB:SFP=False;persist security info=False;Extended Properties=;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Create System Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;User ID=Admin;Jet OLEDB:Global Bulk Transactions=1"; this.Load += new System.EventHandler(this.Page_Load); // // oleDbCommand1 // this.oleDbCommand1.CommandText = "SELECT * FROM"; this.oleDbCommand1.Connection = this.myConnection; } #endregion /* after the user finds the database they wish to open they must hit the submit button to * populate the dropdownlist. The Submit_Click populates the drop down list and updates * the command text.*/ private void Submit_Click(object sender, System.EventArgs e) { myConnection.ConnectionString += ";Data Source=" + File1.PostedFile.FileName; myConnection.Open(); DataTable schemaTable = myConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] {null,null,null,"TABLE"}); myConnection.Close(); DropDownList.DataSource=schemaTable; DropDownList.DataTextField = "TABLE_NAME"; DropDownList.DataValueField = "TABLE_NAME"; DropDownList.SelectedIndex = DropDownList.SelectedIndex; DropDownList.DataBind(); DropDownList.Items.Insert(0, new ListItem("Select a Table")); DropDownList.SelectedIndex = 0; oleDbCommand1.CommandText += " " + DropDownList.SelectedItem.Text; } /* This event fills the data grid so that the user can see the contents of the table selected. * At this time I am recieving an error when trying to open the connection. * (No error information available: DB_SEC_E_AUTH_FAILED(0x80040E4D))*/ private void LoadTable_Click(object sender, System.EventArgs e) { myConnection.Open(); System.Data.OleDb.OleDbDataReader Reader; Reader = oleDbCommand1.ExecuteReader(); DataGrid1.DataSource = Reader; DataGrid1.DataBind(); Reader.Close(); myConnection.Close(); } } } Tag: Use Ado.net to update individual cells in Excel Tag: 92646
  - 17
    - Help with SqlDataAdapter Update command (C#) This is a multi-part message in MIME format. ------=_NextPart_000_000A_01C470C1.0AB15790 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I am pulling my hair out over this one. I have done this same kind of = thing in VB.Net but in C# it doesn't work. I dont' receive any errors = either, it just won't update the pubs.Authors table. Here is the code: private void CodedDataGrid_Load(object sender, System.EventArgs e) { //create a new connection object String ConnectString =3D "Server=3D" + "MyMachine" + "; " + "User ID=3D" = + "sa" + "; Password=3D" + "password" + "; initial catalog =3D " + = "pubs" + ";"; conn =3D new SqlConnection(ConnectString); //create a new adapter adp =3D new SqlDataAdapter("Select au_id,au_lname,au_fname,contract FROM = authors",conn); //creat ea new dataset ds =3D new DataSet(); adp.Fill(ds); this.dbGrid.DataSource =3D ds; } private void btnNew_Click(object sender, System.EventArgs e) { AdapterInsert(); =20 //add a new record to the dataset, but first try { adp.Update(ds,"Table"); } catch (System.Data.SqlClient.SqlException sqlEx) { MessageBox.Show(sqlEx.Message); } catch(System.Exception ex) { MessageBox.Show(ex.Message); } } private void AdapterInsert() { insComm =3D new SqlCommand(); adp.InsertCommand =3D insComm; adp.InsertCommand.Connection =3D conn; adp.InsertCommand.CommandType =3D=20 CommandType.StoredProcedure; adp.InsertCommand.CommandText =3D "InsertAuthor"; SqlParameter MySqlParam1 =3D new = SqlParameter("@au_id",SqlDbType.VarChar); MySqlParam1.Value =3D this.txtID.Text; MySqlParam1.SourceColumn =3D "au_id"; adp.InsertCommand.Parameters.Add(MySqlParam1); SqlParameter MySqlParam2 =3D new = SqlParameter("@au_fname",SqlDbType.VarChar); MySqlParam2.Value =3D this.txtFName.Text; MySqlParam2.SourceColumn =3D "au_fname"; adp.InsertCommand.Parameters.Add(MySqlParam2); SqlParameter MySqlParam3 =3D new = SqlParameter("@au_lname",SqlDbType.VarChar); MySqlParam3.Value =3D this.txtLName.Text; MySqlParam3.SourceColumn =3D "au_lname"; adp.InsertCommand.Parameters.Add(MySqlParam3); SqlParameter MySqlParam4 =3D new = SqlParameter("@contract",SqlDbType.Bit); MySqlParam4.Value =3D "1"; MySqlParam4.SourceColumn =3D "contract"; adp.InsertCommand.Parameters.Add(MySqlParam4); } What the heck am I missing here? Thanks. =20 ------=_NextPart_000_000A_01C470C1.0AB15790 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META http-equiv=3DContent-Type content=3D"text/html; = charset=3Diso-8859-1"> <META content=3D"MSHTML 6.00.2900.2162" name=3DGENERATOR> <STYLE></STYLE> </HEAD> <BODY> <DIV><FONT face=3DArial size=3D2>I am pulling my hair out over this one. = I have done=20 this same kind of thing in VB.Net but in C# it doesn't work. I dont' = receive any=20 errors either, it just won't update the pubs.Authors table. Here is the=20 code:</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>private void CodedDataGrid_Load(object = sender,=20 System.EventArgs e)<BR>  {<BR>  //create a new = connection=20 object<BR></FONT><FONT face=3DArial size=3D2>String ConnectString =3D = "Server=3D" +=20 "MyMachine" + "; " + </FONT><FONT face=3DArial size=3D2>"User ID=3D" + = "sa" + ";=20 Password=3D" + "password" + </FONT><FONT face=3DArial size=3D2>"; = initial catalog =3D "=20 + "pubs" + ";";<BR></FONT><FONT face=3DArial size=3D2>conn =3D new=20 SqlConnection(ConnectString);</FONT><FONT face=3DArial=20 size=3D2>   //create a new adapter<BR>adp =3D new=20 SqlDataAdapter("Select </FONT><FONT face=3DArial=20 size=3D2>au_id,au_lname,au_fname,contract FROM = authors",conn);<BR></FONT></DIV> <DIV><FONT face=3DArial size=3D2>   //creat ea new=20 dataset<BR>   ds =3D new=20 DataSet();<BR>   adp.Fill(ds);<BR>   this.d= bGrid.DataSource=20 =3D ds;</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>   }</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>  private void = btnNew_Click(object=20 sender, System.EventArgs=20 e)<BR>  {<BR>   AdapterInsert();</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial = size=3D2>   <BR>   //add a new=20 record to the dataset, but first</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial=20 size=3D2>   try<BR>   {<BR>  &nbs= p; adp.Update(ds,"Table");<BR>   }<BR>  &nb= sp;catch=20 (System.Data.SqlClient.SqlException=20 sqlEx)<BR>   {<BR>    MessageBox.Show(= sqlEx.Message);<BR>   }<BR>   catch(System.= Exception=20 ex)<BR>   {<BR>    MessageBox.Show(ex.= Message);<BR>   }</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>  }<BR>  private = void=20 AdapterInsert()<BR>  {<BR>   insComm =3D new=20 SqlCommand();<BR>   adp.InsertCommand =3D=20 insComm;<BR>   adp.InsertCommand.Connection =3D = conn;</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial = size=3D2>   adp.InsertCommand.CommandType =3D=20 </FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial=20 size=3D2>CommandType.StoredProcedure;<BR>   adp.InsertComm= and.CommandText=20 =3D "InsertAuthor";</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>   SqlParameter = MySqlParam1 =3D new=20 </FONT><FONT face=3DArial=20 size=3D2>SqlParameter("@au_id",SqlDbType.VarChar);<BR>   M= ySqlParam1.Value=20 =3D this.txtID.Text;<BR>   MySqlParam1.SourceColumn =3D=20 "au_id";<BR>   adp.InsertCommand.Parameters.Add(MySqlParam= 1);</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>   SqlParameter = MySqlParam2 =3D new=20 </FONT><FONT face=3DArial=20 size=3D2>SqlParameter("@au_fname",SqlDbType.VarChar);<BR>  &nbs= p;MySqlParam2.Value=20 =3D this.txtFName.Text;<BR>   MySqlParam2.SourceColumn = =3D=20 "au_fname";<BR>   adp.InsertCommand.Parameters.Add(MySqlPa= ram2);</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>   SqlParameter = MySqlParam3 =3D new=20 </FONT><FONT face=3DArial=20 size=3D2>SqlParameter("@au_lname",SqlDbType.VarChar);<BR>  &nbs= p;MySqlParam3.Value=20 =3D this.txtLName.Text;<BR>   MySqlParam3.SourceColumn = =3D=20 "au_lname";<BR>   adp.InsertCommand.Parameters.Add(MySqlPa= ram3);</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>   SqlParameter = MySqlParam4 =3D new=20 </FONT><FONT face=3DArial=20 size=3D2>SqlParameter("@contract",SqlDbType.Bit);<BR>   My= SqlParam4.Value=20 =3D "1";<BR>   MySqlParam4.SourceColumn =3D=20 "contract";<BR>   adp.InsertCommand.Parameters.Add(MySqlPa= ram4);</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>}<BR></FONT></DIV> <DIV><FONT face=3DArial size=3D2>What the heck am I missing = here?</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>Thanks.</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2> </DIV></FONT> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>  </FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV></BODY></HTML> ------=_NextPart_000_000A_01C470C1.0AB15790-- Tag: Use Ado.net to update individual cells in Excel Tag: 92645
  - 18
    - can u set a SQL table constraint from within VS.NET? Hi I have an table on an SQL server and need to set a constraint like do not allow an insertion of a record of the first field (a varchar) matches any of the existing records. Just wondering if this can be done from within .NET? I think it can be done from the enterprise manager tool but not sure. Thanks. -- Paul G Software engineer. Tag: Use Ado.net to update individual cells in Excel Tag: 92642
  - 19
    - Data adapter UPDATE command not working I'm trying to either modify or insert new records in a database with the code listed below. I tried adding the RowUpdating and RowUpdated events and put breakpoints there, but it never gets there. I'm not sure why. I've checked the dataset and it gets modified after the LoadDataRow command executes. Any help would be appreciated. Thanks, John --------------- code snippet from the btnSave_Click event ------------- object[] objRow = new object[7]; if (mode != "NewRecord") {objRow[0] = lblOrganizationID.Text;} objRow[1] = txtOrganization.Text; objRow[2] = txtAbbreviation.Text; objRow[3] = cboParent.SelectedValue; objRow[4] = cboHierarchy.SelectedValue; objRow[5] = cboSector.SelectedValue; objRow[6] = cboIndustry.SelectedValue; DataRow rowUpdate; dsOrganizations1.Tables["Organizations"].BeginLoadData(); rowUpdate = dsOrganizations1.Tables["Organizations"].LoadDataRow(objRow,true); dsOrganizations1.Tables["Organizations"].EndLoadData(); this.daOrganizations.Update(dsOrganizations1,"Organizations"); Tag: Use Ado.net to update individual cells in Excel Tag: 92635
  - 20
    - ERROR: ORA-00162: external dbid length 17 is greater than maximum (16) QUESTION: I am receiving this error when I open a connection " ORA-00162: external dbid length 17 is greater than maximum (16) " What is wrong? ANSWER: If you are using System.OracleClient.dll version 1.0.5000.0, you can modify your TNSNAMES.ORA and make alias shorter. TNSNAMES.ora is in oralcle/network/admin directory. For example: tnsnames.ora file has... short122.world = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = 133.231.113.280) (PORT = 1521) ) (CONNECT_DATA = (SID = ORAQ38) ) ) NOTE: If you are using the System.OracleClient.dll version 1.0.3300.0, you don't get this problem. EXAMPLE: In .NET Winforms, populate the datagrid upon button1 click. private void button1_Click(object sender, System.EventArgs e) { Cursor.Current = Cursors.WaitCursor; OracleConnection objOraConn = new OracleConnection("user id=yourid;data source=\"short122.world\";password=password"); objOraConn.Open(); string commandString = " SELECT * FROM A_MYTABLE" ; OracleCommand cmd = objOraConn.CreateCommand(); cmd.CommandText = commandString; cmd.Prepare(); OracleDataAdapter da = new OracleDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); objOraConn.Close(); // Execute the statement dataGrid1.DataSource = ds.Tables[0].DefaultView; Cursor.Current = Cursors.Default; } Tag: Use Ado.net to update individual cells in Excel Tag: 92634
  - 21
    - Can't get value from DataSet without explicity type casting? Hey guys, got a question! I've got a DataSet/DataTable, created by using a DataAdapter.Fill call. I'm writing a C# class that handles all the ADO.NET work I need, and a different class will call the public methods of this class. So; I've created the two public fields I need- Public String _custId and Public DateTime _orderDate. But, I can't get the values out of the DataSet/DataTable/DataColumn without doing (in my mind) useless casting! For instance, this doesn't work: _custID = dsOrders.Tables["tblOrd"].Rows[iCurrentRow][0]; I get the message "Cannot implicitly convert type 'object' to 'string' ". To make it work, I have to do: _custID = (String)dsOrders.Tables["tblOrd"].Rows[iCurrentRow][0]; ....or.... _custID = dsOrders.Tables["tblOrd"].Rows[iCurrentRow][0].ToString(); I'm confused as to why? The following line displays "System.String": Console.WriteLine(dsOrders.Tables["tblOrd"].Rows[iCurrentRow].Table.Columns[ 0].DataType); So, shouldn't that mean that I don't have to do anything else? Why do I need to use (String) or .ToString() ? And I also can't get the DateTime into a variable. Trying _orderDate = dsHousing.Tables["tblOrd"].Rows[iCurrentRow][1]; gives me "Cannot implicitly convert type 'object' to 'System.DateTime' ". And, again, if I display the value for Columns[1].DataType, it shows me "System.DateTime". Worse, there's no ".ToDateTime()" method of the Column object, so the only way I can get the DateTime field out is to cast it to a string, and then convert it back to a DateTime. Compared to my problem with the String column, I'm doing twice the un-necessary work just to get the DateTime column! Is this really what's supposed to happen? If the DataType shows string, why do I need to perform an explicit conversion from object to string? It seems like I'm doing un-necessary work; if the value is stored as a certain datatype, I should be able to pull it out of the DataTable and put it into a variable of the same type. I even toyed with the idea of .NET assuming I wanted the DataColumn being put into a String- but I can't find a property of DataColumn that just gives me the value. In classic ADO, there was a Field object, which had a Value property. I can understand "DataColumn" being an object, but is there a specific property for pulling the value out of DataColumn? For instance, if those code samples I just gave would be in classic ADO, I would be trying to say _CustID = rs.Fields(x).Value. Oh I'm using VS 2003.NET EA, with WinXP, .NET 1.1, and have "Using System.Data" and "Using System.Data.OracleClient" at the top of my C# class definition. At first I thought this might be Oracle related, but DataSet/DataTable/DataColumn is part of the System.Data namespace. Thanks! -Thomas Tag: Use Ado.net to update individual cells in Excel Tag: 92627
  - 22
    - Creating a DataRelation Hi, Creating a DataRelation among two tables seems to be pretty simple (according to ADO.NET Core Reference). However, I'm not sure of the best time to create it. Is it better to create the DataRelation between the tables after the tables have been filled, or is it better to create ForeignKeyConstraints when creating the columns of each table? Roshawn Tag: Use Ado.net to update individual cells in Excel Tag: 92621
  - 23
    - Re: Microsoft Data Access Block for .NET and the "like" statement I am a little confused by your response. In your example you show a select based on the CustomerID, in this case "ALFKI." I don't see how this would produce a 'like' statement. In this case there is only one customer in Northwind..Customers whose id is like 'ALFKI%'. To use a specific example from the Customer table I would like to create a query similar to: select * from Northwind..Customers where ContactName like 'Maria%' Paul =?Utf-8?B?RGF2dWQgQnJuY2g=?= wrote: > Yes here is a sample: > System.Data.SqlClient.SqlParameter[] parms = new System.Data.SqlClient.SqlParameter [1]; > DataSet ds; > parms[0] = new SqlParameter( "@CustomerID","ALFKI"); > ds = SqlHelper.ExecuteDataset(ConnectionString,CommandType.Text,"Select * >From Customers where CustomerID= @CustomerID",parms); > return ds; > > David > > > > "Paul Wolpe" wrote: > > > Is there a way to use a like statement with a SqlParameter and the > > Microsoft Data Access Block? > > > > I would like to be able to do this: > > > > int CustomerID = (int) SqlHelper.ExecuteScalar( > > ConfigurationSettings.AppSettings["connectionString"], > > CommandType.Text, > > @"select CustomerId > > from Northwind..Customers > > where ContactName like '%@customer_name%'", > > new SqlParameter("@customer_name",CustomerName) > > ); > > > > Since the DAB already quotes my SqlParameter, SqlServer ends up seeing > > extra quote signs. Something like: > > > > where ContactName like '%'John Doe'%' > > > > Any thoughts? > > > > -Paul > > > > Microsoft Data Access Block: > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/daab-rm.asp > > Tag: Use Ado.net to update individual cells in Excel Tag: 92618
  - 24
    - Issues with connecting from .NET to Sybase ASE 12 If anyone out there has been successful in connecting to Sybase ASE 12 from Visual Studio .NET 2003 either in a Windows Forms or Reporting Services project, can you please help me. .... In summary, I wish to: (a) create a Windows Forms application using VB.NET (Visual Studio .NET 2003 and .NET Framework 1.1) that will connect to Sybase ASE 12.0 to execute stored procedures. (b) create a SQL Server Reporting Services report using Visual Studio .NET 2003, .NET Framework 1.1 and SQL Server Reporting Services that will connect to Sybase ASE 12.0 also via stored procedures. I have Sybase 12 Client installed on my development machine and am able to connect to a test server from this machine using SQL Advantage. My code for (a) is: >>>>> Dim sybaseconn As System.Data.OleDb.OleDbConnection Dim sybaseconnstring As String = "Provider=Sybase.ASEOLEDBProvider;Server Name=XXXXXX;Initial Catalog=YYYYYY;User ID=UUUUUU;Password=PPPPPP;" Try sybaseconn = New System.Data.OleDb.OleDbConnection(sybaseconnstring) sybaseconn.Open() Catch ex As Exception MsgBox(ex.Message) Finally Select Case sybaseconn.State Case ConnectionState.Broken, ConnectionState.Closed Case Else sybaseconn.Close() sybaseconn.Dispose() End Select sybaseconn = Nothing End Try >>>>> When I run this, I receive the following message on the Open() line: "The 'Sybase.ASEOLEDBProvider' provider is not registered on the local machine." and this is the exception: ?ex {System.InvalidOperationException} [System.InvalidOperationException]: {System.InvalidOperationException} HelpLink: Nothing InnerException: {System.Data.OleDb.OleDbException} Message: "The 'Sybase.ASEOLEDBProvider' provider is not registered on the local machine." Source: "System.Data" StackTrace: " at System.Data.OleDb.OleDbConnection.CreateProviderError(Int32 hr) at System.Data.OleDb.OleDbConnection.CreateProvider(OleDbConnectionString constr) at System.Data.OleDb.OleDbConnection.Open() at Sybase_Stuff.Form1.Button1_Click(Object sender, EventArgs e) in C:\Documents and Settings\allchini\My Documents\Visual Studio Projects\Sybase Stuff\Form1.vb:line 98" TargetSite: {System.Reflection.RuntimeMethodInfo} My problem with (b) is as follows: In my Reporting Services project in Visual Studio .NET 2003 I attempt to set up a shared data source by selecting Add New Datasource. This displays the Data Link Properties dialog. On the Provider tab, I select "Sybase ASE OLE DB Provider". When I press Next or OK, I receive the following message: Microsoft Data Link Error Provider is no longer available. Ensure that the provider is installed properly. Can you tell me how to achieve (a) and/ or (b)? Thanks Ian Allchin Tag: Use Ado.net to update individual cells in Excel Tag: 92615
  - 25
    - testing try/catch when filling a data adapter Hello. This may be a dumb question, but it's driving me crazy. The function is supposed to to fill a datatable. It works just fine, but I'm testing to make sure my error handling works so I changed the name of the stored procedure so I know I will get an error. However, for some reason, my try/catch does not catch the error. Instead, the program seems to stop. Does this make sense? Do you have any ideas why this happens? Thank you! Protected sub Run(ByVal sprocName As String, ByVal parameters() As SqlParameter, ByRef dataTable As DataTable) Dim command As SqlCommand command = CreateCommand(sprocName, parameters) Dim dataAdapter As SqlDataAdapter dataTable = New DataTable dataAdapter = New SqlDataAdapter dataAdapter.SelectCommand = command dataAdapter.Fill(dataTable) End Function Tag: Use Ado.net to update individual cells in Excel Tag: 92611

Hi!!
Can any one help me get this info -
Update individual cells in Excel using ADO.NET

I keep getting error in the update statement.

Thanks for any help in advance!!

Here is my code -

Dim connString As String
connString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=ExcelData1.xls;" & _
"Extended Properties=""Excel 8.0;HDR=YES"""

objconnection = New OleDbConnection(connString)
objconnection.Open() 'open the connection

Dim cmd1 As New OleDbCommand
cmd1.Connection = objConnection

cmd1.CommandText = "INSERT INTO EmployeeData1 (Id, Name,
BirthDate) values ('AAA12', 'Andrew21', '12/4/1955')"

cmd1.ExecuteNonQuery()

cmd1.CommandText = "UPDATE [EmployeeData1] SET id = 'ad',
A2 = 'Cell F3'" ', F2 = 'Cell G3' where F1<>'1'"

cmd1.ExecuteNonQuery()

objConnection.Close()

Top

Re: Use Ado.net to update individual cells in Excel by Steve

Steve
Wed Jul 28 09:01:00 CDT 2004

ty putting single quotes around and a dollar sign after the worksheet in
your statement
e.g
cmd1.CommandText = "UPDATE ['EmployeeData1$'] SET id = 'ad',
A2 = 'Cell F3'" ', F2 = 'Cell G3' where F1<>'1'"

"Lisa" <lisa555@hotmail.com> wrote in message
news:566e01c47449$fee1ebf0$a301280a@phx.gbl...
> Hi!!
> Can any one help me get this info -
> Update individual cells in Excel using ADO.NET
>
> I keep getting error in the update statement.
>
> Thanks for any help in advance!!
>
> Here is my code -
>
>
> Dim connString As String
> connString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
> "Data Source=ExcelData1.xls;" & _
> "Extended Properties=""Excel 8.0;HDR=YES"""
>
> objconnection = New OleDbConnection(connString)
> objconnection.Open() 'open the connection
>
> Dim cmd1 As New OleDbCommand
> cmd1.Connection = objConnection
>
> cmd1.CommandText = "INSERT INTO EmployeeData1 (Id, Name,
> BirthDate) values ('AAA12', 'Andrew21', '12/4/1955')"
>
> cmd1.ExecuteNonQuery()
>
>
>
> cmd1.CommandText = "UPDATE [EmployeeData1] SET id = 'ad',
> A2 = 'Cell F3'" ', F2 = 'Cell G3' where F1<>'1'"
>
> cmd1.ExecuteNonQuery()
>
> objConnection.Close()
>

Top