data difference? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

data difference?

Author

Topic

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-08-24 : 16:14:42

what is the difference between character data and unicode data? Like nchar or char?

Brenda

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-24 : 16:19:58

You use unicode data types when you need to store data such as Chinese. Unicode supports a wider range of characters.

Tara

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-08-24 : 16:20:19

My problem is this. I am using a datareader and it works fine, except when I type a dash in the case number. This is what I have as a case number: 153-654987

If I type somthing like this: 153 it will find a case starting with that just find, but if I typr 153-6 it says no matches found. Why does it do that? Do I have the wrong data type? Why does it not like the dash?

Brenda

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-24 : 16:22:05

You don't need unicode data types for that kind of data. Post your table structure and code.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2004-08-24 : 16:26:41

What's a datareader?

And don't tell me it's a database role.....

Brett

8-)

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-08-24 : 16:53:26

[code]Private Sub btnSearchCase_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearchCase.Click
Dim SqlConn As New SqlConnection("data source=crserver;initial catalog=crdatabase;integrated security=SSPI;persist security info=False;workstation id=CRSERVER;packet size=4096")
Dim cmdSqlCommand1 As New SqlCommand()
If IsNumeric(txtSearchCase.Text.Substring(0)) Then
cmdSqlCommand1.CommandText = "SELECT * FROM tblCapRec WHERE CaseNumber LIKE '" & txtSearchCase.Text & "%' ORDER BY CaseNumber, PartNumber, LastName, FirstName"
ElseIf
...
txtSearchCase.Focus()
SqlConn.Close()
Exit Sub
End If
cmdSqlCommand1.Connection = SqlConn
SqlConn.Open()
Dim drSqlDataReader1 As SqlDataReader = cmdSqlCommand1.ExecuteReader()
drSqlDataReader1.Read()
If drSqlDataReader1.Read() Then
CaseNumber = drSqlDataReader1.Item(0)
g_CaseNum = drSqlDataReader1.Item(0)
PartNumber = drSqlDataReader1.Item(1)
g_PartNum = drSqlDataReader1.Item(1)
If IsDBNull(drSqlDataReader1.Item(3)) Then
LastName = ""
Else
LastName = Trim(drSqlDataReader1.Item(3))
End If
If IsDBNull(drSqlDataReader1.Item(4)) Then
FirstName = ""
Else
FirstName = Trim(drSqlDataReader1.Item(4))
End If
End Sub[/code]

tblCapRec:

3 CaseNumber char 10 0
2 PartNumber smallint 2 0
0 Status smallint 2 1
0 LastName varchar 50 1
0 FirstName varchar 50 1

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-24 : 17:24:33

SELECT *. Eeks! And embedded T-SQL code in your app. Double eeks!

Anyway, this works fine:

create table table1
(column1 varchar(10))

insert into table1 values('153-654987')

select * from table1 where column1 like '153-%'

drop table table1

Instead of executing the query, you need to print it out to see if you are building it correctly. I'm guessing you aren't. Can you do a response.Write (I'm not a programmer so not sure if that is correct) of cmdSqlCommand1.CommandText? It needs to be formatted like how I have it above.

Tara

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-08-24 : 17:34:23

What's wrong with SELECT *?

And T-SQL in my code?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-24 : 17:40:21

SELECT * is a performance hit. T-SQL code should be put into stored procedures. Two of the benefits of stored procedures are security and performance.

Tara

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-08-24 : 17:44:31

In the textbox I have I want to be able to enter anything really and have it find that case. For example:

1
15
151-
151-6
151-65
...
151-659874

How do I have it search for all of those different things?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-24 : 17:47:00

Did you see this part:

Instead of executing the query, you need to print it out to see if you are building it correctly. I'm guessing you aren't. Can you do a response.Write (I'm not a programmer so not sure if that is correct) of cmdSqlCommand1.CommandText? It needs to be formatted like how I have it above.

?

Tara

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-08-24 : 17:56:53

What do you mean by print it out? If you mean to do it in Query Analyzer, I have done that. This what I put in:

SELECT * FROM tblCapRec Where CaseNUmber LIKE '151-5%' ORDER BY CaseNumber, PartNumber

It works great! But why not in VS.NET? So you don't program at all?

brendalisalowe
Constraint Violating Yak Guru

269 Posts

Posted - 2004-08-24 : 18:16:25

So it has nothing to do with SQL Server. I found it has to do with the Substring method. It doesn't like it. What is better?

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-08-25 : 03:52:55

quote:

If IsNumeric(txtSearchCase.Text.Substring(0)) Then
cmdSqlCommand1.CommandText = "SELECT * FROM tblCapRec WHERE CaseNumber LIKE '" & txtSearchCase.Text & "%' ORDER BY CaseNumber, PartNumber, LastName, FirstName"

And just how numeric do you think "151-" is ?
The code will probably build an empty cmdSqlCommand1.CommandText.

Verify the CommandText in your code.
It should be in the lines of:
SELECT * FROM tblCapRec Where CaseNUmber LIKE '151-%' ORDER BY CaseNumber, PartNumber

[eekmode]
VB, Triple Eeeks! ;-),
Connection string hardcoded, Quadruble Eeeks!
Where do you close the datareader / connection ?
[/eekmode]

Hope this helps Brenda,
/rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources