I won't know where in the string "5679" will be, but...

If it is in that textbox (in the details row), I have to display a
drill down row underneith that record.
Any help is appreciated.
TrintWhat do you want to display there ? I detailed row exists for that row where
the '5679' exist you add a new group ti the table, for this etailed group
you can then handle the toggle to tell the report that it only has to toggle
or to be visible if the textbox.Value has a specific value.
HTH, Jens Süßmeyer.
--
http://www.sqlserver2005.de
--
"trint" <trinity.smith@.gmail.com> schrieb im Newsbeitrag
news:1113850634.959940.237640@.o13g2000cwo.googlegroups.com...
> If it is in that textbox (in the details row), I have to display a
> drill down row underneith that record.
> Any help is appreciated.
> Trint
>|||Ok,
This is what I don't understand. The hidden rows with downline dealers
only has '5678'. But the visible row that needs a drill down might
have in the dealerlineageid (which is a field not to be displayed)
'5678' and 2754 and 10 other dealer numbers (multiple dealer numbers).
How would I know if 5678 is in there as a value? If it has multiple
dealer numbers separated by commas? Wouldn't the expression for the
"hidden" value need to be a substring query? How would I do that?
Thanks,
Trint

I want to see the actual SQL being passed - how?

I'm getting a "Input string was not in a correct format."when I'm running a insert statement against my SQL Server 2005 db table. This helps me zilch as I cant see the actual SQL statement to see which one wasnt right. Using a SQLDatasource and a Formview btw.

Datasource is called xSqlIB and formview is called fmvIB.

Any ideas?

synergy, have you tried running the profiler against your db and see what is trying to be executed vs the db? also, have you tried to step through to where the string is being created? if you have access to sql server mgt studio, just go to tools and select sql server profiler. then you can see the string that is getting executed...hope this helps -- jp|||

Synergyauto:

I'm getting a "Input string was not in a correct format."when I'm running a insert statement against my SQL Server 2005 db table. This helps me zilch as I cant see the actual SQL statement to see which one wasnt right. Using a SQLDatasource and a Formview btw.

Datasource is called xSqlIB and formview is called fmvIB.

Any ideas?

That error is not coming from SQL Server; it is coming from your .NET code. So it's not getting as far as the SQL Server and running Profiler will not be of much assistance to you.

|||nice call tmorton...|||

You can set up a function in the xSqlIB_Inserting function that will give you your insert statement, you can write a quick loop to write out all of your varialbes to trace and see what they are. Set tracing to true on your page, and all the information you want will show up at the bottom in red. Good luck!

ProtectedSub sqlProjectData_Inserting(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)Handles sqlProjectData.InsertingTry

Trace.Warn(e.Command.CommandText)

Dim xAsInteger = 0DoUntil x = e.Command.Parameters.Count

Trace.Warn(e.Command.Parameters(x).ParameterName)

IfNot (e.Command.Parameters(x).ValueIsNothing)Then

Trace.Warn(e.Command.Parameters(x).Value.ToString)

EndIf

x += 1

LoopCatch exAs ExceptionDim oLoggerAsNew ErrorLogger.clsLogError(ex, Session, Request)

oLogger.LogError()

EndTryEndSub

|||

Unfortunately the page errors before xSqlIB_Inserting is ever called so that could isnt running (and I dont seem to have your ErrorLogger class). Trace function is nifty though and its cool to finally be able to see what the page sees so quickly. Here's a c/p of what the page shows in red. I dont see it showing me the actual db field that is freaking out on though:

Unhandled Execution Error
Input string was not in a correct format.
at System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal)
at System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info)
at System.Byte.Parse(String s, NumberStyles style, NumberFormatInfo info)
at System.String.System.IConvertible.ToByte(IFormatProvider provider)
at System.Convert.ChangeType(Object value, TypeCode typeCode, IFormatProvider provider)
at System.Web.UI.WebControls.Parameter.GetValue(Object value, String defaultValue, TypeCode type, Boolean convertEmptyStringToNull, Boolean ignoreNullableTypeChanges)
at System.Web.UI.WebControls.Parameter.get_ParameterValue()
at System.Web.UI.WebControls.ParameterCollection.GetValues(HttpContext context, Control control)
at System.Web.UI.WebControls.SqlDataSourceView.InitializeParameters(DbCommand command, ParameterCollection parameters, IDictionary exclusionList)
at System.Web.UI.WebControls.SqlDataSourceView.ExecuteInsert(IDictionary values)
at System.Web.UI.DataSourceView.Insert(IDictionary values, DataSourceViewOperationCallback callback)
at System.Web.UI.WebControls.FormView.HandleInsert(String commandArg, Boolean causesValidation)
at System.Web.UI.WebControls.FormView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup)
at System.Web.UI.WebControls.FormView.OnBubbleEvent(Object source, EventArgs e)
at System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args)
at System.Web.UI.WebControls.FormViewRow.OnBubbleEvent(Object source, EventArgs e)
at System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args)
at System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e)
at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument)
at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

|||Anyone home? hehe|||synergy, what is the number you are trying to convert. i see the parse int32 in the printout and what is the code trying to work with that number? - jp|||

jdingo:

synergy, what is the number you are trying to convert. i see the parse int32 in the printout and what is the code trying to work with that number? - jp

Here's the whole datasource from code, I'm not doing any code behind work on the datasource.

<asp:SqlDataSourceID="xSqlIB"runat="server"ConnectionString="<%$ ConnectionStrings:TestETSAppConnectionString %>"

DeleteCommand="DELETE FROM [xECSIBHeader] WHERE [IBHeaderKey] = @.IBHeaderKey"InsertCommand="INSERT INTO [xECSIBHeader] ([IBNumber], [IBTyp], [IBStatus], [CATCode], [Project], [SeverityCode], [CoverageFeeType], [DateClosed], [AdjusterID], [ClaimNumber], [PolicyNumber], [InsuredName], [Location01], [Location02], [City], [State], [ZipCode], [GrossLoss], [SalesTaxPercent], [SalesTaxAmount], [LossDate], [Supplement], [SupplementExplaination], [MultipleClaim], [ReBilled], [ReBilledIBSubmitted], [OriginalIBNumber], [OriginalIBFee], [RevenueAmtApplied], [ExpenseAmtApplied], [SalesTaxAmtApplied], [CommissionDocBal], [DocBal], [DocumentSelected], [CommissionStatus], [InvoiceStatus], [StatementID], [TaxGroup]) VALUES (@.IBNumber, @.IBTyp, @.IBStatus, @.CATCode, @.Project, @.SeverityCode, @.CoverageFeeType, @.DateClosed, @.AdjusterID, @.ClaimNumber, @.PolicyNumber, @.InsuredName, @.Location01, @.Location02, @.City, @.State, @.ZipCode, @.GrossLoss, @.SalesTaxPercent, @.SalesTaxAmount, @.LossDate, @.Supplement, @.SupplementExplaination, @.MultipleClaim, @.ReBilled, @.ReBilledIBSubmitted, @.OriginalIBNumber, @.OriginalIBFee, @.RevenueAmtApplied, @.ExpenseAmtApplied, @.SalesTaxAmtApplied, @.CommissionDocBal, @.DocBal, @.DocumentSelected, @.CommissionStatus, @.InvoiceStatus, @.StatementID, @.TaxGroup)"SelectCommand="xECS_sp_SelectIBDetails"SelectCommandType="StoredProcedure"UpdateCommand="UPDATE [xECSIBHeader] SET [IBNumber] = @.IBNumber, [IBTyp] = @.IBTyp, [IBStatus] = @.IBStatus, [CATCode] = @.CATCode, [Project] = @.Project, [SeverityCode] = @.SeverityCode, [CoverageFeeType] = @.CoverageFeeType, [DateClosed] = @.DateClosed, [AdjusterID] = @.AdjusterID, [ClaimNumber] = @.ClaimNumber, [PolicyNumber] = @.PolicyNumber, [InsuredName] = @.InsuredName, [Location01] = @.Location01, [Location02] = @.Location02, [City] = @.City, [State] = @.State, [ZipCode] = @.ZipCode, [GrossLoss] = @.GrossLoss, [SalesTaxPercent] = @.SalesTaxPercent, [SalesTaxAmount] = @.SalesTaxAmount, [LossDate] = @.LossDate, [Supplement] = @.Supplement, [SupplementExplaination] = @.SupplementExplaination, [MultipleClaim] = @.MultipleClaim, [ReBilled] = @.ReBilled, [ReBilledIBSubmitted] = @.ReBilledIBSubmitted, [OriginalIBNumber] = @.OriginalIBNumber, [OriginalIBFee] = @.OriginalIBFee, [RevenueAmtApplied] = @.RevenueAmtApplied, [ExpenseAmtApplied] = @.ExpenseAmtApplied, [SalesTaxAmtApplied] = @.SalesTaxAmtApplied, [CommissionDocBal] = @.CommissionDocBal, [DocBal] = @.DocBal, [DocumentSelected] = @.DocumentSelected, [CommissionStatus] = @.CommissionStatus, [InvoiceStatus] = @.InvoiceStatus, [StatementID] = @.StatementID, [TaxGroup] = @.TaxGroup WHERE [IBHeaderKey] = @.IBHeaderKey"><DeleteParameters><asp:ParameterName="IBHeaderKey"Type="Int64"/></DeleteParameters><UpdateParameters><asp:ParameterName="IBNumber"Type="String"/><asp:ParameterDefaultValue="R"Name="IBTyp"Type="Byte"/><asp:ParameterDefaultValue="1"Name="IBStatus"Type="Byte"/><asp:ParameterName="CATCode"Type="String"/><asp:ParameterDefaultValue="C002003"Name="Project"Type="String"/><asp:ParameterName="SeverityCode"Type="Int16"/><asp:ParameterDefaultValue="1"Name="CoverageFeeType"Type="Int16"/><asp:ParameterDefaultValue="1/1/1900"Name="DateClosed"Type="DateTime"/><asp:ParameterDefaultValue="271"Name="AdjusterID"Type="Int32"/><asp:ParameterName="ClaimNumber"Type="String"/><asp:ParameterName="PolicyNumber"Type="String"/><asp:ParameterName="InsuredName"Type="String"/><asp:ParameterName="Location01"Type="String"/><asp:ParameterName="Location02"Type="String"/><asp:ParameterName="City"Type="String"/><asp:ParameterName="State"Type="String"/><asp:ParameterName="ZipCode"Type="String"/><asp:ParameterDefaultValue="0.00"Name="GrossLoss"Type="Double"/><asp:ParameterDefaultValue="0"Name="SalesTaxPercent"Type="Double"/><asp:ParameterDefaultValue="0"Name="SalesTaxAmount"Type="Double"/><asp:ParameterDefaultValue="1/1/1900"Name="LossDate"Type="DateTime"/><asp:ParameterName="Supplement"Type="Boolean"/><asp:ParameterName="SupplementExplaination"Type="String"/><asp:ParameterName="MultipleClaim"Type="Boolean"/><asp:ParameterName="ReBilled"Type="Boolean"/><asp:ParameterName="ReBilledIBSubmitted"Type="Boolean"/><asp:ParameterName="OriginalIBNumber"Type="String"/><asp:ParameterName="OriginalIBFee"Type="Double"/><asp:ParameterName="RevenueAmtApplied"Type="Double"/><asp:ParameterName="ExpenseAmtApplied"Type="Double"/><asp:ParameterName="SalesTaxAmtApplied"Type="Double"/><asp:ParameterName="CommissionDocBal"Type="Double"/><asp:ParameterName="DocBal"Type="Double"/><asp:ParameterName="DocumentSelected"Type="Boolean"/><asp:ParameterName="CommissionStatus"Type="Int16"/><asp:ParameterName="InvoiceStatus"Type="Int16"/><asp:ParameterName="StatementID"Type="Int32"/><asp:ParameterName="TaxGroup"Type="String"/><asp:ParameterName="IBHeaderKey"Type="Int64"/></UpdateParameters><SelectParameters><asp:QueryStringParameterDefaultValue="18R315973"Name="ClaimNumber"QueryStringField="ClaimNumber"Type="String"/></SelectParameters><InsertParameters><asp:ParameterName="IBNumber"Type="String"/><asp:ParameterDefaultValue="R"Name="IBTyp"Type="Byte"/><asp:ParameterDefaultValue="3"Name="IBStatus"Type="Byte"/><asp:ParameterName="CATCode"Type="String"/><asp:ParameterDefaultValue="C002003"Name="Project"Type="String"/><asp:ParameterName="SeverityCode"Type="Int16"/><asp:ParameterDefaultValue="1"Name="CoverageFeeType"Type="Int16"/><asp:ParameterDefaultValue="1/1/1900"Name="DateClosed"Type="DateTime"/><asp:ParameterDefaultValue="271"Name="AdjusterID"Type="Int32"/><asp:ParameterName="ClaimNumber"Type="String"/><asp:ParameterName="PolicyNumber"Type="String"/><asp:ParameterName="InsuredName"Type="String"/><asp:ParameterName="Location01"Type="String"/><asp:ParameterName="Location02"Type="String"/><asp:ParameterName="City"Type="String"/><asp:ParameterName="State"Type="String"/><asp:ParameterName="ZipCode"Type="String"/><asp:ParameterDefaultValue="0.00"Name="GrossLoss"Type="Double"/><asp:ParameterDefaultValue="0"Name="SalesTaxPercent"Type="Double"/><asp:ParameterDefaultValue="0"Name="SalesTaxAmount"Type="Double"/><asp:ParameterDefaultValue="1/1/1900"Name="LossDate"Type="DateTime"/><asp:ParameterName="Supplement"Type="Boolean"/><asp:ParameterName="SupplementExplaination"Type="String"/><asp:ParameterName="MultipleClaim"Type="Boolean"/><asp:ParameterName="ReBilled"Type="Boolean"/><asp:ParameterName="ReBilledIBSubmitted"Type="Boolean"/><asp:ParameterName="OriginalIBNumber"Type="String"/><asp:ParameterName="OriginalIBFee"Type="Double"/><asp:ParameterName="RevenueAmtApplied"Type="Double"/><asp:ParameterName="ExpenseAmtApplied"Type="Double"/><asp:ParameterName="SalesTaxAmtApplied"Type="Double"/><asp:ParameterName="CommissionDocBal"Type="Double"/><asp:ParameterName="DocBal"Type="Double"/><asp:ParameterName="DocumentSelected"Type="Boolean"/><asp:ParameterName="CommissionStatus"Type="Int16"/><asp:ParameterName="InvoiceStatus"Type="Int16"/><asp:ParameterName="StatementID"Type="Int32"/><asp:ParameterName="TaxGroup"Type="String"/></InsertParameters></asp:SqlDataSource>|||

Dang, sorry, guess i hit "post" 3 times too many

________________________________________________________________

Note: duplicate posts were deleted by moderator tmorton

|||synergy, i dont have an exact answer from what i have seen, but if you are getting no where and i know you have lots of fields you are working with, but you could run your insert command trying each field at a time and see which one throws the error in case it is a simple matter some mismatch between and accepted value for a field in the DB and an actual value being passed in as a parameter. I know it is a more brute force approach but thats all i have at the moment. good luck -- jp|||Thanks for hanging with me jdingo, I noticed in the code above that 2 fields were listed as "byte" (dunno how it got that way) but they were really integer fields. So passing them "False" instead of an integer was causing the error. Thanks for the help!|||nice synergy, glad that you found it, many times i solve my own problems just by explaining them and going through them with others. have a good one-- jpsql

i want help

hi

i want to check that only string in textbox. the textbox shoould enterd the strings so is there IsString() function in vb.net

plz help me....

Hi,
first of all your subject does say nothing about your problem, so many people won't read it..
secondly try to use the forum search, your problem has been discusses many times..
for example:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=432801&SiteID=1

I only get the first character of the field returned .. what am i doing wrong ?

here is my business/data object for some reason I only get the first character back, say value is Charlie, I only get C

 
public string GetUserName(long UserId) {try {string userName; DiscussionDB data =new DiscussionDB(); List paramlist =new List(); paramlist.Add(data.CreateParameter("@.UserId", UserId)); paramlist.Add(data.CreateParameter("@.UserName","", ParameterDirection.Output, DbType.String, 20)); data.ExecuteNonQuery("dbo.Discussion_User_Name",ref paramlist); userName = paramlist[1].Value.ToString();return userName; }catch {throw; } }ALTER PROCEDURE [dbo].[Discussion_User_Name]@.UserId bigint,@.UserName varchar(20) outputASSET NOCOUNT ONSET @.UserName = (Select [Name] from Discussion_Member WHERE UserID = @.UserID)if (@.UserNameis null or @.UserName ='')BEGIN SET @.UserName = (Select UserName from Membership_User WHERE UserID = @.UserID)END
 

hello,

It does not seem that thre is anything wrong with the code.

Have you checked the size of the columns "Name" and "UserName" in Discussion_Member and/or Membership_User ?

hope this helps?

regards,

g

|||

If you run the stored procedure using the sql server tools, does it return the correct result?

|||

yes when I run the SP in MS sever 2005 studio, I get the full field ... e.g Charlie

|||

You need to set the size of @.UserName, the default is 1. There's a size property toIDbDataParameter that you need to set (it looks like your DiscussionDB is wrapping IDbDataParameter and some other stuff)

|||

I tried wording your suggestion in my code by I can't seem to figure it out

I was thinking that the 20in the code below was giving the size

paramlist.Add(data.CreateParameter("@.UserName","", ParameterDirection.Output, DbType.String, 20));

|||

I thought I got this working but I was wrong

I am still having this issue, here is my last code

public string GetUserName(long UserId) {try {string userName; DiscussionDB data =new DiscussionDB(); List paramlist =new List(); paramlist.Add(data.CreateParameter("@.UserId", UserId)); paramlist.Add(data.CreateParameter("@.UserName","", ParameterDirection.Output, DbType.String, 15)); paramlist[1].Size = 15; data.ExecuteNonQuery("dbo.Discussion_User_Name_Fetch",ref paramlist); userName = paramlist[1].Value.ToString();return userName; }catch {throw; } }

I need to compare two string values and give confidence score on how much they are similar.How d

I need to compare two strings and get a confidence score on how similar they are. I have around half a million of such strings to compare.While Fuzzy look up sample codes I used compare a string against all the records in the reference table,my comparison requirement is limited to two given strings.
Can some body please help me on how to do this using fuzzy look up?
Thank you very much!Do you want to compare all the source strings with one string in the

lookup table? If that is the case then can you not use a view of the

one reference column as the lookup and not the table object? If I have

misinterpreted the requirement then please let me know.

Allan

"Ashraj@.discussions.microsoft.com"

wrote in message

news:046a46c8-8fa8-4399-9417-24493b01d9d0@.discussions.microsoft.com:

> I need to compare two strings and get a confidence score on how similar

> they are. I have around half a million of such strings to compare.While

> Fuzzy look up sample codes I used compare a string against all the

> records in the reference table,my comparison requirement is limited to

> two given strings.

>

>

> Can some body please help me on how to do this using fuzzy look up?

> Thank you very much!|||Hi,
Thanks much for your input.Sorry I wasn't clear.

Actually, I have two files . FILEA and FILEB. They have have similar structure with two fields Accountnumber(unique key) and Address(String value) . FileB is the reference file.FileA is input file.Both have same number of records with matching key fields.

For each account number in FILEA,the Address string has to be compared to address string in FILEB with the same account number.

For example Address in first row "45 Sunset blvd" with acct number 111 ,needs to be compared with address of account 111 in FILEB which is "456 Sunset Blvd " and a confidance and similarity score needs to be registered .

The Key field account number is only for linking purpose which may not be required if I have a way to pass two strings and get the comparison results.

FileA

Acct

Address

111

45 Sunset blvd

555

236 technical str

999

448 madera

FileB

Acct

Address

111

456 Sunset Blvd

555

236 Technical st.

999

449 Madera

Looking forward to any help that can be provided.Please feel free to ask for any clarifications.Thanks much|||Ok So I have this

CREATE TABLE AccountsInput(AcctNUm int, Street varchar(50))

CREATE TABLE AccountsLookup(AcctNUm int, Street varchar(50))

Insert AccountsInput VALUES(111,'45 Sunset Boulevard')

Insert AccountsInput VALUES(555,'236 technical str')

Insert AccountsInput VALUES(999,'448 Madeira')

Insert AccountsLookup VALUES(111,'45 Sunset BLVD')

Insert AccountsLookup VALUES(555,'236 technical st.')

Insert AccountsLookup VALUES(999,'448 Madera')

I want to compare both the acct# and the address to get matches or not.

I configure the fuzzy lookup to match on both the attributes and I get

this.

AcctNUmStreetLooked Up account NumLooked Up Street

Name_Similarity_Confidence

11145 Sunset Boulevard11145 Sunset BLVD0.74537040.9875

555236 technical str555236 technical st.0.88491890.9875

999448 Madeira999448 Madera0.92811570.9875

Is this not what you want ?

Allan

"Ashraj@.discussions.microsoft.com"

wrote in message

news:74b34ed7-c2df-4569-b8bd-1d5e4ed19b4f@.discussions.microsoft.com:

> Hi,

> Thanks much for your input.Sorry I wasn't clear.

>

> Actually, I have two files . FILEA and FILEB. They have have similar

> structure with two fields Accountnumber(unique key) and Address(String

> value) . FileB is the reference file.FileA is input file.Both have same

> number of records with matching key fields.

>

> For each account number in FILEA,the Address string has to be compared

> to address string in FILEB with the same account number.

>

>

> For example Address in first row "45 Sunset blvd" with acct number 111

> ,needs to be compared with address of account 111 in FILEB which is

> "456 Sunset Blvd " and a confidance and similarity score needs to be

> registered .

>

> The Key field account number is only for linking purpose which may not

> be required if I have a way to pass two strings and get the comparison

> results.

>

>

>

> FileA

>

>

>

> Acct

>

> Address

>

> 111

>

> 45 Sunset blvd

>

> 555

>

> 236 technical str

>

> 999

>

> 448 madera

>

>

>

>

>

> FileB

>

>

>

> Acct

>

> Address

>

> 111

>

> 456 Sunset Blvd

>

> 555

>

> 236 Technical st.

>

> 999

>

> 449 Madera

>

>

>

> Looking forward to any help that can be provided.Please feel free to ask

> for any clarifications.Thanks much|||Thanks Alan. It looks almost perfect .
I just wanted to confirm one more thing before I say this is what I exactly want.

For our comparison, I have the following basic assumption that
a) account numbers are not corrupt and all account numbers in FILEA are in FILEB and viceversa.
b) FILEA and FILEB are unique keyed with account number

As you had detailed, I want address of account number 111 in FILEA to be compared to address of account number 111 ONLY in FILEB.

The address comparison should be made ONLY between the records that have same account number in FILEA and FILEB .Comparison and statistics should not be made between address that don't have common account number.
For example, Address of account number 111 SHOULD NOT be compared with address of account number 555 and 999.

In our example for first record it SHOULD NOT execute 3 searches and give the following.
111 45 Sunset Boulevard 111 45 Sunset BLVD 0.7453704 0.9875
--
111 45 Sunset Boulevard 555 236 technical st. 0.1xxxxxx 0.xxxxx

111 45 Sunset Boulevard 999 448 Madera 0.9281157 0.9875

It should do ONLY do comparison one time and give only the following:-

111 45 Sunset Boulevard 111 45 Sunset BLVD 0.7453704 0.9875

Since account numbers are assumed to be clean and same in both files,if we don't include the account number match similarity and confidence and have the confidence and similarity for the address alone that would be perfect.

So the idea is basically to compare the two addresses .Account number only helps to link the input file address with the reference file address.Instead of account number,if we can compare the address sequentially (like first record of input file to be compared with first record of reference only) then that is fine too.

This way,each address of FILEA need NOT be compared with each address of reference file which will be huge system consuming and is also not required.
Thanks for your patience and please do feel free to make me clarify further.

|||The CROSS PRODUCT matching you describe will not occur or at least it

will not be visible to you I think. Thinking about this I do not know

if the engine would consider matching (It is fuzzy after all) but

because of the so way out probability and confidence scores it would not

get further than a distant thought.

To that end I changed the input data slightly

CREATE TABLE AccountsInput(AcctNUm int, Street varchar(50))

CREATE TABLE AccountsLookup(AcctNUm int, Street varchar(50))

Insert AccountsInput VALUES(111,'45 Sunset Boulevard')

Insert AccountsInput VALUES(555,'236 technical str')

Insert AccountsInput VALUES(999,'448 Madeira')

Insert AccountsLookup VALUES(111,'45 Sunset BLVD')

Insert AccountsLookup VALUES(555,'236 technical st.')

Insert AccountsLookup VALUES(999,'448 Madera')

Insert AccountsLookup VALUES(996,'442 Madeira')--added very close to 999

I still only matched on the same rows.

Allam

"Ashraj@.discussions.microsoft.com"

wrote in message

news:5b707de0-1b00-40d3-9a4b-4868791a245f@.discussions.microsoft.com:

> Thanks Alan. It looks almost perfect .

> I just wanted to confirm one more thing before I say this is what I

> exactly want.

>

> For our comparison, I have the following basic assumption that

> a) account numbers are not corrupt and all account numbers in FILEA are

> in FILEB and viceversa.

> b) FILEA and FILEB are unique keyed with account number

>

> As you had detailed, I want address of account number 111 in FILEA to

> be compared to address of account number 111 ONLY in FILEB.

>

> The address comparison should be made ONLY between the records that have

> same account number in FILEA and FILEB .Comparison and statistics should

> not be made between address that don't have common account number.

>

>

> For example, Address of account number 111 SHOULD NOT be compared with

> address of account number 555 and 999.

>

> In our example for first record it SHOULD NOT execute 3 searches and

> give the following.

> 111 45 Sunset Boulevard 111 45 Sunset BLVD 0.7453704

> 0.9875

>

> --

> 111 45 Sunset Boulevard 555 236 technical st. 0.1xxxxxx

> 0.xxxxx

>

>

> 111 45 Sunset Boulevard 999 448 Madera 0.9281157

> 0.9875

>

> It should do ONLY do comparison one time and give only the following:-

>

> 111 45 Sunset Boulevard 111 45 Sunset BLVD 0.7453704 0.9875

>

>

>

>

>

>

> Since account numbers are assumed to be clean and same in both files,if

> we don't include the account number match similarity and confidence and

> have the confidence and similarity for the address alone that would be

> perfect.

>

> So the idea is basically to compare the two addresses .Account number

> only helps to link the input file address with the reference file

> address.Instead of account number,if we can compare the address

> sequentially (like first record of input file to be compared with first

> record of reference only) then that is fine too.

>

> This way,each address of FILEA need NOT be compared with each address of

> reference file which will be huge system consuming and is also not

> required.

>

>

> Thanks for your patience and please do feel free to make me clarify

> further.|||Thank you much. Okay.let me put it in a slightly different way to address the issue,since assuming SSIS internally does the different combinations but displays only one ,would mean still again half million look ups for each of the records in FILEA.

Let us have both Mailing address and property address in one file as below.
This will be the only input data file .

Acct

Mail Address

Prop Address

111

45 Sunset blvd

45 Sunst Boulvrd

555

236 technical str

23 Technic street

999

448 madera

448 Made a

We want to do is Mailing address and prop address strings for each row and give the output as ACCT#,Mail Address,Prop Address ,Similarity,Confidance
as below.

Mail Address

Prop Address

Similarity

Confidence

45 Sunset blvd

45 Sunst Boulvrd

0.xxxxxxx

o.xxxxxxxx

236 technical str

23 Technic street

0.xxxxxxxx

0.xxxxxxxx

448 madera

448 Made a

0.xxxxxxx

0.xxxxxxx

Just one file that has both the reference field and look up field.

I need the function to return a srting

Hii every one
When i use the function of (select) from the data bass it return dataset or some thing else
But I need it to return string or the data element which in the query not all the query

like

I dont need that
_____________
| Id | Name |
------
| 1 | Bill |
-------
I dont need All of that to display But I need to display the name only in Label or textbox
like
Bill

Thanks
MaroRead about "OUTPUT paramaters"|||I think the easiest way for you to get the value you're looking for, would be to capture the output results of your function to a dataset and then set the value of your label or textbox to an item in the dataset.

Dim myDataset As New Dataset()
myDataset.Datasource = yourFunctionName(parms)

Label1.text = myDataset.Tables(0).Rows(0).Item("myColumnHeaderName")|||Thanks that is what I need
Maro

I need some help on my Sql string

Hi,

i have a sqlString to select records from database as below,

StrSql="Select Distinct ItemCode From StockOnHand Where " & _
 "Description like @.Des"

this sql string works correctly, but i need to display those distinct ItemCode with their Description and Price, but i failed using the sql String at below...

StrSql="Select Distinct ItemCode, Description, Price From StockOnHand Where " & _
 "Description like @.Des"

I know there is something wrong with my sql string.. but my SQL knowledge is onli at the basic level, can any1 pls help me on this...?

Thanks in advances
life's Ng::I know there is something wrong with my sql string..

Good, let's start VERY VERY VERY at the basics.

This:

::but i failed using the sql String at below...

will NOT get you help.

It did not "just fail", it gave you an error string DESCRIBING the error. Now, even while you may not understand this error string when you read it (and normally it is pretty self-explaining), WE may want to have a look at the EXACT error coming there.

So please, post the error you got, too.

Btw, using:

"Select DISTINCT" is about the best way to kill your peroformance anyhow. And - sorry - not really smart in the queries you give there. Unless, naturally, you want your database to be terribly slow.|||Without knowing the exact error, I can tell you that this is not correct:
"Description like @.Des"

try this instead:
"Description like '%@.Des%'"|||Hi, Sorry for my mistake to not descript my problem well and thanks for ur info bout "DISTINCT"

i dint receive any error with both of the SQL statements.

In my table, there is 10 columns ("Description", "ItemCode", "Prices".......)

the 2nd one return records which is not expected... few records selected with the same ItemCode, i onli want the distinct ItemCode displayed according to its description.

This page is created just for user to search&check on the ItemCode using their Description... I think i need to reconstruct my database design.

Best Regards
life's Ng

I need help with this sql string....({0}, {1},{2}, {3});", Chr(64 + j)

I was looking around on the internet and need something like this:
Dim NumberOfRows As Integer = 10
Dim NumberOfColumns As Integer = 4
Dim PlaneID As String = "3434278231"
Dim SeatType As String
Dim i As Integer
Dim j As Integer

For i = 1 To NumberOfRows
For j = 1 To NumberOfColumns
If j = 2 Or j = 4 Then
'Assuming B & D are Window Seats (j=1 For A, 2 for B and so on)
SeatType = "Window"
Else
SeatType = "Aisle"
End If
Qry = String.Format("INSERT INTO TBL_SEAT (SEAT_NUM, SEAT_ROW, SEAT_TYPE, PLANE_ID) VALUES ('{0}', {1},'{2}', '{3}');", Chr(64 + j), i, SeatType, PlaneID)
MsgBox(Qry)
Next j
Next i

i figured out most of it but need help figureing out how it inserts the seat number and row?

what does all this do: ('{0}', {1},'{2}', '{3}');", Chr(64 + j)

thanks for any advice.Assigns:

SEAT_NUM Chr(64 + j) to '{0}',
SEAT_ROW i to {1},
SEAT_TYPE SeatType to '{2}' and
PLANE_ID PlaneID to '{3}'.

:rolleyes:

I Need Help With The Connection String

I Need Help With The Connection String cuz that i need to connect with the database for Mobile Application and i have sqlexpress

Since you didn't post your connection string, it appears that you don't need help with it -you want someone to do it for you.

You might find a visit to www.connectionstrings.com to be useful.

|||

Thank you about your help

i need another help

I need to write an stored procedure that contain the 4 T-sql statement (Select,insert,update,delete) these T-SQL related to the same table.

When I Call the stored procedure on the Visual Stedio How can i deals with this Situation I need The Code

|||

Thank you about your help

i need another help

I need to write an stored procedure that contain the 4 T-sql statement (Select,insert,update,delete) these T-SQL related to the same table.

When I Call the stored procedure on the Visual Stedio How can i deals with this Situation I need The Code

I Need Help With The Connection String

I Need Help With The Connection String cuz that i need to connect with the database for Mobile Application and i have sqlexpress

Since you didn't post your connection string, it appears that you don't need help with it -you want someone to do it for you.

You might find a visit to www.connectionstrings.com to be useful.

|||

Thank you about your help

i need another help

I need to write an stored procedure that contain the 4 T-sql statement (Select,insert,update,delete) these T-SQL related to the same table.

When I Call the stored procedure on the Visual Stedio How can i deals with this Situation I need The Code

|||

Thank you about your help

i need another help

I need to write an stored procedure that contain the 4 T-sql statement (Select,insert,update,delete) these T-SQL related to the same table.

When I Call the stored procedure on the Visual Stedio How can i deals with this Situation I need The Code

I need help with query string Parser error

I can not get this query to work can some on help me?

SELECT count(JOBID) AS 'transcount', count(distinct patientid) AS 'patient count', sum(TRANSPORTATION_TCOST)
AS 'tcost,
sum(TRANSPORTATION_DISC_COST) as ' dtcost ',
avg(TRANSPORTATION_DISC) as "avgTDisc",
(sum(transportation_tcost) + sum(TRANSPORTATION_DISC_COST)) as "TGrossAMTBilled',
(sum(transportation_tcost) / count(DISTINCT patientid)) AS 'PatAvgT', sum(TRANSPORTATION_DISC) AS 'avgPercentDiscT' job.JURSDICTION
FROM job LEFT JOIN
payer ON payer.payerid = job.payerid LEFT JOIN
states ON job.jurisdiction = states.initials
WHERE (job.transportation = '1') AND ((job.datedcreated = @.startdate) AND (job.datecreated = @.enddate)) AND states.region = 'GA'
GROUP BY job.JURISDICTION
ORDER BY PatAvgT DESC

Please include the specific error message you are getting. Just browsing at the query I see a missing quote from:

AS 'tcost,

|||

In addition to Cloris' post above, you have used a mix of double quotes/single quotes for alias column names. You dont need to use any quotes for alias.