How can we create a DB for a single attribute such as ORDER DETAILS, CASH RECEIPT, TAX INVOICE having more than 2 text fields.
Also, in every form attribute such as order id is not present - in order identify the same as a primay key. So, which other attributes or fields can be considered as a primary key.
I am in the process of develping a MSRS report using an MSAS 2005 OLAP cube as my data source. In the MSRS Query Builder, I am using an MDX query which successfully executes in Management Studio. Something like the following:
SELECT
NON EMPTY { [Measures].[Fact Count] }
ON COLUMNS,
NON EMPTY{ ( [Dim Attribute].[Hierarchy Not Visible].ALLMEMBERS) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME
ON ROWS
FROM [MyCube]
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
The twist is that the AttributeHierarchyVisible property of the [Dim Attribute].[Hierarchy Not Visible] is set to False.
As mentioned previously, the query successfully executes in Management Studio. However when it is executed in the MSRS Query Builder, the following error message is displayed:
The query cannot be prepared: The query must have at least one axis. The first axis of the query should not have multiple hierarchies, nor should it reference any dimension other than the Measures dimension..
Parameter name: mdx (MDXQueryGenerator)
Is there a way to successfully query dimension attributes whose hierarchies are not visible?
Thanks.
Hi, i'm trying to do a full text search on my site to add a weighting score to my results. I have the following database structure:
Documents: - DocumentID (int, PK) - Title (varchar) - Content (text) - CategoryID (int, FK)
Categories: - CategoryID (int, PK) - CategoryName (varchar)
I need to create a full text index which searches the Title, Content and CategoryName fields. I figured since i needed to search the CategoryName field i would create an indexed view. I tried to execute the following query:
CREATE VIEW vw_DocumentsWITH SCHEMABINDING ASSELECT dbo.Documents.DocumentID, dbo.Documents.Title, dbo.Documents.[Content], dbo.Documents.CategoryID, dbo.Categories.CategoryNameFROM dbo.Categories INNER JOIN dbo.Documents ON dbo.Categories.CategoryID = dbo.Documents.CategoryID
GOCREATE UNIQUE CLUSTERED INDEX vw_DocumentsIndexON vw_Documents(DocumentID)
But this gave me the error:
Cannot create index on view 'dbname.dbo.vw_Documents'. It contains text, ntext, image or xml columns.
I tried converting the Content to a varchar(max) within my view but it still didn't like.
Appreciate if someone can tell me how this can be done as surely what i'm trying to do is not ground breaking.
Hi,
I have found that in the autogenerated model attributes are missing for those fields that have relations to other tables. At first, it may look reasonable since a user can still get down to the field's value through the relation/related table. However, if the relation's key fields is the only thing the user wants to display, then going down to the related table is an overkill.
I can add an attribute manually and bind it to the key field(s). Is there an option in the autogeneration process to do it automatically? The only post I've found so far suggests to do everything manually (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1152575&SiteID=1). Is this the only way?
Thank you,
Leonid
I have followed many tutorials on selecting and replacing text in text fields, varchar fields and char fields, but I have yet to find a single script that will to all 3 based on field type. Let's assume for a moment that I don't know where all in my database a certain value that I need changed resides ... i.e., the data's tablename and fieldname. How would I go about doing the following ... or more importantly, is this even possible in a SQL only procedure?1) Loop over entire database and get all user tables2) Loop over all user tables and get all fields3) Loop over all fields and determine the field type4) switch between field types and change a string of text from 'a' to 'b'Please be gentle, I'm a procedure newb.
View 9 Replies View RelatedI want to insert attibute att1 in field F1 . Value for this attribute is content of another field in this table (F2).
My query :
update MyTable
set F1.modify
('insert attribute att1 {sql:column("F2")} into (/ROOT/Node1)[1]')
Where F1 Is not null
But I get this error :
XML well-formedness check: Duplicate attribute 'att1'. Rewrite your XQuery so it returns well-formed XML.
How do I check the douplicate attribute ?
I have a report I am creating in sql reporting services 2005 and what I am trying to do is have a single text box with multiple lines of data format each item in the textbox to specific parameters but I have not been able to figure it out. I tried to format my data on the sql side so that it cam out as follows:
tried
Monday, Nov. 26, 2007
"Color = Blue FontWeight = Bold " & John Doe & CHAR(9) & "Color = Red FontWeight = Normal " & Jane Smith & CHAR(9) & "Color = Black FontWeight = Bold" John Roberts
This did not work
so then I tried
Monday, Nov. 26, 2007
"FontColor = Blue FontWeight = Bold " & John Doe & CHAR(9) & "FontColor = Red FontWeight = Normal " & Jane Smith & CHAR(9) & "FontColor = Black FontWeight = Bold" John Roberts
Still did not work
What my end result needs to be in a single text box...
Monday, Nov. 26, 2007
John Doe Jane Smith John Roberts
If anyone has the answer thanks ahead of time.
Mike
I have a data table in my project that has a "text" (not varchar) field in it. I am trying to load this field with little paragraphs of text for showing on my pages. How do I get the free text loaded into the table? The database explorer and all the data grid controls cut the text off at one line. There doesn't seem to be any way to get multiline text into this table.
View 2 Replies View Related
HI,
I had to change the key columns of a dimension attribute to fix an error. I did this in BIDS. The change was from a single key column to a composite key column. Now I am getting these error when I process the cube:
Measure group attribute key column x does not match source attribute ..
I looked at the cube XMLA definition under mesaure groups and it still shows a single key column with inherited binding. However, the BIDS does not give me an option correct this in any way. I have had to do this once before and the only option seems to be removing the dimension from the cube and add it back in. But that is very error prone since I lose any specific settings at the cube dimension level not to mention aggregations no longer include the dimension, etc.
Not seeing an alternative, I went through each measure group (I have 7) and changed the key columns manually in the XMLA and saved the cube. This worked, but I don't understand why BIDS automatically doesn't do it.
Is this a flaw in the BIDS or I should be missing something.
thanks
MJ
Hi,
I'm struggling with this. I'd like to perform a joined query
from two or more tables and take the same field from several
rows of one table into seperate fields of a single row in a new table.
Like this:
table 1
uidunameuloc
1mehere
1methere
2youhere
2 youthere
table 2
uidulocuparam#uparamval
1here1a
1here2b
1here3c
1there1d
1there2e
1there3f
2here1g
2here2h
2here3i
2there1j
2there2k
2there3l
result in table 3
uidunameuloc uparval1 uparval2 uparval3
1mehere a b c
1methere d e f
2youhere g h i
2 youthere j k l
uparam# field in table 2 always the same sequence
Any ideas????
Thanks...
If I have a database table with the following columns:
ID
Other_ID
Description
And I want to join the two ID fields to one field in another table that contains the following fields:
ID
Name
How would i do that?
Here is some sample data and what I would like returned
TABLE1
ID Other_ID Description
row 1 1 2 Number1
row 2 3 1 Number2
TABLE2
ID Name
row 1 1 John
row 2 2 Bob
row 3 3 Bill
I want to query TABLE1, row 1 so that I pull back the Names for the values stored in the ID and Other_ID fields so that my results are like:
John Bob Number1
The only way around it now is that I store Other_Name in Table1.
Thanks.
Hi,All:
I am using Sql 2000 db to storage my data,and I have a table including a column named "ScanDate" (Type: SmallDateTime),Now
I just want to save the current system date to this column when I save my system data.
when I saved it and I found that column value include the time data,not only date .
So How can I just save date to my samlldatetime typed column ?
thanks in advanced!
I have a very complex (for me, anyway) data transformation problem.
I've been given a flat-file of physician data from another system which must be automated for entry into the SQL server on a regular basis.
This was no problem until we discovered that several fields (all of which we wanted to use) had multiple pieces of information in them, separated by semicolons.
Well, this didn't seem to be too big of a problem, so I wrote a DTS activeX script to handle it. This is what I originally wrote:
'*******************
Function Transform()
'Declare variables
Dim strOffice
Dim strOfficeNew
Dim cChar
Dim x
Dim y
Dim z
'Scrub values into new rows
strOffice = DTSSource("Col050")
x = 1
y = len(strOffice)
z = 1
While x <= y
cChar = Mid(strOffice, x 1)
If cChar <> ";" Then
strOfficeNew = strOfficeNew & cChar
Else
DTSDestination("Phys_No") = DTSSource("Col001") DTSDestination("Addr_No") = z
DTSDestination("Addr_Office") = strOfficeNew
strOfficeNew = ""
z = z + 1
End If
x = x + 1
Wend
'Insert final record after last semicolon
If strOffice <> "" Then
DTSDestination("Phys_No") = DTSSource("Col001") DTSDestination("Addr_No") = z
DTSDestination("Addr_Office") = strOfficeNew
End If
Transform = DTSTransformStat_OK
End Function
'*********************
This, of course, didn't work. WHat I got was the last part of the parsed data, which for the first record, was the second Address in the field.
I searched around, and found the following script that is supposed to allow multiple rows off of a single row, but I can't seem to merge the two and still get the data out clean.
'**********
Dim nCounter
nCounter = 4
Function Main()
if nCounter > 0 then
Main = DTSTransformStat_SkipFetch
DTSDestination("PatientNumber") = DTSSource("PatientNumber") Select Case nCounter
Case 1
DTSDestination("PhysicianType") = "Admitting" DTSDestination("PhysicianId") = DTSSource
("AdmittingPhysician")
Case 2
DTSDestination("PhysicianType") = "Attending" DTSDestination("PhysicianId") = DTSSource
("AttendingPhysician")
Case 3
DTSDestination("PhysicianType") = "Referring" DTSDestination("PhysicianId") = DTSSource
("ReferringPhysician")
Case 4
DTSDestination("PhysicianType") = "Consulting" DTSDestination("PhysicianId") = DTSSource
("ConsultingPhysician")
End Select
nCounter = nCounter - 1
else
nCounter = 4
Main = DTSTransformStat_SkipInsert
end if
End Function
'**************
I'm not a VB Script expert, so there's probably something very simple that I'm missing here... if someone could point it out, I'd be greatly appreciative.
Jaysen
Hi
I need to export data to CSV file from Sql Reporting Services.
I am including single quotations in my view to display in SRS for string fields.
After export if we open in Notepad, for string field it is adding multiple quotations.
How to add single quotations for string fields - CSV files,
Please let me know if anybody knows.
Thx
Vijji
Dear ppl,
I have got 2 datasets D1 & D2 in a Report. How can i differentiate between the fields of these two.
e.g. I got Name field in both the datasets. So when i do =Fields!Name.Value in a textbox the report gives me error
How can i tell the report from which dataset to pick up the field ??
Regards
Nabeel
need help on update from one table to another like this
this is my first table
tb_all_holiday
id
fname
Start_Date
End_Date
val_holiday
111
aaaa
15/03/2008
21/03/2008
1
222
bbbb
02/05/2008
09/05/2008
3
333
cccc
03/04/2008
15/05/2008
4
333
cccc
29/04/2008
07/07/2008
1
444
dddd
01/05/2008
02/05/2008
1
444
dddd
09/05/2008
19/08/2008
1
555
EEE
09/07/2008
09/08/2008
4
666
fff
10/09/2008
12/09/2008
1
this is my second table to insert into !
i need to insert to another table like this
single row for each day from start_date TO END_DATE
check each employee add row for each day
insert all employee one after one
ID fname new_date val_holiday
----------------------------------------------------
111 aaaa 15/03/2008 1
111 aaaa 16/03/2008 1
111 aaaa 18/03/2008 1
111 aaaa 19/03/2008 1
111 aaaa 20/03/2008 1
111 aaaa 21/03/2008 1
222 bbb 02/05/2008 3
222 bbb 03/05/2008 3
222 bbb 04/05/2008 3
222 bbb 05/05/2008 3
222 bbb 06/05/2008 3
222 bbb 07/05/2008 3
222 bbb 08/05/2008 3
222 bbb 09/05/2008 3
333 ccc 03/04/2008 4
333 ccc 04/04/2008 4
......................................................add row for each day
...............................
333 ccc 15/05/2008 4
TNX for help
While working with a vast variety of support projects, i find a sql design where all the fields in a single form (say about 100 fields which are dump data as they are not related to any reports and searching criteria) are dumped in a sql database column in a XML format. See below an example
<?xml version="1.0" encoding="utf-8"?><FormBuilder><ClientID>0</ClientID><SiteID>0</SiteID><IncidentType></IncidentType><IncidentCategory></IncidentCategory><IncidentSubCategory></IncidentSubCategory><CreatedBy>2</CreatedBy><CreatedOn>Wednesday, April 02, 2014</CreatedOn><ModifiedOn /><ModifiedBy /><Section SectionID="ASD" SectionDisplayName="ASD" ColumnType="1" IsDeleted="0" SectionPosition="1"><SectionField FieldName="Bro" Section="ASD" ModuleID="0" Length="" PickData="" ChkData="" RadioData="" ListData="" FieldType="Text" Checked="false" ColumnType="1" IsDeleted="0" CoulmnOrder="0" FieldID="1" IsPrimary="" IsMandatory="" SystemMandatory="" RowPosition="1" FullRow="" /></Section></FormBuilder>
Just want to know the comments how far is this design feasible.....
What are the pros and cons of such a design...
Where we should use such type of db design where are the fields are dumped in a single column...
I have the following result set but I want to stack or transpose the 3 fields into a single column. I may add more fields later, but right now I want to know what's the best and simplest way.
Current result set:
TotalAllCustomersOnFile | TotalConsumersWithValidEmail | TotalConsumersWithValidEmailAndOptedIn
2,500 1,750 1,500
Desired result set:
Audience | Totals
----------------------------------------------------
TotalAllCustomersOnFile | 2,500
TotalConsumersWithValidEmail | 1,750
TotalConsumersWithValidEmailAndOptedIn | 1,500
Hi there
I have loaded a csv file into a table. Some fields within the file contain a varying number (up to 10) of subfields seperated by line feed characters.
It looks sort of like this
Customer No. Payments Dates
111 pay1|pay2|pay3 dat1|dat2|dat3
I created an Unpivot transformation, and I got
Customer No. Description Detail
111 Payments pay1|pay2|pay3
111 Dates dat1|dat2|dat3
After a Derived Column transformation I got
Customer No. Description Line1 Line2 Line3
111 Payments pay1 pay2 pay3
111 Dates dat1 dat2 dat3
But what I really want is to end up with this:
Customer No. Payments Dates
111 pay1 dat1
111 pay2 dat2
111 pay3 dat3
Is there a transformation that will get me there, or do I just need some cunning SQL?
I tried to Pivot my way back to happiness but I couldn't get it to work
question need help
how can i use this code below not for insert
i need it for update another table but only if exist
the link to the code in this FORUM
http://forums.microsoft.com/MSDN/AddPost.aspx?PostID=3208536&SiteID=1&Quote=True
Adam Haines wrote:
GPS,
Since you have such a dependency on dates and date calculations, you should implement a calendar table. A calendar table will make calculation such as this much more simplistic.
Calendar table link http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html.
Note the calendar table I use is a little different than this one. The only thing you will need to change is isodate to dt.
Now the code to get the results you need:
Code Snippet
declare @t table(
id int,
fname char(4),
Start_Date datetime,
End_Date datetime,
val_holiday int
)
insert into @t values (111, 'aaaa', '3/15/2008', '03/21/2008', 1 )
insert into @t values (222, 'bbbb', '05/2/2008', '05/9/2008', 3)
insert into @t values (333, 'cccc', '04/3/2008', '05/15/2008', 4)
insert into @t values (333, 'cccc', '04/29/2008', '07/07/2008', 1 )
select id, fname, cal.ISODate, val_holiday
from @t t1
inner join Calendar cal
on cal.isodate >= t1.start_date and
cal.ISODate <= t1.end_date
is possible to do it
TNX
Hi all,
I am developing an application for a big office which uses SQL Server 2000.
Apart from my database, on that server, there are two databases by other companies.
The administrator also has access to server but the client only wants him to backup the database.
I have two questions:
1) First of all (if it is possible) I would like to protect my own database from the other companies.
I don't want them to:
see the data in the tables (around 20 tables)
make changes to the stored procedures (more than 100 stored procedures)
be able to backup the database
2) The client will save sensitive data to the database (mainly currency amounts, salaries etc) which he wants to keep hidden.
I am using float type for these fields and I would like to make the data encrypted. I could do it for nvarchar fields but changing these float to nvarchar would be time consuming.
Thanks for your patience reading this!
Would really appreciate some help on any of these
Nicolas
Hi,
I want to create a text file and write to text it by calling its assembly from Stored Procedure. Full Detail is given below
I write a code in class to create a text file and write text in it.
1) I creat a class in Visual Basic.Net 2005, whose code is given below:
Imports System
Imports System.IO
Imports Microsoft.VisualBasic
Imports System.Diagnostics
Public Class WLog
Public Shared Sub LogToTextFile(ByVal LogName As String, ByVal newMessage As String)
Dim w As StreamWriter = File.AppendText(LogName)
LogIt(newMessage, w)
w.Close()
End Sub
Public Shared Sub LogIt(ByVal logMessage As String, ByVal wr As StreamWriter)
wr.Write(ControlChars.CrLf & "Log Entry:")
wr.WriteLine("(0) {1}", DateTime.Now.ToLongTimeString(), DateTime.Now.ToLongDateString())
wr.WriteLine(" :")
wr.WriteLine(" :{0}", logMessage)
wr.WriteLine("---------------------------")
wr.Flush()
End Sub
Public Shared Sub LotToEventLog(ByVal errorMessage As String)
Dim log As System.Diagnostics.EventLog = New System.Diagnostics.EventLog
log.Source = "My Application"
log.WriteEntry(errorMessage)
End Sub
End Class
2) Make & register its assembly, in SQL Server 2005.
3)Create Stored Procedure as given below:
CREATE PROCEDURE dbo.SP_LogTextFile
(
@LogName nvarchar(255), @NewMessage nvarchar(255)
)
AS EXTERNAL NAME
[asmLog].[WriteLog.WLog].[LogToTextFile]
4) When i execute this stored procedure as
Execute SP_LogTextFile 'C:Test.txt','Message1'
5) Then i got the following error
Msg 6522, Level 16, State 1, Procedure SP_LogTextFile, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'SP_LogTextFile':
System.UnauthorizedAccessException: Access to the path 'C:Test.txt' is denied.
System.UnauthorizedAccessException:
at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy)
at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, ileOptions options)
at System.IO.StreamWriter.CreateFile(String path, Boolean append)
at System.IO.StreamWriter..ctor(String path, Boolean append, Encoding encoding, Int32 bufferSize)
at System.IO.StreamWriter..ctor(String path, Boolean append)
at System.IO.File.AppendText(String path)
at WriteLog.WLog.LogToTextFile(String LogName, String newMessage)
Hey everyone,
using SQL 6.5 sp_makewebtask in a stored procedure I want to create a
press-info file.
My query calls four fields, a datum field, two varchar fields and a text
field, it's
the field "PrText" in my example:
EXEC sp_makewebtask @outputfile='F:EntwicklungRedaktionZeitwenden27.ht m',
@query='SELECT Datum, Ort, Headline, PrText
FROM news
WHERE ID = 27',
@templatefile='F:EntwicklungRedaktionZeitwendenTem plates.htm'
My question: Am I limited to 255 characters in this case? After 255
characters the text is cut off and
I have only a truncated text fragment.
Is there a possibility to get the whole text without using @blobfmt (I don't
want to use this parameter because
I want to have the datum data, the varchar headline AND the text in ONE
HTML-file.)
I have tried the same as dynamic query in ASP where I have no problem to get
the whole content of the text field, but I want
to create a static file because this information will be read often, but
won't be updated frequently
and I don't want to charge the server resources to much.
Has anybody an idea? Any help would be appreciated.
Thank You
Dorothee Wiethoff
I'm unable to run a Union query with text fields in it. I get the following error:
'The text, ntext, or image data type cannot be selected at DISTINCT'
Is there no way to run a UNion statement for two tables that have text fields in them??? Please help.
The Text datatype can hold several million characters
The size of a record can be no more than 8 KB in normal data.
Created a table with one text field. For some reason can only store 1K of data into it. Even if the 8K limit was a factor (which it shouldn't for a text field) I cannot add more than 1K of data into my text field. 1023 characters to be exact.
Why is SQL Server 2000 doing this and more importantly how to resolve?
Hi, I'm sure that this is probably a really easy question to most of you, but I seem to be spending ages on it as I am new to SQL. how do I add the carraige return char between joining two text fields ie.
a_blah.blah+CHAR(13)+b_blah.blah
Thanks for your help.
New to using MS SQL server!
I have a requirements DB. Before I came on board, the DB was seeded with requirements text and a requirements ID in separate columns. I've been asked to append the ID in front of the text:
Text - The user shall be able....
ID - 1.01.01.
Combined - 1.01.01.The user shall be able....
I've created the following query that displays the columns:
CREATE VIEW dbo.ReqID
AS
SELECT RQREQUIREMENTS.ID, RQREQUIREMENTS.REQUIREMENTNAME,
RQUSERDEFINEDFIELDVALUES.FIELDVALUE
FROM RQREQUIREMENTS LEFT OUTER JOIN
RQUSERDEFINEDFIELDVALUES ON
RQREQUIREMENTS.ID =
RQUSERDEFINEDFIELDVALUES.REQUIREMENTID
WHERE (RQUSERDEFINEDFIELDVALUES.FIELDID = 171)
The Text field is REQUIREMENTNAME
The ReqId is FIELDVALUE
Should I create an UPDATE query based on this SELECT, updating the REQUIREMENTNAME column with FIELDVALUE + REQUIREMENTNAME?
Please advise - thanks!
I've just gotten my data into SQL Server, and changed the connection stringon the program.There was a memo field (Access 2000) and we put that in a text field (with adefault length of 16?).Now, when displaying the data online (through asp pages), there are bigwhite-space gaps in the pages (that display the memo field data), imageshave a "double" url(http://www.mysite.com/thisfolder/ht...thelinksite.com)there are lots of place with " ".The field data has html tags in it, if that has anything to do with it.It seems like I've heard that there are issues with text fields. Is thiswhat they meant? Is there anything I can do?Thanks, JA
View 4 Replies View RelatedI am trying to update text fields where the field only has the value"closed." So a field that has closed and anything else will not beupdated.update dbo.Histset dbo.Hist.Notes = ''FROM dbo.HistWHERE (Notes LIKE 'closed_')update dbo.Histset dbo.Hist.Notes = ''FROM dbo.HistWHERE (Notes LIKE 'closed%') and len(convert(varchar(7000),notes)) = 7Neither code works and i get this error: "Subquery returned more than1 value. This is not permitted when the subquery follows =, !=, <, <=, >, >= or when the subquery is used as an expression.The statement has been terminated."What's another way to make the update?
View 1 Replies View RelatedI have the following data.
CustomerID Address
1 Spencer St
2 Frank Way
3 Jolly Dr
and i need to extract the address as 2 seperate fileds like so,
CustomerID Street Suffix
1 Spencer St
2 Frank Way
3 Jolly Dr
keep in mind that the Customer table has 2 million records.
Hi All,
I am trying to get at some text fields from an AS400(JBA) system. the problem i'm having is that for each new line of text on AS400 it creates a new row with a line number associated in the SQL table. When trying to export to excel this causes problems because i need the text in one cell not spread over 15/20 lines.
I need to create a query/function that will concatenate the text lines together. I'm fairly new to T-SQL so could do with some help.
Example:-
ID TxtLn Text
R262965
1
Text 1
R262965
2
Text 2
R262965
3
Text 3
What i'm aiming for is:-
ID ConcatText
R262965 Text1 Text2 Text3
Can anyone please help me or guide me towards a starting point.
Thanks
Hi!
I need to sync an application on windows mobile 5 that uses sql server mobile with sql server 2005. I've tried Merge and it populated the tables on the server with new fields and triggers. Does RDA do the same thing?
Jesus saves. But Gretzky slaps in the rebound.
When users enter text into a textbox, to be INSERTed into my table, SQL Server throws an error if their text contains a single quote.
For example, if they enter "It's great!" then it causes this error:Error: Incorrect syntax near 's'. Unclosed quotation mark after the character string ''.
How can I allow text with single quotes to be inserted into the table?
Here's my code:
string strInsert = "INSERT INTO [Comments] ([GameID], [UserID], [Comment]) VALUES (@GameID, @UserID, @Comment)";
SqlConnection myConnection = new SqlConnection(<<myconnectionstuff>>);SqlCommand myCommand = new SqlCommand(strInsert, myConnection);
myCommand.Parameters.Add( "@GameID", Request.QueryString["GameID"] );myCommand.Parameters.Add( "@UserID", (string)Session["UserID"] );myCommand.Parameters.Add( "@Comment", ThisUserCommentTextBox.Text );
try {myCommand.Connection.Open();myCommand.ExecuteNonQuery();}
catch (SqlException ex) {ErrorLabel.Text = "Error: " + ex.Message;}
finally {myCommand.Connection.Close();}