Conversion failed when converting from a character string to uniqueidentifier. - PLEASE HE

I am trying to store a unique identifier that is text into a field in a SQL DB that is type uniqueidentifier and I get the follow error message.

Conversion failed when converting from a character string to uniqueidentifier.

My Code is shown below:

comSQL.Parameters.AddWithValue("@.PROPERTYID", Format(Request.QueryString("ID").ToString,"{0:########-####-####-####-############}"))

This has worked before but isnt' anymore. Any ideas?

jsmith3465:

comSQL.Parameters.AddWithValue("@.PROPERTYID", Format(Request.QueryString("ID").ToString,"{0:########-####-####-####-############}"))

have you tried as...

comSQL.Parameters.AddWithValue("@.PROPERTYID",New Guid(Format("werwerwerwerwerwerwerwerwerwerwe","{0:########-####-####-####-############}")))

|||

I tried adding the New Guid() and that did not solve the problem either. Anymore ideas? I am trying to convert Text to Unique Identifier for storage in SQL Server 2005.

Thanks for all of your help!

Ryan

Conversion failed when converting from a character string to uniqueidentifier.

Hi, i have a problem, i keep getting this Error.

I want to insert an uniqueidentifier using a textbox, i use the following code to insert.

SqlDataSource1.InsertParameters[

"RWID"] =newParameter("RWID",TypeCode.String, RWID);

SqlDataSource1.Insert();

The databasetype is an uniqueidentifier of that column.

Anyone who can help me with this problem?

Hi friend,

Have you tried TypeCode.Object

|||

I tried using TypeCode.Object, then I get another error:

Implicit conversion from data type sql_variant to uniqueidentifier is not allowed. Use the CONVERT function to run this query.

|||

Hi friend,

I tried a sample to reproduce the error. But it its working fine for me. I created a table named t1 with one column c1 of datatype unique identifier.

SQL datasource code

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:iGoldWebConnectionString %>"

SelectCommand="SELECT * FROM [T1]"InsertCommand="insert into t1 values(@.g)" ></asp:SqlDataSource>Data Insert Code

SqlDataSource1.InsertParameters["g"] =newParameter("g",TypeCode.String,Guid.NewGuid().ToString());

SqlDataSource1.Insert();

Its working fine for me.

I hope the problem is with the guid which you get from textbox . Have you checked you receive only valid GUID.

Conversion failed when converting datetime from character string

I have a strange problem that I need help troubleshooting. I have the
following statement in a stored procedure:
SELECT IsNull(NullIf(Convert(varchar(20), Cast(Value AS datetime), 126), ''),
'')
FROM #TFieldValues TFV
WHERE TFV.DataType = 'Date'
When this statement is run, it returns the following error;
Msg 241, Level 16, State 1, Procedure <the name of my procedure>, Line 142
Conversion failed when converting datetime from character string.
The field #TFieldValues.Value is created as varchar(2000).
So, I run the following statement, and 21 rows are returned, where 8 are date
values and 13 are empty strings:
SELECT Value FROM #TFieldValues WHERE DataType = 'Date'
The 8 date values returned are the following:
2/23/2006
03/21/2006
08/23/2006
1O/18/2OO5
1O/18/2OO5
1O/18/2OO5
02/26/2007
02/26/2007
I then run the following statement
SELECT
TFV.Value
FROM #TFieldValues TFV
WHERE
CASE
WHEN ISDATE(Value) = 0 THEN 0
WHEN ISDATE(Value) = 1 THEN 1
END = 1
AND TFV.DataType = 'Date'
Instead of 8 date values being returned, I only return 5, which are the
following:
2/23/2006
03/21/2006
08/23/2006
02/26/2007
02/26/2007
In just looking at the returns in the grid in Management Studio, when I run
the select statement that returned the 8 date values, it appears the
1O/18/2OO5 values are of a different font size. This can probably even be
seen as you compare the zero's from the following paste:
08/23/2006
1O/18/2OO5
Any ideas on validation, or handling this situation?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200703/1
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6f39ac268793b@.uwe...
>I have a strange problem that I need help troubleshooting. I have the
> following statement in a stored procedure:
> SELECT IsNull(NullIf(Convert(varchar(20), Cast(Value AS datetime), 126),
> ''),
> '')
> FROM #TFieldValues TFV
> WHERE TFV.DataType = 'Date'
> When this statement is run, it returns the following error;
> Msg 241, Level 16, State 1, Procedure <the name of my procedure>, Line 142
> Conversion failed when converting datetime from character string.
> The field #TFieldValues.Value is created as varchar(2000).
> So, I run the following statement, and 21 rows are returned, where 8 are
> date
> values and 13 are empty strings:
> SELECT Value FROM #TFieldValues WHERE DataType = 'Date'
> The 8 date values returned are the following:
> 2/23/2006
> 03/21/2006
> 08/23/2006
> 1O/18/2OO5
> 1O/18/2OO5
> 1O/18/2OO5
> 02/26/2007
> 02/26/2007
> I then run the following statement
> SELECT
> TFV.Value
> FROM #TFieldValues TFV
> WHERE
> CASE
> WHEN ISDATE(Value) = 0 THEN 0
> WHEN ISDATE(Value) = 1 THEN 1
> END = 1
> AND TFV.DataType = 'Date'
> Instead of 8 date values being returned, I only return 5, which are the
> following:
> 2/23/2006
> 03/21/2006
> 08/23/2006
> 02/26/2007
> 02/26/2007
> In just looking at the returns in the grid in Management Studio, when I
> run
> the select statement that returned the 8 date values, it appears the
> 1O/18/2OO5 values are of a different font size. This can probably even be
> seen as you compare the zero's from the following paste:
> 08/23/2006
> 1O/18/2OO5
No - it isn't a font issue. These are capital O characters, not zeros.
Switch to a font that uses slashed zeros and you will more clearly see this.
Consider this one of the "advantages" to using the EAV data model - store
anything

Conversion failed when converting datetime from character string

I have a strange problem that I need help troubleshooting. I have the
following statement in a stored procedure:
SELECT IsNull(NullIf(Convert(varchar(20), Cast(Value AS datetime), 126), ''),
'')
FROM #TFieldValues TFV
WHERE TFV.DataType = 'Date'
When this statement is run, it returns the following error;
Msg 241, Level 16, State 1, Procedure <the name of my procedure>, Line 142
Conversion failed when converting datetime from character string.
The field #TFieldValues.Value is created as varchar(2000).
So, I run the following statement, and 21 rows are returned, where 8 are date
values and 13 are empty strings:
SELECT Value FROM #TFieldValues WHERE DataType = 'Date'
The 8 date values returned are the following:
2/23/2006
03/21/2006
08/23/2006
1O/18/2OO5
1O/18/2OO5
1O/18/2OO5
02/26/2007
02/26/2007
I then run the following statement
SELECT
TFV.Value
FROM #TFieldValues TFV
WHERE
CASE
WHEN ISDATE(Value) = 0 THEN 0
WHEN ISDATE(Value) = 1 THEN 1
END = 1
AND TFV.DataType = 'Date'
Instead of 8 date values being returned, I only return 5, which are the
following:
2/23/2006
03/21/2006
08/23/2006
02/26/2007
02/26/2007
In just looking at the returns in the grid in Management Studio, when I run
the select statement that returned the 8 date values, it appears the
1O/18/2OO5 values are of a different font size. This can probably even be
seen as you compare the zero's from the following paste:
08/23/2006
1O/18/2OO5
Any ideas on validation, or handling this situation?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200703/1"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:6f39ac268793b@.uwe...
>I have a strange problem that I need help troubleshooting. I have the
> following statement in a stored procedure:
> SELECT IsNull(NullIf(Convert(varchar(20), Cast(Value AS datetime), 126),
> ''),
> '')
> FROM #TFieldValues TFV
> WHERE TFV.DataType = 'Date'
> When this statement is run, it returns the following error;
> Msg 241, Level 16, State 1, Procedure <the name of my procedure>, Line 142
> Conversion failed when converting datetime from character string.
> The field #TFieldValues.Value is created as varchar(2000).
> So, I run the following statement, and 21 rows are returned, where 8 are
> date
> values and 13 are empty strings:
> SELECT Value FROM #TFieldValues WHERE DataType = 'Date'
> The 8 date values returned are the following:
> 2/23/2006
> 03/21/2006
> 08/23/2006
> 1O/18/2OO5
> 1O/18/2OO5
> 1O/18/2OO5
> 02/26/2007
> 02/26/2007
> I then run the following statement
> SELECT
> TFV.Value
> FROM #TFieldValues TFV
> WHERE
> CASE
> WHEN ISDATE(Value) = 0 THEN 0
> WHEN ISDATE(Value) = 1 THEN 1
> END = 1
> AND TFV.DataType = 'Date'
> Instead of 8 date values being returned, I only return 5, which are the
> following:
> 2/23/2006
> 03/21/2006
> 08/23/2006
> 02/26/2007
> 02/26/2007
> In just looking at the returns in the grid in Management Studio, when I
> run
> the select statement that returned the 8 date values, it appears the
> 1O/18/2OO5 values are of a different font size. This can probably even be
> seen as you compare the zero's from the following paste:
> 08/23/2006
> 1O/18/2OO5
No - it isn't a font issue. These are capital O characters, not zeros.
Switch to a font that uses slashed zeros and you will more clearly see this.
Consider this one of the "advantages" to using the EAV data model - store
anythingsqlsql

Conversion failed when converting datetime from character string

I have a strange problem that I need help troubleshooting. I have the
following statement in a stored procedure:
SELECT IsNull(NullIf(Convert(varchar(20), Cast(Value AS datetime), 126), '')
,
'')
FROM #TFieldValues TFV
WHERE TFV.DataType = 'Date'
When this statement is run, it returns the following error;
Msg 241, Level 16, State 1, Procedure <the name of my procedure>, Line 142
Conversion failed when converting datetime from character string.
The field #TFieldValues.Value is created as varchar(2000).
So, I run the following statement, and 21 rows are returned, where 8 are dat
e
values and 13 are empty strings:
SELECT Value FROM #TFieldValues WHERE DataType = 'Date'
The 8 date values returned are the following:
2/23/2006
03/21/2006
08/23/2006
1O/18/2OO5
1O/18/2OO5
1O/18/2OO5
02/26/2007
02/26/2007
I then run the following statement
SELECT
TFV.Value
FROM #TFieldValues TFV
WHERE
CASE
WHEN ISDATE(Value) = 0 THEN 0
WHEN ISDATE(Value) = 1 THEN 1
END = 1
AND TFV.DataType = 'Date'
Instead of 8 date values being returned, I only return 5, which are the
following:
2/23/2006
03/21/2006
08/23/2006
02/26/2007
02/26/2007
In just looking at the returns in the grid in Management Studio, when I run
the select statement that returned the 8 date values, it appears the
1O/18/2OO5 values are of a different font size. This can probably even be
seen as you compare the zero's from the following paste:
08/23/2006
1O/18/2OO5
Any ideas on validation, or handling this situation?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200703/1"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:6f39ac268793b@.uwe...
>I have a strange problem that I need help troubleshooting. I have the
> following statement in a stored procedure:
> SELECT IsNull(NullIf(Convert(varchar(20), Cast(Value AS datetime), 126),
> ''),
> '')
> FROM #TFieldValues TFV
> WHERE TFV.DataType = 'Date'
> When this statement is run, it returns the following error;
> Msg 241, Level 16, State 1, Procedure <the name of my procedure>, Line 142
> Conversion failed when converting datetime from character string.
> The field #TFieldValues.Value is created as varchar(2000).
> So, I run the following statement, and 21 rows are returned, where 8 are
> date
> values and 13 are empty strings:
> SELECT Value FROM #TFieldValues WHERE DataType = 'Date'
> The 8 date values returned are the following:
> 2/23/2006
> 03/21/2006
> 08/23/2006
> 1O/18/2OO5
> 1O/18/2OO5
> 1O/18/2OO5
> 02/26/2007
> 02/26/2007
> I then run the following statement
> SELECT
> TFV.Value
> FROM #TFieldValues TFV
> WHERE
> CASE
> WHEN ISDATE(Value) = 0 THEN 0
> WHEN ISDATE(Value) = 1 THEN 1
> END = 1
> AND TFV.DataType = 'Date'
> Instead of 8 date values being returned, I only return 5, which are the
> following:
> 2/23/2006
> 03/21/2006
> 08/23/2006
> 02/26/2007
> 02/26/2007
> In just looking at the returns in the grid in Management Studio, when I
> run
> the select statement that returned the 8 date values, it appears the
> 1O/18/2OO5 values are of a different font size. This can probably even be
> seen as you compare the zero's from the following paste:
> 08/23/2006
> 1O/18/2OO5
No - it isn't a font issue. These are capital O characters, not zeros.
Switch to a font that uses slashed zeros and you will more clearly see this.
Consider this one of the "advantages" to using the EAV data model - store
anything

Conversion failed when converting datetime from character string

Hi,

I receive an Error Message: Conversion failed when converting datetime from character string when I try to run this.

Can someone point out what I'm doing wrong?

SELECT Principal,

SUM(CASE WHEN Recdate BETWEEN '=@.LYbegin' AND '=@.LYend' THEN

Amount ELSE 0 END) AS LY,

SUM(CASE WHEN Recdate BETWEEN '=@.TYbegin' AND '=@.TYend' THEN

Amount ELSE 0 END) AS TY

FROM dbo.Checks

GROUP BY Principal

If I execute the query with the dates it works fine:

SELECT Principal,

SUM(Case When RecDate BETWEEN '1-1-2005 00:00:00.000' AND '1-30-2005 00:00:00.000' THEN Amount else 0 end)AS LY,

SUM(Case When RecDate BETWEEN '2-1-2005 00:00:00.000' AND '2-28-2005 00:00:00.000' THEN Amount else 0 end)AS TY

FROM Checks

GROUP BY Principal

Thanks,

Terry McCullagh

I suppose you are trying to use a parameterized query statement in the RS query designer. You should use the following commandtext to have query parameters being detected and working:

SELECT Principal,
SUM(CASE WHEN Recdate BETWEEN @.LYbegin AND @.LYend THEN Amount ELSE 0 END) AS LY,
SUM(CASE WHEN Recdate BETWEEN @.TYbegin AND @.TYend THEN Amount ELSE 0 END) AS TY
FROM dbo.Checks
GROUP BY Principal

-- Robert

|||

Robert,

That works great.

Thank you,

Terry McCullagh

Conversion failed when converting character string to smalldatetime data type.

Hello, I have problem with this code.(This programpresents - there is GridView tied to a SQL database that will sort the data selected by a dropdownList at time categories. There are 2 time categories in DropDownList - this day, this week.

Problem: when I choose one categorie in dropDownlist for examle this week and submit data on the server I got this error.

Conversion failed when converting character string to smalldatetime data type.

Here is code:

<%

@.PageLanguage="C#" %>

<!

DOCTYPEhtmlPUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<

scriptrunat="server">

protectedvoid DropDownList1_SelectedIndexChanged(object sender,EventArgs e)

{

string datePatt =@."yyyymmdd";

// Get start and end of day

DateTime StartDate =DateTime.Today;

string @.StartDate1 = StartDate.ToString(datePatt);

string @.EndDate = StartDate.AddDays(1).ToString(datePatt);// Get start and end of weekstring @.startOfWeek = StartDate.AddDays(0 - (int)StartDate.DayOfWeek).ToString(datePatt);string @.startOfNextWeek = StartDate.AddDays(7 - (int)StartDate.DayOfWeek).ToString(datePatt);

switch (DropDownList1.SelectedValue)

{

case"1":

// day

SqlDataSource1.SelectCommand =

"SELECT [RC_USER_ID], [DATE], [TYPE] FROM [T_RC_IN_OUT]" +"WHERE" +"[DATE] >=" +"'@.StartDate1'" +" AND [DATE] < " +"'@.EndDate'";break;case"2"://week

SqlDataSource1.SelectCommand =

"SELECT [RC_USER_ID], [DATE], [TYPE] FROM [T_RC_IN_OUT]" +"WHERE" +"[DATE] >=" +"'@.startOfWeek'" +"AND [DATE] <" +"'@.startOfNextWeek'";break;

}

}

</

script>

<

htmlxmlns="http://www.w3.org/1999/xhtml">

<

headid="Head1"runat="server"><title>Untitled Page</title><styletype="text/css">body {font:1emVerdana;

}

</style>

</

head>

<

body><formid="form1"runat="server"><div>

<asp:DropDownListID="DropDownList1"runat="server"AutoPostBack="True"OnSelectedIndexChanged="DropDownList1_SelectedIndexChanged"Style="z-index: 100; left: 414px; position: absolute; top: 22px"><asp:ListItemSelected="True"Value="1">jeden den</asp:ListItem><asp:ListItemValue="2">jeden tyden</asp:ListItem></asp:DropDownList>

<asp:GridViewID="GridView1"runat="server"Style="z-index: 102; left: 228px; position: absolute;

top: 107px"

DataSourceID="SqlDataSource1"AutoGenerateColumns="True">

</asp:GridView> <br/><br/><asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="Data Source=CR\SQLEXPRESS;

Initial Catalog=MyConn;Integrated Security=True"

ProviderName="System.Data.SqlClient"></asp:SqlDataSource></div></form>

</

body>

</

html>

string datePatt =@."yyyymmdd";

In your 'date' pattern, mm is minutes.

You want MM for month: yyyyMMdd

|||Thank you, for your reply, Icorrected this error. But the problem is still here.|||

I notice you are trying to use parameters but I don't see the code that adds parameters: Parameters.Add(...).

If you pass in the date as a DateTime, you will not have to format it to a string.

|||Thank you, I will try.

Conversion failed when converting character string to smalldatetime data type

I am newbie in asp and sql, and I am using VS & SQL express

when I try to submit I get following error

"Conversion failed when converting character string to smalldatetime data type"

Following is my insert statement

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:oncallConnectionString %>"

SelectCommand="SELECT data.* FROM data" InsertCommand="INSERT INTO data(Apps, Location, Impact, System, Date, Start_Time, End_Time, Duration, Problem, Cause, Solution, Case, Comments) VALUES ('@.DropDownList1','@.DropDownList2','@.DropDownList3','@.TextBox6','@.DropDownCalendar1','@.DropDownCalendar2','@.DropDownCalendar3','@.TextBox1','@.TextBox2','@.TextBox3','@.TextBox4','@.TextBox5','@.TextBox7')">

</asp:SqlDataSource>

These are @.DropDownCalendar1','@.DropDownCalendar2','@.DropDownCalendar3' defined as datetime in database.

I would appriciate if somebody could help.

Thanks

anybody here?|||

The issue here seems to be specific to the authoring in the ASP.Net pages...someone in the ASP.Net forum may be able to help you.

Here's a potentially useful links to start with: http://forums.asp.net/thread/881828.aspx

Conversion failed when converting character string to smalldatetime data type

I am newbie in asp and sql, and I am using VS & SQL express

when I try to submit I get following error

"Conversion failed when converting character string to smalldatetime data type"

Following is my insert statement

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:oncallConnectionString %>"

SelectCommand="SELECT data.* FROM data" InsertCommand="INSERT INTO data(Apps, Location, Impact, System, Date, Start_Time, End_Time, Duration, Problem, Cause, Solution, Case, Comments) VALUES ('@.DropDownList1','@.DropDownList2','@.DropDownList3','@.TextBox6','@.DropDownCalendar1','@.DropDownCalendar2','@.DropDownCalendar3','@.TextBox1','@.TextBox2','@.TextBox3','@.TextBox4','@.TextBox5','@.TextBox7')">

</asp:SqlDataSource>

These are @.DropDownCalendar1','@.DropDownCalendar2','@.DropDownCalendar3' defined as datetime in database.

I would appriciate if somebody could help.

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:oncallConnectionString %>"

SelectCommand="SELECT data.* FROM data" InsertCommand="INSERT INTO data(Apps, Location, Impact, System, Date, Start_Time, End_Time, Duration, Problem, Cause, Solution, Case, Comments) VALUES (@.DropDownList1,@.DropDownList2,@.DropDownList3,@.TextBox6,@.DropDownCalendar1,@.DropDownCalendar2,@.DropDownCalendar3,@.TextBox1,@.TextBox2,@.TextBox3,@.TextBox4,@.TextBox5,@.TextBox7)">

</asp:SqlDataSource>

|||

Now I am getting following error

Must declare the scalar variable "@.DropDownList1".

How do I do it in asp, I found the syntax for declare statement but don't know how put it in asp?

Thanks

|||Go to the design view, right click the sqldatasource, choose properties, then choose the selectcommand. A dialog should open, in there you can add all your parameters for the select.|||

I did that and still getting same error

after changes it looks like this

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:oncallConnectionString %>"

SelectCommand="SELECT data.* FROM data"InsertCommand="INSERT INTO data(Apps, Location, Impact, System, tDate, Start_Time, End_Time, Duration, Problem, Cause, Solution, DW_Case, Comments) VALUES (@.DropDownList1,@.DropDownList2,@.DropDownList3,@.TextBox6,@.DropDownCalendar1,@.DropDownCalendar2,@.DropDownCalendar3,@.TextBox1,@.TextBox2,@.TextBox3,@.TextBox4,@.TextBox5,@.TextBox7)">

<SelectParameters>

<asp:FormParameterFormField="DropDownList1"Name="@.Apps"/>

<asp:FormParameterFormField="DropDownList2"Name="@.Location"/>

<asp:FormParameterFormField="DropDownList3"Name="@.Impact"/>

<asp:FormParameterFormField="TextBox6"Name="@.System"/>

<asp:FormParameterFormField="DropDownCalendar1"Name="@.tdate"/>

<asp:FormParameterFormField="DropDownCalendar2"Name="@.Start_time"/>

<asp:FormParameterFormField="DropDownCalendar3"Name="@.End_time"/>

<asp:FormParameterFormField="TextBox1"Name="@.Duration"/>

<asp:FormParameterFormField="TextBox2"Name="@.Problem"/>

<asp:FormParameterFormField="TextBox3"Name="@.Cause"/>

<asp:FormParameterFormField="TextBox4"Name="@.Solution"/>

<asp:FormParameterFormField="TextBox5"Name="@.DW_case"/>

<asp:FormParameterFormField="TextBox7"Name="@.Comments"/>

</SelectParameters>

</asp:SqlDataSource>

I really appreciate your.

Thanks

|||I also tried doing samething with insert parameter and still no luck|||You have to call them the same thing. @.DropDownList1=@.Apps. Change one to the other and repeat for all parameters. Either change the insert statement, replacing @.DropDownList1 with @.Apps, *OR* change the parameter "@.Apps" to use the name "@.DropDownList1".|||

Motley

I really appreciate your help,

And a BIG Thanks to you.

sqlsql

'Contains' problem

Hello,
I have a problem with a contains clause. I believe I have the full-text
index set correctly. Whenever I put a '-' (dash) character in the query, it
seems to return incorrect results.
Here is a query that works.
SELECT DISTINCT Store_Products.Family As [key] FROM Store_Products WHERE
CONTAINS(Store_Products.*, ' "suit*" ')
Here is one that does not.
SELECT DISTINCT Store_Products.Family As [key] FROM Store_Products WHERE
CONTAINS(Store_Products.*, ' "9-076*" ')
Now, if I change the query to the following, taking out the 'dash'
character, it returns what it should.
SELECT DISTINCT Store_Products.Family As [key] FROM Store_ProductsWHERE
CONTAINS(Store_Products.*, ' "076*" ')
I am not sure where to look to fix this. Thank you for your time.this may be helpful for you:
PRB: Dashes '-' Ignored in Search with SQL Full-Text and MSIDXS
Queries
http://support.microsoft.com/kb/200043/EN-US/|||very helpful. thank you.
"szeying.tan" <szeying.tan@.gmail.com> wrote in message
news:1110237167.585809.173180@.f14g2000cwb.googlegroups.com...
> this may be helpful for you:
> PRB: Dashes '-' Ignored in Search with SQL Full-Text and MSIDXS
> Queries
> http://support.microsoft.com/kb/200043/EN-US/
>

Contains Predicate and Double Quotes

I have been searching for an escape character or a way of escaping
double quotes that are actually in a string that I am using in the
contains predicate.

Here is an example

select *
from table
where contains(field, '"he said "what is wrong", that is what he
said"')

I need the double quotes in the string because they are part of the
text. Of course, Fulltext search raises the error

Server: Msg 7631, Level 15, State 1, Line 1
Syntax error occurred near 'what is wrong", that is what he said'.
Expected '' in search condition '"he said "what is wrong", that is
what he said"'.

If I remove the double quotes, the search does not return the proper
results.

Thanks in advance for the help
Bill"swtwllm" <swtwllm@.alum.iup.edu> wrote in message
news:c38d6cfb.0402201036.6cdf8d87@.posting.google.c om...
> I have been searching for an escape character or a way of escaping
> double quotes that are actually in a string that I am using in the
> contains predicate.
> Here is an example
> select *
> from table
> where contains(field, '"he said "what is wrong", that is what he
> said"')
> I need the double quotes in the string because they are part of the
> text. Of course, Fulltext search raises the error
> Server: Msg 7631, Level 15, State 1, Line 1
> Syntax error occurred near 'what is wrong", that is what he said'.
> Expected '' in search condition '"he said "what is wrong", that is
> what he said"'.
> If I remove the double quotes, the search does not return the proper
> results.
> Thanks in advance for the help
> Bill

It looks like this has been answered in
microsoft.public.sqlserver.fulltext - please don't post to multiple
newsgroups separately.

Simon|||swtwllm (swtwllm@.alum.iup.edu) writes:
> I have been searching for an escape character or a way of escaping
> double quotes that are actually in a string that I am using in the
> contains predicate.
> Here is an example
> select *
> from table
> where contains(field, '"he said "what is wrong", that is what he
> said"')
> I need the double quotes in the string because they are part of the
> text. Of course, Fulltext search raises the error
> Server: Msg 7631, Level 15, State 1, Line 1
> Syntax error occurred near 'what is wrong", that is what he said'.
> Expected '' in search condition '"he said "what is wrong", that is
> what he said"'.
> If I remove the double quotes, the search does not return the proper
> results.

I would expect doubling the quotes would help, but I don't use full-text
myself, so I don't know.

microsoft.public.sqlserver.fulltext may a better place to ask.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Contains Characters or Numbers Method

Hello,

I was wondering if there is any method that I can use to determine if a field (defined as text) has any character fields or is really a number. I want to figure out if a field is truly all numeric, and out of curiosity, was wondering if there was a way in SQL or T-SQL.

Thanks.

look at SQL Server function IsNumeric. It return 1 if it is, otherwise 0. You can use it with CASE WHEN. If it is numeric, get it, otherwise, set to other value such as 0.

|||

You can check whether the following will work for you:

WHEREyourColumnNOTLIKE'%[^0-9]%'

or

WHERE IsNumeric(yourColumn)=1

|||

Hey,

I didn't know there was an IsNumeric method... and I'm using SQL 2000 so regular expression's won't work for me. Sorry I should have mentioned the 2000 part.

Thanks.