Solved

vb.net dataset   replace "T"

Posted on 2011-03-01
11
293 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
  • 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
 
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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 500 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Visual Studio Publish Web Configuration does not contain Release 5 28
VB.Net How to Exit Sub - Exit Form??? 5 51
Replace &lt; with < 14 57
Turn on intranet settings 1 42
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

863 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now