[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

vb.net dataset   replace "T"

Posted on 2011-03-01
11
Medium Priority
?
304 Views
Last Modified: 2012-08-14
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
Comment
Question by:kavvis
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 

Author Comment

by:kavvis
ID: 35005770
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
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35005819
change query like this

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

Author Comment

by:kavvis
ID: 35006400
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35006463
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
 

Author Comment

by:kavvis
ID: 35006517
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
 

Author Comment

by:kavvis
ID: 35006555
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
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35006591
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
 

Author Comment

by:kavvis
ID: 35006632
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
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 2000 total points
ID: 35006651
I think you need this

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

Expert Comment

by:Pratima Pharande
ID: 35006668
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
 

Author Closing Comment

by:kavvis
ID: 35006731
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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question