restrict size of datatable

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
    - Datagrid and DropDown List control question Hi Newsgroup, Is it possible, by that I mean not too difficult to have a drop down list control in a column of the grid control. I know what values the user can edit into a cell for that column so I'd love to give them the convenience of a drop down list. Would a third party control like ComponentOne's TrueDBGrid for .NET be an easier option? Only issue is that it's very expen$ive. Not scared to program my own functionality into the grid if someone can tell me the right API's to use to achieve this. Or any third party .NET compatible control to do this. Thanks you very much and regards, SpotNet. Tag: restrict size of datatable Tag: 97862
  - 2
    - Cannot define the DataMember in code I Cannot define the DataMember in code. This is referencing the author table from the Pubs database in SQLS2k. == Code 1 ~ Selected pertinent code == Private cnPubs As New SqlConnection Private WithEvents cmSelectAuthor As New SqlCommand Private WithEvents daAuthor As New SqlDataAdapter Private WithEvents dsAuthor As New DataSet --- daAuthor.SelectCommand = cmSelectAuthor With daAuthor.SelectCommand .CommandText = "Select * from authors" .Connection = cnPubs With .Parameters .Add("@au_ID", SqlDbType.Char, 11, "au_ID") .Add("@au_lname", SqlDbType.VarChar, 40, "au_lname") .Add("@au_fname", SqlDbType.VarChar, 20, "au_fname") .Add("@phone", SqlDbType.Char, 12, "phone") .Add("@address", SqlDbType.VarChar, 40, "address") .Add("@city", SqlDbType.VarChar, 20, "city") .Add("@state", SqlDbType.Char, 2, "state") .Add("@zip", SqlDbType.Char, 5, "zip") .Add("@contract", SqlDbType.Bit, 1, "contract") End With End With == Code 2 ~ From the Fill button == Me.DataGrid1.DataSource = dsAuthor Me.DataGrid1.DataMember = "authors" << Error occurs here == Error == System.ArgumentException: Cannot create a child list for field authors. What am I missing? Why does it say anything about a child list? Tag: restrict size of datatable Tag: 97859
  - 3
    - sqlconnection vs. oledbconnection I have read numerous posts on this question, but would like to ask it again for clarification or additional input. We have a vb.net app connecting to mssql2000 primarily through stored proceedures. In terms of efficiency and ease of development or ?? what interface is preferred and why? Tag: restrict size of datatable Tag: 97850
  - 4
    - OleDbDataAdapter, Connection String Question? My Question is this, I'm having trouble getting the OleDbDataAdapter to Point to another connection string and pull the data out of the DataSet I created from the Excel Spread Sheet and Place it up in a SQL table. I'm Sorry for the mess of Code but I've been at this all Day now. If you have any Ideas I could surely use the help. Thank You, Terry string File = ofdExcel.FileName; string sConn = @"Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source="+File+@";" + "Extended Properties=Excel 8.0;"; OleDbConnection DC =new OleDbConnection(sConn); DC.Open(); OleDbCommand OC =new OleDbCommand("SELECT * FROM [Sheet1$]"",DC); OleDbDataAdapter OA =new OleDbDataAdapter(); OA.SelectCommand=OC; DataSet DS =new DataSet(); DataTable custTable = DS.Tables.Add("Global"); OA.Fill(DS,"Global"); //--> Clone the old DataSet and create new pointer to DS2 string Oconn = @"Integrated Security=SSPI;Packet Size=4096;Data Source=""SomeSource"";Tag with column collation when possible=False;Initial Catalog=IA;Use Procedure for Prepare=1;Auto Translate=True;Persist Security Info=False;Provider=""SQLOLEDB.1"";Workstation ID=TVIT13;Use Encryption for Data=False"; OleDbConnection CO2 =new OleDbConnection(Oconn); DataSet DS2 = new DataSet(); DS2 = DS.Clone(); //-->Clone DataSet OleDbCommand OC2 = new OleDbCommand(); OleDbDataAdapter OA2 =new OleDbDataAdapter(); //The Problem is this, How can I get the Clone DataSet to Dump into a OleDbDataAdapter and point it over to the new connection string to place into the SQL Data Base?????? OA2.SelectCommand=OC2; Tag: restrict size of datatable Tag: 97842
  - 5
    - How to copy n paste .asp files to Solution Explorer and have it show up there? Hi, I have just installed Visual Studio.net 2003 Enterprise Architect. I have been using Visual Interdev so far, and I am used to be able to select from a desktop, drag bunch of .asp files, and drop them into a Project Explorer window and have it show there as a tree of files. Is there some setting that I need to enable in Visual Studio.Net to do the drag and drop to Solution Explorer (which seems to be the equivalent of Project Explorer in InterDev) and have it show there as a tree of files? Thank you for your help. Tag: restrict size of datatable Tag: 97837
  - 6
    - Please help me read this exception Where is the problem? Find Below ?Exception ?Stored procedure ?Table Definition ?XML Dataset === The Exception ================================ System.Data.SqlClient.SqlException: Procedure or function F1Ins has too many arguments specified. at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping) at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable) at Test_lkp01RefSource.Form1.Button2_Click(Object sender, EventArgs e) in C:\Documents and Settings\DBuchanan\My Documents\Visual Studio Projects\Test_lkp01RefSource\Form1.vb:line 167 An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in Test_lkp01RefSource.exe Additional information: System error. === End ======================================== === The Stored procedure ========================== CREATE PROCEDURE [dbo].[F1Ins] ( @RefSource varchar(25), @ord tinyint, @hide bit ) AS SET NOCOUNT OFF; INSERT INTO lkp01RefSource(RefSource, ord, hide) VALUES (@RefSource, @ord, @hide); SELECT RefSourceID, RefSource, ord, hide FROM lkp01RefSource WHERE (RefSourceID = @@IDENTITY); GO === End ======================================== === The Table Definition ============================ CREATE TABLE [dbo].[lkp01RefSource] ( [RefSourceID] [tinyint] IDENTITY (1, 1) NOT NULL , [RefSource] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [ord] [tinyint] NOT NULL , [hide] [bit] NOT NULL ) ON [PRIMARY] GO === End ======================================== === The XML Dataset ============================== <?xml version="1.0" standalone="yes"?> <xs:schema id="Dsf1" targetNamespace="http://www.tempuri.org/Dsf1.xsd" xmlns:mstns="http://www.tempuri.org/Dsf1.xsd" xmlns="http://www.tempuri.org/Dsf1.xsd" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" attributeFormDefault="qualified" elementFormDefault="qualified"> <xs:element name="Dsf1" msdata:IsDataSet="true"> <xs:complexType> <xs:choice maxOccurs="unbounded"> <xs:element name="lkp01RefSource"> <xs:complexType> <xs:sequence> <xs:element name="RefSourceID" msdata:ReadOnly="true" msdata:AutoIncrement="true" type="xs:unsignedByte" /> <xs:element name="RefSource" type="xs:string" /> <xs:element name="ord" type="xs:unsignedByte" default="0"/> <xs:element name="hide" type="xs:boolean" default="0"/> </xs:sequence> </xs:complexType> </xs:element> </xs:choice> </xs:complexType> <xs:unique name="Constraint1" msdata:PrimaryKey="true"> <xs:selector xpath=".//mstns:lkp01RefSource" /> <xs:field xpath="mstns:RefSourceID" /> </xs:unique> </xs:element> </xs:schema> === End ======================================== Tag: restrict size of datatable Tag: 97833
  - 7
    - listbox doesn't refresh after adding an item listbox doesn't refresh after adding an item. On a page I have 4 listboxes, the last three of which are child entries of the previous. Works fine. Above each listbox I have a New button. New opens up a NewConcepts form in an AddNew state. I fill in the new concept, update the database and the form closes. NewConcepts was opened showdialog so focus returns to the listboxes. I would have expected the listbox to show the new concept, but it doesn't. Here is the code for the relevant New button: Private Sub btnNewC0_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNewC0.Click Dim frm As New frmNewConcept With frm '.iNew = Me.lstConcepts0.SelectedValue this is true for all the others .iNew = 0 .iNewAddMode = True .ShowDialog() End With 'Dim int As Integer = frm.iAddNumber 'MsgBox(int.ToString) If frm.iAddNumber > 0 Then Me.dsC.Clear() Me.daC.Fill(dsC, "Concepts") Me.lstConcepts0.Refresh() End If frm.Dispose() End Sub If I check the database, the new concept is there. If I close the form and reopen it, the new concept is there. Can somebody please be so kind as to show me how to persuade the listbox to show the new item? Thank you. Dennis Tag: restrict size of datatable Tag: 97832
  - 8
    - Can't create DataAdapter in code that's available throughout form Can't create DataAdapter in code that's available throughout form In form_load I write the following code: Dim daC As New OleDbDataAdapter("SELECT * FROM Concepts", cnConcepts) dsC.Concepts.Constraints.RemoveAt(1) daC.Fill(dsC, "Concepts") dsC.Concepts.ActiveColumn.DefaultValue = True This Kevin Yu will be familiar with. It fills a series of listboxes with concepts. In some of the listboxes a New button will appear above the listbox. Clicking the New button will bring up a form in addnew mode only for concepts with the proper ParentID. It all works fine. The problem arises when I want to clear and refill the listboxes, so that the new entry in the appropriate listbox will show. Here is part of the code for the appropriate New button. Dim frm As New frmNewConcept With frm '.iNew = Me.lstConcepts0.SelectedValue this is true for all the others .iNew = 0 .iNewAddMode = True .ShowDialog() End With If frm.iAddNumber > 0 Then dsC.Clear() daC.Fill(dsC, "Concepts") End If Of course it won't compile. In the New button I get the message 'Name daC is not declared'. I tried declaring daC on the form level Dim daC As New OleDbDataAdapter("SELECT * FROM Concepts", cnConcepts). It will compile, but I get the runtime error, SystemInvalidOperationException. The select command property has not been initialized before calling 'Fill'. ... at system.data.common at dbdataadapter.fill(DataSet.dataset, strSourceTable) ... at TopicFromStart_Load...line 1386 Line 1386 Line 1386 is daC.Fill(dsC, "Concepts") My question is how do I code a DataApapter in a form so that it fills the Dataset and also is available to other controls' event procedures. Thank you in advance. Dennis Tag: restrict size of datatable Tag: 97830
  - 9
    - GAC how can I install an assmebly using gactutil.exe on a GAC directory on a remote server? thanks for your help! Reza Tag: restrict size of datatable Tag: 97817
  - 10
    - SQLServer or ADONET tricky I think there must be a trick (in SQLserver or ADONET) to do it: I have created a view with 4 fields. The last field is a calculated field myView (From a relationship between Table1(T1) and Table2(T2)) T1.Agent T1.Sales T2.Charge Total (Sales-Charge) Ken 2000 500 1500 Tom 3000 NULL NULL There is no value (Row) for Tom in the table who has the charge field (T2), as a consecuence the total is Null because the sustraction can be made. Is it possible to get this outputs for Tom without adding a record to the table who has the charge field(T2) ? Tom 3000 NULL 3000 What is important is to get a real value in the total field. any sug ken Tag: restrict size of datatable Tag: 97811
  - 11
    - Beginner's question re: DataSets and the dispose method [C#] Hi everyone, I suppose this question reaches a little further than just DataSets, but I was wondering if I should/need to call the Dispose() method on a DataSet I'm about to reassign. For instance: DataSet m_DataSet = new DataSet("My DataSet"); //do something below here... //now, in the same method, reassign m_DataSet. But should I call Dispose() first? m_DataSet = new DataSet("Another DataSet"); I'm not sure if just reassigning the m_DataSet variable (without calling Dispose) is ok or maybe not the best practice. Or, is it a Bad Idea, capitalized and underscored? Thanks for the help. -Jim Tag: restrict size of datatable Tag: 97806
  - 12
    - Validate Data in SQL table I have a form that the user enters values on it. One of the fields I need to validate what is being entered against records in a SQL table. Something like doing a findfirst in Access and then doing a nomatch. How do I go about doing this? Thanks In Advance, Lou Tag: restrict size of datatable Tag: 97805
  - 13
    - DataSet / HasChanges / Delete Row Hi, In my DataSet, I'm trying to keep track of row deletions, so later I can = check HasChanges to see if I need to update the datasource. I'm trying = to delete a specific row in the dataset that I search out from a primary = key. I've found the DataRow I want to delete and have a reference to = it. I've tried this, myDataSet.myTable.rows.remove(theRowIFound), but it appears this = actually removes the row, and doesn't just change the rowstate = (correct?) The only way I see to delete a row/change rowstate is to have the actual = ordinal of the row I'm after. How can I get this? Or is there an = easier way I can delete this row and change the rowstate, using the = actual reference to the row I have? Thanks! --Michael Tag: restrict size of datatable Tag: 97804
  - 14
    - Urgent~ How to merge 2 datasets in 1 DataGrid? Hi experts, I am not sure if I should use Merge on this case, but I want to add a Vendor Name column in the datagrid1 where the Vendor Name has to get from another server (not linked and can't be linked). The first table contains Job tracking information, which has a "J_Vendor_ID" column, and no Vendor Name in this server. The 2nd table on the other server contains VendName and VendorID. I want to have a Vendor Name column (VendName) in datagrid, and VendName column has to get from the 2nd table, the VendName should list only from table1's the VendorID. The J_vendor_ID from table1 and VendorID from table 2 will be the key columns for the 2 datasets merging. I received error when it's doing the dataSet.Merge(dataSet2): System.Data.DataException: Mismatch columns in the PrimaryKey : Tag: restrict size of datatable Tag: 97802
  - 15
    - click on datagrid item, display new datagrid based on that click I want to have a datagrid that is just one column of group names (say from qryGroups) When someone clicks on a group name (ie clicks in that 'cell' or the column could be a hyperlink column... whatever is best), i want the datagrid to then display results from qrySubgroups, but filter it for only those that have the column intGroup equal to the intGroup value from the one they clicked from qryGroups ie qryGroups has: intGroup GroupName 1 Accounting 2 Orders 3 Service qrySubgroups has intGroup SubGroupName 1 Taxes 1 Booked Revenue 2 Customers 3 Maintenance 3 Repairs So there are Taxes and Booked Rev as subgroups of Accounting so the original dataset will display: Accounting Orders Service when the user clicks on Accounting, it should then display: Taxes Booked Revenue how do i best do this? can i just put both tables (qryGroups, qrySubgroups) into the dataset that the table runs off of, then when they click on it, have it change from using dataset1.qryGroups to dataset1.qrySubgroups and have it filter by qryGroups.intGroup ? or maybe the original cells are hyperlinks and they open subgroups.aspx and pass the value of the group (intGroup) and then the dataset on that page runs off of that value (filters by it?) or maybe some easier or other way i'm not thinking of! any help!?!? Tag: restrict size of datatable Tag: 97799
  - 16
    - Error with automated execution Hi I am opening a connection on a shared db as below which works fine when used in a vb.net win app. Conn = New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=F:\Events Data\Events Data.mdb;User Id=admin;Password=;") The problem is that as soon as I turn the app into a service or run it using windows scheduler I get error on conn.open statement. The error is 'F:\Events Data\Events Data.mdb' is an invalid path, please check that the path is correct...or something on those lines. If I run it as service on a standalone PC then that works. So apparently db connection have different rules for a standard win app and services with a db shared over a lan. How can I make the code work as a service? Thanks Regards Tag: restrict size of datatable Tag: 97798
  - 17
    - Architectural Question This is a multi-part message in MIME format. ------=_NextPart_000_0311_01C4B836.46A5C3F0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello, I'm using a Component Class object to represent a database table. This = Data Access Object (DAO) has a connection object and data adapter object = in it along with a bunch of methods that need to be there every time I = create a new DAO to represent a db table.=20 Instead of having to code that "common" logic for each new class I'd = like to create a base DAO, put the common logic there and inherit from = it (instead of System.ComponentModel.Component) for each new DAO. My current steps for creating a new DAO are: 1) Create a new Component Class object 2) Drag the database from the Server Explorer onto the design pane. This = creates the connection object to the db. 3) Drag a table from the database in Server Explorer onto the design = pane. This creates the data adapter object for that table. 4) Create all the common methods in the new DAO Within an application, each DAO will have the exact same connection = object so I'd like to include that in the base DAO. Thinking along those lines, I created a new DAO (call it BaseDAO) and = did steps 1, 2 & 4 from above. Then I created another DAO (ChildDAO) = that inherits from the BaseDAO. I was hoping that I would then be able = to just do step 3 from above for this new DAO.=20 When I try to bring up the designer for ChildDAO I get the following = error: The designer could not be shown for this file because none of the = classes within it can be designed. The designer inspected the following = classes in the file:=20 ChildDAO --- The base class 'LayerDataAccess.BaseDAO' could not be = loaded. Ensure the assembly has been referenced or built if it is part = of the project. Both the BaseDAO and ChildDAO objects are in the same project. Does = anyone have any ideas what I'm doing wrong? Thanks in advance for any help. David. ------=_NextPart_000_0311_01C4B836.46A5C3F0 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.2800.1476" name=3DGENERATOR> <STYLE></STYLE> </HEAD> <BODY> <DIV><FONT face=3DArial size=3D2>Hello,</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>I'm using a Component Class object to = represent a=20 database table. This Data Access Object (DAO) has a connection = object and=20 data adapter object in it along with a bunch of methods that need to be = there=20 every time I create a new DAO to represent a db table. </FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>Instead of having to code that "common" = logic for=20 each new class I'd like to create a base DAO, put the common logic = there=20 and inherit from it (instead of System.ComponentModel.Component) for = each new=20 DAO.</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>My current steps for creating a new DAO = are:</FONT></DIV> <DIV><FONT face=3DArial size=3D2>1) Create a new Component Class = object</FONT></DIV> <DIV><FONT face=3DArial size=3D2>2) Drag the database from the Server = Explorer onto=20 the design pane. This creates the connection object to the = db.</FONT></DIV> <DIV><FONT face=3DArial size=3D2>3) Drag a table from the database in = Server=20 Explorer onto the design pane. This creates the data adapter object for = that=20 table.</FONT></DIV> <DIV><FONT face=3DArial size=3D2>4) Create all the common methods in the = new=20 DAO</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>Within an application, each DAO will = have the exact=20 same connection object so I'd like to include that in the base = DAO.</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>Thinking along those lines, I created a = new=20 DAO (call it BaseDAO) and did steps 1, 2 & 4 from above. = Then I=20 created another DAO (ChildDAO) that inherits from the BaseDAO. I = was hoping=20 that I would then be able to just do step 3 from above for this new DAO. = </FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>When I try to bring up the designer for = ChildDAO I=20 get the following error:</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <BLOCKQUOTE dir=3Dltr style=3D"MARGIN-RIGHT: 0px"> <DIV><FONT face=3DArial size=3D2> The designer could not be shown = for this=20 file because none of the classes within it can be designed.  The = designer=20 inspected the following classes in the file: </FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2> ChildDAO --- The base class=20 'LayerDataAccess.BaseDAO' could not be loaded.  Ensure the = assembly has=20 been referenced or built if it is part of the=20 project.<BR></FONT></DIV></BLOCKQUOTE> <DIV><FONT face=3DArial size=3D2>Both the BaseDAO and ChildDAO objects = are in the=20 same project. Does anyone have any ideas what I'm doing = wrong?</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>Thanks in advance for any = help.</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>David.</FONT></DIV></BODY></HTML> ------=_NextPart_000_0311_01C4B836.46A5C3F0-- Tag: restrict size of datatable Tag: 97790
  - 18
    - Error filling datagrid --____LPHMXLZMXOMRLFKSEJCW____ Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Hi I am trying to fill a datagrid using the following snippet: GetSQL =3D "Select QID, Q, A, B, C, D, CorrectAnswer, Expired " & _ "From Questions " & _ "INNER JOIN " & _ "QuestionTypesList ON Questions.QTID =3D QuestionTypesList= .QTID " & _ "WHERE (QuestionTypesList.QuestionTypes =3D '" & = QsetSelected & "')" rs.Close() rs.Open(GetSQL, ConfigCN, ADODB.CursorTypeEnum.adOpenStatic, = ADODB.LockTypeEnum.adLockOptimistic) rs.MoveFirst() Dim da As OleDbDataAdapter =3D New OleDbDataAdapter Dim ds As DataSet =3D New DataSet Try da.Fill(ds, rs, "QTable") Catch ex As Exception MsgBox(ex) End Try DataGrid1.DataSource =3D ds.Tables(0) But when I do I get a catch error when trying da.fill: Argument 'Prompt' cannot be converted to type 'String'. Any ideas/suggestions welcome! Cheers Adam --____LPHMXLZMXOMRLFKSEJCW____ Content-Type: multipart/related; boundary="____WHPEPQYSAQXEHDGESJXG____" --____WHPEPQYSAQXEHDGESJXG____ Content-Type: text/html; charset=windows-1252 Content-Transfer-Encoding: quoted-printable <HTML><HEAD> <META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-1"= > <META content=3D"MSHTML 6.00.2800.1476" name=3DGENERATOR></HEAD> <BODY style=3D"MARGIN: 4px 4px 1px; FONT: 8pt Tahoma; COLOR: #000000"> <DIV>Hi I am trying to fill a datagrid using the following snippet:</DIV> <DIV>GetSQL =3D "Select QID, Q, A, B, C, D, CorrectAnswer, Expired " & = _<BR>           &nbs= p;     "From Questions " & _<BR>   &= nbsp;           &nbs= p; "INNER JOIN " & _<BR>       &nbsp= ;         "QuestionTypesList ON = Questions.QTID =3D QuestionTypesList.QTID " & _<BR>   &n= bsp;           &nbsp= ; "WHERE (QuestionTypesList.QuestionTypes =3D '" & QsetSelected & = "')"</DIV> <DIV> </DIV> <DIV>        rs.Close()<BR>  &= nbsp;     rs.Open(GetSQL, ConfigCN, ADODB.CursorTypeEnu= m.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic)<BR>   &= nbsp;    rs.MoveFirst()<BR>     &nb= sp;  Dim da As OleDbDataAdapter =3D New OleDbDataAdapter<BR> &nbs= p;      Dim ds As DataSet =3D New DataSet<BR>&nbsp= ;       Try<BR>     =        da.Fill(ds, rs, "QTable")<BR> &nb= sp;      Catch ex As Exception<BR>  &nbs= p;         MsgBox(ex)<BR> &nbs= p;      End Try<BR>     &= nbsp;  DataGrid1.DataSource =3D ds.Tables(0)</DIV> <DIV> </DIV> <DIV>But when I do I get a catch error when trying da.fill:</DIV> <DIV>Argument 'Prompt' cannot be converted to type 'String'.</DIV> <DIV> </DIV> <DIV>Any ideas/suggestions welcome!</DIV> <DIV> </DIV> <DIV>Cheers</DIV> <DIV>Adam</DIV></BODY></HTML> --____WHPEPQYSAQXEHDGESJXG____-- --____LPHMXLZMXOMRLFKSEJCW____-- Tag: restrict size of datatable Tag: 97788
  - 19
    - Generated Insert Command from CommandBuilder I'm having some trouble with an insert back to a datasource using the the insertcommand built by commandbuilder. I suspect it may be a slight problem with the insert statement. Is there any way to see the actual insert statement that commandbuilder creates? Tag: restrict size of datatable Tag: 97777
  - 20
    - returning numeric values from access database Hi - I am developing a database class which accesses an Access database to simply retrieve decimal values. When I call the code below it fails with an ' Unspecified error' - The database it calls have the 2 columns (BEGINDEC & ENDDEC) and both are set to data type 'Double' in the database. I have also tried this code in varying forms but always with different error messages - what is my best option to return Double values from an Access Database - James... double ipdec = GetAddress(Address); OleDbConnection conn = new OleDbConnection(@"Data Source=GIPS.mdb;Password=;Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin"); try { string sql = "SELECT COUNTRY FROM GEOS WHERE BEGINDEC >= "; sql += ipdec; sql += " AND ENDDEC <= "; sql += ipdec.ToString(); conn.Open(); OleDbDataAdapter adapt = new OleDbDataAdapter(sql,conn); DataSet ds = new DataSet(); adapt.Fill(ds); foreach(DataRow dr in ds.Tables[0].Rows) { Debug.WriteLine(dr["COUNTRY"]); }catch{exception ex)Debug,Writeline(ex.Message)); Tag: restrict size of datatable Tag: 97776
  - 21
    - Call SQL Stored procedure with Bit datatype I am having difficulty calling a stored procedure from VB.Net that has a Bit parameter. To try and figure it out, I have created some tests. The SQL SP looks like this: CREATE procedure up_insert_test @value bit as insert into tblTest(field1) values(@value) And the table has two fields. The first is ID which is an indentity field, and the second is Field1 that is just a nullable Bit field. Here is my VB Code: Dim cn As SqlConnection Dim cmd As SqlCommand cn = New SqlConnection(g_DBConnection) cn.Open() If cn.State = ConnectionState.Open Then cmd = New SqlCommand("up_insert_test") cmd.Connection = cn Dim p As New SqlParameter("@value", SqlDbType.Bit) p.Value = 1 cmd.Parameters.Add(p) cmd.ExecuteNonQuery() 'Fails here!!!!! cn.Close() End If Everytime I get to the ExecuteNonQuery I get the followning error: An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in system.data.dll Additional information: System error. If anyone can give me a hand, I would greatly appreciate it. Thanks, Rob Tag: restrict size of datatable Tag: 97763
  - 22
    - How to get the parent of a deleted (child) row Hello, My question is quite straightforward: How do I get the parent row of a deleted (child) row. If I use the method "GetParentRow" of the child then it returns "nothing". I have tried using the version parameter of the method but without any result. I probably could use the original value of the deleted row to "manually" go through the parent rows and find its parent but this seems like a lot of work for something as simple as this. Hoping for a quick answer, WiZZiX Tag: restrict size of datatable Tag: 97762
  - 23
    - Saving rtf using binary and oledb --____LPHMXLZMXOMRLFKSEJCW____ Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Hi, I am trying to save some rtf code from an rtf field to a TEXT field on = SQL2000. The code I have been playing with (thanks to Nick for the originally = supplied code!) is: Sub DoByteConversion() Dim pBytStuff As Byte() =3D StringToArray(Rtxt_readme.Rtf) = 'convert rtf field to byte array Dim pDBNConnection As OleDbConnection Dim pDBCCommand As OleDbCommand pDBNConnection =3D New OleDbConnection pDBNConnection.ConnectionString =3D Globals.udl0Val 'take udk = connection from Globals Class Call pDBNConnection.Open() If (pDBNConnection.State =3D ConnectionState.Open) Then pDBCCommand =3D pDBNConnection.CreateCommand pDBCCommand.CommandText =3D "UPDATE TestsConfig Set ReadMe =3D = @bits Where Type =3D '" & TestSelected & "'" pDBCCommand.CommandTimeout =3D 20 Call addParameter(pDBCCommand, "bits", pBytStuff) If (pDBCCommand.ExecuteNonQuery() > 0) Then 'OPERATION WAS FINE! End If End If End Sub Public Function getOleDbType(ByVal iType As Type) As OleDbType If (iType Is GetType(String)) Then Return OleDbType.VarChar ElseIf (iType Is GetType(Integer)) Then Return OleDbType.Integer ElseIf (iType Is GetType(Boolean)) Then Return OleDbType.Boolean ElseIf (iType Is GetType(Date)) Then Return OleDbType.Date ElseIf (iType Is GetType(Char)) Then Return OleDbType.Char ElseIf (iType Is GetType(Decimal)) Then Return OleDbType.Decimal ElseIf (iType Is GetType(Double)) Then Return OleDbType.Double ElseIf (iType Is GetType(Single)) Then Return OleDbType.Single ElseIf (iType Is GetType(Byte())) Then Return OleDbType.Binary ElseIf (iType Is GetType(Guid)) Then Return OleDbType.Guid End If End Function Public Sub addParameter(ByVal iCommand As OleDbCommand, ByVal = iParameter As String, _ ByVal iValue As Object) If (iCommand.CommandText.IndexOf(iParameter) <> -1) Then Dim pODPParameter As OleDbParameter =3D _ iCommand.Parameters.Add(iParameter, getOleDbType(iValue.GetType= )) pODPParameter.Value =3D iValue End If End Sub However when I run this it I just get an unhandled exception in Data.Dll Any ideas would be appreciated! Cheers Adam --____LPHMXLZMXOMRLFKSEJCW____ Content-Type: multipart/related; boundary="____WHPEPQYSAQXEHDGESJXG____" --____WHPEPQYSAQXEHDGESJXG____ Content-Type: text/html; charset=windows-1252 Content-Transfer-Encoding: quoted-printable <HTML><HEAD> <META http-equiv=3DContent-Type content=3D"text/html; charset=3Diso-8859-1"= > <META content=3D"MSHTML 6.00.2800.1476" name=3DGENERATOR></HEAD> <BODY style=3D"MARGIN: 4px 4px 1px; FONT: 8pt Tahoma; COLOR: #000000"> <DIV>Hi,</DIV> <DIV> </DIV> <DIV>I am trying to save some rtf code from an rtf field to a TEXT = field on SQL2000.</DIV> <DIV> </DIV> <DIV>The code I have been playing with (thanks to Nick for the originally = supplied code!) is:</DIV> <DIV>Sub DoByteConversion()</DIV> <DIV> </DIV> <DIV>        Dim pBytStuff As Byte() = =3D StringToArray(Rtxt_readme.Rtf) 'convert rtf field to byte array</DIV> <DIV> </DIV> <DIV>        Dim pDBNConnection As = OleDbConnection<BR>        Dim = pDBCCommand As OleDbCommand<BR>        = pDBNConnection =3D New OleDbConnection<BR>     &nb= sp;  pDBNConnection.ConnectionString =3D Globals.udl0Val 'take udk = connection from Globals Class<BR>       = Call pDBNConnection.Open()<BR>        = If (pDBNConnection.State =3D ConnectionState.Open) Then<BR>  &nbs= p;         pDBCCommand =3D = pDBNConnection.CreateCommand<BR>       &= nbsp;    pDBCCommand.CommandText =3D "UPDATE TestsConfig = Set ReadMe =3D @bits Where Type =3D '" & TestSelected & "'"<BR>&nbs= p;           pDBCCommand.= CommandTimeout =3D 20<BR>        &n= bsp;   Call addParameter(pDBCCommand, "bits", pBytStuff)<BR>&nbsp= ;           If (pDBCComma= nd.ExecuteNonQuery() > 0) Then<BR>      &n= bsp;         'OPERATION WAS = FINE!<BR>           = End If<BR>        End If<BR> &nbsp= ;  End Sub<BR>    Public Function getOleDbType(ByVal = iType As Type) As OleDbType<BR>        = If (iType Is GetType(String)) Then<BR>      &= nbsp;     Return OleDbType.VarChar<BR>  &nbsp= ;     ElseIf (iType Is GetType(Integer)) Then<BR> =            Return = OleDbType.Integer<BR>        ElseIf = (iType Is GetType(Boolean)) Then<BR>      &nb= sp;     Return OleDbType.Boolean<BR>   &= nbsp;    ElseIf (iType Is GetType(Date)) Then<BR> &nbsp= ;          Return OleDbType.Da= te<BR>        ElseIf (iType Is = GetType(Char)) Then<BR>        &nbs= p;   Return OleDbType.Char<BR>     &nbsp= ;  ElseIf (iType Is GetType(Decimal)) Then<BR>    =         Return OleDbType.Decimal<BR>&nbs= p;       ElseIf (iType Is GetType(Double)) = Then<BR>            = Return OleDbType.Double<BR>        = ElseIf (iType Is GetType(Single)) Then<BR>     &nb= sp;      Return OleDbType.Single<BR>  &n= bsp;     ElseIf (iType Is GetType(Byte())) Then<BR>&nbs= p;           Return = OleDbType.Binary<BR>        ElseIf = (iType Is GetType(Guid)) Then<BR>       =      Return OleDbType.Guid<BR>    &= nbsp;   End If<BR>    End Function</DIV> <DIV> </DIV> <DIV>    Public Sub addParameter(ByVal iCommand As = OleDbCommand, ByVal iParameter As String, _<BR>    &nbs= p;            &= nbsp;          ByVal iValue = As Object)</DIV> <DIV> </DIV> <DIV>        If (iCommand.CommandText.In= dexOf(iParameter) <> -1) Then<BR>      =       Dim pODPParameter As OleDbParameter =3D = _<BR>            = iCommand.Parameters.Add(iParameter, getOleDbType(iValue.GetType))<BR> =            pODPParameter.= Value =3D iValue<BR>        End = If<BR>    End Sub</DIV> <DIV> </DIV> <DIV>However when I run this it I just get an unhandled exception in = Data.Dll</DIV> <DIV> </DIV> <DIV>Any ideas would be appreciated!</DIV> <DIV> </DIV> <DIV>Cheers</DIV> <DIV>Adam</DIV></BODY></HTML> --____WHPEPQYSAQXEHDGESJXG____-- --____LPHMXLZMXOMRLFKSEJCW____-- Tag: restrict size of datatable Tag: 97761
  - 24
    - OutputParameter does not return valid value from Oracle StoredProcedure Hi all, I have a problem using a stored procedure that insert a new record and set the value of an output parameter to the new ID. The insert works fine but the output parameter is always 0. Here is the sp code : PROCEDURE AddRole(RoleSite_ID OUT Number, Site_ID in Number, Default_Role in NUMBER, Created_By in Number) AS BEGIN INSERT INTO D_RoleSite(Rolesiteid, SiteID, IsDefaultRole, CreationDate, CreatedBy, LastUpdate, UpdatedBy) VALUES (Seq_RoleSite.Nextval, Site_ID, Default_Role, (SELECT CURRENT_DATE FROM DUAL), created_By, (SELECT CURRENT_DATE FROM DUAL), created_By); SELECT Seq_RoleSite.CurrVal INTO RoleSite_ID FROM DUAL; COMMIT; End AddRole; and my .net code : OracleCommand oCmd = new OracleCommand("ASPortal.PermissionCtl.AddRole", oConn); oCmd.CommandType = CommandType.StoredProcedure; OracleParameter oParam = new OracleParameter("Site_ID", OracleDbType.Int32, siteID, ParameterDirection.Input); oCmd.Parameters.Add(oParam); oParam = new OracleParameter("Default_Role", OracleDbType.Int32, defaultRole, ParameterDirection.Input); oParam = new OracleParameter("Created_By", OracleDbType.Int32, createdBy, ParameterDirection.Input); oCmd.Parameters.Add(oParam); oParam = new OracleParameter("RoleSite_ID", OracleDbType.Int32, ParameterDirection.Output); oCmd.Parameters.Add(oParam); try { oConn.Open(); oCmd.ExecuteNonQuery(); roleID = (int)oCmd.Parameters["RoleSite_ID"].Value; ... roleID's value is always 0 when I test my sp in PL/SQL developer the output parameter is well assigned with the new ID. Is use ODP .NET Any help will be appreciated. Kind regards. Tag: restrict size of datatable Tag: 97758
  - 25
    - .Fill in OleDbDataAdapter We are having a problem with OleDbDataAdapters. We have found that if you call the .Fill method twice the second time it returns no rows. You can also reproducs this by persisting the recordset to XML first. If you persist it to binary format it works fine. Does anyone have a solution for this? Thanks, Tag: restrict size of datatable Tag: 97749
- Next
  - 1
    - .Fill on OleDbDataAdapter We are having a problem with OleDbDataAdapters. We have found that if you call the .Fill method twice the second time it returns no rows. You can also reproducs this by persisting the recordset to XML first. If you persist it to binary format it works fine. Does anyone have a solution for this? Thanks, Tag: restrict size of datatable Tag: 97748
  - 2
    - ASP Error on INSERT Statement Hi I'm getting the following error: Microsoft OLE DB Provider for ODBC Drivers error '80040e14' [Microsoft][ODBC Microsoft Access Driver] The INSERT INTO statement contains the following unknown field name: 'ClientName'. Make sure you have typed the name correctly, and try the operation again. I have checked the database and the field names are correct. The IIS server has the appropriate permissoins to the database. When I run the query from Access it sucessfully inserts a row. Thanks for your help and here is my code: set objCmd = server.CreateObject("adodb.command") set objCmd.ActiveConnection = oUnifiedConn DIM strTest strTest = "INSERT INTO Client (ClientName) VALUES ('" & strName & "');" response.Write strTest objCmd.CommandText = strTest 'objCmd.CommandType = adCmdtext objCmd.Execute ' line 82 Thanks Mark Tag: restrict size of datatable Tag: 97742
  - 3
    - ASP Error on INSERT INTO Statement Hi I'm getting the following error: Microsoft OLE DB Provider for ODBC Drivers error '80040e14' [Microsoft][ODBC Microsoft Access Driver] The INSERT INTO statement contains the following unknown field name: 'ClientName'. Make sure you have typed the name correctly, and try the operation again. I have checked the database and the field names are correct. The IIS server has the appropriate permissoins to the database. When I run the query from Access it sucessfully inserts a row. Thanks for your help and here is my code: set objCmd = server.CreateObject("adodb.command") set objCmd.ActiveConnection = oUnifiedConn DIM strTest strTest = "INSERT INTO Client (ClientName) VALUES ('" & strName & "');" response.Write strTest objCmd.CommandText = strTest 'objCmd.CommandType = adCmdtext objCmd.Execute ' line 82 Thanks Mark Tag: restrict size of datatable Tag: 97741
  - 4
    - HELP i need to write an SQL statement that will have 4 fields and 4 parameters now the parameter may or may not have values and the fields them-self may or may not have values this is what i have so far SELECT field1, field2, field3, field4 FROM theTable WHERE (field1 = P1 or P1 IS NULL AND field1 IS NULL) AND (field2 = P2 or P2 IS NULL AND field2 IS NULL) AND (field3 = P3 or P3 IS NULL AND field3 IS NULL) AND (field4 = P4 or P4 IS NULL AND field4 IS NULL) p1 dose not have a value p2 = 'A' p3 = 'c' p4 dose not have a value -- Darren Spooner, EMT-B Darren@spoonerusa.net U.S. Space & Rocket Center Shipping & Receiving Clerk & Developer DarrenS@spacecamp.com HEMSI Urban Responder Unit 323 Tag: restrict size of datatable Tag: 97739
  - 5
    - Filter from a DataTable Hi, I need to do a filter from a datatable but with a group condition. I want to do some thing like "Select field From table Group by field" but with the data in my datatable. Thanks Tag: restrict size of datatable Tag: 97738
  - 6
    - File access question Hello folks, I am experiencing a rather strange problem. I have written a service that attempts to open a file on a network share. When I try to open the file from my service, I get the "Access is denied" error. However, if I try to open the file from a GUI application, I can open the file without problem. I've tried using the FileIOPermissions object to get around the error with no luck. Obviously I am missing something very fundamental, the file is accessible to 'Everyone'. Does anybody know what I am doing wrong? Thank you, Jason Tag: restrict size of datatable Tag: 97732
  - 7
    - ADO add to database problem, help? Okay so i'm having some trouble with adding to my database through the dataset. i can read from it and it prints just fine, i run the code okay with no errors, but it simply doesn't add the record, i've left it simple here just so i can get it right before i start adding the extra functionality. okay so the table name is UserID, and i want to add into the table, what am i doing wrong? private void btnAddRecord_Click(object sender, System.EventArgs e) { OleDbConnection AddConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\salesleadmgmt.mdb"); OleDbDataAdapter addAdapter = new OleDbDataAdapter("Select * from UserID", AddConnection); DataSet addSet = new DataSet(); DataRow addRow; OleDbCommandBuilder addCommandBuilder = new OleDbCommandBuilder(addAdapter); addAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey; addAdapter.Fill(addSet, "UserID"); Console.WriteLine("i was able to load the userid table."); addRow = addSet.Tables["UserID"].NewRow(); addRow["UsrName"] = "Bob"; addRow["passwr"] = "Bobpwd"; addRow["UsrID"] = "6"; addSet.Tables["UserID"].Rows.Add(addRow); Console.WriteLine("i inserted the new values"); } this runs in the .CS file, i don't get any of the write lines or anything, it does run on a button click, i have another button click that works fine, i'll show that one, whats wrong with this first one? working display click [code:1:e515321317] private void Button1_Click(object sender, System.EventArgs e) { string ConnectString =@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\salesleadmgmt.mdb"; OleDbConnection CustConnection = new OleDbConnection(ConnectString); CustConnection.Open(); DataSet CustSet = new DataSet(); OleDbDataAdapter CustAdapt = new OleDbDataAdapter("SELECT * FROM CustInfo", CustConnection); CustAdapt.Fill(CustSet); CustConnection.Close(); CustGrid.DataSource = CustSet; CustGrid.DataBind(); } [/code:1:e515321317] *-----------------------* Posted at: www.GroupSrv.com *-----------------------* Tag: restrict size of datatable Tag: 97727
  - 8
    - Moving CurrencyManager to specific row in Data Source I have a series of user-bound controls that are associated with a dataset, and need to be able to move the displayed data row in one control based on events in another. I have all of the controls bound to the same dataset on my form, and the navigation controls are passing a complete DataRow object in the event arguments. I know I need to get the CurrencyManager for the binding context, and set it's Position, but it's not working properly. The code I am currently using follows (Names have been changed to protect the innocent :). I have a strongly typed dataset called "TypedDataSet" with a table called "Table" in it. The table has an "ID" column which is the primary key. The data is being read into the dataset from an XML document. /**********/ TypedDataSet ds = _dataSet; TypedDataSet.TableRow row = e.nodeData as TypedDataSet.TableRow; int pos = ds.Table.DefaultView.Find(row.ID); CurrencyManager cm = BindingContext[ds, "Table"] as CurrencyManager; cm.Position = pos; /**********/ When I run this code, the data-bound controls do move to a new row. But it's always the wrong row. The problem appears to be that the DataView has the rows sorted differently than the DataTable. I have tried explicitly sorting by primary key (DefaultView.Sort = "ID ASC") as well as using the default (DefaultView.ApplyDefaultSort = true), but the results are identical. The DataTable.Find() method doesn't appear to be useful, since that returns a DataRow -- that I already have -- but I have no way to move the CurrencyManager to a specific data row object, only to a specific position. I'm sure there's some .NET property or method I'm missing but I can't seem to find it. Any ideas? --Mike 's/not.//g' to email me. Tag: restrict size of datatable Tag: 97726
  - 9
    - VB.Net (ADO): Thoughts on Copying 7 tables to 1 table 2 Part Question: I've a VB.net application where I read information from 7 Tables in a MS Access Db. There is a performance hit when this happens (about a 1-2 second hit per table). So I'm going to combine the relavent parts of each table into a single table in a "new' MDB file. I've experimented with this and found that the best way that I can see is to gather the information from each table into an Array and then dump that info into my new MDB file. I want all the 7 tables starting with the FIRST row in the new table. I first get the number of rows for a table, re-dim my array and gather the info. Then I repeat for the next table - I compare table row lengths to make sure my Array does not get smaller than the maximum table row found. All appears to work fine. Question #1: Can someone suggest a better way (if there is one) to take the "selected" columns from a single MDB (having 7 tables) into a new single MDB with 1 table. I tried reading each of the 7 tables into my new MDB, but this doesn't work as each table adds new rows past the previous table information. Question #2: Every now and then, I find one of my columns (about mid way down) has a bunch of NULL cells when the data should be continuous in the column. I run the application again (maybe a third time) and all is OK. All the other columns are OK (and the code is similar). A head scratcher for me. Any thoughts? Thanks in advance. Regards, Bruce Tag: restrict size of datatable Tag: 97725
  - 10
    - Access datetime format problem? I'm generating 2 datetime values inside a VB.Net program say datetime1 and datetime2. datetime1 is retrieved from an Access database from a column of datetime type. datetime2 is generated internally by the program. I want to test the condition: If datetime1=datetime2 then CodeBlock But CodeBlock never seems to get called even when I'm sure that datetime1 and datetime2 are identical. If I look at the identical datetime1/2 values I do see a difference in formatting in the time part: datetime1 (from Access) shows time as eg 11:59:59 PM datetime1 (internally set) shows time as eg 23:59:59 Question is: Am I trying to test identity of the datetime values in the wrong way or is the problem the difference in time formatting? If the latter then is there some way I can set the database to return datetime values using 24 hour clock times? TIA JGD Tag: restrict size of datatable Tag: 97722
  - 11
    - problem with having a delete in database propagate to a bound data Hi, I have notice something with the datagrid update i have an application which is updated from multiple sources when one user deletes a row on the datagrid, the delete propgates back to the database. I see it is gone, from the database by using the SQL query analyzer. but, when other users app calls it's fill command to refresh it's datatable, It sees the already deleted row. what am i doing wrong? Tag: restrict size of datatable Tag: 97721
  - 12
    - Access Query Problem This is a multi-part message in MIME format. ------=_NextPart_000_005D_01C4B74E.88CF5E00 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Good Morning, Got some good advice yesterday, but still running into issues. I am = trying to query an Access db once a user selects a month/yr from a = dropdown list. =20 Below is summary of my code: =20 Dim CurrentStrMth as String=20 Dim CurrentEndMth as String if drpdate.selecteditem.text =3D "JANUARY 04" then=20 CurrentStrMth =3D "#1/1/2004#"=20 CurrentEndMth =3D "#1/31/2004#" =20 When I run the query below without passing any variables it works fine: cmdSelect1 =3D New OleDbCommand("SELECT Count(*) from db WHERE [Submit = Date/Time]>=3D#1/1/2004# and [Submit = Date/Time]<=3D#1/31/2004#",dbconnection) However, when I try to incorporate the string variables, as noted below, = into the query I get "Character constant must contain exactly one = character". I can't find anything on this on the web!=20 cmdSelect1 =3D New OleDbCommand("Select Count(*) from db where [submit = date/time] >=3D '"CurrentStrMth"' and [submit date/time] <=3D = '"CurrentEndMth"'", dbconnection) If someone could assist me further with this issue, it would be greatly = appreciated? Thanks in advance! MKC ------=_NextPart_000_005D_01C4B74E.88CF5E00 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.2800.1476" name=3DGENERATOR> <STYLE></STYLE> </HEAD> <BODY bgColor=3D#ffffff> <DIV><FONT face=3DArial size=3D2>Good Morning,</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>Got some good advice yesterday, but = still running=20 into issues.  I am trying to query an Access db once a user selects = a=20 month/yr from a dropdown list.  </FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>Below is summary of my = code:</FONT></DIV> <DIV> </DIV> <DIV>Dim CurrentStrMth as String </DIV> <DIV>Dim CurrentEndMth as String</DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV>if drpdate.selecteditem.text =3D "JANUARY 04" then </DIV> <DIV>CurrentStrMth =3D "#1/1/2004#" </DIV> <DIV>CurrentEndMth =3D "#1/31/2004#"  </DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2> <DIV><FONT face=3DArial size=3D2>When I run the query below without = passing any=20 variables it works fine:</FONT></DIV> <DIV> <DIV>cmdSelect1 =3D New OleDbCommand("SELECT Count(*) from db WHERE = [Submit=20 Date/Time]>=3D#1/1/2004# and [Submit=20 Date/Time]<=3D#1/31/2004#",dbconnection)</DIV></DIV></FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>However, when I try to incorporate the = string=20 variables, as noted below, into the query I get "Character constant must = contain=20 exactly one character".  I can't find anything on this on the=20 web! </FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV>cmdSelect1 =3D New OleDbCommand("Select Count(*) from db where = [submit=20 date/time] >=3D '"CurrentStrMth"' and [submit date/time] <=3D=20 '"CurrentEndMth"'", dbconnection)</DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV> <DIV><FONT face=3DArial size=3D2>If someone could assist me further = with this=20 issue, it would be greatly appreciated?</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>Thanks in advance!</FONT></DIV> <DIV><FONT face=3DArial size=3D2>MKC</FONT></DIV></DIV></BODY></HTML> ------=_NextPart_000_005D_01C4B74E.88CF5E00-- Tag: restrict size of datatable Tag: 97720
  - 13
    - ExecuteNonReader hangs Hello everyone, my webservice read from (and write to) Oracle queues. It uses the Oracle Data Provider downloaded from Oracle site (Oracle10g Data Provider for .NET 10.1.0.2.0). Webservice calls a stored procedure that executes the read operation. Sometimes the ExecuteNonReader() hangs (I'd like to set a timeout on command, but OracleCommand does not support this option). Consequently the webservice fails for timeout. I tested the same class without using webservice and it doesn't hangs. Do anyone know what is the problem? Is it a bug? I'am using .NET 1.1 on Windows 2003. Thanks, Gionni Tag: restrict size of datatable Tag: 97717
  - 14
    - How to you page and sort large dataset result in datagrid? In ASPNET 1.1 Is there a good way to page/sort VERY large Result set in datagrid > > > > > > P. Wilson blog wrote: " Here's a stored procedure that I use for sorting and paging large recordsets in SQL Server, as opposed to using the more common and terribly inefficient entire dataset approach in .NET. It certainly doesn't matter much in the little classroom examples of a few hundred, or even thousands, of records, but working with larger recordsets with datasets is just bad. This is even more true with ASP.NET, since the entire dataset is usually saved in viewstate, and since the web server is doing the processing for many users. This stored procedure is certainly not the only way to do sorting and paging in SQL Server, but it is probably the most efficient, although proper indexing is still required to avoid table scans. Note that this does not use temporary tables, which are convenient but not as optimal -- this also means you could modify this to be dynamic SQL for Access or other databases! The parameters are the name of the table, the name of the primary key (necessary for the little bit of optimization included), the name of the sort field (or sort fields, with or without ASC/DESC), and the page size (number of records). It also allows optional parameters for the page index (defaults to 1st page) and a query filter (defaults to null) so you can sort and page through filtered records only! Note that it returns two recordsets -- the first is the results you expect, and the second recordset is a scalar with the number of total pages so you can better define the GUI pager for a grid. This is relatively easy to integrate with the ASP.NET datagrid if you use custom paging and sorting, and it will minimize both your load on the server and the amount of data sent to the client's browser! By the way, this code is just modified from some I found on the net, and there are certainly some minor optimizations that can be done, like using different sql for the first page. " CREATE PROCEDURE GetSortedPage( @TableName VARCHAR(50), @PrimaryKey VARCHAR(25), @SortField VARCHAR(100), @PageSize INT, @PageIndex INT = 1, @QueryFilter VARCHAR(100) = NULL ) AS SET NOCOUNT ON DECLARE @SizeString AS VARCHAR(5) DECLARE @PrevString AS VARCHAR(5) SET @SizeString = CONVERT(VARCHAR, @PageSize) SET @PrevString = CONVERT(VARCHAR, @PageSize * (@PageIndex - 1)) IF @QueryFilter IS NULL OR @QueryFilter = '' BEGIN EXEC( 'SELECT * FROM ' + @TableName + ' WHERE ' + @PrimaryKey + ' IN (SELECT TOP ' + @SizeString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' WHERE ' + @PrimaryKey + ' NOT IN (SELECT TOP ' + @PrevString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' ORDER BY ' + @SortField + ') ORDER BY ' + @SortField + ') ORDER BY ' + @SortField ) EXEC('SELECT (COUNT(*) - 1)/' + @SizeString + ' + 1 AS PageCount FROM ' + @TableName) END ELSE BEGIN EXEC( 'SELECT * FROM ' + @TableName + ' WHERE ' + @PrimaryKey + ' IN (SELECT TOP ' + @SizeString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' WHERE ' + @QueryFilter + ' AND ' + @PrimaryKey + ' NOT IN (SELECT TOP ' + @PrevString + ' ' + @PrimaryKey + ' FROM ' + @TableName + ' WHERE ' + @QueryFilter + ' ORDER BY ' + @SortField + ') ORDER BY ' + @SortField + ') ORDER BY ' + @SortField ) EXEC('SELECT (COUNT(*) - 1)/' + @SizeString + ' + 1 AS PageCount FROM ' + @TableName + ' WHERE ' + @QueryFilter) END RETURN 0 GO posted on Friday, October 10, 2003 9:04 AM Comments >>>> COMMENTS # re: Sorting and Paging Recordsets in SQL Server Darrell Posted @ 10/10/2003 10:15 AM * Just taking a quick look, you could change the "SELECT Count(*)" to "SELECT Count(@PrimaryKey)" and that should give you a speed boost. Reducing the number of columns in the select statement always speeds things up, even for aggregate operations like Count. Very interesting though; I will have to see how well it works for complex queries. # re: Sorting and Paging Recordsets in SQL Server Torbjörn Axelsson Posted @ 10/27/2003 11:14 AM Very interesting! Right now I'm using a solution based on creating a temporary table in the stored procedure for selecting rows from a table containing over a million rows. I will test to implement this solution and see if it boosts performance, which would be very welcome :) In my project I will have to make an inner join to retrieve User names from another table, but since I only have to do this on the outermost SELECT-statement (after TableName), and I have a good indexed structure, that shouldn't have a big impact on performance. Any thoughts? * # Recordset sorting in SQL Server Jamie Jones Posted @ 11/16/2003 11:05 AM * # O/R Mappers: Simple Database Features ? Paul Wilson's .NET Blog Posted @ 1/9/2004 3:54 PM * # re: Sorting and Paging Recordsets in SQL Server David Portas Posted @ 1/23/2004 5:42 PM See also Aaron Bertrand's comprehensive survey of techniques for paging a result set in SQLServer: http://www.aspfaq.com/show.asp?id=2120 * # re: Sorting and Paging Recordsets in SQL Server HELP! Posted @ 2/11/2004 7:34 PM what's the asp supposed to look like? * # re: Sorting and Paging Recordsets in SQL Server Mark Posted @ 3/6/2004 8:49 AM I was wondering if you had an example of using this code with an asp.net datagrid, complete with paging and sorting. email: mwpat@rogers.com * # re: Sorting and Paging Recordsets in SQL Server Paul Wilson Posted @ 3/6/2004 11:28 AM I use it internally at work on a project, but that's not an example I can share. I also use this, but wrapped up in my WilsonORMapper, on my site, but there I use repeaters since I don't like datagrids. There are lots of other examples that show you the asp.net code that you want, so I haven't bothered to recreate one yet again -- the only thing I'm pointing out is how to set up the stored procedure in a better way. * # re: Sorting and Paging Recordsets in SQL Server babu Posted @ 4/2/2004 5:26 AM quite helpful - babu * # re: Sorting and Paging Recordsets in SQL Server Bob Posted @ 4/5/2004 5:08 AM Sorry, I'm new to sql. Why the extra "SELECT * FROM ~ IN ~" statement? Why not just do a "SELECT TOP ' + @SizeString + ' * FROM ..." and make the second select the top select (if you see what I mean). Wouldn't this have the same affect? * # re: Sorting and Paging Recordsets in SQL Server SANDESH Posted @ 4/6/2004 3:25 AM GOOD ONE * # re: Sorting and Paging Recordsets in SQL Server theintrepidfox@hotmail.com Posted @ 4/17/2004 7:47 PM Hi! Excellent fantastic work! You've made my day as I was experiencing with paging scripts but the only solution I came up with supporting DESC and filtering doesn't perform very well. I probably would have spend the next days coming up with something like yours. Thanks very much! * # re: Sorting and Paging Recordsets in SQL Server AcidJazz Posted @ 4/29/2004 8:11 PM Great stuff! I spent last couple of hours looking for something similar and I could see this proc working very well in my project. Thanks! * # re: Sorting and Paging Recordsets in SQL Server senkwe Posted @ 7/8/2004 9:34 AM Has anybody tried this for tables with a few million rows yet? Thanks. * # re: Sorting and Paging Recordsets in SQL Server Paul Wilson Posted @ 7/8/2004 9:42 AM I actually just got done reading http://codeproject.com/aspnet/paginglarge.asp -- and I may switch what I use. I've never heard any complaints, but it does look like there are better solutions. * # re: Sorting and Paging Recordsets in SQL Server Jonny Posted @ 7/21/2004 7:29 AM I believe I read somewhere the sp_executesql will compile the dynamic query and create an execution plan for it. This may speed this up a bit if the same exact query is executed. * # Taking the value of PageCount Resa Posted @ 7/27/2004 1:59 AM Excuse me but i have never used a stored procedure before, and although your code works great (thanks a lot by the way :)) i want to ask how can i get the PageCount value from the second table generated by ur stored procedure? Thank you very much in advance :) * # re: Sorting and Paging Recordsets in SQL Server Paul Wilson Posted @ 7/27/2004 5:59 AM Assuming you're using .NET and a DataReader then the NextResult method is what you want. If you are using a .NET DataSet then you will look at the second table, i.e. .Tables[1]. * # re: Sorting and Paging Recordsets in SQL Server Resa Posted @ 8/2/2004 2:06 AM I'm using sql server, and the NextResult method doesn't work, is there another way, i mean using sql server? thanks Tag: restrict size of datatable Tag: 97709
  - 15
    - Linebreaks in SQLCommand.CommandText (C#) Hi, From a C# application I read the SQL text from a text file. The text file contains line breaks that need to be conserved. The text file is read by a streamreader and added to a StringBuilder object. Each line in the text file is read, one at the time. After reading a line, I add a carriage return/linefeed by System.Enviroment.NewLine. But carriage returns / linefeeds are added to the StringBuilder as "/r/n". When I add the content of the StringBuilder object to the SqlCommand.CommandText property and run the query, the SQL fails. I have done a similar thing in VB. In VB I added carriage returns/linefeed by the "vbCrLf" constant. This worked fine. Line breaks were added and maintained when passing the SQL to the SqlCommand.CommandText property - no codes were "visible". I hope someone knows how to handle this in C#. Thanks, Dorte Tag: restrict size of datatable Tag: 97705
  - 16
    - insert an image to sql server avi_livni@hotmail.com in msn messenger hi i try to insert an image to sqlserver i do like this first i wrote a stored procedure in my sql server in database="new_home" (not in master db) : CREATE PROCEDURE sp_textcopy ( @srvname varchar (30), @login varchar (30), @password varchar (30), @dbname varchar (30), @tbname varchar (30), @colname varchar (30), @filename varchar (30), @whereclause varchar (40), @direction char(1)) AS DECLARE @exec_str varchar (255) SELECT @exec_str = 'textcopy /S ' + @srvname + ' /U ' + @login + ' /P ' + @password + ' /D ' + @dbname + ' /T ' + @tbname + ' /C ' + @colname + ' /W "' + @whereclause + '" /F ' + @filename + ' /' + @direction EXEC master..xp_cmdshell @exec_str this stored procedure i add to : db="new_home" table="image" now i only try to give parmeters in asp.net in oledb , i saw the example : sp_textcopy@srvname = 'ServerName', @login = 'Login', @password = 'Password', @dbname = 'pubs', @tbname = 'pub_info', @colname = 'logo', @filename = 'c:\picture.bmp', @whereclause = " WHERE pub_id='0736' ", @direction = 'I' but i do not now where i write this code and how i write it asp.net instead i write this, but i didnot see any new image in my database : <%@ Import Namespace="System.Data.SQLClient" %> <%@ Import Namespace="System.Data.OLEDB" %> <%@ Page Language="VB" Debug="true" %> <script language="VB" runat="server"> sub Page_Load(obj as Object, e as EventArgs) 'set up connection dim Conn as new OleDbConnection _ ("Provider=SQLOLEDB;" & _ "Data Source=D:\Program Files\Microsoft SQL Server\MSSQL$LOVE\Data\new_home_Data.MDF") dim obdCmd as OleDbCommand = new OleDbCommand("sp_textcopy",Conn) obdCmd.CommandType = CommandType.StoredProcedure dim objParam as OleDbParameter objParam =obdCmd.Parameters.Add("@srvname", OleDbType.Char) objParam.Direction=ParameterDirection.Input objParam.Value="AAA\LOVE" objParam =obdCmd.Parameters.Add("@login", OleDbType.Char) objParam.Direction=ParameterDirection.Input objParam.Value="" objParam =obdCmd.Parameters.Add("@password", OleDbType.Char) objParam.Direction=ParameterDirection.Input objParam.Value="" objParam =obdCmd.Parameters.Add("@login", OleDbType.Char) objParam.Direction=ParameterDirection.Input objParam.Value="" objParam =obdCmd.Parameters.Add("@dbname", OleDbType.Char) objParam.Direction=ParameterDirection.Input objParam.Value="new_home" objParam =obdCmd.Parameters.Add("@ltbname", OleDbType.Char) objParam.Direction=ParameterDirection.Input objParam.Value="image" objParam =obdCmd.Parameters.Add("@colname", OleDbType.Char) objParam.Direction=ParameterDirection.Input objParam.Value="image" objParam =obdCmd.Parameters.Add("@filename", OleDbType.Char) objParam.Direction=ParameterDirection.Input objParam.Value="c\10.jpg" objParam =obdCmd.Parameters.Add("@whereclause", OleDbType.Char) objParam.Direction=ParameterDirection.Input objParam.Value="where id=1" objParam =obdCmd.Parameters.Add("@direction", OleDbType.Char) objParam.Direction=ParameterDirection.Input objParam.Value="i" try obdCmd.Connection.Open() dim objReader as OleDbDataReader objReader = obdCmd.ExecuteReader catch ex as OleDbException response.write("Error retriving Data") obdCmd.Connection.Close() end try end sub </script> *-----------------------* Posted at: www.GroupSrv.com *-----------------------* Tag: restrict size of datatable Tag: 97704
  - 17
    - How to clear and refill a dataset dragged from the toolbox How to clear and refill a dataset dragged from the toolbox If I generate a dataset from a dataadapter I can ds.clear da.fill(ds, "Table") How do I do it otherwise? Thanks, Dennis Tag: restrict size of datatable Tag: 97697
  - 18
    - Can't create a new dataset from the toolbox Can't create a new dataset from the toolbox. In the past, on the advice of MVPs, I've created datasets by dragging the dataset component from the datatab of the toolbox onto the form. The first time it created dataset1, a dataset that did not exist before. The second time it created a dataset2. Today I dragged a dataset on a new form. It only offered current datasets, not a default of a new dataset. When I typed in tfsNet2.dataset3 and clicked OK, I got an error message Visual Studio cannot create an instance of TFSMet2.Dataset3. Confirm the class name is a valid dataset class. What happened? Please help. Dennis Tag: restrict size of datatable Tag: 97695
  - 19
    - Q: Copying a table from one database to another Hi Can anybody tell me the easiest way in ADO.NET to copy a table from one Access database to another Access Database? Some example code would be useful. Thanks in advance Geoff Tag: restrict size of datatable Tag: 97694
  - 20
    - SQAL question Sorry, but I didn't find a group for SQL questions. May be someone can answer here. In my app I want to reflect a hirarchy in a tree view. Therefore in the database I have a table like this: HirachyId (primary key) ParentHirarchyId (key to reflect the arent entry) CategoryId (a foreign key into a category table) Content may be like the following (0 is root hirarchy) 1 0 10 2 0 11 3 1 16 4 1 8 5 3 27 6 3 28 0 +-- 1 | +-- 3 | | +-- 5 | | +-- 6 | | | +-- 4 | +-- 2 Now I want a SQL-select statement which "recursive" gives me all records for a given node, i.e., when the node is "1" I want to get the records 1 0 10 3 1 16 4 1 8 5 3 27 6 3 28 Can this be done with a SQL-statement? -- Sleepless in Berlin Wernfried Tag: restrict size of datatable Tag: 97693
  - 21
    - finding records and displaying it in grid Hi, Earlier i asked a question regarding finding a record through ado.net. I found a solution for it through dataview. I have another question now. Supposing i have a table, 'contacts' whose fileds are like this : Contactid(autoname), customerid(FK from another table), contactname(char)... Here customerID is a foreignkey from tables 'Customers'. Since names might repeat in this table, how do i list the names when a search is made on this name. I want to display the fields displayed in row. I tried with findrowview, but could not succeed. Would someone help me pls. K E Senthil Kumar Tag: restrict size of datatable Tag: 97689
  - 22
    - How te reference to a datagrid in a class? Hi I've a DLL project with a form in it. At that form i want to add a datagrid, but datagrid is not recognized if i type dim db as new Datagrid. In a windows forms project it works correct, but not in the DLL project. What do i wrong????? Peter Tag: restrict size of datatable Tag: 97686
  - 23
    - Convert <table> from HTML text file to datatable I'm just wondering if anyone have come across a code that reads an html file,search for <table> tag and convert its contents into a datatable and save it into a dataset.If there are few html tables,then each will be converted into a datatable and added into the same dataset. thanks. Tag: restrict size of datatable Tag: 97678
  - 24
    - Passing Parameters through a linked server? Hello I have a stored proc: CREATE PROCEDURE dbo.sp_test @IN int, @ID int OUTPUT AS begin SET NOCOUNT ON Set @ID=12345 + @IN end GO I'm executing this through a linked server. The exception returned is: Could not execute procedure 'sp_test' on remote server 'QAPS2Remote'.\r\n[OLE/DB provider returned message: Parameter type cannot be determined for at least one variant parameter.] The literal query being run is: declare @P1 int set @P1=NULL exec QAPS2Remote.HRXMLCollector_prod.dbo.sp_test @IN = 1, @ID = @P1 output select @P1 How do I declare the parameters using C#? The code I'm using is: dbCommand.Connection = conn; dbCommand.CommandType = CommandType.StoredProcedure; dbCommand.CommandText = "QAPS2Remote.HRXMLCollector_prod.dbo.sp_test"; SqlParameter sqlParameterIn = dbCommand.Parameters.Add("@IN", SqlDbType.Int); sqlParameterIn.Direction = ParameterDirection.Input; sqlParameterIn.Value = 1; SqlParameter sqlParameterOut = dbCommand.Parameters.Add("@ID",SqlDbType.Int); sqlParameterOut.Direction = ParameterDirection.Output; dbCommand.ExecuteNonQuery(); Thanks in advance. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! Tag: restrict size of datatable Tag: 97670
  - 25
    - Paging a DataGrid or similar control...? I have a windows application that returns 10s of thousands of rows to a DataGrid (for all intents and purposes you can assume that it's a DataGrid, I'm actually using the Infragistics UltraWinGrid though). Due to the ever growing amount of data I want to implement some sort of record paging. There are a lot of examples for ASP.Net but not many for windows applications. I am planning to use some of the techniques used for ASP.Net, but I was hoping someone out there may have a few suggestions specifically for a windows application. Also, I am using MSDE as my database backend. Thanks Tag: restrict size of datatable Tag: 97661

hi all.

i am developing a database application with sqlserver2000 where users
can enter search strings manually.
So it is possible that the result gets really big, to big for a website.
is it possible to restrict a datatable to a defined number of rows, or
bytes?
or maybe there is s.th. in t-sql?

thangs for help.
daniel

Top

Re: restrict size of datatable by Earl

Earl
Mon Oct 25 07:44:59 CDT 2004

Use either SELECT TOP and/or SET ROWCOUNT in SQL2K.

"Daniel" <daniel.maier@zdv.uni-tuebingen.de> wrote in message
news:clib01$k6h$1@newsserv.zdv.uni-tuebingen.de...
> hi all.
>
> i am developing a database application with sqlserver2000 where users can
> enter search strings manually.
> So it is possible that the result gets really big, to big for a website.
> is it possible to restrict a datatable to a defined number of rows, or
> bytes?
> or maybe there is s.th. in t-sql?
>
> thangs for help.
> daniel

Top