inserting a new record

I want to insert a new record into my db in asp.net. Im programming in vb and using an sql server database. i know this is a begginers question but that's exactly what i am. Thanks in advanceHave you had a look at the ASP.net starter kits?

Inserting 1:M relationship data via One Stored Procedure

Hi,

Uses: SQL Server 2000, ASP.NET 1.1;

I've the following tables which has a 1:M relationship within them:

Contact(ContactID, LastName, FirstName, Address, Email, Fax)
ContactTelephone(ContactID, TelephoneNos)

I have a webform made with asp.net, and have given the user to add maximum of 3 telephone nos for a contact (Telephone Nos can be either Mobile or Land phones). So I've used Textbox's in the following way for the appropriate fields:

LastName,
FirstName,
Address,
Fax,
Email,
MobileNo,
PhoneNo1,
PhoneNo2,
PhoneNo3.

Once the submit button is pressed, I need to take all of this values and insert them in the tables via a Single Stored Procedure. I need to know could this be done and How?

Eagerly awaiting a response.

Thanks,

The best reference for this kind of thing when you truly have a 1:M relationship is Erland's web page: http://www.sommarskog.se/arrays-in-sql.html

But if you have a max of 3, then just write the proc with 3 parameters (something like):

create procedure contact$insert
(
@.LastName,
...
@.MobileNo,
@.PhoneNo1,
@.PhoneNo2,
@.PhoneNo3
)
--add your own error handling of course or add SET XACT_ABORT ON that
--will stop the tran on any error

begin tran

insert into contact (lastName, ..., MobileNo) --note, assuming contactId is an identity
values (@.lastName, ..., @.MobileNo)

declare @.newContactId int
set @.newContactId = scope_identity()

insert into contactTelephone
select @.newContactId, @.phoneNo1
where @.phoneNo1 is not null
union all
select @.newContactId, @.phoneNo2
where @.phoneNo2 is not null
union all
select @.newContactId, @.phoneNo3
where @.phoneNo3 is not null

commit tran

|||

Hi Louis,

Thanks for the Response, this cleared my mind and the problem. Thank you again!

sql

insert/update timestamp in a SQL server 2000 db programatically

Hi,

How can i store the record insert/update timestamp in a SQL server 2000 db programacally. ? what are the date/time functions in ASP.NET 2.0 ? I know that this can be done by setting the default valut to getdate() function in SQL, but any other way on ASP page or code-behind page ?

Thanks,

Alex

You can use

string

s =DateTime.Now.ToString("dd/MMM/YYYY");

and then put it into the relevant parameter for your SqlCommand

|||Yes, that is correct if i assume i put that line of code in the code-behind page. But what will the syntax be if i need to use the same in the aspx page

My insert statement is as follows:

InsertCommand="INSERT INTO [StudentRegistration] ([RegDate], [FirstName], [SecondName], [FamilyName], [Photo], [CourseId], [MorningClass], [AfternoonClass], [Block], [Street], [HouseAptNo], [Area], [POBox], [PostalCode],, [HomePhone], [Mobile], [WorkPhone], [BirthDate], [Gender], [Nationality], [MaritalStatus], [CivilIdNo], [ExpiryDate], [ContactName], [ContactTel], [ContactMob], [ContactEmail], [MedicalCond], [CompleteHS], [CompYear], [ExpDate], [WhichSchool], [SchoolType], [Other], [QualTitle1], [QualInst1], [QualComp1], [QualTitle2], [QualInst2], [QualComp2], [QualTitle3], [QualInst3], [QualComp3], [QualTitle4], [QualInst4], [QualComp4], [Notes], [DateAdded], [AddedByFK]) VALUES (@.RegDate, @.FirstName, @.SecondName, @.FamilyName, @.Photo, @.CourseId, @.MorningClass, @.AfternoonClass, @.Block, @.Street, @.HouseAptNo, @.Area, @.POBox, @.PostalCode, @.Email, @.HomePhone, @.Mobile, @.WorkPhone, @.BirthDate, @.Gender, @.Nationality, @.MaritalStatus, @.CivilIdNo, @.ExpiryDate, @.ContactName, @.ContactTel, @.ContactMob, @.ContactEmail, @.MedicalCond, @.CompleteHS, @.CompYear, @.ExpDate, @.WhichSchool, @.SchoolType, @.Other, @.QualTitle1, @.QualInst1, @.QualComp1, @.QualTitle2, @.QualInst2, @.QualComp2, @.QualTitle3, @.QualInst3, @.QualComp3, @.QualTitle4, @.QualInst4, @.QualComp4, @.Notes, @.DateAdded , @.AddedByFK)"

<asp:ParameterName="DateAdded"Type="DateTime"/>

in this code, how do i retrieve the current date/time from SQL server while inserting a new record ?

i want to set the DateAdded field to default to the current date/time

Thanks,

Alex

|||I would personally use a stored procedure as then you can pass back the SQL datetime as the return value or an output parametersql

Insert Word document into image field

How can I insert a Word document into a image field in
SQL server 7 or 2000 via ASP?
Thanks
MoroBCheck out the following links:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnaskdr/html/askgui07312001.asp
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q258038
--
HTH,
SriSamp
Please reply to the whole group only!
"MoroB" <m.bonini@.abax.it> wrote in message
news:09b001c3a2c2$7cfff450$a601280a@.phx.gbl...
> How can I insert a Word document into a image field in
> SQL server 7 or 2000 via ASP?
> Thanks
> MoroB|||Hi,
This is what we used, takes a bit of reading, and ignore
the .net stuff but it works.
http://support.microsoft.com/default.aspx?scid=KB;EN-
US;Q194975&
>--Original Message--
>How can I insert a Word document into a image field in
>SQL server 7 or 2000 via ASP?
>Thanks
>MoroB
>.
>|||Does It work for Visual Studio or also with asp, that
it's what i need?
MoroB
>--Original Message--
>Hi,
>This is what we used, takes a bit of reading, and ignore
>the .net stuff but it works.
>http://support.microsoft.com/default.aspx?scid=KB;EN-
>US;Q194975&
>
>>--Original Message--
>>How can I insert a Word document into a image field in
>>SQL server 7 or 2000 via ASP?
>>Thanks
>>MoroB
>>.
>.
>|||They don't work with asp
>--Original Message--
>Check out the following links:
>http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/dnaskdr/html/askgui07312001.asp
>http://support.microsoft.com/default.aspx?scid=kb;EN-
US;q258038
>--
>HTH,
>SriSamp
>Please reply to the whole group only!
>"MoroB" <m.bonini@.abax.it> wrote in message
>news:09b001c3a2c2$7cfff450$a601280a@.phx.gbl...
>> How can I insert a Word document into a image field in
>> SQL server 7 or 2000 via ASP?
>> Thanks
>> MoroB
>
>.
>|||I've not personally tested them with ASP, but they work fine with VB. So
with just some modifications, they should work in ASP.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
<anonymous@.discussions.microsoft.com> wrote in message
news:0f0501c3a2c7$7e670c20$a101280a@.phx.gbl...
> They don't work with asp
> >--Original Message--
> >Check out the following links:
> >http://msdn.microsoft.com/library/default.asp?
> url=/library/en-us/dnaskdr/html/askgui07312001.asp
> >http://support.microsoft.com/default.aspx?scid=kb;EN-
> US;q258038
> >
> >--
> >HTH,
> >SriSamp
> >Please reply to the whole group only!
> >
> >"MoroB" <m.bonini@.abax.it> wrote in message
> >news:09b001c3a2c2$7cfff450$a601280a@.phx.gbl...
> >> How can I insert a Word document into a image field in
> >> SQL server 7 or 2000 via ASP?
> >>
> >> Thanks
> >> MoroB
> >
> >
> >.
> >

Insert unicode data into the database with Typed DataSet

Hi all,

I am using aStrongly Typed DataSet (ASP.NET 2.0) to insert new data into aSQL Server 2000 database, types of some fields in db arenvarchar.

All thing work fine except I can not insertunicode data(Vietnamese language) into db.

I can't find where to putprefix N.

Please help me!!!

The last collation definition is the MSDN link below is for Vietnamese_CI_AS you have to use that as your database and column collation because 2000 support column level collation. So use NChar, NVarchar and NText as your data types and use the collation below. VS2005 also comes with the option to choose the editor to use in saving your files and the correct encoding so use Vietnamese encoding to save your dataset file, that is covered in the second link. Hope this helps.

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

https://www.microsoft.co.ke/middleeast/msdn/arabicsupp.aspx#7

insert to recordset gives different date format to table insert ?

Help please!

I have an asp page with some simple vbscript to add a record to a table, the record has a datefield (dob).

the insert results in a US formated date if I add a record to a dynamic recordset but a UK formated date if I insert direct to the table ?

i.e.

if request("dob") is "01/11/2007" (1st november 2007)

set conn = server.createobject("adodb.connection")

set rs = server.createobject("adodb.recordset")

rs.open "tez", mc, 2, 2 rs.addnew

rs("dob") = request("dob")

rs.update

11 jan 2007 stored in table

while

set trs = Server.CreateObject("ADODB.RecordSet")

qfn= "insert tez values('"+request("dob")+"')"

trs.Open qfn,mc

results in

1 november 2007 is written to the table.

Both of these methods are used in the same asp page.

This is on a windows2003 server, sql2005,iisv6, asp.netv2

I have tried every setting I can find in iis,asp,sql server to no avail.

I need the recordset method to work correctly.

Terry

It is a 'best practice' to use ISO formatted dates when working with systems that are in different Regions, etc.

Have the application submit the date to the data server in the form or "yyyy/mm/dd".

|||

Hi,

If you are using the datetime data type, SQL Server stores internally and transfers to clients (TDS) the date in an internal format, which would always be the same. The final representation which you get would eventually depend on the client and the client API. For instance, in sqlcmd.exe you could insert something like:

insert mytab values ('July 3 2006')

(note that you could control the format using commands like SET DATEFORMAT, etc)

But if you use an ADO script like this:

Dim cn
Set cn=CreateObject("ADODB.Connection")
cn.Open "Provider=SQLOLEDB;Integrated Security=SSPI;Data Source=<myserver>;initial catalog=<mydb>"
Dim rs
Set rs=cn.Execute("SELECT * FROM master..mytab")
MsgBox rs.Fields("d")
rs.Close

cn.Close

the results would be:

7/3/2006

How do you query/display the value? It might also sometimes depend on the regional settings of your machine.

HTH,
Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Unfortunatly this is not my application so I can not change the way dates are submitted, I am supporting an application that, when a date is input the day and month of the date are reversed.

This is an issue with a setting on either our sql2005 sever setup, our iis setup or maybee a .net issues.

I have checked every thing I can think of to no avail.

During my investigation I notice the symptons as described, that on the same asp page, with the same connection object an "insert <table> values(<mydate>)" slq command works correctly while a recordset.add does not.!

|||

Yes the table is using a datetime data type, but for the same connection object on the same asp page,

an "insert <mytable> values('1/10/2005')"

inserts '1 october 2005' into the table

while an

recordset.open

recordset.add

recordset.datefield = '1/10/2005'

inserts '10 january 2005' into the table !?

terry

|||

Those problems with datetimes in old Visual Basic and ASP... That's why is best practice to keep an standard.

Your problem is that the code converts strings to datetime in two different languages and environments, that have different settings:

1 - In Visual Basic Script (ASP), you convert a string (from the request object), to a datetime.

rs("dob") = request("dob")

From http://msdn2.microsoft.com/en-us/library/3eaydw6e(VS.80).aspx, Visual Basic in general and VBScript in particular, follows the convention of M/d/yyyy, always. So if request("dob") is "01/11/2007", VBScript takes it as "January 11, 2007" in its internal datetime representation, which is sent to the server not as a string, but as an integer representing datetime. No Regional Settings are used.

2 - But when you send SQL to the server, the conversion depends on your database and server configuration. So the code:

set trs = Server.CreateObject("ADODB.RecordSet")

qfn= "insert tez values('"+request("dob")+"')"

trs.Open qfn,mc

Creates for example the SQL

insert tez values('01/11/2007')

Suppose this SQL is processed by your SQL Server 2005, default installation on a server that has for Regional Settings the format "D/M/Y" (UK for example), then SQL Server converts using the regional settings, and thus the value inserted is equivalent to "November 1, 2007"

Now that you know the root cause, there are two solutions:

1 - The easiest and quicker workaround, but limited: Change the Regional Settings in your server, to United States (the equivalent to the behavior hardcoded in Visual Basic). That will solve this problem, but may break another application not correctly implemented doing the same but with other format assumptions...

2 - The better, portable workaround: Never use VB code for converting a string to a datetime; send the string to the database, and make the database server convert the data (if possible explicitly, for example by using "set dateformat dmy" in SQL Server).

So, change all your client recordsets, to server recordsets, and send the dates in a defined, standard format that your app converts. It is also a good practice to send it to stored procedures, which define the dateformat explicitly.

Note that the recommended solution (but more work) is #2, because if you take #1 and then you have to move the same code to another server (which may not be in your control, maybe hosted on a different country), it could break again.

Bruno Guardia - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Could anybody help Please,

I have application running on Windows XP which uses the date format of en-US(MM\DD\YYYY),But when I shift the application to windows 2003 server with date format of Germany it fails to work.Even i tried to change the language from Germany to English -united state even then it is taking Germany format of date itself.(DD.MM.YYYY)

|||

are you using SQL Server? If so, you should apply the solution described (use a consistent data format, independent of regional settings)

insert to recordset gives different date format to table insert ?

Help please!

I have an asp page with some simple vbscript to add a record to a table, the record has a datefield (dob).

the insert results in a US formated date if I add a record to a dynamic recordset but a UK formated date if I insert direct to the table ?

i.e.

if request("dob") is "01/11/2007" (1st november 2007)

set conn = server.createobject("adodb.connection")

set rs = server.createobject("adodb.recordset")

rs.open "tez", mc, 2, 2 rs.addnew

rs("dob") = request("dob")

rs.update

11 jan 2007 stored in table

while

set trs = Server.CreateObject("ADODB.RecordSet")

qfn= "insert tez values('"+request("dob")+"')"

trs.Open qfn,mc

results in

1 november 2007 is written to the table.

Both of these methods are used in the same asp page.

This is on a windows2003 server, sql2005,iisv6, asp.netv2

I have tried every setting I can find in iis,asp,sql server to no avail.

I need the recordset method to work correctly.

Terry

It is a 'best practice' to use ISO formatted dates when working with systems that are in different Regions, etc.

Have the application submit the date to the data server in the form or "yyyy/mm/dd".

|||

Hi,

If you are using the datetime data type, SQL Server stores internally and transfers to clients (TDS) the date in an internal format, which would always be the same. The final representation which you get would eventually depend on the client and the client API. For instance, in sqlcmd.exe you could insert something like:

insert mytab values ('July 3 2006')

(note that you could control the format using commands like SET DATEFORMAT, etc)

But if you use an ADO script like this:

Dim cn
Set cn=CreateObject("ADODB.Connection")
cn.Open "Provider=SQLOLEDB;Integrated Security=SSPI;Data Source=<myserver>;initial catalog=<mydb>"
Dim rs
Set rs=cn.Execute("SELECT * FROM master..mytab")
MsgBox rs.Fields("d")
rs.Close

cn.Close

the results would be:

7/3/2006

How do you query/display the value? It might also sometimes depend on the regional settings of your machine.

HTH,
Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Unfortunatly this is not my application so I can not change the way dates are submitted, I am supporting an application that, when a date is input the day and month of the date are reversed.

This is an issue with a setting on either our sql2005 sever setup, our iis setup or maybee a .net issues.

I have checked every thing I can think of to no avail.

During my investigation I notice the symptons as described, that on the same asp page, with the same connection object an "insert <table> values(<mydate>)" slq command works correctly while a recordset.add does not.!

|||

Yes the table is using a datetime data type, but for the same connection object on the same asp page,

an "insert <mytable> values('1/10/2005')"

inserts '1 october 2005' into the table

while an

recordset.open

recordset.add

recordset.datefield = '1/10/2005'

inserts '10 january 2005' into the table !?

terry

|||

Those problems with datetimes in old Visual Basic and ASP... That's why is best practice to keep an standard.

Your problem is that the code converts strings to datetime in two different languages and environments, that have different settings:

1 - In Visual Basic Script (ASP), you convert a string (from the request object), to a datetime.

rs("dob") = request("dob")

From http://msdn2.microsoft.com/en-us/library/3eaydw6e(VS.80).aspx, Visual Basic in general and VBScript in particular, follows the convention of M/d/yyyy, always. So if request("dob") is "01/11/2007", VBScript takes it as "January 11, 2007" in its internal datetime representation, which is sent to the server not as a string, but as an integer representing datetime. No Regional Settings are used.

2 - But when you send SQL to the server, the conversion depends on your database and server configuration. So the code:

set trs = Server.CreateObject("ADODB.RecordSet")

qfn= "insert tez values('"+request("dob")+"')"

trs.Open qfn,mc

Creates for example the SQL

insert tez values('01/11/2007')

Suppose this SQL is processed by your SQL Server 2005, default installation on a server that has for Regional Settings the format "D/M/Y" (UK for example), then SQL Server converts using the regional settings, and thus the value inserted is equivalent to "November 1, 2007"

Now that you know the root cause, there are two solutions:

1 - The easiest and quicker workaround, but limited: Change the Regional Settings in your server, to United States (the equivalent to the behavior hardcoded in Visual Basic). That will solve this problem, but may break another application not correctly implemented doing the same but with other format assumptions...

2 - The better, portable workaround: Never use VB code for converting a string to a datetime; send the string to the database, and make the database server convert the data (if possible explicitly, for example by using "set dateformat dmy" in SQL Server).

So, change all your client recordsets, to server recordsets, and send the dates in a defined, standard format that your app converts. It is also a good practice to send it to stored procedures, which define the dateformat explicitly.

Note that the recommended solution (but more work) is #2, because if you take #1 and then you have to move the same code to another server (which may not be in your control, maybe hosted on a different country), it could break again.

Bruno Guardia - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Could anybody help Please,

I have application running on Windows XP which uses the date format of en-US(MM\DD\YYYY),But when I shift the application to windows 2003 server with date format of Germany it fails to work.Even i tried to change the language from Germany to English -united state even then it is taking Germany format of date itself.(DD.MM.YYYY)

|||

are you using SQL Server? If so, you should apply the solution described (use a consistent data format, independent of regional settings)

insert to recordset gives different date format to table insert ?

Help please!

I have an asp page with some simple vbscript to add a record to a table, the record has a datefield (dob).

the insert results in a US formated date if I add a record to a dynamic recordset but a UK formated date if I insert direct to the table ?

i.e.

if request("dob") is "01/11/2007" (1st november 2007)

set conn = server.createobject("adodb.connection")

set rs = server.createobject("adodb.recordset")

rs.open "tez", mc, 2, 2 rs.addnew

rs("dob") = request("dob")

rs.update

11 jan 2007 stored in table

while

set trs = Server.CreateObject("ADODB.RecordSet")

qfn= "insert tez values('"+request("dob")+"')"

trs.Open qfn,mc

results in

1 november 2007 is written to the table.

Both of these methods are used in the same asp page.

This is on a windows2003 server, sql2005,iisv6, asp.netv2

I have tried every setting I can find in iis,asp,sql server to no avail.

I need the recordset method to work correctly.

Terry

It is a 'best practice' to use ISO formatted dates when working with systems that are in different Regions, etc.

Have the application submit the date to the data server in the form or "yyyy/mm/dd".

|||

Hi,

If you are using the datetime data type, SQL Server stores internally and transfers to clients (TDS) the date in an internal format, which would always be the same. The final representation which you get would eventually depend on the client and the client API. For instance, in sqlcmd.exe you could insert something like:

insert mytab values ('July 3 2006')

(note that you could control the format using commands like SET DATEFORMAT, etc)

But if you use an ADO script like this:

Dim cn
Set cn=CreateObject("ADODB.Connection")
cn.Open "Provider=SQLOLEDB;Integrated Security=SSPI;Data Source=<myserver>;initial catalog=<mydb>"
Dim rs
Set rs=cn.Execute("SELECT * FROM master..mytab")
MsgBox rs.Fields("d")
rs.Close

cn.Close

the results would be:

7/3/2006

How do you query/display the value? It might also sometimes depend on the regional settings of your machine.

HTH,
Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Unfortunatly this is not my application so I can not change the way dates are submitted, I am supporting an application that, when a date is input the day and month of the date are reversed.

This is an issue with a setting on either our sql2005 sever setup, our iis setup or maybee a .net issues.

I have checked every thing I can think of to no avail.

During my investigation I notice the symptons as described, that on the same asp page, with the same connection object an "insert <table> values(<mydate>)" slq command works correctly while a recordset.add does not.!

|||

Yes the table is using a datetime data type, but for the same connection object on the same asp page,

an "insert <mytable> values('1/10/2005')"

inserts '1 october 2005' into the table

while an

recordset.open

recordset.add

recordset.datefield = '1/10/2005'

inserts '10 january 2005' into the table !?

terry

|||

Those problems with datetimes in old Visual Basic and ASP... That's why is best practice to keep an standard.

Your problem is that the code converts strings to datetime in two different languages and environments, that have different settings:

1 - In Visual Basic Script (ASP), you convert a string (from the request object), to a datetime.

rs("dob") = request("dob")

From http://msdn2.microsoft.com/en-us/library/3eaydw6e(VS.80).aspx, Visual Basic in general and VBScript in particular, follows the convention of M/d/yyyy, always. So if request("dob") is "01/11/2007", VBScript takes it as "January 11, 2007" in its internal datetime representation, which is sent to the server not as a string, but as an integer representing datetime. No Regional Settings are used.

2 - But when you send SQL to the server, the conversion depends on your database and server configuration. So the code:

set trs = Server.CreateObject("ADODB.RecordSet")

qfn= "insert tez values('"+request("dob")+"')"

trs.Open qfn,mc

Creates for example the SQL

insert tez values('01/11/2007')

Suppose this SQL is processed by your SQL Server 2005, default installation on a server that has for Regional Settings the format "D/M/Y" (UK for example), then SQL Server converts using the regional settings, and thus the value inserted is equivalent to "November 1, 2007"

Now that you know the root cause, there are two solutions:

1 - The easiest and quicker workaround, but limited: Change the Regional Settings in your server, to United States (the equivalent to the behavior hardcoded in Visual Basic). That will solve this problem, but may break another application not correctly implemented doing the same but with other format assumptions...

2 - The better, portable workaround: Never use VB code for converting a string to a datetime; send the string to the database, and make the database server convert the data (if possible explicitly, for example by using "set dateformat dmy" in SQL Server).

So, change all your client recordsets, to server recordsets, and send the dates in a defined, standard format that your app converts. It is also a good practice to send it to stored procedures, which define the dateformat explicitly.

Note that the recommended solution (but more work) is #2, because if you take #1 and then you have to move the same code to another server (which may not be in your control, maybe hosted on a different country), it could break again.

Bruno Guardia - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Could anybody help Please,

I have application running on Windows XP which uses the date format of en-US(MM\DD\YYYY),But when I shift the application to windows 2003 server with date format of Germany it fails to work.Even i tried to change the language from Germany to English -united state even then it is taking Germany format of date itself.(DD.MM.YYYY)

|||

are you using SQL Server? If so, you should apply the solution described (use a consistent data format, independent of regional settings)

sql

Insert TIME only in DateTime field

I am doing a temporary retro-upgrade right now. So, I know this isn't exactly in the scope of ASP.Net. Ordinally my posts are. However, I need a VBScript example of how to insert the Date only into the DateTime field of an SQL 2000 Server. By default, if you try to, the server automatically adds the date "1/1/1900". Can anyone help me please?That is how datetime fields work. You need to address this using formatting on the client side. DateTime has a number of useful ToString() overloads.|||Thanks for your help. It is too bad Access doesn't function in datatypes more similarly to SQL, especially since they are products of the same company. This migration would go alot easier. I am migrating some VBScript applications from Access to SQL, then from VBScript to VB.Net. It has been quite fun so far. Hehehe...

INSERT special characters into SQL Database (Protect my Tic)

Greetings!

I'm using classic ASP and an insert statement to save bulletin messages to a database that can be updated or viewed later. At first I thought it worked perfectly so I took some sample messages and one of them kept erroring out.

I've pretty much determined that it hates the ' character.. and I'm sure it'd hate other special characters as well so here's my question. How do I insert special characters into a database? My co-worker suggested HTMLencode and decode but really that's not going to work.Was it a quote that didn't go through?|||

Quote:

Originally Posted by Arielle

Greetings!

I'm using classic ASP and an insert statement to save bulletin messages to a database that can be updated or viewed later. At first I thought it worked perfectly so I took some sample messages and one of them kept erroring out.

I've pretty much determined that it hates the ' character.. and I'm sure it'd hate other special characters as well so here's my question. How do I insert special characters into a database? My co-worker suggested HTMLencode and decode but really that's not going to work.

hi Arielle,

if you are using sql server2000 for backend activities, it accepts all the special characters while inserting a record,except for ' single quotes. You would have noticed that it throws errors, when we try to insert something like this:

eg:
insert into <tablename> values(val1,val2,'the student's of csc dept have scored the highest')

unclosed quotation mark before the character string...
incorrect syntax near...

if you want to insert your message that contains apostophe character from your asp page to sql server table, then you will have to use the Replace() function in your asp page.

Replace(string,find,replacewith)
where,
string: the string to be searched
find: the part of the string that will be replaced
replacewith: the replacement substring

eg: dim txt,crreason
txt=request("txtCrReason")
crreason=Replace(txt,"'","''")
...and send the value stored in the variable 'crreason' to your insert query.

try this and let me know if it has solved your problem. Gud Luck!

cheers,
jai