vb.net dataset replace "T"

Hi I have a problem.

I read data from a SQL database.. in this database I have some values..
like

date, time, batchnumber, quantiy, comment

and before the batchnumber could contain a Letter. The letter T then the batchnumber.

ex. T123456

No this T is not in used longer but I can´t delete the old data that contains T.

So I wounder can I in some simple way replace the T in my dataadpater before I filll my dataset?

Plese look at my code below.

'Create SQLQuery
sqlquery = "SELECT * FROM TEST WHERE Batch_nr =" & "" & comment & ""

' Create a DataAdapter
da = New SqlDataAdapter(sqlquery, MyConnection)
Try
' Fill the DataAdapter
da.Fill(ds)

Catch ex As Exception
MessageBox.Show( ex.Message )
End Try

so I woul dlike to replace the T or remove it just before I do the
da.Fill(ds)

tips help..

I read my data in a .net application and get the data from a MSSQL 2008 database...

kavvis

Asked: 2011-03-01

Advertise Here

1 Solution

kavvisAuthor Commented: 2011-03-01

I don´t think I would like to do something in the query because I still need the data from the T123456 but I don´t want to save it in the dataset whit T123456 I would like to replace it or remove it...

LVL 39

Pratima PharandeCommented: 2011-03-01

change query like this

sqlquery = "SELECT ate, time, REPLACE(batchnumber,'T','');
, quantiy, comment FROM TEST WHERE Batch_nr =" & "" & comment & ""

kavvisAuthor Commented: 2011-03-01

hmm...
pratima_mcs:

If I do in this way do I get the data from the lines that contains the T123456
or will I don´t get that data?

and I can´g understand your line... should it be ate or is it a misspell? Pleas check my line I have..

sqlquery = "SELECT * FROM Test WHERE Batch_nr =" & "" & comment & ""

Should it really be lite this then?
sqlquery = "SELECT ate, time, REPLACE(batchnumber,'T','');, quantiy, comment FROM TEST WHERE Batch_nr =" & "" & comment & ""

LVL 39

Pratima PharandeCommented: 2011-03-01

try this no ;

sqlquery = "SELECT ate, time, REPLACE(batchnumber,'T',''), quantiy, comment FROM TEST WHERE Batch_nr =" & "" & comment & ""

It will give you your result

kavvisAuthor Commented: 2011-03-01

OK I try to explain..

I have try to run my querys in management for MSSQL 2008

this is my query i run in the management

select Comments from TEST where Batch_nr= '32517290'

Msg 245, Level 16, State 1, Line 5
Conversion failed when converting the nvarchar value 'T12345678' to data type int.

thats the error so this would I like to remove the T in my query but I still want the T123456 data ... I hope you understand..

kavvisAuthor Commented: 2011-03-01

I losing it soon..

tested this

select REPLACE(Batch_nr,'T','') Kommentar from TEST where Batch_nr = '32517200'

then I get a column in return called Kommentar and then the value
32517200

LVL 39

Pratima PharandeCommented: 2011-03-01

select Comments from TEST where Batch_nr= CAST(REPLACE('T12345678','T','') AS int)

try like this

sqlquery = "SELECT * FROM TEST WHERE Batch_nr =" & CAST(REPLACE(comment,'T','') AS int)

kavvisAuthor Commented: 2011-03-01

SELECT * FROM Test WHERE Batch_nr = CAST(REPLACE(3251710,'T','') AS int)

Still give me error
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value 'T12345678' to data type int.

Thank you for try to help me.. can´t understand that I never can learn how to do when I get this big problems :(

LVL 39

Pratima PharandeCommented: 2011-03-01

I think you need this

SELECT * FROM Test WHERE CAST(REPLACE(Batch_nr,'T','') AS int) = 3251710

LVL 39

Pratima PharandeCommented: 2011-03-01

Ok tell me what are the datatypes for Batch_nr and comment ? is it varchar both or int ??

If both varchar

sqlquery = "SELECT * FROM TEST WHERE CAST(REPLACE(Batch_nr,'T','') AS int) =" & CAST(REPLACE(comment,'T','') AS int)

if Batch_nr varchar , comment int then

sqlquery = "SELECT * FROM TEST WHERE CAST(REPLACE(Batch_nr,'T','') AS int) =" & comment

hope this helps

kavvisAuthor Commented: 2011-03-01

Thank you it worked verry well!

I closed this form but something happend!

It alla worked thank you verry mutch..
I have a nother question that I think is easy to fix but I start a new thered for that because this is to long! thank you!
you help me learn this verry well!!

//Kavvis

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Expand your skills with this month's free Premium course.

vb.net dataset replace "T"

Join & Write a Comment Already a member? Login.

Featured Post