I am developing one web site, in that I can upload files to sql server, it will save the uploaded file in binary type and we can download the files from there. Upto this everything is working perfectly. But now my problem is - i have to implement the wildcard character search in this site. The file name and extension will save on the database in different columns, and one more thing is file name is unique. I confused to implement wildcard search in this site, where i can implement this type of search in database or in my application with c#? So please help me to do this. I dont know that this is the right place to post this question or not. If it is not right place, please guide me to where I can post this. If it is right place, please help me to implement wild card search.
In the below section of code I'm connecing to a SQL database and when I use the "=" in the search it works ok, but when I try to add the ability to use wildcards by changin "=" to "LIKE" its not working
Working code:
Function GetName(ByVal first_Name As String) As System.Data.DataSet Dim connectionString As String = "server='(local)'; trusted_connection=true; database='FamilyInfo'" Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "SELECT Names.* FROM Names WHERE (Names.First_Name = @First_Name)" Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand dbCommand.CommandText = queryString dbCommand.Connection = dbConnection
Dim dbParam_first_Name As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_first_Name.ParameterName = "@First_Name" dbParam_first_Name.Value = first_Name dbParam_first_Name.DbType = System.Data.DbType.StringFixedLength dbCommand.Parameters.Add(dbParam_first_Name)
Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.SqlClient.SqlDataAdapter dataAdapter.SelectCommand = dbCommand Dim dataSet As System.Data.DataSet = New System.Data.DataSet dataAdapter.Fill(dataSet)
Return dataSet End Function
Code that I would like to work
Function GetName(ByVal first_Name As String) As System.Data.DataSet Dim connectionString As String = "server='(local)'; trusted_connection=true; database='FamilyInfo'" Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "SELECT Names.* FROM Names WHERE (Names.First_Name LIKE '%' + @First_Name '+')" Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand dbCommand.CommandText = queryString dbCommand.Connection = dbConnection
Dim dbParam_first_Name As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_first_Name.ParameterName = "@First_Name" dbParam_first_Name.Value = first_Name dbParam_first_Name.DbType = System.Data.DbType.StringFixedLength dbCommand.Parameters.Add(dbParam_first_Name)
Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.SqlClient.SqlDataAdapter dataAdapter.SelectCommand = dbCommand Dim dataSet As System.Data.DataSet = New System.Data.DataSet dataAdapter.Fill(dataSet)
Hi All, I'm trying to use a very simple SQL String that check if a specific string exist in the DB. When I do the SQL and use a regular Connection I get the result. When I use a data set and on the table adapter I need to use the "LIKE %" method I get an error.
What should I write on the table adapter in order to make this search?
ItemID is a type string.
This is my SQL: SELECT itemID, itemName FROM Items WHERE (itemID LIKE @ItemIDTemp)
This sample give no result or
SELECT itemID, itemName FROM Items WHERE (itemID LIKE '%' + @ItemIDTemp + '%')
This sample return an error message "Data conversion failed. [OLEDB status value (if known)=2]"
I'm trying to put together a script that would alter a table and add NOT FOR REPLICATION to the identity column on several hundred tables. Since I am unable to write a complex script to automate the entire process, I've decided to do the update one table at a time. Here is a script that I'm working on. I'm getting an error message when I try to run the script. Can someone please show me how to properly use variable with a wild card in a string ? or how to properly add this variables in this script?
DECLARE @Var1 AS CHAR(50), @tbn AS CHAR(50) SELECT @tbn = 'tablename' SELECT @Var1 = Name FROM SysIndexes WHERE Name LIKE 'pk%' AND Name LIKE '%' + @tbn + '%' EXECUTE('ALTER TABLE [@tbn] DROP CONSTRAINT ' + @Var1 ALTER TABLE + @tbn + DROP COLUMN mkey ALTER TABLE + @tbn + ADD viewkey INT IDENTITY (1,1) NOT FOR REPLICATION NOT NULL ALTER TABLE + @tbn + ADD CONSTRAINT PK_+ @tbn PRIMARY KEY (mkey) WITH FILLFACTOR=90 ON [PRIMARY]
I am using Conditinal split in my package. I need to remove certain rows which are matching my criteria. The criteria requires using wild card characters like, first_name = '%john%'.
I want to retrieve data from SQL containing non English character but fail, can anyone shed me some light?
What I use currently:
Dim strSQL As String strSQL = "SELECT ArticleID, " strSQL &= "ISNULL(Body, '') AS Body, " strSQL &= "ISNULL(Subject, '') AS Subject " strSQL &= "FROM Articles " strSQL &= "WHERE (Subject LIKE N'%' + @Keyword + '%' OR [Body] LIKE N'%' + @Keyword + '%') " Dim con As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString")) Dim cmd As New SqlDataAdapter(strSQL, con) cmd.SelectCommand.Parameters.Add("@Keyword", SqlDbType.NVarChar).Value = keyword ...
I'm not so sure where should I place the letter "N", I use :
SELECT ArticleID, ISNULL(Body, '') AS Body, ISNULL(Subject, '') AS Subject, FROM Articles WHERE (Subject LIKE N'%SomeNonEnglishString%' OR [Body] LIKE N'%SomeNonEnglishString%')
in Query Analzyer, it works! But it failed in my program... oh my god...
I'm new on sqlserver, but now i have a question, how could i find an exact string like "rouse" inside of a larger string that is in one table?, i'm studyng on one university in Costa Rica, but after a few lessons nobody had told my how to do that. For example if i need to find the string 'rouse' that could be inside of 25 rows or more i know that if i use pathindex sql returns me in what row is but how put that instruction in a select or in a stored procedure. If someone could help me i'll be very thankfull. Sorry for my english i know it suck but here we don't have a lot of practice.
I just implemented full text search on a table and it doesn't work like I expected. I am using a stored procedure to search a company name column.
CREATE PROCEDURE swsp_GetCompanyRecord @companyName varchar(120) as begin select recID, companyName from TABLE where contains (companyName, @companyName) end
I use the stored procedure as follows:
swsp_GetCompanyRecord '"Reynolds Aluminum"' (Quotation marks inside single quote)
If I search for a company (e.g., Reynolds Aluminum), I get the following results:
Search Returns Reynolds Reynolds Aluminum Reynolds A Reynolds Aluminum Reynolds Al nothing Reynold nothing Aluminum Reynolds Aluminum
I guess I expected this to work similar to 'LIKE' in that it would return the full company name if I gave it any portion of the name. Is there something wrong with the way I've implemented this?
I have setup full text search on a table. Everything works fine. Now I want to find documents that contain the next string: 'T-150/04'
The query will give zero results. Is there a way to find documents that contain these kind of strings? (slahses and minus)
I use the following query:
SELECT * FROM CONTAINSTABLE (jurisprudentie, text, '"T-150/04"' )
If I do this query:
SELECT * FROM CONTAINSTABLE (jurisprudentie, text, '"judge"' )
It will work fine. I know it has to do something with the interpunction things (slahs and minus), but I don't know what query may solve my problem.
Also a select * from containstable(jurisprudentie, text, '"T_150_04"' ) doesn't give me the proper result.
The minus sign and the slash are disturbing things. If i search for "150" or for "04" I will find the record, but I would like to speficically search for the exact string "T-150/04". Because these are references to case law issued by the court.
Does anyone know how to how to performance case-insensitive search onXML data type in SQLServer 2005? Or I have to convert all the xml datato lower case before I store it?Thanks in advance.John
I am new to SQL Server, DTS etc... But here is the question I have:
I have some files in a folder which have a the yr, month, date appended at the end, for e.g. testfile2005_11_28.log In a folder I have to look for the log files which are named testfileYYYY_MM_DD.log.
Dim item For Each item In logFolder.Files If (UCASE(item.Name) like ("TESTFILE" +"[0-9][0-9][0-9][0- 9]"+"_"+"[0-12][0-12]"+"_"+"[0-3][0-9]" + ".log") )Then msgbox item.Name End For
I am getting compilation error: "Sub or Function not found" in the If statement. Can anyone please help me?
Hi, I am using Visual Studio 2005. I am trying to create a ASP.net website and use a web form to get the results of an enquiry. It is almost the same as the presentation video on this website called "How to Create Data Driven Website", but I have added a new twist to it. I want to use the "LIKE" instead of "=" in my SQL statement and I want to use wild cards in the parameter value that I enter. <form id="form1" runat="server"> <div> <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox> <% dim str as string = "%" & textbox1.text & "%" </%> <asp:TextBox ID="TextBox2" runat="server" Visible="False" Text=str></asp:TextBox> <asp:Button ID="Button1" runat="server" Text="Submit" Width="87px" BackColor="Khaki" ForeColor="Navy" PostBackUrl="~/Default.aspx" /> and the ControlID of the Datagrid will take in the value of "TextBox2" instead of "TextBox1" as so: <SelectParameters> <asp:ControlParameter ControlID="TextBox2" DefaultValue="SO06000001" Name="SO" PropertyName="Text" Type="String" /> However the syntax above does not work. May I ask what is the correct syntax? Thanks. Peter
I am very new to sql and would like some help with a simple select statement. I am trying to pull birthdates using the following code. I can get the query to run but some of the dates are not between the parameters. What am i doing wrong?
SELECT Employees.EmployeeStatusID, People.FirstName, People.LastName, People.BirthDate FROM Employees FULL OUTER JOIN People ON Employees.SystemAssignedPersonID = People.SystemAssignedPersonID WHERE (People.BirthDate BETWEEN '10 - 01 - 1900' AND '12 - 15 - 2007') ORDER BY People.BirthDate
Hello everyone, trying to use stored procedure for my datagrid. in there i have the parameter that i would like to combine with wild character to retrieve some data..Does not work. Please help to come up with right syntax... here is the code: SELECT First_Name, Last_Name, Address, City, Customer_ID, Company_Name, State, ZIP, Phone_Number_1, Phone_Number_2, Email FROM v2_Customers WHERE (City = @search_text) OR (First_Name = @search_text) OR (Last_Name LIKE '%'+@search_text) OR (Address = @search_text)
Is there any tool available that can encrypt Credit card numbers or other fields stored in the database? For example, when a customer enters his credit card number or his password on the site, is there a way by which the Front end can encrpt the field before storing it in the database and decrypt it when it the customer requests it, through the application?
table Orders ord_id int ord_CudtomerID int, ord_CardNo char(16)
Example values in table CreditCardRange 1 , 5000 0000 2000 1234 , 5000 0030 3000 1356
Example values in table Orders 1, 1, 5000 0010 2000 2333
Does sql provide any magic finction to help in following select
select T1.* from Orders T1 join CreditCardRange T2 on T2.ccr_CudtomerID = t1.ord_CudtomerID and SQLMagicFunction(T1.ord_CardNo) between SQLMagicFunction(T2.ccr_CardRangeFrom ) and SQLMagicFunction(T2.ccr_CardRangeTo )
My team is currently building a system for the Windows CE 5.0 platform that uses a SQL CE database to synchronize with a SQL Server 2005 database. During this synchronization process, the application needs to pull down a good bit of data and it reaches its cap for memory. A possible solution for this is to use an SD card for storing the database. However, when the Synchronization process is run, the following SQL Exception Occurs:
HResult = -2147467259 Message = "SQL Mobile encountered problems when creating the database." NativeError = 28558 Source = "Microsoft SQL Server 2005 Mobile Edition" StackTrace = at System.Data.SqlServerCe.NativeMethods.CheckHRESULT() at System.Data.SqlServerCe.SqlCeReplication.AddSubscription() Our Trace at System.Windows.Forms.Control.OnClick() at System.Windows.Forms.Control.WnProc() at System.Windows.Forms.Control._InternalWnProc() at Microsoft.AGL.Forms.EVL.EnterMainLoop() at System.Windows.Forms.Application.Run() Main
The connection string we're using for the Mobile Database is "data source=Storage CardDatabase.sdf"
Has anyone ever had any problems when trying to perform Merge/Replication on expandable Flash Memory and if so, how did you solve them?
SQL mobile apps like - .Net Frame work, SQLCE mobile etc - can they be install on the Storage Card? I am running out of space on my Verizon 6700 and don't have enought memory to install on the device. I cleaned up as much as I could still not enough space on the device.
I am new to SQL developement ( am .Net developer). I need to implement a stored proc in sql 2005 on CC expiration task.
I have CC exiration date. By using expiration date (like '01/08'),
Conditions are:
1. If CC expiring within 31 days. Stored proc will write the Customer data in EmailNotification table with message type =1. (Customer will not get second notification until 14 days before expiring)
2. If CC expiring within 14 days. Stored proc will write the customer data in EmailNotification table with message type =2. (Customer will not get second notification until 11 days before expiring)
3. If CC expiring within 11 days. Stored proc will write the customer data in EmailNotification table with message type =3. (Customer will not get second notification until 2 days before expiring)
4. If CC expiring within 2 days. Stored proc will write the customer data in EmailNotification table with message type and update orderStatusID to On Hold CC Expired in order table.
My Tables are
1.
TABLE [dbo].[Order]( [OrderID] [int] IDENTITY(1,1) NOT NULL, [CustomerID] [int] NOT NULL, [CustomerCode] [nvarchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [DateCreated] [datetime] NOT NULL, [DateOrdered] [datetime] NOT NULL, [OrderTypeID] [int] NULL, [OrderStatusID] [int] NULL, [LastProcessDate] [datetime] NULL,
2. CREATE TABLE [dbo].[Payment]( [PaymentID] [int] IDENTITY(1,1) NOT NULL, [OrderID] [int] NOT NULL, [OrderPaymentType] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [PaymentType] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [CreditCardNumber] [varbinary](max) NULL, [Last4DigitsOfCreditCardNumber] [nvarchar](4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [ExpirationDate] [varbinary](max) NULL, [CVV2] [varbinary](max) NULL, [Amount] [numeric](18, 2) NOT NULL
3. CREATE TABLE [dbo].[Customer]( [CustomerID] [int] IDENTITY(1,1) NOT NULL, [CustomerCode] AS ([dbo].[udf_FormatCode]('RS',[CustomerID])), [Email] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [FirstName] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [MiddleName] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [LastName] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [DateCreated] [datetime] NOT NULL, [DateModified] [datetime] NOT NULL, [MembershipTypeID] [int] NULL
Please atleast basic structure of stored proc code. It will greatful to me.
I develop a app on PPC 4.20 using SQLServer 3.1. I developed a o/r mapper whitch auto-creates the Database and Tables using attribute-decorated objects. That all works fine - as long as my Database is in \MyDocuments . Trying to store the Database in \Storage Card\test.sdf, whitch is absolut neccessary because it will become to big for storing onto device, I get error 25113, "A not supportes operation to host was called" (Translated from German)
When I trie to copy a Database to the SD-Card, inserts are not commited.
Is it not supported to store Databasefiles on a Flashcard?
My development-environment is .net 2.0 sp2, device-emulator v2, Windows XP, vs2005. In the emulator, i have linked a windows-based directory as storagecard.
What am I doing wrong??
class SQLManager { private static SQLManager instance = null;
Hi friends, i got an issue on my website regarding theft of cc details...sql db contains credit card details table ... it has been hacked by someone...we have analysed that latest cards are misused.. i have tried different scenarios(sql injection) ... what are all the key things have to check and implement .can any one advise please..
Using shared folder in windows mobile 5 emulator, it is not possible to create a database (sqlserver mobile 2005) in the emulated storage card.
The error "sql mobile made an unsupported request to the host operating system. Minor Err 25133 is raised. Reading or writing file from/to the emulated storage card occurs without error.
I have seen some different behaviors when I am writing SQL Server compact edition Database file into SD Memory card or any user removable storage media.
The following Steps for reproducing these behaviors.
1. Set your database location is in the SD Memory card. 2. Create a database and establish session. 3. Writing first record into the database is done 4. Remove your SD card manually from the system 5. Try to write a Second record into the database 6. You will get the error from this "SqlCeWriteRecordProps" API call - This is fine. 7. Put your SD card back into the system. 8. Writing Second record again into the database is done - This is fine.
So far the behaviors are good after that
9. Remove your SD card manually from the system again 10. Try to write a Third record into the database 11. You will suppose to get the error from this "SqlCeWriteRecordProps" API call but you won€™t get that error €“ I don€™t know why? 12. Then Try to write a Forth record into the database 13. You won't get any error from the API call "SqlCeWriteRecordProps" and return status is success. 14. Now put your SD card back into the system. 15. Then write Fifth record but this time its writing record Third, Fourth and Fifth into the database.
Note: I am clearing (means Free) my record structure everything after calling this function "SqlCeWriteRecordProps". So I don€™t know why its wring Third, fourth and fifth record into the database.
ok i have intel dual core i have a conflict only in playing a game black hawk down it gives me a run stop error. locks up and has to be restarted. microsoft gave me a fix but when i do the fix it causes me to get a system dump error on the game. i can update my web site do anything else let daughter play her games or do her school work and nothing happens. i was told i needed to set up the dual core so that my programs dont conflict i am a moron when it comes to computers is there a fix for this or i am i just going to have to go back to single core processor for now thanks
I have built an application for storing my flight times on my phone after each leg of a trip... I am using C# and Sql CE as the storage system for later export to a larger desktop version (in the works). My question is related to storing the sdf file on the storage card. Can it be done and what would the connection string look like since I have tried and got a error stating it can't find the sdf file in question. I have it setup right where if I were to do a fresh install it would ask for the install directory main memory or storage card. If I select storage card it installs just fine but when I run the app it does fail with the aforementioned error..
I have typed a query by combining multiple tables to show sum of TotalReceivedAmount by MRN. It also have payment mode which is paid by cash or credit card. Now the query shows the sum for MRN combining all the payment done by credit card and cash. How am I supposed to show the amount paid by credit card and cash separately?
Here is the query :
SELECT Distinct   dbo.CA_Payment.MRN, dbo.CA_Patient.FirstName, dbo.CA_PaymentModeMaster.Description AS PaymentMode, CASE (dbo.CA_Payment.PaymentModeID) WHEN 2 THEN dbo.CA_Payment.CardNumber END AS CreditCardNumber, CASE (CA_Registration.PatientTypeID) WHEN 1 THEN dbo.CA_PatientTypeMaster.Description WHEN 2 THEN dbo.CA_DebtorMaster.DebtorName END AS Debtor,Â