DB Independence

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
    - DataSet constraints Hello, I am having a problem inserting into a dataset that is loaded from a view that does joins with other tables and includes required fields from those tables. For example, I might have a view that is an Orders view which does a join to a Contact table and contains the contact's last name (required field). Now, when I do an insert, I only want to insert the information pertaining to the Order, which is then sent back to the business object and processed with stored procedures. The problem is, when I create a new row, add the order information, and try to add the row into my table, I get an error saying that column 'Last' does not allow nulls. This is not desirable. I would like to send only necessary information, like the ContactID, back over the wire. The workaround I have found for this issue is to manually certain fields to allow DBNulls. This worked out ok for writing unit tests for my business object, but it is not an option for production code. I guess I would like a way to fill a dataset with the table/column schema, but without any further schema/constraints. My smart client already has a separate 'screen' schema that defines foreign key and required field constraints. Thanks for any suggestions! -Glenn Tag: DB Independence Tag: 111268
  - 2
    - Access With Mobile .NET When it developed in Embedded Visual Basic my database it was in Access and OK worked, now development in Visual Basic .NET (Smart Device Application) and do I need to occupy Access and don't I find any example of as making it, does somebody have an example or does know if Access can be in charge of with .NET to develop on mobile computers? THANK YOU Tag: DB Independence Tag: 111267
  - 3
    - Oracle database connection error For login screen program username and password are accepted anc check it from oracle database. The valid username and password entered. but I got the error "ORA-12154: TNS could not resolve service name". i am using provider for this connection. But the same oracle database can be connected thru power builder in the same machine. How to rectify the error? Thanks in advance S. Viswanathan Tag: DB Independence Tag: 111263
  - 4
    - ThreadAbortException from DbDataAdapter.Fill Hello, recently I have encountered with the strange issue concerning ThreadAbortException that throws DbDataAdapter.Fill (see below the stacktrace [1]). At the first time I suppose that this problem due to Command.Timeout property that was set too small, but to tell the truth it has the maximum value - 65534 and this exception throws after 10-15 minutes of execution of Stored Procedure. Also I've noticed that the problem completely relates to SQL server when it was really loaded (about 99% of processor usage). So my question is how can I detect what causes this exception (and how to prevent it) - if it is SQL server, is it possible to modify properties of SP execution timeout or something? All kind of information would be really helpful. Thanks! [1] Stacktrace: at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior) at System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) Tag: DB Independence Tag: 111262
  - 5
    - Yukon Question: FOR XML EXPLICIT Vs. FOR XML PATH Okay, this is a really simple question. This for xml explicit query --- SELECT 1 as Tag, NULL as Parent, G.GrandParentID as [GrandParent!1!GrandParentID], NULL as [Son!2!SonName] FROM GrandParent G WHERE G.GrandParentID IN (Select GrandParentID from Son) UNION ALL SELECT 2 as Tag, 1 as Parent, S.GrandParentID, LTRIM(RTRIM(S.SonName)) FROM GrandParent G, Son S WHERE G.GrandParentID = S.GrandParentID ORDER BY [GrandParent!1!GrandParentID], [Son!2!SonName] FOR XML EXPLICIT , ROOT('XML') Produces --- <XML> <GrandParent GrandParentID="1"> <Son SonName="Han" /> </GrandParent> <GrandParent GrandParentID="2"> <Son SonName="Darth" /> <Son SonName="Luke" /> </GrandParent> </XML> But when I try writing this same query as FOR XML PATH as - Select G.GrandParentID GrandParent/@GrandParentID, RTRIM(S.SonName) GrandParent/Son/@SonName FROM GrandParent G INNER JOIN SON S ON G.GrandParentID = S.GrandParentID For Xml Path('GrandParent'), Root('XML') - I get the following <XML> <GrandParent> <GrandParent GrandParentID="2"> <Son SonName="Luke" /> </GrandParent> </GrandParent> <GrandParent> <GrandParent GrandParentID="2"> <Son SonName="Darth" /> </GrandParent> </GrandParent> <GrandParent> <GrandParent GrandParentID="1"> <Son SonName="Han" /> </GrandParent> </GrandParent> </XML> How the heck do I tell SQL Server 2005 that all sons (Luke & Darth) with GrandParentID=2 are supposed to be nested into one element??? Thanks for ur help !!! - Sahil Malik [MVP] http://codebetter.com/blogs/sahil.malik/ Tag: DB Independence Tag: 111259
  - 6
    - <DataRowView> VERSUS <DataRowView.Row> Does anyone know why while working with drv (DataRowView) a) if I uses this: -------------------------------------------------------------------- drv.Row.Item("CODE") = 254 -------------------------------------------------------------------- would always make the row state "Modified" b) if I uses this: -------------------------------------------------------------------- drv.Item("CODE") = 254 -------------------------------------------------------------------- will sometimes keep the row "Unchanged" ==================================================================== Just to make clear, I do not call .ApplyChanges anywhere and I do not run the a) and b) lines one after each other. I use them in the same loop separately and they do sometimes produce different results. Assigning the value to the DataRowView will always fail to change the state of the row to the "Modified" if the row is the first in the DataView. However if I use drv.Row to assign the value, it would change the state of the row to "Modified". does anyone have a clue why is it so? Genadij Tag: DB Independence Tag: 111258
  - 7
    - bound Datagrid and dataset - update??? Hi I am having a few problems with one of my forms... I have a data grid bound to a dataset using a currencymanager and it's all working fine. However when i edit or change any of the data in the Datagrids columns/rows i need to call update so the changes are saved back to my database, this is where the problems being. To catch the changed event of my data i have added the following events based on the Dataset Class... CBDataset.Tables["tbltest"].ColumnChanging += new DataColumnChangeEventHandler(DGCB_ColumnChanging); CBDataset.Tables["tbltest"].ColumnChanged += new DataColumnChangeEventHandler(DGCB_ColumnChanged) ; the column changing event is used to validate what has been entered and the column changed event i use to run my update command. When a cell is changed in my data grid both events fire however my update command returns 0 'the state has not changed to modified'?? until i navigate either up or down rows in the grid?? why is this and what can i do to get it to update without navigating up/down rows in the grid before the data has a rowstate of modified! Regards Darryn Tag: DB Independence Tag: 111256
  - 8
    - StreamWriter/StreamReader hey guys, i have a prg that writes a flat text file with specific (spacing) whitespace between the elements. that works fine. but i was curious: when i use streamReader to read that back and say put into a listbox, the spacing (formatting) goes away. does anyone have any insight on how to keep the formatting on this? here's how im reading it. Dim sr As New StreamReader("C:\MyFile.text") Dim line As String line = "" Try While (sr.Peek > -1) line = sr.ReadLine lstOne.Items.Add(line) End While Finally sr.Close() End Try thanks again rik ********************************************************************** Sent via Fuzzy Software @ http://www.fuzzysoftware.com/ Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources... Tag: DB Independence Tag: 111252
  - 9
    - Ado.NET BUG Hi, I use OleDbDataReader to execute an MDX query. While iterating on the reader, I get the following error: "The provider could not determine the Object value. For example, the row was just created, the default for the Object column was not available, and the consumer had not yet set a new Object value." I sometimes also get this error when I use OleDbConnection.GetOleDbSchemaTable. HELP! Thanks in advance. Tag: DB Independence Tag: 111249
  - 10
    - Just $19.95 to start -HOME BUSINESS HERE'S THE DIFFERENCE... · Just $19.95 to start a HOME BUSINESS · Just $19.95 a month to keep it going · Anybody can do that · That's a 1/2 a tank of gas · Not a financial decision If you want more information, please reply this e-mail with your contact( name+e-mail )with the subject: MORE INFO Tag: DB Independence Tag: 111246
  - 11
    - Filling a table using multiple adapters hi, I have a dataset with 2 tables(Header and Detail) I am using 10 adapters to fill the dataset(5 for Header and 5 for detail). All the adapters are having same columns(schema) as the tables in the dataset. While filling the dataset using the adapters one by one, i am not getting the expected data. First adapter is filling the table with all the data, second adapter is filling the table with two row(expected is 11 row) and so on.. but if i am filling the dataset table with only one adapter at a time then i am getting the expected result. i am adding the code wich i am using to fill the dataset. thank you Naison dsDayBook.Clear(); daGVHeader.Fill (dsDayBook.Header); daGVDetail.Fill (dsDayBook.Detail); daPVHeader.Fill (dsDayBook.Header); daPVDetail.Fill (dsDayBook.Detail); daRVHeader.Fill (dsDayBook.Header); daRVDetail.Fill (dsDayBook.Detail); daPIHeader.Fill (dsDayBook.Header); daPIDetail.Fill (dsDayBook.Detail); daRIHeader.Fill (dsDayBook.Header); daRIDetail.Fill (dsDayBook.Detail); grid.DataSource = dsDayBook; grid.DataBind() ; Tag: DB Independence Tag: 111242
  - 12
    - How to insert data form Xml into database I have a xml files, there are many record in it. Can I insert these record into database at once, or I need to insert it record by record? Tag: DB Independence Tag: 111236
  - 13
    - How to access a huge xml document I always used XmlDocument to load a xml file into memory and to access it. But if the xml is very huge (Say over 500MB) How can I access it? Tag: DB Independence Tag: 111234
  - 14
    - Return always 0 I am trying to find out why my return from my ASP.Net page is always 0. I have the following code: **************************************************** Dim objCmd as New SqlCommand("AddNewResumeCoverTemplate",objConn) objCmd.CommandType = CommandType.StoredProcedure objCmd.parameters.add("@ClientID",SqldbType.VarChar,20).value = session("ClientID") objCmd.parameters.add("@Email",SqlDbType.VarChar).value = session("Email") objCmd.parameters.add("@ResumeTitle",SqlDbType.VarChar,45).value = ResumeTitle.Text objCmd.parameters.add("@Resume",SqlDbType.text).value = ResumeText.Text objCmd.parameters.add("@CoverLetterTitle",SqlDbType.VarChar,45).value = ResumeTitle.Text objCmd.parameters.add("@CoverLetter",SqlDbType.text).value = CoverLetter.Text objCmd.Parameters.Add("ReturnValue", SqlDbType.Int).Direction = ParameterDirection.ReturnValue objConn.Open() Dim applicantReader = objCmd.ExecuteReader while applicantReader.Read() if applicantReader("ResumeID") is DBNull.Value then trace.warn("ResumeID = nothing") trace.warn("ResumeID = " & applicantReader("ResumeID") ) else trace.warn("ResumeID <> nothing") end if end while trace.warn("Error return = " & Convert.ToInt32(objCmd.Parameters("ReturnValue").Value)) ***************************************************************************** The stored procedure essentially looks like: ***************************************************************************** CREATE PROCEDURE AddNewResumeCoverTemplate ( @ClientID varChar(20),@Email varChar(45),@ResumeTitle varChar(45),@Resume text,@CoverLetterTitle varChar(45),@CoverLetter text ) AS declare @errorCode int ... select @errorCode = 1 return @errorCode GO ****************************************************************************** I put the select statement there just to force @errorCode to be 1. But my pages trace.warn is showing it as 0 (always). If I use an ExecureNonQuery, it comes back 1 (as it should) At first I used an "if" statement for the applicantReader.Read() and that didn't work. I remember someone mentioning that a DataReader has to read all the data before it will sent the return value. That was why I changed it to "while". But that still sends me a 1. Do I have it set up correctly? Thanks, Tom Tag: DB Independence Tag: 111226
  - 15
    - SQLDataReader is slow - first time Hi Folks, I have a typical situation here. My application is a sequence of windows jobs (all are coded in C#). Now one of my jobs when run is supposed to fetch around 100,000 records from the SQLDataReader, the SP returns 100,000 records. So, this SP call from C# and then reading the data using DataReader are happening on one method say its "myMyMethod()". Every other part of the application is fast than the part where I am trying to read the data from the DataReader, it takes 20 min to read the data. I have the time printed before the start of reading data from the DataReader and immediately after I am done with reading data from the DataReader (that is how I know that it takes roughly 20 min). Now I am doing all that stuff that I need to do to have the DataReader behave fast like, I am using (this is inside myMyMethod()" ) : while (dataReader.Read()) { ..... object[] myObjectArray = new object[dataReader.FiledCount]; dataReader.GetValues(myObjectArray); //Now read each value and cast it to the right type and assign it to the // valueObject. myLongVal = (long)myObjectArray[0]; myStringVal = (string)myObjectArray[1]; ....... myObjectArray = null; } Now after this job is done (which takes rougly 20 min). Now I will start running my next job, which calls the same "myMyMethod()" which I said above. This job also is suppose to get almost the same amount of data as my Job-1 did. But now the same method runs in just 2 min (as opposed to 20 min in my previous job). If I somehow manipulate my data in the DB and run my job-2 first then again the "myMyMethod()", it takes 20 min now. I am just confused...can somebody help me. I have checked there is no memory leak or anything. I dont why the same SP call and the same DataReader is taking more time first time and second time it runs fast... Thanks in advance. Have a nice day. Regards, Kris Tag: DB Independence Tag: 111225
  - 16
    - Database backup/restore Is there any way to backup/restore an SQL Server database from .NET, using the System::Data::SQLClient namespace? Thanks Tag: DB Independence Tag: 111224
  - 17
    - DataAdaper.Fill question Hi all, with the sample below, if the query returns huge amount of data, will database returns the portion required by program or returns all data, then Fill methods filter unnecessary ones? int currentIndex = 0; int pageSize = 5; string orderSQL = "SELECT * FROM Orders ORDER BY OrderID"; SqlDataAdapter myDA = new SqlDataAdapter(orderSQL, nwindConn); DataSet myDS = new DataSet(); myDA.Fill(myDS, currentIndex, pageSize, "Orders"); -- WWW: http://hardywang.1accesshost.com ICQ: 3359839 yours Hardy Tag: DB Independence Tag: 111222
  - 18
    - Adding a row to a dataset I have a need in my application to add a dummy row to a dataset object after I have filled it with a call to the database. Trying to find a solution I thought that doing a dataset merge might work, however its doesn't seem to be. Following examples I found in some MSDN articles I came up with the following code: DataSet ds = new DataSet(); DataSet dsTemp = new DataSet(); DataRow dr; DataTable dt = new DataTable("tempTable"); DataColumn dc = new DataColumn("VALUE",Type.GetType("System.String")); dt.Columns.Add(dc); DataColumn dc1 = new DataColumn("TEXT",Type.GetType("System.String")); dt.Columns.Add(dc1); dr = dt.NewRow(); dr["VALUE"] = "test"; dr["TEXT"] = "test"; dt.Rows.Add(dr); dsTemp.Tables.Add(dt); dsTemp.AcceptChanges(); ds = functionThatReturnsData(); ds.Merge(dsTemp); return ds; In the above example, my expectations where that ds would contain both its data and the data from dsTemp. However it only contains the data returned from the db call. If I switch it around to: dsTemp.Merge(ds), dsTemp only contains its original data and nothing from ds. Am I just missing something here or am I way off base? thanks Tag: DB Independence Tag: 111212
  - 19
    - Redistributing - MSDE I want to develop an app that requires database management. I was initially planning on using an mdb and the Jet driver. However, MSDE offers more robust features. For some reason I always thought you could develop apps with MSDE and freely redistribute. However, I recently read that MSDE "is licensed for development purposes only". That would seem to indicate I can't redistribute. Anybody know? Thanks in advance. Tag: DB Independence Tag: 111209
  - 20
    - Need advice-VB.NET to C# transition it looks like i'm in a C# world here in houston,Texas and i'm gonna have to make the plunge after doing and really enjoying VB.NET with winforms and web forms for the last couple of years. yes, even picked up some javascript along the way. my question is (since there's no classes being held in this area @ the community colleges during the summer on this subj), does anyone have any suggestions for some really great books that'll make the VB.NET to C# transition any easier??? i'm really gonna miss my global variables. thanks for any advice rik ********************************************************************** Sent via Fuzzy Software @ http://www.fuzzysoftware.com/ Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources... Tag: DB Independence Tag: 111205
  - 21
    - CSV read problem using Jet OLEDB I am trying to read a CSV file using Jet OLEDB provider. The file (badimport.csv) looks like this: Name,Description,DefaultPrice_$_per_m3 S6,Structural sawlog MSG6,65 S8,Structural sawlog MSG8,75 S10,Structural sawlog MSG10,90 S12,Structural sawlog MSG12,120 Using an OLE DB connection the first column seems to be getting interpreted as a Decimal and not a string. All the records in the name field come in with the first character (i.e. S) truncated. My pared back code looks like this: using System; using System.Collections; using System.IO; using System.Data; using System.Data.OleDb; public class MyClass { public static void Main() { string csvFileName = "C:\\badimport.csv"; // break to debugger // System.Diagnostics.Debugger.Break(); DataTable csvTable = ReadFromCSVFile( csvFileName ); DataColumn nameColumn = csvTable.Columns[ "Name" ]; DataColumn descriptionColumn = csvTable.Columns[ "Description" ]; DataColumn priceColumn = csvTable.Columns[ "Price" ]; for ( int row = 0; row < csvTable.Rows.Count; row++ ) { try { DataRow csvRow = csvTable.Rows[row]; string name = GetAsString( csvRow, "Name"); string description = (string)csvRow[ descriptionColumn ]; int price = (int)csvRow[ priceColumn]; Console.WriteLine(" Row {0}: Name = {1}, Description = {2}, Price = {3}", row, name, description, price); } catch ( Exception e ) { throw new ApplicationException( "Error importing LogProductDefinition on line " + (row+2) + " of file '" + csvFileName + "': " + e.Message + e.StackTrace ); } } } private static System.Data.DataTable ReadFromCSVFile( string csvFileName ) { // can use a file-specific SCHEMA.INI file to control the importing process // specifically the MaxScanRows=0 entry forces the entire file to be scanned to determine the column types. // Format of this file is defined here: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcjetschema_ini_file.asp // if ( !File.Exists( csvFileName ) ) { throw new ApplicationException( " Cannot find CSV file : \"" + csvFileName + "\"." ); } string csvPath = Path.GetDirectoryName( Path.GetFullPath( csvFileName ) ); OleDbConnection connection = new OleDbConnection( "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + csvPath + ";" + "Extended Properties=\"Text;HDR=Yes;FMT=Delimited\"" ); return ReadDataTable( connection, Path.GetFileName(csvFileName) ); } private static System.Data.DataTable ReadDataTable( OleDbConnection connection, string tableName ) { connection.Open(); try { // Read all data into a data table. OleDbDataAdapter adapter = new OleDbDataAdapter( "SELECT * FROM [" + tableName + "]", connection ); System.Data.DataTable dataTable = new System.Data.DataTable( tableName ); adapter.Fill( dataTable ); return dataTable; } finally { connection.Close(); } } private static string GetAsString( DataRow row, string columnName ) { DataColumn column = row.Table.Columns[ columnName ]; if ( column == null ) { throw new ApplicationException( "Column '" + columnName + "' not found" ); } object content = row[column]; if ( content == System.DBNull.Value ) { return ""; // empty cell is OK } try { content = row[column]; if ( content.GetType() == typeof( System.String ) ) { return (string)content; } else { return content.ToString(); } } catch ( Exception e ) { throw new ApplicationException( "Unable to convert value \"" + content.ToString() + "\" in column \"" + column.ColumnName + "\" to a string. " + e.Message ); } } } If is set up a SCHEMA.INI file with the follwoing contents in the same directory the name column is read correctly: [badimport.csv] ColNameHeader=True MaxScanRows=0 Anyone have any odea why the first column is interpreted as a decimal? Thanks, John. Tag: DB Independence Tag: 111190
  - 22
    - Timeout on dataAdapter Update method I have run out of ways to determine the reason for a timeout error. The timeout is occuring on the line indicated with ==>. After completing the statement before, the status of the connection object(cn) is Open. After the timeout occurs and execution is transferred to the Catch block, the connection is closed. Also, during the time the Update method is executing, the CPU usage in Windows Task Manager is pegged. What can I do to determine the cause of this timeout? Thanks for the help, Lars Dim cn As New SqlConnection(ConnectionSettings.cnString) Dim daMaster As New SqlDataAdapter("usp_UnivUseCode_Sell_All", cn) Dim daDetail As New SqlDataAdapter("usp_UnivUseCodeMap_Sell_All", cn) Dim tblMaster As DataTable = ds.Tables(0) Dim tblDetail As DataTable = ds.Tables(1) daMaster.InsertCommand = Me.CreateInsertCommandUse(cn) daMaster.UpdateCommand = Me.CreateUpdateCommandUse(cn) daMaster.DeleteCommand = Me.CreateDeleteCommandUse(cn) daDetail.InsertCommand = Me.CreateInsertUpdateCommandMap(cn) daDetail.UpdateCommand = Me.CreateInsertUpdateCommandMap(cn) daDetail.DeleteCommand = Me.CreateDeleteCommandMap(cn) cn.Open() Try ' Submit the only new Master/Detail rows daMaster.Update(tblMaster.Select(Nothing, Nothing, DataViewRowState.Added)) ==> daDetail.Update(tblDetail.Select(Nothing, Nothing, DataViewRowState.Added)) ' Submit the only modified Master/Detail rows ... Catch ex As SqlException MessageBox.Show(ex) End Try cn.Close() Private Function CreateInsertUpdateCommandMap(ByVal cn As SqlConnection) As SqlCommand Dim cmd As New SqlCommand("usp_UnivUseCodeMap_Save", cn) cmd.CommandType = CommandType.StoredProcedure Dim pc As SqlParameterCollection = cmd.Parameters pc.Add("@CompID", SqlDbType.Int, 0, "CompID") pc.Add("@UseCodeFK", SqlDbType.Int, 0, "UseCodeFK") pc.Add("@UnivUseCode", SqlDbType.Char, 2, "UnivUseCode") Return cmd Tag: DB Independence Tag: 111187
  - 23
    - Using a cursor SQL server side Problem Hello All Thank you for any help you can provide! I have a long list of links with decscriptions titles etc in a SQL Server 2000 database. The basic problem is that I am attempting to use a data base cursor to to the proper starting point in a sorted view, get the next 10-or 20 records and return them. Problem is ony one record is being returned. Here is the code SQL ALTER PROCEDURE dbo.PS_GetLinks @start as int, @rcount as int AS declare @ACounter as tinyint declare @RecordCount as tinyint set @ACounter=1 set @RecordCount = (Select Count (id) FROM Selectlinks) Declare My_Cursor CURSOR SCROLL for SELECT URL, Title, description, @RecordCount as Rcnt FROM Selectlinks Open My_Cursor Fetch absolute @start FROm My_Cursor WHILE (@ACounter < @rcount) BEGIN fetch next FROM My_Cursor set @ACounter = @ACounter + 1 END Close My_Cusor Deallocate my_Cursor RETURN --ASP NET code below Dim Start As Int64 = 1 '(mPage * (10 - 9)) Dim rd As SqlClient.SqlDataReader Dim cmd As SqlClient.SqlCommand = con.CreateCommand Dim lnk As HyperLink Try cmd.Parameters.Add("@start", 1) cmd.Parameters.Add("@rcount", 10) cmd.CommandText = "PS_GetLinks" cmd.CommandType = CommandType.StoredProcedure con.Open() rd = cmd.ExecuteReader While rd.Read lnk = New HyperLink lnk.NavigateUrl = rd("URL").ToString lnk.Text = rd("title") centercontent.Controls.Add(lnk) centercontent.Controls.Add(New LiteralControl("<br>")) 'RecordCount = rd("rcnt") End While Dim nav As New MultiPage(mPage, 10, 340) centercontent.Controls.AddAt(0, nav.buildnav) 'Catch ex As Exception Finally If Not rd Is Nothing Then rd.Close() End If If Not con Is Nothing Then con.Close() End If End Try Tag: DB Independence Tag: 111185
  - 24
    - Why does setting the InsertCommand proprty of a Data Adapter fail? Hi; I am trying to do a simple operation of inserting a row into a database. I have set a data adapter to get data from the database to check for duplication. That works fine. When I try to use this data adapter to INSERT the data into the database, if there are no duplicate records, I get an exception when I try to set MyAdapter.InsertCommand.CommandText property. Can Anyone tell me why? Here is the code: Private Sub btnOK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOK.Click Dim sConnectionString As String = "Provider=Microsoft.Jet.OleDB.4.0;" & "Data Source=" _ & System.AppDomain.CurrentDomain.BaseDirectory & "BDCIN.mdb;" _ & "User Id=admin;" & "Password=" Dim conLigacao As New OleDb.OleDbConnection Dim daAdaptador As New OleDb.OleDbDataAdapter("Produtos", conLigacao) Dim dsProdutos As New DataSet Dim dsAlteracoes As New DataSet Dim sSQLProdutos As String Dim sSQLInsertProdutos As String = "INSERT INTO Productos (Ref, Nome) VALUES (1,'TESTE')" Dim elementos As Integer Try If Not IsNumeric(txtRefProd.Text) Then MessageBox.Show("O conteÃºdo da referÃªncia do Produto de ve ser nÃºmerico.", "Valor errado") txtRefProd.Focus() Else sSQLProdutos = "SELECT * FROM Produtos WHERE Ref =" & txtRefProd.Text conLigacao.ConnectionString = sConnectionString conLigacao.Open() daAdaptador.SelectCommand.CommandText = sSQLProdutos daAdaptador.Fill(dsProdutos) conLigacao.Close() If dsProdutos.Tables("Table").Rows.Count > 0 Then MessageBox.Show("A referÃªncia " & txtRefProd.Text & " jÃ¡ existe.", "ReferÃªncia jÃ¡ existente.") txtRefProd.Focus() Else Dim rowNovoRegisto As DataRow rowNovoRegisto = dsProdutos.Tables("Table").NewRow rowNovoRegisto("Ref") = txtRefProd.Text rowNovoRegisto("Nome") = txtNomProd.Text dsProdutos.Tables("Table").Rows.Add(rowNovoRegisto) dsAlteracoes = dsProdutos.GetChanges() conLigacao.Open() daAdaptador.InsertCommand.CommandText = sSQLInsertProdutos daAdaptador.Update(dsAlteracoes, "Produtos") conLigacao.Close() Me.Close() End If End If Catch ex As Exception MessageBox.Show(ex.ToString) End Try End Sub I tried this approach from a WROX book, but It does not work. The exception I get from the line "daAdaptador.InsertCommand.CommandText = sSQLInsertProdutos" is: System.NullReferenceException: Object reference not set to an instance of an object. at ManutenÃ§Ã£o_Intermix.IntroProduto.btnOK_Click(Object sender, EventArgs e) in C:\Documents and Settings\Pedro\My Documents\Visual Studio Projects\Estudo VB\ManutenÃ§Ã£o Intermix\IntroProduto.vb:line 162 Thanks for any help. Tag: DB Independence Tag: 111184
  - 25
    - convert true to 1 in asp.net application I bulk insert records using OPENXML, into table "myTable". which has the following field: column name : myCol dataType : bit .... ... .. myCommand.CommandText = "select myCol from myTable " adapter.SelectCommand = myCommand adapter.TableMappings.Add("Table", "myTable") adapter.Fill(dS) Dim tbl As DataTable = ds.Tables("myTable") for i=0 to 1 Dim NewRow As DataRow = tbl.NewRow() NewRow("myCol") = 1 'get converted automatically by the compiler to: true/false tbl.Rows.Add(NewRow) ' which generate the error. next Dim sb As StringBuilder = New StringBuilder(1000) Dim sw As System.IO.StringWriter = New System.IO.StringWriter(sb) Dim col As DataColumn For Each col In tbl.Columns col.ColumnMapping = System.Data.MappingType.Attribute Next ds.WriteXml(sw, System.Data.XmlWriteMode.WriteSchema) myCommand.Connection = cn myCommand.CommandType = CommandType.StoredProcedure myCommand.CommandText = "myStoredProcedure" myCommand.Parameters.Add(New SqlParameter("@xmldata", System.Data.SqlDbType.NText)) myCommand.Parameters(0).Value = sb.ToString() Dim a As String cn.Open() com.ExecuteNonQuery() cn.Close() 'error : Syntax error converting the nvarchar value 'true' to a column of data type bit explicit conversion of true/false to int return always "True"/"False" wording thanks for help Tag: DB Independence Tag: 111171
- Next
  - 1
    - Child Relationships If I create a child relationship between 2 tables, but the parent table has a value which is not found in the child table, then this will cause an exception....right? So how to I indicate that the parent/child relationship may or may not exists so that no exception is generated when that occurs? Tag: DB Independence Tag: 111170
  - 2
    - Updating typed dataset schema from code Repost using registered alias: I have an existing .xsd file with typed dataset information in it. I need the ability to update this existing schema information when a column is removed from one of the underlying data adapters. Currently I read the existing schema info into a temp dataset object, update the schema for the table in question by just calling MyAdapter.FillShema and passing it my temp dataset. I then write the schema file back to disk. This works great for column additions, the new columns are placed in the schema, but it does not change the schema if a column is deleted. I've been searching through the merge documentation, the MissingSchemaAction documentation, anything I can think of, but I'm at a loss. Any ideas or pointers in a new direction? TIA Tag: DB Independence Tag: 111166
  - 3
    - Error MSDAAB ExecuteDataset I'm confused on calling ExecuteDataset with on a stored procedure with a single parameter. The following code gets an error stating there is no Stored Procedure with the name of '4' (probably the value of the CommandType.StoredProcedure enumeration). Dim parm() = New SqlParameter(0) {} parm(0) = New SqlParameter("@CompID", SomeParm) Dim ds As DataSet = SqlHelper.ExecuteDataset( _ ConnectionSettings.cnString, _ CommandType.StoredProcedure, _ "usp_StoredProc", _ parm) However, if I change the call to: Dim ds As DataSet = SqlHelper.ExecuteDataset( _ ConnectionSettings.cnString, _ "usp_StoredProc", _ SomeParm) there is no error and the code works correctly. Can someone explain what I am missing here? TIA Lars Tag: DB Independence Tag: 111164
  - 4
    - DataGrid.DataSource undefined when trying to Sort I had something like the following working: !!!!!!!!!! !!!!!!!!!! Start of Code snipet!!!!!!!!!! !!!!!!!!!! <asp:DataGrid id="FormListDataGrid" runat="server" AutoGenerateColumns="False" ShowHeader="true" HeaderStyle-CssClass="header" AllowSorting="true" OnSortCommand="SortCurrentMonth_OnClick" HeaderStyle-Height="25px"> <Columns> <asp:HyperLinkColumn Runat="server" DataNavigateUrlField="FormName" DataNavigateUrlFormatString="ShowForm.aspx?form={0}" DataTextField="FormName" HeaderText="Form Name" SortExpression="FormName"></asp:HyperLinkColumn> <asp:BoundColumn DataField="Due Date" HeaderText="Due Date" DataFormatString="{0:d}" SortExpression="Due Date"/> <asp:BoundColumn DataField="Entity" HeaderText="Entity" SortExpression="Entity" /> </Columns> </asp:DataGrid> protected void SortCurrentMonth_OnClick(Object source, DataGridSortCommandEventArgs e) { ((DataView) FormListDataGrid.DataSource).Sort= e.SortExpression; FormListDataGrid.DataBind(); } !!!!!!!!!! !!!!!!!!!! End of Code snipet!!!!!!!!!! !!!!!!!!!! This aspx load up the DataGrid on page start up. However, I now created another ASPX for reporting, whose DataGrid is set to Visible=false initially, with the DataSource, etc. only set at the click of a button. Code-snippet as follows: ~~~~~~~~~~~~~~~Start of new Code snippet~~~~~~~~~~~~~~~ <asp:DataGrid id="ReportDataGrid" runat="server" AutoGenerateColumns="False" ShowHeader="true" HeaderStyle-CssClass="header" AllowSorting="true" OnSortCommand="SortReport_OnClick" HeaderStyle-Height="25px" CellSpacing="5"> <Columns> <asp:HyperLinkColumn Runat="server" DataNavigateUrlField="FormName" DataNavigateUrlFormatString="ShowForm.aspx?form={0}" DataTextField="FormName" HeaderText="Form Name" SortExpression="FormName"></asp:HyperLinkColumn> <asp:BoundColumn runat="server" DataField="Due Date" HeaderText="Due Date" DataFormatString="{0:d}" SortExpression="Due Date" /> <asp:BoundColumn runat="server" DataField="Manager" HeaderText="Last Manager" SortExpression="Manager" /> <asp:BoundColumn runat="server" DataField="DummyComments" HeaderText="Dummy Comments" /> <asp:BoundColumn runat="server" DataField="Status" HeaderText="Status" SortExpression="Status"/> </Columns> </asp:DataGrid> private void ReportButton_Click(object sender, System.EventArgs e) { string reportSQL = "SELECT * FROM forms"; //Render reportSQL now based upon selected criteria on the web form DataSet dataList = new DataSet(); OdbcConnection Conn = new OdbcConnection("Driver={Microsoft Access Driver (*.mdb)};DBQ=c:\\dev\\outstandingForms.mdb"); Conn.Open(); OdbcDataAdapter accessDataAdapter = new OdbcDataAdapter(reportSQL,Conn); accessDataAdapter.Fill(dataList); DataView oView= new DataView(dataList.Tables[0]); ReportDataGrid.DataSource= oView; ReportDataGrid.DataBind(); Conn.Close(); ReportDataGrid.Visible= true; } protected void SortReport_OnClick(Object source, DataGridSortCommandEventArgs e) { ((DataView) ReportDataGrid.DataSource).Sort= e.SortExpression; ReportDataGrid.DataBind(); } ~~~~~~~~~~~~~~~End of new Code snippet~~~~~~~~~~~~~~~ The issue now is whenever I try to sort something by clicking on the column heading, I get an error, which is caused by an un-initialised ReportDataGrid.DataSource! How could I rectify this? I don't really want to re-run the query and re-set the DataSource, re-create the SQL everytime, do I? "Kevin Yu [MSFT]" wrote: > Hi Patrick, > > I agree with Elton that you have to assign e.SortExpression to the sort > expression of the DataView. > > oView.Sort = e.SortExpression; > > Here is an example: > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/ > frlrfsystemwebuiwebcontrolsdatagridsortcommandeventargsclasssortexpressionto > pic.asp > > Kevin Yu > ======= > "This posting is provided "AS IS" with no warranties, and confers no > rights." > > Tag: DB Independence Tag: 111162
  - 5
    - Cannot Open Any more databases I am converting from ADO to ADO.NET, and have written a routine to get records from an Access database. It's a customer-order type program. It works fine until I run a 'batch job' which runs through many orders - I then get the message "Cannot Open Any more databases" (I presume this is refering to tables?). I've tried closing all connections, but it still doesn't work properly. I don't want to set the connections to nothing if I can avoid it, as they will then need to be reinitialised each time I call the function (shown below). Any help on this would be appreciated! Function PopDataTable(ByVal ods As DataSet, ByVal sTable As String, ByVal sSQL As String, Optional ByVal sdsSQL As String = "", Optional ByVal rowParent As DataRow() = Nothing, Optional ByVal sRelation As String = "", Optional ByVal bForceClearTable As Boolean = False, Optional ByVal oda As OleDbDataAdapter = Nothing, Optional ByRef ocn As OleDbConnection = Nothing, Optional ByVal ocomSel As OleDbCommand = Nothing) As DataRow() Dim rowR As DataRow Dim rowSelected() As DataRow Dim iRecCount As Integer Dim iSelCount As Integer Dim shChildCount As Short Dim shTabCount As Short PopDataTable = Nothing If IsNothing(ocn) Then 'use the generic connection ocn = cnGen End If If IsNothing(ocomSel) Then 'use the generic command object ocomSel = comSelGen End If iSelCount = 0 'See if the rows are there If Not IsNothing(ods.Tables(sTable)) And Not bForceClearTable Then If sdsSQL = "" Then rowSelected = GetChildren(rowParent, sRelation, shChildCount) Else rowSelected = ods.Tables(sTable).Select(sdsSQL) End If iSelCount = rowSelected.Length PopDataTable = rowSelected ElseIf Not IsNothing(ods.Tables(sTable)) And bForceClearTable Then 'Clear the dataset if necessary or required ods.Tables(sTable).Clear() End If 'if there are no records execute query If iSelCount = 0 Then Try ocn.Open() 'Set the SelectCommand string and connection... ocomSel.CommandText = sSQL ocomSel.Connection = ocn 'set the data adapter to use this information oda.SelectCommand = ocomSel 'Fill the dataset table iRecCount = oda.Fill(ods, sTable) shTabCount = ods.Tables.Count() If iRecCount > 0 Then If sdsSQL <> "" Then PopDataTable = ods.Tables(sTable).Select(sdsSQL) Else 'If there's no relationship, select the whole datatable 'as it's just been populated If RelationShipExists(ods, sRelation) Then 'PopDataTable = GetChildren(rowParent, sRelation, shChildCount) PopDataTable = ods.Tables(sTable).Select(sdsSQL) Else PopDataTable = ods.Tables(sTable).Select(sdsSQL) End If End If End If Catch eFillException As System.Exception MsgBox(eFillException.ToString) 'TODO: handle errors here Finally ocomSel.Connection.Close() ocomSel.Connection.ReleaseObjectPool() oda.SelectCommand.Connection.Close() oda.SelectCommand.Connection.ReleaseObjectPool() ocn.Close() End Try End If Return PopDataTable End Function Tag: DB Independence Tag: 111161
  - 6
    - Data Table Joins I have data from two different databases which I want to join together. If I create one Datatable with the results of one query and another Datatable with the results of the second query, can I somehow join the 2 Datatables to produce a result which will look like I had executed a single query to return a single datatable? Tag: DB Independence Tag: 111154
  - 7
    - DataGrid header apperance I have a DataGrid that support sorting in ASP.NET 1.1 as defined at the end of this post. The issue is that regardless of what "style" attributes I put in the <asp:DataGrid/> tag or the <asp:HyperLinkColumn /> tags, I don't seem to be able to change the actual CSS class for the generated <a/> hyperlink tags for the sort column links. How could I do this? (Note settin style in the header won't do, as default CSS styles for <a/> has already been defined.  <asp:DataGrid id="FormDataGrid" runat="server" AutoGenerateColumns="False" HeaderStyle-CssClass="header" AllowSorting="True" OnSortCommand="SortArchived_OnClick" HeaderStyle-Height="25px"> <Columns> <asp:HyperLinkColumn Runat="server" DataNavigateUrlField="FormName" DataNavigateUrlFormatString="ShowForm.aspx?form={0}&xoq=31" DataTextField="FormName" HeaderText="Form Name" SortExpression="FormName"></asp:HyperLinkColumn> <asp:BoundColumn DataField="Due Date" HeaderText="Due Date" DataFormatString="{0:d}" SortExpression="Due Date"/> </Columns> </asp:DataGrid>  Tag: DB Independence Tag: 111145
  - 8
    - Join on two data tables in same dataset I have one dataset. Filled one DataTable from dbase. Filled another DataTable using ReadXml() and Merge(). So now I have to DataTables that I want to Join on that exist in memory but are from disparate data sources. Can I Join on them with ADO.NET? Thanks. Tag: DB Independence Tag: 111132
  - 9
    - Dataset to XL Hi Is there any way to export to XL thru Dataset , or DataTable or Grid or any other controls , than manually writing to each cell in XL Renjith Tag: DB Independence Tag: 111131
  - 10
    - Dataview filtering problem (Type your message here) -------------------------------- From: vallabh naidu Hi everyone, I have a typical problem, I dont weather what i am writing is= correct or not but I just want to filter the dataview and here= goes the code but that code below is not filtering according to= the condition=2E=2E=2E eagarly waiting for a positive result from any= of you people=2E Dim myfilter As String =3D "Parent_Org =3D'" & e=2ENode=2ETag &= "'" Dim DVOrg As New DataView(dt, myfilter, "ORG_Cd",= DataViewRowState=2EModifiedCurrent) Dim dr As DataRow Dim SelNode As TreeNode =3D e=2ENode If DVOrg=2ETable=2ERows=2ECount > 1 Then For Each dr In DVOrg=2ETable=2ERows Dim Nd As New TreeNode Nd=2EText =3D dr("ORG_Cd") + " " + dr("UDOrg_cd") + "= " + dr("ORG_DESC") Nd=2ETag =3D dr("ORG_Cd") SelNode=2ENodes=2EAdd(Nd) Next End If ----------------------- Posted by a user from =2ENET 247 (http://www=2Edotnet247=2Ecom/) <Id>VZHkFM4KsE+m93C804jX4g=3D=3D</Id> Tag: DB Independence Tag: 111130
  - 11
    - ADO.Net 1.1 : SQL Insert ? How do I convert this ASP 3.0 to VB.Net coding? ASP 3.0 Coding ----------------- Set rs = Server.CreateObject("ADODB.Recordset") rsstatm = "SELECT * FROM F4111" rs.Open rsstatm, ConnSQL rs.movefirst do while not rs.eof rsAdd = "INSERT INTO F4111A (ILITM,ILLITM,ILAITM) VALUES" rsAdd = rsAdd & "( " & rs("ILITM")&"," rsAdd = rsAdd & "'" & rs("ILLITM") &"'," rsAdd = rsAdd & "'" & rs("ILAITM") &"' )" ConnSQL.Execute(rsAdd) Set rsAdd = Nothing rs.movenext loop rs.close Set rs = Nothing Set rsstatm = Nothing Please help. Many thanks. Tag: DB Independence Tag: 111129
  - 12
    - RowFilter Expressions with multiple relationships I am difficulty in filtering a dataTable with muiltiple FK relationships. I have a table tblContacts(name, cityID, companyID) which has FK relationships to 2 lookup tables tblCity and tblCompany created in the DataSet. When I build the DataView.rowFilter String, I use "Parent.cityName like 'San*' AND Parent.companyName like 'Uni*'", but I get an error "Additional information: Invalid variable: Parent.cityName" at run time. Is there a way to work around this error? Thanks. Tag: DB Independence Tag: 111115
  - 13
    - Statement separator for Oracle client provider Hi, I have a large portion of an application (actually some sort of middleware) that has been developed first for SqlServer and now I am in the process of making it accessible to Oracle as well. The problem is that I use DataSets where, for instance, the UpdateCommand contains an UPDATE sql statement followed by a semicolon and a SELECT sql statement, all of this in the same CommandText. In this way, and in one single round-trip to the server, I can modify and obtain a fresh copy of the record. With Oracle provider, this is not working at all since Oracle dislikes the ';' and throws an exception (invalid character...). I have tried this in ORACLE SQL Plus Worksheet and works fine if one places a newline after the ';', but my hopes were seriously affected when I tried it in ORACLE SQL Plus, where it does not accept the semicolon regardless of a following newline. Can somebody shed some light on this, please!! -- Thanks in advance, Juan Dent, M.Sc. Tag: DB Independence Tag: 111113
  - 14
    - ado and ado.net dissimilarities Salam, this is faisal and i want to know about vb.net Actually, i am a programmer of Visual Basic and used to work with ado in order to have connectivity with database, now i want to move on .Net api but the problem is that, i can understand the tools provided by the .net but unable to work with ado.net. Can any Please tell me all about this. ------------------------------- From: faisal khanzada ----------------------- Posted by a user from .NET 247 (http://www.dotnet247.com/) <Id>mTbt6M6UTEadruoUV2jBXw==</Id> Tag: DB Independence Tag: 111102
  - 15
    - Adding a Row With an AutoIncrement Field I am trying to add a row to a table in a dataset. The table contains an ID field, which is set up as an AutoIncrement field, with AutoIncrementSeed = 1 and AutoIncrementStep = 1. I like the convenience of the following syntax: MyDataset.Tables("MyTable").Rows.Add(New Object(){"data for field1", "data for field2", "data for field3"...}) but because the first field is an AutoIncrement field, I'm not sure what to put in place of the first field. I tried vbNull, but that doesn't help. It complains on the second add that the ID already exists. How do I deal with the AutoIncrement field in an add such as the one above? Thanks. Tag: DB Independence Tag: 111101
  - 16
    - Help! Typed Dataset across multiple databases (e.g. IDBDataAdapte Is there a way the strongly typed dataset wizard will create classes that are usable across multiple databases? We are writing an application that can use Oracle, PostgreSQL, or Sql Server as the database. We have setup a DAL (using IDBConnection, IDBDataAdapter, etc.) to facilitate database portability. We can now use the DAL for all our database functions, which is great. However, we can't figure out how we to utilize the auto-generated strongly typed dataset classes across multiple databases. Is it possible? Our app has 1000's of columns, so this would be a MASSIVE time saver vs. creating all the classes by hand or hand-writing all the SQL. Thanks, Josh Tag: DB Independence Tag: 111100
  - 17
    - How does Dataset.Relations work internally? Does ADO.NET internally sets up hashtables for the columns that are used to tie tables, so that later on hashtables can be used to quickly retrieve data? Or does it just loop through data to locate the ChildRows. For instance, in a line like this. ds.Relations.Add( new DataRelation(RELATION_BREAK_PRODUCT, tblBreak.Columns[FIELD_BREAK_ID], ds.Tables["Product"].Columns[FIELD_BREAK_ID])); Thanks. Tag: DB Independence Tag: 111097
  - 18
    - Load a string of CSV from memory into a dataset Hi Group, Here is what I'm trying to do in my application: 1. the customer uploads an encrypted file to the server (it's a ASP.NET application) 2. the uploaded file is decrypted to a comma separated list of values 3. the list is being written to a CSV file 4. the csv file is being loaded into a datatable, using OleDbDataAdapter 5. the recordset, after some checking and validation, is being written to a SQL database. Question: Is it possible to skip somehow the steps where I'm writing the comma separated list to a csv file and then reloading the list into a datatable? Something like loading the datatable directly from the comma separated string **in memory** ? Many thanks ! Andrei. Tag: DB Independence Tag: 111093
  - 19
    - Invalid Key node inside constraint error I have an XML document that I'm reading into a dataset. I programmatically created dataadapters and constraints for all the tables and relationships and I'm able to insert all the data into the database no problem. Instead of running the code to create the constraints all the time, I used WriteXMLSchema to write out an .xsd for me that has everything already defined. Now when I read this schema back using ReadXMLSchema, I get the error: Invalid 'Key' node inside constraint named: Arrests-Inv_Holding_Facilities .NET created this .xsd for me, why can't it read it in? I've made no changes to it manually. I've seen several messages with this same question out on the web, but no answers. Can anyone tell me what the problem is? Tag: DB Independence Tag: 111090
  - 20
    - Oracle Driver Behavior I am running the Oracle 9.02 driver in development, which is working fine with the following statement. #region Add Inbound Transaction // for valid requests, insert transaction into table: INBOUNDTRANSACTIONS // returns boolean indicating success public bool addInboundTransaction(string structureID, string msgIn, string status, string lastUpdatedBy, string originatingID, string hostSystem) { OleDbConnection cn = new OleDbConnection(DataSource); string sql = "INSERT INTO TABLENAME (TABLEROWID, STRUCTUREID, STATUS, LASTUPDATEDBY, MSGIN, ORIGINATINGID, HOSTSYSTEM) VALUES (IBTXN_SEQ.NEXTVAL, :1,:2,:3,:4,:5,:6)"; OleDbCommand cmd = new OleDbCommand(); cmd.CommandTimeout = CommandTimeOut; cmd.CommandText = sql; cmd.Connection = cn; cmd.CommandType = CommandType.Text; OleDbParameter p1 = cmd.Parameters.Add("strucID", OleDbType.VarChar,50); p1.Direction = ParameterDirection.Input; p1.Value = structureID.Trim(); OleDbParameter p2 = cmd.Parameters.Add("status", OleDbType.VarChar,50); p2.Direction = ParameterDirection.Input; p2.Value = status.ToUpper().Trim(); OleDbParameter p3 = cmd.Parameters.Add("lastUpdatedBy", OleDbType.VarChar,50); p3.Direction = ParameterDirection.Input; p3.Value = lastUpdatedBy.ToUpper().Trim(); byte[] ba = stringToByteArray(msgIn); OleDbParameter p4 = cmd.Parameters.Add("blobData", OleDbType.Binary, ba.Length); p4.Direction = ParameterDirection.Input; p4.Value = ba; // the ip/dns address of the system making the request (like http_referrer) OleDbParameter p5 = cmd.Parameters.Add("originatingID", OleDbType.VarChar,50); p5.Direction = ParameterDirection.Input; p5.Value = originatingID.ToUpper().Trim(); // the name of the host system (http, websvcs) OleDbParameter p6 = cmd.Parameters.Add("hostSystem", OleDbType.VarChar,50); p6.Direction = ParameterDirection.Input; p6.Value = hostSystem.ToUpper().Trim(); cn.Open(); cmd.ExecuteNonQuery(); cn.Close(); return true; } #endregion But, when trying to run this same code from test system, which is running Oracle driver 8.01.07, I am receiving this error: "ORA-01036: illegal variable name/number" Is there something I can do to fix my SQL so it runs properly using the 8.01 driver? Or is it possible to install the 9.02 driver on test without disrupting the current apps that are using 8.01? - not a highly supported solution by other developers on my team. Or can I run these drivers side by side on the same machine? Ideally, I can identify what is causing this code to fail using the 8.01 driver and just redo the SQL. Thanks for the assistance! Tag: DB Independence Tag: 111086
  - 21
    - ADO.NET System.Data.SqlClient I'm developing web applications using Visual Studio.NET 2003 Version 7.1.3088 on two laptop configurations. One is Windows 2000 Professional, other is Windows XP SP2. Microsoft.NET Framework 1.1 Version 1.1.4322 SP1 (on XP Laptop), Microsoft.NET Framework 1.1 Version 1.1.4322 (on 2K Laptop). I have SQL Server 2000 - Version 8.00.0384 - SP4 installed on standalong machine. I've used VB.NET 2003 for over 1.5 yrs - no problems on 2K platform. I have web application connecting flawlessly to SQL server using SqlClient namespace. I have "headaches" while migrating same web application on XP platform. Continually getting message "System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding". Basically SQL Server 2000 - is fully operation for all apps, includes web app on 2000 Professional development machine. Just can't get SQL connects to database on XP Sp2 platform. I feel differences in OS are causes - different DLL's, possible issue with MDAC - TDS stream issue. I've submitted 3 postings to other newsgroups. Other disagree with MDAC issue & OS issue. I guess my questions are then - What is causing this to NOT work on SP2 of XP platform - same code - etc... Just 98% of time WON'T connect - once in blue-moon connection passes with XP Sp2. Again, no problems with 2000 OS platform. Please advise! Tag: DB Independence Tag: 111083
  - 22
    - Accessing deleted and changed rows in a DataSet I'm writing a client to connect to a legacy system (pre-.NET) which only accepts input and provides output using ADO Recordsets. I've been able to convert Recordset to DataSet, and if the DataSet has no changes I can convert back to Recordset. The problem comes when converting DataSet to Recordset when rows have been deleted or updated. The only way I could find to do the conversion involves "hand-crafting" the XML for the ADO Recordset combined with running an XSLT transform on the DataSet data. What I can't do is access the original rows in the DataSet in order to write <rs:update>, <rs:original>, and <rs:delete> elements and their child rows in the <rs:data> section. This data must be in there somewhere, otherwise how could a DataSet update a database directly? Does anyone know how I can get to it? Alternatively, if I'm missing an obvious way of making this easier, please can someone enlighten me ;-) Thanks Ian Tag: DB Independence Tag: 111082
  - 23
    - Can't insert in my table Hello, I have a stange error and I don't know where to look at. I do multiple insert in muliple table in my MDB databse. Everything work except from one table. when the code execute I get this error: Message "Syntax Error in the instruction INSERT INTO." StackTrace " 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 NTiersConstructeur.Data.DataProviderOleDb.Execute(String SQL) in C:\_DEV\NTiersConstructeur\Classe\DataProvider.vb:line 187" String When I execute the sql string in my mdb, It's work!!! Here is the sql string INSERT INTO [Valeur] (IDEntity, [Name], [Description], [Version], [TypeValue], [ReadWriteValue], [FieldNameValue]) VALUES (20, 'Valeur1', '', '', 'String', 'ReadWrite', '' ) Here is the code that execute all the insert, update and delete SQL string Private Function Execute(ByVal SQL As String) As Boolean Try Dim strConnectionString = System.Configuration.ConfigurationSettings.AppSettings("OLEConnectionString") Dim oConnection As New OleDbConnection oConnection.ConnectionString = strConnectionString oConnection.Open() Dim oCommand As OleDbCommand oCommand = New OleDbCommand(SQL, oConnection) oCommand.CommandType = CommandType.Text Dim oTransaction As OleDbTransaction oTransaction = oConnection.BeginTransaction oCommand.Transaction = oTransaction Dim intResult As Integer = 0 intResult = oCommand.ExecuteNonQuery Dim blnResult As Boolean = False If intResult >= 0 Then blnResult = True End If If blnResult Then oTransaction.Commit() Else oTransaction.Rollback() End If oConnection.Close() oConnection.Dispose() oCommand.Dispose() Return blnResult Catch ex As ArgumentException Catch ex As OleDbException MsgBox(ex.ToString) Catch ex As Exception MsgBox(ex.ToString) End Try End Function Can someone tell me what I am doing wrong? Is there an error in my sql string? Why do I have this error only with one table and not from the others? Tag: DB Independence Tag: 111081
  - 24
    - Divide by Zero exception Hi there My .asp application draws data from a number of sources, converts the results to type.double and does various calculations with these figures. Because of data entry errors on the system from which some of the data is drawn (this system is unfortunately not under my control so I can not sort this problem out at source....) I occasionally get Divide by Zero Exceptions which crash my application. Does anyone have a clever (or otherwise) way of catching this particular error and inserting a value of 0 in to the result instead of crashing ? Any help would be appreciated Tag: DB Independence Tag: 111076
  - 25
    - Problem with Data Refresh Hi there I have an .asp application that uses an accounting package for some of it's data. This links to a warehouse stock system. I am not running an ODBC link straight to the accounting system, but instead run a data refresh every 5 minutes that synchronises all the stock in the warehouse in to an SQL table - my .asp application links to this. I believe I am having issues with queries that are executed during this scheduled refresh of data - the application does not fail, but I am getting reports of inaccurate data resulting from queries at apparently random times of the day. Does anyone know if I am able to either lock the table during a refresh (and how my ado programming will recognise this status) so that I can catch the query and display a message along the lines of "Data refreshing, please wait" and then requery once the refresh has completed - any more practical suggestions would be musch appreciated. Thanks very much for your time Stuart Tag: DB Independence Tag: 111075

I'm looking for some DB Independence information on web, but I just found
information like persistence-oriented. I would some patter, but not
persistence, for DB Independence using the old and good SQL.

Any tip?

Top

Re: DB Independence by Chad

Chad
Tue May 31 05:29:47 CDT 2005

"Max B?ndchen" <someone@microsoft.com> wrote in news:eoaNiXWZFHA.2768
@tk2msftngp13.phx.gbl:
> I'm looking for some DB Independence information on web, but I just found
> information like persistence-oriented. I would some patter, but not
> persistence, for DB Independence using the old and good SQL.

Im working on an article that I hope to post on CodeProject next week
regarding exactly this topic.

--
Chad Z. Hower (a.k.a. Kudzu) - http://www.hower.org/Kudzu/
"Programming is an art form that fights back"

Blog: http://blogs.atozed.com/kudzu

Top

Re: DB Independence by Chad

Chad
Wed Jun 15 15:35:37 CDT 2005

"Chad Z. Hower aka Kudzu" <cpub@hower.org> wrote in
news:Xns96677F1F2EA70cpub@127.0.0.1:
> Im working on an article that I hope to post on CodeProject next week
> regarding exactly this topic.

Well here is the very begginning article. Mind you this doesnt touch much on how the DB independence
works, but you can see where its going maybe.

http://www.codeproject.com/useritems/CSharpSQL.asp

--
Chad Z. Hower (a.k.a. Kudzu) - http://www.hower.org/Kudzu/
"Programming is an art form that fights back"

Blog: http://blogs.atozed.com/kudzu

Top