Dynamic SQL Generation Error

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
    - Remove DataTable Entries I have two DataTables, A and B. Both have the primary key "CID". How do I remove all entries in Table A from Table B (with the same PK)? Tag: Dynamic SQL Generation Error Tag: 61253
  - 2
    - Problem With DataRow Rowstate Hello Everyone I've got a problem when I set a value into a DataRow X after an Acceptchanges() on DataTable that contains X. After this operation DataRow.Rowstate is Unchanged... strange... it must be Modified .. isn't it? I you see the following code (just "little boring"...) Public Class testclass Protected mDataTable As Data.DataTable Protected mRow As Data.DataRow Protected mDataAdapter As SqlClient.SqlDataAdapter Protected mConn As SqlClient.SqlConnection '---------------------------- ' New '---------------------------- Public Sub New() mDataTable = New Data.DataTable() mConn = New SqlClient.SqlConnection("Data Source=test-srv;Integrated Security=SSPI;Initial Catalog=Test") mDataAdapter = New SqlClient.SqlDataAdapter("SELECT ID, Descrizione FROM Table", mConn) mDataAdapter.FillSchema(mDataTable, SchemaType.Mapped) mRow = mDataTable.NewRow() End Sub Public Sub Insert() If mRow.RowState <> DataRowState.Detached Then Exit Sub mRow(0) = "c51ac21a-05bc-475c-ad75-1affd8f980c3" mRow(1) = "Hello!!" '-------------------------------------------- ' Delete evenually records from DataTable '-------------------------------------------- mDataTable.Clear() '----------------------------------------- ' AddRow '----------------------------------------- mDataTable.Rows.Add(mRow) Debug.WriteLine(mRow.RowState) '--------------------------- 'Accept '---------------------------- mDataTable.AcceptChanges() Debug.WriteLine(mRow(1, DataRowVersion.Original) & " " & mRow(1, DataRowVersion.Current) & " " & mRow.RowState) '---------------------------------------- ' Try to change value BUT!!!!! '---------------------------------------- mRow(1) = "Foo2" Debug.WriteLine(mRow(1, DataRowVersion.Original) & " " & mRow(1, DataRowVersion.Current) & " " & mRow.RowState) mRow(1) = "Foo3" Debug.WriteLine(mRow(1, DataRowVersion.Original) & " " & mRow(1, DataRowVersion.Current) & " " & mRow.RowState) End Sub AND CLIENT USER... Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim trow As New testclass() trow.Insert() End Sub End Class You can see that after AcceptChanges is all ok... (Rowstate= Unchanged) but when I set: mRow(1) = "Foo2" mRow.rowstate remains Unchanged.... Please Help me!! Marco Gatto Tag: Dynamic SQL Generation Error Tag: 61250
  - 3
    - How to programmatically post proposed values for a row in windows form datagrid? Hello! Would anyone kindly teach me how to programmatically post proposed values for a row in windows form datagrid? The reason that I raise this question is -- whenever I call OdbcDataAdpter.Update() method to apply updates and inserts the user performed on a datagrid, I would always lose the modifications on the row that is last edited. I have to ask my users to manually click another row to post all proposed values to the dataset. By the way, this code fails to be effective: this.BindingContex[mydataset.mytable].EndCurrentEdit(); //this = the form (My datagrid is bound to "mydataset.mytable", so I'm NOT using the wrong CurrencyManager.) TIA, XIA Chengbin Tag: Dynamic SQL Generation Error Tag: 61249
  - 4
    - Getting ID from SQL after inserting Hi, I have a datatable with 1 row. It consists of columns: name, date, time and value. I use dataadapter to insert these data to a SQL2000 table. Structure of the table is almost the same. It contains all these 4 columns plus one more which is a table PrimaryKey and IdentityColumn - autoincrementing integer. After SqlDataAdapter.Update I would like to get that value of PrimaryKey assigned by sql2000 to my new row in sql table. How can I do it ? Thank in advance! Przemo Tag: Dynamic SQL Generation Error Tag: 61244
  - 5
    - Info: "General network error" exception when calling a stored procedure Hi, I was experiencing sporadic exceptions when calling stored procedures (e.g. with SqlCommand.ExecuteNonQuery). The exception message was either one of the following two: - General network error. Check your network documentation - A severe error occurred on the current command. The results, if any, should be discarded In addition, in the event log of the server running the SQL server, the following message could be found (from the SQL server): - Error: 17805, Severity: 20, State: 3 Invalid buffer received from client. After some investigation, I found that the errors were caused by calling stored procedures with wrong parameters. I my case I was for example calling ExecuteNonQuery with a SqlParameter whose SqlDbType property was set to NVarChar, while the stored procedure expected a VarChar. These errors started to appear after installing SQL server SP3a and the cumulative patch from July 2003. We did not have the errors previously (with SP2). Because I have seen posts from other people with similar problems, I thought I'd share what I have found. Best regards, Martin Bischoff Tag: Dynamic SQL Generation Error Tag: 61243
  - 6
    - get the position of a specific item using binding context Could you tell me more details about how to get the position of a specific item using binding context? >-----Original Message----- >The binding context is your only option as it will provide you with the >requested functionality. As you state yourself, it's down to the "missing" >cursors, but you can obviously implement this yourself if you really need >it. What's the problem with the binding context? > >-- >Carsten Thomsen >Enterprise Development with Visual Studio .NET, UML, and MSF >http://www.apress.com/book/bookDisplay.html?bID=105 >"Haisheng Lin" <haishenglin@hotmail.com> wrote in message >news:042501c354cc$5263a730$a101280a@phx.gbl... >> >> In vb6, I use adodc and bind the fields to textbox.text. >> If I need to find the specific item, I just use >> adodc.recordset.find, then the cursor will move to >> the first occurence and textbox.text will show the >> the item you need. But in ado.net, there is no cursor in >> dataset. You have to use the position in bindcontext. >> If you just need to go through items one by one, that is >> ok, you just add one or subtract one from the position. >> But if you need to find an item in the middle, >> how can you know the position you need to move to? >> Do I need to implement those search algorithems myself? >> >> >> >> >-----Original Message----- >> >There might be a functionality to do this, but I have >> seen people work >> >around very easily by putting another column >> called "rowindex" which >> >keeps the index stored there. [inefficient, but works] >> > >> >Rahul Singh >> > >> >anant systems, inc. | making information work for you >> > >> > anantsystems.net | ioserver.net [Developer and >> Business .NET Hosting] >> > >> > >> > >> >"Haisheng Lin" <haishenglin@hotmail.com> wrote in message >> >news:26aa01c353ff$bf1c1380$a001280a@phx.gbl... >> >> I have a table with two columns: >> >> prod_id (primary key), prod_name. >> >> >> >> I filled my dataset with this table and bind >> >> two textboxs to the two columns. >> >> I am trying to search a specific product >> >> using its prod_name and I want to know >> >> the position of this row in datatable.rows. >> >> >> >> How can I do it? >> >> >> >> Thank you! >> >> >> >> >> >> >> >> >> > >> > >> >. >> > > > >. > Tag: Dynamic SQL Generation Error Tag: 61229
  - 7
    - Connections (Audit Login/Logout) I have a question on when and where we should create connections to SQL. I have a base class that handles all data access and dishes up connections etc as needed. I initially had a class level connection object that all general dataacess methods used to execute against. This introduced a problem when using transactions and executing more than one on a connetion at a time. I over cone this by creating a new connection each time I exeute a method. When I do a trace on SQL I get many 'Audit login' events occuring as the connections are being created. When I log out of my app and the connections are being disposed I get many, many, many audit logouts. Is it normal to create a new connection every time it is needed or is it better to try and reuse an existing connection object if possible?. Maybe I can use IF @@TRANCOUNT = 0 then use connection else create new one? Thanks Devron Tag: Dynamic SQL Generation Error Tag: 61227
  - 8
    - How to refresh DataGrid to display latest dataset data?? Hi All, i 'm having a problem of refreshing DataGrid on Windows form.. i have bind a dataset to datagrid .. which works perfectly in the beginning but once i update the database with executing sql command and retrieve dataset data again... the datagrid will not refresh... it still display old data.... i tried to set datagrid.datasource = nothing and set dataset bindings again to datagrid... it still showing old data.... does anyone know how to solve this problem and why it happens??.... please let me know... Thanks All, Ivan Tag: Dynamic SQL Generation Error Tag: 61226
  - 9
    - DataTable.rows.find Private Sub FindInMultiPKey(ByVal myTable As DataTable) Dim foundRow As DataRow ' Create an array for the key values to find. Dim findTheseVals(2) As Object ' Set the values of the keys to find. findTheseVals(0) = "John" findTheseVals(1) = "Smith" findTheseVals(2) = "5 Main St." foundRow = myTable.Rows.Find(findTheseVals) ' Display column 1 of the found row. If Not (foundRow Is Nothing) Then Console.WriteLine(foundRow(1).ToString()) End If End Sub I find the example in msdn. I wonder why the array findTheseVals declared dimension as 2 instead of 3. There are 3 key elements but the size of the array is only 2?? Tag: Dynamic SQL Generation Error Tag: 61224
  - 10
    - Bound DataSet not showing changes on Web Form I have a WebForm with databound WebControls (textboxes). I added a typed DataSet being filled via a class object that provides the data access. In my page load event I fill the form's dataset: FormDataSet.Merge(oDBClass.GetDataSet() ) then DataBind() Works great - fills the data into the form for the client as expected. I then type data into the web form and click submit. In the Submit button's code I run GetXML on the bound dataset and I don't see the change??? I am assuming that this bound dataset would contain the result of the user operations on the form after a submit. Am I wrong and perhaps I need to loop through the webcontrols and manually stuff the data into the Dataset prior to sending it to the database? Any ideas appreciated. Thanks. Jack Tag: Dynamic SQL Generation Error Tag: 61222
  - 11
    - VB.NET and Access 2002 Hi, I am creating a GUI for my access database in VB.NET. I have created the Data connection, DataAdapter, DataSet and CommandBuilder in code. The program connects and reads data. When I use a find to get the current record I get an exception stating that my table has no primary key. I have defined the yourID column as unique and primary key. I am not sure why VB doesn't understand that a primary key exists. Any Ideas? Revelent Code ... (don't laugh I'm new at this =) ) Public Class Form1 Inherits System.Windows.Forms.Form Dim frmEnterPay As New Form2b() 'database connection code Dim objConnection As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=judgment.mdb") Dim objJudgmentinfoDA As New OleDb.OleDbDataAdapter("Select * from judgmentinfo", objConnection) Dim objJudgmentinfoCB As New OleDb.OleDbCommandBuilder(objJudgmentinfoDA) Dim objDataSet As New DataSet() ? Public Sub fillDS() 'clear dataset objDataSet.Clear() 'fill schema objJudgmentinfoDA.FillSchema(objDataSet, SchemaType.Source, "judgmetninfo") 'fill dataset from judgmentinfo table objJudgmentinfoDA.Fill(objDataSet, "judgmentinfo") 'clear combo box cboDbtr.Items.Clear() 'loop through each row, adding the debtors ID to the combo box Dim i As Integer, strCurrentID As String For i = 1 To objDataSet.Tables("judgmentinfo").Rows.Count strCurrentID = objDataSet.Tables("judgmentinfo").Rows(i - 1).Item("yourID") cboDbtr.Items.Add(strCurrentID) Next 'select first item on the list cboDbtr.SelectedIndex = 0 End Sub Public Sub FillDetails() Dim objRow As DataRow 'throws exception on next line**** objRow = objDataSet.Tables("judgmentinfo").Rows.Find(cboDbtr.SelectedItem.ToStrin g) txtCdtName.Text = objRow.Item("lname") txtCdtLName.Text = objRow.Item("fname") *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! Tag: Dynamic SQL Generation Error Tag: 61212
  - 12
    - ASP.NET DataAdapter/CommandBuilder Syntax Error I am updating an Access 2000 database using ASP.NET. I pass a datatable to a function in a DLL that updates the database with a DataAdapter. When I run the program it crashes when updating and gives the error: "Syntax Error in INSERT INTO Statement". I'm wondering what I can do to fix it. Private Sub SaveWork() 'Create an instance of a class to perform DB operations.... Dim objWork as new Work() Dim dr As DataRow Dim dtUpdate As New DataTable() 'return template for blank row dtUpdate = objWork.GetAllWork(True) dr = dtUpdate.NewRow With dr 'set info in the datatable with values in textbox fields .Item("PersonID") = CInt(Session("PersonID")) .Item("ProjectID") = intProjectID .Item("Position") = txtPosition.Text.Trim .Item("Sector") = txtSector.Text.Trim .Item("Hours") = dblHours .Item("Description") = txtDescription.Text .Item("Date") = strDate .Item("Temp") = True End With dtUpdate.Rows.Add(dr) 'Adding the new record to the datatable objWork.SaveWork(dtUpdate, intProjectID) End Sub The function in the Work.vb class.... Public Function SaveWork(ByVal dtUpdate As DataTable, ByVal ProjectID As Integer) As Boolean OpenDB() Dim da As New OleDbDataAdapter("SELECT * FROM WorkLog", con) Dim cb As New OleDbCommandBuilder(da) da.InsertCommand = cb.GetInsertCommand da.Update(dtUpdate) '!!!!!!!!!!!!!!Crashes on this line!!!!!!!!!!!!!! CloseDB() Return True End Function Tag: Dynamic SQL Generation Error Tag: 61209
  - 13
    - Performing a one-pass reading of a database I am coding a sampling algorithm in a library and I got IO efficiency problems with "OleDbDataAdapter.Fill". Here is the situation, I have coded a method: void MySamplingAlgo(DataTable source, DataTable destination) And my algorithm is efficient: each DataRow of "source" is read only once and a few percentage of them are copied into "destination" (the selected samples). But my problem is the following: When I use OleDbAdapter (and OleCommandBuilder) to connect to a medium MS Access 2000 table (~100mo), the method OleDbAdapter.Fill first loads the entire table in RAM. For large tables, this is totally inefficient since my computer runs out of RAM and starts using virtual memory. I would like to know 1) How can I do (while using the abstract DataTable) in order to have the table read only once (the data should not be read outside "MySamplingAlgo") ? 2) How can I do to prevent the DotNet framework from loading in RAM the entire table that are accessed via "OleDbAdapter" ? Any help would be greatly appreciated, Joannes Vermorel ____________________ http://www.vermorel.com/ Tag: Dynamic SQL Generation Error Tag: 61202
  - 14
    - ADO.NET Schema Translation This is a multi-part message in MIME format. ------=_NextPart_000_0021_01C3550F.8F2E1C20 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I'm a bit new to XML Schema and am having a hard time finding = documentation that properly answers a question I have on how when a XML = schema is loaded into a dataset, it's parses certain elements. = Specifically, I'd like to have several elements of type vendor = encapsulated in a vendors elements. I'd like for the DataSet to parse = and have a single Vendor table w/ each element in the vendors element as = a row in that table. My question is, what do I need to do in my schema = to keep the vendors element from being defined as a seperate table? I'd = like for it to somehow be ignored by the parser. Thanks! - James ------=_NextPart_000_0021_01C3550F.8F2E1C20 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.1170" name=3DGENERATOR> <STYLE></STYLE> </HEAD> <BODY> <DIV><FONT face=3DTahoma size=3D2>I'm a bit new to XML Schema and am = having a hard=20 time finding documentation that properly answers a question I have = on how=20 when a XML schema is loaded into a dataset, it's parses certain = elements.=20 Specifically, I'd like to have several elements of type vendor = encapsulated in a=20 vendors elements. I'd like for the DataSet to parse and have a single = Vendor=20 table w/ each element in the vendors element as a row in that table. My = question=20 is, what do I need to do in my schema to keep the vendors element from = being=20 defined as a seperate table? I'd like for it to somehow be ignored by = the=20 parser. Thanks!</FONT></DIV> <DIV><FONT face=3DTahoma size=3D2></FONT> </DIV> <DIV><FONT face=3DTahoma size=3D2>- James</FONT></DIV></BODY></HTML> ------=_NextPart_000_0021_01C3550F.8F2E1C20-- Tag: Dynamic SQL Generation Error Tag: 61195
  - 15
    - SQL Insert statement Hello, I am trying to execute an sql insert command and I am getting an exception stating that a table field does not accept null values. While I do not provide a value for the field in the command, there is a default value defined for that column in the db. I have tried the exact same command directly in FoxPro and it works fine. Does anyone have any ideas as to why the provider is trying to insert a null value behind the scenes? I am working with Visual Studio .NET 2003, C#, Visual Foxpro 7.0 SP1. A code snippet and the exception message are below. Thanks Bill string cmdText = "INSERT INTO table1 (name, parametervalue ) VALUES ('test', 'test')" OleDbCommand cmd = new OleDbCommand( conn, cmdText ); conn.Open() cmd.ExecuteNonQuery(); System.Data.OleDb.OleDbException: Field IROW does not accept null values. at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr) at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteNonQuery() at DataAccess.ParametersDAC.AddRecord(DataRow record) in e:\dataaccess\parametersdac.cs:line 286 Tag: Dynamic SQL Generation Error Tag: 61190
  - 16
    - Primary Keys and DB Triggers I am using SQL Server 2000, VS.net 2003, C#. In my application, when adding a row to a dataset, a primary key is autogenerated. When I call dataadapter.Update (dataset.datatable); the primary key is updated to match what was assigned in the database. This works exactly as expected UNLESS there is a database trigger on the table which is being updated. If there is a trigger on the database, the primary key is NOT updated. If I call dataadapter.fill (dataset.datatable) again, then I have two rows containing the same data with different primary keys. This is a *huge* problem for me that I have not been able to overcome. Has anyone else experienced this? Is there a solution? Thanks, Nate Tag: Dynamic SQL Generation Error Tag: 61183
  - 17
    - Oracle's OLEDB: VARCHAR2 encoding bug / problem Hello, VarCHAR2 values are not returned as Unicode values after the first fetch in some queries that join tables using the latest non-beta Oracle 9i Client and Oracle's OLEDB provider. We perform the following query "SELECT A.First_Name, P.NT_USER_NAME FROM Account A, Person P WHERE P.Person_ID = A.Person_ID" using OleDbCommand's ExecuteReader() method. We loop through the results and display the field values in an edit box. The first fetch of rows (based on the connection string FetchSize property) is fine. Any rows after the initial fetch have VARCHAR2 encoding problems. The VarCHAR2 values are not returned as Unicode values. Each character is packed into a byte (i.e. character 1 is in low byte of string[0], character 2 is in high byte of string[0], character 3 is in low byte of string[1], etc...). Data is returned correctly when selecting these fields in separate queries without joining the tables. Also, some queries with joins work ok too, but the above one doesn't. Below is sample code to produce problem and table definitions. Sorry there is so much but I thought it may be useful. NOTE: The problem also happens with ODP.NET using the command's fetchsize property. using (OleDbConnection conn = new OleDbConnection ("Provider=OraOLEDB.Oracle;Password=xxx;Persist Security Info=True;User ID=CANDI;Data Source=yyy;Extended Properties=;OleDb.NET=true;FetchSize=5")) { conn.Open(); OleDbCommand cmd = new OleDbCommand("SELECT A.First_Name, P.NT_USER_NAME FROM Account A, Person P WHERE P.Person_ID = A.Person_ID", conn); OleDbDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { for (int i = 0; i < reader.FieldCount; i++) { string val; if (reader.IsDBNull(i)) val = ""; else val = reader.GetValue(i).ToString(); resultsTxt.AppendText(String.Format("{0} ", val)); } resultsTxt.AppendText("\r\n"); } } TABLE DEFINITIONS: CREATE TABLE ACCOUNT ( ACCOUNT_ID NUMBER(10) NOT NULL, FIRST_NAME VARCHAR2(20) NOT NULL, LAST_NAME VARCHAR2(30) NOT NULL, CANDI_USER_NAME VARCHAR2(50) NOT NULL, ACCOUNT_TYPE_ID NUMBER(10), CREATED DATE, LAST_ACCESSED DATE, DEPARTED DATE, PERSON_ID NUMBER(10), AGENT_ID NUMBER(10), INSTRUMENT_ID NUMBER(10) ) TABLESPACE CANDI_DATA_SMALL PCTUSED 40 PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 2M NEXT 2M MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT ) LOGGING NOCACHE NOPARALLEL; CREATE UNIQUE INDEX ACTOR_PK ON ACCOUNT (ACCOUNT_ID) LOGGING TABLESPACE CANDI_INDEX_SMALL PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 2M NEXT 2M MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT ) NOPARALLEL; ALTER TABLE ACCOUNT ADD ( CONSTRAINT ACTOR_PK PRIMARY KEY (ACCOUNT_ID) USING INDEX TABLESPACE CANDI_INDEX_SMALL PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 2M NEXT 2M MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 )); ALTER TABLE ACCOUNT ADD ( CONSTRAINT ACCOUNT_TYPE_ACCOUNT_FK1 FOREIGN KEY (ACCOUNT_TYPE_ID) REFERENCES ACCOUNT_TYPE (ACCOUNT_TYPE_ID)); ALTER TABLE ACCOUNT ADD ( CONSTRAINT AGENT_ACCOUNT_FK1 FOREIGN KEY (AGENT_ID) REFERENCES AGENT (AGENT_ID)); ALTER TABLE ACCOUNT ADD ( CONSTRAINT INSTRUMENT_SYSTEM_ACCOUNT_FK1 FOREIGN KEY (INSTRUMENT_ID) REFERENCES INSTRUMENT_SYSTEM (INSTRUMENT_ID)); ALTER TABLE ACCOUNT ADD ( CONSTRAINT PERSON_ACCOUNT_FK1 FOREIGN KEY (PERSON_ID) REFERENCES PERSON (PERSON_ID)); CREATE TABLE PERSON ( PERSON_ID NUMBER(10) NOT NULL, EMAIL_NAME VARCHAR2(50), NT_USER_NAME VARCHAR2(20), PHONE VARCHAR2(20), SITE VARCHAR2(20) ) TABLESPACE CANDI_DATA_SMALL PCTUSED 40 PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 2M NEXT 2M MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT ) LOGGING NOCACHE NOPARALLEL; CREATE UNIQUE INDEX PERSON_PK ON PERSON (PERSON_ID) LOGGING TABLESPACE CANDI_INDEX_SMALL PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 2M NEXT 2M MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT ) NOPARALLEL; ALTER TABLE PERSON ADD ( CONSTRAINT PERSON_PK PRIMARY KEY (PERSON_ID) USING INDEX TABLESPACE CANDI_INDEX_SMALL PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 2M NEXT 2M MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 )); Thank you for any help on this issue as it may prevent us from using Oracle's data access technologies. Thanks, Rob Tag: Dynamic SQL Generation Error Tag: 61182
  - 18
    - cannot connect to SQLServer! I keep getting the following error. I seem to have tried everything, heres the stack trace:- [SqlException: SQL Server does not exist or access denied.] System.Data.SqlClient.SqlConnection.Open() +759 ConnectionTest1.WebForm1.Page_Load(Object sender, EventArgs e) in c:\inetpub\wwwroot\ConnectionTest1 \WebForm1.aspx.vb:35 System.Web.UI.Control.OnLoad(EventArgs e) +67 System.Web.UI.Control.LoadRecursive() +29 System.Web.UI.Page.ProcessRequestMain() +724 I've pinged the server and it's there! I can connect through access, I have ASP pages running off SQL db's on the server. I am close to the edge :o Here's the code I'm running, can anyone help me or walk me through getting connected pleeeeze. also any test code would be appreciated: CODE: Imports System.Data.SqlClient Public Class WebForm1 Inherits System.Web.UI.Page #Region " Web Form Designer Generated Code " 'This call is required by the Web Form Designer. <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent() End Sub Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init 'CODEGEN: This method call is required by the Web Form Designer 'Do not modify it using the code editor. InitializeComponent() End Sub #End Region Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 'Put user code to initialize the page here Dim strConnection As New SqlConnection ("Password=;" & _ "Persist Security Info=True;" & _ "User ID=sa;" & _ "Initial Catalog=pubs;" & _ "Data Source=myDatabase") Dim strSql As String = "SELECT * FROM authors" Dim objCmd As New SqlClient.SqlCommand(strSql, strConnection) strConnection.Open() 'objCmd.ExecuteNonQuery() strConnection.Close() End Sub End Class Thanks in advance Tag: Dynamic SQL Generation Error Tag: 61179
  - 19
    - Data Binding to C# class? I know you can bind a dataset to a Windows.Forms.Control such as a comboBox .... but, was wondering if you can do the same to a class object? Example: Create a simple class (we'll call it clsEmployee) Create some properties of the employee like firstName, lastName etc... Create your SqlDataAdapter and DataSet and fill your SqlDataAdapter Create a DataViewManager and set it equal to the DataSet.DefaultViewManager In a typical comboBox scenario, you would set you comboBox's "DataSource" to the DataViewManager and then set the comboBox's DisplayMember to (Table.FieldName) and the ValueMember to (Table.FieldName) I would like to be able to do this with my class properties: emp.firstName = (Table.FieldName); emp.lastName = (Table.FieldName); Can this be done? Obviously not through the means described above sind clsEmployee does not have a "DataSource" property, but is there another way? dave Tag: Dynamic SQL Generation Error Tag: 61177
  - 20
    - solved..... finally got this to work... Tag: Dynamic SQL Generation Error Tag: 61176
  - 21
    - Access 2003 VBA Does anyone know if Access 2003 VBA is going to be switching to .Net? TIA, Michael Tag: Dynamic SQL Generation Error Tag: 61174
  - 22
    - Generate ADO.NET Code from Designer Hi, Is there any way to drop ADO object controls on the designers, and then automatically generate the the ADO.NET code that the controls implement? Thanks, Bob Tag: Dynamic SQL Generation Error Tag: 61168
  - 23
    - Getting null value for the connection string rom web.config I'm trying to get connection string,stored in encrypted form, from web.config. I've wriiten key and value pair under Configuration->Appsettings tag of one assembly and have used it as string connString = System.Configuration.ConfigurationSettings. AppSettings["ConnectionStringPlatform"] in a function of other assembly.I've also assigned a strong name and placed the assembly in global cache. I'm getting null value in connString. Can anybody tell me the reason for getting null value? An early reply would be appreciated. Regards, -Ritu Tag: Dynamic SQL Generation Error Tag: 61167
  - 24
    - Dataset code...is this correct? ok, I am trying to make one call to the db and create a datalist and then populate a datalist and a datagrid with different filtered versions of the dataset. I presume this is a better way of doing it than making two seperate calls to the db where I could just use 2 stored procs to filter the data. Any help or advice if I'm doing it the wrong way would be appreciated. ' Obtain Link information from Links table Dim links As New DNSite.LinksDB() Dim myDS As DataSet myDS = links.GetLinks(CatID) ' filter dataset for premier links ' and bind to the datalist control plusdl.DataSource = myDS.Tables("tblLinks").Select("TypeName = Premier Plus") plusdl.DataBind() ' bind remaining links to DataGrid premierDG.DataSource = myDS.Tables("tblLinks").Select("TypeName <> Premier Plus") premierDG.DataBind() ----------------------------------- I get the following error: ----------------------------------- Object reference not set to an instance of an object. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.NullReferenceException: Object reference not set to an instance of an object. Source Error: Line 88: ' and bind to the datalist control Line 89: Line 90: plusdl.DataSource = myDS.Tables("tblLinks").Select("TypeName = Premier") Line 91: plusdl.DataBind() Source File: **********\DNSLinks.ascx.vb Line: 90 Stack Trace: [NullReferenceException: Object reference not set to an instance of an object.] ASPNetPortal.DNSLinks.BindGrid() in ********\DNSLinks.ascx.vb:90 ASPNetPortal.DNSLinks.Page_Load(Object sender, EventArgs e) in ********\DNSLinks.ascx.vb:76 System.Web.UI.Control.OnLoad(EventArgs e) +67 System.Web.UI.Control.LoadRecursive() +29 System.Web.UI.Control.LoadRecursive() +92 System.Web.UI.Control.LoadRecursive() +92 System.Web.UI.Control.LoadRecursive() +92 System.Web.UI.Page.ProcessRequestMain() +724 Tag: Dynamic SQL Generation Error Tag: 61161
  - 25
    - Concurency Hello What is the equivalent of locking records for update to ensure that only one cliente really does it using disconnected recordsets?!? With connect recordsets we use pessimistic lock type to ensure that... my doubt is what to do when you use disconnected recordsets that uses optimistic locks?!? best regards JR Tag: Dynamic SQL Generation Error Tag: 61146
- Next
  - 1
    - filter dataset?? I have a dataset and i want to filter it into a new dataset or reader. I have the following code below but do not know how to finish it. I want to inport all rows where the TypeName column = a certain value (premier plus) Some help would be appreciated:) Dim links As New DNSite.LinksDB() Dim myDS As DataSet myDS = links.GetLinks(CatID) ' filter dataset for premier plus links ' and bind to the datalist control Dim pluslinks As New DataSet() pluslinks.Tables.Add(myDS.Tables("tblLinks").Clone) pluslinks.Tables(0).ImportRow(myDS.Tables("tblLinks").??? Tag: Dynamic SQL Generation Error Tag: 61142
  - 2
    - Concurrency problem I am controlling concurrency in my database records via a datetime column, with a stored procedure such as: UPDATE .......WHERE myid=@myid AND lastmodified=@lastmodified SELECT @lastmodified=lastmodified ........WHERE myid=@myid In this way, after any update, I get the new lastmodified column returned so that if the user saves the data again everything still works. This works as expected. However, if the update fails, I get the expected DBConcurrencyException, but the data in the Row property does not contain the new datetime value, but the original datetime. I can get at the new datetime by trapping the RowUpdated event and examining Command.Parameters, but as I am using a generic routine for all my updates I cannot use this method as different tables may have different datetime column names and I cannot pass the column name to the event. Anyone any ideas? Tag: Dynamic SQL Generation Error Tag: 61141
  - 3
    - Oracle & MS Access Hello, I'm brand new to programming in VB.NET and for my first project I need to do the following: Connect to an Oracle database (till now no problem with ODBC) get a table and put it in a MS Access (2000) database. Till now I succeed in getting the table and putting it in a datagrid to show the result, I also can create an .mdb file but I don't succeed to put my table (dataset, or data table) into the Access application. That's one problem, the second is that normally this table is an historical table with several records for one person but with only a FROM field an not a TILL field, so it would be preferable that I immediately can add a field to it (TILL) that represents the day -1 from the next record as long as it is the same person (with first a sort on field personnr and from), the last record of that person must got the actual date as the TILL date (I managed this pure in Excel, but not in Access nor in VB.NET, but like I said I'm very very new in VB). I hope that someone can help me and I thank you all in advance Peter Tag: Dynamic SQL Generation Error Tag: 61139
  - 4
    - Data Binding Issue This is a multi-part message in MIME format. ------=_NextPart_000_0008_01C35525.89B187D0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I have a table with 2 foreign keys in it that refer back to the same = table. Table 1: TM_CTP_Clocking_Type (Dataset =3D dsLookup) CTP_ID Int (PK) CTP_DESCRIPTION String Table 2: TM_CLK_Clockings (Dataset =3D dsEmp) CLK_ID Int (PK) CLK_IN_CTP_ID Int (FK to Table 1) CLK_OUT_CTP_ID Int (FK to Table 1) CLK_FIELD1 String etc... =20 I have a form with several Textboxes that are bound to other fields in = Table 2 and they work fine. On the same form, I also have 2 Combo = Boxes that are populated with Descriptions from Table 1, and the = SelectedValue is set according to the value of the Foreign Keys in Table = 2. My problem is at Runtime, both ComboBoxes are set to the same value = (that of the second Combo box "cmbCLK_OUT_CTP_ID" - or second Data = Binding?). Here is my Data Binding code:=20 With Me.cmbCLK_IN_CTP_ID .DataSource =3D dsLookup.TM_CTP_Clocking_Type .ValueMember =3D "CTP_ID" .DisplayMember =3D "CTP_DESCRIPTION" .SelectedIndex =3D -1 .DataBindings.Clear() .DataBindings.Add(New Binding("SelectedValue", dsEmp, = "TM_CLK_Clockings.CLK_IN_CTP_ID")) End With With Me.cmbCLK_OUT_CTP_ID .DataSource =3D dsLookup.TM_CTP_Clocking_Type .ValueMember =3D "CTP_ID" .DisplayMember =3D "CTP_DESCRIPTION" .SelectedIndex =3D -1 .DataBindings.Clear() .DataBindings.Add(New Binding("SelectedValue", dsEmp, = "TM_CLK_Clockings.CLK_OUT_CTP_ID")) End With When I comment out the code for the second ComboBox's Data Binding, I = get the correct result in the first Combo Box. Am I doing something wrong in the code or is having 2 relationships to = the same table too much for .NET Data Binding? Many Thanks, Joe --=20 Joe Hanna Advanced Software Engineering joe.hanna_AT_advancedsoftware_DOT_net_DOT_au ------=_NextPart_000_0008_01C35525.89B187D0 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.1170" name=3DGENERATOR> <STYLE></STYLE> </HEAD> <BODY bgColor=3D#ffffff> <DIV><FONT size=3D2>I have a table with 2 foreign keys in it that refer = back to=20 the same table.</FONT></DIV> <DIV><FONT size=3D2></FONT> </DIV> <DIV><FONT size=3D2></FONT> </DIV> <DIV><FONT face=3D"Courier New" size=3D2>Table 1:  = TM_CTP_Clocking_Type =20 (Dataset =3D dsLookup)</FONT></DIV> <DIV><FONT face=3D"Courier New" size=3D2></FONT> </DIV> <DIV><FONT face=3D"Courier New" size=3D2>    =    =20    =20 CTP_ID           &= nbsp;  Int   (PK)</FONT></DIV> <DIV><FONT face=3D"Courier New" size=3D2>    =    =20    =20 CTP_DESCRIPTION     String</FONT></DIV> <DIV><FONT face=3D"Courier New" size=3D2></FONT> </DIV> <DIV><FONT face=3D"Courier New" size=3D2></FONT> </DIV> <DIV><FONT face=3D"Courier New" size=3D2>Table 2: =20 TM_CLK_Clockings      (Dataset =3D=20 dsEmp)</FONT></DIV> <DIV><FONT face=3D"Courier New" size=3D2></FONT> </DIV> <DIV><FONT face=3D"Courier New" size=3D2>    =    =20    =20 CLK_ID           &= nbsp;  Int   (PK)</FONT></DIV> <DIV><FONT face=3D"Courier New" size=3D2>    =    =20    =20 CLK_IN_CTP_ID       Int   = (FK to=20 Table 1)</FONT></DIV> <DIV><FONT size=3D2> <DIV><FONT face=3D"Courier New" size=3D2> <DIV><FONT face=3D"Courier New" size=3D2>    =    =20    =20 CLK_OUT_CTP_ID      Int   (F= K to=20 Table 1) <DIV><FONT face=3D"Courier New" size=3D2>    =    =20    =20 CLK_FIELD1          Str= ing</FONT></DIV> <DIV></FONT></FONT>         =             e= tc...           &n= bsp;           =20 </DIV></DIV></DIV></FONT></DIV> <DIV><FONT face=3D"Courier New" size=3D2></FONT> </DIV> <DIV><FONT size=3D2>I have a form with several Textboxes that are bound = to other=20 fields in Table 2 and they work fine.  On the same form, I also = have =20 2 Combo Boxes that are populated with Descriptions from Table 1, = and the=20 SelectedValue is set according to the value of the Foreign Keys in Table = 2.  My problem is at Runtime, both ComboBoxes are set to the same = value=20 (that of the second Combo box "cmbCLK_OUT_CTP_ID" - or second Data=20 Binding?).</FONT></DIV> <DIV><FONT size=3D2></FONT> </DIV> <DIV><FONT size=3D2>Here is my Data Binding = code:</FONT> </DIV> <DIV><FONT size=3D2></FONT> </DIV><FONT size=3D2> <DIV><BR><FONT face=3D"Courier New">      With=20 Me.cmbCLK_IN_CTP_ID</FONT></DIV> <DIV><FONT face=3D"Courier New"></FONT> </DIV> <DIV><FONT face=3D"Courier = New">       =20 .DataSource =3D=20 dsLookup.TM_CTP_Clocking_Type<BR>      &nbs= p;=20 .ValueMember =3D "CTP_ID"<BR>       =20 .DisplayMember =3D = "CTP_DESCRIPTION"<BR>       =20 .SelectedIndex =3D -1</FONT></DIV> <DIV><FONT face=3D"Courier New"></FONT> </DIV> <DIV><FONT face=3D"Courier = New">       =20 .DataBindings.Clear()<BR>       =20 .DataBindings.Add(New Binding("SelectedValue", dsEmp,=20 "TM_CLK_Clockings.CLK_IN_CTP_ID"))</FONT></DIV> <DIV><FONT face=3D"Courier New"></FONT> </DIV> <DIV><FONT face=3D"Courier New">      End=20 With</FONT></DIV> <DIV><FONT face=3D"Courier New"></FONT> </DIV> <DIV><FONT face=3D"Courier New">      With=20 Me.cmbCLK_OUT_CTP_ID</FONT></DIV> <DIV><FONT face=3D"Courier New"></FONT> </DIV> <DIV><FONT face=3D"Courier = New">       =20 .DataSource =3D=20 dsLookup.TM_CTP_Clocking_Type<BR>      &nbs= p;=20 .ValueMember =3D "CTP_ID"<BR>       =20 .DisplayMember =3D = "CTP_DESCRIPTION"<BR>       =20 .SelectedIndex =3D -1</FONT></DIV> <DIV><FONT face=3D"Courier New"></FONT> </DIV> <DIV><FONT face=3D"Courier = New">       =20 .DataBindings.Clear()<BR>       =20 .DataBindings.Add(New Binding("SelectedValue", dsEmp,=20 "TM_CLK_Clockings.CLK_OUT_CTP_ID"))</FONT></DIV> <DIV><FONT face=3D"Courier New"></FONT> </DIV> <DIV><FONT face=3D"Courier New">      End=20 With</FONT></FONT></DIV><FONT size=3D2> <DIV><BR>When I comment out the code for the second ComboBox's Data = Binding, I=20 get the correct result in the first Combo Box.</DIV> <DIV> </DIV> <DIV>Am I doing something wrong in the code or is having 2 relationships = to the=20 same table too much for .NET Data Binding?</DIV> <DIV> </DIV> <DIV> </DIV> <DIV>Many Thanks,</DIV> <DIV>Joe</DIV> <DIV> </DIV> <DIV>-- <BR><BR>Joe Hanna<BR>Advanced Software=20 Engineering<BR>joe.hanna_AT_advancedsoftware_DOT_net_DOT_au</DIV> <DIV> </DIV> <DIV><BR></FONT> </DIV></BODY></HTML> ------=_NextPart_000_0008_01C35525.89B187D0-- Tag: Dynamic SQL Generation Error Tag: 61136
  - 5
    - find the position of an item in the middle of datarows In vb6, I use adodc and bind the fields to textbox.text. If I need to find the specific item, I just use adodc.recordset.find, then the cursor will move to the first occurence and textbox.text will show the the item you need. But in ado.net, there is no cursor in dataset. You have to use the position in bindcontext. If you just need to go through items one by one, that is ok, you just add one or subtract one from the position. But if you need to find an item in the middle, how can you know the position you need to move to? Do I need to implement those search algorithems myself? >-----Original Message----- >There might be a functionality to do this, but I have seen people work >around very easily by putting another column called "rowindex" which >keeps the index stored there. [inefficient, but works] > >Rahul Singh > >anant systems, inc. | making information work for you > > anantsystems.net | ioserver.net [Developer and Business .NET Hosting] > > > >"Haisheng Lin" <haishenglin@hotmail.com> wrote in message >news:26aa01c353ff$bf1c1380$a001280a@phx.gbl... >> I have a table with two columns: >> prod_id (primary key), prod_name. >> >> I filled my dataset with this table and bind >> two textboxs to the two columns. >> I am trying to search a specific product >> using its prod_name and I want to know >> the position of this row in datatable.rows. >> >> How can I do it? >> >> Thank you! >> >> >> >> > > >. > Tag: Dynamic SQL Generation Error Tag: 61135
  - 6
    - Selecting values from a datatable that don't exist in another table Hey y'all, I'm working on a program at the moment where items in the stocklist can be either top-level products or accessories. When listing top level products, i need to show every stock item that doesn't show up in the ProductAccessories table. A sample select statement in sql server is as follows: SELECT * FROM Stocklist WHERE NOT EXISTS( SELECT * FROM ProductAccessories WHERE ProductAccessories.AccessoryId = Stocklist.StockNo); A sample of the productaccessories table would be: ProductId AccessoryId ABC123 ABC124 I've all the tables in a dataset in memory though, and want to work with that. Can this be done using the rowfilter property or any other way? I've thought about building a rowfilter string by looping through the accessories table and appending a series of not equals such and such an id, but there could be thousands of products / hundreds of accessories. It's also running on the compact framework, so speed would be an issue... Any ideas? Cheers Des Tag: Dynamic SQL Generation Error Tag: 61124
  - 7
    - Saving data in DataSet from one source in another Hi, I have a dataset containing 2 tables from XML file. I have used XmlTextReader and DataSet.ReadXml to do it. These data are shown on user form using DataGrid. Now I would like to inset these data (after approving by the user) into 2 tables in SQL2000. Tables of course have required columns but with different names. Anybody knows how to do it? I thought about binding existing DataSet with new DataAdapter or making a new copy of DataSet. But with no result... Thanks! Przemo. Tag: Dynamic SQL Generation Error Tag: 61095
  - 8
    - another dreaded Concurrency Violation problem My code generated a concurrency violation when I have an ORDER BY clause in the SQL, but it works fine without the ORder by. What gives? Is MS not supporting ORder By in this ADO.NET disconnected DataSet paradigm? X "SELECT * FROM tblKanji ORDER BY CardID ASC" O "SELECT * FROM tblKanji" Dim sql As String = "SELECT * FROM tblKanji ORDER BY " & orderByKey & " " & OrderDirection Debug.WriteLine(sql) m_da = New OleDbDataAdapter(sql, m_cn) m_da.FillSchema(m_ds, SchemaType.Source, "Kanji") m_da.Fill(m_ds, "Kanji") Dim cmdBuilder As New OleDbCommandBuilder(m_da) With m_da .InsertCommand = cmdBuilder.GetInsertCommand .DeleteCommand = cmdBuilder.GetDeleteCommand .UpdateCommand = cmdBuilder.GetUpdateCommand End With With m_ds.Tables("Kanji") .Rows(0)("StrokeCount") = .Rows(0)("StrokeCount").ToString & "9" m_da.Update(m_ds, "Kanji") End With Tag: Dynamic SQL Generation Error Tag: 61094
  - 9
    - update record in access table Hi, I recently started learning VB.NET and I ran into a first problem I cannot get solved. When loading an access table into a datagrid, loading and updating is OK. (no problems so far) When I load the same date into textboxes, introduce some navigation buttons to the form to read different records, I can only "load" data from my accessDB but cannot update changes back to the DB. I assume my problem is generated because my update doesn't "know" witch row to update. Kan anyone provide me some example data on how to update records based on the explained situation? (in case my explenation is not cleat enough, please let me know. I'm getting desperate about this problem) (trying to solve this problems since days withoud success) :-( thanks in advance, Peter Tag: Dynamic SQL Generation Error Tag: 61091
  - 10
    - DefaultView.Count always returns 1 This is a multi-part message in MIME format. ------=_NextPart_000_0008_01C3546A.6FA5CFB0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, =20 I have the following scenario: =20 myDataSet.Tables("tblCustomer").Rows.Count =3D 4 This is quite fine as the table contains 4 rows. However=20 myDataSet.Tables("tblCustomer").DefaultView.Count =3D 1=20 is not exactly what I expected it to be (I thought the result should be = 4). Can anybody explain this behavior to me? Thanks in advance Daniel ------=_NextPart_000_0008_01C3546A.6FA5CFB0 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.2600.0" name=3DGENERATOR> <STYLE></STYLE> </HEAD> <BODY> <DIV> <P class=3DMsoNormal style=3D"MARGIN: 0cm 0cm 0pt"><SPAN lang=3DEN-US=20 style=3D"mso-ansi-language: EN-US"><FONT size=3D2><FONT=20 face=3DVerdana>Hi,<?xml:namespace prefix =3D o ns =3D=20 "urn:schemas-microsoft-com:office:office" = /><o:p></o:p></FONT></FONT></SPAN></P> <P class=3DMsoNormal style=3D"MARGIN: 0cm 0cm 0pt"><SPAN lang=3DEN-US=20 style=3D"mso-ansi-language: EN-US"><o:p><FONT face=3DVerdana=20 size=3D2> </FONT></o:p></SPAN></P> <P class=3DMsoNormal style=3D"MARGIN: 0cm 0cm 0pt"><SPAN lang=3DEN-US=20 style=3D"mso-ansi-language: EN-US"><FONT size=3D2><FONT face=3DVerdana>I = have the=20 following scenario:<o:p></o:p></FONT></FONT></SPAN></P> <P class=3DMsoNormal style=3D"MARGIN: 0cm 0cm 0pt"><SPAN lang=3DEN-US=20 style=3D"mso-ansi-language: EN-US"><o:p><FONT face=3DVerdana=20 size=3D2> </FONT></o:p></SPAN></P> <P class=3DMsoNormal style=3D"MARGIN: 0cm 0cm 0pt"><SPAN lang=3DEN-US=20 style=3D"mso-ansi-language: EN-US"><FONT face=3DVerdana size=3D2><FONT=20 face=3D"Courier New">myDataSet.Tables("tblCustomer").Rows.Count =3D=20 4</FONT></FONT></SPAN></P> <P class=3DMsoNormal style=3D"MARGIN: 0cm 0cm 0pt"><SPAN lang=3DEN-US=20 style=3D"mso-ansi-language: EN-US"><FONT face=3DVerdana=20 size=3D2></FONT></SPAN> </P> <P class=3DMsoNormal style=3D"MARGIN: 0cm 0cm 0pt"><SPAN lang=3DEN-US=20 style=3D"mso-ansi-language: EN-US"><FONT size=3D2><FONT = face=3DVerdana>This is quite=20 fine as the table contains 4 rows. However </FONT></FONT></SPAN></P> <P class=3DMsoNormal style=3D"MARGIN: 0cm 0cm 0pt"><SPAN lang=3DEN-US=20 style=3D"mso-ansi-language: EN-US"><FONT size=3D2><FONT=20 face=3DVerdana></FONT></FONT></SPAN> </P> <P class=3DMsoNormal style=3D"MARGIN: 0cm 0cm 0pt"><SPAN lang=3DEN-US=20 style=3D"mso-ansi-language: EN-US"><FONT face=3D"Courier New"=20 size=3D2>myDataSet.Tables("tblCustomer").DefaultView.Count =3D 1=20 </FONT></SPAN></P> <P class=3DMsoNormal style=3D"MARGIN: 0cm 0cm 0pt"><SPAN lang=3DEN-US=20 style=3D"mso-ansi-language: EN-US"><FONT size=3D2><FONT=20 face=3DVerdana></FONT></FONT></SPAN> </P> <P class=3DMsoNormal style=3D"MARGIN: 0cm 0cm 0pt"><SPAN lang=3DEN-US=20 style=3D"mso-ansi-language: EN-US"><FONT size=3D2><FONT = face=3DVerdana>is not=20 exactly what I expected it to be (I thought the result should be=20 4). </FONT></FONT></SPAN><SPAN lang=3DEN-US=20 style=3D"mso-ansi-language: EN-US"><FONT size=3D2><FONT = face=3DVerdana>Can anybody=20 explain this behavior to me?</FONT></FONT></SPAN></P> <P class=3DMsoNormal style=3D"MARGIN: 0cm 0cm 0pt"><SPAN lang=3DEN-US=20 style=3D"mso-ansi-language: EN-US"><FONT size=3D2><FONT=20 face=3DVerdana></FONT></FONT></SPAN> </P> <P class=3DMsoNormal style=3D"MARGIN: 0cm 0cm 0pt"><SPAN lang=3DEN-US=20 style=3D"mso-ansi-language: EN-US"><FONT size=3D2><FONT = face=3DVerdana>Thanks in=20 advance</FONT></FONT></SPAN></P> <P class=3DMsoNormal style=3D"MARGIN: 0cm 0cm 0pt"><SPAN lang=3DEN-US=20 style=3D"mso-ansi-language: EN-US"><FONT face=3DVerdana=20 size=3D2></FONT></SPAN> </P> <P class=3DMsoNormal style=3D"MARGIN: 0cm 0cm 0pt"><SPAN lang=3DEN-US=20 style=3D"mso-ansi-language: EN-US"><FONT face=3DVerdana = size=3D2>  =20 Daniel</FONT></SPAN></P></DIV></BODY></HTML> ------=_NextPart_000_0008_01C3546A.6FA5CFB0-- Tag: Dynamic SQL Generation Error Tag: 61086
  - 11
    - [ANN] PGSqlClient 1.0 Alpha 1 released Hello: The first version of PGSqlClient an ADO.NET Data Provider for PostgreSQL 7.4+. PGSqlClient 1.0 Alpha 1 source code is available for download at: http://prdownloads.sourceforge.net/pgsqlclient/PGSqlClient1.0-Alpha1-Src.zip?download A binary package for Windows and MS .NET Frameworkis available at: http://prdownloads.sourceforge.net/pgsqlclient/PGSqlClient1.0-Alpha1.exe?download -- Best regards Carlos Guzmán Álvarez Vigo-Spain Tag: Dynamic SQL Generation Error Tag: 61085
  - 12
    - Ilistsource and Ienumberable I am a bit of a plodder so sorry if this sounds dumb. I am trying to get data into a .NET web form datagrid from a vfoxpro 8 table via the oledb driver. But when I debug I get a error message teling me: "An invalid datasource is being used for datagrid1. A valid datasource must implement either IListSource or IEnumerable." I'm completely stumped, but I suspect the answer is so obvious I will feel a complete idiot when I know it. Can anyone put me out of my misery? Thanks in advance for any ideas. John Wilson Tag: Dynamic SQL Generation Error Tag: 61083
  - 13
    - How to rerun a sub with a SQL UPDATE statement IF not successful first time? Hi, I have a Sub procedure where I retrieve an xml string from a single field: (1) SELECT FieldA FROM tblTEST WHERE... (2) Append an element to the DOM (3) UPDATE FieldA... In the unlikely (but always possible) event that the UPDATE action bumps heads with someone else's UPDATE to the same field, how would I tell the sub to start from the beginning of the sub IF the UPDATE was not successful...sorry, but I have no clue...how do I even capture if the UPDATE doesn't happen? TIA, Kathy Tag: Dynamic SQL Generation Error Tag: 61079
  - 14
    - Search in Dataset I have a table with two columns: prod_id (primary key), prod_name. I filled my dataset with this table and bind two textboxs to the two columns. I am trying to search a specific product using its prod_name and I want to know the position of this row in datatable.rows. How can I do it? Thank you! Tag: Dynamic SQL Generation Error Tag: 61077
  - 15
    - Access dBase II files over a network share. Hello folks, I am trying to access a dBase III file on a network share via VB.NET code. For some reason it does not recognize the location as a network file in the OLEDb connection object. I keep getting this error: '\\Mike_desktop\avalon projects\FullData' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides. Any help would be greatly appreciated. Thanks Mike Tag: Dynamic SQL Generation Error Tag: 61072
  - 16
    - Update UI with DS Changes I programmatically change the values in a dataset. However, the changes are not reflected in the UI. What am I missing. If I set the values in the UI controls the dataset is updated as expected. However, I am trying to decouple the logic from the UI (in case we switch from forms to ASP etc). Any help, hints, or comments are greatly appreciated. Cheers, Knut Vonheim Tag: Dynamic SQL Generation Error Tag: 61069
  - 17
    - DbNull Which is it? I have a scalar operation with a sql command, and I want to check for null values. If I check for dbnull it reads the command as a string. If I set it to a string, it reads it as dbnull. Dim dcOrdNumber As SqlCommand Dim null As DBNull If dcOrdNumber.ExecuteScalar() Is null Then ... ' Cast from type 'DBNull' to type 'String' is not valid. If dcOrdNumber.ExecuteScalar() = "NULL" Then ... ' Operator is not valid for type 'DBNull' and string "NULL". Tag: Dynamic SQL Generation Error Tag: 61064
  - 18
    - Strange behavior of my program Hello All, I wrote a program in which I have a OleDbConnection which I open in the contructor and close in the Destructor. When I run the program I get the following error Unhandled Exception: System.InvalidOperationException: Handle is not initialized . at System.WeakReference.get_Target() at System.Data.Common.WeakReferenceCollection.Close(Boolean flag) at System.Data.OleDb.OleDbConnection.CloseReferences(Boolean canceling) at System.Data.OleDb.OleDbConnection.DisposeManaged() at System.Data.OleDb.OleDbConnection.Close() at LoadCkpt.Finalize() If I remove the contructor and destructor and open and close the connection in my method only, then the program runs successfully. I don't understnad why is this error coming? Why is it that I can't open/close my connection in my contructor descructor? Has anyone else seen this kind of error? I will be very gratefull for your response. regards, Abhishek. Tag: Dynamic SQL Generation Error Tag: 61060
  - 19
    - web.config sql connectionstring problem Dear .Netters, Following is the code from my web.config file ========== <add key="SqlConnectionA.ConnectionString" value="workstation id=myPC;packet size=4096;integrated security=SSPI;data source=\"myPC\\myDB\";persist security info=False;initial catalog=telecomTimeSheet" /> ========== I call this connectionstring in my code behind file like this: ============ this.sqlConnection1.ConnectionString = System.Configuration.ConfigurationSettings.AppSettings.GetValues("SqlConnect ionA.ConnectionString").ToString(); ============= When I view the file I get the following error: ========= [ArgumentException: Format of the initialization string does not conform to specification starting at index 0.] System.Data.Common.DBConnectionString.GetKeyValuePair(Char[] connectionString, Int32 currentPosition, String& key, Char[] valuebuf, Int32& vallength, Boolean& isempty) +942 System.Data.Common.DBConnectionString.ParseInternal(Char[] connectionString, UdlSupport checkForUdl, NameValuePair& keychain) +116 System.Data.Common.DBConnectionString..ctor(String connectionString, UdlSupport checkForUdl) +114 System.Data.SqlClient.SqlConnectionString..ctor(String connectionString) +13 System.Data.SqlClient.SqlConnectionString.ParseString(String connectionString) +96 System.Data.SqlClient.SqlConnection.set_ConnectionString(String value) +11 ttsheet.timesheet.timeSheetForm.InitializeComponent() +1590 ttsheet.timesheet.timeSheetForm.OnInit(EventArgs e) +13 System.Web.UI.Control.InitRecursive(Control namingContainer) +241 System.Web.UI.Control.InitRecursive(Control namingContainer) +179 System.Web.UI.Control.InitRecursive(Control namingContainer) +179 System.Web.UI.Page.ProcessRequestMain() +174 ========= Any ideas in what I am doing wrong? SSP Tag: Dynamic SQL Generation Error Tag: 61057
  - 20
    - Help - Class Library Project Hellooooo, maybe MS guys can answer this please? How do I add SQL Tables from Server Explorer control, and then configure Adapter, and generate Datasets if I have a "class library project" Yes I know I can drag tables onto asp.net page, and on a WinForm - but I don't have these because my project is "Class Library Project" DLL. What's the recommended workaround? I need to be able to frequently update my code- gen'ed files. Thank you Tag: Dynamic SQL Generation Error Tag: 61053
  - 21
    - Help - Class Library Project Hellooooo, maybe MS guys can answer this please? How do I add SQL Tables from Server Explorer control, and then configure Adapter, and generate Datasets if I have a: <CLASS LIBRARY PROJECT> Yes I know I can drag tables onto asp.net page, and on a WinForm - but I don't have these because my project is "Class Library Project" DLL. What's the recommended workaround? I need to be able to frequently update my code- gen'ed files. Thank you Tag: Dynamic SQL Generation Error Tag: 61052
  - 22
    - Access2K Oledb DataView: DefaultValue Unique AllowDBNull properties When an OleDb dataview is instantiated using (Select * from MyAccess2000Table), why aren't the following datacolumn properties accurately exposed: Unique AllowDBNull DefaultValue Dim Col As DataColumn For Each Col In MyDataView.Table.Columns Col.Unique Col.AllowDBNull Col.DefaultValue Next When I test these values at runtime, they do not reflect the state of my Access table. Doesn't the JET4.0 Provider surface them to the DataView? TIA Tag: Dynamic SQL Generation Error Tag: 61042
  - 23
    - .Find Method w/multiple keys I've got a dataset (ratesds2) containing multiple relational tables (3) for which I'd like to find a single child record. Table(1) contains a single record for each product and table(2) contains details for the product. The product details table is "nested" based on the productID. Table one has a single key field (productID) and table two has two key fields with productID from table 1 being one of the two and the other a unique product attribute key. Dataset is correctly defined with the two primary key fields in table 2. All I want to do is retrieve a specific row from table 2 given the two key fields as follows and put the value of a single field into string variable; Sample Code ----------------------------------------------- Dim filterstring As String = "productid = '123'" Dim productdetailsview As New DataView productdetailsview = ratesds2.Tables(2).DefaultView productdetailsview.RowFilter = filterstring Dim foundRowsc() As DataRowView = productdetailsview.Find(New Object() "123", "C"}) dim attribiute_c as string = foundRowsc(0)("Details") This doesn't work. Any ideas? Tag: Dynamic SQL Generation Error Tag: 61041
  - 24
    - accessing column types without doing a query Is there a convinient way to access column types without having to actually perform a query? I know about the GetOleDbSchemaTable() function but I don't see anything about column types there. Thanks, Dave Tag: Dynamic SQL Generation Error Tag: 61029
  - 25
    - Storing changes to a dataset. Hello, I have posted the original problem to the newsgroup earlier this week and thought I would try again with my latest problems. I am developing a Data access component which is to access data from a Foxpro database (schema below) using the OleDbProvider. So far I have no problem getting information out of the database into a dataset but storing changes back to the Database is causing me alot of exceptions and I can't seem to understand why. Any help would be greatly appreciated. Thanks Bill I am developing in Visual Studio .Net 2003 (C#) and using Visual Foxpro 7.0 SP1. Database Schema ------------------ Table1 irow : integer [primary key] [default value calculated by stored procedure = column max value + 1] name : character (32) value : character (254) owner: integer [Foreign key to table 2] host: character (32) Table2: id: integer [Primary key] name: character(32) Description: character (254) Sql statements --------------- (Each is encapsulated with an OleDbCommand object generated by the visual studio designer) 1) SELECT table1.irow, table1.name, table1.value, table1.owner, host FROM table1, table2 WHERE table1.owner = table2.id AND table2.name = ? 2) INSERT INTO table1 (name, value, owner, host ) VALUES (?, ?, ?, ?) 3) UPDATE table1 SET name = ?, host = ? WHERE irow = ? 4) DELETE FROM table1 WHERE irow = ? Code ------ Originally I tried to use the OleDbDataAdapter that generated the DataSet, to store the changes. public virtual void SetParamter (DataSet dataset ) { OleDbCommandBuilder cb = new OleDbCommandBuilder( dataAdapter ); dataAdapter.Update( dataset ); } This generated the following exception: System.Data.OleDb.OleDbException: No value given for one or more required parameters. at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping) at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable) at System.Data.Common.DbDataAdapter.Update(DataSet dataSet) at DataAccess.SetParameters(DataSet dataset) in e:\MyProjects\dataaccess.cs:line 168 The Table mapping is configured in the DataAdapter. I then tried creating my own commands and associating it with the DataAdapter, instead of using the command builder, and this resulted in the same exception. Next I tried to do the update myself by iterating through the dataset and calling the appropriate Add/Modify/Delete method depending on the DataRow's RowState. This generated exceptions as well, shown below along with each method's code. 1) Insert method: protected override bool AddRecord ( DataRow record ) { bool retVal = false; InsertRecordCommand.Parameters["ParameterName"].Value = record["name"]; InsertRecordCommand.Parameters["ParameterValue"].Value = record["value"]; InsertRecordCommand.Parameters["OwnerId"].Value = this.OwnerId; InsertRecordCommand.Parameters["Host"].Value = record["host"]; try { DbConnection.Open(); if ( InsertRecordCommand.ExecuteNonQuery() == 1 ) retVal = true; } catch ( Exception e ) { Console.WriteLine( e.ToString() ); } finally { if ( DbConnection != null ) DbConnection.Close(); } return retVal; } System.NullReferenceException: Object reference not set to an instance of an object. at System.Data.Common.ICommandText.Execute(IntPtr pUnkOuter, Guid riid, tagDBPARAMS pDBParams, Int32& pcRowsAffected, Object& ppRowset) at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteNonQuery() at DataAccess.AddRecord(DataRow record) in e:\MyProjects\dataaccess.cs:line 227 2) UpdateRecordCommand: protected override bool ModifyRecord( DataRow record ) { bool retVal = false; UpdateRecordCommand.Parameters["ParameterValue"].Value = record["value"]; UpdateRecordCommand.Parameters["Host"].Value = record["host"]; UpdateRecordCommand.Parameters["Irow"].Value = record["irow"]; try { DbConnection.Open(); if ( UpdateRecordCommand.ExecuteNonQuery() == 1 ) retVal = true; } catch ( Exception e ) { Console.WriteLine( e.ToString() ); } finally { if ( DbConnection != null ) DbConnection.Close(); } return retVal; } System.Data.OleDb.OleDbException: Syntax error. at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr) at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteNonQuery() at DataAccess.ModifyRecord(DataRow record) in e:\MyProjects\dataaccess.cs:line 267 3) DeleteRecordCommand: protected override bool DeleteRecord( DataRow record ) { bool retVal = false; record.RejectChanges(); //To be able to get irow value from record DeleteRecordCommand.Parameters["Irow"].Value = record["irow"]; record.Delete(); //Mark as deleted again. try { DbConnection.Open(); if ( DeleteRecordCommand.ExecuteNonQuery() == 1 ) retVal = true; } catch ( Exception e ) { Console.WriteLine( e.ToString() ); } finally { if ( DbConnection != null ) DbConnection.Close(); } return retVal; } System.NullReferenceException: Object reference not set to an instance of an object. at System.Data.Common.ICommandText.Execute(IntPtr pUnkOuter, Guid riid, tagDBPARAMS pDBParams, Int32& pcRowsAffected, Object& ppRowset) at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteNonQuery() at DataAccess.DeleteRecord(DataRow record) in e:\MyProjects\dataaccess.cs:line 303 Tag: Dynamic SQL Generation Error Tag: 61021

Using Access

I have created an oledbdataadapter

configured the select command

created an oldbcommandbuilder for the dataadapter

When I attempt to update the dataset, I get the following
error

"Dynamic SQL generation is not supported against a select
command that does not return any base table information"

The table does have a primary key.

Thanks in adnvance

John Lott

Top