[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 306
  • Last Modified:

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...
0
kavvis
Asked:
kavvis
  • 6
  • 5
1 Solution
 
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
 
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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
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:
I think you need this

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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now