Connection String Has Semicolon (;) - How On Earth Can I Get This Working?
Aug 3, 2007
Ok, here's my setup. I've got a named instance in a SQL 2000 cluster. I only have dbo rights on my database, because it is a shared infrastructure. Here's my current web.config connection string (the meat, anyway):
When I'm at the office, this is my connection string, pretty normal:
connectionString="Data Source=ServerNameInstanceName;Initial Catalog=blah..."
But, when I connect through the VPN, I can't just connect through the named instance - I have a specific port. This is where things get odd.
First, if I try to connect through SQL Server Management Studio (2005), i get nothing. If I try to connect using "ServerNameInstanceName, (comma) Port Number" it connects, but not to my instance. I get a seperate set of databases that I believe are in the default instance. So, I changed the comma to a semicolon (;) - and it still connected to the same thing - connected to the database, but to the wrong set of databases. So, on a whim, I tried plunking my string, which was now "ServerNameInstanceName;(semicolon) PortNumber" into the SQL 2000 Tools and it worked in both Query Analyzer and in Enterprise Manager. So, I thought, I'll just slam this into my connection string and all will be well. No. I can't use a semicolon in my connection string, and I can't find an escape character to use. Double semicolons don't work, a comma doesn't connect me properly, double colons don't work, the JDBC brackets don't work {} - so I'm at a loss. I'm out of ideas. I've set up aliases, and those don't work earlier.
I'm using ASP.net 2 with VB & C# and Visual Studio 2005 Professional. Thanks for any help anyone can give on this!
View 3 Replies
ADVERTISEMENT
Mar 17, 2007
I have the Visual Studio 2005 Team Suite trial version and the SQL Server 2005 trial (which came with team suite) installed in my machine.
When I installed the SQL Server 2005 I installed it in the Windows Authentication mode. So every time I open SQL Server 2005 it doesnt ask for Username/Password (Its greyed out). The only thing available were:
Server Type: Database Engine
ServerName: ServerSue
Authentication: Windows Authentication.
Here is my Problem:
I created a small application in C#. In the web.config file I created the following:
<appSettings>
<add key="resumecon" value="SERVER=ServerSue;database=Resume;"/>
</appSettings>
Then in the Code behind I created the following string:
string strResCon = ConfigurationManager.AppSettings["resumecon"].ToString();
Then in method (for a Login page) I created the connection string as follows and wrote code to insert some values into the Resume database. I called this method on a button click event.
SqlConnection conLogin = new SqlConnection(strResCon);
When I run the page and when I click the button I get the following error:
Login failed for user ''. The user is not associated with a trusted SQL Server connection.
I think some thing is wrong in the addkey of webconfig? How do I change this?
View 5 Replies
View Related
Apr 3, 2007
Here is the connect tring from table properties:ODBC;DRIVER=SQL Server;SERVER=VICRAUCHSRVRVGSMISC;APP=Microsoft Data Access Components;WSID=VICRAUCH;DATABASE=vgs_prod;TABLE=d bo.USysCandidatesHere is the connect string from the ADO .Open connect string:"ODBC;DRIVER=SQL Server;SERVER=VICRAUCHSRVRVGSMISC;APP=Microsoft Data Access Components;UID=sa;PWD=xxxXX99X;WSID=VICRAUCH;DATAB ASE=vgs_prod"Here is the ADO .Open code. Set CNN = New ADODB.ConnectionDim strDEFConn As StringstrDEFConn = FixConnStr(DEFCONN)CNN.Open strDEFConn The last line fails with the CNN.Open strDEFConn with this message:Run-time error '-2147467259 (80004005)';[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified This code works when the SQL Server is on it's own server, but for testing at my own office, I have SQL Server on the same machine as the Access application. I'm getting the above error where SQL Server and the Access app are on the same machine. I can open a linked SQL table from the user interface, and VBA code that deals with the tables as Access tables works. It is the ADO .Open statement where the error happens. Thanks for any help you can give me on getting this to work.
View 5 Replies
View Related
Dec 5, 2013
I have 8 fields - I have requirement to concatenate using '+' operator with semicolon delimiter but issues is in the
Output I get semicolons for the fields that are empty below is my code :
-------------
case
when [SLII Request Type] ='Job Posting' and [SmartLaborII Request Status] like 'Pending Approval (Level 4%'
and [New Extension or Replacement Audit Flag] like 'FLAG%'
then 'Reject – New, Extension, Replacement invalid entry' --'it is jp'
else ''
end as [ES Fully approved data 1],
case
[Code] ....
View 6 Replies
View Related
Aug 23, 2006
I added a connection (ADO.NET) object by name testCon in the connection manager - I wanted to programmatically supply the connection string. So I used the "Expressions" property of the connection object and set the connectionstring to one DTS variable. The idea is to supply the connection string value to the variable - so that the connection object uses my connection string.
Then I added a "Backup Database Task" to my package with the name BkpTask. Now whenever I try to set the connection property of BkpTask to the testCon connection object, by typing testCon, it automatically gets cleared. I am not able to set the connection value.
Then after spending several hours I found that this is because I have customized the connection string in testCon. If I don't customize the connection string, I am able to enter the "testCon" value in the connection property of the BkpTask.
Is this an intrinsic issue?
View 2 Replies
View Related
Jan 14, 2004
im using the free asp.net web matrix.. and i have sql server installed on my local machine
i have looked all over the web to find how.. and cant find any.. does anyone actually have code to show how to do this?
please help someone
View 2 Replies
View Related
Aug 15, 2006
I have two lists of contacts. They're similar. I want a list of all the contacts whose email address occurs only in the first list.
SELECT COUNT(DISTINCT EMAIL) FROM List1
returns 13460
SELECT COUNT(DISTINCT EMAIL) FROM List2
returns 13220
SELECT EMAIL FROM List1 WHERE EMAIL NOT IN (SELECT DISTINCT EMAIL FROM List2)
returns 0 rows
How can it be returning no rows? What am I failing to take into consideration?
:confused:
View 6 Replies
View Related
Apr 20, 2007
I am really trying to be very understanding/patience/tolerant here but this is just really annoying. Why does the green line in Intergration Services just never go straight?!?!?!?!?!??? I mean what is the ultimate purpose of having a tilted line? it just makes your package looks like it was done in 2 minutes and hasn't been sought after.
A very disgusted,
BI Developer
View 8 Replies
View Related
Sep 27, 2007
Hi,
I'm having a BIG problem, this is my 3rd day looking for answer !I'm trying to create a custom membership provider with MS SQL database 2005 but not the default ASPNETDB. I've changed the web.config to be as following:<connectionStrings><add name="sqlConn" connectionString="Data Source=.;Integrated Security=True;Initial Catalog=ASPNETDB;"providerName="System.Data.SqlClient" /></connectionStrings> <system.web><trace enabled="true" /><roleManager enabled="true" /><authentication mode="Forms" />
<membership defaultProvider="MySqlProvider"><providers><remove name="AspNetSqlProvider"/><add name="MySqlProvider" connectionStringName="SqlConn" type="System.Web.Security.SqlMembershipProvider" applicationName="/" /></providers></membership>
But when I try to login to the site using the login control the following error occurs:
Server Error in '/etest' Application.
Configuration Error Description: An error occurred during the processing of a configuration file required to service this request. Please review the specific error details below and modify your configuration file appropriately. Parser Error Message: The connection name 'LocalSqlServer' was not found in the applications configuration or the connection string is empty.Source Error:
Line 149: <roleManager>
Line 150: <providers>
Line 151: <add name="AspNetSqlRoleProvider" connectionStringName="LocalSqlServer" applicationName="/" type="System.Web.Security.SqlRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
Line 152: <add name="AspNetWindowsTokenRoleProvider" applicationName="/" type="System.Web.Security.WindowsTokenRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
Line 153: </providers>Source File: C:WINDOWSMicrosoft.NETFrameworkv2.0.50727Configmachine.config Line: 151
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.213
It is clear that this is a prblem with my Machine.Config file - since I've "worked!" on this file for a while. But when I've checked the Machine.config file I've found the LocalSqlServer connection it is talking about! .
I'm lost and I dunno what to do, can anyone help?
Here is the mahine.config in case if you need it:<connectionStrings>
<add name="LocalSqlServer" connectionString="data source=.;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient" /></connectionStrings>
<system.data><DbProviderFactories>
<add name="Odbc Data Provider" invariant="System.Data.Odbc" description=".Net Framework Data Provider for Odbc" type="System.Data.Odbc.OdbcFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" /><add name="OleDb Data Provider" invariant="System.Data.OleDb" description=".Net Framework Data Provider for OleDb" type="System.Data.OleDb.OleDbFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
<add name="OracleClient Data Provider" invariant="System.Data.OracleClient" description=".Net Framework Data Provider for Oracle" type="System.Data.OracleClient.OracleClientFactory, System.Data.OracleClient, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" /><add name="SqlClient Data Provider" invariant="System.Data.SqlClient" description=".Net Framework Data Provider for SqlServer" type="System.Data.SqlClient.SqlClientFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
<add name="SQL Server CE Data Provider" invariant="Microsoft.SqlServerCe.Client" description=".NET Framework Data Provider for Microsoft SQL Server 2005 Mobile Edition" type="Microsoft.SqlServerCe.Client.SqlCeClientFactory, Microsoft.SqlServerCe.Client, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" /></DbProviderFactories>
</system.data><system.web>
<processModel autoConfig="true" /><httpHandlers />
<membership><providers>
<add name="AspNetSqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" connectionStringName="LocalSqlServer" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="true" applicationName="/" requiresUniqueEmail="false" passwordFormat="Hashed" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="7" minRequiredNonalphanumericCharacters="1" passwordAttemptWindow="10" passwordStrengthRegularExpression="" /></providers>
</membership><profile>
<providers><add name="AspNetSqlProfileProvider" connectionStringName="LocalSqlServer" applicationName="/" type="System.Web.Profile.SqlProfileProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
</providers></profile>
<roleManager><providers>
<add name="AspNetSqlRoleProvider" connectionStringName="LocalSqlServer" applicationName="/" type="System.Web.Security.SqlRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" /><add name="AspNetWindowsTokenRoleProvider" applicationName="/" type="System.Web.Security.WindowsTokenRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
</providers></roleManager>
</system.web>
View 8 Replies
View Related
Sep 16, 2015
I am using sqljdbc41.jar to connect with MSSQL database, it is working fine on my local machine.Where as on the remote server, same class giving me error
Login failed for user '<domain><windows loginID>'
My connection string is URL...
I am using sqljdbc41.jar and on 64 bit processor , I am using following command which included path for sqljdbc_auth.dll java -Djava.library.path= C: sqljdbc_4.1enuauthx64 TestDao and error is Login failed for user '<domain><windows loginID>' why it is not picking up username passed in connection string. I have 2 machines, one is local and other is remote. on both machine I login using my domain, it is working absolutely fine on local then why the error is coming on remote machine.Both the machines are identical.
View 4 Replies
View Related
Jan 18, 2008
I have been trying to host my website on Go Daddy for about 3 weeks and I have cleared several problems but this one remains. I can get into the ASPNETDB database for doing logins , etc but I cant access my database called "PINEmgt".
To try to understand where the problem is located,I built a very simple application without login controls and put on it one Detailform accessing a single line table "Signin". I continually get the error message I got with my real app :"The connection name 'PINEMgtConnectionString2' was not found in the applications configuration or the connection string is empty. " There is only one connection string in my app -'PINEMgtConnectionString2'. When I run the app locally on my machine it works. After I move everything to Go Daddy, I get the error message and the following dump"
Line 24: </Fields>
Line 25: </aspetailsView>
Line 26: <aspqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStringsINEMgtConnectionString2 %>"
Line 27: InsertCommand="INSERT INTO Signup(first_name, last_name, email, customer_id) VALUES (,,,)"
Line 28: SelectCommand="SELECT * FROM [Signup]"></aspqlDataSource>
This is indeed the code from my single page app.
The dump is:
[InvalidOperationException: The connection name 'PINEMgtConnectionString2' was not found in the applications configuration or the connection string is empty.]
System.Web.Compilation.ConnectionStringsExpressionBuilder.GetConnectionString(String connectionStringName) +3039085
ASP.default_aspx.__BuildControlSqlDataSource1() in d:hostinguck7scoutDefault.aspx:26
ASP.default_aspx.__BuildControlform1() in d:hostinguck7scoutDefault.aspx:10
ASP.default_aspx.__BuildControlTree(default_aspx __ctrl) in d:hostinguck7scoutDefault.aspx:1
ASP.default_aspx.FrameworkInitialize() in d:hostinguck7scoutDefault.aspx.vb:912306
System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +40
System.Web.UI.Page.ProcessRequest() +86
System.Web.UI.Page.ProcessRequestWithNoAssert(HttpContext context) +18
System.Web.UI.Page.ProcessRequest(HttpContext context) +49
ASP.default_aspx.ProcessRequest(HttpContext context) +29
System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +303
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +64
After Googleing, asking Go Daddy for info (which they dont give) , etc for three weeks, I need serious mental help
in the form of a solution of this problem. Your help with the problem or the name of a good mental health professional in eastern N.C. (maybe both) would be appreciated.
View 10 Replies
View Related
May 30, 2008
I have seen another thread with this same problem, but the problem seemed to be a typo. I am getting an error when I try to access the web site ..
The connection name 'USASH-AS0013' was not found in the applications configuration or the connection string is empty.
However, in my web.config file I do have a connection named USASH-AS0013. Here is my web.config file (with my user/pwd hidden). Any help would be greatly appreciated.
<?xml version="1.0"?>
<!--
Note: As an alternative to hand editing this file you can use the
web admin tool to configure settings for your application. Use
the Website->Asp.Net Configuration option in Visual Studio.
A full list of settings and comments can be found in
machine.config.comments usually located in
WindowsMicrosoft.NetFrameworkv2.xConfig
-->
<configuration>
<appSettings/>
<connectionStrings>
<add name="USASH-AS0013" connectionString="Provider=SQLOLEDB;Data Source=USASH-AS0013;User ID=****;Password=*****;Initial Catalog="Smart Factory""
providerName="System.Data.OleDb" />
</connectionStrings>
<system.web>
<!--
Set compilation debug="true" to insert debugging
symbols into the compiled page. Because this
affects performance, set this value to true only
during development.
Visual Basic options:
Set strict="true" to disallow all data type conversions
where data loss can occur.
Set explicit="true" to force declaration of all variables.
-->
<compilation debug="true" strict="false" explicit="true"/>
<pages theme="MySkin" >
<namespaces>
<clear/>
<add namespace="System"/>
<add namespace="System.Collections"/>
<add namespace="System.Collections.Specialized"/>
<add namespace="System.Configuration"/>
<add namespace="System.Text"/>
<add namespace="System.Text.RegularExpressions"/>
<add namespace="System.Web"/>
<add namespace="System.Web.Caching"/>
<add namespace="System.Web.SessionState"/>
<add namespace="System.Web.Security"/>
<add namespace="System.Web.Profile"/>
<add namespace="System.Web.UI"/>
<add namespace="System.Web.UI.WebControls"/>
<add namespace="System.Web.UI.WebControls.WebParts"/>
<add namespace="System.Web.UI.HtmlControls"/>
</namespaces>
</pages>
<!--
The <authentication> section enables configuration
of the security authentication mode used by
ASP.NET to identify an incoming user.
-->
<authentication mode="Windows"/>
<!--
The <customErrors> section enables configuration
of what to do if/when an unhandled error occurs
during the execution of a request. Specifically,
it enables developers to configure html error pages
to be displayed in place of a error stack trace.
<customErrors mode="RemoteOnly" defaultRedirect="GenericErrorPage.htm">
<error statusCode="403" redirect="NoAccess.htm" />
<error statusCode="404" redirect="FileNotFound.htm" />
</customErrors>
-->
</system.web>
</configuration>
View 3 Replies
View Related
Feb 19, 2015
I have a child package where the ConnectionString property of a Connection Manager is set by a Parent Package Variable Configuration. I set up a script task that brings up a message box with the value of the ConnectionString property right before the dataflow task.
MessageBox.Show(Dts.Connections["CPU_*"].ConnectionString.ToString());When I run the parent package, the message box shows that the connection string is changing with every iteration, but in the dataflow it always draws the data from the same source.
The connection manager is an ADO.Net type, RetainSameConnection is set to False, and I've been researching this for days.
(Update 2/23/2015): To make this stranger, when I look at the diagnostic logs, they tell me that when the new connections are being opened they are using the new connection strings.
View 2 Replies
View Related
Jan 4, 2006
I came across something strange today. I was wondering I was doing something wrong.
View 7 Replies
View Related
Jul 3, 2007
This is the first time I have used SSIS, so please bear with the ignorance.
I have a super simple package that inserts x000's of rows into a temporary table. The data source is a file that the user will upload. I need to be able to tell the package what file to upload. I'm thinking the simplest thing would be to edit the connectionString property of the SourceConnectionFlatFile at runtime. Is this possible? What form should the file path be in (UNC, other)? And, are there any other considerations I should be aware of?
Thanks!
View 1 Replies
View Related
Jan 26, 2007
<add name="NORTHWNDConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|NORTHWND.MDF;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" />
Above is my cnn string that is not working, it might be du to that m using SQLserver standard version ............
Do I have to change the cnn string.?
View 3 Replies
View Related
Mar 23, 2008
Hi,
I have a textbox,in which I am allowing user to write the username starting with,user should enter minimum 3 characters, atleast,and when they enter that information, all the names starting with those 3 characters shouls be shown.can any one help me in this.
my query is:
"select username from registration where username like '" + TextBox1.Text + "%'";
It is working with one char,but if I write more than one character in the textbox,it is not creating any error but it is not returning the result.
View 2 Replies
View Related
Apr 27, 2005
Code:
<%@Language = "VBScript"%>
<%Option Explicit%>
<%
dim oRs,oConn,dateofleave,sql,uid
dateofleave = trim(request.querystring("leavedate"))
uid = trim(request.querystring("employeeID"))
set oConn = Server.CreateObject("ADODB.Connection")
oConn.Provider="Microsoft.Jet.OLEDB.4.0"
oConn.Open(Server.MapPath("test.mdb"))
set oRs = Server.CreateObject("ADODB.Recordset")
sql ="INSERT INTO test (dateofleave)"
sql = sql & "VALUES ('&dateofleave') WHERE employeeID=&uid"
set oRs = oConn.execute(sql)
%>
can someone help me with this error?
Microsoft JET Database Engine- Error '80040e14'
Missing semicolon ( at end of SQL statement.
/test/booking.asp, line 18
where line 18 is "set oRs = oConn.execute(sql)"
View 4 Replies
View Related
Apr 18, 2008
Hi all......
I have an issue
There are 2 tables... source and target...
Data from source goes into target table under same field...
ISSUE IS -
Data from 'n' no. of records from source table goes into a single record in the target table with delimiter being a semi colon (;)...
where(n >=2)...
For example - if the source table has 'src1', 'src2' and 'src3' as the data then target table will have a single record with semicolon as delimiter as 'src1;src2;src3'
How do we compare the data under this particular field now...
Do we have to use a if then loop for identifying when the ; ends in target data...
kindly help by giving a example...
Thanks,
Avi.
View 1 Replies
View Related
Feb 25, 2007
hi friends,
I have search in my project based on strings(surname, firstname).. but it does'nt works wen name consists of only two letters.... could somebody please help me on this.
Thanks sandeep
View 3 Replies
View Related
Jan 3, 2005
In the below section of code I'm connecing to a SQL database and when I use the "=" in the search it works ok, but when I try to add the ability to use wildcards by changin "=" to "LIKE" its not working
Working code:
Function GetName(ByVal first_Name As String) As System.Data.DataSet
Dim connectionString As String = "server='(local)'; trusted_connection=true; database='FamilyInfo'"
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "SELECT Names.* FROM Names WHERE (Names.First_Name = @First_Name)"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection
Dim dbParam_first_Name As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_first_Name.ParameterName = "@First_Name"
dbParam_first_Name.Value = first_Name
dbParam_first_Name.DbType = System.Data.DbType.StringFixedLength
dbCommand.Parameters.Add(dbParam_first_Name)
Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.SqlClient.SqlDataAdapter
dataAdapter.SelectCommand = dbCommand
Dim dataSet As System.Data.DataSet = New System.Data.DataSet
dataAdapter.Fill(dataSet)
Return dataSet
End Function
Code that I would like to work
Function GetName(ByVal first_Name As String) As System.Data.DataSet
Dim connectionString As String = "server='(local)'; trusted_connection=true; database='FamilyInfo'"
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "SELECT Names.* FROM Names WHERE (Names.First_Name LIKE '%' + @First_Name '+')"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection
Dim dbParam_first_Name As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_first_Name.ParameterName = "@First_Name"
dbParam_first_Name.Value = first_Name
dbParam_first_Name.DbType = System.Data.DbType.StringFixedLength
dbCommand.Parameters.Add(dbParam_first_Name)
Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.SqlClient.SqlDataAdapter
dataAdapter.SelectCommand = dbCommand
Dim dataSet As System.Data.DataSet = New System.Data.DataSet
dataAdapter.Fill(dataSet)
Return dataSet
End Function
Any help would be greatly appreciated
View 3 Replies
View Related
Apr 10, 2004
Hey
I am trying to retieve a value from teh database and add one to it, then update the database with thenew value before redirecting to a page.
I am recieving this error and don't know why, i have the following coed below.
Dim objReaderQ as OleDBDataReader
Dim strSQLRead As String
Dim objCmd As New OleDbCommand
strSQLRead ="Select Quantity from tblCart Where (Productid=" & intProdidHold & ") AND (Cartid='" & strCartid & "')"
objCmd = new OleDbCommand(strSQLRead, objConn)
objReaderQ = objCmd.ExecuteReader()
if objReaderQ.Read()
'update quantity by 1
Dim i as integer
i = objReaderQ("quantity")
i = i + 1
objReaderQ.Close()
Dim strSQLQuantity as String = "INSERT INTO tblCart (Quantity) VALUES (@quantity) WHERE (productid=" & intProdidHold & ") AND (Cartid='" & strCartid & "');"
Dim objCmdQuantity As New OleDbCommand(strSQLQuantity, objConn)
objCmdQuantity.Connection = objConn
objCmdQuantity.Parameters.Add("@quantity", OleDbType.VarChar, 255)
objCmdQuantity.Parameters("@quantity").Value = i
objCmdQuantity.ExecuteNonQuery() ' <--- Error Is Occuring On This Line
Response.Redirect("ViewBasket.aspx")
end if
I really can't see what is wrong as i have placed the semi colon it wanted at the end of the string.
Thanks you for your time
Chris
View 1 Replies
View Related
Sep 27, 2005
I'm working with an existing program that crafts an insert statement
from user input. The data is machine generated, though, and some
of it has semicolons. How can I "escape" those semicolons so that
I can insert them into the database? Does the ESCAPE key work
with the insert statement, too? Can I just replace the ";" with
some other character(s) to escape it? I'm not finding much in the
online help. Thanks!
View 5 Replies
View Related
Apr 13, 2014
did VARCHAR(20)
SELECT top 10 CAST(did AS INT) FROM table
Msg 245, Level 16, State 1, Line 3
Conversion failed when converting the varchar value 'cht6p575l7ry61k1npt' to data type int.
SELECT CONVERT(INT, did) FROM table
Msg 245, Level 16, State 1, Line 5
Conversion failed when converting the varchar value 'cht6p575l7ry61k1npt' to data type int.I found a SQL Function that removes all the alphabetic characters from the string and just leaves the integers.
View 4 Replies
View Related
Apr 14, 2007
Hi,
I Have a correct connectionstring and it connected the database without any problems but once I close the program, the problems start. When I reopen the solution, I recceive error messages as
The databse already exists, choose a different database name.
What does this mean?
What happened to the connectionstring?
View 3 Replies
View Related
Aug 30, 2007
Hello friends,
I am designing an ETL process in which I have a source column which contains names seprated by semicolon( i.e Rajat Kr Sharma;Mr Sammer;Mr Ravi;Mr Ankur Bhatnagar) in each row.
ETL process should create n records,one for each single name in destination table as n numbers of name apprear
in each row of source table.
The number of names can vary in each row of source table as per no. of delimited character semicolon.
So Can some one suggest me to lay out some plan and what controls of SSIS I should use?
Here I m giving the pictorial view of activity I'm trying to carryout.
Source Table Row
______________________________________________________________________
|Rajat Kr Sharma;Mr Sammer;Mr Ravi;Mr Ankur Bhatnagar |
|_____________________________________________________________________ |
Changes to Destination Table
______________________________________
|Rajat Kr Sharma |
|_____________________________________|
|Mr Sammer |
|_____________________________________|
|Mr Ravi |
|_____________________________________|
|Mr Mr Ankur Bhatnagar |
|_____________________________________|
View 3 Replies
View Related
Apr 28, 2008
when I backup the SQL Server 2005 database ,always get prompt
Error description: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'E'.
+48166A04.0004 [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
+48166A04.0004 [Microsoft][ODBC SQL Server Driver][SQL Server]The label 'E' has already been declared. Label names must be unique within a query batch or stored procedure.}
how to deal with it, thanks
View 5 Replies
View Related
Jan 26, 2008
Hello i constructed the following query in SQL management studio and it runs fine, exactly how i want it to. However when i run it in my code behind i get the error: 'Too many characters in character literal'
I desperatly need to use this query exactly how it displays in the mangement tool - why wont it work the same in my code behind?
(I am using c# in my code behind and binding the results of the query to a gridview)
Regards
"SELECT DISTINCT (dbo.AuthorList(publicationID))+ ';' + ISNULL(' Editor: '+Publication.editor, '')+ '' + ISNULL(' "'+Publication.title+'"', '')+ '' + ISNULL(' Book: "'+Publication.booktitle+'"', '')+ '' + ISNULL(' '+Publication.type, '')+ '' + ISNULL(' '+Publication.journal, '')+ '' + ISNULL(' Series: '+Publication.series+'.', '')+ '' + ISNULL(' '+Publication.school, '')+ '' + ISNULL(' '+Publication.institution, '')+ '' + ISNULL(' '+Publication.organisation, '')+ '' + ISNULL(' '+Publication.month, '')+ '' + ISNULL(' ('+convert(nvarchar, Publication.year)+')', '')+ '' + ISNULL(' '+Publication.publisher, '')+ '' + ISNULL(' '+Publication.address+'.', '')+ '' + ISNULL(' '+Publication.howpublished, '')+ '' + ISNULL(' Ed: '+convert(nvarchar, Publication.edition)+'.', '')+ '' + ISNULL(' '+Publication.note, '')+ '' + ISNULL(' Vol:'+Publication.volume+'.', '')+ '' + ISNULL(' No:'+Publication.number+'.', '')+ '' + ISNULL(' Pages:'+Publication.pages+'.', '')+ '' + ISNULL(' Chapter:'+Publication.chapter+'.', '') AS SingleColumn, Publication.publicationID FROM Publication;"
View 1 Replies
View Related
Jun 20, 2008
Hi, I'd like to know if there's a way to get sql server NOT to ignore the colon when performing a full text search (CONTAINS) for a string "sometext:". At this moment the query works, only the results are not narrowed to the ones containing the specified colon. I've read about this and I saw that these kind of characters (word breakers and stemmers) are ignored and want to know if there's a way to work around this (obviously performing well - so LIKE fails the test). Thanks
View 2 Replies
View Related
Jan 7, 2012
This SQL is meant to show the changes that will be made, when removing a selected user's email address from a batch.
However, when executed, each row is duplicated, and in the duplication, the semi-colon or comma isn't removed. For example, if I wanted to remove user "sam@mail.com"
The table results displayed would be:
Row 1:
BatchID: 50
ParamName:EmailTo
ParamValue: jack@mail.com;sam@mail.com;frank@mail.com
NewParamValue: jack@mail.com;frank@mail.com
Row 2:
BatchID: 50
ParamName:EmailTo
ParamValue: jack@mail.com;sam@mail.com;john@mail.com
NewParamValue: jack@mail.com;;frank@mail.com
Ideally, it should only display each row once, and not have the semicolon error. It seems to be a union error, because when I comment out the First and second union statements, it runs fine.
-- Delete email address from a.Batch
IF(@EmailAddress IS NOT NULL)
BEGIN
IF(LEN(@EmailAddress) > 0)
BEGIN
IF(@ShowOnly = 1)
[Code] ......
View 2 Replies
View Related
May 15, 2007
Good Morning,
I hope that I am just missing a step in the process somewhere. I have established a string variable called DatabaseServer and another called DatabaseName. In the OLE DB connectionstring expression I have the following:
Code Snippet
"Data Source=" + @[User::DatabaseServer] + ";Initial Catalog=" + @[User::DatabaseName] + ";Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;"
My understanding is that changing the variable from SERVERA to SERVERB should repoint the connection. (Test to pre-production to production in my current promotion environment.) This should also take effect in any dataflow task using the database connection above right? When I try to repoint the server variable (and only the server variable) I am either a.)throwing errors saying the original connection cannot be found in the data flow task, or b.)the job appears to be processing fine, but the repoint did not take place and data is still flowing to the original server.
Can anyone offer suggestions as to what I am missing?
The properties values in the DB connection:
ConnectionManagerType is OLEDB
DataSourceID is blank
DelayValidation is False (Setting to true changes nothing in my test)
Expression for ConnectionString is (see above)
RetainSameConnection is False
SupportsDTCTTransactions is True (and appears to be unchangeable.)
View 7 Replies
View Related
Oct 11, 2007
Hi all,
I use SqlHelper to connect to database. I need to set a timeout to execute some sp because I don´t want to wait for this sp if it make me wait a lot. So I set the Connection TimeOut every time I execute a sp. But this seems not working properly. I set this value to 5 and I execute a sp than runs in 15 but It waits for it.
Any idea
Best regards
www.ITCubo.net
View 1 Replies
View Related
Jun 14, 2008
My total test page is shown below. I monitor the connections by SP_WHO2. Without the second call, connection pooling seems to be working, ie I refresh my browser repeately but the number of connections as seen from SP_WHO2 does not increase.
However, if I have the second call, every time I refresh the page at the browser, the number of connections increases by one. This is obviously not acceptable in a real world application.
I tried both Integrated Authentication (with no impersonation) and using a hardcoded service account. Both have the exact same results. In fact this test is not about multi-user yet, it is the same single user just refreshing the same page.
May I know what have I done wrong? All the documentation from Microsoft says close the connection after using it. In the case of SqlDataSource how do I close the connection?
Thanks
<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
protected void Page_Load(object sender, EventArgs e)
{
SqlDataSource1.SelectCommand = "Select ID from Master";
System.Data.SqlClient.SqlDataReader reader =
(System.Data.SqlClient.SqlDataReader)SqlDataSource1.Select(DataSourceSelectArguments.Empty);
if (reader.HasRows && reader.Read())
Label1.Text = reader["ID"].ToString();
SqlDataSource1.Dispose();
//second call: read from another table
SqlDataSource1.SelectCommand = "Select Name from Students";
reader = (System.Data.SqlClient.SqlDataReader)SqlDataSource1.Select(DataSourceSelectArguments.Empty);
if (reader.HasRows && reader.Read())
Label1.Text += reader["Name"].ToString();
reader.Close();
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Label ID="Label1" runat="server" Text="Label"></asp:Label>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:myConnectionString %>"
DataSourceMode="DataReader"></asp:SqlDataSource>
</div>
</form>
</body>
</html>
View 2 Replies
View Related
