What SQL Statement is being passed?

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
 - OLEDB for FoxPro and SPROCS Is there anyway to call a SPROC using the OLEDB provider for foxPro inside a VB.Net class and then have the ability to reference the column data inside a datareader or datatable? I currently only get a boolean value back when I call a SPROC that executes a simple select statement. HELP PLEASE!! Thanks! Dan Tag: What SQL Statement is being passed? Tag: 73613
 - 2
 - ADO .net in production environment Hello: I just started looking into ADO .net, and I wonder if it works well in production environment. One of the questions I have is working with multiple tables with ADO .net; that these tables are related. If there are update, insert, delete to the dataset returned from the database. When sending the modified data back to the database, is ADO .net automatically takes care of all the relationship and makes updates in all tables involved? Thanks. Quin Tag: What SQL Statement is being passed? Tag: 73611
 - 3
 - One bound form, two functions..help please Hi, I'm planing to have a form with 10 bound textboxes to a dataset throgh the currency manager and bindingcontext. What I need to do is use that same form based on the user choice. He/she could first press a button to fill-out all textboxes from dataset and do normal navigation through all records in the dataset, OR he/she could enter a search criteria in the first textbox (txtEmployee ID), press the same button to search and get that specific record only. I don't have any problem with the first case (press the button to get records and navigate), the problem is when the button is pressed for search, how can I search the dataset???? Correct me if i'm wrong, I'm planing to clear the binding property of all textboes and clear thier TEXT property. The question is How to search for that specific record based on the field enter in the textbox(which the choice to search) MTIA, Grawsha Tag: What SQL Statement is being passed? Tag: 73605
 - 4
 - XSD for existing XML structure. I have an xml file that is currently being used ... I need to make the xsd file for it for intellisense to work in the .net ide. So, when I try to do the xsd file, it keeps giving me an error about the tables being nested and are wrong...so after looking and sifting through google, I'll try here as well :) <store id="WalStore"> <product id="Paint1"> <product id="Paint3"></product> <product id="Paint4"> <product id="Paint5"></product> <product id="Paint6"></product> </product> <product id="Paint7"></product> <product id="Paint8"> <product id="Paint9"></product> <product id="Paint10"> <product id="Paint11"> </product> </product> </product> <product id="Paint2"> <product id="Paint12"></product> </product> </store> So, how can I create the XSD for the above xml? Once I can do that, my problem *should* be solved for my application. Thanks a bunch! Mythran the Pro-g(r)am(m)er Tag: What SQL Statement is being passed? Tag: 73602
 - 5
 - Expression Column doesn't refresh Hi, I have a problem. I have no any clue about this. Please help me to figure it out. I have a Customers table and Orders table. There is a relation,named "Customer2Orders", between Customers (parent table) and Orders (child table) on CustomerID. One Customer may have multiple Orders. I have an expression column 'Total' in Customers table. It is computed based on the child table's column InvoiceAmount, i.e. Total = Sum(Child(Customer2Orders).InvoiceAmount). If I add a child record, Total is computed automatically. But if I delete a child record, Total is not reflected. It still keeps the old value which is wrong. Thanks in advance! Fei Tag: What SQL Statement is being passed? Tag: 73598
 - 6
 - need XML help with nested tables Hi- I need to develop an FAQ section for our website. We would like to break up the FAQ by products, then categories with each category having n question/answer pairs. I would like to pass parameters in the querystring based on what the product/category the user selected, then populate a datagrid with the correct set of question/answer I am pretty new to XML, but basically I made something like this: <products> <product> <name>prod1</name> <partNum>prod1</partNum> <categories> <category> <name>basic category</name> <faqs> <faq> <question>This is a sample question</question> <answer>this is the answer</answer> </faq> </faqs> </category> </categories> </product> <product> <name>prod2</name> <partNum>prod2</partNum> <categories> <category> <name>basic something or something category</name> <faqs> <faq> <question>This is another question</question> <answer>and this is another answer</answer> </faq> </faqs> </category> </categories> </product> </products> So, then I use a DataSet and the ReadXML() method, this result in 5 tables. I was hoping that it would somehow be nested for me, but the way it is right now I can't see an easy way to use it the way I want. the first table lists the products(good) the second table I can't really tell what is in there third tables has all the categories, so on and so on. What is confusing is... what is the point of structuring data hierarchically in XML file if you can't maintain it? I'm sure you can, but how? Is what I'm doing not common? Someone please shed a little light on this for me, I would appreciate it!! Thanks, Steve Tag: What SQL Statement is being passed? Tag: 73597
 - 7
 - Adding a record using C# This is a multi-part message in MIME format. ------=_NextPart_000_0028_01C3BA8B.EDE71230 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Can someone tell me what is wrong with this code? I am simply trying to = add a record to a database. The error message is as follows: An unhandled exception of type 'System.InvalidOperationException' = occurred in system.data.dll Additional information: Update unable to find TableMapping['Table'] or = DataTable 'Table'. ************************************************************** DataSet ds =3D new DataSet(); ds.Tables.Add("FundData"); // Create Access objects:=20 System.Data.OleDb.OleDbCommand cmd1 =3D new = System.Data.OleDb.OleDbCommand("SELECT * FROM FundData", dbConn); System.Data.OleDb.OleDbDataAdapter AdpFunds =3D new = System.Data.OleDb.OleDbDataAdapter(cmd1); AdpFunds.Fill(ds, "FundData"); DataRow dr =3D ds.Tables["FundData"].NewRow(); dr["Symbol"] =3D sym; ds.Tables["FundData"].Rows.Add(dr); ds.AcceptChanges(); AdpFunds.Update(ds); ------=_NextPart_000_0028_01C3BA8B.EDE71230 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META http-equiv=3DContent-Type content=3D"text/html; = charset=3Diso-8859-1"> <META content=3D"MSHTML 6.00.2800.1276" name=3DGENERATOR> <STYLE></STYLE> </HEAD> <BODY> <DIV> Can someone tell me what is wrong with this = code?  I am=20 simply trying to add a record to a database.  The error message is = as=20 follows: An unhandled exception of type=20 'System.InvalidOperationException' occurred in = system.data.dll Additional information: Update unable to find=20 TableMapping['Table'] or DataTable 'Table'. <FONT=20 face=3DArial>************************************************************= ** DataSet ds =3D new=20 DataSet(); ds.Tables.Add("FundData"); // Create Access objects: = <FONT=20 size=3D2> System.Data.OleDb.OleDbCommand cmd1 =3D new = System.Data.OleDb.OleDbCommand("SELECT * FROM=20 FundData", dbConn); System.Data.OleDb.OleDbDataAdapter AdpFunds =3D new = System.Data.OleDb.OleDbDataAdapter(cmd1); AdpFunds.Fill(ds, "FundData"); DataRow dr =3D ds.Tables["FundData"].NewRow(); dr["Symbol"] =3D sym; ds.Tables["FundData"].Rows.Add(dr); ds.AcceptChanges(); AdpFunds.Update(ds);</DIV></BODY></HTML> ------=_NextPart_000_0028_01C3BA8B.EDE71230-- Tag: What SQL Statement is being passed? Tag: 73595
 - 8
 - Error in stored procedure Hi, I just wanted to ask what is wrong with this stored procedure. i keep getting the following error message: ADO error: The column prefix 'dbo' does not match with a table name or alias name used in the query. ALTER PROCEDURE dbo.SPSelEquipBooked2 (@Arrival datetime, @Departure datetime) AS SELECT dbo.Equip_Inv_No FROM dbo.Equipment_Inv WHERE dbo.equipment_Inv.Equipment_Inv_No Not In( SELECT dbo.Equipment_Inv.Equip_Inv_No FROM dbo.Equipment_Booking INNER JOIN dbo.Equipment_Inv ON dbo.Equipment_Booking.Equip_Inv_No = dbo.Equipment_Inv.Equip_Inv_No INNER JOIN dbo.Caravan_booking ON dbo.Equipment_Booking.BookingNo = dbo.Caravan_booking.BookingNo AND dbo.Equipment_Booking.Caravan_Inv_No = dbo.Caravan_booking.Caravan_Inv_No INNER JOIN dbo.Booking_Details ON dbo.Caravan_booking.BookingNo = dbo.Booking_Details.BookingNo WHERE (dbo.Booking_Details.Dt_of_departure >= @Arrival) AND (dbo.Booking_Details.Dt_of_arrival <= @Departure)) thx in advance Tag: What SQL Statement is being passed? Tag: 73591
 - 9
 - Question about nested transaction in ADO.NET I am using SQL Server 2000 as data storage I have a transaction which has the following structure connection.begintran Process A Process B Process C connection.commit rollback when failed In each process(A, B, C), there will also be BeginTran, Commit & Rollback inside it. I have such structure because process A, B, C are methods provided by different object. I have found that when I failed in process B, A will not be rollback... So how can I implement a nested transaction? Thanks Tag: What SQL Statement is being passed? Tag: 73583
 - 10
 - read an output parameter from a stored procedure by its name? hi. I cannot retrieve an output parameter from a stored procedure by name. Is this correct? Works not: [ sParamValue = mycmd.Parameters["@ID"].Value.ToString(); ] Works fine: [ sParamValue = mycmd.Parameters[0].Value.ToString(); ] TIA /OF Tag: What SQL Statement is being passed? Tag: 73578
 - 11
 - Datagrid - upgrade / save new rows I have an ADO.NET connection to an Access 2000 DB with 1 table. I have a Data Grid that shows me all records when I open the Data Grid. In the program it shall be possible to add rows to the table directly in the grid, by adding rows. Have tried to code this, but the result is only an empty new row. The information in the row is lost. Can anybody help? I have OleDgDataAdapter called 'da', OleDbConnection called 'cn', a dataset called 'ds1'. NSR Tag: What SQL Statement is being passed? Tag: 73572
 - 12
 - Reading text file using ADO.net This is a multi-part message in MIME format. ------=_NextPart_000_01AA_01C3BAA0.55E26560 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hope somebody can help... I'm trying to load up a text file using ADO.net, as follows: Dim TextConnectionString As String TextConnectionString =3D "Provider=3DMicrosoft.Jet.OLEDB.4.0;" & _ "Data Source=3D" & FilePath & ";" & _ "Extended Properties=3D""Text;HDR=3DNO;FORMAT=3DDelimited""" Dim TextConn As New = System.Data.OleDb.OleDbConnection(TextConnectionString) TextConn.Open() Dim da As New System.Data.OleDb.OleDbDataAdapter("Select * from " & = filename, TextConn) Dim ds As DataSet =3D New DataSet("CSVFiles") da.Fill(ds, filename) When I run this, it fails at the "fill" statement with the error = "Database or object is read only". The file in question can definitely be read and written (it's a short = CSV file). What have I done wrong??? Many thanks in advance. Simon ------=_NextPart_000_01AA_01C3BAA0.55E26560 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META http-equiv=3DContent-Type content=3D"text/html; = charset=3Diso-8859-1"> <META content=3D"MSHTML 6.00.2800.1276" name=3DGENERATOR> <STYLE></STYLE> </HEAD> <BODY> <DIV>Hope somebody can help...</DIV> <DIV> I'm trying=20 to load up a text file using ADO.net, as follows: <FONT=20 size=3D2>Dim TextConnectionString As=20 String TextConnectionString =3D "Provider=3DMicrosoft.Jet.OLEDB.4.0;" = &=20 _ "Data Source=3D" & FilePath & ";" & _ "Extended=20 Properties=3D""Text;HDR=3DNO;FORMAT=3DDelimited""" Dim TextConn As = New=20 System.Data.OleDb.OleDbConnection(TextConnectionString) TextConn.Open(= ) Dim=20 da As New System.Data.OleDb.OleDbDataAdapter("Select * from " & = filename,=20 TextConn) Dim ds As DataSet =3D New = DataSet("CSVFiles") da.Fill(ds,=20 filename) When I run this, it fails at the "fill" = statement=20 with the error  "Database or object is read only". The = file in=20 question can definitely be read and written (it's a short=20 CSV file).     What have I done = wrong??? Many=20 thanks in=20 advance. Simon </DIV></BO= DY></HTML> ------=_NextPart_000_01AA_01C3BAA0.55E26560-- Tag: What SQL Statement is being passed? Tag: 73570
 - 13
 - sqlCommand.ExecuteScalar return value 'undefined value' How can that be? Isn't it supposed to return dbnull if there is nothing in the select? Ronen Tag: What SQL Statement is being passed? Tag: 73564
 - 14
 - Queries Hi, I am new to ADO and still have a lot to learn from you. In Access, I used to have make queries (sometimes based on other queries) and display them in a form. In ADO.NET, there is a dataset. But I could not figure out how to do this. Is there any examples I can get? Tag: What SQL Statement is being passed? Tag: 73563
 - 15
 - Error whne dragging table to dataset Hi When I drag a table onto a dataset from the server explorer I get a Data Adapter Configuration Error...'Error in SELECT clause: expression near "".....'. How can I fix this? I am using FoxPro tables. Thanks Regards Tag: What SQL Statement is being passed? Tag: 73562
 - 16
 - Help with DataTable update (BeginLoadData method) I have an ASP.NET/VB app that updates values in a DataTable over the course of about 3 different pages. On the way out of the first of these pages, I explicitly build the DataTable from values in a DataGrid, and set the PrimaryKey of the DataTable to be the first cell in the grid (which is a UserID value). I then store the DataTable in a session object, from which it is retrieved for subsequent pages. All this seems to be working fine. On the second page, I retrieve the DataTable from the session object, apply a filter to it, and display the filtered data in a DataGrid. In this grid, the user can change the data, specifically assigning an employee to a different supervisor. The Grid is populated with the current employee/supervisor assignment in the database, and then any necessary changes are made by the user. This, too, seems to be working fine. The problem I'm having is that on the update command of the Grid, which is where I'm updating the DataTable (the thought being that I would just do an "in-place" update of the DataTable and then rebind the grid). I am able to trap the new assignment values (which are set in a listbox within the grid) and can trap the employee ID value from the grid (which is my primary key), but whenever I call the LoadDataRow method, rather than updating the row based on the primary key, a new row insert is *attempted*. I say attempted because I get an error on the EndLoadData method, which I believe is due to not passing in values for all the columns. I was hoping to be able to just pass in the key value and the values of the columns that had changed, but apparently that is not sufficient. Which is the first question - when attempting a LoadDataRow call, do I need to pass in valid values for *all* of the columns in the datatable (except, of course, those for which there are default values, of which I have none in this instance). You'll see in my example code that I'm just passing in the first, eighth and ninth columns, which I suspect is the problem. However, the *real* question is why isn't the LoadDataRow method finding the pre-existence of the key value in the table? I've checked and the value returned by my code is exactly the same as the one in the table, length is the same, etc. I set the primary key when I first build the table on the previous page, but I thought perhaps that attribute somehow gets lots when I set the DataTable to a session object so, as you will see in the example, I explicitly set the primary key again on this page, which seems to work, but the code still inserts a new row (or attempts to). Here is the relevant code from the page in question: Dim DDL As DropDownList = CType(e.Item.Cells(2).Controls(1), DropDownList) Dim NewID As Integer = DDL.SelectedIndex Dim SupervisorID As String = DDL.Items(NewID).Value Dim SupervisorName As String = DDL.Items(NewID).Text Dim NewRow(9) As Object Dim Key As TextBox = e.Item.Cells(1).Controls(0) Dim RowID As String = Key.Text Dim dsTechnicianList As DataTable dsTechnicianList = CType(HttpContext.Current.Session("ILApplicators"), System.Data.DataTable) Dim dcFirst As DataColumn = dsTechnicianList.Columns(0) Dim dcPrimary(1) As DataColumn dcPrimary(0) = dcFirst dsTechnicianList.PrimaryKey = dcPrimary NewRow(0) = RowID NewRow(7) = SupervisorName NewRow(8) = SupervisorID Dim myRow As DataRow dsTechnicianList.BeginLoadData() myRow = dsTechnicianList.LoadDataRow(NewRow, True) dsTechnicianList.EndLoadData() Any help or ideas would be GREATLY appreciated! TIA, Mike Tag: What SQL Statement is being passed? Tag: 73561
 - 17
 - Rollback Transaction Timing Out Framework 1.0 SQL Server 2000 After aborting a long (around 10 minutes) and with numerous updates, the Rollback function will raise an exception giving the message: System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. -- at System.Data.SqlClient.SqlTransaction.Rollback() If the transaction is not aborted, ie the Commit function is called, a timeout does not occur. In extracts from source below the timeout is set to 0 (for ever). Does anyone have experience of the Rollback timing out? ...... addCon = new System.Data.SqlClient.SqlConnection(); ...... System.Data.SqlClient.SqlCommand addCom = new System.Data.SqlClient.SqlCommand(); Try { ..... addCom.Connection = addCon; addCom.CommandTimeout = 0; //go on for ever addCon.Open(); //Start Transaction addCom.Transaction = addCon.BeginTransaction(); transactionStarted = true; ....... catch(Exception e) { ImportController.errorLevel = errorLevelMajorError; Audit("ERROR: Update PDS " + e.ToString()); if (transactionStarted) { errorLevelTransactionRolledBack = errorLevelTransactionRolledBack; addCom.Transaction.Rollback(); <<--- Times out here } addCon.Close(); addCom.Dispose(); } Thanks in advance Tag: What SQL Statement is being passed? Tag: 73559
 - 18
 - Append 1 Datatable to another (same schema)?? I have several datatables that are of the identical schema, each holding different data. There are times when it would be nice to combine the data from two datatables into one datatable. Would prefer not having to do this through looping. What would be the best way to do this? Tag: What SQL Statement is being passed? Tag: 73556
 - 19
 - Refilling table Hi I have several tables in a dataset. How can I reread the data from underlying database in only one of the tables? Thanks Regards Tag: What SQL Statement is being passed? Tag: 73555
 - 20
 - User Defined Sql Function and ADO.Net Hi all, Does anyone know the syntax for returning a value from a user defined function in Sql Server using VB.Net. Each time I do it I get: invalid object name We have a function which takes one parameter and returns a table. I can find little or no documents directly addressing this subject. Thanks in advance, Bill Tag: What SQL Statement is being passed? Tag: 73554
 - 21
 - Multiple Access Databases I want to redo my Forms in VB.NET. Now when I go and test my Forms to make sure they work I want to set up a second ACCESS file as a test database. When everything works and I want to switch over to my Production database how would I do that? Would I have to go into all my VB forms and redo my ADO links or is there an easier way? Tag: What SQL Statement is being passed? Tag: 73551
 - 22
 - OleDB returning DBNull for certain cells Hi, I'm using OleDBConnection to read data from Excel. I'm able to read everything except when a cell has numeric value and the column has alphanumeric data. I get DBNull when it encounters a numeric value. I'm sure many people have seen this. Does anyone know how to address this issue? Thanks, Paul Tag: What SQL Statement is being passed? Tag: 73550
 - 23
 - Can Sql Server 7 be used with ADO.Net? Does ADO.Net work with SQL Server 7 or is it only compatible with SQL Server 2000? Thanks in advance Chris Pearce Tag: What SQL Statement is being passed? Tag: 73546
 - 24
 - OleDB troubles reading Excel spreadsheet I'm trying to use an OleDbConnection to read some data from an Excel Spreadsheet. I create my connection string, and open the connection, but when i query the sheet/table i get the error that OleDb could not find the sheet. I've now tried to get a list of the sheets (to see if i was using the wrong sheet name or something) but my list of sheets comes back with 0 entries. Any Help! this spreadsheet DOES have 2 sheets inside it... What am i not doing? Are there any good examples/resources out there for reading Excel in this way? Private _connectionString as String Dim rs As OleDbDataReader Dim cmd As New OleDbCommand Dim query As String Dim table As String Dim lookup As DataTable 'I build my connection string newConnectionStr.Append("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=") newConnectionStr.Append(fileName) newConnectionStr.Append(";Extended Properties=Excel 8.0;") _connectionString = newConnectionStr.ToString 'I open my connection and attempt to see what sheets are available _connection.Open() lookup = _connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing) If lookup.Rows.Count > 0 Then table = CStr(lookup.Rows(0)("TABLE_NAME")) End If _connection.Close() _connection.Dispose() This returns me nothing, 0 rows end up in 'lookup'. If i try and run the query "Select * FROM [$Sheet 1$]" I get the "not able to find" error. Help? Thanks! jeffpriz Tag: What SQL Statement is being passed? Tag: 73543
 - 25
 - Programmatic access to control in DataGrid HeaderTemplate FindControls does not work for a server control in a DataGrid HeaderTemplate. Doing the following: <DataGridName>.FindControl("<controlId>") This brings back null for a HtmlInputCheckBox or a CheckBox Web control. How do you programmatically access a server control in the header of an DataGrid? Or can you? Thanks, Tommy. Tag: What SQL Statement is being passed? Tag: 73541
- Next
 - 1
 - DataBinding Format Question I am using the following code to apply the databinding format for Date (04/12/2003) **********************CODE***************************** Private WithEvents DateBinding As Binding DateBinding = New Binding("Text", Me.DsReport1, _ "Report.Date") Me.RichTextBox.DataBindings.Add(DateBinding) Sub Format(ByVal sender As Object, ByVal e As ConvertEventArgs) Handles _ DateBinding.Format Dim dt As DateTime = CType(e.Value, DateTime) e.Value = dt.ToString("dd/MM/yyyy") End Sub ********************************************************* It works perfectly but every time I load the form on design mode, the following messages appear: "The variable DateBinding is not declared or it was not assigned never" "The type System.Windows.Forms.Form does not have a field named DateBinding" Now, this can't be right and I sure don't want to leave it like that but I can't figure it out. Any recommendations please? Thanks! Tag: What SQL Statement is being passed? Tag: 73539
 - 2
 - FIND like old ADO method Is there any kind of FIND in ADO.NET that works the same way as the old ADO Recordset FIND? The old ADO Find would accept a criteria string, and then find the first record that matches the criteria. For instance, rs.Find("text_value like '* | *') would position me on the first record that contained a bar (' | '). It didn't filter anything, but just positioned the database at that record (or, in ADO.NET, would give me the row index of the first record matching this criteria). I see SELECT on a datatable and ROWFILTER on a dataview, but these all filter... I simply want to find the first record matching this criteria. I can probably get around it by simply reading all the records and programatically searching for the first one that matchs, but was hoping for a built-in solution. Thanks in advance. Tom Tag: What SQL Statement is being passed? Tag: 73535
 - 3
 - Using SqlCommandBuilder with Transactions Hi, i have a DataSet with 3 Tables. For each table i have defined a SqlDataAdapter Object. The SqlCommands for Deleting, Inserting und Updating were created by the SqlCommandBuider Object. I have wrote the following Code, to write the Content DataSet. public void WriteData() { SqlCommandBuilder buildCmd1; SqlCommandBuilder buildCmd2; SqlCommandBuilder buildCmd3; buildCmd1 = new SqlCommandBuilder(this.adapter1); buildCmd2 = new SqlCommandBuilder(this.adapter2); buildCmd3 = new SqlCommandBuilder(this.adapter3); this.sqlConn.Open(); this.adapter1.Update(this.dataSet, table1); this.adapter2.Update(this.dataSet, table2); this.adapter3.Update(this.dataSet, table3); this.sqlConn.Close(); } Now i want to encapsulate the three Updates in one Transaction? How can i assign the dynamic generated SqlCommand Objects to a SqlTransaction Object? Thanks Andreas Tag: What SQL Statement is being passed? Tag: 73524
 - 4
 - Datareader and Getint32 does not work Using VS.2003 and ODBC I make a SELECT count(*) from table1. with ExecuteSQLSelect This must return always !!! a number-value . But if I try to read from the datareader with GetInt32 a get always a inValid cast operation exception . Only Getstring(0) works fine. Is there any solution for this behavior ??? Thanks for any help Peter *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! Tag: What SQL Statement is being passed? Tag: 73520
 - 5
 - Attaching a table to a remote Database Hello ! I'm trying to make a link between two Access databases. Actually there are a few Tables in one of them I want to attach to the other (attach or add) knowing that the schema of the tables changes depending of which database is opened in the first place. Consequently I can't write a SQL script I can run against the second database. Can anyone help me doing this ? I don't have an idea on how to program such an operation in Visual Basic.NET :/ -Pulse Tag: What SQL Statement is being passed? Tag: 73519
 - 6
 - codegen:nullValue not working for me Hello, Have a datset returning a null value in a text string and crashes app. Used article 318039 as a guideline to modify dataset.xsd. (part of a WebService) Still Get Null. Excerpt follows. <xs:schema id="MASpedDataSet" targetNamespace="http://www.tempuri.org/MASpedDataSet.xsd" xmlns:codegen="urn:schemas-microsoft-com:xml-msprop" Other namespaces follow that were produced by Gen DataSet The other altered line: <xs:element name="BusRider" codegen:nullValue="N" type="xs:string" minOccurs="0" /> Have also tried nullValues of " ", _empty and "_empty". All out of ideas (for modifying XML, I know I can use IIF statements for each and every assign to a form control, but will likely throw self from window after doubling source code size as well as the time to complete each Form. mklapp Tag: What SQL Statement is being passed? Tag: 73515
 - 7
 - codegen:nullValue not working for me Hello, Have a datset returning a null value in a text string and crashes app. Used article 318039 as a guideline to modify dataset.xsd. (part of a WebService) Still Get Null. Excerpt follows. <xs:schema id="MASpedDataSet" targetNamespace="http://www.tempuri.org/MASpedDataSet.xsd" xmlns:codegen="urn:schemas-microsoft-com:xml-msprop" Other namespaces follow that were produced by Gen DataSet The other altered line: <xs:element name="BusRider" codegen:nullValue="N" type="xs:string" minOccurs="0" /> Have also tried nullValues of " ", _empty and "_empty". All out of ideas (for modifying XML, I know I can use IIF statements for each and every assign to a form control, but will likely throw self from window after doubling source code size as well as the time to complete each Form. mklapp Tag: What SQL Statement is being passed? Tag: 73514
 - 8
 - Detached DataRows and Relationships Hi, Does anybody know how to set the parent row of a child table's row to a detached row? I'm using Forms DataBinding. If I add a new row for editing, using BindingContext(someDataSet,"ParentTable").AddNew() the row's RowState is Detached. Now, if I try to add any child rows to a child table with this row set as the child rows' parent, an InvalidConstraintException will be thrown, telling me that the child's key value must exist in in the parent table (which it doesn't because the parent row is still detached). Is there a way around this, without having to first add the parent row to its table (by calling bindingcontext(someDataSet, "ParentTable).EndCurrentEdit())? Hope that all made sense Thanks, Dave Hagedorn Tag: What SQL Statement is being passed? Tag: 73513
 - 9
 - Detached Row And Relations in DataSet Hi, Does anybody know how to set the parent row of a child table's row to a detached row? I'm using Forms DataBinding. If I add a new row for editing, using BindingContext(someDataSet,"ParentTable").AddNew() the row's RowState is Detached. Now, if I try to add any child rows to a child table with this row set as the child rows' parent, an InvalidConstraintException will be thrown, telling me that the child's key value must exist in in the parent table (which it doesn't because the parent row is still detached). Is there a way around this, without having to first add the parent row to its table (by calling bindingcontext(someDataSet, "ParentTable).EndCurrentEdit())? Hope that all made sense Thanks, Dave Hagedorn Tag: What SQL Statement is being passed? Tag: 73511
 - 10
 - sproc with output param and a trigger We have been using the same sproc for an insert for quite some time. The sproc create a new key for the record to insert, uses the key for the insert and then has an output param for the key. The key field in the DataTable is 'names_id'. The is the sourcecolumn for the sql output param. Yesterday, one of our developers added an insert trigger to the table the sproc inserts into. Now, we are getting an exception that state 'name_id' cannot be Null. Very strange as his usually suggests to me I am passing in a parameter that is NULL. Well, names_id is not a param to the sproc. It's the source column for the sql output param. I am new to triggers so I have no idea what introducing the trigger has done. Anyone know what the source column for an output param would all of a sudden be NULL? Strange thing to is I no longer see the sproc running in SQL Profiler. Tag: What SQL Statement is being passed? Tag: 73499
 - 11
 - SP works when debugged/execute via query analyser but not when called from ADO.NET Hi, Okay heres a bit of background. Running WinXP Pro SP1, IIS5.1, SQL Server 2000 SP3, VS.NET 2003, Framework 1.1. This is porting job from VB6 webclass app to VB.NET httphandler DLL. I have a database wrapper class which has methods like RunSP etc... so all my DB calls use the same code so the problem is not that I am coding the command object differently or anything like that. Any way I call an SP that both creates and deletes data. The SP in fact calls other SPs and the SP uses BEGIN TRANS etc... However for some reason after it has been run the data is not correct, its almost as if one of the inner called SPs has failed or been passed the wrong ID or something. However if I pause the code on the line that calls my .RunSP method, and then go to query analyser and either debug and step through the SP or just execute it straight and then manually by hand set variables in my code to those values that would normally be set by the output parameters of the SP then it all works fine!!! I tried taking out all the BEGIN TRANS from the SPs and inner SPs involved but still the same problem. I am a bit new to the whole .NET thing, and while I like using it a lot, things that should be simple are really P*£"ing me off. Any help about what to look for or check would be much appreciated. Regards, Peter Tag: What SQL Statement is being passed? Tag: 73498
 - 12
 - datagrid I have a select stmt as follows SELECT qty, qty * 2 AS qty1, qty + 10 AS tuir FROM sales I have to bind table values which I obtain by running the query to a datagrid (web control)Item template column How should i give the expression for my datafrid columns ? Tag: What SQL Statement is being passed? Tag: 73497
 - 13
 - One DS, two tables and two XML files? Greetings, I would like to know please how can I create two xml files from one dataset with two tables in it? The WriteXML method of the DataSet creates one XML file with BOTH tables of the dataset. Dim ds as new dataset Dim da as new dataadapter 'code goes here for declaration and database connection da.fill(ds,"table1") da.fill(ds,"table2") ds.WriteXML(C:\text.xml) 'this will create ONE XML file...problem! I need TWO MTIA, Grawsha Tag: What SQL Statement is being passed? Tag: 73488
 - 14
 - ODBC.net Vs OleDB.net which is Better can some one tell me why is that ODBC.net is faster than OleDB.net??? Tag: What SQL Statement is being passed? Tag: 73481
 - 15
 - ADO XML and .NET Hello I want to read an XML file that was created with ADO into an ADO.NET dataset. The file was created using ... rs.Save "titles.xml", adPersistXML and is read into the dataset using... ds.ReadXML("titles.xml") My problem is that although the data is read OK, the inline schema is ignored and all columns are infered a DataType of String. Can ADO.NET read the ADO schema? If so, how? Thank you Tag: What SQL Statement is being passed? Tag: 73476
 - 16
 - Serializing/Deserializing a typed DataTable existing in a DataSet I want to serialize a DataTable that exista within a typed Dataset into an XML file and later deserialize it. I am not able to do that.... any pointers? Tag: What SQL Statement is being passed? Tag: 73475
 - 17
 - sqlCommand.ExecuteScalar problem on return value Hi "Select UserId from users where userid = 2" How come it return Undefined value if the select doesn't return any result? How can I know if the select succeded or not? Thanks, Ronen Tag: What SQL Statement is being passed? Tag: 73472
 - 18
 - HTTP 404 Error for ASP.NET pages The web server is running Windows Server 2003 Standard Edition, and it returns HTTP 404 error when running ASP.NET pages, but ASP pages are working fine. I also installed Visual Studio .NET, and the framework version should be 1.1. I am sure ASP.NET is enable in the machine by checking the following methods: 1) Go to manage your server window, click add/remove a role, and choose application server, and it only have choices disable ASP.NET, that means ASP.NET is enable already. 2) In IIS manager, click right pane, and I cannot see ASP.NET. I enabled all the followings: All Unknown CGI Extensions All Unknown ISAPI Extensions Active Server pages Front Page Server Extensions 2002 Internet Data Connector Server Side Includes WebDAV I really have no idea whats going on. I am using Standard Edition, I dont know if it has 32-bit and 64-bit versions. Since the following link says ASP.NET is disable in 64-bit Windows Server 2003. How can I tell if Windows Server 2003 is 32-bit or 64-bit?? http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/windowsserver2003/proddocs/entserver/unsupported64BitFeatures.asp Tag: What SQL Statement is being passed? Tag: 73470
 - 19
 - Getting @@Identity from an Access table My code below always returns 0 for the Identity. Any idea why. The database is in Access 2k. int ID=0; OleDbConnection conn=new OleDbConnection(); OleDbCommand aCommand=new OleDbCommand(); conn.ConnectionString =ConnectionString; conn.Open(); aCommand=conn.CreateCommand(); aCommand.CommandText =strSQL; aCommand.ExecuteNonQuery(); strSQL = "SELECT @@IDENTITY AS ID"; aCommand.CommandText = strSQL; ID=(int)aCommand.ExecuteScalar(); conn.Close(); return (ID); Tag: What SQL Statement is being passed? Tag: 73469
 - 20
 - SCOPE_IDENTITY () return value I have an identity column defined as int in sql 2000. I use SCPOE_IDENTITY () to get the new inserted column id. The problem is that the object returned from the ExecuteScalar is of type Decimal? why is that happens? Thanks, Ronen Tag: What SQL Statement is being passed? Tag: 73468
 - 21
 - How to get the ID of an item inserted into a database right after it has been inserted I am inserting rown into my data table and would like to be able to retriever id of the row inserted immediatelly after it has been inserted. How can I do that? Regards, Dino -- ------------------------------------------------------------------------- FIGHT BACK AGAINST SPAM! Download Spam Inspector, the Award Winning Anti-Spam Filter http://mail.giantcompany.com Tag: What SQL Statement is being passed? Tag: 73467
 - 22
 - This SqlTransaction has completed; it is no longer usable. This error happens every once in a while. I cannot figure out what is wrong or why this happens. This SqlTransaction has completed; it is no longer usable. public void InsertVisitorTargeter() { MyUtils.SqlClient sqlClient = null; SqlTransaction tran = null; try { sqlClient = MyUtils.SqlClientFactory.CreateSqlClient(); tran = sqlClient.SqlCommand.Connection.BeginTransaction(IsolationLevel.RepeatableRead, "VisitorTargeter"); sqlClient.SqlCommand.Transaction = tran; CommitToDatabase(sqlClient); tran.Commit(); } catch(Exception ex) { tran.Rollback(); throw(ex); } finally { // release resources MyUtil.SqlClientFactory.DisposeSqlClient(sqlClient); } } //edited method for ease of reading a bit. private void CommitToDatabase(MyUtil.SqlClient sqlClient) { System.Diagnostics.Trace.Write("Inside CommitToDataBase", "Targeter"); System.Diagnostics.Trace.Write("Visitor is " + visitorID, "Targeter"); sqlClient.SqlCommand.CommandText = "[dbo].[usp_VisitorTargeter_ins]"; sqlClient.SqlCommand.Parameters.Clear(); // create parameters sqlClient.SqlCommand.Parameters.Add("@RETURN_VALUE", SqlDbType.Int,4); sqlClient.SqlCommand.Parameters.Add("@ID", SqlDbType.UniqueIdentifier); sqlClient.SqlCommand.Parameters.Add("@Email", SqlDbType.VarChar, 256); sqlClient.SqlCommand.Parameters["@Email"].Value = email; sqlClient.SqlCommand.Parameters["@RETURN_VALUE"].Direction = ParameterDirection.ReturnValue; sqlClient.SqlCommand.Parameters["@ID"].Direction = ParameterDirection.Output; sqlClient.SqlCommand.ExecuteNonQuery(); // check that sproc ran successfully by returning 1 and affecting one table row only if ( 0 != (int)sqlClient.SqlCommand.Parameters["@RETURN_VALUE"].Value) //|| } I also have a begin tran and then a rollback/commit tran in the sp. Tag: What SQL Statement is being passed? Tag: 73463
 - 23
 - Merge dataset into query call in dataadapter???? Hi everybody - This may seem like an easy question to the .NET gurus out there but it has me very much stumped on how to accomplish. I have a dataset that I have merged together from separate queries. What I want is to include it in my final query so I can include other fields but I'm trying to do a join on certain fields in this dataset I've generated. How can I accomplish this? So for example, I'd like to do something like this: Select FirstName, dsQueryC.InitDate From Employee INNER JOIN dsQueryC etc...... (Being able to reference the dataset within the query of a data adapter) I'm completely lost on how to do this in ADO.Net Any thoughts are appreciated, Ben Tag: What SQL Statement is being passed? Tag: 73457
 - 24
 - Create XSD Dataset with child table in two nested relations. It's a commonly known problem that XSD.exe cannot parse the following XML into either an xsd file or class <Data><One><File><Name>Test1</Name></File></One><Two><File><Name>Test2</Name></File></Two></Data Because it can't cope with the fact that the <File> element appears in two places (the Custom tool error: Unable to convert input xml file content to a DataSet. The same table (*TableName*) cannot be the child table in two nested relations. error). The MS provided tool "Infer.exe" WILL create an .xsd schema for this XML, but doesn't provide a way to generate a strongly-typed DataSet for it. Can anyone point me to a good workaround? There must be some way (other than me creating the typed DataSet completely manually) that I can get a repeated element into a DataSet using the XSD tool combined with some custom code. Any ideas would be appreciated Richard Tag: What SQL Statement is being passed? Tag: 73446
 - 25
 - Web Services with large datasets I'm designing an app that will need to run in environment where different data is on different DB servers and OS'es (SQL Server, Oracle, Windows, AS400 etc). My app will need to pull together data from a few separate databases and then run some reports on the data. My original thinking was that XML and webservices would be the route to go. I could have someone provide a webservice that would give me some XML that I could turn into a dataset to be analyzed to create the reports. This is great in theory, but when I look at the sizes of the XML files, I'm not so sure. Webservices seem to be better suited to passing around small datasets or scalar values. How to people generally deal with this? Is the XML file size less of an issue than it seems to me? Or, do I need to look at figuring out a way to get a more direct connection to datasources in cases like this (i.e. reporting)? I know on the DB end I can quickly switch providers, but the SQL for one DB backend never seems to translate to a different DB backend, so you're stuck modifying much of the SQL too. Chris Tag: What SQL Statement is being passed? Tag: 73441

Is there a way to show what values are being passed in an SQL Statement?

I can display the commandtext of OleDbInsertCommand1 but it displays the "?"
is there a way to show the actual values of the insert statement?

Thanks

Charlie

Top

Re: What SQL Statement is being passed? by Miha

Miha
Fri Dec 05 01:25:15 CST 2003

No, unless there is some sort of database profiler which intercepts the
traffic between db and oledb (event then depends on its capabilities)
Once there it was universal OleDb profiler but it was later removed.

--
Miha Markic - RightHand .NET consulting & development
miha at rthand com

"Charles May" <quarantine1@hotmail.com> wrote in message
news:7fSzb.304995$275.1047997@attbi_s53...
> Is there a way to show what values are being passed in an SQL Statement?
>
> I can display the commandtext of OleDbInsertCommand1 but it displays the
"?"
> is there a way to show the actual values of the insert statement?
>
> Thanks
>
> Charlie
>
>

Top