AllowDBNull in DataTables

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
    - SqlDataReader Function From a VB6 refugee All, I'm trying to create a DataReader Function to call from within my class to open a simple datareader. I have thoroughly read the Data Access Patterns and practices and do not believe in using canned code right off the bat. I want to rebuild the canned code so I understand it, then I may use it or use my own understood version. The error message is in Error Text: Message="Invalid attempt to Read when reader is closed." '===================== This is call to function =================== Dim dr As SqlDataReader = Create_SQL_DataReader("20060213") While dr.Read() !!! Errs here End While '===================== End call to function =================== Shared Function Create_SQL_DataReader(ByVal sDate As String) As SqlDataReader Dim SQLConn As New SqlConnection(sConn) Using SQLConn ' This Builds SQL String into "s" and uses passed in param "sDate" Dim s As String = BuildShiftSQL(sDate) Dim Cmd = New SqlCommand(s, SQLConn) SQLConn.Open() Dim iDr As SqlDataReader = Cmd.ExecuteReader() Return iDr s = String.Empty SQLConn.Close() SQLConn.Dispose() End Using End Function Error Text: System.InvalidOperationException was unhandled Message="Invalid attempt to Read when reader is closed." Source="System.Data" StackTrace: at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout) at System.Data.SqlClient.SqlDataReader.Read() at WindowsApplication1.dataReaderDataSetForm.Main() in C:\Data\VBExpress\Parts\Parts\Form1.vb:line 20 at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args) at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args) at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly() at System.Threading.ThreadHelper.ThreadStart_Context(Object state) at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state) at System.Threading.ThreadHelper.ThreadStart() Tag: AllowDBNull in DataTables Tag: 127015
  - 2
    - ODP .net Dear All, i have establish a connection to Oracle 10g using ODP.net , i could populate data in a listbox/combo box, but i need to display these data in one by one , i mean navigation using |< < > >| these buttons , plshelp me blw is the code i'm using to populate data in combo/list box ----------------------------------------- dbconnection.getConnection() ' connection string written in a module calling her e Dim conn As New OracleConnection(oradb) Dim exp As Exception Try conn.Open() Dim sql As String = "select EMPNO,ENAME,mgr from emp where 1 =1 " Dim cmd As New OracleCommand(sql, conn) cmd.CommandType = CommandType.Text Dim dr As OracleDataReader = cmd.ExecuteReader() TextBox1.Text = dr.Read() MsgBox("1") ListBox1.Items.Add(dr.Item(1)) conn.Close() conn.Dispose() Catch exp MsgBox(exp.Message.ToString) End Try ------------------------------------------------ Tag: AllowDBNull in DataTables Tag: 127011
  - 3
    - Problem in creating sql database in visual web developer 2005 express Hi, When I go to Add New item->Sql database-> click ADD i get the following error.I also get the same error when i open the security page on web site admininstration tool. "There is a problem with your selected data store. This can be caused by an invalid server name or credentials, or by insufficient permission. It can also be caused by the role manager feature not being enabled. Click the button below to be redirected to a page where you can choose a new data store. The following message may help in diagnosing the problem: An error occurred during the execution of the SQL file 'InstallCommon.sql'. The SQL error number is 1802 and the SqlException message is: CREATE DATABASE failed. Some file names listed could not be created. Check related errors. An unexpected error occurred while checking the sector size for file 'U:\VISUAL STUDIO 2005\WEBSITES\APP9\APP_DATA\ASPNETDB_TMP.MDF'. Move the file to a local NTFS volume, where the sector size can be retrieved. Check the SQL Server error log for more information. Creating the ASPNETDB_1e007377194340a4850801591429f1bc database" Please help me-- Thanks Tag: AllowDBNull in DataTables Tag: 127009
  - 4
    - ObjectDataSource field binding to DataSet does not work I built a dataset with 11 tables and their relationships using the data set builder. I removed all the table adapters: the intention is to use the Enterprise Library to load the data, and to do the updates. That side of things works fine. The plan is to use ObjectDataSource to bind web controls to the tables in the data set. Unfortunately the data source does not show any of the fields in the table I am returning. The class I am linking to the data source has a bunch of parameter-less methods to return each of the tables in the data set. These are strongly typed, but the signature of my method is GetCompany(), returns DataTable, and I specify it as the Select method. I have verified that the method is called at run-time, but the details view does not appear, even with a manual bound field. How do I bind do a table in a dataset? Tag: AllowDBNull in DataTables Tag: 127004
  - 5
    - How to synchronise, grid and dataset update ??? Dear all, I have a strange behaviour when updating a grid. I will try to explain clearly: I have a CustDataSet object fill with customer information like Name, Firstname, Country, adress That Custdataset contains all my customer information. Then I have a web page on which my user can select only customers from a particular country. For that i create a Dataview object from CustDataset. I set the RowFilter to get only the desired country. Then I bind that DataView to my DataGrid. If CustDataset contains information from that country, then they get properly displayed in the grid. At this stage, my user is able to edit records from that dataGrid bind to DataView or Add a new entry. For edition an Edit, Upadate template column has been added and in the EditCommand event I have following code : dgLabData.EditItemIndex = e.Item.ItemIndex dgLabData.DataSource = dataView dgLabData.DataBind() Then my user gets text box field to update data directly from grid. When pressing the Updade link, current records need to be update and store in custDataset. In the grid UpdateCommand I retrive cells value as follow: newData(0) = CType(e.Item.Cells(1).Controls(0), TextBox).Text newData(1) = CType(e.Item.Cells(2).Controls(0), TextBox).Text newData(2) = CType(e.Item.Cells(3).Controls(0), TextBox).Text dgLabData.EditItemIndex() = -1 dgLabData.DataSource = dataview dgLabData.DataBind() After this I did not get display any changes made, or new row added but always the previous initial state. How to synchrosise changes made in the dataGrid, dataview, dataset object in order that it reflect changes when DataBind is called ?? I am lost, thnaks for your help serge Tag: AllowDBNull in DataTables Tag: 127003
  - 6
    - <%><%><%><%>HOW TO BE FUNNY!<%><%><%><%> You too can learn to be funny in just 7 days flat: http://www.ExpertHumor.com/HowToBeFunny.htm *** Free account sponsored by SecureIX.com *** *** Encrypt your Internet usage with a free VPN account from http://www.SecureIX.com *** Tag: AllowDBNull in DataTables Tag: 126999
  - 7
    - >@>@>@>@>GET MORE CHICKS............LEARN GUITAR............. Chicks dig guitar players. So get more chicks! Learn how to play guitar! http://www.ExpertHumor.com/LearnGuitar.htm *** Free account sponsored by SecureIX.com *** *** Encrypt your Internet usage with a free VPN account from http://www.SecureIX.com *** Tag: AllowDBNull in DataTables Tag: 126998
  - 8
    - Amnesty for illegal immigrants will not happen this time..................... Our politicians lead the league in all talk, no action. Is blowing us some sunshine good enough? Read what The Expert thinks: http://www.ExpertHumor.com/ *** Free account sponsored by SecureIX.com *** *** Encrypt your Internet usage with a free VPN account from http://www.SecureIX.com *** Tag: AllowDBNull in DataTables Tag: 126997
  - 9
    - Will illegal immigrants get deported?.........Of course not..................... Our politicians just wanted to blow us some sunshine. Get the details from The Expert: http://www.ExpertHumor.com/ *** Free account sponsored by SecureIX.com *** *** Encrypt your Internet usage with a free VPN account from http://www.SecureIX.com *** Tag: AllowDBNull in DataTables Tag: 126995
  - 10
    - Illegal Immigration, the Non-Issue of the Week?????????????? The way our politicians rushed to talk about illegal immigration this week, maybe they thought it came with a free lunch. They won't do anything about it, but it will get them some money. The Expert provides the details at: http://www.ExpertHumor.com/ *** Free account sponsored by SecureIX.com *** *** Encrypt your Internet usage with a free VPN account from http://www.SecureIX.com *** Tag: AllowDBNull in DataTables Tag: 126994
  - 11
    - can't connect to sql server using ip number My code: Dim IP As String IP = "192.168.0.169" SqlConnection1.ConnectionString = "workstation id=xxxxx;integrated security=SSPI;Data Source=" & IP & ";persist security info=False;initial catalog=adp1SQL" The error occurs when I use the IP address instead "mshome". when IP="MSHOME" the code runs smoothly. while in the development stage, this project is only instaled in the PC where MSDE 2000 is installed. Could this be the problem? Tag: AllowDBNull in DataTables Tag: 126991
  - 12
    - changing key field in Data Form Wizard form I created a form using the Data From Wizard. It lets me change and update all the fields except the primary key. When I modify the pk textbox, it throws this exception: "Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints." The row count on the form in incremented by 1 and everything is messed up until I restart the application. Any ideas? Thanks. Tag: AllowDBNull in DataTables Tag: 126988
  - 13
    - IndexOutOfRangeException - Index was outside the bounds of the arr We are encountering an extremely odd issue. We process incoming files for customers and output text files to produce printed material. Then an XML file containing the fields of the original file along with all fields necessary to produce the output text files are saved as an XML file. If an individual's printed material is destroyed in the process of fulfillment, we must reproduce the output for them. We do this by reading the XML file back into a dataset and re-outputting the text files. The XML file (and hence the datatable in the dataset) have a LOT of fields/columns (some have over 1000). We discovered that if a column is added to the datatable at the end of the columns collection, AND is later referred to in a SELECT clause to return a row collection, AND then large amounts of columns are removed and then added back into the columns collection, the following error is thrown: System.IndexOutOfRangeException - Index was outside the bounds of the array. We also discovered that if we added the column referred to in the SELECT clause at the BEGINNING of the columns collection (which was not easy) the error did NOT occur. However, this is simply side-stepping the bigger issue. I have sample code that will duplicate this behavior, but that and the XML is too long to post. Is there some way to post this code and XML? This is running on .NET 1.1 framework. Any light that could be shed on this issue would be greatly appreciated. Thank you. Tag: AllowDBNull in DataTables Tag: 126986
  - 14
    - Concurrency violation trying to update a table in SQL Server I have an intermittent error where I get the following exception: "Error: Concurrency violation: the UpdateCommand affected 0 records." Here are the steps I follow: Create SQLConnection Set connection string of the connection. Open connection Create SQL Data Adapter with the connection and a simple select statement including an indexed column Create SqlCommandBuilder on this adapter Fill DataTable with the Adapter Loop through each datarow and based on contents of a field, possibly update another field in the same row Call If Not tbl.GetChanges Is Nothing Then adapter.Update(tbl) This last step is where the error occurs. I am the only user of the system so there are no concurrency issues. When I tried to run profiler while executing, the problem went away and I no longer seem to be able to reproduce this problem on that machine. Any help would be appreciated. Thanks in advance, Boris Zakharin Tag: AllowDBNull in DataTables Tag: 126985
  - 15
    - How to loop through rows of strongly typed dataset table In vb.net 1.1 I could do this: Dim ds As dsEquip = GetDataset() Dim dr As dsEquip.tbEquipRow For Each dr In ds.tbEquip.Rows 'Do something Next now in c# 2.0 I'm trying to do the same thing like this: dsEquip ds = oData.PersonGetRecShort(); foreach (dsEquip.tbEquipRow dr in (dsEquip.tbEquipRow)ds.tbEquip.Rows) { } I get a compile error saying: Error 1 Cannot convert type 'System.Data.DataRowCollection' to 'ClientDataLayer.dsEquip.tbEquipRow' F:\Data\Sample Projects\WWF\myFormState\BusinessLayer\Persons_BLL.cs 53 63 BusinessLayer Using c# 2.0, what's the correct way to loop through all the rows in ds.tbEquip.Rows? Thanks. Tag: AllowDBNull in DataTables Tag: 126983
  - 16
    - XML string to SqlDataReader for SQL Server 2005 CLR Stored Procedure I need some help getting an xml string into a SqlDataReader and I am stuck. (I admit I'm no guru at this stuff.) I can easily get it into a DataSet object, but the SqlContext.Pipe.Send() method needs a SqlDataReader object as a parameter. (I don't want to send it as an XML string.) Here is my method so far...any clues and code samples would be greatly appreciated...I'm a visual learner. =) [Microsoft.SqlServer.Server.SqlProcedure] public static void GetAPList2() { // allow https access System.Net.ServicePointManager.CertificatePolicy = new AirWave.TrustAllCertificatePolicy(); string webURL = "https://webservice.somesite.com/ap_list.xml?id=47"; CredentialCache sec = new CredentialCache(); sec.Add(new Uri(webURL), "Basic", new NetworkCredential("*****", "*****")); WebClient client = new WebClient(); client.Credentials = sec; string pageHTML = Encoding.UTF8.GetString(client.DownloadData(webURL)); Byte[] pageData = client.DownloadData(webURL); string pageHtml = Encoding.ASCII.GetString(pageData); DataSet xmlDS = new DataSet(); XmlTextReader rdr = new XmlTextReader(pageHTML, XmlNodeType.Document, null); xmlDS.ReadXml(rdr); SqlDataReader dr = /* STUCK HERE!???? */ SqlContext.Pipe.Send(dr); } Tag: AllowDBNull in DataTables Tag: 126982
  - 17
    - How can I determinate if the Database exist before detach it? I use the code below to detach a Database, but if the database does not exist, it will thow exception. How can I determinate if the Database exist before detach it? *********************************************************************** string sConnection = "Data Source=......"; string sCmdDetach="sp_detach_db 'Health'"; SqlConnection cnn = new SqlConnection(sConnection); cnn.Open(); SqlCommand cmd= new SqlCommand(sCmdDetach, cnn); try { cmd.ExecuteNonQuery(); } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { cnn.Close(); } Tag: AllowDBNull in DataTables Tag: 126980
  - 18
    - How could you explain this? Hello I've experienced a very low speed filling DataSet in program running on PocketPC and connected to SQL Server via Wireles Lan. I used SqlDataAdapter. Table basically consists of text fields of type nvarchar and has about 6000 rows. It takes about 5 minutes to fill DataSet using SqlDataAdapter.Fill. Then I wrote a stored procedure which created a temporary table inserting there fields from the initial table and converting them from nvarchar to varchar. And now from resultset of this procedure SqlDataAdapter fills DataSet 8-9 times faster! I've expected 2 times increase in better case. Why is there such increase of speed? Maybe because SqlDataAdapter or DataSet makes something like a re-encoding when filling and there is a way to avoid this not converting nvarchar to varchar? Tag: AllowDBNull in DataTables Tag: 126974
  - 19
    - Trouble entering empty date into Access table from ASP.NET app using ADO.NET Hi, I have an ASP.NET app that uses ADO.NET for data access. I have a form that allows a user to enter a date. This date field is not required however. If they don't enter it, I shove a variable declared of type System.DateTime that is initialized to null into the database. It works but if you open the table in Access, the date field contains 12:00 AM. When this value is rendered in an Access form, it shows as 12/30/1899. What can I do to basically insert null/nothing into that field? I'm using the System.Data.OleDb library and a parameterized query to insert this date data into the Access table. I tried checking for an empty date and substituted the date parameter value with DBNull.Value but that doesn't seem to remedy the situation. Thanks for the help, Josh Blair Evergreen, CO Tag: AllowDBNull in DataTables Tag: 126972
  - 20
    - Why won't Dataset reflect changes? I have a single page application that uses Web Service to fill a dataset decending. When I add a new record to the database and re-fill dataset it won't reflect the first record added until after I've added a second one. Like wise the second record added won't show in dataset until a third is added. I've read 44 pages of this forum and tried about every solution to dataset updating that I found. DataAdapter.Update, dsxxxx.AcceptChanges, totally killing the dataset and recreating and still I get this same effect. I do not use any caching or states on this page. To check if it was the Web Service I added a direct sqlDataAdapter to the page along with another dataset and these 2 preformed exactly the same as Web Service supplied dataset. I know the record is being written as I can time stamp the page post-back and look at the SQL table and times are same down the 10ths of a second. I'm using VS2003 with SQL2005 with C# coding. Any idea's I would be willing to try. Thanx Tag: AllowDBNull in DataTables Tag: 126971
  - 21
    - Nesting ambient transactions.. doable? im trying to put a transaction around my NUnit tests fixtures and a seperate transaction around each test and I'm trying to do that in my base class so that all my test can benefit from them. The problem is that I can't find a way to do both transactions. it's either around the whole fixture or around the test. is there a way to do both? here is my base test class: using System; using System.Transactions; using NUnit.Framework; namespace Library.Tests { public enum TestTransactionMode { None, AroundFixture, AroundTests } [TestFixture] public abstract class BaseFixtureWithTxRollback : BaseFixture { Transaction fixtureTransaction; Transaction testTransaction; private TestTransactionMode testTransactionMode = TestTransactionMode.AroundFixture; /// <summary> /// The transaction mode of the test /// </summary> public TestTransactionMode TestTransactionMode { get { return testTransactionMode; } set { testTransactionMode = value; } } [TestFixtureSetUp] public override void TestFixtureSetUp() { base.TestFixtureSetUp(); if (testTransactionMode == TestTransactionMode.AroundFixture) { fixtureTransaction = new CommittableTransaction(); Transaction.Current = fixtureTransaction; } } [TestFixtureTearDown] public override void TestFixtureTearDown() { if (fixtureTransaction != null) { fixtureTransaction.Rollback(); } } [SetUp] public override void TestSetUp() { base.TestSetUp(); if (testTransactionMode == TestTransactionMode.AroundTests) { testTransaction = new CommittableTransaction(); Transaction.Current = testTransaction; } } [TearDown] public override void TestTearDown() { base.TestTearDown(); if (testTransaction != null) { testTransaction.Rollback(); } } } } So basically i want to have another TestTransactionMode.AroundTestAndFixture. I found SubordinateTransaction class which takes ISimpleTransactionSuperior in its constructor but there is no implementation for this interface. Tag: AllowDBNull in DataTables Tag: 126960
  - 22
    - How can i write an object to db? I get an e-mail in C# I want to store this e-mail item in SQL Server 2005. I don't have any hardcopy of this mail item. I only have a programmatic C# object, and i wish to insert it into a varbinary field. How can i convert this object to a suitable form to insert to db? Tag: AllowDBNull in DataTables Tag: 126953
  - 23
    - Join two Datatables I have two datatables in a dataset with a relation defined. How do I generate a JOINed view of this data? Thanks, MS Tag: AllowDBNull in DataTables Tag: 126949
  - 24
    - Empty Parameter Hello, I have sql command like: "Select... Where myColumn = @myParameter" Now, I can simply add parameter to the collection and filter data. What if I want to return not filtered data, I mean to skip, avoid executing this "myColumn = @myParameter"?! Is this possible, or I have to change select command every time I don't want filtered data? Tag: AllowDBNull in DataTables Tag: 126945
  - 25
    - Update Database records ?? Dear all, I have a dataset object which as been filled from an SQL server table I have then a dataview from that dataset,which is bind to a datagrid object. My user can edit, update, delete entry directly from that datagrid through an ASP page. Is there a simple way to automatically handle a database object based on the rowstate of the datagid ? What I mean is that : - if my user add an entry I should run a proper INSERT querry - if my user Update an entry I should run a proper UPDATE querry - if my user add an entry I should run a proper DELETE querry Is there any self intelligent function which is able to read the status of Rows and execute proper statement. I could also use the GetChange function to build a new dataset and then handle th select .. Any tips thnaks for your help regards serge Tag: AllowDBNull in DataTables Tag: 126943
- Next
  - 1
    - Is there a way i can debug through the Sql script in my SqlDataSource .... Hello, I'm very excited about the new SqlDataSource in asp.net 2.0. I have written a wuite long sql script on my SqlDatasource UpdateCommand. I want to see wheater it actually does what it was meant to do. Is there any way i can do this? Many thanks JJ Tag: AllowDBNull in DataTables Tag: 126937
  - 2
    - Connecting to SQL 2005 Express over a network share Hello, To my amazement I cannot connect to a SQL 2005 Express over a network share. Now reading through the newsgroups I can understand why this can be a problem BUT... HOW does one go about having two PCs read/write data to a centralized SQL 2005 Express Database? Any help would be GREAT! Phil Tag: AllowDBNull in DataTables Tag: 126934
  - 3
    - Threads and Transaction Problems.... After four long hours of googling on the subject and finding almost nothing, I'm forced to post this one. I'm doing a small project that reads several IIS and stores in a database the domains hosted there. This application runs quite fine. But I'd like to add some transaction control on it, because it might ab-end at any given time (so far it didn't, but I think it's sheer dumb luck :-) Stripped out of anything meaningful code, my approach to create the transaction is: ---- BEGIN pseudoCode ---- Imports System.Threading Private ThreadCount As Integer = 0 Sub Main() For Each s as Server In Farm Interlocked.Increment(ThreadCount) Do While Not ThreadPool.QueueUserWorkItem(AddressOf WorkerThreadForServer, s) Thread.Sleep(100) Loop Next Do While (ThreadCount <> 0) Thread.Sleep(100) Loop End Sub Sub WorkerThreadForServer(s as object) Dim dbConn as SqlConnection Dim dbTrans as SqlTransaction Try dbConn = new SqlConnection dbConn.QueryString = QUERYSTRING dbConn.Open() dbTrans = dbConn.BeginTransaction() For Each w as WebServer in s InsertDataInDB() Next dbTrans.Commit() Catch(ex as Exception) dbTrans.Rollback() Finally dbConn.Close() End Try Interlocked.Decrement(ThreadCount) End Sub ---- END pseudoCode ---- As far as I could gather on the net the above code SHOULD work fine, but it stumbles on the transactions. If I remove the transactions the code works fine, smoothly even. During the process of inserting the data on the server I dump some information on the screen and in a log file. When there's no transaction I see information being dumped from all servers at once, when the transactions are active, however, I see information of only one server and then it throws an TimeOutException while including the info. I've checked, rechecked and triple-checked the code and I can't see anything wrong. Can anyone help me? Regards, PJ http://pjondevelopment.50webs.com Tag: AllowDBNull in DataTables Tag: 126928
  - 4
    - Create XLS file from Query? (VB, OLEDB, ADO.NET 2.0) I need to create new Excel 2003 XLS files containing a single worksheet that has the results of a database query (a combination of text, integer, and date columns). Can someone please point me to an example of how to do this in a VB2005/ADO.NET 2.0 Winforms app, *WITHOUT* using Interop (Office will *not* be installed on the machine generating the file). Thanks! Tag: AllowDBNull in DataTables Tag: 126927
  - 5
    - How do i perform intersection in 2 datsets?? How do i perform intersection in 2 datasets having a same col. *** Sent via Developersdex http://www.developersdex.com *** Tag: AllowDBNull in DataTables Tag: 126910
  - 6
    - Datarelation query If i need to relate two tables using datarelation on a column "col1" out of which,table1 i want to make as parent table contains duplicate entries in "col1" and i want to retrieve all the rows present in table2 having matching rows on "col1" of only ("col1" and "col2") of table2. *** Sent via Developersdex http://www.developersdex.com *** Tag: AllowDBNull in DataTables Tag: 126909
  - 7
    - Data Access Layer design question Hi. I have a quick question. I am trying to create a quick data access layer for command CRUD tasks using stored procedures. My question is more of a design point of view. In each of my methods Create, Read, Update, and Delete, it seems I am repeating a lot of code such as: <Code> // Create the connection and Open it SqlConnection conn = new SqlConnection(connectionString); conn.Open(); cmd = new SqlCommand(storedProcedure, conn); // Set the type of command to a stored procedure cmd.CommandType = CommandType.StoredProcedure; // Add the SP parameters to the command cmd.Parameters.AddRange(parameters); </Code> What's the opinion on this? Should I create a connection in a method and pass it back or just repeat this chunk of code in each method? I read some articles that mentioned that when passing connection references around one needs to be careful to close connections, etc. Tag: AllowDBNull in DataTables Tag: 126903
  - 8
    - how to INSERT records to an OLEDB target ?? Hi I have a datatable with several field and, particularly, a binary field (byte[]) (taken from some database) using the OleDB connection. Now I want to INSERT all the record of this table into a newly CREATEd table of another database (say mysql or access or sybase or whatever: just assume it be an arbitrary OleDb connection). I am connected via the OleDb connection. My question. What is the best way to insert these records (I would like to be able to do the insert whatever be the target (always OleDb)). Any snippet would be much appreciated. I have already seen several posting on how to use PARAMETERS, @field, BUT my greatest concern is the binary field. If you could elaborate a little on the best way to upload the BINARY stuff, I would be grateful. I have no idea how to deal with it. Is it sufficient that I assign the byte array to the Value property of a parameter? Or would this not make any sense? -Pam Tag: AllowDBNull in DataTables Tag: 126902
  - 9
    - TableAdapter vs DataAdapter performance Hi, Thinking of the best way to develop a data acces layer for a new program I have been doing some performance test. My lab pilot is as follows: SqlServer 2000 + VS2005 TS in the same machine. Storage procedure returning 100.000 records from a table ----------------------------------------------- DataTable dt = new DataTable(); ArticulosTableAdapter ta = new ArticulosTableAdapter(); SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(sqlCommand);. Connected Model: ----------------------------- puno = Environment.TickCount; sqlCommand.Connection.Open(); SqlDataReader sqlDim = sqlCommand.ExecuteReader(); dt.L.oad(sqlDim); sqlDim.Close(); sqlCommand.Connection.Close(); time = TimeSpan.FromMilliseconds(Environment.TickCount - puno); Trace.Write("Load From DataReader Time-> "); Trace.WriteLine(time); Running: Load From DataReader Time-> 00:00:02.3440000 Disconnected Model: ------------------------------------ 1) TypedDataSet with DataAdapter puno = Environment.TickCount; da.Fill(_articulosDS); time = TimeSpan.FromMilliseconds(Environment.TickCount - puno); Trace.Write("Typed DataSet With DataAdapter Time-> "); Trace.WriteLine(time); Running: Typed DataSet With DataAdapter Time-> 00:00:00.5620000 2) TypedDataSet with TableAdapter puno = Environment.TickCount; ta.Fill(_articulosDS.Articulos); time = TimeSpan.FromMilliseconds(Environment.TickCount - puno); Trace.Write("Typed DataSet With TableAdapter Time-> "); Trace.WriteLine(time); Running: Typed DataSet With TableAdapter Time-> 00:00:02.6250000 I understand that filling a DataTable with a DataReader is a time consuming process that will take 2,34 secs for 100.000 records, but I do not understand why Filling a TableAdapter I am getting poor performance (3 times less) than Filling a DataAdapter. Any Idea ? Thank you José Ramón Tag: AllowDBNull in DataTables Tag: 126896
  - 10
    - Databinding Problem Hi, I am working on a small windows app that pulls data from a SQL Server table and binds the data to the controls for the windows form. One field in the SQL Server table has a data type of bit. I do allow Nulls on the field. This bit field in my dataset was bound to the check state of a checkbox. What was happening, is that if the field value was NULL then the form would not display any data in this control or any other control on the form and never generated an error. Does anyone know if this behavior is documneted anywhere, or if this same type of thing happens with databinding other kinds of controls? Thanks Tag: AllowDBNull in DataTables Tag: 126887
  - 11
    - Q311522 Is this issue fixed in VS.NET 2005 release ?? I have VS.NET Beta2 and still have problems with Columns in DataSets (designed with the designer) containing expressions involving relations (child/parent). Indeed when I call ds.GetChanges() it launches an exception saying that the relation is not initialized. Where can I find a list of fixes in VS.NET 2005 release from VS.NET Beta2 ?? Thanks ! Tag: AllowDBNull in DataTables Tag: 126886
  - 12
    - Oracle Lob (blob) Read / Write woes VS 2005, VB, .Net Frameword 2.0, Oracle 9i Database, System.Data.OracleClient I am trying to insert documents (word, pdf, images) into a BLOB data column in an Oracle 9i database using the System.Data.OracleClient. I have permissions on the database and the connection works to retrieve records. There appears to be very little help or documentation for using the System.Data.OracleClient in Visual Basic 2005 so I took a C# example from (.NET Framework Developer's Guide / Working with Oracle LOBs) and converted it to VB. I am getting stumped by the CreateTempLob function with an Oracle error (#01036: illegal variable name/number) when trying to execute the blobCmd.ExecuteNonQuery. Ugh. My code is below, again converted from C# to VB. Any suggestions greatly appreciated and eagerly attempted. Thank you. ***** BEGIN CODE CLASS ***** Imports System Imports System.Data Imports System.Data.OracleClient Imports System.Windows.Forms Imports System.IO Imports System.Collections.Generic Public Class BlobImports Public Sub DoBlob(ByVal blobPath As String, ByVal blobFilename As String, ByVal blobID As String) 'create connection Dim cnBlob As New OracleClient.OracleConnection("Data Source=database;Persist Security Info=True;User ID=user;Password=pword;Unicode=True") Try With cnBlob ' open connection .Open() 'create command object Dim cmdBlob As OracleClient.OracleCommand = cnBlob.CreateCommand() 'select text cmdBlob.CommandText = "SELECT BLOB_ID, BLOB_DESC FROM BLOBS WHERE BLOB_DESC = :pBlobDesc" 'add parameter cmdBlob.Parameters.Add(":pBlobDesc", OracleType.VarChar) cmdBlob.Parameters(0).Value = blobFilename 'read records ReadBlobRecords(cmdBlob) 'update command text for update cmdBlob.CommandText = _ "SELECT BLOB_DESC, BLOB FROM BLOBS WHERE BLOB_DESC = :pBlobDesc FOR UPDATE" 'update record with blob item - causes error in createtempblob function WriteBlob(cmdBlob, blobPath, blobFilename) End With Catch ex As Exception MsgBox(ex.ToString & vbCrLf & ex.Message, MsgBoxStyle.OkOnly, "Foo") Finally cnBlob.Close() End Try End Sub Private Sub ReadBlobRecords(ByVal blobCommand As OracleClient.OracleCommand) Dim blobTrans As OracleClient.OracleTransaction = blobCommand.Connection.BeginTransaction blobCommand.Transaction = blobTrans Dim blobReader As OracleClient.OracleDataReader = blobCommand.ExecuteReader() While blobReader.Read lbBlobs.Items.Add(blobReader.GetOracleString(1)) End While blobTrans.Commit() End Sub Private Sub WriteBlob(ByVal blobCommand As OracleClient.OracleCommand, ByVal blobPath As String, ByVal blobName As String) 'read file into binary Dim fi As New FileInfo(blobpath & "\" & blobname) Dim sr As New StreamReader(fi.FullName) Dim tempBuffer As String = sr.ReadToEnd sr.Close() 'begin transaction to post blobs Dim blobTrans As OracleClient.OracleTransaction = blobCommand.Connection.BeginTransaction blobCommand.Transaction = blobTrans Dim blobReader As OracleClient.OracleDataReader = blobCommand.ExecuteReader While blobReader.Read Dim oBlobFile As OracleClient.OracleLob = blobReader.GetOracleLob(1) ' call to create temp blob throws oracle error Dim inBlobFile As OracleClient.OracleLob = CreateTempLob(blobCommand, oBlobFile.LobType) Dim lngBlob As Long = oBlobFile.CopyTo(inBlobFile) blobTrans.Commit() End While End Sub Private Function CreateTempLob(ByVal blobCmd As OracleClient.OracleCommand, ByVal blobType As OracleClient.OracleType) blobCmd.CommandText = _ "DECLARE A " & blobType & "; BEGIN DBMS_LOB.CREATETEMPORARY(A, FALSE); :tmpBlob = A;" Dim pBlobTemp As OracleParameter = blobCmd.Parameters.Add("tmpBlob", blobType) ''pBlobTemp.Value = Nothing pBlobTemp.Direction = ParameterDirection.Output ' here is where the error is occuring blobCmd.ExecuteNonQuery() Return pBlobTemp.Value End Function ***** END CODE CLASS ***** Thank you again for assistance. -- JonSteng ~ Making Magic in Sunny Central Florida ~ Tag: AllowDBNull in DataTables Tag: 126883
  - 13
    - Binding of textboxes to a empty table Hi folks, I am looking for a solution of an "simple" problem, but I dont found it. I have a dataset with to tabels on it. On a Form i have a lot of TextBoxes to display the data from two database tables. The databinding of the TextBoxes is linked to two BindingSource-Objects (.NET 2.0). BindingSource1 connect to table1 at my dataset, BindingSource2 connect to table2. That works fine (changed data are updated at the database) if both tables are filled with rows. If table2 have no rows (no address of a person is known) it is possible to fill in data into the textboxes, but the data are not saved into the dataset (no row is created and filled). What is the best way to solve this? Is there any event of BindingSource? Table? (or what?) witch give me the information that a new rows is needed. Is it possible to force the BindingSource to create an new Row? Because of the checks at my dataset i need to set some columns (foreign key ...) by myself. Thanks in advance for any help. Niels Tag: AllowDBNull in DataTables Tag: 126881
  - 14
    - Exception message using EnterpriseLibrary.Data I'm getting an exception message using EnterpriseLibrary.Data block. In the profiler I can see that the sql i fired in the sql database. Imports Microsoft.Practices.EnterpriseLibrary.Data Dim db As Database = DatabaseFactory.CreateDatabase Dim cmd As DBCommandWrapper = db.GetSqlStringCommandWrapper(sql) Dim ds As DataSet = db.ExecuteDataSet(cmd) I'm getting the following ex.Message: "The type initializer for "Microsoft.Practices.EnterpriseLibrary.Data.Instrumentation.DataCommandFailedEvent" threw an exception." I'm not sure what this is all about! \M Tag: AllowDBNull in DataTables Tag: 126879
  - 15
    - DataReader Can anybody tell me how SqlDataReader and OracleDataReader process the encoding of contents from database when it's GetString(or GetSqlString,GetOracleString) method is called? I have been annoyed on this issue for several days and searched many materials but no help. -- Zhou Peng Tag: AllowDBNull in DataTables Tag: 126878
  - 16
    - Encoding about GetString method of DataReader Can anybody tell me how SqlDataReader and OracleDataReader process the encoding of contents from database when it's GetString(or GetSqlString,GetOracleString) method is called? I have been annoyed on this issue for several days and searched many materials but no help. -- Zhou Peng Tag: AllowDBNull in DataTables Tag: 126877
  - 17
    - CONNECTING TO EXTERNAL DATABASE Hi, I'm trying to make a new connection to a external database, it's not local, so what is the direction I must write to connect to it? It is the www... or I must write the IP of the server where it is stored? Thanks! Ángel Tag: AllowDBNull in DataTables Tag: 126874
  - 18
    - Simple DataRelation question ??? Dear all, Does the way of creating a datarelation between a parent and a child based on more that one column , is going to create a FK first ?? thnaks regards serge Tag: AllowDBNull in DataTables Tag: 126871
  - 19
    - Store procedure vs Direct statement ??? Dear all, Is there any restriction or rules when you must you Store procedure call within application code compare to direct SQL statement. For sure the first reason is data security as all querry are executed on the server. But is there some cases where store procedure should be avoid ? For my case if data security is a must, the first thing would be to always use strore procedure Any comments are welcome Regards Serge Tag: AllowDBNull in DataTables Tag: 126862
  - 20
    - Columns from two tables in one datagridview row Hi, Im using Visual Studio 2005 and playing around with all the new ADO .Net 2.0 features, I am hoping that what I am chasing is now available. I am wanting to display an updatable row (don't need insert or delete) which displays columns from two tables which have a one to one relationship. The following is the sql query which produces exactly what I am after: SELECT d_client.client_id, d_client.client_status, d_client_members.member_id, d_client_members.phone_mobile FROM d_client LEFT OUTER JOIN d_client_members ON d_client.client_id = d_client_members.client_id WHERE (d_client_members.flag_primary_contact = 1) OR (d_client_members.flag_primary_contact IS NULL) I have run this query in sql server and it returns 1 row per client which is exactly what I want. In vs 2005 im not sure how to use this query correctly so instead I have set the relationship up between the two tables and used seperate select queries in each table in the dataset designer. When I set the datagridview to the d_client binding source, i get a parent row with the d_client information and then a child row with the d_client_members details. I know I am doing something wrong here but all the new features have me a little overwhelmed... Any help will be greatly apprecaited Cheers Paul Tag: AllowDBNull in DataTables Tag: 126852
  - 21
    - UniqueIdentifier Sql Data and Parameters Hi everybody, I have a column in one of tables in one of the sql server that has a data type of UniqueIdentifier. Now, I am supplying a value to used this column as filter to get specific record. Note: strCompId is in string datatype. [code] adoOleDbDataAdapter2.SelectCommand.Parameters.Add("@compid", OleDbType.Guid, Me.strCompId.Length, "COGUID").Value = Me.strCompId [/code] The code shown above causes a conversion error. There is no conversion from string to GUID. Is there? [code] adoOleDbDataAdapter2.SelectCommand.Parameters.Add("@compid", OleDbType.VarChar, Me.strCompId.Length, "COGUID").Value = Me.strCompId [/code] The code above returns that "@compid" parameter does not exist. How can I solved this? Thanks. den2005 -- MCP Year 2005, Philippines Tag: AllowDBNull in DataTables Tag: 126842
  - 22
    - ExecuteNonQuery returning -1 I have a stored procedure that adds a new record in the database and have another SP that updates a record. I used to call ExecuteNonQuery method on command object and it used to return 1 if method executed sucessfully. This was all fine and dandy in .Net1.1 Now in .Net 2.0 I use the same code and ExecureNonQuery returns -1 for both command objects. Any thing changed between 1.1 and 2.0 that I need to be aware of? This is causing a lot of code to break. Tag: AllowDBNull in DataTables Tag: 126840
  - 23
    - Storing wave files in database Hi, in my current project I need to record audio data analyse them and store everything in some sort of database. I have not much experience with databases except for some small tests with ADO and MS Access, MySql and Paradox. The goal is to record sample files for test speakers and store the audio data, name and gender of the speaker and finally some analysis results. All this would happen on a local computer Win2K/XP. Network access, if needed at all, would be limited to a few users. My questions: 1) Can anyone give some advice as to whether it is reasonable to store large amounts of binary data in a database, wave files in my case? Or should I store file paths to external wave files? 2) In case I use an MS Access database: is there a limit on how many data I can store in it? The number of records wouldn't be that large but the total amount of data could be well beyond 2 GB. 3) Are there any performance issues in reading/storing large amounts of binary data from a database compared to file access for external referenced files? How about MS Access in particular? 4) If all of the above is absolutely nonsense, does anyone have any experience what a good solution would be for this task? thanks for any comment, Nicolas Tag: AllowDBNull in DataTables Tag: 126839
  - 24
    - Very Strange: field value is not updated if you try to set it to its default value This is completely baffling me and I'm hoping someone can help me understand why this doesn't work. I'm updating existing rows in a MS SQL table by reading them into a datatable, modifying the values then updating the database using the update method of SqlDataAdapter. If I set a value of a field to the same value as the default value for that column the value resorts back to its previous value when I update the table. For example: The definition for Field1 is a varchar where Allow Nulls is false and the default value is ''. I have filled a datatable with the existing rows from my sql table. When I change Field1 to any value other than '' the data is saved correctly when I run the update method on the SqlDataAdapter. If I set the value of Field1 to '' the value is ignored and the previously saved value is saved. This is my select command: select Col_ID, Field1 from MyTable This my update command/parameter: sqlDA.UpdateCommand = new SqlCommand("update MyTable set Field1=@Field1 where Col_ID=@Col_ID", sqlCnn); sqlDA.UpdateCommand.Parameters.Add("@Field1", SqlDbType.VarChar, 50, "Field1"); sqlDA.UpdateCommand.Parameters.Add("@Col_ID", SqlDbType.Int, 4, "Col_ID"); To ensure that I wasn't completely insane, I watched the events in SQL Profiler and sure enough, my values were getting replaced with the original values. Following is the actual statement displayed in SQL Profiler. Notice the value for Field1 is being saved as SALEPR which is the original value: exec sp_executesql N'update MyTable set Field1=@Field1 where Col_ID=@Col_ID', N'@Field1 varchar(50),@Col_ID int', @Field1 = 'SALEPR', @Col_ID = 241 Since I was manually creating my update command I decided to try the same thing using the SqlCommandBuilder. Profiler reflected a far more complicated update command generated by CommandBuilder but the net result was the same; The sql statement was using the previously saved value, not my newly entered value. I should also point out that the RowState for the row was set to modified and I had not run the AcceptChanges method. I'm pretty sure that Profiler would not have displayed my update if either of these two conditions were different. I decided to check one more thing. I made sure that the last row in the datatable was modified to set Field1 to '' then ran the sqlDA.Update() and set a breakpoint on the line following the update then checked the value of the Field1 parameter and it was set to to 'SALEPR'. I also checked the value in the table row and it was still set to a blank string. One final thing to try... I added one more line of code immediately following sqlDA.Update() to update the last row only and it saved the Field1 value correctly as follows: sqlDA.UpdateCommand.Parameters["@Field1"].Value = ""; // no need to set Col_ID because it is already set to the Col_ID of the last row sqlDA.UpdateCommand.ExecuteNonQuery(); This concludes that my logic is sound, but why is the Update method screwing with my data? I'm starting to think there is a bug in the SqlDataAdapter in .NET Framework 1.1. mike Tag: AllowDBNull in DataTables Tag: 126838
  - 25
    - Error when trying to create new SQL Server login via ado.Net I get an error when I try to execute the following code using ADO.Net in VB.Net: conn.open sqlString="CREATE LOGIN test WITH PASSWORD = '1qaz2wsx'" command=new SqlCommand(sqlString) command.connection = conn command.ExecuteNonQuery() The error occurs when executing the ExecuteNonQuery line and is: "Line1: Incorrect syntax near 'LOGIN' " The database connection is connected to the master database of SQL 2005 Express using the sa account. This line executes just fine in Sql Server Studio Manger when connected as sa. Am I not allowed to create database logins via ado.net?!?! I need to for my application. Thx, Marcus Tag: AllowDBNull in DataTables Tag: 126836

I use a data adapter to fill a DataTable (that contains no row or columns)
with data from a table in my SQL Server Express database.

The columns that the DataAdapter creates in my DataTable all have the
correct datatype but each is created so that AllowDBNull = True. However,
the table in the SSE Database has columns where NULLs are not allowed.

Any idea why this information isn't making it from teh database to the
DataTable?

Whats more, if I try and change the change the columns in the DataTable
myself after the Fill() method is called, setting AllowDBNull = True seems
to have no effect. Why would this be?

TIA

Top

RE: AllowDBNull in DataTables by EltonW

EltonW
Fri Mar 31 20:22:01 CST 2006

Hi elziko,

DataAdapter basically only retrieves data and data type, and set
column'AllowDBNull to default true. You can change it to not allow null value
(false).

HTH

Elton Wang

"elziko" wrote:

> I use a data adapter to fill a DataTable (that contains no row or columns)
> with data from a table in my SQL Server Express database.
>
> The columns that the DataAdapter creates in my DataTable all have the
> correct datatype but each is created so that AllowDBNull = True. However,
> the table in the SSE Database has columns where NULLs are not allowed.
>
> Any idea why this information isn't making it from teh database to the
> DataTable?
>
> Whats more, if I try and change the change the columns in the DataTable
> myself after the Fill() method is called, setting AllowDBNull = True seems
> to have no effect. Why would this be?
>
> TIA
>
>
>

Top