I'm using BCP for some command line SQL statement. The file I'm generating is comma delimited. Some of my records contain comma's in them and require them to be in quotation marks. What is the parameter for BCP to put those entries that have commas in them to have those records be in quotation marks?
View 2 RepliesWhat is the difference ',"?
View 4 Replies View RelatedI have a block of xml that I wish to update in my sql database. The problem I have is the the data has double and single quotation marks in it and all my attempts to send this to my sql database gives me errors reguarding the quotation marks. Is there a way I can send the xml to the database without these problems. I am using the xml in the sql database to make it easy to read and right the xml as xmldatasource does not allow reading and writing easily.
this section shows the problem, i am loading xml from a file and trying to insert this into the sql database
XmlTextReader reader = new XmlTextReader(Server.MapPath("xml/wt.xml"));reader.WhitespaceHandling = WhitespaceHandling.None;XmlDocument xmlDocF = new XmlDocument();xmlDocF.Load(reader);this.SqlDataSource1.UpdateCommand = "update [data] set [linkXML] '" + xmlDocF.InnerXml+ "' where [index] = 1" ;this.SqlDataSource1.Update();reader.Close();
pls help
I have problem with sql query with aspnet.
In my web page, i have a text box for input name or string to search in database. I have problem if the search string consist of quotation mark => ' .
Any suggestions? Thanks.
I have an ASP.Net page that allows people to type in strings and store them into a SQL Server DB; which in turn gets displayed on a website. The project has an admin side that can add/delete/edit announcements, which get displayed on an intranet site. These announcements can be clicked on to display further detail. When announcements are clicked on a javascript popup window is generated that displays the strings. All data is stored in a SQL Server DB.What I need to know is: how do I check to see if a string has a quotation mark or apostrophe in it so that I can replace it with the appropriate HTML code? (Though it seems I can't display an apostrophe, even when using the HTML code ''')If I store the string as was entered by the administrator (with quotation marks instead of '"'), the popup window will not display.
View 7 Replies View RelatedHi,I've created a table in SQL Server 2000 and I'm now trying to searchthrough the data and return specific rows. I'm using this command:select * from Export where libelle_court='Recherche d'investisseurs'The problem is this: The search fails whenever there is a curlysingle quotation mark within the table field ( ' as opposed to ' ).For example, if the field entry in my table is this:Recherche d'investisseursthen both of the following commands retun no fields:select * from Export where libelle_court='Recherche d'investisseurs'select * from Export where libelle_court='Recherche d''investisseurs'However, if the field entry in my table is this:Recherche d'investisseursthen both of the commands quoted above succeed.How can I get SQL Server to treat the curly quotation mark correctlyand return the right results? I've tried changing the collation butwith no success.Thanks,Rob
View 3 Replies View RelatedHi i have to pass Query to the SqlCommand with " ' " single quote when i try this i got the error msg
Unclosed quotation mark before the character string ')'.
insert into service(problem) values('Receipt Printer Can't Work') where service_id = 112;
help me Very Urgent
.ctl00_Menu1_0 { background-color:white;visibility:hidden;display:none;position:absolute;left:0px;top:0px; }
.ctl00_Menu1_1 { color:#284E98;font-family:Verdana;font-size:0.8em;text-decoration:none; }
.ctl00_Menu1_2 { color:#284E98;background-color:#B5C7DE;font-family:Verdana;font-size:0.8em;height:29px;width:100%; }
.ctl00_Menu1_3 { }
.ctl00_Menu1_4 { padding:2px 5px 2px 5px; }
.ctl00_Menu1_5 { }
.ctl00_Menu1_6 { padding:2px 0px 2px 0px; }
.ctl00_Menu1_7 { }
.ctl00_Menu1_8 { background-color:#507CD1; }
.ctl00_Menu1_9 { }
.ctl00_Menu1_10 { background-color:#507CD1; }
.ctl00_Menu1_11 { color:White; }
.ctl00_Menu1_12 { color:White;background-color:#284E98; }
.ctl00_Menu1_13 { color:White; }
.ctl00_Menu1_14 { color:White;background-color:#284E98; }
I try to get values from database table name GoldPriceRecord , and display them in a simple labels text. However, i receive the following error:
Error:
Unclosed quotation mark before the character string ''.
Public LondonDateTime As Date
Public CommodityPrice As String
Public SysDate As DatePublic CommodityPrice_Check As String
Protected Sub Page_Load(ByVal sender As Object, _ ByVal e As System.EventArgs)
Dim strsql1 As String
Dim strsql2 As String
strsql1 = "SELECT TOP 1 Bid, SystemDate FROM GOLDBEST.DBO.GoldPriceRecord ORDER BY SystemDate DESC'"
SetGoldPriceRecord(strsql1)
fdsa1.Text = LondonDateTime
fdsa2.Text = CommodityPrice
fdsa3.Text = SysDate
fdsa4.Text = CommodityPrice_Check
End Sub
Public Function SetGoldPriceRecord(ByVal strsql As String)
Dim connString As String = _ConfigurationManager.ConnectionStrings("Local_LAConnectionString1").ConnectionString
Using myConnection As New SqlConnection(connString)Dim myCommand As New SqlDataAdapter(strsql, myConnection)
Dim DS As New DataSetmyCommand.Fill(DS, "GoldPriceRecord") <---- Error
CommodityPrice = CType(DS.Tables(0).Rows(0)("Bid"), String)CommodityPrice_Check = CType(DS.Tables(0).Rows(0)("Bid"), String)
SysDate = CType(DS.Tables(0).Rows(0)("SystemDate"), Date)LondonDateTime = CType(DS.Tables(0).Rows(0)("SystemDate"), Date)
myConnection.Close()
End UsingReturn LondonDateTime
Return CommodityPriceReturn SysDate
Return CommodityPrice_Check
End Function
<form id="form1" runat="server">
<p><asp:Label ID="fdsa1" runat="server" /></p>
<p><asp:Label ID="fdsa2" runat="server" /></p>
<p><asp:Label ID="fdsa3" runat="server" /></p>
<p><asp:Label ID="fdsa4" runat="server" /></p>
</form>
I have a view. When I select data, I have to use double quotation on selected column name, for instance:
select "assy_no" from myView;
if I try
select assy_no from myView
I will get error: ORA-00904: "ASSY_NO": invalid identifier
When I query on table, double quotation is not required.
Why the column name on view requires double quotation?
Hi,
My first post. I am trying to run a select query to a linked server (Cache database on VMS OS) from Microsoft SQL 2000 Query Analyzer.
Here is my problem query:
SELECT *
FROM OPENROWSET('MSDASQL',
'DSN=SHADOW',
'SELECT * FROM Member_Acct WHERE close_dt > {d'2007-07-01'}'
I am getting this error:
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '2007'.
I have tried using double quotes and everythingelse I could. Database expect me to send date in this format {d'YYYY-MM-DD'}.
Please help me.
Hi,
I am getting the error "Unclosed quotation mark after the character string ')" with this code:
Code Snippet
cmd = New SqlCommand("INSERT INTO PRODUCT VALUES ('" & Me.TextBox1.Text & _
"','" & Me.TextBox2.Text & "','" & Me.TextBox3.Text & "','" & Me.ComboBox.Text & _
"','" & Me.TextBox4.Text & "'"")", conn)
Do anyone know what's going wrong around Me.TextBox4.Text. Thanks.
Hi There,
I'm having a little trouble with a large query I have written to be sent as an email.
I want to send it as a csv so I've added commas in between each value in the query.
This is causing an error though, I think the ' that surround the commas are closing the query early. Is there any way around this? like encapsulating the query?
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'ggsgn'
,@recipients = 'sgsdm'
,@subject = 'Nsgdday'
,@attach_query_result_as_file = 1
,@body_format = 'text'
,@query ='
use sdgsgdgs
select
a1.c#,',', client,',', fr,',', tpe,',', dateReq as DateRequested,',', ... '
I have an app that I created and I am trying to upload the MS SQL DB to my web host. I downloaded MS SQL Server Database Publishing Wizard and then open up Visual Studio 2005. I right click my database, and then try publishing it. I convert to a MS 2000 DB (was originally 2005). I save the .sql statement and try copying into the host's (Godaddy) query analyzer. (It's only about 400k).
Well, I tried cutting and pasting the doc so I could see exactly where I get the error. I get a good part of it successful, but then I try the pasting the code below (not modified at all) and get the error about "unclosed quotation..." I skip this and go to the next process, and is successful, but often, I am getting this error.... WHY?
Please help.
Code copied directly from generated script from MS Publishing wizard:
/****** Object: StoredProcedure [dbo].[aspnet_Users_DeleteUser] Script Date: 02/07/2007 18:34:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[aspnet_Users_DeleteUser]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[aspnet_Users_DeleteUser]
@ApplicationName nvarchar(256),
@UserName nvarchar(256),
@TablesToDeleteFrom int,
@NumTablesDeletedFrom int OUTPUT
AS
BEGIN
DECLARE @UserId uniqueidentifier
SELECT @UserId = NULL
SELECT @NumTablesDeletedFrom = 0
DECLARE @TranStarted bit
SET @TranStarted = 0
IF( @@TRANCOUNT = 0 )
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END
ELSE
SET @TranStarted = 0
DECLARE @ErrorCode int
DECLARE @RowCount int
SET @ErrorCode = 0
SET @RowCount = 0
SELECT @UserId = u.UserId
FROM dbo.aspnet_Users u, dbo.aspnet_Applications a
WHERE u.LoweredUserName = LOWER(@UserName)
AND u.ApplicationId = a.ApplicationId
AND LOWER(@ApplicationName) = a.LoweredApplicationName
IF (@UserId IS NULL)
BEGIN
GOTO Cleanup
END
-- Delete from Membership table if (@TablesToDeleteFrom & 1) is set
IF ((@TablesToDeleteFrom & 1) <> 0 AND
(EXISTS (SELECT name FROM sysobjects WHERE (name = N''vw_aspnet_MembershipUsers'') AND (type = ''V''))))
BEGIN
DELETE FROM dbo.aspnet_Membership WHERE @UserId = UserId
SELECT @ErrorCode = @@ERROR,
@RowCount = @@ROWCOUNT
IF( @ErrorCode <> 0 )
GOTO Cleanup
IF (@RowCount <> 0)
SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
END
-- Delete from aspnet_UsersInRoles table if (@TablesToDeleteFrom & 2) is set
IF ((@TablesToDeleteFrom & 2) <> 0 AND
(EXISTS (SELECT name FROM sysobjects WHERE (name = N''vw_aspnet_UsersInRoles'') AND (type = ''V''))) )
BEGIN
DELETE FROM dbo.aspnet_UsersInRoles WHERE @UserId = UserId
SELECT @ErrorCode = @@ERROR,
@RowCount = @@ROWCOUNT
IF( @ErrorCode <> 0 )
GOTO Cleanup
IF (@RowCount <> 0)
SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
END
-- Delete from aspnet_Profile table if (@TablesToDeleteFrom & 4) is set
IF ((@TablesToDeleteFrom & 4) <> 0 AND
(EXISTS (SELECT name FROM sysobjects WHERE (name = N''vw_aspnet_Profiles'') AND (type = ''V''))) )
BEGIN
DELETE FROM dbo.aspnet_Profile WHERE @UserId = UserId
SELECT @ErrorCode = @@ERROR,
@RowCount = @@ROWCOUNT
IF( @ErrorCode <> 0 )
GOTO Cleanup
IF (@RowCount <> 0)
SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
END
-- Delete from aspnet_PersonalizationPerUser table if (@TablesToDeleteFrom & 8) is set
IF ((@TablesToDeleteFrom & 8) <> 0 AND
(EXISTS (SELECT name FROM sysobjects WHERE (name = N''vw_aspnet_WebPartState_User'') AND (type = ''V''))) )
BEGIN
DELETE FROM dbo.aspnet_PersonalizationPerUser WHERE @UserId = UserId
SELECT @ErrorCode = @@ERROR,
@RowCount = @@ROWCOUNT
IF( @ErrorCode <> 0 )
GOTO Cleanup
IF (@RowCount <> 0)
SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
END
-- Delete from aspnet_Users table if (@TablesToDeleteFrom & 1,2,4 & 8) are all set
IF ((@TablesToDeleteFrom & 1) <> 0 AND
(@TablesToDeleteFrom & 2) <> 0 AND
(@TablesToDeleteFrom & 4) <> 0 AND
(@TablesToDeleteFrom & 8) <> 0 AND
(EXISTS (SELECT UserId FROM dbo.aspnet_Users WHERE @UserId = UserId)))
BEGIN
DELETE FROM dbo.aspnet_Users WHERE @UserId = UserId
SELECT @ErrorCode = @@ERROR,
@RowCount = @@ROWCOUNT
IF( @ErrorCode <> 0 )
GOTO Cleanup
IF (@RowCount <> 0)
SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1
END
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
COMMIT TRANSACTION
END
RETURN 0
Cleanup:
SET @NumTablesDeletedFrom = 0
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
ROLLBACK TRANSACTION
END
RETURN @ErrorCode
END'
END
GO
***************************ERROR:****************************
Unclosed quotation mark before the character string 'CREATE PROCEDURE [dbo].[aspnet_Users_DeleteUser] @ApplicationName nvarchar(256), @UserName nvarchar(256), @TablesToDeleteFrom int, @NumTablesDeletedFrom int AS BEGIN DECLARE @UserId uniqueidentifier SELECT @UserId = NULL SELECT @NumTablesDeletedFrom = 0 DECLARE @TranStarted bit SET @TranS...
/****** Object: StoredProcedure [dbo].[aspnet_Users_DeleteUser] Script Date: 02/07/2007 18:34:18 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER OFFGOIF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[aspnet_Users_DeleteUser]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)BEGINEXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE [dbo].[aspnet_Users_DeleteUser] @ApplicationName nvarchar(256), @UserName nvarchar(256), @TablesToDeleteFrom int, @NumTablesDeletedFrom int ASBEGIN DECLARE @UserId uniqueidentifier SELECT @UserId = NULL SELECT @NumTablesDeletedFrom = 0 DECLARE @TranStarted bit SET @TranStarted = 0 IF( @@TRANCOUNT = 0 ) BEGIN BEGIN TRANSACTION SET @TranStarted = 1 END ELSESET @TranStarted = 0 DECLARE @ErrorCode int DECLARE @RowCount int SET @ErrorCode = 0 SET @RowCount = 0 SELECT @UserId = u.UserId FROM dbo.aspnet_Users u, dbo.aspnet_Applications a WHERE u.LoweredUserName = LOWER(@UserName) AND u.ApplicationId = a.ApplicationId AND LOWER(@ApplicationName) = a.LoweredApplicationName IF (@UserId IS NULL) BEGIN GOTO Cleanup END -- Delete from Membership table if (@TablesToDeleteFrom & 1) is set IF ((@TablesToDeleteFrom & 1) <> 0 AND (EXISTS (SELECT name FROM sysobjects WHERE (name = N''vw_aspnet_MembershipUsers'') AND (type = ''V'')))) BEGIN DELETE FROM dbo.aspnet_Membership WHERE @UserId = UserId SELECT @ErrorCode = @@ERROR, @RowCount = @@ROWCOUNT IF( @ErrorCode <> 0 ) GOTO Cleanup IF (@RowCount <> 0) SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1 END -- Delete from aspnet_UsersInRoles table if (@TablesToDeleteFrom & 2) is set IF ((@TablesToDeleteFrom & 2) <> 0 AND (EXISTS (SELECT name FROM sysobjects WHERE (name = N''vw_aspnet_UsersInRoles'') AND (type = ''V''))) ) BEGIN DELETE FROM dbo.aspnet_UsersInRoles WHERE @UserId = UserId SELECT @ErrorCode = @@ERROR, @RowCount = @@ROWCOUNT IF( @ErrorCode <> 0 ) GOTO Cleanup IF (@RowCount <> 0) SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1 END -- Delete from aspnet_Profile table if (@TablesToDeleteFrom & 4) is set IF ((@TablesToDeleteFrom & 4) <> 0 AND (EXISTS (SELECT name FROM sysobjects WHERE (name = N''vw_aspnet_Profiles'') AND (type = ''V''))) ) BEGIN DELETE FROM dbo.aspnet_Profile WHERE @UserId = UserId SELECT @ErrorCode = @@ERROR, @RowCount = @@ROWCOUNT IF( @ErrorCode <> 0 ) GOTO Cleanup IF (@RowCount <> 0) SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1 END -- Delete from aspnet_PersonalizationPerUser table if (@TablesToDeleteFrom & 8) is set IF ((@TablesToDeleteFrom & 8) <> 0 AND (EXISTS (SELECT name FROM sysobjects WHERE (name = N''vw_aspnet_WebPartState_User'') AND (type = ''V''))) ) BEGIN DELETE FROM dbo.aspnet_PersonalizationPerUser WHERE @UserId = UserId SELECT @ErrorCode = @@ERROR, @RowCount = @@ROWCOUNT IF( @ErrorCode <> 0 ) GOTO Cleanup IF (@RowCount <> 0) SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1 END -- Delete from aspnet_Users table if (@TablesToDeleteFrom & 1,2,4 & 8) are all set IF ((@TablesToDeleteFrom & 1) <> 0 AND (@TablesToDeleteFrom & 2) <> 0 AND (@TablesToDeleteFrom & 4) <> 0 AND (@TablesToDeleteFrom & 8) <> 0 AND (EXISTS (SELECT UserId FROM dbo.aspnet_Users WHERE @UserId = UserId))) BEGIN DELETE FROM dbo.aspnet_Users WHERE @UserId = UserId SELECT @ErrorCode = @@ERROR, @RowCount = @@ROWCOUNT IF( @ErrorCode <> 0 ) GOTO Cleanup IF (@RowCount <> 0) SELECT @NumTablesDeletedFrom = @NumTablesDeletedFrom + 1 END IF( @TranStarted = 1 ) BEGIN SET @TranStarted = 0 COMMIT TRANSACTION END RETURN 0Cleanup: SET @NumTablesDeletedFrom = 0 IF( @TranStarted = 1 ) BEGIN SET @TranStarted = 0 ROLLBACK TRANSACTION END RETURN @ErrorCodeEND' ENDGO
Thanks in advance,
Rob
Hello,
Can anybody help me with this error , System.Data.SqlClient.SqlException. "Line 1: Incorrect Syntax near :'m' ."
I have a textbox named DESCRIPTION and am trying to insert the values entered by user in this textbox.
It works well if a user types " I am unable to " but it throws this error when a user types " I'm unable to "...because of I'm ..
How do i fix this?
My Query:
strSQL = "Insert into ABC(DESCRIPTION) values('" & strDescription & "')"
When i try below query in query analyser , am getting this error ....
Error 1: [Line 1: Incorrect syntax near 'm'.]
Error 2: [ Unclosed quotation mark before the character string '.]
update ABC set DESCRIPTION = 'I'm unable' WHERE ABC_ID = '142'
Thanks to reply.
<WebMethod()> Public Function getCertificateInformation( _
ByVal cerNumber As String, _
ByVal StudentID As String) As DataSet
Dim cnn As New SqlConnection( _
"user id=sa;password=;" & _
"initial catalog=uni2;data source=(local)")
Dim strSQL As String
strSQL = "SELECT cerNumber, name," _
& " address, stId, year FROM Msc" _
& " WHERE cerNumber='" & cerNumber _
& "' AND stId='" & StudentID
when running this i am getting an error "System.Data.SqlClient.SqlException: Unclosed quotation mark before the character string '12'". isn't the sql statement correct? need help plz!
** 12 one of the input