Re: remote - Database Discussion Boards

Varghese Paul M7-Jan-09 2:10

7-Jan-09 2:10

Hi friends,

I am working with SQLServer 2005 database (Collation set as Chinese) using odbc api SQLExecDirect and SQLExecute.
Some of my SELECT queries containing some chinese characters works fine with SQLExecute and SQLExecDirectW but NOT with SQLExecDirect. My gut feeling is SQLExecute is converting the query into unicode before it executes. Is this assumption right ?

thank you

cheers
Varghese Paul

Re: Chinese characters in SELECT query

Wendelius7-Jan-09 2:40

Wendelius

7-Jan-09 2:40

This would depend on the driver and it's implementation. One problem you may encounter is: http://support.microsoft.com/kb/271198[^].

However, if SQLExecDirectW is working, why not use it instead of SQLExecDirect.

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

retrieving data from several tables having same column name

nripendra346-Jan-09 20:58

nripendra34

6-Jan-09 20:58

Hi to all,

I have several tables in which all the column names are same.
for example I have 4 tables.
1.glassware
2.barware
3.utensils
In all the three tables I have same columns.Suppose one of the column is "product_id".
Now in my UI I am having a search control based on product_id.
How I will search in all the three tables.

I dont know much abt database.Please help me out.

I used:"select product_id, sh_desc,image,price from Glassware,Barware,Utensils where product_id='"+textbox1.text+"'"

Thanks in advance.

Re: retrieving data from several tables having same column name

SeMartens6-Jan-09 21:11

SeMartens

6-Jan-09 21:11

Hi,

you could use a union to get the rows matching in all three tables:

<br />
SELECT Glassware.product_id, Glassware.sh_desc, glassware.image, glassware.price from glassware where glassware.product_id = '" + textbox1.text + "'<br />
<br />
UNION<br />
<br />
SELECT Barware.product_id, Barware.sh_desc, Barware.image, Barware.price from Barware where  barware.product_id = '" + textbox1.text + "'<br />
<br />
UNION<br />
<br />
SELECT Utensils .product_id, Utensils .sh_desc, Utensils .image, Utensils .price from Utensils where  Utensils .product_id = '" + textbox1.text + "'<br />
<br />

By the way, please don't attach the value textbox1.text to the sql query directly. Use parameters to do this, because it could be a possible security hole (sql injection).

If all three tables have the same columns why don't you use one table with an additional column, saying whether the row is a glassware, barware or utensils? Makes your queries much simpler.

Hope this helps.

Regards
Sebastian

It's not a bug, it's a feature!

Me in Softwareland.

Re: retrieving data from several tables having same column name

nripendra346-Jan-09 21:16

nripendra34

6-Jan-09 21:16

Hi,

Thanks a lot.It worked.
I will rather use one table.

cheers,
nirpendra

remote

Programmer.6-Jan-09 20:31

Programmer.

6-Jan-09 20:31

Is it possible connect to a remote sql server for test a program on the net or know?

Re: remote

Aman Bhullar6-Jan-09 20:32

Aman Bhullar

6-Jan-09 20:32

Programmer. wrote:
Is it possible connect to a remote sql server for test a program on the net or know?

Yes, it it possible until you have server IP, Username, Password and Database

Re: remote

Wendelius7-Jan-09 0:48

Wendelius

7-Jan-09 0:48

If there are firewalls between the client and the server (and most likely there is), you must have an open port for the conversation.

It may be that you have to have a secure connection to the firewall first and after that the port the sql server is listening is seen as open. After that, it's just a normal connection.

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

how do i set the password and username from a database remotely for MS sql?

neodeaths6-Jan-09 3:52

neodeaths

6-Jan-09 3:52

hi
i am currently using MS SQL and have a window form connecting thru a database remotely thru a static IP(though i am doing it locally now to try)

i would like to know how can i store my user password and username as my window form need to be able to log in and out

currently i connect thru to my database thru
SqlConnection
and open the connection however i am needed to create a user on the security of the ms sql database and map it to my database.

which means that my sql connection statment is currently having a userid and password

is it possible to set the ms sql database or the windowform to loginto the ms sql server and authenticate thru a table in the selected database

Re: how do i set the password and username from a database remotely for MS sql?

Wendelius6-Jan-09 4:06

Wendelius

6-Jan-09 4:06

neodeaths wrote:
i would like to know how can i store my user password and username as my window form need to be able to log in and out

If you don't ask the user for the password or username could you simply use integrated security (Windows authentication mode).

neodeaths wrote:
i am needed to create a user on the security of the ms sql database and map it to my database

This can be done with both Windows authentication mode and SQL Server authentication mode.

neodeaths wrote:
is it possible to set the ms sql database or the windowform to loginto the ms sql server and authenticate thru a table

No, to gain access to a table you need to logon first.

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

deference between two columns in two Sequenced rows

Ahmed El-Badry6-Jan-09 0:10

Ahmed El-Badry

6-Jan-09 0:10

table name trips

starttime endtime triptime Stoprate
14/08/2008 10:39:00 PM 14/08/2008 10:56:00 PM 16 3
14/08/2008 10:59:00 PM 14/08/2008 11:1:00 PM 2

i want the sql queries to create stoprte value

Ahmed El-Badry

Re: deference between two columns in two Sequenced rows

Wendelius6-Jan-09 0:20

Wendelius

6-Jan-09 0:20

Ahmed El-Badry wrote:
i want the sql queries to create stoprte value

Could you be more specific what you're trying to achieve? You want to calculate stoprate value with what kind of formula?

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

Re: deference between two columns in two Sequenced rows

Ahmed El-Badry6-Jan-09 0:27

Ahmed El-Badry

6-Jan-09 0:27

i want calculate stoprate value

table name trips

starttime endtime triptime Stoprate
14/08/2008 10:39:00 PM 14/08/2008 10:56:00 PM 16 3
14/08/2008 10:59:00 PM 14/08/2008 11:1:00 PM 2

stop value = 14/08/2008 10:56:00 PM - 14/08/2008 10:59:00 PM = 3
i want the sql queries to calculate stoprte value

Ahmed El-Badry

Re: deference between two columns in two Sequenced rows

Wendelius6-Jan-09 0:40

Wendelius

6-Jan-09 0:40

So the stoprate is endtime from row 1 - starttime from row 2 in minutes? If that's correct, you could use something like:

select datediff(minute,
                trips.endtime, 
                (select min(t2.starttime)
                 from   trips t2
                 where  t2.starttime > trips.endtime))
from trips...

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

Re: deference between two columns in two Sequenced rows

Ahmed El-Badry6-Jan-09 0:45

Ahmed El-Badry

6-Jan-09 0:45

thnx i will tray it Smile | :)

Ahmed El-Badry

Re: deference between two columns in two Sequenced rows

Wendelius6-Jan-09 3:11

Wendelius

6-Jan-09 3:11

You're welcome. Smile | :)

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

Unicode in SQL query

Varghese Paul M5-Jan-09 23:09

Varghese Paul M

5-Jan-09 23:09

Hi friends,

I am working with SQL Server 2005 database with Collation selected as Chinese_PRC_CI_AS

I have a table DMASTER in the database with a field called DNAME of type VARCHAR(254) which contains some Chinese characters.

When i run a query like
select * from dmaster where DNAME = N'翻译文字或网页翻译文字或网页';
from the SQL Server 2005 client, it works fine.

But if I try the same query programmatically using API "SQLExecDirect", it returns no records.

May I know the problem and how to solve it.

Thank you

cheers
Varghese Paul

Re: Unicode in SQL query

Wendelius5-Jan-09 23:32

Wendelius

5-Jan-09 23:32

Have you tried using SQLExecDirectW which is unicode version of the function. See: Unicode Function Arguments[^]

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

Re: Unicode in SQL query

Varghese Paul M6-Jan-09 0:46

Varghese Paul M

6-Jan-09 0:46

Thank you. It worked fine.

cheers
Varghese Paul

Re: Unicode in SQL query

Wendelius6-Jan-09 0:48

Wendelius

6-Jan-09 0:48

You're welcome.

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

How can i do this query?? [modified]

foryou5-Jan-09 22:39

foryou

5-Jan-09 22:39

HI!!
I have a table (sqlserver2005)

ID-------date1--------date2
1--------1/12/2008-----30/5/2009
2--------1/12/2009-----30/6/2010
...
I typed a date in a textbox.
I need to select ID as that date will be between date1 and date2.
How can i do that thanks.
exemple whene date=30/2/2009 id is 1.
Thanks!

modified on Tuesday, January 6, 2009 4:57 AM

Re: How can i do this query??

Wendelius5-Jan-09 23:03

Wendelius

5-Jan-09 23:03

You can use between operator like:

SELECT ...
FROM ...
WHERE DateFromTextBox BETWEEN date1 AND date2

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

Re: How can i do this query??

foryou7-Jan-09 1:10

foryou

7-Jan-09 1:10

HI!!
I did this :

String rqtr2 = "select ID as [p1] from Table where ('" + TextBox2.Text + "' between date1 and date2)";

and

TextBox2.Text = Convert.ToString(DateTime.Now) ;

but it is not right .
Help me please!
thanks

Re: How can i do this query?? [modified]

Wendelius7-Jan-09 1:21

Wendelius

7-Jan-09 1:21

foryou wrote:
but it is not right

What do you mean by that? Could you post an example row and what you entered in the textbox. That would clarify your problem. The forst guess would be that when you convert the date to a string, it isn't in a format that the db understands.

Little off-topic: it's a bad habit to use literals in the sql statement. Use parameters instead.

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

modified on Wednesday, January 7, 2009 7:27 AM

Re: How can i do this query??

foryou7-Jan-09 1:41

foryou

7-Jan-09 1:41

Hi!
Merci Mika Wendelius
sorry I dont speak English well.
what I am in the textbox is the system date (detetime)
example :
07/01/2009 11:59:16
Little off-topic: it's a bad habit to use literals in the sql statement. Use parameters instead.

I do not know how to use parameters in this case.
Thanks!!

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.