Out of curosity I am asking this design type question because I am curious as to what thoughts you fine folks have on the subject matter.
I have seen many instances of a databse design where there are detail records that have a dollar value associated with them such as an invoice table which holds invoice records. I have noticed that no where is a total of the invoices stored, causing SQL to have to run through the records each time a total is needed.
In days gone by prior to SQL, data structures would be such that a total accumulator field in a data file somewhere would be updated as invoices were processed so that the system would not have to run through the individual records to derive the total of them.
I am familiar with normalization in terms of SQL as I have worked with SQL for years and of course I have work with non-SQL technologies before SQL came onto the scene. What I am curious to know is that is it such a bad thing in the SQL world to have something like an invoice total accumulator that constantly is updated as invoices are processed and queries can access this total accumulator directly rather than having to gather the individual invoice records and total them up first before presenting the total to the user?
I am new here, and I've tried to see if this topic has been discussed elsewhere, but since I am not familiar with the terminology, I'll stick my neck out and ask.
Quick background: I am a C# developer with limited exposure to and experience with SQL. I now have to make some design decisions for a newer version of a "legacy" app. Since those of us who are left here basically know how to query the database and do the CRUD work, but don't have a lot of mad skills in the db design department, I am wondering to which degree we should leave things alone.
Ok, so now to the actual issue.
This database is used by an application that registers sales locally for certain types of sales teams. All data is eventually extracted and sent off as ASCII files to central processing, but until then, they are stored in a table for each sales campaign.
Information about campaigns is stored in one table called CampaignSettings. Highlights of this table include a CampaignID (primary key) and some generic sales information, plus a reference to the table that holds customer information. Yep, each campaign has it's own table, as customer registrations (not just for sales, but for information requests etc.) can easily scale into the hundereds of thousands, plus the number of campaigns will eventually reach at least 20-50 active at any given time.
Yep, perhaps it sounds like a huge task for inexperienced db people, that's why we have to learn, and fast! ;)
Ok, so these individual campaign tables is where it gets interesting, and they are the reason I am writing. Each campaign table has the same basic structure - a RecordID, an insertion date, several customer information fields. And then they have two additional sets of fields; The salesinfo "nodes", and the "custom fields".
There is a table called sinode, which pretty much looks like this:
id - int, primary key campaign - foreign key to campaignsettings campaignid field field - varchar, the name of the column in the campaign table desc - a description of what goes in the field (used in the sales registration app) default - a reasonable default value showing the user what to register
Using this method, the idea is that each campaign can have a different structure (i.e. different product types sold = different information needs to be registered).
Also, for the "custom fields" stuff, something similar is employed. There is a scriptvariables table, similar to the sinode one, which contains custom fields that campaigns contain - when they feel like it, they can for instance find everyone with a certain value in a custom field and send them some sales propaganda by snailmail, with some of these script variables used in the letter. The difference between salesinfo columns and custom field columns is basically that only salesinfo columns (plus a few of the standard ones present in all tables) are reported to central processing.
QUESTION #1: Is there a name for such a table structure, where the very definition of tables themselves are somewhat dynamic and applications need to query support tables in order to know the customer table structure for each campaign. Is the name "Brain Dead Design"? ;) Or is this an acceptable way of doing things?
Since we're now upgrading things a little, among other things we're considering moving from "old fashioned" ADO.NET to LINQ or some kind of O/R mapping application, so that we'll cut down on the maintenance of the database <-> object layer.
QUESTION #2: Will an O/R Mapper or LINQ (when Orcas goes RTM) be able to handle the kind of tables we're talking about here, considering that the structure of the individual customer table is not known at compile time, but depends on the database?
And then, there's the question that has been haunting me for a while now..
QUESTION #3: If you, the reader, who with great probability knows a lot more about database design than I do, were put in charge of such a project - how would YOU design it? Keep it like today? Create a huge normalization table for sales items and custom fields? What kind of architecture would be "correct" in this case, considering that our customers scale from the very small with few campaigns to the relative large with big, nasty customer tables.
I will be extremely grateful for any replies, and I promise to read many heavy SQL books as penance, and spend 10% of my work day helping newbies for several weeks once I attain enlightenment! :)
Hello guys, I am a hobbyist programmer and now that I have started asp.net, I was wondering about the correct way to enter data into a table. For example, i have a table called players with a field called status. What should I code the status as? "Active" or "Retired" or 1 and 0? I thought using 1 and 0 would be better as far as database size, but it is pretty difficult to understand how to modify the gridview. I come from PHP and everything is a lot more accesible there as far as modification of output. Thanks in advance!
I am a developer and I have a problem trying to design a system to manage data coming from web surveys. On average each subject will take part in more than one survey and each survey may potentially have 100+ questions.
I am focusing here only on the table(s) that will hold the survey data.
I'm thinking about having a wide table storing each question in the survey(s) as a column against the subjects. My main concerns with this approach:
- the "yuckiness" and potentially other performance issues(?) associated with a table with 100s of columns - the 8kb size limit per row (unlikely to touch it, but possible) - 1024 column limit (unlikely to touch it, but possible)
Another approach obviously is to have a single table where we have, in a simplified version, 3 columns: person_id, question_id and data/ answer. The problem with this is that the "data" column will have to accommodate all types of data, from bits to varchars, and that field validation seems now impossible jeopardizing data integrity, and most importantly, you can't easily work with the data in filtering/ reporting etc.
What do knowledgeable designers come up with in this case? Can someone point me in the right direction?
Ilve install sql server 2012 in my pc and i want to change datetime default format. How can i do this and please i dont want to take the result from select convert() or select cast or something like this. I ve want to take the format i want writing query select datecolumn from table.Â
Now the format i have is:Â 2015-11-16 09:04:00.000
And i want this format: 16.11.2015 09:04:00
Is any way to convert automaticaly by select only column? or can i change at all once? or must write function to when i select the column can change automatic ? or another thing, i ve see in column properties something like formula. In computed column specification in formula i wrote this:Â
And I take the format i want automaticaly but i get the current date for all rows and i cant edit or insert that column anymore. So, how to change the format of date time but no from select query.
I'm designing a database that stores personal details of students and teachers for a school. I, wish to know if a there is away of formatting the bit data type to male/female. Cause i am only able to achieve 0 and 1 in this data type. So is there any way to change it, or is there any other data type suitable to provide only two options Also the database should be protected in a way that students may not be able to view or change. Only teachers/admin have the right to access/modify the data.
I would like to fail a package depending on the error. The package extracts data from Excel files. I would like to continue processing if an Excel file is badly formatted, but stop processing if there is a serious issue. like the file server hosting the Excel files crashed. I was thinking about dynamically changing the MaxeErrorCount property based on the Error ID or description.
Ok, I'm doing a football database for fixtures and stuff. The problem I am having is that in a fixture, there is both a home, and an away team. The tables as a result are something like this:
It's not exactly like that, but you get the point. The question is, can I do a fixture query which results in one record per fixture, showing both teams details. The first in a hometeam field and the second in an away team field.
Fixture contains the details about the fixture like date and fixture id and has it been played
Team contains team info like team id, name, associated graphic
TeamFixture is the table which links the fixture to it's home and away team.
TeamFixture exists to prevent a many to many type relationship.
Make sense? Sorry if this turns out to be really easy, just can't get my head around it at the mo!
I would like to create a table called product. My objective is to get list of packages available for each product in data grid view column while selecting each product. Each product may have different packages type (eg:- Nos, CTN, OTR etc). Some product may have two packages and some for 3 packages etc. Quantity in each packages also may be differ ( for eg:- for some CTN may contain 12 nos or in other case 8 nos etc). Prices for each packages also will be different that also need to show. Â How to design the table..Â
Product name  :  Nestle milk | Rainbow milk packages  : CTN,OTR, NOs |
CTN, NOs Price: 50,20,5 | 40,6
(Remarks for your reference):CTN=10nos, OTR=4 nos  | CTN=8 Nos
This is nutty. I never got this error on my local machine. The only lower case m in the sql is by near the variable Ratingsum like in line 59. [SqlException (0x80131904): Incorrect syntax near 'm'.An expression of non-boolean type specified in a context where a condition is expected, near 'type'.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +925466 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +800118 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +186 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1932 System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) +196 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +269 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135 view_full_article.btnRating_Click(Object Src, EventArgs E) +565 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1746</pre></code> Here is my button click sub in its entirety: 1 Sub btnRating_Click(ByVal Src As Object, ByVal E As EventArgs) 2 'Variable declarations... 3 Dim articleid As Integer 4 articleid = Request.QueryString("aid") 5 Dim strSelectQuery, strInsertQuery As String 6 Dim strCon As String 7 Dim conMyConnection As New System.Data.SqlClient.SqlConnection() 8 Dim cmdMyCommand As New System.Data.SqlClient.SqlCommand() 9 Dim dtrMyDataReader As System.Data.SqlClient.SqlDataReader 10 Dim MyHttpAppObject As System.Web.HttpContext = _ 11 System.Web.HttpContext.Current 12 Dim strRemoteAddress As String 13 Dim intSelectedRating, intCount As Integer 14 Dim Ratingvalues As Decimal 15 Dim Ratingnums As Decimal 16 Dim Stars As Decimal 17 Dim Comments As String 18 Dim active As Boolean = False 19 Me.lblRating.Text = "" 20 'Get the user's ip address and cast its type to string... 21 strRemoteAddress = CStr(MyHttpAppObject.Request.UserHostAddress) 22 'Build the query string. This time check to see if IP address has already rated this ID. 23 strSelectQuery = "SELECT COUNT(*) As RatingCount " 24 strSelectQuery += "FROM tblArticleRating WHERE Itemid=" & articleid 25 strSelectQuery += " AND ip = '" & strRemoteAddress & "'" 26 'Open the connection, and execute the query... 27 strCon = System.Web.Configuration.WebConfigurationManager.ConnectionStrings("sqlConnectionString").ConnectionString 28 conMyConnection.ConnectionString = strCon 29 conMyConnection.Open() 30 cmdMyCommand.Connection = conMyConnection 31 cmdMyCommand.CommandType = System.Data.CommandType.Text 32 cmdMyCommand.CommandText = strSelectQuery 33 intCount = cmdMyCommand.ExecuteScalar() 34 intSelectedRating = Int(Me.rbRating.Text) 35 conMyConnection.Close() 36 'Close the connection to release these resources... 37 38 If intCount = 0 Then 'The user hasn't rated the article 39 'before, so perform the insert... 40 strInsertQuery = "INSERT INTO tblArticleRating (rating, ip, itemID, comment, active) " 41 strInsertQuery += "VALUES (" 42 strInsertQuery += intSelectedRating & ", '" 43 strInsertQuery += strRemoteAddress & "', " 44 strInsertQuery += articleid & ", '" 45 strInsertQuery += comment.Text & "', '" 46 strInsertQuery += active & "'); " 47 cmdMyCommand.CommandText = strInsertQuery 48 conMyConnection.Open() 49 cmdMyCommand.ExecuteNonQuery() 50 conMyConnection.Close() 51 Me.lblRating.Text = "Thanks for your vote!" 52 Comments = comment.Text.ToString 53 54 If Len(Comments) > 0 Then 55 emailadmin(comment.Text, articleid) 56 End If 57 'now update the article db for the two values but first get the correct ratings for the article 58 strSelectQuery = _ 59 "SELECT SUM(rating) As RatingSum, COUNT(*) As RatingCount " 60 strSelectQuery += "FROM tblArticleRating WHERE Itemid=" & articleid 61 conMyConnection.Open() 62 cmdMyCommand.CommandText = strSelectQuery 63 dtrMyDataReader = cmdMyCommand.ExecuteReader() 64 dtrMyDataReader.Read() 65 Ratingvalues = Convert.ToDecimal(dtrMyDataReader("RatingSum").ToString) 66 Ratingnums = Convert.ToDecimal(dtrMyDataReader("RatingCount").ToString) 67 Stars = Ratingvalues / Ratingnums 68 conMyConnection.Close() 69 'Response.Write("Values: " & Ratingvalues) 70 'Response.Write("Votes: " & Ratingnums) 71 72 UpdateRating(articleid, Stars, Ratingnums) 73 Else 'The user has rated the article before, so display a message... 74 Me.lblRating.Text = "You've already rated this article" 75 End If 76 strSelectQuery = _ 77 "SELECT SUM(rating) As RatingSum, COUNT(*) As RatingCount " 78 strSelectQuery += "FROM tblArticleRating WHERE Itemid=" & articleid 79 conMyConnection.Open() 80 cmdMyCommand.CommandText = strSelectQuery 81 dtrMyDataReader = cmdMyCommand.ExecuteReader() 82 dtrMyDataReader.Read() 83 Ratingvalues = Convert.ToDecimal(dtrMyDataReader("RatingSum").ToString) 84 Ratingnums = Convert.ToDecimal(dtrMyDataReader("RatingCount").ToString) 85 Stars = Ratingvalues / Ratingnums 86 If (Ratingnums = 1) And (Stars <= 1) Then 87 lblRatingCount.Text =" (" & (String.Format("{0:f2}", Stars)) & ") / " & dtrMyDataReader("RatingCount") & " Vote" 88 ElseIf (Ratingnums = 1) And (Stars > 1) Then 89 lblRatingCount.Text = " (" & (String.Format("{0:f2}", Stars)) & ") / " & dtrMyDataReader("RatingCount") & " Vote" 90 ElseIf (Ratingnums > 1) And (Stars <= 1) Then 91 lblRatingCount.Text =" (" & (String.Format("{0:f2}", Stars)) & ") / " & dtrMyDataReader("RatingCount") & " Votes" 92 ElseIf (Ratingnums > 1) And (Stars > 1) Then 93 lblRatingCount.Text = " (" & (String.Format("{0:f2}", Stars)) & ") / " & dtrMyDataReader("RatingCount") & " Votes" 94 End If 95 96 'Response.Write(String.Format("{0:f2}", Stars)) 97 'Response.Write("Values: " & Ratingvalues) 98 'Response.Write("Votes: " & Ratingnums) 99 If (Stars > 0) And (Stars <= 0.5) Then 100 Me.Rating.ImageUrl ="./images/rating/05star.gif" 101 ElseIf (Stars > 0.5) And (Stars < 1.0) Then 102 Me.Rating.ImageUrl = "./images/rating/05star.gif" 103 ElseIf (Stars >= 1.0) And (Stars < 1.5) Then 104 Me.Rating.ImageUrl = "./images/rating/1star.gif" 105 ElseIf (Stars >= 1.5) And (Stars < 2.0) Then 106 Me.Rating.ImageUrl = "./images/rating/15star.gif" 107 ElseIf (Stars >= 2.0) And (Stars < 2.5) Then 108 Me.Rating.ImageUrl = "./images/rating/2star.gif" 109 ElseIf (Stars >= 2.5) And (Stars < 3.0) Then 110 Me.Rating.ImageUrl = "./images/rating/25star.gif" 111 ElseIf (Stars >= 3.0) And (Stars < 3.5) Then 112 Me.Rating.ImageUrl = "./images/rating/3star.gif" 113 ElseIf (Stars >= 3.5) And (Stars < 4.0) Then 114 Me.Rating.ImageUrl = "./images/rating/35star.gif" 115 ElseIf (Stars >= 4.0) And (Stars < 4.5) Then 116 Me.Rating.ImageUrl = "./images/rating/4star.gif" 117 ElseIf (Stars >= 4.5) And (Stars < 5.0) Then 118 Me.Rating.ImageUrl = "./images/rating/45star.gif" 119 ElseIf (Stars >= 4.5) And (Stars <= 5.0) Then 120 Me.Rating.ImageUrl = "./images/rating/5star.gif" 121 End If 122 dtrMyDataReader.Close() 123 conMyConnection.Close() 124 End Sub
If you want to reduplicate the error, click over here and try to submit a rating: http://www.link-exchangers.com/view_full_article.aspx?aid=51 Thanks for helping me figure this out.
Happy Friday! A while since I have posted a question, and this one is probably real easy. I am trying to store numeric values from a php form in MSSQL 2000 database. However, the columns are set to float and if the value is 1.00, when entered into the table it is saved as 1
If I change the column type to money, the query fails, with an error message of conversion of datatype varchar to datatype money statement terminated.
anybody know what I need to do? do I need to do something in my query to specify that this is NOT varchar data?
I like to define my procedure parameter type to match a referenced table colum type, similar to PL/SQL "table.column%type" notation. That way, when the table column is changes, I would not have to change my stored proc. Any suggestion?
The table in SQL has column Availability Decimal (8,8)
Code in c# using sqlbulkcopy trying to insert values like 0.0000, 0.9999, 29.999 into the field Availability we tried the datatype float , but it is converting values to scientific expressions€¦(eg: 8E-05) and the values displayed in reports are scientifc expressions which is not expected we need to store values as is
Error: base {System.SystemException} = {"The given value of type SqlDecimal from the data source cannot be converted to type decimal of the specified target column."}
"System.InvalidOperationException: The given value of type SqlDecimal from the data source cannot be converted to type decimal of the specified target column. ---> System.InvalidOperationException: The given value of type SqlDecimal from the data source cannot be converted to type decimal of the specified target column. ---> System.ArgumentException: Parameter value '1.0000' is out of range. --- End of inner exception stack trace --- at System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData metadata) --- End of inner exception stack trace --- at System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData metadata) at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternal() at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount) at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table, DataRowState rowState) at System.Data.SqlClient.SqlBulkCopy.WriteToServer(DataTable table) at MS.Internal.MS COM.AggregateRealTimeDataToSQL.SqlHelper.InsertDataIntoAppServerAvailPerMinute(String data, String appName, Int32 dateID, Int32 timeID) in C:\VSTS\MXPS Shared Services\RealTimeMonitoring\AggregateRealTimeDataToSQL\SQLHelper.cs:line 269"
Code in C#
SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnection, SqlBulkCopyOptions.Default); DataRow dr; DataTable dt = new DataTable(); DataColumn dc;
try {
dc = dt.Columns.Add("Availability", typeof(decimal)); €¦.
dr["Availability"] = Convert.ToDecimal(s[2]); ------ I tried SqlDecimal €¦€¦€¦.
Implement time interval type in the form of a user defined type in SS2k8r2? Specifically an interval type described in the book Temporal Data and the Relational Model by C. J. Date at all. As an example, an interval is below:
1/4/2006:1/10/2006
which would mean the time period from 1/4 to 1/10.
I am trying to use the Bulk Insert Task to load from a csv file. My final column is a bit that is nullable. My file is an ID column that is int, a date column that is mm/dd/yyy, then 20 columns that are real, and a final column that is bit. I've tried various combinations of codepage and datafiletype on my task component. When I have RAW with Char, I get the error included below. If I change to RAW/Native or codepage 1252, I don't have an issue with the bit; however, errors start generating on the ID and date columns.
I have tried various data type settings on my flat file connection, too. I have tried DT_BOOL and the integer datatypes. Nothing seems to work.
I hope someone can help me work through this.
Thanks in advance,
SK
SSIS package "Package3.dtsx" starting.
Error: 0xC002F304 at Bulk Insert Task, Bulk Insert Task: An error occurred with the following error message: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.The bulk load failed. The column is too long in the data file for row 1, column 24. Verify that the field terminator and row terminator are specified correctly.Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 23 (cancelled).".
Error: 0xC002F304 at Bulk Insert Task 1, Bulk Insert Task: An error occurred with the following error message: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.The bulk load failed. The column is too long in the data file for row 1, column 24. Verify that the field terminator and row terminator are specified correctly.Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 23 (cancelled).".
Task failed: Bulk Insert Task 1
Task failed: Bulk Insert Task
Warning: 0x80019002 at Package3: The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
Hi all, I am developing ASP.NET 1.1 application using VB.NET & SQL Server, on my machine I am using SQL Server 2000, and everything is working just fine. The problem appears when I uploaded the site to the Host, they are using SQL Server 2005, is there any reason for this, I am using casting in the code, and I am sure there is something wrong with the hosting settings. Any suggestions. Best Regards Wafi Mohtaseb
Hello Friends How are you?? Friends i am getting problem in SQL Server 2005. I am deployng web application on production server as well as Databse also. In production server i inserted new field in all tables which is rowguid and its type is uniqueidentifier. The default binding for this field is newsequentialid(). In some pages it works ok but in some places it generates error like 'Conversion from type 'DBNull' to type 'String' is not valid'. Can anybody help me to solve this problem. Its urgent so plz reply me as soon as possible. I'll be very thankfull to you. Thanks in Advance. Regards,
I have extensively revied both of the design methodologies and I cannot come up with a single clear reason to use one over the other!
Type - Attributes is where you have a table holding the type categories, type, a table holding the type attributes expected and then a table holding the type attribute value:
Now the above sure is flexible in the sence that a type of automobile can be added without affecting the database schema, but was if some attributes do not take a numeric value? How do you handle computations on attributes specific attributes? Why would I use this structure as opposed to the super type - sub type as shown below?
Now, adding new sub types probably isn't very flexible but, now you can specify data types for each attribute instead of using sql_variant, which by the documentation cannot be used in aggregate functions and may render poor result when used with ADO.
Regardless of the method used, alot of back end coding is required for computations, what table to send the attributes, etc...
Can anyone please help me clarify. What method is best and why. So far I am leaning for option 2. More work but seems to be more flexible in the sence of customization of each datatype.
E.G., what if you wanted to specify attributes about the cap that can be supplied to trucks?
I'm trying to use the SSIS Execute SQL Task to pull XML from a SQL 2005 database table. The SQL is of the following form:
SELECT (
SELECT MT.MessageId 'MessageId', MT.MessageType 'MessageType', FROM MessageTable MT ORDER BY MT.messageid desc FOR XML PATH('MessageStatus'), TYPE
) FOR XML PATH('Report'), TYPE
For some reason I can only get this query to work if I use an ADO.NET connection type. If I try to use something like the OLEDB connection I get the following error:
<ROOT><?MSSQLError HResult="0x80004005" Source="Microsoft XML Extensions to SQL Server" Description="No description provided"?></ROOT>
Can anyone tell me why the SELECT ... FOR XML PATH... seems only to work with ADO.NET connections?
I am having a UDT problem. When I run the Create Type command. I receive the "could not find type" error. I have seen other posts on here related to the default namespace in VB.NET. When I add the namespace I receive "Incorrect syntax near '.'." What is the format of the EXTERNAL NAME parameter. Thanks for any help. Code below...
Incorrect Syntax Error:
DROP ASSEMBLY CadSqlUdtsCREATE ASSEMBLY CadSqlUdtsAUTHORIZATION [dbo]FROM 'E:CAD.NETCADUDTsReleaseCadSqlUdts.dll'WITH PERMISSION_SET = SAFEGOCREATE TYPE dbo.ReportingAreaUDTEXTERNAL NAME CadSqlUdts.[CadSqlUdts.CadSqlUdts].ReportingAreaUDT;GO
Could Not Find Type Error:
DROP ASSEMBLY CadSqlUdtsCREATE ASSEMBLY CadSqlUdtsAUTHORIZATION [dbo]FROM 'E:CAD.NETCADUDTsReleaseCadSqlUdts.dll'WITH PERMISSION_SET = SAFEGOCREATE TYPE dbo.ReportingAreaUDTEXTERNAL NAME CadSqlUdts.ReportingAreaUDT;GO What's up??
I am trying to put the data from a field in my database into a row in a table using the SQLDataSource.Select statement. I am using the following code: FileBase.SelectCommand = "SELECT Username FROM Files WHERE Filename = '" & myFileInfo.FullName & "'" myDataRow("Username") = CType(FileBase.Select(New DataSourceSelectArguments()), String)But when I run the code, I get the following error:Server Error in '/YorZap' Application. Unable to cast object of type 'System.Data.DataView' to type 'System.String'. 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.InvalidCastException: Unable to cast object of type 'System.Data.DataView' to type 'System.String'.Source Error: Line 54: FileBase.SelectCommand = "SELECT Username FROM Files WHERE Filename = '" & myFileInfo.FullName & "'" Line 55: 'myDataRow("Username") = CType(FileBase.Select(New DataSourceSelectArguments).GetEnumerator.Current, String) Line 56: myDataRow("Username") = CType(FileBase.Select(New DataSourceSelectArguments()), String) Line 57: Line 58: filesTable.Rows.Add(myDataRow)Source File: D:YorZapdir_list_sort.aspx Line: 56 Stack Trace: [InvalidCastException: Unable to cast object of type 'System.Data.DataView' to type 'System.String'.] ASP.dir_list_sort_aspx.BindFileDataToGrid(String strSortField) in D:YorZapdir_list_sort.aspx:56 ASP.dir_list_sort_aspx.Page_Load(Object sender, EventArgs e) in D:YorZapdir_list_sort.aspx:7 System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +13 System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +45 System.Web.UI.Control.OnLoad(EventArgs e) +80 System.Web.UI.Control.LoadRecursive() +49 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3743 Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210 Please help me!
I am stuck with a SSIS package and I can€™t work out. Let me know what steps are the correct in order to solve this. At first I have just a Flat File Source and then Script Component, nothing else.
Error:
[Script Component [516]] Error: System.InvalidCastException: Unable to cast COM object of type 'System.__ComObject' to class type 'System.Data.SqlClient.SqlConnection'. Instances of types that represent COM components cannot be cast to types that do not represent COM components; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface. at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.AcquireConnections(Object transaction) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper90 wrapper, Object transaction)
Script Code (from Script Component):
' Microsoft SQL Server Integration Services user script component ' This is your new script component in Microsoft Visual Basic .NET ' ScriptMain is the entrypoint class for script components
Imports System Imports System.Data.SqlClient Imports System.Math Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Public Class ScriptMain Inherits UserComponent
Dim nDTS As IDTSConnectionManager90 Dim sqlConnecta As SqlConnection Dim sqlComm As SqlCommand Dim sqlParam As SqlParameter
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim valorColumna As String Dim valorColumna10 As Double
valorColumna = Row.Column9.Substring(1, 1)
If valorColumna = "N" Then valorColumna10 = -1 * CDbl(Row.Column10 / 100) Else valorColumna10 = CDbl(Row.Column10 / 100) End If
Executed SQL statement: SELECT Schoolindex, Variant, VVSchool, [index], indincl, VVRuimtes, School FROM School WHERE (Schoolindex = @PARAM1)
Error Source: SQL Server Compact Edition ADO.NET Data Provider
Error Message: @PARAM1 : Unable to cast object of type 'System.Data.SqlTypes.SqlInt32 to type System.IConvertable'. ****************************************
The same querypreview works fine without the parameter:
SELECT Schoolindex, Variant, VVSchool, [index], indincl, VVRuimtes, School FROM School WHERE (Schoolindex = 186)
Can anybody tell me why this is? And tell me a way to get the tableadapter working?
Hi all,I have a table called PTRANS with few columns (see create script below).I have created a view on top that this table VwTransaction (See below)I can now run this query without a problem:select * from dbo.VwTransactionwhereAssetNumber = '101001' andTransactionDate <= '7/1/2003'But when I create an index on the PTRANS table using the command below:CREATE INDEX IDX_PTRANS_CHL# ON PTRANS(CHL#)The same query that ran fine before, fails with the error:Server: Msg 242, Level 16, State 3, Line 1The conversion of a char data type to a datetime data type resulted inan out-of-range datetime value.I can run the same query by commeting out the AssetNumber clause and itworks fine. I can also run the query commenting out the TransactionDatecolumn and it works fine. But when I have both the conditions in theWHERE clause, it gives me this error. Dropping the index solves theproblem.Can anyone tell me why an index would cause a query to fail?Thanks a lot in advance,AmirCREATE TABLE [PTRANS] ([CHL#] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[CHCENT] [numeric](2, 0) NOT NULL ,[CHYYMM] [numeric](4, 0) NOT NULL ,[CHDAY] [numeric](2, 0) NOT NULL ,[CHTC] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL) ON [PRIMARY]GOCREATE VIEW dbo.vwTransactionsASSELECT CONVERT(datetime, dbo.udf_AddDashes(REPLICATE('0', 2 -LEN(CHCENT)) + CONVERT(varchar, CHCENT) + REPLICATE('0', 4 -LEN(CHYYMM))+ CONVERT(varchar, CHYYMM) + REPLICATE('0', 2 -LEN(CHDAY)) + CONVERT(varchar, CHDAY)), 20) AS TransactionDate,CHL# AS AssetNumber,CHTC AS TransactionCodeFROM dbo.PTRANSWHERE (CHCENT <> 0) AND (CHTC <> 'RA')*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
I have a ntext column which stores rows of xml data. Ive decided that the best way to query the xml data is to Convert the ntext data into xml data type and use Sql Server 2005 builtin Xml Query tools to perform FLWOR expressions. The problem Im having is getting the ntext data Converted to xml.When I try to convert the ntext text, I of course get an error that local variables of ntext type are invalid.Any suggestions on a way I might accomplish this?Thanks, -lance
Hi Is it possibly in SqlDataSource's SelectCommad to convert a text-type value to a date-type value (11/1/2008 (text) -> 11/1/2008 (date) ) , so that order by can be used. Or is the other ways to use order by correctly with text-type (date)values Thanks.
I'm getting this error on a vb.net page the needs to execute two separate stored procedures. The first one, is the main insert, and returns the identity value for the ClientID. The second stored procedure inserts data, but needs to insert the ClientID returned in the first stored procedure. What am I doing wrong with including the identity value "ClientID" in the second stored procedure? Unable to cast object of type 'System.String' to type 'System.Web.UI.WebControls.Parameter'. 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.InvalidCastException: Unable to cast object of type 'System.String' to type 'System.Web.UI.WebControls.Parameter'.Source Error:
Line 14: If li.Selected Then Line 15: InsertClientCompanyType.InsertParameters("CompanyTypeID").DefaultValue = li.Value Line 16: InsertClientCompanyType.InsertParameters("ClientID") = ViewState("ClientID") Line 17: Line 18: Source File: C:InetpubwwwrootIntranetExternalAppsNewEmploymentClientNewClient.aspx.vb Line: 16 Here is my code behind... What am I doing wrong with grabbing the ClientID from the first stored procedure insert?
Protected Sub InsertNewClient_Inserted(ByVal sender As Object, ByVal e As SqlDataSourceStatusEventArgs)ClientID.Text = e.Command.Parameters("@ClientID").Value.ToString()ViewState("ClientID") = e.Command.Parameters("@ClientID").Value.ToString()End SubProtected Sub Submit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Submit.ClickInsertNewClient.Insert()For Each li As ListItem In CompanyTypeID.Items If li.Selected ThenInsertClientCompanyType.InsertParameters("CompanyTypeID").DefaultValue = li.ValueInsertClientCompanyType.InsertParameters("ClientID") = ViewState("ClientID")InsertClientCompanyType.Insert()End IfNextEnd Sub
I have an application which runs successfully on a couple of my customer's machines but fails on a third. It seems to fail when opening the database:
Unable to cast COM object of type 'ADODB.CommandClass' to interface type 'ADODB._Command'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{B08400BD-F9D1-4D02-B856-71D5DBA123E9}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)). Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=false; Initial Catalog=lensdb;Data Source = SQL
Before I got this error I was getting another problem (sorry didn't make a copy of that error's text) that made me think that adodb.dll simply wasn't loaded/registered. I got rid of that error by copying my adodb.dll onto the third machine and running gacutil /i. There is now an entry in winntassemblies for adodb.
Just in case you think it could be an obvious registry problem: when I started getting the current error I thought that maybe the registry needed updating and I merged the following lines into onto the target machine (from my dev machine):
Hi, I have developed a custom server control for .NET Framework 2.0. The server control has a property named BinaryData of type byte[]. I marked this property to be data bindable. Now, I have varbinary(Max) type of field in my SQL Database and I have used SQLDataSource and bound this varbinary(Max) field with the property BinaryData (byte[]) of my control. It is working fine as long as the data value is not NULL. Now, In my control, I have handled the NULL value so that no Exception is thrown. Still, when I bind this property using the SQLDataSource, I get Error "Unable to cast object of type 'System.DBNull' to type 'System.Byte[]'." I am not sure if I can do anything to stop this erro within my control. If it is not possible from the control, then what is the workaround that I can do in my ASPX page in order to stop this error ? Thanks a lot in advance.
Hi, I got this field (dateSubmitted) having a data type of DateTime but I receive this error "Unable to cast object of type 'System.DateTime' to type 'System.String'." All value for dateSubmitted field are 12/27/2007 12:00:00 AM. cheers,imperialx
