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...
kavvisAsked:
Who is Participating?
 
Pratima PharandeConnect With a Mentor Commented:
I think you need this

SELECT * FROM  Test WHERE CAST(REPLACE(Batch_nr,'T','') AS int) = 3251710
0
 
kavvisAuthor Commented:
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...

0
 
Pratima PharandeCommented:
change query like this

 sqlquery = "SELECT ate, time, REPLACE(batchnumber,'T','');
, quantiy, comment FROM  TEST WHERE Batch_nr =" & "" & comment & ""
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
kavvisAuthor Commented:
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 & ""


0
 
Pratima PharandeCommented:
try this no ;

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

It will give you your result
0
 
kavvisAuthor Commented:
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..
0
 
kavvisAuthor Commented:
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

0
 
Pratima PharandeCommented:
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)
0
 
kavvisAuthor Commented:
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 :(
0
 
Pratima PharandeCommented:
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
0
 
kavvisAuthor Commented:
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
0
All Courses

From novice to tech pro — start learning today.