Solved

vb.net dataset   replace "T"

Posted on 2011-03-01
11
292 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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

743 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

12 Experts available now in Live!

Get 1:1 Help Now