Any ideas?I just posted my best guesses of causes for this here:
http://forums.asp.net/thread/1366165.aspx
Hope it helps,
Joe|||Thanks, your tips actually did help. There was a trigger on the table I was trying to insert into and it was attempting to insert into a table where the email field only allowed for addresses as large as 40 characters.
WHERE (a_Name_Symbol.Symbol IN REPLACE(SELECT Portfolio_Symbols FROM a_Users_Portfolios WHERE (UserID = @.UserID) AND (Portfolio_Name = @.Portfolio_Name),'''',''')
Entire SPROC
------------------------------
CREATE PROCEDURE _premium_BSH (@.Portfolio_Name NVarChar (50), @.UserID int, @.Symbol VarChar (1500)) AS
SELECT a_Name_Symbol.Name, a_Name_Symbol.Symbol, a_Industry.Industry, a_Sector.Sector, a_Quarter_Index.Period, a_Technical_Signals.Signal,
a_Technical_Signals.[Date], a_Financials.Revenue, a_Financials.Income, a_Financials.EPS, a_Financials.Margin_Net AS [Net Margin],
a_Financials.PE, a_Hyperlinks.Yahoo_Main AS Yahoo, a_Hyperlinks.MSN_10Qs AS Financials, a_Hyperlinks.MSN_events AS Events,
a_Hyperlinks.StockCharts AS Technicals
FROM a_Financials INNER JOIN
a_Hyperlinks ON a_Financials.Yahoo_Main = a_Hyperlinks.Yahoo_Main INNER JOIN
a_Industry ON a_Financials.Industry = a_Industry.Industry INNER JOIN
a_Sector ON a_Financials.Sector = a_Sector.Sector INNER JOIN
a_Name_Symbol ON a_Financials.Symbol = a_Name_Symbol.Symbol INNER JOIN
a_Technical_Signals ON a_Name_Symbol.Symbol = a_Technical_Signals.Symbol INNER JOIN
a_Quarter_Index ON a_Financials.Period = a_Quarter_Index.Period
WHERE (a_Name_Symbol.Symbol IN REPLACE(SELECT Portfolio_Symbols FROM a_Users_Portfolios WHERE (UserID = @.UserID) AND (Portfolio_Name = @.Portfolio_Name),'''',''') AND (NOT (a_Technical_Signals.Signal IS NULL)) AND (a_Quarter_Index.Period = '2003 Q3')
ORDER BY a_Name_Symbol.Name, a_Technical_Signals.Signal
GOI see what you're trying to do here - and it will only work with dynamic SQL.
You want to construct a query in the form of:
select ... from ... where Symbol in ('A', 'B', 'C', 'D', 'E')
from a string of 'A B C D E'
You'll need to do this:
declare @.Query varchar(8000)
declare @.Search varchar(100)set @.Search = 'A B C D E'
set @.Query = 'select ... from ... where Symbol in (''' + replace(@.Search, ' ', ''', ''') + ''')'exec(@.Query)
Have fun. Just one more thing though, if you can post me your table definitions, I can help you normalise them. They're not normalised from what I can see in your query above.|||post me your table definitions
how do I do that?|||Is this what you mean?
CREATE PROCEDURE _premium_BSH (@.Portfolio_Name NVarChar (50), @.UserID int, @.Symbol VarChar (1500)) AS
declare @.Query varchar(8000)
declare @.Search varchar(1000)
set @.Search = 'SELECT Portfolio_Symbols
FROM a_Users_Portfolios
WHERE (UserID = ''' + @.UserID + ''') AND (Portfolio_Name = ''' + @.Portfolio_Name + ''')'
set @.Query = 'SELECT a_Name_Symbol.Name, a_Name_Symbol.Symbol, a_Industry.Industry, a_Sector.Sector, a_Quarter_Index.Period, a_Technical_Signals.Signal,
a_Technical_Signals.[Date], a_Financials.Revenue, a_Financials.Income, a_Financials.EPS, a_Financials.Margin_Net AS [Net Margin],
a_Financials.PE, a_Hyperlinks.Yahoo_Main AS Yahoo, a_Hyperlinks.MSN_10Qs AS Financials, a_Hyperlinks.MSN_events AS Events,
a_Hyperlinks.StockCharts AS Technicals
FROM a_Financials INNER JOIN
a_Hyperlinks ON a_Financials.Yahoo_Main = a_Hyperlinks.Yahoo_Main INNER JOIN
a_Industry ON a_Financials.Industry = a_Industry.Industry INNER JOIN
a_Sector ON a_Financials.Sector = a_Sector.Sector INNER JOIN
a_Name_Symbol ON a_Financials.Symbol = a_Name_Symbol.Symbol INNER JOIN
a_Technical_Signals ON a_Name_Symbol.Symbol = a_Technical_Signals.Symbol INNER JOIN
a_Quarter_Index ON a_Financials.Period = a_Quarter_Index.Period
WHERE (a_Name_Symbol.Symbol IN (''' + replace(@.Search, ' ', ''', ''') + ''') AND (NOT (a_Technical_Signals.Signal IS NULL)) AND (a_Quarter_Index.Period = ''2003 Q3'')
ORDER BY a_Name_Symbol.Name, a_Technical_Signals.Signal'
exec(@.Query)
GO|||No, I mean post me your tables - their names, and columns.
e.g.
TableName (Col1 DataType, Col1 DataType, ...)
...|||imsmart.info
LOL|||Hmm, I'm getting more and more confused as to what your inputs are (e.g. what is an example value of @.Symbol) so I'm going to give up. You get the idea, though? With the dynamic query?|||LOL with what?|||This problem is related to your non-normalised database. Normalise your DB and the whole issue will disappear.
Hi, I am deploying my web through a web hosting services which provides SQL database support. I got following errors whenever I try to open my webpage,which never happen when I run my web on the local machine.
I have my connection string configured in my web.config as below:
<addname="ArtHouseConnection"connectionString="Server=serveripaddress; Integrated Security=True; Database=arteh3_database;User Id=username;Password=password;"providerName="System.Data.SqlClient"/>
<addname="ASPNETDBConnectionString1"connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated Security=True;User Instance=True"providerName="System.Data.SqlClient"/>
This is the source code where error was generated:
public
staticclass ArtHouseConfiguration{
//cache connection stringprivatereadonlystaticstring dbConnectionString;//cache data provider nameprivatereadonlystaticstring dbProviderName;privatereadonlystaticstring siteName;//initialize constructor propertiesstatic ArtHouseConfiguration(){
dbConnectionString = ConfigurationManager.ConnectionStrings[
"ArtHouseConnection"].ConnectionString;dbProviderName = ConfigurationManager.ConnectionStrings[
"ArtHouseConnection"].ProviderName;siteName = ConfigurationManager.AppSettings["SiteName"];}
and finally this is the error mg I got:
Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details:System.NullReferenceException: Object reference not set to an instance of an object.
Source Error:
Line 30: static ArtHouseConfiguration()Line 31: {Line 32: dbConnectionString = ConfigurationManager.ConnectionStrings["ArtHouseConnection"].ConnectionString;Line 33: dbProviderName = ConfigurationManager.ConnectionStrings["ArtHouseConnection"].ProviderName;Line 34:
Source File:d:\inetpub\vhosts\artehouse.org\httpdocs\App_Code\ArtHouseConfiguration.cs Line:32
Stack Trace:
[NullReferenceException: Object reference not set to an instance of an object.] ArtHouseConfiguration..cctor() in d:\inetpub\vhosts\artehouse.org\httpdocs\App_Code\ArtHouseConfiguration.cs:32[TypeInitializationException: The type initializer for 'ArtHouseConfiguration' threw an exception.] ArtHouseConfiguration.get_EnableErrorLogEmail() in d:\inetpub\vhosts\artehouse.org\httpdocs\App_Code\ArtHouseConfiguration.cs:74 Utilities.LogError(Exception ex) in d:\inetpub\vhosts\artehouse.org\httpdocs\App_Code\Utilities.cs:100 ASP.global_asax.Application_Error(Object sender, EventArgs e) in d:\inetpub\vhosts\artehouse.org\httpdocs\Global.asax:20 System.EventHandler.Invoke(Object sender, EventArgs e) +0 System.Web.HttpApplication.RaiseOnError() +182
Anyone got any ideas? thank you!
Hi genjioo,
This seems to have something to do with the web.config file.
Please check if the connections strings have been put under the configuration\connectionStrings section. Here is a typical sample
<?xml version="1.0" encoding="utf-8"?>
<configuration>
<connectionStrings>
<add name="ConnStr1" connectionString="LocalSqlServer: data source=127.0.0.1;Integrated Security=SSPI;Initial Catalog=aspnetdb"
providerName="System.Data.SqlClient" />
</connectionStrings>
</configuration>
If it is not put under this, the ConfigurationManager.ConnectionStrings will not be able to find it.
hi
i have a database with some data in it and iam using full-text-search to search through my data and the search query works fine in sql server manamgnet studio. so my problem is in my web application, i have a textbox where i enter e.g car and click the search button, the button executes the search query but i recive an error"Input string was not in a correct format". here is the code that the button executes:
ProtectedSub searchButton_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)searchSqlDataSource.SelectCommand =
"Select ID, title, FROM myTable WHERE CONTAINS(description, @.search)"searchSqlDataSource.SelectParameters.Add(
"search", searchTextBox.Text)searchSqlDataSource.DataBind()EndSubtnx in advac
can you set the value of a label for instance to the value of the input string so that you can see the actual string? or have you tried stepping through the code and see what the actual string is that is getting sent. if you have access to a sql management studio, you can post the string into the query window and see what is incorrect with the syntax. give those a try and good luck! -- jp|||I often get that error message when the text contains a semicolon ";". i.e. searchTextBox.text = "johnny went home; vivi left town."
My only resolve was to filter for the semicolon, but I'm sure there's a better way to do it.
Johnny
****SQL Server related question.
I have a table in which one of the columns (col1) holds a string, like: 1,2,3,4,5,6,7,8,9,10
I am passing an int value (@.intValue) to the sproc.
What I want to be able to do is query the table like...
SELECT * FROM myTable where @.intValue ... is in col1
Thanks a lot!!!!you can do something like
SELECT * FROM myTable WHERE col1 LIKE '%' + convert(varchar,@.intValue) + '%'
hth|||Thanks for the response ndinakar!
I tried your suggestion...
It does work...to a point. However... in my concatenated string, it is a possibility that I will have something that looks like this.
3,4,5,34,6,21
Then, when I query this table...say I am looking for '2' ...it returns the record with 21 in it.
Any other ideas about how I can go about this? I am thinking that I might have to break this concatenated value into separate columns.
Thanks for the reply. I greatly appreciate it!|||same approach, but you need to insure you have leading and trailing seperators
from this:
3,4,5,34,6,21
to this:
,3,4,5,34,6,21,
now you search for %,val,%
or in your example ,2, which wont match ,21,|||Thanks a lot! That works great...exactly what I was looking for.
Hi all...
I have a data source string being passed to a SqlConnection object in the format "Data Source=%?SERVER%[...]". Now, to me this kind of looks like an environment variable query, but I'm not sure (and I can't find any system environment variables with that name on the server. Can anyone tell me, specifically, what the "?" is for?
This project uses VS2005, C# 2.0
Thanks in advance.
It looks to me like it is meant to be a value that is prompted for. Where are you getting the connection string from?
|||There's no prompting going on - that's just the value in the string. Here's a sample:
1string strCon ="Data Source=%?SERVER%;Initial Catalog=MyDatabase;Integrated Security=False;Persist Security Info=True;User ID=sa;Password=%?SAPWD%;Encrypt=False";2SqlConnection con =new SqlConnection(strCon);Thanks again.|||Data Source is the name of your sqlserver instance. like connectionString="Data Source= MSHOME\SQLEXPRESS;Initial Catalog=....|||
Well if you don't know of any reason why that is there and you know the connection information put the connection string in the web.config and call it a day. :)
|||Guess that's what I'm going to have to go with (web.config) :P
Was just hoping somebody knew if there was something along the lines of a search-&-replace-before-compile feature in VS2005...
Thanks
This is probably a simple wuestion but i would appreciate some help
I am trying to get and insert theuserId into a table called "Orders"
I got the user id as
Dim CustomerId As Object = Membership.GetUser(User.Identity.Name).ProviderUserKey.ToString
The button on click command is
worldshop.ShoppingCart.CreateOrder(CustomerId,...................
The Function Has the parameter for Customer Id as follows
Dim dbParam_CustomerId As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_CustomerId.ParameterName = "@.CustomerId"
dbParam_CustomerId.Value = CustomerId
dbParam_CustomerId.DbType = System.Data.DbType.String
command.Parameters.Add(dbParam_CustomerId)
When i run this I am getting the error message
"Conversion failed when converting from a character string to uniqueidentifier."
Can anyone tell me where I am going wrong
many thanks
Martin
What type is CustomerID defined as in the database? It sounds like it is NOT a string, but rather a uniqueidentifier (a GUID).|||thanks for your reply. i got it sorted out
martin
|||Do you mind sharing with us what your solution was? as I am having the same problem...|||I'll check and get back to you
martin
|||sorry about the delay in getting back to you
I was having the same problem. I solved it by instantiating the userid as an object
Dim userId As Object = Membership.GetUser(User.Identity.Name).ProviderUserKey
i passed the object to the function as a guid
ByVal userId As Guid
Dim dbParam_userId As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_userId.ParameterName = "@.UserId"
dbParam_userId.Value = userId
dbParam_userId.DbType = System.Data.DbType.Guid
command.Parameters.Add(dbParam_userId)
last i put the guid into the database as char 36
@.UserId char (36)
HTH
