SQL Insert statement

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
    - 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: SQL Insert statement Tag: 61183
  - 2
    - 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: SQL Insert statement Tag: 61182
  - 3
    - 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: SQL Insert statement Tag: 61179
  - 4
    - 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: SQL Insert statement Tag: 61177
  - 5
    - solved..... finally got this to work... Tag: SQL Insert statement Tag: 61176
  - 6
    - Access 2003 VBA Does anyone know if Access 2003 VBA is going to be switching to .Net? TIA, Michael Tag: SQL Insert statement Tag: 61174
  - 7
    - 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: SQL Insert statement Tag: 61168
  - 8
    - 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: SQL Insert statement Tag: 61167
  - 9
    - 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: SQL Insert statement Tag: 61161
  - 10
    - 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: SQL Insert statement Tag: 61146
  - 11
    - 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: SQL Insert statement Tag: 61142
  - 12
    - 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: SQL Insert statement Tag: 61141
  - 13
    - 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: SQL Insert statement Tag: 61139
  - 14
    - 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: SQL Insert statement Tag: 61136
  - 15
    - 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: SQL Insert statement Tag: 61135
  - 16
    - 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: SQL Insert statement Tag: 61124
  - 17
    - 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: SQL Insert statement Tag: 61095
  - 18
    - 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: SQL Insert statement Tag: 61094
  - 19
    - 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: SQL Insert statement Tag: 61091
  - 20
    - 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: SQL Insert statement Tag: 61086
  - 21
    - [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: SQL Insert statement Tag: 61085
  - 22
    - 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: SQL Insert statement Tag: 61083
  - 23
    - 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: SQL Insert statement Tag: 61079
  - 24
    - 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: SQL Insert statement Tag: 61077
  - 25
    - 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: SQL Insert statement Tag: 61072
- Next
  - 1
    - 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: SQL Insert statement Tag: 61069
  - 2
    - 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: SQL Insert statement Tag: 61064
  - 3
    - 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: SQL Insert statement Tag: 61060
  - 4
    - 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: SQL Insert statement Tag: 61057
  - 5
    - 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: SQL Insert statement Tag: 61053
  - 6
    - 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: SQL Insert statement Tag: 61052
  - 7
    - 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: SQL Insert statement Tag: 61042
  - 8
    - .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: SQL Insert statement Tag: 61041
  - 9
    - 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: SQL Insert statement Tag: 61029
  - 10
    - 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: SQL Insert statement Tag: 61021
  - 11
    - Formatting a MS Access date/time field in Windows Form column I read MS Access records in. The date/time field in my datagrid only shows the date...does not include the time. Is there a way to format or get the Column to show the time as well? This is in Windows Forms. Amil Tag: SQL Insert statement Tag: 61015
  - 12
    - Populating a Combo Box from DataReader Hi! I am trying to populate a combo box using a data reader. I want to add the Item as well as the ValueMember. Will this do the trick? Do While oDr.Read cmbestudiante.Items.Add(oDr.Item("Estudiante_Apellido") + ", " + oDr.Item("Estudiante_Nombre")) cmbestudiante.ValueMember = oDr.Item("Estudiante_ID") Loop Tag: SQL Insert statement Tag: 61013
  - 13
    - executing procedure several times Hi, I have to execute a stored procedure sp_StorProc several times. How do I batch it? I do not want to make a trip to the database at each execution. One approach is to concatenate all sp_StorProc calls into one string and then execute the command. However, this is not very elegant. Is there any other way? Regards, San Tag: SQL Insert statement Tag: 61011
  - 14
    - Data access application block Hi, I'm using the SqlHelper class from microsofts data access application block and have problems when trying to call a stored procedure that has a TEXT parameter. I'm using the following environment: - SQL Server 2000 SP3a + Cumulative Patch (July 2003) - .NET 1.0 - Data Access Application Block 1.0 The method SqlHelperParameterCache.DiscoverSpParameterSet returns a SqlParameter with SqlDbType set to VarChar and Size set to Int32.MaxValue. The problem seems to be in SqlCommandBuilder.DeriveParameters. When calling the stored procedure with the automatically discovered parameter set, I get an SqlException with one of the following messages: - General network error. Check your network documentation. - A severe error occurred on the current command. The results, if any, should be discarded. In the servers event log, I can find the following error: Event Type: Error Event Source: MSSQL$MYSERVER Event Category: (2) Event ID: 17052 Date: 25.07.2003 Time: 16:33:35 User: N/A Computer: MYSERVER Description: Error: 17805, Severity: 20, State: 3 Invalid buffer received from client. Data: ... After reading some newsgroup posts, it seems to me that the problem also occurrs when using binary data. Is there a fix for these problems? Thanks, Martin Bischoff Tag: SQL Insert statement Tag: 61007
  - 15
    - Data Access Application Block/Nested Dataset/Transactions I am in the process of writing some WEB Services that will receive a nested Dataset and have to apply any changes to the appropriate tables. I am planning on using the Data Access Application Block for my database calls. I have the UpdateDataset method working, but now I want to add transactional support. If the dataset contains changes to table1, table2, and table3 I want to wrap the updates in a transaction so I can roll back if necessary. Any ideas on how the application block handles this ? Do I have to modify the application block and add my own custom code ? Tag: SQL Insert statement Tag: 61005
  - 16
    - OOP data storage vs. SQL calls I have a general question for which I would like to get some general input from the online community. I'm building an asp.net web application that will make extensive use of a SQL Server 2K database. One of the relationships in this database is many Tasks to a 1 Project. I can see two basic ways to manage this in my web application: The first is by creating a Project class and a Task class, and talking directly to the database with either -- meaning that on each page where I need data about either a Project or a Task, I make the stored procedure calls and add, modify, or delete the data appropriately. The other is to use the IEnumerable interface so that I can do clever things like Project.Tasks.Add() and Project.Tasks.Modify(), etc. But, in this model, it seems to make more sense to get all the necessary data upon instantiation of the Project class, and then carry it around in a Session object, and make database calls as I need to in order to keep the class and the database in sync. OK, so here's the question -- which is more efficient, and is there a point of diminishing returns for the preferred answer. In short, does it make more sense to have more database calls (potentially every page), or to have one big one, and carry the enumerated object around in Session, making calls only as necessary. Please note that I realize that number of concurrent users, number of layers in the architecture, etc. are all factors in making the decision . . . I'm looking for an "all other things being equal, x is more efficient, and here's why . . . " Anyone got an opinion on this? Any input is appreciated. Thanks, Vic Fees Tag: SQL Insert statement Tag: 60993
  - 17
    - DataSets or Datareaders?!?! Hello Imagine you've an web page made by an ASP.NET web service and the asp.net page itself. All the Data retriving that the application does is made=20 using the webservice... when a web form is loaded the web=20 page calls several functions of the web service to get the options to populate drop down boxes etc... so far i've been using datasets returned from the web=20 service to the page. example: in a form on dropdown box could have a list of=20 clients. In the web page, on form load, i invoke a=20 webmethod of my web service that returns a dataset to the=20 page with a table consisting of those records fetched. In=20 the page I run over all the rows of the tabl=E7e and populate the=20 dropdown box. my doubt is: am i using ado.net right to do such things=20 or should i return a datareader for the web page instead of a=20 dataset?!?! is it good practice to works as i've been doing?!?! its' not a waste of resources to have things done that=20 way?!?! best regards Miguel Pais Tag: SQL Insert statement Tag: 60984
  - 18
    - ADO.NET synchronization/concurrency Hello How can I insure that with an web app only one client access in write mode to a record in a table?!? My application scenario is the following: I've a web project that makes all the data retriving from the client and some client side validation. That web page often retrives data from an web service that, in each function, opens a database connection and gets the data, sending it to the client right after closing the connection... knowing that ADO.NET uses disconnected recordsets and with this kind of develloping architecture how can I insure concurrency to allow only on web client in write mode and the others in reading mode until the resource is free?!?! best regards Jorge Ribeiro Tag: SQL Insert statement Tag: 60980
  - 19
    - Get a list of SQL Stored procedure parameters from database Hi, I am trying to modify a common function in SQLHelper class of the DAAB, which will return the SQL parameters for a stored proc. I am passing the connection string and the stored procedure name to it. How do I get the parameters from the database. The reason, I need to do this, because I am using Beta2 version and Deriveparameters function doesnot work in beta 2. How can I achieve the same functionality? Please Help!!!! Thanks in advance. Tag: SQL Insert statement Tag: 60979
  - 20
    - another "InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool" error I've looked at all of the posts and on the web and found many people running into this same issue. I've take an look at my code (posted below) and it looks fine, but this is my first attemp at ADO.NET with Stored Procedures on SQL Server. I currently don't have any maxpool setting configured, so it's using what ever the default is (100 I believe). I've tried to use SQL Profiler to find out how many active connections there are as well as PerfMon and SQL Ent. Mgr., but I can't seem to find anything that shows any bad connections. Any guidance would be appreciated. Here's my connection string from the web.config file: <appSettings> <add key="ConnectionString" value="server=X.X.X.X;database=demo;uid=demo;pwd=12345;" /> </appSettings> where X.X.X.X is the IP of my remote Microsoft SQL Server 2000 - 8.00.760 on Windows NT 5.0 (Build 2195: Service Pack 4) ---------------------- code to get data into a DataList object ------------------------- Private Function getJobs(ByVal state As Integer) As DataSet ' Create Instance of Connection and Command Object Dim myConnection As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString")) Dim myCommand As New SqlDataAdapter("demo_GetJobs", myConnection) ' Mark the Command as a SPROC myCommand.SelectCommand.CommandType = CommandType.StoredProcedure ' Add Parameters to SPROC Dim parameterUserID As New SqlParameter("@UserID", SqlDbType.Int, 4) parameterUserID.Value = 1 myCommand.SelectCommand.Parameters.Add(parameterUserID) ' Add Parameters to SPROC Dim parameterState As New SqlParameter("@State", SqlDbType.Int, 4) parameterState.Value = state myCommand.SelectCommand.Parameters.Add(parameterState) ' Create and Fill the DataSet Dim myDataSet As New DataSet Try myCommand.Fill(myDataSet) Finally ' Close the Connection If myConnection.State = ConnectionState.Open Then myConnection.Close() End If 'the commands below were added to try to make sure the connection was closed and released myCommand.Dispose() myCommand = Nothing myConnection.Dispose() myConnection = Nothing End Try Return myDataSet End Function -------------------------------- end code ---------------------------------- Sincerely, Jeff Braun Tag: SQL Insert statement Tag: 60977
  - 21
    - Insert into Access DB Using DataSet Hi... Here is my code and my problem is that it doesn't update DB. Langauage Used : C# OleDbAdapter customerDA= new OleDbAdapter("Select * from Cusomers",connection); DataSet customerDS = new DataSet(); customerDA.Fill(customerDS); DataRow customerRow = customerDS.Tables[0].NewRow(); customerRow["Name"] = "Kris"; customerDS.Tables[0].Rows.Add(customerRow); customerDA.Update(customerDS.Tables[0].Select(null,null,DataViewRoState.Adde d); connection.close(); Tag: SQL Insert statement Tag: 60975
  - 22
    - DataSet Undo System I'm trying to make an undo system for my application using adonet. The idea is simple: before performing the undoable operation make the copy of dataset. If the user wants to undo the changes, then restore the data from the old dataset. I have the following issues: 1. Many objects througth the form have references to a dataset and/or it's tables. So it is not enough just to replace the _dataSet variable but I would need to rebind all controls. I don't like it. 2. Because of what I said in the first bullet I am trying to replace the data inside the existing dataset like this: if (_copyOfDataSet != null) { _dataSet.Clear(); _dataSet.Merge(_copyOfDataSet, false); } but I'm far from sure that it warks as expected. So far, my simple experiments have shown that it works but I'm looking for your ideas and suggestions. Actually there are n copies of dataset after the user performs n undoable operations so I'm not looking for Accept/Reject Changes solution which would support only one "undo" operation. Thanks Tag: SQL Insert statement Tag: 60973
  - 23
    - Small ADO.NET design flaws Hello, Could someone from the ADO.NET design team respond to this , please : - why the IDbDataAdapter.Fill method does not have an overload which takes a DataTable parameter ? This is wierd considering that *ALL* the providers implement this overload. - why there isn't a ICommandBuilder interface ? Should we understand that is not required for a new a provider to implement the CommandBuilder functionality ? Thank you for your time. Cheers, Marius. Tag: SQL Insert statement Tag: 60972
  - 24
    - Reading list of Providers for OLEDB Connection. This is a multi-part message in MIME format. ------=_NextPart_000_007E_01C35298.A4C757A0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, I have requirement to retrive list of all providers supported in the = current system to prepare the OLE DB connection string. Is there any way in ADO.NET, i am using System.Data.OleDb as OleDb only = have the method to read the Schema information of any database = connection. Regards Rajeev ------=_NextPart_000_007E_01C35298.A4C757A0 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.1106" name=3DGENERATOR> <STYLE></STYLE> </HEAD> <BODY> <DIV><FONT face=3DVerdana size=3D2>Hi,</FONT></DIV> <DIV><FONT face=3DVerdana size=3D2></FONT> </DIV> <DIV><FONT face=3DVerdana size=3D2>I have requirement to retrive list of = all=20 providers supported in the current system to prepare the OLE DB = connection=20 string.</FONT></DIV> <DIV><FONT face=3DVerdana size=3D2>Is there any way in ADO.NET, i am = using=20 System.Data.OleDb as OleDb only have the method to read the Schema = information=20 of any database connection.</FONT></DIV> <DIV><FONT face=3DVerdana size=3D2></FONT> </DIV> <DIV><FONT face=3DVerdana size=3D2>Regards</FONT></DIV> <DIV><FONT face=3DVerdana size=3D2>Rajeev</FONT></DIV></BODY></HTML> ------=_NextPart_000_007E_01C35298.A4C757A0-- Tag: SQL Insert statement Tag: 60970
  - 25
    - outer and internal loop inside a dataset How can I loop on a sorted datatable in order to sum an internal group? Here's what I mean: Dim tempacct As String For Each irow In dspslips.Tables(0).Rows tempacct = irow("bipad") & irow("issuecode") Do While irow("bipad") & irow("issuecode") = tempacct Loop Next The problem is the internal do while loop is endless because I don't see a way to increment irow inside a while loop. If I change it to a for each loop with, say, a .select("bipad = etc) it doesn't change anything because it doesn't move the outer loop except by one (next), whereas I want to make it move to the endo of the bipad/issuecode combination. I know I can set the table initially to 'distinct', but you would think there'd be a better way. Tx for any help. Bernie Yaeger Tag: SQL Insert statement Tag: 60969

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

Top