Optimizing LIKE in SQL statements

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
    - How to find in DataView if the primarykey more than one We can use DataView.Find("PrimaryKey") to find a primaykey value a DataView. but How can we find if the Prmarykey is compound with more than one keys? Tag: Optimizing LIKE in SQL statements Tag: 120199
  - 2
    - "Could not execute query against OLE DB provider 'MSIDXS'" Hi, I am trying to use Indexing Server to search for Arabic contents in files. First, using Indexing Server, when I write the arabic word and execute the search, I get the error message: "the query contained only ignored words". I switched the Locale on the machine to Arabic. It worked fine. Now, I have a stored procedure, that uses a linked server to the indexing server. The stored procedure takes the contents to look for, and returns a list of files where the search expression was met. Executing the stored query using the Query analyzer works fine. However, when I use ADO.NET to execute the query, I get the following error message: "Could not execute query against OLE DB provider 'MSIDXS'". Any ideas? Thanks in advance, Sharko Tag: Optimizing LIKE in SQL statements Tag: 120198
  - 3
    - Copy tables from Oracle to Access FAST? I have used the code below to copy data from one Access file to another Access file, and it's seems to be pretty fast. When the source tables are in Oracle and using an OracleConnection as source, my insertstring won't work anymore. Will I have to start filling datasets, traverse the rows and copy each field in for-loops? Won't that be terribly slow? Is there a faster way? //Destination Access file exists and has the same tablenames and fieldnames as the source, so: OleDbConnection conSrc = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;"+ "data source=" + @"c:\source.mdb" ); conSrc.Open(); string insertstring = @"INSERT INTO tablename IN "c:\destination.mdb" SELECT * FROM tablename"; OleDbCommand cmd = new OleDbCommand(insertString, conSrc); cmd.ExecuteNonQuery(); Tag: Optimizing LIKE in SQL statements Tag: 120192
  - 4
    - DataGrid page level delete all function Hi, Here is my issue. I have a datagrid on my aspx page. I have paging enabled for the datagrid. Suppose Suppose I have 3 pages in my datagrid. When I go to the last page (which is the 3rd page here) and select all the messages and delete, since the delete is on a page level, it deletes all the messages on that page. But the problem is that since now my datagrid doesnt' have a 3rd page and I am still on the 3rd page, its throwing an error. Any ideas/solutions on how to tackle this. Thanks in advance. Pradeep Tag: Optimizing LIKE in SQL statements Tag: 120191
  - 5
    - Can't open Excel spreadsheet from within .Net web app on Windows XP This is a multi-part message in MIME format. ------=_NextPart_000_0008_01C5DBD9.D060ACF0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I'm trying to write an import utility for a web app that I am developing = and I'm using an Excel spreadsheet to hold the data to be imported. I'm using ODBC and this is my connection string-- "Driver=3D{Microsoft Excel Driver (*.xls)};Dbq=3D" + = HttpContext.Current.Server.MapPath(".//" + csv_store_read);" where csv_store_read is the directory where the Excel file is. My application has to use a user account other than the anonymous = internet account due to the requirements of the external system that it = is accessing. I also have Impersonate set to true in the Web.config = file. I've also hard coded the file path to point to the file on the local = machine and neither are working. I keep getting the following error: [Microsoft][ODBC Excel Driver] Disk or network error. This works fine on my Windows 2000 machine so I know it has something to = do with security but I've tried everything I can think of to no avail. Any ideas as to what I am missing? TIA, Bill Youngman ------=_NextPart_000_0008_01C5DBD9.D060ACF0 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.2900.2627" name=3DGENERATOR> <STYLE></STYLE> </HEAD> <BODY> <DIV><FONT face=3DArial size=3D2>I'm trying to write an import utility = for a web app=20 that I am developing and I'm using an Excel spreadsheet to hold the data = to be=20 imported.</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV><FONT face=3DArial size=3D2>I'm using ODBC and this is my = connection=20 string--</FONT></DIV> <DIV><FONT face=3DArial size=3D2></FONT> </DIV> <DIV> <P><FONT face=3DArial size=3D2>"Driver=3D{Microsoft Excel Driver = (*.xls)};Dbq=3D" +=20 HttpContext.Current.Server.MapPath(".//" + csv_store_read);"</FONT></P> <P><FONT face=3DArial size=3D2>where csv_store_read is the directory = where the Excel=20 file is.</FONT></P> <P><FONT face=3DArial size=3D2>My application has to use a user account = other than=20 the anonymous internet account due to the requirements of the external = system=20 that it is accessing. I also have Impersonate set to true in the = Web.config=20 file.</FONT></P> <P><FONT face=3DArial size=3D2>I've also hard coded the file path to = point to the=20 file on the local machine and neither are working. I keep getting the = following=20 error:</FONT></P> <P><FONT face=3DArial size=3D2><EM>[Microsoft][ODBC Excel Driver] Disk = or network=20 error.</EM></FONT></P> <P><FONT face=3DArial size=3D2>This works fine on my Windows 2000 = machine so I know=20 it has something to do with security but I've tried everything I can = think of to=20 no avail.</FONT></P> <P><FONT face=3DArial size=3D2>Any ideas as to what I am = missing?</FONT></P> <P><FONT face=3DArial size=3D2>TIA,</FONT></P> <P><FONT face=3DArial size=3D2>Bill = Youngman</FONT></P></DIV></BODY></HTML> ------=_NextPart_000_0008_01C5DBD9.D060ACF0-- Tag: Optimizing LIKE in SQL statements Tag: 120186
  - 6
    - Create DataView or DataTable from DataSet Hello, I know the subject sounds sort of backwards, but I created a DataSet by using three different DataAdapters with different queries (each query joins two tables in my database) and filled individual DataSets for each DataAdapter. Then I merged the three DataSets to one, and now I have a DataSet but no defined tables, etc. (or, at least not that I know of!). I want to create a DataView so I can sort contents of a DataGrid (whose datasource is based on my resulting DataSet), but I don't know how to create a DataView if I never defined DataTables. Maybe there was a more straightforward way of defining the original DataSet? I'm trying to keep it simple and not too complicated. My project is using C# and not VB. Any help would be appreciated! Thanks, Christine Tag: Optimizing LIKE in SQL statements Tag: 120185
  - 7
    - newbie needs help with a web service for infopath I've been trying to figure this out but can't seem to grasp it. I'm very new to web services and am trying to create a simple one that can receive and submit to a sql table to be used by Infopath. I have been able to do this thanks to this microsoft article. http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odc_ip2003_tr/html/odc_INF_Lab_09.asp Now I want to be able to use parameters to create query fields in infopath so that these results can be easily filtered. This is where I'm lost. I've tried using the SqlParameters but when I add them into the select statement it makes the web service not work. (says it's missing an expected parameter which was not supplied) I'm assuming I'm missing code to be put into the webmethod but I don't know what goes in. Any help would be greately appreciated. thanks. Tag: Optimizing LIKE in SQL statements Tag: 120184
  - 8
    - How to compact an Access Database under VB.Net? Hi, In VB6, and DO, I used to progam this simply by Compactdatabase(SOurce, Destination) And also I used : repairdatabase (MyDataBase) These where standard DAO.DBEngine methods How do I do that in pure VB.Net? Thanks in advance GB6 Tag: Optimizing LIKE in SQL statements Tag: 120180
  - 9
    - no primary key on union query Hello, I need to find a string value in a dataset table which is the result of a union query that does not have any primary keys. Is there another method or approach other than find and contains that I could use that does not require primary keys or is it possible to "supply" a primary key on the query result? Thanks, Mike Tag: Optimizing LIKE in SQL statements Tag: 120176
  - 10
    - "General Network Error" and Connection Pooling I have a very simple WinForms app, that uses a sqlDataAdapter retrieve data into a dataset. The dataset is set to be the datasource of a DataGrid. If - while editing rows in my grid - i lose my network connection (or stop + start my sqlserver), i get a "General Network Error" when I call my dataAdapters .update method. (...naturally, after reestablishing network connection, that is...) However, if i disable connection pooling, I get no error. Also, with connection pooling enabled, if I repeatedly call update, eventually it succeeds on the third attempt. Anyone who can smarten me up? J. Jespersen Denmark Tag: Optimizing LIKE in SQL statements Tag: 120167
  - 11
    - Newbie - Datagrid Binding Problem Hi There Thanks for Everyones Help So Far, I used the Database Form Wizard and i`ve looked at he code from that. Its working Great Now. Now the Only Problem I have is i`ve added a Datagrid to my App Aswell, Makes it easier then clicking next a few hundred times to get to the record I want. Ok I could just use a datagrid but it looks a lot nicer if the Datagrid is there aswell. I`ve marked my Datagrid as Read only and want the user to be able to click on the row they want and for that. When I click on my Row on my datagrid is dosn`t update my Position_Changed thinggy which tells them what record number there one. Here is my Position Changed Code: Private Sub dsSystemUsers_PositionChanged() Me.lblNavLocation.Text = (((Me.BindingContext(dsSystemUsers, "Passwords").Position + 1).ToString + " of ") + Me.BindingContext(dsSystemUsers, "Passwords").Count.ToString) End Sub I take it I need to Add a Line for My Datagrid I just can`t find any info. Anywhere on how to do it:( Anybody point me in the right Direction please? Many Thanks NSC Tag: Optimizing LIKE in SQL statements Tag: 120160
  - 12
    - Create EnterpriseLibrary.Data.Database object from connection stri Is there any way to create a Microsoft.Practices.EnterpriseLibrary.Data.Database object without reading from a configuration file? I am writing a utility that manages databases in SQL Server. The utility queries SQL Server for the database names. So I don't know the names of the databases beforehand, but I will know the names and connection strings once the server has been queried. However, I have a class that I'd like to work with that requires a Microsoft.Practices.EnterpriseLibrary.Data.Database object. Is there any way to create a Microsoft.Practices.EnterpriseLibrary.Data.Database when I already know the connection string? Thanks in advance Tag: Optimizing LIKE in SQL statements Tag: 120150
  - 13
    - Dataset with large tables Please someone explain this to me ! From what I understand ADO.NET is disconnected. Ok. I have Windows app with a student table with about 15000 students. In ADO I would open a server side cursor and page thru the table in a custom grid I built. So at most I would retrieve about 20 records or so per page (depending on no of displayable rows). Now with ADO.NET I will have to send the whole 15000 records to the client in a DataSet. How can this be good ? I just don't get how everyone claims server side cursor slow down an app. In ADO I retrieve 20 records at a time in a grid and it's instataneous. In .NET it's slowwwwwww. This is just one example but my database is extremely large (over 2000 SQL server tables). If I have many users opening the student module at the same time, they have to wait and wait to get those 15000 records when I only need an initial set of records to display the first page... I just don't understand how real systems are done in a disconnected manner. Tag: Optimizing LIKE in SQL statements Tag: 120145
  - 14
    - Question about creating Access tables in VB.Net using ADOX I'm writing a VB.Net program. At startup the program looks for a default Access mdb and if it isn't there, creates it. At least that's the design. My question is this: Using ADOX, is there anyway to declare an AutoIncrement table field? I don't see anything in the DataTypeEnum that seems equivalent. Dim tbl As New Table tbl.Name = "Table1" tbl.Columns.Append("Field1", ??????? ) Tag: Optimizing LIKE in SQL statements Tag: 120144
  - 15
    - There is no row at position x I am getting the exception "There is no row at position 5" on a dataview that has 10 rows (count = 10) The original dataset, i.e. unfiltered, has 12 rows. when it is filtered, there are 10. i can look in the dataview and see all ten rows. but when i am in the foreach(DataRowViw pr in orderDV) loop i get the error when i try to access a field in the sixth row (position 5). i cannot figure out why i keep getting this exception . does anyone know why? thanks m I have included the code that causes this error: string Filter = "BatchID = " + BatchID.ToString(); // usullay BatchID = 0 DS.Tables[OrderTable].DefaultView.RowFilter = Filter; DataView orderDV = DS.Tables[OrderTable].DefaultView; foreach(DataRowView pr in orderDV) { string Orders_Id = pr["OrderNumber"].ToString(); // why do i get an exception here? //pr["OrderNumber"] {"There is no row at position 5." } System.IndexOutOfRangeException foreach (DataRow cr in pr.Row.GetChildRows("OrderCollection")) { } pr["BatchID"] = 1; // assign new BatchID } Tag: Optimizing LIKE in SQL statements Tag: 120140
  - 16
    - OLE DB Connect Help Needed Hi everyone, I'm having a problem connecting to a mySQL database using OLEDB with vb.net. open3 = New OleDbConnection open3.ConnectionString = "Provider=SQLOLEDB; Data Source=www.db.edu; Initial Catalog=xxx; User Id=XXX; PWD=XXX;" Doesn't work: "Access Denied or server doesn't exist" Yet: open2 = New OdbcConnection open2.ConnectionString = "Provider=MSDASQL; DRIVER={MySQL ODBC 3.51 Driver}; SERVER=" & txtServer.Text & ";" & "PORT=3306; DATABASE=xxx;" & "USER=" & txtUser.Text & ";" & "PASSWORD=" & txtPass.Text & ";" & "OPTION=3" -- and -- open = New MySqlConnection open.ConnectionString = "server=" & txtServer.Text & ";" & "user id=" & txtUser.Text & ";" & "password=" & txtPass.Text & ";" & "database=xxx" both work just fine. They all point to the same database, use the same login and pass, and call the same database. Thanks, matt Tag: Optimizing LIKE in SQL statements Tag: 120139
  - 17
    - get DataColumn metadata hello all, i am looking for a way to get metadata of a DataColumn (in c#). i want to write a switch-case statement(if it possible) that check the Column DataType as declared at the database (adChar, adVarChar,adDate, adDBDate, adDBTimeStamp,adDecimal, adSingle, adDouble, adNumeric, ..., ....) and then to get for each one it's 'DefinedSize' and 'NumericScale' values. in ADO Recordset it was very simple, what is the best & right way to do it in ADO.net (C#)? thank's stan Tag: Optimizing LIKE in SQL statements Tag: 120134
  - 18
    - DataColumn expression to create new Guid I would like to set up a DataColumn in my table of type Guid and a new Guid(Guid.NewGuid) would be the expression that gets evaluated when ever a new row was added. I have the DataSet bound to a grid so I would like to set this up at compile time. The Guid DataColumn is also the primary key so it has to be set when a row is added. Thanks, Marc Tag: Optimizing LIKE in SQL statements Tag: 120127
  - 19
    - User Controls and ADO Connections I have 3 User Control .ascx files that are included in a base .aspx file. In each ascx file, as well as the base aspx file, I make ADO.Net calls that require a datatbase connection. It appears that I have to open and close the connection in each file (i.e. I can not reuse the open connection between all 4 pages)? This means that I must open and close the connection 4 times when servering one page (base aspx and 3 ascx files). Is there any way that I can accomplish this without so opening and closing the connection so many times? Wouldn't this become a scalability problem if I continually have to open and close connections multiple times on each page? Tag: Optimizing LIKE in SQL statements Tag: 120112
  - 20
    - problem with axMsChart in scal of x and y axis Hello Guys, I working in C#. I have problem with axMschart contorl. I m not able to change the scale type of x axis. By default it is DateTime. I want it to float/double. Please help out me . thanks in advance Tag: Optimizing LIKE in SQL statements Tag: 120110
  - 21
    - One or many DataSet Hi, I am working with WB.NET and SQL Server. I have some trouble grasping what a DataSet in VB.NET is. Do the DataSet mirror the whole database used as the data store for one application or is it recommended to use multiple Dataset for this database (one application). Example: My DataSet have 3 tables: Table1, Table2 and Table3. Table2 has 2 columns, CustomerID and OrderID Table1 has 1 column CustomerID Table3 has 1 column OrderID. For GridList1 I need to define the CustomerID as the PrimaryKey in Table2 and link it to Table1's CustomerID For GridList2 I need to define the OrderID as The PrimaryKey in Table2 and link it to Table3's OrderID. Then I have one Table (Table2) with two different PrimaryKeys depending on witch Grid I want to display my data in. Do this work or do I have to make two different DataSets, one for Table1 and Table 2 and one for Table2 and Table3? My tables have quite a lot of data. When I fill my Datasets with data from the database, can I choose witch table in the DataSet I want to fill or do I have to fill the whole DataSet at once? Is it best practice to keep the amount of DataSets small but with many tables or it is best practice to have many DataSets but a small amount of tables in each one of them? Thanks in advance :o) Tag: Optimizing LIKE in SQL statements Tag: 120104
  - 22
    - SqlDataReader Ok this one has me stumped. Everything looks right, but for whatever reason the DataReader keeps telling me not set to an instance of an object. When I step through the code, it runs the line SqlDataReader mySqlReader; However I can see in the watch value that the mySqlReader is set to an undefined value When it gets to the mySqlReader = sc.ExecuteReader() command I get the not set error Anyone have any thoughts? Code below <code> int [] results; results = new int[5]; SqlDataReader mySqlReader; sc.Connection = sqlConn; sc.CommandTimeout = 450; try { sqlConn.Open(); mySqlReader = sc.ExecuteReader(); while(mySqlReader.Read()) </code> Tag: Optimizing LIKE in SQL statements Tag: 120100
  - 23
    - Retrieving Excel Schema Information Hello, I am retreiving Excel Schema Information from a Excel Sheet. I am using GetOleDBSchemaTable for it. I see a strange behaviour sometimes that when I retrieve the Column names from a particular Excel file, It returns the column names which are there as well as some extra columns like "F1,F2.,..." Can anyone please tell me if this is a bug or I am doing something wrong. Any help is appreciated. Regards, Rohit Tag: Optimizing LIKE in SQL statements Tag: 120088
  - 24
    - How do I specify a 1 to many relationship? I want to specify a relationship between two data tables in a dataset. (Both data tables come from executing stored procedures in SQL Server.) When I tried to add the relationship through the Relations property of the dataset, I got the following error message: "The columns don't currently have unique values." I believe the problem is there is a one-to-many relationship between the parent data table and the child data table. So my question is how do I specify that when I create the relationship? Is there a property of the DataColumn object that I have to specify? Right now I'm trying to link the two by common columns (client number and case number); should I specify some other column in the child's DataColumn which would be unique for each client/case? (I didn't do this in this case, because the example I saw in David Sceppa's book "Microsoft ADO.NET" didn't do this. I had thought that I had to specify only those columns from both data tables which would be linked between the parent and child DataColumns.) I am using ADO.NET 1.1. Rod Tag: Optimizing LIKE in SQL statements Tag: 120081
  - 25
    - ADO.Net Best Practices Validation Tool Does anyone know of a tool that can look at C# code and make sure that all open datareaders are closed as well as validate other ADO.Net best practice principles? I have a team of 10 developers and we recently had a bug in our code that claims that an existing connection can't be used because it is associated with an open datareader. Unfortunately, looking through all the code is blinding me and so I wondered if there was a tool that could do it for me. Any and all help is appreciated. -- Rodney Tag: Optimizing LIKE in SQL statements Tag: 120077
- Next
  - 1
    - DataTable.Select Hi! I am using the following code in my vb.net application.. its working fine on windows 2000 professional and XP Professional, but its not giving me appropriate result when running on a Windows 2003 Server. Dim tblFileList As DataTable Dim drCORows As DataRow() Dim strExFileName As String strExFileName = ConfigurationSettings.AppSettings("ExOthForm") tblFileList = dsFileList.Tables(0) strSort = "FormSequence ASC" drCORows = tblFileList.Select("CopyTo='CO' AND FilePath NOT IN (" + strExFileName + ")", strSort) Please help Thanks, Baren Tag: Optimizing LIKE in SQL statements Tag: 120074
  - 2
    - Perform JOIN in dataset? I would like to pull data from two different databases into one dataset (using a DataAdapter with different commands and connections, and filling that one set with the different results). With this data, I need to perform a JOIN to get the records from those two databases that have a common id (WHERE table1.id = table2.id). How can I do this? I couldn't do a JOIN with my original query that was used in the SelectCommand portion of the DataAdapter because the OracleCommand only takes one connection (to one database). I don't want to get too fancy, so if I can keep it simple it would be better for me. I'm also using C# and not VB. Thanks! Christine Tag: Optimizing LIKE in SQL statements Tag: 120053
  - 3
    - Using Diffgrams for multiple insert/update/delete Hi, I want to do multiple insert/update/delete of multiple rows using a single stored procedure using diffgram. Can I please get a small sample/link for achieving this. I just need the the stucture of the stored proc as I have the diffgram generated successfully. Advanced thanks. -- Samba! Tag: Optimizing LIKE in SQL statements Tag: 120051
  - 4
    - Reading Excel using OLEDB When I read excel file sheet as table through ADO.NET using OLEDB it does not return all data. I know that it scans 8 rows for deciding about datatype and it can be changed to 16 rows. I changed it to 16 rows but still does not work. I know what is happening, it finds the column name which obviously string type on 8th column in my file and probably it guess the columns as string type.. After that I have all numeric data for that column. It does not return all these data Is there any work around ? Tag: Optimizing LIKE in SQL statements Tag: 120050
  - 5
    - When to use new DataSet? When to perform DataSet resident joins? Whenever I create a new database query, I have to answer a couple of questions that are even harder to ignore if you are using visual studio: (1) Should I create a new table in an existing dataset or a new dataset? Is there peril in having one table per dataset and having lots of data sets? (2) Should I use a generic late binding dataset or have visual studio create a custom early binding dataset class for me? (3) Should I implement my one to many relationship in the SQL with an INNER JOIN clause or should I create two datatables and join them in the dataset using the visual studio dataset GUI designer using drag and drop? (4) If I have a many to many relationship, is it possible to implement this in the dataset GUI designer instead of in the SQL with INNER JOIN statements? What would be the advantage? (5) Am I going to supply the dataset with add, delete and update objects? What is the advantage of assigning these Command objects to the Dataset instead of just calling them directly as needed if they are needed? (5a) When you are configuring a new data adapter, you get the oppertunity to let visual studio automatically generate the SQL statements to INSERT, DELETE and UPDATE and assign their respective command objects to the dataset. I've done it and it even worked, but I did not feel like I understood it. How does visual studio know how to automatically generate the SQL update, add and delete SQL statements when you have a complex join statement anyway? (5b) When is visual studio not able to automatically generate the UPDATE, DELETE, and INSERT SQL statements and their respective command objects? I received a message from Visual Studio recently complaining that I was not using enough PK to automatically generate the UPDATE, DELETE and INSERT SQL statements. This was OK since I could not imagine how Visual Studio was going to know how to implement these statements anyway. Was it because I was joining some fields that were secondary indices? I've successfully used the dataset GUI designer to connect the foreign key field to the PK of another table. (I'm not talking about the query designer here -- this is connecting fields in the dataset after they have been read out of the database tables). It worked. I was impressed but I was not (still not) clear on the advantage. When I tried to do the same thing with a many to many I could not figure out how to do it. So I'd love some help on the perils and merits to each approach! Thanks, Siegfried Tag: Optimizing LIKE in SQL statements Tag: 120046
  - 6
    - ADO .NET ODP Equivalent of DBMS_SESSION.SET_CONTEXT Is there a way using the ODP for .NET (Oracle 9i, .NET 1.1) to set the context for calling a stored procedure, using .NET code? For example, I want to do the equivalent of the following: DBMS_SESSION.SET_CONTEXT('rowlevelaudit_ctx', 'userid', 'Chris'); in VB .NET so that during subsequent calls, I can do the follwoing: SELECT SYS_CONTEXT ('rowlevelaudit_ctx', 'userid') INTO v_username FROM DUAL; The greater goal here is to have row level auditing by user, while still having ony a single login to the database. I want to avoid having to pass in each individual's userid to every single stored procedure. Any help would be greatly appreciated, Chris Tag: Optimizing LIKE in SQL statements Tag: 120041
  - 7
    - Relational Database Samples in C# Can anyone point me to samples of Relational Databases (MSAccess or SQL Server) that show how to update and add records to relational tables? Any help would be greatly appreciated. Thanks. Tag: Optimizing LIKE in SQL statements Tag: 120038
  - 8
    - FYI: Sudden timeouts when executing SqlCommand I had a problem recently where a SqlCommand would work for weeks at a time, and then start timing out. My SqlCommands always invoke stored procedures, and the stored procedures always return the smallest amount of data possible (usually only one row or using output parameters). I had no idea what was causing the problem. The code hadn't changed. I wracked my brain looking at the code, stepping through it, checking the objects, viewing stuff in the debugger, blah blah blah. Everything seemed peachy. Then I pointed to a duplicate database on a different server. The code executed without a hitch. I compared the stored procedures and everything matched up. There was no problem there. After another few weeks of searching for the cause of this mysterious error, we pinned it down to one thing: A monstrously huge transaction file. Somehow, the SqlAgent on the development server had been shut down for about a month (prolly by a wayward developer), and the backups weren't running. Consequently the transaction log had reached a size in excess of 15GB. Not surprisingly, the drive was down to 50MB free. We reenabled the backup, ran it, and truncated the logs; everything ran just beautifully after that. If your SqlCommands start inexplicably timing out, check your transaction log, and check the free space on the drive. It may not be the cause of YOUR problem, but you'll be able to rule it out. Hope this helps! Tag: Optimizing LIKE in SQL statements Tag: 120037
  - 9
    - no value given for one or more required parameters - error Hi all, Why am I getting a 'no value given for one or more required parameters' error when I try to fill the dataset. Below is the code I'm using and I'm getting the error on the DASOURCE.FILL(DSSOURCE, "FIELDS"); part of the code. objSourceConn is fine. Can anyone help? Thank you. string selectFromSource; selectFromSource = "SELECT * FROM Fields WHERE ['Label name'] = '" + drDistinctSource["Label name"].ToString() + "'"; OleDbDataAdapter daSource = new OleDbDataAdapter(selectFromSource, objSourceConn); DataSet dsSource = new DataSet(); daSource.Fill(dsSource, "Fields"); -- Message posted via DotNetMonster.com http://www.dotnetmonster.com/Uwe/Forums.aspx/dotnet-ado-net/200510/1 Tag: Optimizing LIKE in SQL statements Tag: 120034
  - 10
    - Connect a TableAdapter to an already existing DataTable hejdig. I have a DataTable from before and now want to connect it to a database. I havn't found a way, through VS.Net2005rc1, to create a TableAdapter and connect it to an already existing DataTable. Is there a way (besides hacking the XML)? TIA /OF Tag: Optimizing LIKE in SQL statements Tag: 120030
  - 11
    - UpdateCommand doesn't work fine. Hello, I've implemented a control that works with a database oracle, with dataset and dataadapter. It works fine, inserts, delete, and update works fine when the sentence is like "update table set a=:1,b:1", but when the update is "update table set a=:1,b=:2 where c=:1", it says "Concurrency Infraction: Update Command affected 0 register" or "ORA-01722: Incorrect number". It makes the sentence dynamically the code is: If txt.isPrimaryKey Or txt.ConcurrencyField Then If Not primerw Then where &= " AND " Else primerw = False End If where &= txt.FieldName & " =:" & txt.FieldName Else If Not primerc Then strupdate &= "," Else primerc = False End If strupdate &= txt.FieldName & "=:" & txt.FieldName End If If param Is Nothing Then ReDim param(0) Else ReDim Preserve param(param.Length) End If param(nparams) = New OracleParameter param(nparams).ParameterName = ":" & txt.FieldName param(nparams).OracleDbType = txt.FieldType param(nparams).SourceColumn = txt.FieldName If txt.isPrimaryKey Or txt.ConcurrencyField Then param(nparams).SourceVersion = DataRowVersion.Original Else param(nparams).SourceVersion = DataRowVersion.Current End If nparams += 1 Thanks in advance Silvia. Tag: Optimizing LIKE in SQL statements Tag: 120026
  - 12
    - DataTable.Select method in .NET 2.0 Hi, I have a problem with DataTable.Select method in Microsoft .NET 2.0. I have a ListBox control, with DataSource pointing to a DataTable "RolePermissions". ListBox has SelectionMode property set to "Multiple". I would like to enable User to Delete Multiple items with just one click. I am almost sure, that this worked in .NET 1.1. I have a DataTable with column "permissionID" of type uniqueidentifier Here is my Code: // We will concatenate selected Guid's into one string StringBuilder sbItemsSelected = new StringBuilder(); // for every Item .... foreach (ListItem item in lbRolePermissions.Items) { // ... which is selected... if (item.Selected == true) { // ...we add selected ID, and space after it sbItemsSelected.Append("'"); sbItemsSelected.Append(item.Value); sbItemsSelected.Append("' "); } } /// We now have list of ID's, separated with spaces (with space at he end) string selectedItems = sbItemsSelected.ToString(); // remove last space selectedItems = selectedItems.Trim(); // insert commas instead of spaces selectedItems = selectedItems.Replace(" ", ","); // get the table, to handle data DataTable dtRolePermissions = Session["RolePermissions"] as DataTable; /// select rows, which are of interest to us ******************************************************************** DataRow[] drsRolePermissions = dtRolePermissions.Select("permissionID IN (" + selectedItems + ")"); ********************************************************************* /// delete them foreach (DataRow drRolePer in drsRolePermissions) { drRolePer.Delete(); } /// save new Table in Session variable Session["RolePermissions"] = dtRolePermissions; // bind the Table again lbRolePermissions.DataSource = dtRolePermissions; lbRolePermissions.DataBind(); The line inside "stars" is line, in which an exception occurs, the exception Message is: "Cannot perform '=' operation on System.Guid and System.String." I thought, that exception can occur because the Items in listBox are filtered with an expression, but that would be stupid of Microsoft not to let such operations. I am confused.... this really worked in .NET 1.1 I used it a lot in previous applications.... Is there some explanation to this fact ? ( apart from saying, that this is a bug ;-) ) Thank You in advance for help, maciu Tag: Optimizing LIKE in SQL statements Tag: 120024
  - 13
    - Detecting detail datagrid rows changing Hi All I need to detect when the row count in a detail grid changes as the user navigates on the Master datagrid I need to change one of the column widths on the detail grid to allow for the scrollbar appearing and disappearing. I can't seem to find an event which fires when the detail grid changes I am using VB 2003 and use a dataset to bind the 2 grids with a relation Regards Steve Tag: Optimizing LIKE in SQL statements Tag: 120023
  - 14
    - Writing back data (Update) Hello, I have created a connection of a Siebel Database (or to be more specific Sybase SQL Anyware), and I am able to retrieve data and view it contents (Tables, Views Stored Procs) using odbc from ADO.NET. The problem I am having is trying to Update a table. It does not appear that I have permission or there is some other type of commands I should be using besides SQL statements. I really need information on writing data back to this database. Any information would be greatly appreciated. If necessary you will be able to VPN onto the computer to accomplish this. I would also be interested in a referral or consultant. Siebel has been no help, especially since Oracle has agreed to purchase them. Thrid party drivers are ok to, providing they do not require individual lisences for each computer that is using it. Thanks, Chuck Tag: Optimizing LIKE in SQL statements Tag: 120021
  - 15
    - How to know a column whether indexed or not in sql2k Tag: Optimizing LIKE in SQL statements Tag: 120011
  - 16
    - Odd Behavior Copying VarBinary Data I'm copying the content from one DataTable to another. Both tables have a SQL column of type VarBinary. The data is actually copied this way. DataRow[Column] = DataRow[Column]; Would this method cause a problem with VarBinary data types? The problem I'm seeing is that the data seems to be duplicated. So, if the both rows contained the value "1 2 3" after the copy I'm seeing a value of "1 1 2 2 3 3" in the row where the data was copied to. Any thoughts ideas are appreciated. Tag: Optimizing LIKE in SQL statements Tag: 120005
  - 17
    - DataTable Adapters and Oracle Provider (2005) Using the GUI tools to have a Table Adapter's Fill& GetData proc point to a SPROC in the database (or a function) works well if the proc is a Function that has no parameters, OR if it is a proc and you are using SQL Server. In my case, I want to use functions inside Oracle, and want to pass parameters. First I tried using SPROC. Note I'm not trying to return a cursor here - my goal is simply to get the SPROC to run. CREATE OR REPLACE PROCEDURE getScreenByApparatus (parmapparatusID in varchar) IS myCursor doe_pkg.TPI_Cursor; BEGIN open myCursor for Select scr.screenID, scr.screenType,scr.Description, scr.userid from screen scr, screenprocessstep sps where sps.apparatusID = 1 -- parmApparatusID and sps.screenID = scr.screenID; end getScreenByApparatus; --- End SQL --- In the DataSet designer, I set the SCREENTableAdapter (attached to the SCREEN object I dragged over) to use these values: Command Text: getScreenbyApparatus CommandType: StoredPrcocedure ExecuteMode: Reader Parameters: Parameter1: DbType: AnsiString Direction: Input ProviderType: VarChar I Right click Preview Data, select the table and procedure, and fill in the Parameter (either Hi There, "Hi There" or "hiThere") I then get back a dialog: Could not be previewed: ORA-6550: line 1 column 7: PLS-00306. wrong number or types of arguments in call to 'GETSCREENBYAPPARATUS' ORA-6550:line 1, column 7 PL/SQL: Statement ignored. (I know I have a select statement that's pointing into a cursor, and I've tried this with an OUT cursor as the second parameter, OUT cursors as return values, but will skip that discussion until I get the basics working). This all works smashingly well in SQL Server. What does it all mean? However, when I create the For example, my Oracle -- Senior Developer Transform Pharmaceuticals Tag: Optimizing LIKE in SQL statements Tag: 120003
  - 18
    - using webrequest Hi I have a requirement to submit form data to a url after collecting the data from the database. For example I need to send firstname and lastname through a post method I can call on the fly. This is the traditional route below , I want to emulate this submit action from a vb.class using request.method="post" <FORM action="http://somesite.com/prog/adduser" method="post"> First name: <INPUT type="text" name="firstname"><BR> Last name: <INPUT type="text" name="lastname"><BR> email: <INPUT type="text" name="email"><BR> <INPUT type="submit" value="Send"> <INPUT type="reset"> </FORM> clear as mud? Anyone have any tips ? Tag: Optimizing LIKE in SQL statements Tag: 120001
  - 19
    - Intermittent SP error - "has too many arguments" Hello, I have an audit table into which I insert information about the use of the application. This works sometimes and other times fails. I cannot find any reason for it failing. It is always given the information. Here are the errors I receive; --------------------------- Exception Message: Procedure or function usp_UseAudit_ins has too many arguments specified. --------------------------- --------------------------- Exception Source: .Net SqlClient Data Provider --------------------------- --------------------------- Exception StackTrace: at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping) at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable) at CLIP.UseageAudit.InsertAuditRecord(String selection, String version) --------------------------- The call to the method \\ InsertAuditRecord("ManageJobs", _version) // The Method \\ Friend Sub InsertAuditRecord(ByVal selection As String, ByVal version As String) Call DAL.InsertUseAudit() Dim row As DataRow = _dataSet1.Tables("tblUseAudit").NewRow row("pkUseAuditId") = Guid.NewGuid.ToString row("ClipUser") = SystemInformation.UserName.ToString row("version") = version row("DateTime") = Now row("Selection") = selection _dataSet1.Tables("tblUseAudit").Rows.Add(row) Dim dsDataChanges As New CLIP.dsTables dsDataChanges = CType(_dataSet1.GetChanges, CLIP.dsTables) If (Not (dsDataChanges) Is Nothing) Then DAL.daUseAudit.Update(dsDataChanges, "tblUseAudit") _dataSet1.Merge(dsDataChanges) _dataSet1.AcceptChanges() // The DataAccess (DAL) \\ Public Sub InsertUseAudit() With cmdUseAudit_Ins .CommandType = CommandType.StoredProcedure .CommandText = "usp_UseAudit_ins" .Connection = sqlConn With cmdUseAudit_Ins.Parameters ' No return required - Does not work even with this incuded '.Add(New SqlParameter("@RETURN_VALUE", SqlDbType.Int, '4, ParameterDirection.ReturnValue, False, CType(0, Byte), 'CType(0, Byte), "", DataRowVersion.Current, Nothing)) .Add(New SqlParameter("@pkUseAuditId", SqlDbType.VarChar, 36, "pkUseAuditId")) .Add(New SqlParameter("@ClipUser", SqlDbType.VarChar, 50, "ClipUser")) .Add(New SqlParameter("@version", SqlDbType.VarChar, 50, "version")) .Add(New SqlParameter("@DateTime", SqlDbType.SmallDateTime, 4, "DateTime")) .Add(New SqlParameter("@Selection", SqlDbType.VarChar, 50, "Selection")) End With End With End Sub // Parts of the DataSet \\ Friend tblUseAudit As New DataTable Me.tblUseAudit = Me.Tables.Add("tblUseAudit") ' tblUseAudit Dim pkUseAuditId As DataColumn = Me.tblUseAudit.Columns.Add("pkUseAuditId", GetType(String)) pkUseAuditId.MaxLength = 36 pkUseAuditId.AllowDBNull = False Dim ClipUser As DataColumn = Me.tblUseAudit.Columns.Add("ClipUser", GetType(String)) ClipUser.MaxLength = 50 ClipUser.AllowDBNull = False Dim version As DataColumn = Me.tblUseAudit.Columns.Add("version", GetType(String)) version.MaxLength = 50 version.AllowDBNull = False Dim DateTime As DataColumn = Me.tblUseAudit.Columns.Add("DateTime", GetType(DateTime)) DateTime.AllowDBNull = False Dim Selection As DataColumn = Me.tblUseAudit.Columns.Add("Selection", GetType(String)) Selection.MaxLength = 50 Selection.AllowDBNull = False pkUseAuditId.ReadOnly = True tblUseAudit.PrimaryKey = New DataColumn() {tblUseAudit.Columns("pkUseAuditId")} The Table \\ CREATE TABLE [tblUseAudit] ( [pkUseAuditId] [varchar] (36) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [ClipUser] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [version] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [DateTime] [smalldatetime] NOT NULL , [Selection] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY] GO // The Stored Procedure \\ CREATE PROCEDURE dbo.usp_UseAudit_ins( @pkUseAuditId varchar(36), @ClipUser varchar(50), @version varchar(50), @DateTime smalldatetime, @Selection varchar(50) ) AS SET NOCOUNT OFF; INSERT INTO tblUseAudit( pkUseAuditId, ClipUser, version, DateTime, Selection ) VALUES ( @pkUseAuditId, @ClipUser, @version, @DateTime, @Selection ) /** no return required - doesn't work even with this included. ; SELECT pkUseAuditId, ClipUser, version, DateTime, Selection FROM tblUseAudit WHERE (pkUseAuditId = @pkUseAuditId) **/ GO // What could cause this error? Thank you, dbuchanan Tag: Optimizing LIKE in SQL statements Tag: 119991
  - 20
    - Connection String Problem Hi, I am trying to encrypt my connection string in the app.config file. I am using Base64 encryption, I just want to hide it from casual users. I keep getting file not found error. However when I just use the connection string in plain text I can connect fine. The only difference between the two is the @ sign is used in front of the connection string (plain) when I view them in the command window (see below). Any ideas? > ? this.connectionString "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\WINDOWS\\system32\csdm.dll;Jet OLEDB:Database Password=passwd;" > ? this.connectionString @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\WINDOWS\\system32\csdm.dll;Jet OLEDB:Database Password=passwd;" Tag: Optimizing LIKE in SQL statements Tag: 119984
  - 21
    - retrieve a column value from a row that might be DBNull I have a dataset that I want to update. I pull the row out that I want to update. There is a value in one of the columns that must be analized to determine if other values need to change. This works great as long as there is something in that column. If that DB column is empty, I get this error... "System.Data.StrongTypingException: Cannot get value because it is DBNull. ---> System.InvalidCastException: Cast from type 'DBNull' to type 'Decimal' is not valid. I have tried all sorts of things. I am wanting to get like, row.CCNum THis is great unless CCNumis empty. I've tried the method isDBNull(row.CCNum), but I still get the same error. How do I return a value from a DB and not error out if its empty? I have multiple columns to update, so I don't want to use an execute reader. I would like to use the 'row' type methods to get this done. Tag: Optimizing LIKE in SQL statements Tag: 119979
  - 22
    - Multiple Connections for a Dataset Hi, I have a method in a data access class (all in C#) where I'm trying to put information from different connections into a single dataset to return to the calling class. Any ideas on how I can do this? I only know how to pull data from different tables using ONE connection. I have data in a completely different connection that I need to pull from. This is what I currently have: Connection.Open(); myDataAdapter = new OracleDataAdapter(); myDataAdapter.SelectCommand = new OracleCommand( query, base.Connection ); myDataSet = new DataSet(); myDataAdapter.Fill( myDataSet ); return myDataSet; This fills my dataset using the query and only one connection. I don't want to have to pass connection strings to the method that does this. Any ideas would be greatly appreciated! Christine Tag: Optimizing LIKE in SQL statements Tag: 119977
  - 23
    - Filtering question with related tables Hi guys, I've been struggling with this one for a while. I have a typed dataset. It has two tables inside of it; Resources and EmployeeMaster. Both EmployeeMaster and Resources have a column called "EmpID", an int, which is also both of their primary keys. This is the relationship between the two, and is present in the dataset. EmployeeMaster has detailed data about employees such as full name, address, email, etc. Resources is a table that I created that is used to determine which employees are using a program I am writing right now, and what their security level is. That column is of type int called "UserType". So this is all set up, my dataset is populating nicely with every row from both tables, everything seems to be set. Now, what I want to do is populate a listbox on a C# Windows.Forms app with employee names (stored only in EmployeeMaster) of only those employees who have a UserType (stored only in Resources) of 5. The equivalent SQL for this is simple. "SELECT emp.LastName FROM EmployeeMaster emp, Resources r WHERE r.UserType = 5" I can't figure out how to get the equivalent of this to work in my typed DataSet so I can databind it to my listbox. I looked at DataViews, but couldn't find any decent examples of the RowFilter that was filtering on a child table. I did find one page that said there's a "[child]" keyword that should be put in there, but I couldn't figure that out. I also looked at DataViewManagers, but hit a dead end on that one as well. Can one of you gurus give me a little guidance, please? I feel silly asking what's probably a really simple question, but I've scoured the net for hours and haven't found anything. Thank you VERY much to any and all help! Andy K Tag: Optimizing LIKE in SQL statements Tag: 119973
  - 24
    - creating an xsd through xsd designer - xml format Hi all, I need some help: I have a DataSet object created by the .NET "xsd designer". The DataSet is being filled from a Stored Procedure which returns the results of a few select statements. I have no problem filling the DataSet, but when I use the method GetXml() I get an XML string that is not in the format I wanted. I get somthing like this: <Table> <Field_1>1</Field_1> <Field_2>eng</Field_2> </Table> <Table1> <Field_1>1</Field_1> <Field_2>eng</Field_2> </Table1> When actually I wanted something like this: <Table> <Field_1>1</Field_1> <Field_2>eng</Field_2> <Table1> <Field_1>1</Field_1> <Field_2>eng</Field_2> </Table1> </Table> I created the xsd by dragging & droping tables in the designer. I put "Table1" into "Table" when I did that, but nothing happened. Is there a way to force a selected format on the XML I generate from the data in such a DataSet, or do I have to create the whole thing by code? Thanks! Lea *** Sent via Developersdex http://www.developersdex.com *** Tag: Optimizing LIKE in SQL statements Tag: 119969
  - 25
    - How to force a specific format on xml generated by xsd Hi all, I need some help: I have a DataSet object created by the .NET "xsd designer". The DataSet is being filled from a Stored Procedure which returns the results of a few select statements. I have no problem filling the DataSet, but when I use the method GetXml() I get an XML string that is not in the format I wanted. I get somthing like this: <Table> <Field_1>1</Field_1> <Field_2>eng</Field_2> </Table> <Table1> <Field_1>1</Field_1> <Field_2>eng</Field_2> </Table1> When actually I wanted something like this: <Table> <Field_1>1</Field_1> <Field_2>eng</Field_2> <Table1> <Field_1>1</Field_1> <Field_2>eng</Field_2> </Table1> </Table> I created the xsd by dragging & droping tables in the designer. I put "Table1" into "Table" when I did that, but nothing happened. Is there a way to force a selected format on the XML I generate from the data in such a DataSet, or do I have to create the whole thing by code? Thanks! Lea ********************************************************************** Sent via Fuzzy Software @ http://www.fuzzysoftware.com/ Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources... Tag: Optimizing LIKE in SQL statements Tag: 119968

What can be done on the SQLServer end of things to optimize the speed of Fill
methods that use LIKE within the SQL statement.

--
Michael Hockstein

Top

Re: Optimizing LIKE in SQL statements by William

William
Sat Oct 29 14:15:27 CDT 2005

Check out the Query Analyzer and see the query plan for the LIKE variations
you're executing. For example, if you code
".... WHERE x LIKE 'y%' "
the QO might find that an index can quickly locate the row. On the other
hand if you code:
".... WHERE x LIKE '%y' "
the QO must do a rowscan--the indexes can't help.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
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.
__________________________________

"michael" <howlinghound@nospam.nospam> wrote in message
news:8C3CBA5B-CB10-453E-9B94-01D783AA6D3B@microsoft.com...
> What can be done on the SQLServer end of things to optimize the speed of
> Fill
> methods that use LIKE within the SQL statement.
>
>
> --
> Michael Hockstein

Top