Database

Wendelius19-Dec-08 23:57

19-Dec-08 23:57

Fulltext indexing would be the correct way if you search for words.

Krishnraj wrote:
it has some limitation for some words which it can not able to search as per my knowledge

What kind of limitations? Full text search should be able to search for any exact words. If you want to search for a word in different forms, then it may have limitations.

The need to optimize rises from a bad design.My articles[^]

Krishnraj20-Dec-08 1:01

Krishnraj

20-Dec-08 1:01

Hi mika,

Thank for replying..

Few days ago i read somewhere in site that fulltext indexing can not search all words. that is why i wrote like this.

so to search from fulltext indexed column i have to use Contain or something that kind of word. M i right?
Actually i have no idea how to use it? so give some suggesion how to use it.

Thank you again... Smile | :)

Krishnraj

Wendelius20-Dec-08 1:45

20-Dec-08 1:45

Krishnraj wrote:
so to search from fulltext indexed column i have to use Contain or something that kind of word. M i right?

Yes, see CONTAINS (Transact-SQL)[^] and FREETEXT (Transact-SQL)[^]

Krishnraj wrote:
Actually i have no idea how to use it? so give some suggesion how to use it.

On those documents there are several good sample queries. Have a look at them.

Also remember that Full Text Indexing service must be running in order to use full text search.

The need to optimize rises from a bad design.My articles[^]

Parameter @Model has no default value

Krishnraj20-Dec-08 2:10

Krishnraj

20-Dec-08 2:10

Thank you very much for yr suggesion Rose | [Rose]

and yes i also take care abt indexing service

Krishnraj

dec8219-Dec-08 12:27

dec82

19-Dec-08 12:27

I am using OleDb database and has this error when ExecuteNonQuery().
what can i do to solve it?
Thanks

Blue_Boy19-Dec-08 18:27

19-Dec-08 18:27

Check this link.[^]

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

Rob Graham20-Dec-08 3:44

20-Dec-08 3:44

It would be helpful if you provided a snippet of the actual code so that we could see what you are doing, but my first guess would be that you are executing a query that has a parameter named @Model, but you have not provided a value for that parameter.

dec8220-Dec-08 5:45

dec82

20-Dec-08 5:45

string cmd;
string field;
string parameter;
field = "Site,Model";
parameter = "@Site,@Model";
OleDbConnection aConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\Database.mdb");
cmd = "INSERT INTO Run(" + field + ") values (" + parameter + ")";
OleDbCommand sCommand = new OleDbCommand(cmd);
sCommand.Connection = aConnection;
aConnection.Open();
sCommand.Parameters.Add("@Site", OleDbType.VarChar, 20);
sCommand.Parameters.Add("@Model", OleDbType.VarChar, 20);
sCommand.Parameters["@Site"].Value = CommunicationSettings.site;
sCommand.Parameters["@Model"].Value = CommunicationSettings.Select.ToString();
sCommand.ExecuteNonQuery();
aConnection.Close();

Thanks...

Rob Graham20-Dec-08 7:29

20-Dec-08 7:29

I don't believe OledbCommand supports named parameters in a command whose CommandType is CommandText (the default). It expects you to use parameter markers (?) and mostly ignores the name.

If you change

cmd = "INSERT INTO Run(" + field + ") values (" + parameter + ")"

cmd = "INSERT INTO Run(" + field + ") values ( ?,?)"

It should work correctly. Note that because parameter marker tokens are used, theorder in which the parameters are added to the collection becomes critical (must match the order they are actually expected in the statement).

Wendelius20-Dec-08 10:51

20-Dec-08 10:51

Rob Graham wrote:
I don't believe OledbCommand supports named parameters

A very good answer (got my 5 Poke tongue | ;-P

). From the (overlooked) documentation:
"The OLE DB.NET Framework Data Provider uses positional parameters that are marked with a question mark (?) instead of named parameters"

The need to optimize rises from a bad design.My articles[^]

Rob Graham20-Dec-08 13:19

Which type should i use for saving a string like this: 000000-0000?

20-Dec-08 13:19

Thanks. You'd think Microsoft could have come up with a more informative error message though...

Jan Sommer19-Dec-08 8:43

Jan Sommer

19-Dec-08 8:43

Our customers are all id'ed by their social securitynumber (CPR) which is in the format 000000-0000 here in Denmark. How would you save this in the DB? The easiest way is ofcourse to save it as a string, but i'm also considering an integer-field.

Does anyone know of the performance in searching when dealing with strings vs. integer fields (in a db), and would it be worth the effort to write code that converts between userinputted CPR's to integer? We are using MSSQL, and i'm programming in C# if it matters.

Re: Which type should i use for saving a string like this: 000000-0000?

Blue_Boy19-Dec-08 8:57

Re: Which type should i use for saving a string like this: 000000-0000?

19-Dec-08 8:57

Why you don't use varchar column datatype in you DB? Why field in DB have to be integer datatype? I suggest to use varchar column in DB.

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

Jan Sommer19-Dec-08 9:01

Jan Sommer

19-Dec-08 9:01

That is why i'm asking the question - i don't know. Won't the performance fall enough to take the cpr-fields datatype into consideration?

Re: Which type should i use for saving a string like this: 000000-0000?

Blue_Boy19-Dec-08 9:07

Re: Which type should i use for saving a string like this: 000000-0000?

19-Dec-08 9:07

As you know if you store value (example) 000012 as integer in DB then you will get only 12 value and zeros before 12 value will be eliminated automatically.So in your case I would use varchar datatype in DB.

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

Rob Graham20-Dec-08 3:37

Re: Which type should i use for saving a string like this: 000000-0000?

20-Dec-08 3:37

That's an easily corrected formatting issue, and not a reason to prefer integer over varchar.

Blue_Boy20-Dec-08 6:10

Re: Which type should i use for saving a string like this: 000000-0000?

20-Dec-08 6:10

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

Wendelius19-Dec-08 9:03

Re: Which type should i use for saving a string like this: 000000-0000?

19-Dec-08 9:03

Store it as a string. We have a very similar format for SSN and it's proven to be best in string.

If you want to search the whole SSN (using equality operator) or by describing the start of it (using LIKE '...%'), it will be efficient with string as long as you have an index on it.

If you want to search efficiently using subparts of the SSN (for example Year BETWEEN XX and YY), you can break it down to other calculated fields such as year, month etc and index them. Create a trigger which fills the calculated fields and never let the user to modify them.

Also if you can, enforce a uniqueness on the index on SSN, the search will be faster. Typically SSN should be unique but I don't know if you have exceptions and even if you do, do you encounter them in your application.

Also use triggers to verify the correct format of SSN so you won't have accidental mistakes/duplicates.

Hope this helps,

Mika

The need to optimize rises from a bad design.My articles[^]

Jan Sommer19-Dec-08 9:11

Jan Sommer

19-Dec-08 9:11

Mika > Thanks a lot! That answer was very helpful.

Blue_boy > You could format the integers when you show it to the user and easily insert the missing 0. But i'm convinced - i shall use varchar for SSN Smile | :)

Re: Which type should i use for saving a string like this: 000000-0000?

Wendelius19-Dec-08 9:13

Re: Which type should i use for saving a string like this: 000000-0000?

19-Dec-08 9:13

Jan Sommer wrote:
Thanks a lot! That answer was very helpful

No problem Smile | :)

The need to optimize rises from a bad design.My articles[^]

Blue_Boy19-Dec-08 9:20

Re: Which type should i use for saving a string like this: 000000-0000?

19-Dec-08 9:20

Jan Sommer wrote:
You could format the integers when you show it to the user and easily insert the missing 0

Yes,but your question was how to store value in DB like string or integer,by the way the SSN format 000000-0000 you can not store in DB as integer so you must use varchar.

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

Rob Graham20-Dec-08 3:39

Re: Which type should i use for saving a string like this: 000000-0000?

20-Dec-08 3:39

You have no idea what you are talking about. You are confusing data storage representation and UI visualization. They are not related.

Blue_Boy20-Dec-08 6:11