Showing posts with label string. Show all posts
Showing posts with label string. Show all posts

Monday, March 26, 2012

Escaping international (unicode) characters in string

Y'all:

I am needing some way, in the SQL Server dialect of SQL, to escape unicode
code points that are embedded within an nvarchar string in a SQL script,
e.g. in Java I can do:

String str = "This is a\u1245 test.";

in Oracle's SQL dialect, it appears that I can accomplish the same thing:

INSERT INTO TEST_TABLE (TEST_COLUMN) VALUES ('This is a\1245 test.");

I've googled and researched through the MSDN, and haven't discovered a
similar construct in SQL Server. I am already aware of the UNISTR()
function, and the NCHAR() function, but those aren't going to work well if
there are more than a few international characters embedded within a
string.

Does anyone have a better suggestion?

Thanks muchly!
GRB

--
---------------------
Greg R. Broderick usenet200705@.blackholio.dyndns.org
A. Top posters.
Q. What is the most annoying thing on Usenet?
---------------------Greg R. Broderick (usenet200705@.blackholio.dyndns.org) writes:

Quote:

Originally Posted by

I am needing some way, in the SQL Server dialect of SQL, to escape unicode
code points that are embedded within an nvarchar string in a SQL script,
e.g. in Java I can do:
>
String str = "This is a\u1245 test.";


SELECT @.str = 'This is a' + nchar(1245) + ' test'

Note here that 1245 is decimal. If you want to use hex code (which you
normally do with Unicode), you would do:

SELECT @.str = 'This is a' + nchar(0x1245) + ' test'

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog <esquel@.sommarskog.sewrote in
news:Xns993F9F38ABD05Yazorman@.127.0.0.1:

Quote:

Originally Posted by

Greg R. Broderick (usenet200705@.blackholio.dyndns.org) writes:

Quote:

Originally Posted by

>I am needing some way, in the SQL Server dialect of SQL, to escape
>unicode code points that are embedded within an nvarchar string in a
>SQL script, e.g. in Java I can do:
>>
>String str = "This is a\u1245 test.";


>
SELECT @.str = 'This is a' + nchar(1245) + ' test'
>
Note here that 1245 is decimal. If you want to use hex code (which you
normally do with Unicode), you would do:
>
SELECT @.str = 'This is a' + nchar(0x1245) + ' test'


When there are more than one or two non-US-ASCII characters in the string,
this quickly becomes impractically unwieldy, thus my comment in my original
posting:

-- quote --

I am already aware of the UNISTR() function, and the NCHAR() function, but
those aren't going to work well if there are more than a few international
characters embedded within a string.

-- quote --

Thanks anyway, though. :-)

--
---------------------
Greg R. Broderick usenet200705@.blackholio.dyndns.org
A. Top posters.
Q. What is the most annoying thing on Usenet?
---------------------|||Greg R. Broderick (usenet200705@.blackholio.dyndns.org) writes:

Quote:

Originally Posted by

When there are more than one or two non-US-ASCII characters in the
string, this quickly becomes impractically unwieldy, thus my comment in
my original posting:


If the are in sequence, you could do:

convert(nvarchar, 0x34123512...)

although this is certainly not too funny as you have twitch the bytes
around.

Another solution to use something like Microsoft Visual Keyboard, and
simply put the actual characters there.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql

escaping a string

hi guys

please help me. i've never user stored procs before but here is my problem.

this is only what i am allowed to display but it shows my problem

declare @.suite varchar(10),@.company varchar(1)
set @.suite = 'brutus'

set @.company = 'A'
exec ('insert into tot (SuiteName,Company) values ('+@.suite+','+@.company+')')

when i exec the query it says that "brutus" is an invalid column name. i know that i need to insert a extra ' but i don't know how or what is the escape character. please help me.

Hi,

this should be accomplishedby this one here:

exec ('insert into tot (SuiteName,Company) values ('''+@.suite+''','''+@.company+''')')

But I would rather prefer not using dynamic sql in this case:

insert into tot (SuiteName,Company) values (@.suite,@.company)

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

thanks that helps but when i do this

1. exec ('alter table tot add '+@.UnitT+' varchar(3) DEFAULT '' WITH VALUES')

2. alter table tot add @.UnitT varchar(3) DEFAULT ' ' WITH VALUES

not one work. in 1 i get the same problem and in 2 it tells met there is a problem with syntax before @.unit

|||

No, therefore you would need dynamic SQL, that will not function with the second opion I posted.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

OK THANKS

i used your first solution with the ''' multiple quotes and it works perfect. thanks

Thursday, March 22, 2012

escape single qoute

Is there a way to escape single quotes ' in a sql statement that uses a sql data source that is automated? I know you can do it with a string manip and replacing them with double single quotes. I am just looking for a simple way.

Thanks

Adam

If you are using parameters, you don't have to worry about single quotes in strings because SQL Server preserves the input data as it is.

If you aren't using parameters, you'll have to use two single quotes.

|||

You need to use two single quotes.

Escape a SQL string programmatically?

I need to construct a SQL statement programmatically and must escape strings included as values to follow SQL rules. For example:

command.CommandText = "INSERT INTO Table (strColumn) VALUES('" +
EscapeSQLChars(badChars) + "')";
command.ExecuteNonQuery();

Is there a built-in .Net command that does what I want EscapeSQLChars to do?

Use paramitrimized queries. Then you never have to worry about format's or

SQL Injection.
It's olso better for the preformance, because you don't need

to have to concatenate a string for example:


string query

= "SELECT * FROM Table1 WHERE ID = " + txtId.Text + " AND Name = \"" +

"txtName.Text + "\"";

No escape characters needed, you doesn't

have to think about using a " or not etc.

Parameters are like

placeholders, you use them in Stored Procedures as well.

A little example:


// TODO: Set date

variable.
DateTime date = DateTime.Now;

// Set query and parameters.
const string query = "SELECT * FROM Table1

WHERE MyDate = @.MyDate";
SqlParameter pMyDate = new SqlParameter("@.MyDate",

SqlDbType.DateTime);
pMyDate.Value = date;

// Create connection and open it.
SqlConnection dbConn = new

SqlConnection("ConnectingString");
dbConn.Open();

try
{
using(SqlCommand dbCommand = new SqlCommand(query,

dbConn))
{
// Add paramter to Command.
dbCommand.Parameters.Add(

pMyDate );

// Execute the query and get results.
SqlDataReader reader =

dbCommand.ExecuteReader();

try
{
// Walkthrough

results.
while(reader.Read())
{
// TODO: Do something with

the data.
}
}
finally
{
// Close

reader.
reader.Close();
}
}
}
finally
{
// Close

connection.
dbConn.Close();
}

Escape a SQL string programmatically?

I need to construct a SQL statement programmatically and must escape strings included as values to follow SQL rules. For example:

command.CommandText = "INSERT INTO Table (strColumn) VALUES('" +
EscapeSQLChars(badChars) + "')";
command.ExecuteNonQuery();

Is there a built-in .Net command that does what I want EscapeSQLChars to do?

Use paramitrimized queries. Then you never have to worry about format's or

SQL Injection.
It's olso better for the preformance, because you don't need

to have to concatenate a string for example:


string query

= "SELECT * FROM Table1 WHERE ID = " + txtId.Text + " AND Name = \"" +

"txtName.Text + "\"";

No escape characters needed, you doesn't

have to think about using a " or not etc.

Parameters are like

placeholders, you use them in Stored Procedures as well.

A little example:


// TODO: Set date

variable.
DateTime date = DateTime.Now;

// Set query and parameters.
const string query = "SELECT * FROM Table1

WHERE MyDate = @.MyDate";
SqlParameter pMyDate = new SqlParameter("@.MyDate",

SqlDbType.DateTime);
pMyDate.Value = date;

// Create connection and open it.
SqlConnection dbConn = new

SqlConnection("ConnectingString");
dbConn.Open();

try
{
using(SqlCommand dbCommand = new SqlCommand(query,

dbConn))
{
// Add paramter to Command.
dbCommand.Parameters.Add(

pMyDate );

// Execute the query and get results.
SqlDataReader reader =

dbCommand.ExecuteReader();

try
{
// Walkthrough

results.
while(reader.Read())
{
// TODO: Do something with

the data.
}
}
finally
{
// Close

reader.
reader.Close();
}
}
}
finally
{
// Close

connection.
dbConn.Close();
}

Sunday, February 26, 2012

error:the string was not recognized as a valid DateTime.

hi all, i'm trying to insert the time/date a button was clicked on a gridview and it generates an error:the string was not recognized as a valid DateTime.There is an unknown word starting at index 0 i have changed the culture to en-US but it still doesn't work. i actually created the date/time column after some data had been entered into the table so the column allows nulls. this is my code:

InsertCommand="INSERT INTO test101(Surname,Names,Registration,Login Time)VALUES (@.Surname, @.Names, @.Registration,@.Login_Time)"

<Insert Parameters><asp:Parameter DefaultValue= DateTime.Now Type=DateTime Name="Login_Time" /></Insert Parameters
any suggestions?

When do you recieve that error? You mentioned button on a GridView, so I suppose that you're trying to update some values and, if that's the case, use this code:

protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
e.NewValues["Login_Time"] = DateTime.Now;
}

If, on the other hand, you're trying to insert some values in database, use this code (this is example for inserting values with DetailsView ):

protected void DetailsView1_ItemInserting(object sender, DetailsViewInsertEventArgs e)
{
e.Values["Login_Time"] = DateTime.Now;
}

|||

sorry, this message was supposed to be:

hi all, i'm trying to insert the time/date at the time a user clicks the submit button but it generates an error:the string was not recognized as a valid DateTime.There is an unknown word starting at index 0 i have changed the culture to en-US but it still doesn't work. this is my code:

InsertCommand="INSERT INTO test101(Surname,Names,Registration,Login Time)VALUES (@.Surname, @.Names, @.Registration,@.Login_Time)"

<Insert Parameters><asp:Parameter DefaultValue= DateTime.Now Type=DateTime Name="Login_Time" /></Insert Parameters
any suggestions?

|||

Where is that submit button (in DetailsView or in FormView)?

|||

it's on a login page. so i want the time the user logged on to be saved on a database

|||

Can you post your whole code?

|||

One way to achieve your goal is to change your InsertParameter into this:

<Insert Parameters><asp:Parameter Type="DateTime" Name="Login_Time" /></Insert Parameters>

and put this in Page_Load event:

SqlDataSource1.InsertParameters["Login_Time"].DefaultValue = DateTime.Now.ToString();

|||<asp:ButtonID="login1"runat="server"Text="Login"

Height="31px"OnClick="login1_Click"Width="65px"BackColor="#E0E0E0"Font-Size="Medium"/>

<asp:ButtonID="Reset1"runat="server"BackColor="#E0E0E0"Font-Size="Medium"

Height="32px"OnClick="Reset1_Click"Text="Reset"Width="63px"/>

<br/>

<br/>

<asp:SqlDataSourceID="SqlDataSource1"runat="server"

ConnectionString="<%$ ConnectionStrings:engineeringConnectionString %>"

InsertCommand="INSERT INTO test101(Surname,Names,Registration,[Course code],[Login Time]) VALUES (@.Surname,@.Names,@.Registration,@.Course_code,@.Login_Time)">

<InsertParameters>

<asp:ControlParameterControlID="TextBox1"DefaultValue="Textbox1.Text"Name="Surname"

PropertyName="Text"/>

<asp:ControlParameterControlID="TextBox2"DefaultValue="TextBox2.Text"Name="Names"

PropertyName="Text"/>

<asp:ControlParameterControlID="TextBox3"DefaultValue="TextBox3.Text"Name="Registration"

PropertyName="Text"/>

<asp:ControlParameterControlID="TextBox4"DefaultValue="TextBox4.Text"Name="Course_code"

PropertyName="Text"/>

<asp:Parameter Name="Login_Time" type=DateTime/>

</InsertParameters>

</asp:SqlDataSource>

<asp:GridViewID="GridView1"

runat="server"AutoGenerateColumns="False"AutoGenerateDeleteButton="True"DataKeyNames="ID"

AutoGenerateEditButton="True"AllowSorting="True"BackColor="LightGoldenrodYellow"BorderColor="Tan"BorderWidth="1px"CellPadding="2"ForeColor="Black"GridLines="None"PageSize="20"

Height="374px"EmptyDataText="null"DataSourceID=SqlDataSource1Visible="False"AllowPaging="True">

<Columns>

<asp:BoundFieldDataField="ID"HeaderText="ID"InsertVisible="False"ReadOnly="True"

SortExpression="ID"/>

<asp:BoundFieldDataField="Surname"HeaderText="Surname"SortExpression="Surname"/>

<asp:BoundFieldDataField="Names"HeaderText="Names"SortExpression="Names"/>

<asp:BoundFieldDataField="Registration"HeaderText="Registration"SortExpression="Registration"/>

<asp:BoundFieldDataField="Course code"HeaderText="Course code"SortExpression="Course code"/>

<asp:BoundFieldDataField="Grade"HeaderText="Grade"SortExpression="Grade"/>

<asp:BoundFieldDataField="login Time"HeaderText="Login Time"SortExpression="login Time"/>

</Columns>

</asp:GridView>

and this is the code behind:

protectedvoid Page_Load(object sender,EventArgs e)

{

SqlConnection conn =newSqlConnection("Data Source=(local);Initial Catalog=engineering; Integrated Security=True");

conn.Open();

GridView1.DataBind();

conn.Close();

}

publicvoid login1_Click(object sender,EventArgs e)

{

SqlDataSource1.Insert();

if (TextBox1.Text !="" && TextBox2.Text !="" && TextBox3.Text !="" && TextBox4.Text !="")

{

Response.Redirect("test1.aspx");

}

|||

Just add SqlDataSource1.InsertParameters["Login_Time"].DefaultValue = DateTime.Now.ToString(); in login1_Click event like this:

public void login1_Click(object sender, EventArgs e)
{
SqlDataSource1.InsertParameters["Login_Time"].DefaultValue = DateTime.Now.ToString();
SqlDataSource1.Insert();
if (TextBox1.Text != "" && TextBox2.Text != "" && TextBox3.Text != "" && TextBox4.Text !="")
{
Response.Redirect("test1.aspx");
}

And one question: Why are you opening connection in Page_Load event?:

SqlConnection conn = new SqlConnection("Data Source=(local);Initial Catalog=engineering; Integrated Security=True");
conn.Open();
GridView1.DataBind();
conn.Close();

If you're using SqlDataSource this is unnecessarily.

|||

THANK YOU SO MUCH!!!!!

Friday, February 24, 2012

error:25 - connection string is not valid

I have installed SQL Server 2005 Developer edition. When I open SQL Server Management Studio, and try to connect to the database engine, I enter these credentials:

Server name: LT2000\MSSQLSERVER
Login: sa
Password: my password

When I try to connect I receive the following error:
An error has occured while trying to establish a connection to the server. When connecting to SQL server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.(provider: sql network interfaces, error:25 - connection string is not valid)(Microsoft SQL Server, Error:87)

What can I do?

Is that an error message you are seeing via a ASP.NET page trying to connect?|||

Peter Smith:

I have installed SQL Server 2005 Developer edition. When I open SQL Server Management Studio, and try to connect to the database engine, I enter these credentials:

What can I do?

I don't understand what you mean by connecting to the database engine because I have not had the need for that in 8years plus using SQL Server, but your error means you are trying to connect with only named pipes you need to use both named pipes and TCP/IP. Then enable remote connection that is covered by Microsoft below. Hope this helps.

http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx

http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277

|||

The surface area configuration tool used to configure SQL Serve 2005 check the doc below remote connection.

http://msdn2.microsoft.com/en-us/library/ms178763.aspx

|||

Actually the error has nothing to do with pipes. If you look in the error message you find the message "error:25 - connection string is not valid", that is the error. If you are sure those are that is your valid credential you should use a connection string just like this one: "Data Source=LT2000\MSSQLSERVER;Initial Catalog=Northwind;User ID=sa;Password=mypassword"; Initial catalog is of course the name of your database. If that doesn't work, then use a data source wizard and enter your credentials, then test the connection and see if it works. If it doesn't you probably have wrong credentials. You can try also using the Windows Authentication('cause you set your sql server to user mixed authentication mode), in that way your connection string will be: "Data Source=LT2000\MSSQLSERVER;Initial Catalog=Northwind;Integrated Security=true".

Tell us if that works for you.

|||

Hi,

When I use MS SQL Management Studio and I want to connect, I get to see a dropdown...in this dropdown I can select "database engine".
I then fill in the type of authentication (sql server in this case), and I can log on.

I dont have the problem anymore, but that might be that out of frustration I just reinstalled SQL Server...it works fine now :)

Wednesday, February 15, 2012

Error: Subreport could not be shown

I have an infuriating problem which I can't seem to overcome with my first
report containing a sub-report. 1 simple parameter a GUID string is passed
from the parent report to the subreport and all I seem to get in return is
the message 'Error: Subreport could not be shown' with no indication as to
what the problem is or might be. If i view the subreport on its own and
manually enter the parameter the sub report displays fine and as expected
but when the parameter is passed via the parent form it won't play ball.
Thanks in advance for any help - u will be doing me a big favour if you can
help me overcome this.
SimonProbably a type-problem. Are you sure you "send" a string and that the
receiving parameter is also a string?
"Simon Dingley" wrote:
> I have an infuriating problem which I can't seem to overcome with my first
> report containing a sub-report. 1 simple parameter a GUID string is passed
> from the parent report to the subreport and all I seem to get in return is
> the message 'Error: Subreport could not be shown' with no indication as to
> what the problem is or might be. If i view the subreport on its own and
> manually enter the parameter the sub report displays fine and as expected
> but when the parameter is passed via the parent form it won't play ball.
> Thanks in advance for any help - u will be doing me a big favour if you can
> help me overcome this.
> Simon
>
>|||Hi and thanks for the reply,
The parameter passed is a 'field' e.g. =Fields!qst_id.Value which is
actually a GUID from the database. As far as I can see there is no way for
me to specify in the sub-report properties what data type the parameter is.
However, in the sub report itself the parameter data type is defined as
being a string ?
Does this shed any light on things ?
Thanks, Simon
"Antoon" <Antoon@.discussions.microsoft.com> wrote in message
news:4CD7BC66-C0E9-4E79-957E-0C8040C1EA4C@.microsoft.com...
> Probably a type-problem. Are you sure you "send" a string and that the
> receiving parameter is also a string?
> "Simon Dingley" wrote:
> > I have an infuriating problem which I can't seem to overcome with my
first
> > report containing a sub-report. 1 simple parameter a GUID string is
passed
> > from the parent report to the subreport and all I seem to get in return
is
> > the message 'Error: Subreport could not be shown' with no indication as
to
> > what the problem is or might be. If i view the subreport on its own and
> > manually enter the parameter the sub report displays fine and as
expected
> > but when the parameter is passed via the parent form it won't play ball.
> >
> > Thanks in advance for any help - u will be doing me a big favour if you
can
> > help me overcome this.
> >
> > Simon
> >
> >
> >|||Try passing Fields!qst_id.Value.ToString() to the subreport
(I do presume the subreport is within a matrix, tabele, list,... and not "on
its own". In the last case you should use an agregate function fe
first(Fields!qst_id.Value).ToString() )
"Simon Dingley" wrote:
> Hi and thanks for the reply,
> The parameter passed is a 'field' e.g. =Fields!qst_id.Value which is
> actually a GUID from the database. As far as I can see there is no way for
> me to specify in the sub-report properties what data type the parameter is.
> However, in the sub report itself the parameter data type is defined as
> being a string ?
> Does this shed any light on things ?
> Thanks, Simon
> "Antoon" <Antoon@.discussions.microsoft.com> wrote in message
> news:4CD7BC66-C0E9-4E79-957E-0C8040C1EA4C@.microsoft.com...
> > Probably a type-problem. Are you sure you "send" a string and that the
> > receiving parameter is also a string?
> >
> > "Simon Dingley" wrote:
> >
> > > I have an infuriating problem which I can't seem to overcome with my
> first
> > > report containing a sub-report. 1 simple parameter a GUID string is
> passed
> > > from the parent report to the subreport and all I seem to get in return
> is
> > > the message 'Error: Subreport could not be shown' with no indication as
> to
> > > what the problem is or might be. If i view the subreport on its own and
> > > manually enter the parameter the sub report displays fine and as
> expected
> > > but when the parameter is passed via the parent form it won't play ball.
> > >
> > > Thanks in advance for any help - u will be doing me a big favour if you
> can
> > > help me overcome this.
> > >
> > > Simon
> > >
> > >
> > >
>
>|||Antoon - you're a star. That worked a treat.
Many thanks for your help.
Simon
"Antoon" <Antoon@.discussions.microsoft.com> wrote in message
news:283D9BE2-1B26-4066-A3AE-A477868DEB96@.microsoft.com...
> Try passing Fields!qst_id.Value.ToString() to the subreport
> (I do presume the subreport is within a matrix, tabele, list,... and not
"on
> its own". In the last case you should use an agregate function fe
> first(Fields!qst_id.Value).ToString() )
>|||One more quick question if I can. This works great for me when developing
and previewing in Visual Studio .Net but when I deploy the report to the
reporting server I get the subreport could be shown error again ? Do you
know why this is ?
Simon
"Antoon" <Antoon@.discussions.microsoft.com> wrote in message
news:283D9BE2-1B26-4066-A3AE-A477868DEB96@.microsoft.com...
> Try passing Fields!qst_id.Value.ToString() to the subreport
> (I do presume the subreport is within a matrix, tabele, list,... and not
"on
> its own". In the last case you should use an agregate function fe
> first(Fields!qst_id.Value).ToString() )|||Not too worry - after viewing the subreport as a standalone report i
discovered there was a problem connecting through the shared datasource.
Thanks again.
Simon
>One more quick question if I can. This works great for me when developing
>and previewing in Visual Studio .Net but when I deploy the report to the
>reporting server I get the subreport could be shown error again ? Do you
>know why this is ?
>Simon

ERROR: String or binary data would be truncated.

ERROR: String or binary data would be truncated.

The error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e57'

[Microsoft][ODBC SQL Server Driver][SQL Server]String or binary data would be truncated.

/maxwebportal/inc_footer.asp, line 121

Thanks for ANY assistance. I'm pretty much new at this. <CHRIS>

>>>Code Below...Scroll Down for Line 121:

if exceer=okoame then

if CurPageInfoChk = "" then
strOnlineLocation = "Unknown page"
else
if CurPageType = "forums" and trim(strRqForumID) <> "" then
'## Forum_SQL - load the user list
strSql = "SELECT " & strTablePrefix & "FORUM.F_PRIVATEFORUMS FROM " & strTablePrefix & "FORUM WHERE FORUM_ID = " & strRqForumID & " AND F_PRIVATEFORUMS <> 0"

set rsPrf = my_Conn.Execute(strSql)

if not (rsPrf.BOF and rsPrf.EOF) then
isPrivateForum = 1
else
isPrivateForum = 0
end if

rsPrf.Close
set rsPrf = nothing

end if
if not CurPageInfo () = "" then
if isPrivateForum = 1 then
strOnlineLocation = "Private Page"
else
strOnlineLocation = CurPageInfo ()
end if
else
strOnlineLocation = "Hidden page"
end if
end if

strOnlineUser = OnlineSQLencode(strOnlineUser)
strOnlineLocation = OnlineSQLencode(strOnlineLocation)
strOnlineTimedOut = strOnlineCheckInTime - 1500 'time out the user after 25 minutes
strSql = "SELECT " & strTablePrefix & "ONLINE.UserID, " & strTablePrefix & "ONLINE.UserIP, " & strTablePrefix & "ONLINE.LastChecked"
strSql = strSql & " FROM " & strTablePrefix & "ONLINE "
strSql = strSql & " WHERE " & strTablePrefix & "ONLINE.UserIP='" & strOnlineUserIP & "' AND " & strTablePrefix & "ONLINE.UserID='" & strOnlineUser & "'"
set rsWho = my_Conn.Execute (strSql)

if rsWho.eof or rsWho.bof then
' THEY ARE A NEW USER SO INSERT THERE USERNAME
on error resume next
Set objRS2 = Server.CreateObject("ADODB.Recordset")
strSQL = "INSERT INTO " & strTablePrefix & "ONLINE (UserID,UserIP,DateCreated,CheckedIn,LastChecked,M
_BROWSE) VALUES ('"
strSql = strSQL & strOnlineUser & "','" & strOnlineUserIP & "','" & strOnlineDate & "','" & strOnlineCheckInTime & "','" & strOnlineCheckInTime & "','" & strOnlineLocation & "')"
my_Conn.Execute (strSql)
if err.number <> 0 then response.write err.number & "|" & err.description
else
' THEY ARE A ACTIVE USER
strSql = "SELECT " & strTablePrefix & "ONLINE.UserID, " & strTablePrefix & "ONLINE.UserIP, " & strTablePrefix & "ONLINE.LastChecked"
strSql = strSql & " FROM " & strTablePrefix & "ONLINE "
strSql = strSql & " WHERE " & strTablePrefix & "ONLINE.UserID='" & strOnlineUser & "' AND " & strTablePrefix & "ONLINE.UserIP = '" & strOnlineUserIP & "'"
set rsLastChecked = my_Conn.Execute (strSql)

' LETS UPDATE THE TABLE SO IT SHOWS THERE LAST ACTIVE VISIT
strSql = "UPDATE " & strTablePrefix & "ONLINE SET M_BROWSE='" & strOnlineLocation & "' , LastChecked='" & strOnlineCheckInTime & "' WHERE UserID='" & strOnlineUser & "' AND " & strTablePrefix & "ONLINE.UserIP='" & strOnlineUserIP & "'"
my_Conn.Execute (strSql) << Line 121<<<<<<<basically,... the data you are trying to update using this...

' LETS UPDATE THE TABLE SO IT SHOWS THERE LAST ACTIVE VISIT
strSql = "UPDATE " & strTablePrefix & "ONLINE SET M_BROWSE='" & strOnlineLocation & "' , LastChecked='" & strOnlineCheckInTime & "' WHERE UserID='" & strOnlineUser & "' AND " & strTablePrefix & "ONLINE.UserIP='" & strOnlineUserIP & "'"

is too long for the field you are trying to put it into.

eg you field is a varchar(10) and you are tyring to put "Help me get this right" in there.... and it won't fit...

So, check your field sizes, check the data you are inserting and soon you will discover what one(s) is wrong.|||Originally posted by rokslide
basically,... the data you are trying to update using this...

' LETS UPDATE THE TABLE SO IT SHOWS THERE LAST ACTIVE VISIT
strSql = "UPDATE " & strTablePrefix & "ONLINE SET M_BROWSE='" & strOnlineLocation & "' , LastChecked='" & strOnlineCheckInTime & "' WHERE UserID='" & strOnlineUser & "' AND " & strTablePrefix & "ONLINE.UserIP='" & strOnlineUserIP & "'"

is too long for the field you are trying to put it into.

eg you field is a varchar(10) and you are tyring to put "Help me get this right" in there.... and it won't fit...

So, check your field sizes, check the data you are inserting and soon you will discover what one(s) is wrong.

Generally this mssg comes when the query inserts a record and any one of the column size if it exceeds the input parameter value then this kind fof error happens.
Ex - tableA is having a coulmn1 varchar(20) if the query is entering a value which is 21 varchar then this type of error may be displayed.

ERROR: String or binary data would be truncated.

The error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e57'

[Microsoft][ODBC SQL Server Driver][SQL Server]String or binary data would be truncated.

/maxwebportal/inc_footer.asp, line 121

Thanks for ANY assistance. I'm pretty much new at this. <CHRIS>

>>>Code Below...Scroll Down for Line 121:

if exceer=okoame then

if CurPageInfoChk = "" then
strOnlineLocation = "Unknown page"
else
if CurPageType = "forums" and trim(strRqForumID) <> "" then
'## Forum_SQL - load the user list
strSql = "SELECT " & strTablePrefix & "FORUM.F_PRIVATEFORUMS FROM " & strTablePrefix & "FORUM WHERE FORUM_ID = " & strRqForumID & " AND F_PRIVATEFORUMS <> 0"

set rsPrf = my_Conn.Execute(strSql)

if not (rsPrf.BOF and rsPrf.EOF) then
isPrivateForum = 1
else
isPrivateForum = 0
end if

rsPrf.Close
set rsPrf = nothing

end if
if not CurPageInfo () = "" then
if isPrivateForum = 1 then
strOnlineLocation = "Private Page"
else
strOnlineLocation = CurPageInfo ()
end if
else
strOnlineLocation = "Hidden page"
end if
end if

strOnlineUser = OnlineSQLencode(strOnlineUser)
strOnlineLocation = OnlineSQLencode(strOnlineLocation)
strOnlineTimedOut = strOnlineCheckInTime - 1500 'time out the user after 25 minutes
strSql = "SELECT " & strTablePrefix & "ONLINE.UserID, " & strTablePrefix & "ONLINE.UserIP, " & strTablePrefix & "ONLINE.LastChecked"
strSql = strSql & " FROM " & strTablePrefix & "ONLINE "
strSql = strSql & " WHERE " & strTablePrefix & "ONLINE.UserIP='" & strOnlineUserIP & "' AND " & strTablePrefix & "ONLINE.UserID='" & strOnlineUser & "'"
set rsWho = my_Conn.Execute (strSql)

if rsWho.eof or rsWho.bof then
' THEY ARE A NEW USER SO INSERT THERE USERNAME
on error resume next
Set objRS2 = Server.CreateObject("ADODB.Recordset")
strSQL = "INSERT INTO " & strTablePrefix & "ONLINE (UserID,UserIP,DateCreated,CheckedIn,LastChecked,M _BROWSE) VALUES ('"
strSql = strSQL & strOnlineUser & "','" & strOnlineUserIP & "','" & strOnlineDate & "','" & strOnlineCheckInTime & "','" & strOnlineCheckInTime & "','" & strOnlineLocation & "')"
my_Conn.Execute (strSql)
if err.number <> 0 then response.write err.number & "|" & err.description
else
' THEY ARE A ACTIVE USER
strSql = "SELECT " & strTablePrefix & "ONLINE.UserID, " & strTablePrefix & "ONLINE.UserIP, " & strTablePrefix & "ONLINE.LastChecked"
strSql = strSql & " FROM " & strTablePrefix & "ONLINE "
strSql = strSql & " WHERE " & strTablePrefix & "ONLINE.UserID='" & strOnlineUser & "' AND " & strTablePrefix & "ONLINE.UserIP = '" & strOnlineUserIP & "'"
set rsLastChecked = my_Conn.Execute (strSql)

' LETS UPDATE THE TABLE SO IT SHOWS THERE LAST ACTIVE VISIT
strSql = "UPDATE " & strTablePrefix & "ONLINE SET M_BROWSE='" & strOnlineLocation & "' , LastChecked='" & strOnlineCheckInTime & "' WHERE UserID='" & strOnlineUser & "' AND " & strTablePrefix & "ONLINE.UserIP='" & strOnlineUserIP & "'"
my_Conn.Execute (strSql) << Line 121<<<<<<<This error occurs when the data you are inserting into your column exceeds the data width specified for the column. Check your DDL and insert statement to find out the particular column.

error: string must be exactly one character long

hi all, i'm retreiving user input using textboxes and saving to a gridview. i'm getting this error and i dont know whats causing it.

<asp:SqlDataSourceID="SqlDataSource1"runat="server"

InsertCommand="INSERT INTO test101(Surname,Names,Regno)VALUES (@.Surname, @.Names, @.Regno)"

ConnectionString="<%$ ConnectionStrings:engineeringConnectionString %>"ProviderName=System.Data.SqlClient

ConflictDetection="CompareAllValues">

<InsertParameters>

<asp:ControlParameterControlID="TextBox1"DefaultValue="TextBox1.Text"Name="Surname"

PropertyName="Text"Size="50"Type=Char/>

<asp:ControlParameterControlID="TextBox2"DefaultValue="TextBox2.Text"Name="Names"

PropertyName="Text"Size="50"Type=Char/>

<asp:ParameterDefaultValue="TextBox3.Text"Name="Regno"/>

</InsertParameters>

</asp:SqlDataSource>

<asp:GridViewID="GridView1"

runat="server"AutoGenerateColumns="False"AutoGenerateDeleteButton="True"DataKeyNames="ID"

AutoGenerateEditButton="True"AllowSorting="True"BackColor="LightGoldenrodYellow"

BorderColor="Tan"BorderWidth="1px"CellPadding="2"ForeColor="Black"GridLines="None"PageSize="20"

Height="374px"EmptyDataText="null"DataSourceID=SqlDataSource1Visible="False">

<Columns>

<asp:BoundFieldDataField="ID"HeaderText="ID"InsertVisible="False"ReadOnly="True"

SortExpression="ID"/>

<asp:BoundFieldDataField="Surname"HeaderText="Surname"SortExpression="Surname"/>

<asp:BoundFieldDataField="Names"HeaderText="Names"SortExpression="Names"/>

<asp:BoundFieldDataField="Registration"HeaderText="Registration"SortExpression="Registration"/>

<asp:BoundFieldDataField="Grade"HeaderText="Grade"SortExpression="Grade"/>

</Columns>

</asp:GridView>

and the code behind is:

protectedvoid Page_Load(object sender,EventArgs e)

{

SqlConnection conn =newSqlConnection("Data Source=(local);Initial Catalog=engineering; Integrated Security=True");

conn.Open();

GridView1.DataBind();

conn.Close();

}

publicvoid login1_Click(object sender,EventArgs e)

{

SqlDataSource1.Insert();

Response.Write("you have successfully being added to the database");

can anyone help?!!!

Check out this link:http://vbcity.com/forums/topic.asp?tid=148073

Good luck.