Database

Create a custom search string

26-Apr-08 3:15

select * from myTable where short='CPU' and FullForm='central proccesing unit'

instad CPU value and central proccesing unit value set your textbox values

I Love T-SQL

Re: oledb query

ChinaTJBoy1-May-08 3:39

ChinaTJBoy

1-May-08 3:39

hi
What do you are development tools?
C#?

Re: oledb query

Member 30299284-May-08 19:55

Member 3029928

4-May-08 19:55

hi zhq,
yes im using visual c# 2005.
actualy im creating computer dictionary.
actulay i have windows form with comboBox(simple style) and other one is textBox(multiline style).
I have dicdat.mdb database there are two fields(word and meaning field).
initialy when application run all word field will be display and when user type some word at combiBox and hit enter it sould be display meaning at textbox.
please if you have some source or clue please help me out.
............
other ine problem is some time its display at textbox but there is < div > code is appear just like : < div > random access memory.< div >
what is ther problem.

sincerly
bir

Paul McGann25-Apr-08 7:42

Paul McGann

25-Apr-08 7:42

Hello,

I'm all new to this programming but i'm gently getting the grasp of things, but sometimes feel i'm stepping ahead of myself. Just a quick question, i'm trying to create a search string based on certain choices that the user makes. The user is able to select all the choices or nothing at all or parts of the choices. My problem is I think I'm goin about it the wrong way below is the string that i have created that creates an sql search string, based upon the users choices.

Dim search_str As String = "SELECT * FROM Properties WHERE"

If ddl_type.SelectedValue <> "0" Then
search_str += "Property_Type =" + ddl_type.SelectedItem.Text
End If

If ddl_location.SelectedValue <> "0" Then
search_str += "AND Property_Location =" + ddl_location.SelectedItem.Text
End If

If ddl_bedrooms.SelectedValue <> "0" Then
search_str += "AND Property_Bedrooms =" + ddl_bedrooms.SelectedItem.Text
End If

If ddl_max_price.SelectedValue <> "0" Then
search_str += "AND Property_Price <=" + ddl_max_price.SelectedItem.Text
End If

If ddl_min_price.SelectedValue <> "0" Then
search_str += "AND Property_Price >=" + ddl_min_price.SelectedItem.Text
End If

For i = 0 To cbl_features.Items.Count - 1
If cbl_features.SelectedItem.Selected = True Then
search_str += "AND Property_Features Like" + cbl_features.SelectedItem.Text
End If
Next

could someone have a look at this and check to see if this is the best method for creating this search, or possibly suggest an alternative.

Regards
Paul Mc Gann

Re: Create a custom search string

Mark J. Miller25-Apr-08 9:06

Mark J. Miller

25-Apr-08 9:06

Your approach is valid for the situation, but you are leaving yourself wide open to be hacked. Try this instead:

<br />
Dim search_str As String = "SELECT * FROM Properties WHERE"<br />
Dim parameters As ArrayList = new ArrayList()<br />
<br />
If ddl_type.SelectedValue <> "0" Then<br />
  SqlParameter p = new SqlParameter("@Property_Type", SqlDbType.whatever)<br />
  p.Value = Server.HtmlEncode(ddl_type.SelectedItem.Text)<br />
  search_str += "Property_Type = @Property_Type"<br />
  parameters.Add(p)<br />
End If<br />
<br />
If ddl_location.SelectedValue <> "0" Then<br />
  SqlParameter p = new SqlParameter("@Property_Location", SqlDbType.whatever)<br />
  p.Value = Server.HtmlEncode(ddl_location.SelectedItem.Text)<br />
  search_str += "AND Property_Location = @Property_Location"<br />
End If<br />
<br />
' and so on and so forth....<br />
<br />
SqlCommand cmd = new SqlCommand(search_str)<br />
For i = 0 To parameters.Count - 1<br />
  cmd.Parameters.Add(CType(parameters(i), SqlParameter))<br />
Next<br />
<br />
' execute your query and return the result ....<br />

If you want more information on why do a search for "Sql Injection" and "Cross site scripting" or "XSS". There are also performance reasons for using parameters. Here are some codeproject articles on these topics:

Sql Injection[^]

Cross site scripting (aka XSS)[^]

Ad hoc query performance[^]

Mark's blog: developMENTALmadness.blogspot.com

Funniest variable name:
lLongDong - spotted in legacy code, was used to determine how long a beep should be. - Dave Bacher

Re: Create a custom search string

Paul McGann25-Apr-08 9:45

Paul McGann

25-Apr-08 9:45

thanks mark much appreciated, the site is only to run on my localhost but it's worth having a look at the articles for future projects.

Re: Create a custom search string

ChandraRam27-Apr-08 23:24

ChandraRam

27-Apr-08 23:24

If this is the route you have chosen to go...

Mc--Gann wrote:
If ddl_type.SelectedValue <> "0" Then
search_str += "Property_Type =" + ddl_type.SelectedItem.Text
End If

There's the possibility that ddl_type.SelectedValue will be 0, in which case, your sql statement will have a syntax error.

For example:

Mc--Gann wrote:
Dim search_str As String = "SELECT * FROM Properties WHERE"

If ddl_type.SelectedValue <> "0" Then
search_str += "Property_Type =" + ddl_type.SelectedItem.Text
End If

If ddl_location.SelectedValue <> "0" Then
search_str += "AND Property_Location =" + ddl_location.SelectedItem.Text
End If

might result in
search_str = "SELECT * FROM Properties WHERE AND Property_Location =" + ddl_location.SelectedItem.Text

Also, check that you have a space prefixed in each of the concatenations...

If ddl_location.SelectedValue <> "0" Then<br />
search_str += " AND Property_Location =" + ddl_location.SelectedItem.Text<br />
End If

HTH

is it possible to replicate mysql data on mssql server [modified]

Talsaniya Jayesh25-Apr-08 5:27

Talsaniya Jayesh

25-Apr-08 5:27

hi

please help me out to replicate mysql data to mssql or any alternate for this if any.

Thank you.
Jes

modified on Friday, April 25, 2008 11:46 AM

Application roles

aecordoba25-Apr-08 4:15

aecordoba

25-Apr-08 4:15

Hi there!
I'm coding a Windows Application on VS 2005 with access to several databases.
For now, I'm using "Windows Authentication" in order to connect to the databases. But in deploy time, I'm thinking that will connect the application with databases in remote SQL Server Authentication way.
I read a little about MS SQL Server security, and I think when I deploy the application, I should use an "Application Role".

Can somebody tell me where can I read about Application Roles, and how have I to use it?

--
Adrián Córdoba

total Impression for same id and name

r aa j24-Apr-08 23:04

r aa j

24-Apr-08 23:04

hi here I am using SQL SErver 2005 with following query.....

select distinct dbo.AE_Site.siteId as siteId,dbo.AE_Site.siteName as
siteName,dbo.AE_Dimension.Impression as Impression from
AE_Dimension inner join AE_Channel on dbo.AE_Dimension.channelId=dbo.AE_Channel.channelId
inner join AE_Site on dbo.AE_Channel.siteId=dbo.AE_Site.siteId where dbo.AE_Dimension.cpm='0.5$'

output is
siteId siteName Impression
301 msn india 10
301 msn india 15
336 Yahoo India 45645

but I want output like

siteId siteName Impression
301 msn india 25
336 Yahoo India 45645

anybody plz reply me ASAP....
Thanks

Rajendran.AL

Re: total Impression for same id and name

Blue_Boy25-Apr-08 0:05

Re: total Impression for same id and name

25-Apr-08 0:05

select distinct dbo.AE_Site.siteId as siteId,dbo.AE_Site.siteName as <br />
siteName,(select sum(dbo.AE_Dimension.Impression) from dbo.AE_Dimension where dbo.AE_Dimension.chanelID in (select dbo.AE_Channel.channelId from AE_Channel)) where <br />
dbo.AE_Channel.siteId in (select dbo.AE_Channel.siteID from AE_Channel) and dbo.AE_Dimension.cpm='0.5$'

hope it helps to you...

I Love T-SQL

andyharman25-Apr-08 0:12

andyharman

25-Apr-08 0:12

Try:

select S.siteId, S.siteName, sum(D.Impression) as Impression
  from dbo.AE_Dimension as D
  inner join dbo.AE_Channel as C
    on D.channelId = C.channelId 
  inner join dbo.AE_Site as S
    on C.siteId = S.siteId
  where D.cpm = '0.5$'
  group by S.siteId, S.siteName

Note that using table-aliases (e.g. "D" instead of "dbo.AE_Dimension") makes it more readable.

Re: total Impression for same id and name

Rajkamal_dfine25-Apr-08 20:52

Rajkamal_dfine

25-Apr-08 20:52

Use group by siteId ...

N.Rajakumar B.E.,

database problem

snouto24-Apr-08 22:24

24-Apr-08 22:24

hello everyone , iam sorry for being far from this forum from along time but it is out of my hands . ok !

now there is a problem or a difficulty for me to completely design this database , using this case study

"we have 50 materials available for students to study.
each term , every student has the ability to sign only 3 materials only of his choice from the above 50 materials , suppose that this student failed in one of them so the next term will only sign two with the material he failed in so the total is also 3 materials in each term , and there are some materials if he failed in them render him from taking some other materials which are related to the materials he failed in , explain further .

suppose that student failed in material B and material G is built in knowledge upon material B thus he can't choose material G except if he succeeded in B first .

so i am suppose to design this database for student result examination i don't know really how the database could be designed ???

any help please .

Human knowledge belongs to the world.

Ashfield24-Apr-08 22:27

Ashfield

24-Apr-08 22:27

You already posted this. It must be obvious nobody is going to do your homework for you. Have a try, and post any REAL problems and you will be helped.

Bob
Ashfield Consultants Ltd

snouto24-Apr-08 22:29

24-Apr-08 22:29

can you help me

Human knowledge belongs to the world.

Vasudevan Deepak Kumar24-Apr-08 22:57

Vasudevan Deepak Kumar

24-Apr-08 22:57

You were advised to give a try and revert back in case of hurdles that you encounter in the process. You may not use a discussion forum as a substitute for your self-initiated homework. That is not going to work.

Vasudevan Deepak Kumar
Personal Homepage
Tech Gossips

A pessimist sees only the dark side of the clouds, and mopes; a philosopher sees both sides, and shrugs; an optimist doesn't see the clouds at all - he's walking on them. --Leonard Louis Levinson

Blue_Boy24-Apr-08 22:39

24-Apr-08 22:39

pay me and I will desing database for you Poke tongue | ;-P

I Love T-SQL

snouto24-Apr-08 22:42

24-Apr-08 22:42

you mean design not desing . thanks

Human knowledge belongs to the world.

Blue_Boy24-Apr-08 22:45

24-Apr-08 22:45

Yes you got it.

I Love T-SQL

snouto24-Apr-08 22:48