Using parameters in the IN clause in a SelectCommand

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
    - @@identity Hi I am having problems with my stored procedure. i keep getting a message stating that the stored procedure 'InsClientAndSelIdentity' expects a parameter @Identity the thing is i have declared it in my sproc below!!! I dont understand what i am doin wrong Do i need to write anything in my code as well? i.e assign a value to the Identity I thought that as it is an output parameter i will not have to Can anyone plz help me ALTER PROCEDURE dbo.InsClientAndSelIdentit @Surname char(50) @Forename char(50) @OrgName varchar(50) @Address varchar(300) @Postcode varchar(50) @PhoneNo varchar(25) @Identity int OU A SET NOCOUNT OFF INSERT INTO Client(Surname, Forename, OrgName, Address, Postcode, PhoneNo) VALUES (@Surname, @Forename, @OrgName, @Address, @Postcode, @PhoneNo) SET @Identity = SCOPE_IDENTITY() Return Tag: Using parameters in the IN clause in a SelectCommand Tag: 77915
  - 2
    - Beginner's Question--accessing database server This is a multi-part message in MIME format. ------=_NextPart_000_0008_01C3E0FC.F1C1DBA0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello I am trying to access my data using some of the examples on the VB = Resource CD to learn about VB.net. I have used the following code: Public Sub InsertRow(ByVal myConnectionString As String) MyConnectionString =3D "database=3DNorthwind;Server=3Dlocalhost" End Sub Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As = System.EventArgs) Handles Button2.Click Dim myConnection As New SqlConnection Dim myInsertQuery As String =3D "INSERT INTO Customers (CustomerID, = CompanyName) Values('NWIND', 'Northwind Traders')" Dim myCommand As New SqlCommand(myInsertQuery) myCommand.Connection =3D myConnection myConnection.Open() myCommand.ExecuteNonQuery() myCommand.Connection.Close() End Sub 'SelectSqlClientSrvRows When I click f5 and run the app, I get no compile errors. When I click = on the button, however and attempt to access the server, I get the = following error: An unhandled exception of type 'System.InvalidOperationException' = occurred in system.data.dll Additional information: The ConnectionString property has not been = initialized. Additional information is as follows: 1. I have Visual Studio.net installed with MSDE on my computer. In my = server explorer I have the following items showing.=20 SQL Servers ---GARYLAP\INSTANCE-------- 2. Under the GARYLAP\INSTANCE I have the following databases: Master Model MSDB Northwind I have modified the connection string until I finally gave up. I tried = server =3Dgarylap and garylap\instance but to no avail. Any help would = be greatly appreciated. =20 Thanks, Gary ------=_NextPart_000_0008_01C3E0FC.F1C1DBA0 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><FONT face=3DArial size=3D2>Hello I am trying to access my data = using some of=20 the examples on the VB Resource CD to learn about VB.net.  I have = used the=20 following code:</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>Public Sub InsertRow(ByVal = myConnectionString As=20 String)<BR>MyConnectionString =3D = "database=3DNorthwind;Server=3Dlocalhost"<BR>End=20 Sub<BR>Private Sub Button2_Click(ByVal sender As System.Object, ByVal e = As=20 System.EventArgs) Handles Button2.Click<BR>Dim myConnection As New=20 SqlConnection<BR>Dim myInsertQuery As String =3D "INSERT INTO Customers=20 (CustomerID, CompanyName) Values('NWIND', 'Northwind Traders')"<BR>Dim = myCommand=20 As New SqlCommand(myInsertQuery)<BR>myCommand.Connection =3D=20 myConnection<BR>myConnection.Open()<BR>myCommand.ExecuteNonQuery()<BR>myC= ommand.Connection.Close()<BR>End=20 Sub 'SelectSqlClientSrvRows</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>When I click f5 and run the app, I get = no compile=20 errors.  When I click on the button, however and attempt to access = the=20 server, I get the following error:</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>An unhandled exception of type=20 'System.InvalidOperationException' occurred in = system.data.dll</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>Additional information: The = ConnectionString=20 property has not been initialized.</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>Additional information is as = follows:</FONT></DIV> <DIV><FONT face=3DArial size=3D2>1.    I have Visual = Studio.net=20 installed with MSDE on my computer. In my server explorer I have = the=20 following items showing. </FONT></DIV> <DIV><FONT face=3DArial size=3D2>SQL Servers =20 ---GARYLAP\INSTANCE--------</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>2.    Under the=20 GARYLAP\INSTANCE  I have the following databases:</FONT></DIV> <DIV><FONT face=3DArial size=3D2>Master</FONT></DIV> <DIV><FONT face=3DArial size=3D2>Model</FONT></DIV> <DIV><FONT face=3DArial size=3D2>MSDB</FONT></DIV> <DIV><FONT face=3DArial size=3D2>Northwind</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>I have modified the connection string = until I=20 finally gave up.  I tried server =3Dgarylap and garylap\instance = but to no=20 avail.  Any help would be greatly appreciated.  </FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>Thanks,</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>Gary</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV></BODY></HTML> ------=_NextPart_000_0008_01C3E0FC.F1C1DBA0-- Tag: Using parameters in the IN clause in a SelectCommand Tag: 77913
  - 3
    - Consume ASP.NET DataSet from web service called from ASP I have a legacy ASP application that calls a web service in a newly developed application in ASP.NET that exposes a web service. I call the web service from ASP code. The .NET web service returns a DataSet, which is serialized of course My question is: in the ASP consumer application, what datatype do I use to recieve the serialized DataSet from the ASP.NET web service? or more specifically, how do I deserialize the DataSet in my ASP application so I can read it into an XML document? Tag: Using parameters in the IN clause in a SelectCommand Tag: 77909
  - 4
    - Deleting rows from a DataTable in a DataSet I have a typed DataSet with a DataTable in it(created by using the "Generate DataSet" of the DataAdapter). Sometimes, I want to delete all of the records in the DataTable and I use the following snippet: Dim dr As DataRow For Each dr In tbTable.Rows dr.Delete() Next I have found that if I don't make a call to the DataAdapter's .Update function before I call the above snippet, I get the following error message: "Collection was modified; Enumeration operation may not execute" I really should be able to delete these records independently of what's on the SQL server. Why will it work only after an Update? Michael Tag: Using parameters in the IN clause in a SelectCommand Tag: 77907
  - 5
    - annoying delay during refresh of aspx web page with ODBC Access query on IIS Windows 2003 Hello All, I have been working on this for almost a week now and I haven't anything up my sleeves anymore that I could test in addition or change.... Since I am not sure, if this is a Windows 2003 Server or ADO or ODBC issue, I am posting this on all of the three newsgroups. That's the setup: Windows 2003 Server with IIS and ASP.NET actiavted Access 2002 mdb file (and yes, proper rights are set on TMP paths and path, where mdb file is located) ASP.Net web server component that will query and update the Access DB in the functionality of a web counter The problem is, that after the first call of the web site with the web counter component, the refresh will take about 8-9 seconds. Doesn't matter if I close the IE or just hit the refresh. From what I can observe is, that after the start of the IIS web server no w3wp.exe process is running. As soon as I open the aspx web site with the .Net built counter component, a w3wp.exe process is being instantiated and the calls to the Access DB are made. Since the w3wp.exe process has to start, the inital display of the web page takes about 1-2 seconds and everything works fine. I can see a .tmp file being created in the TMP directory and a .ldb file being created in the path of the .mdb file for a brief moment. Then they disappear. The w3wp.exe process stays. Then, if I hit refresh or close and reopen the web site, nothing seems to happen for 8-9 seconds and then the page displays without any errors. The difference is, that the .tmp file and the .ldb file are also sitting in the corresponding paths for a while longer, but both disappear again. Since I have been doing quiet a bit of trouble shooting already, here is some detailed info as of what happens during the inital call of the web site, and during subsequent calls: Output of current second and millisecond of the machine during initial call, when no w3wp.exe process existed yet: 58:343 objCounterDBConnection = New OdbcConnection 58:359 objCounterDBCommand = New OdbcCommand 58:359 objCounterDBConnection.Open() 58:390 objCounterDbRdr = objCounterDBCommand.ExecuteReader 58:562 objCounterDbRdr.Read 58:562 objCounterDbRdr.Close() 58:578 objCounterDBConnection.Close() 58:625 preparing insert 58:640 objCounterDBConnection.Open() 58:640 objCounterDBCommand.ExecuteNonQuery() 58:640 objCounterDBConnection.Close() 58:687 objCounterDBConnection & objCounterDBCommand.Dispose() 58:687 finished Output of current second and millisecond of the machine during subsequent call, when existing w3wp.exe process apparently take the job: 10:359 objCounterDBConnection = New OdbcConnection 10:359 objCounterDBCommand = New OdbcCommand 10:359 objCounterDBConnection.Open() 10:375 objCounterDbRdr = objCounterDBCommand.ExecuteReader 19:140 objCounterDbRdr.Read 19:140 objCounterDbRdr.Close() 19:140 objCounterDBConnection.Close() 19:187 preparing insert 19:187 objCounterDBConnection.Open() 19:187 objCounterDBCommand.ExecuteNonQuery() 19:187 objCounterDBConnection.Close() 19:234 objCounterDBConnection & objCounterDBCommand.Dispose() 19:234 finished As you can see, during a subsequent call, the ExecuteReader takes close to 9 seconds before it returns the OdbcDataReader object. WHY???? This is not an update and hence has nothing to do with lazy propagation issues as far as I can tell what I have seen on different posts. Also, this problem occurs only on the Windows 2003 Server, not the Windows 2000 Server. What is the issue here? - the initial w3wp.exe process is being run under the NETWORK SERVICE account and I have given that account proper permissions to perform the job. That is being verified in my opinion by the fact, that the first run of the component works fine. To me this is an absolute issue of Windows 2003 Server and/or ODBC on Windows 2003 Server. Is it ODBC, is it the Windows 2003 server? I have no clue. Your help on that would be really appreciated and I can post the sources if necessary if somebody else would like to try that out. I'm almost 99.99% certain that this is a true Windows 2003 Server or ODBC issue that I am just about ready to open a call with Microsoft on that. The source is clean as it could be and I have double checked with quiet a few examples on MSDN and other Tech articels on that. Yesterday I had the IIS and application part completly removed from the server, everything cleaned up and started all over with the install. Exact same results. No other pocesses are running on that machine at all. It's clean as it could be. So I am really stumbling on that. Please help! Thank you, Wolfgang Tag: Using parameters in the IN clause in a SelectCommand Tag: 77905
  - 6
    - Sorting & Filter Dataset Hi All, VB.NET, I have a dataset populater from a database. I tried this tempDS.Tables(0).Select("fldProjectStatusID=1", "fldJobName") But it is not sorting or selecting, it still shows everything in the order it was queried from the database in. THanks Lee Tag: Using parameters in the IN clause in a SelectCommand Tag: 77894
  - 7
    - executing sql against a dataset Hi all is there a way to issue sql commands against a dataset. i mean can we execute an update command on the dataset. like.. update <datatable> set <somecol> = <somevalue> where <someothercol> = <someothervalue> Im not asking about the exact synatax as mentioned above.. but i m just asking if there is a way to update multiple rows of single col in one go. thanks Noor Tag: Using parameters in the IN clause in a SelectCommand Tag: 77890
  - 8
    - Dataset.GetXML() with the power of FOR XML EXPLCIT? Can anyone tell me if this is possible? We have several ASP/SQL Server 2000/XSLT applications with hundreds of stored procedures returning data using FOR XML EXPLICIT. What we would like to do is port to .NET and support numerous different databases by changing the stored procedures to return "normal" resultsets (i.e. a recordset), then convert the recordset to XML and pass to the XSLT to generate the HTML output. I have seen the GetXML() method of the Dataset object but this seems to only be able to generate simple XML output. By that I mean a simple table with rows and columns would produce: <table> <row> <column1>data</column1> <column2>data</column2> <column3>data</column3> .... </row> <row>...</row> </table> Is there a way to produce more heirarchical XML as I can generate using the FOR XML EXPLICIT syntax and multiple table joins? e.g. <order ordernumber="5" vat="4.17" deliverycharge="3.95" totalcost="36.02" orderdate="2004-01-22 11:44:58"> <emailaddress><![CDATA[a@a.com]]></emailaddress> <itemtype itemtypeid="171" cost="27.90" costhasnulls="0"> <title><![CDATA[Containers]]></title> <msgs><![CDATA[]]></msgs> <orderorderitem id="6" productcode="SH127" quantity="1"> <title><![CDATA[Cylinder]]></title> <deliverydetails><![CDATA[e<br/>f<br/>g<br/>h<br/>United Kingdom]]></deliverydetails> <invoicedetails><![CDATA[a<br/>b<br/>c<br/>d<br/>Afghanistan]]></invoicedetails> <notes><![CDATA[]]></notes> <dispatch/> </orderorderitem> <orderorderitem> ... </orderorderitem> </itemtype> <itemtype> ... </itemtype> </order> Thanks in advance, Mike. Tag: Using parameters in the IN clause in a SelectCommand Tag: 77889
  - 9
    - Creating a database Hi, I need to create an application that would connect to an SQL Server and create a database with all tables and relations needed. I have never done something like this before, I know that an easy way to do the same is to create a text file with all "CREATE" statements and run it on the server. However, I have to do it programatically from a VB.net application. I need some help on that. Is this a good way to do it? How to do it? etc Any help will be appreciated Dino -- ------------------------------------------------------------------------- FIGHT BACK AGAINST SPAM! Download Spam Inspector, the Award Winning Anti-Spam Filter http://mail.giantcompany.com Tag: Using parameters in the IN clause in a SelectCommand Tag: 77886
  - 10
    - Retrieving Related Records I have a table called Notes that houses all the notes created in my system. Any object can have a collection of notes. Even though objects can't share a single note, it's still designed to be in a single table. For Example, here are seven tables: Object1 Object2 Object3 Note ------- ------- ------- ------ Obj1Id Obj2Id Obj3Id NoteId ... ... ... NoteText Obj1_Note Obj2_Note Obj3_Note --------- --------- --------- Obj1Id Obj2Id Obj3Id NoteId NoteId NoteId If I retrieve the following dataset: "SELECT * FROM Note WHERE NoteText LIKE '%searchtext%'" Is it possible to get the object that the note is linked to? I hope this makes sense. The reason I need this is because when working with notes in a generic sense, the user needs to be able to access the record to which the note refers. I need a way to work from the bottom up, and not the top down. Database design suggestions are welcome. Thanks, Jason Tag: Using parameters in the IN clause in a SelectCommand Tag: 77885
  - 11
    - Updating a boolean value in an Access DB I'm having problems updating a boolean value in an Access database. The value is defined as a Yes/No type that I've always assumed could be treated as a simple boolean. To update the value, I retrieve the data from the specified table into a DataTable from a OleDbDataAdapter. I cycle through the rows looking for the specific value to replace. I then replace the value in the DataTable then create an UPDATE query and run the OleDbDataAdapter's UpdateCommand method. Here's the exception message that I get: "System.Data.OleDB.OleDbException: Syntax error in UPDATE statement." I've been able to replace non-boolean values without any problems, but I keep hitting a snag on the booleans. Here's an example of the query that I'm using in the UpdateCommand: "UPDATE Device SET Mg = 'True' WHERE NAME = 'Desired Name'" I've also tried replacing 'True' with its numeric equivalent (-1) with the same results Any assistance would be greatly appreciated. Brad Tag: Using parameters in the IN clause in a SelectCommand Tag: 77871
  - 12
    - Please help urgently. Hi all My problem is a little lengthy. But please help me. In my application, ADO.NET DataSet object is not getting constructed in the Web Service Web Method called by the Windows Application passing an ADO.NET DataSet Object as a parameter to the Web Method in Web Service Debugging using the Soap Extensions, clearly shows that the Serialized DataSet is passed to the Web Service, but the Web Service Web Method is not able to construct the ADO.NET DataSet from the Serialized Data. I am hereby giving a brief overview of what I am doing. We're developing a WindowsForms based distributed application. All our WindowsForms clients connect to the business components layers using Web Service. Therefore, the front-end only talks to the Web Service and Web Service calls the Business and Data Layer components. Since the Web Service Layer acts as a gateway between the WindowsForms clients and the business components, We pass all the data using DataSets. Here is the code snippet, private void LoadGroup(string ticker) { //We're creating an object of a stronly typed DataSet EquityPeerCriteriaSet equityPeerCriteriaSet = new EquityPeerCriteriaSet(); //We're setting the properties of the strongly typed DataSet equityPeerCriteriaSet.CriteriaType ="Ticker"; equityPeerCriteriaSet.CriteriaValue =m_ticker; //We're creating the Web Service's proxy object WebReferences.EquityPeerListService.EquityPeerListMgmtService equityPeerListService= new WebReferences.EquityPeerListService.EquityPeerListMgmtService(); //We're calling the web method and passing the DataSet from Dataset DspeerListsSet = equityPeerListService.GetPeerList(equityPeerCriteriaSet); ... ... } Our Web Method looks like this, [WebMethod] [TraceExtension] public DataSet GetPeerList(DataSet dsEquityPeerCriteriaSet) { try { //Creating an object type of the Business Layer EquityPeerListManager equityPeerListManager =new EquityPeerListManager(); ... ... return peerListsSet.DataSet; } catch(Exception ex) { SoapException Se = new SoapException (ex.Message,SoapException.ClientFaultCode,ex.InnerException); throw Se; } } The problem is that, the DataSet object comes as undefined when entering the Web Method. We've also created a SOAP Extension class and debugged the SOAP message chain. The data is present in both SoapMessageStage.BeforeDeserialize and SoapMessageStage.AfterDeserialize stages and we're able to see the data in the log file. This behavior looks very strange. If you can provide a pointer, that would help us a lot. Thanks and appreciate your response. Thanks again. Tag: Using parameters in the IN clause in a SelectCommand Tag: 77868
  - 13
    - Importing from a text file to a Dataset I have a large(1.25M), fixed field delimited, text file which I want to read into a DataSet. So, I opened a StreamReader, read a line, assigned the values to a new DataRow, and finally added the DataRow to a DataTable in the DataSet. Seemed to be a staight forward task but boy did it progress at a snail's pace! So I tried to speed things up by first importing the data from the text file into SQL Server. To my amazement, the text import went like lightning! How can I get lightning fast text import? Michael Tag: Using parameters in the IN clause in a SelectCommand Tag: 77867
  - 14
    - OnClick & ButtonClick Hi Team, I want execute "Onclick" event whcih will trigger to a ClientSide Validation Javascript and once that over, should call Server side Button_Click(). Please let me know how to do this for a Server side Button Control. Thanks in advance Cheeeeeeeeeers !! Tag: Using parameters in the IN clause in a SelectCommand Tag: 77866
  - 15
    - Cannot find datatype bigint I have moved an asp.net application from windows 2000 to windows xp. The application gets data from an sql server 2000. Some of the queries declare some variables with the BIGINT datatype to hold the results of some calculations. When I run the application I get the error "Cannot find datatype bigint" - is it because I have moved the application from win 2000 to win xp? What can I do to solve the problem? The int datatype will not be sufficient to hold the calculated values so this is not a solution. I would appreciate any answer that gives me an idea of what to do. Thanks, Dorte Tag: Using parameters in the IN clause in a SelectCommand Tag: 77855
  - 16
    - DataSet to Text File Well I had a way to write an array to an excel spreadsheet but on a huge time critical run it failed iwth the dreaded HRESULT: 0x800A03EC error. It worked fine when i sampled the data to go in but when it all tried to go in it bombed. So I need to write this to a tab delimited file and I sure hope somebody is awake tonight. How do I write my dataset to make a tab delimted text file? Tag: Using parameters in the IN clause in a SelectCommand Tag: 77848
  - 17
    - I need Help with System.Data.sqlClient Hello, In my projet, there are class for each table of my database.So, if I want to get, add, change or delete some thing in a table, I use the class that correpond to it. I have this kind of code in all class. Usually, it has allways work fine for all the projects that I have done. I don't know why, but with a particular project, I have a lot of trouble. The code allways jam on the DataAdapter Fill Line. Something, my web application is incredibly fast, but sometime I can wait up to two minutes before the execution of the Fill line finish and gets to the next statement. Do I miss something in the configuration of my web application or maybe it is my SQL Server that have a problem. I work on a computer that it is not connect to a network so I am the only one who connect to the SQL Server. Public Function GetTotalAvoir(ByVal pstrId As String, ByVal pstrNoPeriode As String, ByVal pstrDSN As String) Dim strCommande As String = "" Try strCommande = "SELECT TotalAvoir FROM Bilan WHERE NoCompagnie = " & pstrId & " AND NoPeriode = " & pstrNoPeriode Dim daDataAdapter As New SqlDataAdapter(strCommande, pstrDSN) Dim dsDataSet As New DataSet() daDataAdapter.Fill(dsDataSet, "Bilan")<-----------This is the line with witch I have a lot of trouble Dim dtDataTable As DataTable = dsDataSet.Tables(0) If dtDataTable.Rows.Count > 0 Then GetTotalAvoir = dtDataTable.Rows(0).Item(0) Else GetTotalAvoir = DBNull.Value End If dtDataTable = Nothing daDataAdapter.Dispose() Catch Finally End Try End Function Thank you Marc Tag: Using parameters in the IN clause in a SelectCommand Tag: 77844
  - 18
    - Generic coding Hi I have two identical databases A & B with several tables. Each table has an ID and a timestamp. Is it possible to write a generic procedure that will take the table name (as it is same in both dbs) and the names of the two dbs and update tables in db A from corresponding tables in db B depending on which record is most recent? I am just trying to avoid writing code for each of the tables. Thanks Regards Tag: Using parameters in the IN clause in a SelectCommand Tag: 77843
  - 19
    - Help needed with Oracle Enterprise Manager I can run queries using SQL+ but when I run the same queries using Oracle Enterprise Manager, I get nothing. I am using the Scott Schema and log in as the DBA in Enterprise Manager. When I run these stored procedures using my C# application, again I get nothing back. Any suggestions. Tag: Using parameters in the IN clause in a SelectCommand Tag: 77838
  - 20
    - union and Dataviews?? is it possible to create a union multiple tables within a dataview?? Taking in consideration that both table will look the same but come from different connections select * from table1 union select * from table2 if so can anyone show me how to do this??? Vince Tag: Using parameters in the IN clause in a SelectCommand Tag: 77834
  - 21
    - .NET and connection pooling in Terminal Services Hi! I'm designing an architecture that will be run on a 16-node Terminal Services (Citrix) cluster. An Oracle 9i cluster contains the data and ODP.NET will be the interface. Everything else is done on .NET 1.1. For this particular apps there are about 20-100 simultaneous users, though design will be stateless multi-tier, of course. The problem is how to find the best trade-off between performance/scalability. One important thing to consider is connection pooling. I understand how it works in a "typical" multitier environment, but this Terminal Services changes things a bit (or does it?). Normally, no new pool is created if: -Connection string is the same -Process ID is the same (is it the same for multiple TS-clients on the same physical server?) -Application Domain is the same (see the question above) -If there are no transactions outside database If the application and all the dependent assemblies (client, BL and DAL) are installed to the TS, will there be 16 connection pools (1 per TS-node) or one pool/user thus no usable pooling? If the pooling doesn't work in above situation, we may need to move the DAL to a centralized server and use .NET Remoting. However, this I would like to keep as the last resort. That question about processes and application domains is the most important ones at the moment, but all suggestions are welcome, too :) Thanks, -- Joni Moilanen MCSD.NET, MCDBA Tag: Using parameters in the IN clause in a SelectCommand Tag: 77832
  - 22
    - HELP NEEDED: ADO.NET - DATAADAPTER WON'T UPDATE TO DATABASE Hi, I'm needing assistance with ADO.NET saving a dataset to my database. I have included code that may show you where I'm going wrong. My goal is simple enough: I have two SQL Server 2000 databases with only minor differences. The program I wish to write will automate the process of copying over certain tables from one database to the other. The example I have included here is for tables that have the exact same structure; the only difference between the old and the new is that the old table from the first database has data, and the new table from the second database is blank. I want to copy the data from the old and put it in the new. My problem is that although I can get the data from the old into a dataset supposedly linked to the new (via dataadapters), updating the dataadapter for the new does not submit the data to the actual database. Any assistance would be greatly appreciated. I have commented my sample code to explain what I have done at each step. Sincerely, -- JR Fournier jurfman@yahoo.com -+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+- Private Sub CopyFromOldToNew(ByVal strTable As String) 'strTable is the name of the table passed from outside functions. 'I wish to use a subroutine like this to perform the copy because I 'will not know the structure of the tables in advance. 'strConnOld and strConnNew are globally defined connection variables. 'They are defined as follows: 'Const strConnOld As String = _ ' "Data Source=localhost;Initial Catalog=utility_be_old;" _ ' "Integrated Security=SSPI" 'Const strConnNew As String = _ ' "Data Source=localhost;Initial Catalog=utility_be_v2;" _ ' "Integrated Security=SSPI" 'I then declare old and new datasets and dataadapters. Dim dsOld As New DataSet() Dim dsNew As New DataSet() Dim daOld As New SqlDataAdapter("Select * from " & strTable, _ strConnOld) Dim daNew As New SqlDataAdapter("Select * from " & strTable, _ strConnNew) 'While filling my dsOld dataset is obviously necessary, I don't think 'filling my dsNew is. The actual "new" database will always contain 'the structure only, not any data. Keep in mind, the table name that 'has been passed to this subroutine exists in BOTH the old and the 'new database. The structure is the same. I would use an SQL 'command and perform this copy manually, but my goal is to create a 'function that has no advance knowledge of which table is copied. daOld.Fill(dsOld, strTable) 'daNew.Fill(dsNew, strTable) 'Of all the code involved, the cmdBuilder usage confuses me the most. Dim cmdBuilder As SqlCommandBuilder = _ New SqlCommandBuilder(daNew) 'The following line of code is one of about 5 ways I have tried. 'This merge statement will successfully take my dsOld data and put 'it into my dsNew dataset. Now, I have also done the row by row 'copy, etc, but the end result of getting the data into the new 'dataset is the same. This is a one line solution that achieves 'the same effect. If there is a way to just use one dataset, since 'the data is the same, I would be happy to use it. But any attempts 'to only use the one dataset resulted in "This table [or this column] 'does not belong to that table", etc. dsNew.Merge(dsOld, True, MissingSchemaAction.Add) 'I added a datagrid to display the contents of the new dataset, 'proving that dsNew contains the data that should be saved to the 'database. DataGrid1.SetDataBinding(dsNew, strTable) 'I confess that the cmdBuilder usage confuses me a bit. 'If I am misunderstanding the usage, please assist. daNew.UpdateCommand = cmdBuilder.GetUpdateCommand() daNew.InsertCommand = cmdBuilder.GetInsertCommand() 'Is AcceptChanges actually needed on my dsNew dataset? dsNew.AcceptChanges() 'From what I understand, telling my daNew dataadapter to update 'should commit my added rows to the new database. No error is 'thrown, but no changes are made in the actual database. daNew.Update(dsNew.Tables(0)) End Sub -+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+-+=+- Please help! :) Tag: Using parameters in the IN clause in a SelectCommand Tag: 77830
  - 23
    - Binding a Textbox Generated dinamically Hi Folks! Maybe some one can give me a hand with this problem: I'm building one asp.net page that reads data from the table and builds the table and the controls dinamically, I create a dataset and fill it with all the tables needed. All works fine displaying the info but I have problem with the Databinding since it works different in Vb.NET and ASP.NET, the databindings doesn't exist in ASP.NET it only accepts the Databinder.eval. I call the Sub and pass the Table to draw and which panel to display using DrawTable("FinInfo_Income", 1) The line that display the current value is marke {**}, I tried to use the Databinder.Eval without any luck.. Does anyone know the right form to bind the control ...? TIA for any help! Public Sub DrawTable(ByVal Tablename As String, ByVal TableNumber As Integer) Dim numrows As Integer Dim numcells As Integer Dim i As Integer Dim j As Integer Dim r As TableRow Dim c As TableCell Dim CatSum As Integer Dim Colname As String CatSum = 0 For j = 1 To DS.Tables(Tablename).Columns.Count - 1 r = New TableRow() For i = 1 To DS.Tables(Tablename).Rows.Count Dim box As New System.Web.UI.WebControls.TextBox() Dim lbl As New Label() c = New TableCell() lbl.Text = DS.Tables(Tablename).Columns(j).ColumnName.ToString If DS.Tables(Tablename).Rows(0).ItemArray.GetValue(j) Is System.DBNull.Value Then box.Text = "" Else {**}box.Text = DS.Tables(Tablename).Rows(0).ItemArray.GetValue(j) If DS.Tables(tablename).Columns(1).DataType.ToString = "System.Decimal" Then CatSum = CatSum + DS.Tables(Tablename).Rows(0).ItemArray.GetValue(j) End If End If c.Controls.Add(lbl) c.Controls.Add(box) r.Cells.Add(c) Next i 'This code adds the columns to the Panel Select Case TableNumber Case 1 TableFI.Rows.Add(r) Case 2 TableFH.Rows.Add(r) Case 3 TableFFC.Rows.Add(r) Case 4 TableFLE.Rows.Add(r) Case 5 TableFR.Rows.Add(r) Case 6 TableFS.Rows.Add(r) Case 7 TableFT.Rows.Add(r) Case 8 TableFOE.Rows.Add(r) End Select Next j End Sub Tag: Using parameters in the IN clause in a SelectCommand Tag: 77829
  - 24
    - Typed DataSet confusion Greetings! I have a database with a table named Holdings. I has VB.Net create a .xsd file for it so that I could use a typed dataset with it. The table has 21 rows. If I use ordinary datasets, I get a DataTable object with 21 rows. If I use a typed dataset, I get a HoldingTable object with 0 rows. What am I doing wrong? Here's the code: Dim holdingsAdapter As New OleDbDataAdapter() Dim holdingsSet As New HoldingsDataSet() Dim holdingsTable As HoldingsDataSet.HoldingsDataTable Dim holdingsRow As HoldingsDataSet.HoldingsRow 'Dim holdingsSet As New DataSet() 'Dim holdingsTable As DataTable 'Dim holdingsRow As DataRow holdingsAdapter.SelectCommand = New OleDbCommand _ ("SELECT * FROM Holdings", mDB) holdingsAdapter.Fill(holdingsSet) holdingsTable = holdingsSet.Tables(0) MsgBox("The holdings table has " & holdingsTable.Rows.Count & " rows.") This code is in a Try block, and no exceptions are thrown. Also, I notice that my HoldingsDataSet object has a member named Holdings. Is that member an instance of type HoldingsDataTable, like I think it is? Thanks very much! Rob P.S. I have already been told that using "Tables(0)" defeats the purpose of a typed dataset. That's not the point here. Once I know I can get the data I need into the typed dataset, I'll switch over to using it the way it's supposed to be used. Tag: Using parameters in the IN clause in a SelectCommand Tag: 77823
  - 25
    - Calling a SQL SP in a Stored Procedure Group This function: Public Function RunSP(ByVal SP As String, Optional ByVal Params As Hashtable = Nothing) As Object Dim oConn As New SqlClient.SqlConnection(CONNECTION_STRING) Dim oCmd As New SqlClient.SqlCommand(SP, oConn) Dim SqlParameter As SqlClient.SqlParameter oCmd.CommandType = CommandType.StoredProcedure SqlClient.SqlCommandBuilder.DeriveParameters(oCmd) If Not Params Is Nothing Then For Each SqlParameter In oCmd.Parameters If Params.ContainsKey(SqlParameter.ParameterName) Then If IsDBNull(Params.Item(SqlParameter.ParameterName)) Then SqlParameter.Value = DBNull.Value Else SqlParameter.Value = Params.Item(SqlParameter.ParameterName).ToString End If End If Next End If Dim oDA As New SqlClient.SqlDataAdapter(oCmd) Dim oDS As New Data.DataSet oDA.Fill(oDS) oDA.Dispose() : oDA = Nothing RunSP = oDS oCmd.Connection.Close() oCmd.Dispose() : oCmd = Nothing End Function returns a dataset returned by the specified Stored Procedure with parameters filled in from the supplied hashtable. However, if I try to call a stored procedure from a stored procedure group (e.g. set SP to "spCustomers_Select;2"), it bombs on the "DeriveParameters" line, saying the stored procedure doesn't exist. Is there a fix for this, besides changing this to construct an EXEC statement? (And would that even work?) Tag: Using parameters in the IN clause in a SelectCommand Tag: 77819
- Next
  - 1
    - Calling a Stored Procedure in a Stored Procedure Group This function: Public Function RunSP(ByVal SP As String, ByVal ReturnType As ReturnType, Optional ByVal Params As Hashtable = Nothing) As ObjectDim oCmd As SqlClient.SqlCommand = WarehouseDBCommand(SP) Dim SqlParameter As SqlClient.SqlParameter oCmd.CommandType = CommandType.StoredProcedure SqlClient.SqlCommandBuilder.DeriveParameters(oCmd) If Not Params Is Nothing Then For Each SqlParameter In oCmd.Parameters If Params.ContainsKey(SqlParameter.ParameterName) Then If IsDBNull(Params.Item(SqlParameter.ParameterName)) Then SqlParameter.Value = DBNull.Value Else SqlParameter.Value = Params.Item(SqlParameter.ParameterName).ToString End If End If Next End If RunSP = RunAndReturn(oCmd, ReturnType) oCmd.Connection.Close() oCmd.Dispose() : oCmd = Nothing Tag: Using parameters in the IN clause in a SelectCommand Tag: 77817
  - 2
    - Passing DataSet to remote server: possible bug Our DAL (Data Access Layer as a dll) is hosted by IIS. We use binary serialization to pass a DataSet into the DAL from a client exe. Our client exe calls GetChanges on DataSet and passes the changes to our remoted DAL. On inserts we have an output parameter for the primary key. After the call to DataAdapter.Update, we watch the insert sproc run and return the output param value. We watch the DataRow change from Added to Unchanged. We watch the source column for output param get set to the outparam's value. The DAL then returns the DataSet to the client exe. Once back in the client exe, the DataRow is still marked as Added and the source column is empty. This is exactly the way it looked when it was sent to the remoted DAL. Now, if we run the DAL locally in a private folder, we see the same results in the DAL. However, back in the client exe the DataRow is marked as Unchanged and the source column contains the output parm value. This leads me to believe there is a bug in the Remoting or ADO.NET architecture. Tag: Using parameters in the IN clause in a SelectCommand Tag: 77815
  - 3
    - Oracle Lite 9i connection (newbie) I have installed Oracle Lite 9i at: C:\Oracle\OraLite9i\Mobile\sdk\OLD40\polite.odb (virgin install). Have also installed ODP.NET in Visual Studio.NET (Framework 1.1). Now I try to open the database with the following code: Imports Oracle.DataAccess.Client Public Function OpenOracleLite() As DataSet Dim cnOracleLite As String Dim cn As New OracleConnection(cnOracleLite) Try 'Oracle Lite 9i (ODP.NET) cnOracleLite = "Data Source=POLITE;" & _ "User Id=SYSTEM;" & _ "Password=;" cn.Open() Catch exc As Exception Debug.WriteLine(Err.Number, Err.Description) Finally cn.Close() cn = Nothing End Try End Function Getting the following error: Error: 5 ORA-12154: TNS:could not resolve service name This is my first Oracle install. Please note the connection string parameters. Are they correct for after a virgin install of Oracle Lite 9i? What else is needed (probably in the way of database administration) to get the connection to see the oracle lite db in the path above, or another oracle lite database on another drive? Also: does 9i have any GUI user interface, or is it like MSDE? Thanks, Dean Slindee Tag: Using parameters in the IN clause in a SelectCommand Tag: 77814
  - 4
    - Character set on an Oracle DB problem Hello all, I have a problem retrieving data from an Oracle 8.1.7 DB. The query execute correctly, but same characters of strings are not correct. These characters are tipically deutch characters (ö et simila). The nls_characterset, both for server and client is US7ASCII, and i can't change it. I tried the "Microsoft Data Provider for Oracle" and the "Oracle Data Provider.NET" too, but I was unable to fix the problem. Please HELP!!! Lorenzo Tag: Using parameters in the IN clause in a SelectCommand Tag: 77813
  - 5
    - Here is one Hiya Group, Sorry to be such a pain but .......... I have a little problem, On my Form I have a ListBox, with 5 Options, Each Option relates to a Table in My database, I also have Add, Update, and Delete Buttons, with one DataGrid. My question is how do i populate the datagrid from the Listbox and get the Add, Remove, and Update Buttons working for each Table? Any Code of URL`s? Many Thanks Si Tag: Using parameters in the IN clause in a SelectCommand Tag: 77812
  - 6
    - Clone or copy table.. Schema Information.. Generic ado.net connection Hi: Im writing a table designer through an ado.net provider. Im using a generic ado.net provider, so i cant use database specific data (sysobjects or something like that). I can get some information using DataReader.GetSchemaTable(), but is not enoug. I need, for instace, the types the database uses to fill a combobox, so the user can choose and i can create my "create table" and "alter table sentences". At the moment im using some rare practices like: When the user changes the data type or the size of the type for a column, I have to create an auxiliar table, to send the data, drop the current table and then create a new table with the schema selected by the user, update that table, and drop the auxiliar one. (I don't like that, because if the connection fails, there will remain some garbage tables on the database). Another rare thing im doing is to execute many IDbCommand.Prepare () to "create" tables with all the data base data types i know, (int, blob, char.. 45! in total) to discriminate which of them the current database supports.. :-b As you can see, i need real solutions and ideas. Is there some article, or source code available? Do you have any suggestions? Please help Tag: Using parameters in the IN clause in a SelectCommand Tag: 77806
  - 7
    - Why do I have TimeOut Hello, In my Global.asax, I have my connection string Application("strDSNSQL") = "SERVER=(local); UID=LOGIVELO_USER; PWD=!ItsAll4Me; DATABASE=Logivelo1" I have thise code in an ASPX page Dim DecisionCompagnie As Compagnie Dim dtDecisionCompagnie As DataTable DecisionCompagnie = New Compagnie() dtDecisionCompagnie = DecisionCompagnie.GetLastDecision(Session("compagnie"), Session("Periode"), Application("strDSNSQL")) If dtDecisionCompagnie.Rows.Count > 0 Then txtNomCompagnie.Text = dtDecisionCompagnie.Rows(0).Item(4) txtNomGroupe.Text = dtDecisionCompagnie.Rows(0).Item(2) txtPeriode.Text = Session("Periode") txtEmissionAction.Text = dtDecisionCompagnie.Rows(0).Item(6) txtEmpruntLongTerme.Text = dtDecisionCompagnie.Rows(0).Item(7) txtRemboursement.Text = dtDecisionCompagnie.Rows(0).Item(9) ... This is the function that I call in my Compagnie Class Public Function GetLastDecision(ByVal pstrId As String, ByVal pintNoPeriode As Integer, ByVal pstrDSN As String) As DataTable Dim strCommande As String = "" Try strCommande = "SELECT * FROM DecisionCompagnie WHERE NoCompagnie = " & pstrId & " AND NoPeriode = " & pintNoPeriode Dim daDataAdapter As New SqlDataAdapter(strCommande, pstrDSN) Dim dsDataSet As New DataSet() daDataAdapter.Fill(dsDataSet, "DecisionCompagnie") Dim dtDataTable As DataTable = dsDataSet.Tables(0) GetLastDecision = dtDataTable dtDataTable = Nothing daDataAdapter = Nothing Catch... When I try this code 10 times, 8 times it is ok and 2 times I have a TimeOut. I have SQLServer 2000 on my computer and I am alone on the network. I want to understand what I do wrong Thank you Marc Tag: Using parameters in the IN clause in a SelectCommand Tag: 77805
  - 8
    - How can I use the same data to update many tables Private Sub btnSaveAndClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSaveAndClose.Clic In one point in my application I allow the user to select some text - say Yom Kippur War - and then click the QuickWord button. That opens up a frmQuickWord. On it appears a single textbox with the word selected QuickWord was meant to create a new Keyword Set. The selected text in this case will be the Keyword Set, the Keyword and the Search Phrase. In cases where there'll more 2 or more keywords and search phrases(Yom Kippur War, Yom Kipper War, October War), the user will click a different button I realized at once that my problem in the following code is that I haven't bound the textbox to the data. If I have to bind a textbox and a checkbox then it seems I'll need a second textbox for the autoincremental ID field. What I'm afraid of is that I'll have to rebind them in the code for the Keyword, SearchPhrase, and KeywordSetAssignments tables. This would be very wordy, and I have a number of forms of this nature to finish. Is there a simpler way Sorry for the slight incoherence. I'm quite handicapped and quite ill, with a high fever. Nevertheless, I want to get this project done, before I expire. Don't have so much more to do dennis Tr MsgBox(cnQWKS.DataSource.ToString cnQWKS.Open( Dim iKSModified = daQWKS.Update(DsQWKS1, "KeywordSets" Dim strOutput As Strin strOutput = "Modified " & iKSModified & " KeywordSet(s) MessageBox.Show(strOutput, "Update succeeded!", MessageBoxButtons.OK, MessageBoxIcon.Information daQWK.Update(DsQWKS1, "Keywords" daQWSP.Update(DsQWKS1, "SearchPhrase" daQWSP.Update(DsQWKS1, "KeywordSetAssignments" Catch ex As Exceptio MessageBox.Show("Type = " & ex.GetType.ToString & vbCr & "Message = " & ex.Message End Tr cnQWKS.Close( Me.Close( End Sub Tag: Using parameters in the IN clause in a SelectCommand Tag: 77799
  - 9
    - DataSet.Select Is there any way to do a DataSet.Select() statement and use more than one selection "query"? For instance, instead of just selecting all rows where the IDNum is greater than 32 with DataSet.Select("IDNum > 32"), select all rows where the IDNum > 32 and the Region = Midwest. Is this possible? Tag: Using parameters in the IN clause in a SelectCommand Tag: 77784
  - 10
    - Editing Rows Yellow to al Iâ??m trying to do edit a row of a datatable Dim dataTable As New DataTable("Vencimento" I create four columns Dim colWork As New DataColumn("ABONO", GetType(String) colWork.Caption = "Abono dataTable.Columns.Add(colWork colWork = New DataColumn("VALORAB", GetType(String) colWork.Caption = "Valor dataTable.Columns.Add(colWork colWork = New DataColumn("DESCONTO", GetType(String) colWork.Caption = "Desconto dataTable.Columns.Add(colWork colWork = New DataColumn("VALORDES", GetType(String) colWork.Caption = "Valor dataTable.Columns.Add(colWork Them I populate the first tow columns with a For, like this For I as string = 1 To row = dataTable.NewRow( row("ABONO") = "Ret. Efectiva row("VALORAB") = "10,00 dataTable.Rows.Add(row Nex This code create three equal lines, and the columns â??DESCONTOâ?? and â??VALORDESâ?? have the null expressio What I want to do now is, fill the columns â??DESCONTOâ?? and â??VALORDESâ? With â??IRSâ?? and â??5,00â?? strings The out put must be like thi Abono Valor Desconto Valo Ret. Efectiva 10,00 IRS 5,0 Ret. Efectiva 10,00 IRS 5,0 Ret. Efectiva 10,00 IRS 5,0 Thereâ??s any one how can help me Thank Ana Rita Tag: Using parameters in the IN clause in a SelectCommand Tag: 77780
  - 11
    - Compact & Repair in Access from ADO.NET Hi I wonder how can I use Compact & Repair feature in Access 2003 from my C# project using ADO.NET? Thanks for help Kamil Tag: Using parameters in the IN clause in a SelectCommand Tag: 77775
  - 12
    - Column types in a System.DataTable Hello, How does a System.DataTable determine the datatypes of its columns when filled using a select statement. If I fill a datatable using the sample table/data, Select * from myTest (MSS 2K), below the test1 column is defined as a decimal. Why wouldn't it be defined as a Int64? Or can this be controlled? Sample Table and Data: ================ Create Table myTest ( test1 numeric(10,0) not null ) Insert Into myTest Values ( 1, 'value 1' ) Insert Into myTest Values ( 2, 'value 2' ) Thanks, Rob Panosh Tag: Using parameters in the IN clause in a SelectCommand Tag: 77772
  - 13
    - Calculated Columns in a DataTable Are there any plans to include the ability to have user defined functions in Calculated Columns in data tables? If not do you know who I can contact to put in a feature request? Tag: Using parameters in the IN clause in a SelectCommand Tag: 77769
  - 14
    - Find in DataView using a non sorting column Dear Sirs. in VS 2002. was no direct way to find in DataView if you not find by a sorted column. is it enchansed in vs 2003 ? plz provide me an example Thank you in advance. Tag: Using parameters in the IN clause in a SelectCommand Tag: 77762
  - 15
    - Validating Data During Row Changes I want to stay on a row in the table until all validations are OK. For this I use RowChanging event of DataTable and throw an exception if validation is not OK. If I throw an exception without catching, the program ends. If I throw an exception and catch this exception the messagebox with error is show and then position changes. How to stop moving to another position ? Thank you Tag: Using parameters in the IN clause in a SelectCommand Tag: 77750
  - 16
    - How to enumerate sql server names/instances on the netork Visual Studio 2003, C#, SQL Server 2000 How to enumerate SQL Server names/instances on the network for a WinForm application ? Thank you Tag: Using parameters in the IN clause in a SelectCommand Tag: 77749
  - 17
    - multiple recordsets in ms access h i m tring to create multiple recordsets from ms access thru vb.ne my string i str="select name from jobs;select col2 from jobs but it gves me erro "Characters found after end of SQL statement wat to d amrita thnx Tag: Using parameters in the IN clause in a SelectCommand Tag: 77748
  - 18
    - GUIDs as keys - definition and use Hello, I want to use GUIDs as keys for records in a number of tables for a database I'm designing. The reason- I want to be able to "merge" records from different site installations in the future and I don't want key conflicts. Starting with the schema design (sqlserver)- what is the best "type" to pick to hold the GUID? I'm thinking varchar of length 36. After I've designed the schema in SQLServer, I plan to use the server explorer and create a dataset definition for use in .Net. - Any tips for dealing with GUIDS with regard to the code the designer generates? i.e for creating/modifying and deleting records? Thanks in advance, Gene Tag: Using parameters in the IN clause in a SelectCommand Tag: 77746
  - 19
    - ADO.NET: using xml as a database, adding rows to the xmlfile Hi, I've got a debug/learn function for doing my xml thingie. I simply want to append data to an xml file. This confuses me, i've looked at xmldocument, xmldatadocument and from what i understand a DataSet is a flexible and "easy" (if you know how) way to work with xml files. I've worked alot with ADO in VB6 so this seemed tempting (i'm still a .net noob or NOOB) I've managed to create a schema for my table and i think i know how to add the data correctly but i can't manage to update my data back to my DataSet and write the DataSet as an XMLFile. I've looked on the DataAdapter but i end up wondering how do i make an OleDB conn to my xml file and i resemble nooo this seems stupid and bigfoot print'ed since they say .net and xml is like bread and butter. I just want to use an xml file as a simple database for my app. Would anyone be so kind to help me out with a updatetable thingie ? My Code fails on the AcceptChanges which i suppose is because my DataRow isn't written back to the DataSet but how do i do that ? Please ignore the bad naming convention this code is mixed with a lot copy paste from various examples... none covers how to work with just a dataset and an xml file.. I think i fail to see the wood because of the forest if you know what i mean :) Public Function doCreateNewXmlfile() Dim ds As New DataSet Dim col As DataColumn Dim oDataRow As DataRow 'Create the table structure. With ds.Tables.Add("Routers") col = .Columns.Add("IPAddr", GetType(String)) col.MaxLength = 15 col = .Columns.Add("HostName", GetType(String)) col.MaxLength = 90 col = .Columns.Add("LogFileName", GetType(String)) col.MaxLength = 30 End With ' Save our schema representing our table structure Dim oFileStream As New System.IO.FileStream(Me.setXmlIpTableFileSchemaName, FileMode.Create) ds.WriteXmlSchema(oFileStream) oFileStream.Close() oFileStream = Nothing ' Create some data in our xmldatabase Dim row As DataRow = ds.Tables("Routers").NewRow row("IPAddr") = "192.168.1.1" row("HostName") = "tower" row("LogFileName") = "tower_Log" row.AcceptChanges() oFileStream = New System.IO.FileStream(Me.setXmlIpTableFileName, FileMode.Create) ds.WriteXml(oFileStream) oFileStream.Close() oFileStream = Nothing End Function Thanks a lot /Kai Tag: Using parameters in the IN clause in a SelectCommand Tag: 77745
  - 20
    - Dataset binding to comboboxes - As many items as records?? Hi All, I have been having a hard time figuring how to approach a problem and was hoping I might be able to get some assistance. Here is my situation. I have a dataset that is based on a table in my SQL server database. I am using Databinding to bind the dataset to a series of controls on my form. A few of these controls are comboboxes. One of the comboboxes is bound the the field in the dataset that is my primary key (it's called ID). This works well for me because I can scroll through the combobox and select whichever record I want to deal with and all the controls are updated to that record when I select it. The problem however, resides with another combobox which is bound to a field in the database that can only have two (2) possible choices, Yes or No. I was expecting that this combobox would only consist of two possible choices however I am finding that there are as many "items" in the combobox as there are records in the database. Granted there are only two possible "values" (Yes or No) but they are repeated throughout the combobox. Any idea what I am doing wrong? Has anyone seen this before? Thanks in advance for any assistance! - Derek Tag: Using parameters in the IN clause in a SelectCommand Tag: 77743
  - 21
    - Cast DataView as a DataTable I'm using a third-party tool that takes a DataTable as a parameter. I really need to pass it a DataView instead. When I try to explicitly cast the DataView as a DataTable I get an error that they aren't compatible data types. Does anyone have a suggestion on how to do this? My last resort is to create a new DataTable object and copy the rows from the DataView into the DataTable. But this is an ugly hack and I really hope there is a better solution. Thanks. Tag: Using parameters in the IN clause in a SelectCommand Tag: 77741
  - 22
    - Multiple Parents Ok, I've already figured out that its not allowed to have 2 (or more) parents to 1 child table in .NET. (unless I'm totally misinformed). http://support.microsoft.com/default.aspx?scid=kb;en-us;325695 So, how do you get around it. Surely there are plenty of you who have run into this. In my case I have 3 tables (more actually but 3 will suffice for now): PurchaseOrders, Tickets, and Items. Should be a pretty classic example. A customer walks into the shop and places an order for various items which come from several different vendors, therefore these items will belong to several different PurchaseOrders. A Ticket is created for the customer, listing all the items. Thus, a Tickets to Items relationship is implied. However, at the end of the day (or end of the week, etc) the purchaser draws up a series of PurchaseOrders. The PurchaseOrder will have all the items from a particular vendor, for all the Tickets that were generated that day (or week, etc). So, the PurchaseOrder Table needs a relationship with Items as well. Thus, 2 parents, same child. How does ADO.NET handle this? How do I handle this? Thanks, Blake Tag: Using parameters in the IN clause in a SelectCommand Tag: 77739
  - 23
    - Add new row to Access database Hi all, I wrote a simple application using C# & Ms. Access DB. 1. Read all record from database into dataset and perform searching functionality. (work) 2. I want to add new record into the database if searching fail. (problem) Here is the procedure: MyTable has 2 columns only (myKey | myValue) where myKey is the PK and myValue column is allow null. ...C# coding private OleDbDataAdapter da; private DataSet dsMyTable; AddRow(string newKey){ DataTable dt = dsMyTable.Tables["MyTable"]; DataRow irow = dt.NewRow(); irow[0] = newKey; dt.Rows.Add(irow); da.Update(dsMyTable,"MyTable"); } there was no error. But when I check in MyTable I couldn't find new record added ??!!! Please advices, Sokun Tag: Using parameters in the IN clause in a SelectCommand Tag: 77738
  - 24
    - In memory of a friend! Hello all! Not sure how this should be posted. If I understand my reading. i'll need Visual Basic .NET, ASP .NET, and ADO .NET. So I've posted this thread in all three newsgroups. I apologize up front if this was a mistake. A little background. I've got a large collection of movies in VHS and DVD formats. I've been categorizing them like they were in a database (i.e. Movie Title, Director(s), Writer(s), Cast, etc.), but using a simple text file. I have a MS Windows SharePoint Services (WSS) site, and I thought it would be a great idea to finally put the info in a formal database (I'm using SQL Server 2000 as the underlying database for WSS). A very close friend volunteered to take on this project and come up with a way to query for particular movies. His plan was to use Visual Basic .NET and what else I'm not sure. Well, the week before Xmas my friend died unexpectently. And to his memory I'd like to complete the project on my own, and maybe a little help from a couple of newsgroups. I'm just not sure where to start? I've been doing some reading and as I mentioned above, it seems I'm going to need Visual Basic .NET along with both ASP .NET and ADO .NET. Am I right in that assumption? How I envision the movie portion of the WSS site! There'd be a quick-launch link on the main page. This would take you to a page where half would be setup to search the database for a particular movie. You would put in your search criteria, Movie Title (or what you thought the title was), actor(s), or Director(s). Clicking the [SEARCH] button would bring up another page with the search results. The results of course would be hotlinked movie titles, movies that actor(s) starred, or Director(s) directed. Clicking on one of these hotlinks would take you to that particular movie page on the server. Does that make sense? The bottom of this initial WSS page would also list the alphabet. Maybe 4 rows of 5 and 1 row of 6. Clicking on these hotlinks would take you to a page with that particular alphabetical listing of movies with hotlinks to the particular movie page. I've been copying the small movie posters from imdb.com, and I've found a place where I can get movie trailers, both of which this movie page would house along with all of the other movie info. I know I'm being overly long, but I wanted to explain exactly what the plan was. Now, where do I start? I've got a little experience with Visual Basic .NET, probably enough to get me in trouble, but what else do I need to complete this project? Any help would be greatly appreciated! To the memory of JB! TIA Hodgepodge Tag: Using parameters in the IN clause in a SelectCommand Tag: 77736
  - 25
    - Related records - is a datarelation necessary I have a table named ProposedCosts and another table named AdditionalCosts. AdditionalCosts has a foriegn key to the ProposedCostID in the ProposedCost table. When I retrieve records out of the database, I use a SQLDAtaAdapter and the .Fill method to fill a 'ProposedCost' datatable and a 'AdditionalCost' datatable, both tables are in the same dataset. From the front end, if I wanted to add a new row to ProposedCost, that isn't any problem except for the fact that it does not generate a ProposedCostID (this is an identity key in the database). The problem is that if I want to create an 'additional cost' record, I have no ProposedCostID. What is the best way to handle a situation like this? I thought about going back to the database with the dataset and somehow adding a row to the datatable that way, but then when I finally do, do an update, won't it think the row I added is a 'new' row and not an update? Thanks. STom Tag: Using parameters in the IN clause in a SelectCommand Tag: 77735

I am having a problem with a SelectCommand Parameter value. The SQL text
includes something like

"...AND TRANSCRIPT_ID IN (@IDLIST)...."

Placing an appropriate string in the Parameter collection value for this
parameter (say, "'TH'" - note the single quotes) fails to return any rows,
yet the same statement in the Query Analyzer works fine. Is there some
trick to putting paramterized values into an 'IN' clause?

Top

Re: Using parameters in the IN clause in a SelectCommand by William

William
Thu Jan 22 15:40:24 CST 2004

It can't take a Parameter from ADO. See the archives for a more detailed
explanation.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

"Thomas Hill" <thomas.hill@leavenworth.army.mil> wrote in message
news:O%23r6j9S4DHA.488@TK2MSFTNGP12.phx.gbl...
> I am having a problem with a SelectCommand Parameter value. The SQL text
> includes something like
>
> "...AND TRANSCRIPT_ID IN (@IDLIST)...."
>
> Placing an appropriate string in the Parameter collection value for this
> parameter (say, "'TH'" - note the single quotes) fails to return any rows,
> yet the same statement in the Query Analyzer works fine. Is there some
> trick to putting paramterized values into an 'IN' clause?
>
>

Top