How To Display Constraint Type
i was trying to display constraints but i cant display the type, i dont know wat the type is called to display it
View Complete Forum Thread with Replies
Related Forum Messages:
Named Constraint Is Not Supported For This Type Of Constraint (not Null)
Hi, all. I am trying to create table with following SQL script: Code Snippet create table Projects( ID smallint identity (0, 1) constraint PK_Projects primary key, Name nvarchar (255) constraint NN_Prj_Name not null, Creator nvarchar (255), CreateDate datetime ); When I execute this script I get following error message: Error source: SQL Server Compact ADO.NET Data Provider Error message: Named Constraint is not supported for this type of constraint. [ Constraint Name = NN_Prj_Name ] I looked in the SQL Server Books Online and saw following: CREATE TABLE (SQL Server Compact) ... < column_constraint > ::= [ CONSTRAINT constraint_name ] { [ NULL | NOT NULL ] | [ PRIMARY KEY | UNIQUE ] | REFERENCES ref_table [ ( ref_column ) ] [ ON DELETE { CASCADE | NO ACTION } ] [ ON UPDATE { CASCADE | NO ACTION } ] As I understand according to documentation named constraints should be supported, however error message says opposite. I can rephrase SQL script by removing named constraint. Code Snippet create table Projects( ID smallint identity (0, 1) constraint PK_Projects primary key, Name nvarchar (255) not null, Creator nvarchar (255), CreateDate datetime ); This script executes correctly, however I want named constraints and this does not satisfy me.
View Replies !
What Type Of Constraint To Prevent Duplicates
I am trying to either write a trigger or a check constraint to preventduplicates in my table. There are two columns I need to look at for theduplicates and only one combo value for both columns is allowed in thetable. For e.g.Column Serial can have only one '123456' value with testresult value as'PASS'. This serial can be in the table many times with any other comboso for e.g.The table could contain 100 entries for serial column value '123456'with testresult value 'FAIL', 'PENDING' etc.** TESTED SCHEMA BELOW **-- create tableCREATE TABLE bstresult(ID int IDENTITY (1, 1) NOT NULL ,serial char (10) NULL ,testresult char (10) NULL)-- Insert valid valuesinsert into bstresult values ('123456','PASS')insert into bstresult values ('123456','FAIL')insert into bstresult values ('123456','FAIL')insert into bstresult values ('123456','PENDING')-- insert invalid value this should failinsert into bstresult values ('123456','PASS')If I simply create a unique constraint on both columns it will notallow the FAIL combo or PENDING combo with the same serial which I needto allow.Appreciate your help.
View Replies !
Display Money Type Using + And -
This may be an easy question but I've been reading for about a halfhour and experimenting without results.I simply want the results of my query to display a specific field thatis typed "money" using + and -The program that consumes the data expects + on positive numbers and -on negative. I was hoping to do it in the view instead of processingthe results with the VB application that interogates the DB.Thanks
View Replies !
Rev Counter Type Graph / Display
Good day, I am wanting to create a rev counter type graph / display in SSRS 2000. Is this possible at all? I have seen there is a plug in from Dundas that will do it but it is very pricey and was hoping there was an inbuilt function to do it or a free add on from Microsoft. Does anyone know if this is possible and if so how to do it? Please any help would be greatly appreciated. Thanks
View Replies !
How To Display Money Type Data Properly?
How to display money type data properly, with commas every 3 digits? I've tried : CONVERT (money, ProjectCost, 1) AS ProjectCost but it gives me the type as 1234.56 no commas in it? can anyone help me with that? thanks!
View Replies !
Unique Constraint Error When There Is No Constraint
We are using SQL CE 3.5 on tablet PCs, that synchs with our host SQL 2005 Server using Microsoft Synchronization Services. On the tablets, when inserting a record, we get the following error: A duplicate value cannot be inserted into a unique index. [ Table name = refRegTitle,Constraint name = PK_refRegTitle But the only PK on this table is RegTitleID. The table structure is: [RegTitleID] [int] IDENTITY(1,1) NOT NULL, [RegTitleNumber] [int] NOT NULL, [RegTitleDescription] [varchar](200) NOT NULL, [FacilityTypeID] [int] NOT NULL, [Active] [bit] NOT NULL, The problem occurs when a Title Number is inserted and a record with that number already exists. There is no unique constraint on Title Number. Has anyone else experienced this?
View Replies !
Incorrect Syntax Near 'm'. An Expression Of Non-boolean Type Specified In A Context Where A Condition Is Expected, Near 'type'
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.
View Replies !
Float Type Steals My Decimal Points And Money Type Kills My Query
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?
View Replies !
Sqlbulkcopy Error : The Given Value Of Type SqlDecimal From The Data Source Cannot Be Converted To Type Decimal Of The Specified
Hi, 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 €¦€¦€¦. } bulkCopy.DestinationTableName = "dbo.[Tbl_Fact_App_Server_AvailPerMinute]"; bulkCopy.WriteToServer(dt); thx
View Replies !
Bulk Insert Task Failing On Data Type Conversion For A Destination Column Of Type Bit
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. SSIS package "Package3.dtsx" finished: Failure.
View Replies !
Msg 6522, Level 16, State 2, Line 1: System.InvalidCastException: Conversion From Type 'SqlBoolean' To Type 'Boolean' Is Not Val
I created a function called Temperature in VB to be used as a UDF in SQL2005. I get the error listed below. Any thoughts? CREATE FUNCTION Temperature(@FluidName SQL_variant, @InpCode SQL_variant, @Units SQL_variant, @Prop1 SQL_variant, @Prop2 SQL_variant) RETURNS Float AS EXTERNAL NAME Fluids_VB6.[Fluids_VB6.FluidProperties.Fluids].Temperature Then ran function: select dbo.temperature('R22','t','e','225.6','0') Got this: Msg 6522, Level 16, State 2, Line 1 A .NET Framework error occurred during execution of user defined routine or aggregate 'Temperature': System.InvalidCastException: Conversion from type 'SqlBoolean' to type 'Boolean' is not valid. System.InvalidCastException: at Microsoft.VisualBasic.CompilerServices.Conversions.ToBoolean(Object Value) at Fluids_VB6.FluidProperties.Fluids.Setup(Object& FluidName) at Fluids_VB6.FluidProperties.Fluids.CalcSetup(Object& FluidName, Object& InpCode, Object& Units, Object& Prop1, Object& Prop2) at Fluids_VB6.FluidProperties.Fluids.CalcProp(Object& FluidName, Object& InpCode, Object& Units, Object& Prop1, Object& Prop2) at Fluids_VB6.FluidProperties.Fluids.Temperature(Object FluidName, Object InpCode, Object Units, Object Prop1, Object Prop2) Thanks Buck
View Replies !
Conversion From Type 'DBNull' To Type 'String' Is Not Valid
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,
View Replies !
Conversion From Type 'SqlInt32' To Type 'Integer' Is Not Valid]
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
View Replies !
I Really Need A Debate! Type - Attributes Vs Super Type - Sub Types
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: tbAutombbileCategories CategoryID | Category ------------------------------- 1 | Car 2 | Truck 3 | Motorcycle tbAutomobileAttributes AttributeID | fkCategoryID | Attribute ------------------------------------------- 1 | 1 (car) | Doors 2 | 2 (truck) | Cab 3 | 2 (truck) | Capacity tbAutomobile VIN | Category | Make | Model ------------------------------------- 1 | 1 | Honda | Accord 2 | 2 | Ford | F150 tbAutomobileAttributeValues fkVIN | fkAttributeID | Value --------------------------------- 1 | 1 | 2 2 | 1 | 0 2 | 2 | 1000 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? tbCategories CategoryID | Category -------------------------- 1 | Cars 2 | Trucks tbAutomobile (Super Type) VIN | fkCategoryID | Make | Model ------------------------------------- 1 | 1 |Honda | Accord tbCars fkVIN | Doors | ----------------- 1 | 2 tbTrucks fkVIN | Cab | Capacity --------------------------- 2 | 0 | 1000 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? tbTrucks fkVIN | Cab | Capacity | fkCapID -------------------------------------- 2 | Y | 1000 | 1 tbCaps CapID | Vendor | Price | et.... Any thoughts at all? I thought this would have been a pretty damn hot topic! Mike B
View Replies !
Operand Type Clash: Int Is Incompatible With Void Type
this code work fine on one server (version 7.00.842) and gives following error on another (version 7.00.623) --------------------- Error: Server: Msg 206, Level 16, State 2, Procedure V_PrevisionOccupation, Line 5 Operand type clash: int is incompatible with void type --------------------- start of the code if object_id('V_PrevisionOccupation') is not Null drop view V_PrevisionOccupation go create view dbo.V_PrevisionOccupation as select T1.Id_property, (select T92.BeginDate from acFiscalPeriod T91 join acFiscalPeriod T92 on T92.id_FiscalPeriod = T91.id_FiscalPeriod +1 and T92.ID_FiscalYear = T91.ID_FiscalYear where T91.ID_FiscalYear = T3.ID_FiscalYear and T91.BeginDate <= T1.ADate and datediff(mm, T91.BeginDate, T1.ADate)=0) as PeriodDate, T1.ADate, .... blia, blia ,blia ------------------------ 1. T91.id_FiscalPeriod is not Null 2. when put: select T1.Id_property, (select T91.BeginDate from acFiscalPeriod T91 -- join acFiscalPeriod T92 on T92.id_FiscalPeriod = T91.id_FiscalPeriod +1 and T92.ID_FiscalYear = T91.ID_FiscalYear where T91.ID_FiscalYear = T3.ID_FiscalYear and T91.BeginDate <= T1.ADate and datediff(mm, T91.BeginDate, T1.ADate)=0) as PeriodDate, it works ? Do I miss something in code or it is version error ?
View Replies !
Connection Type Limitations When Using XML Data Type In SQL Task
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? Thanks Walter
View Replies !
How Get The Varchar Type Value And Resultset Type Value From A Procedure
db server is ms sqlserver. the following is my procedure code: CREATE PROCEDURE [test_cc] @ww char(20) output AS begin select @ww='666666666' select col_name from table_name end GO then,following is some of my jdbc invoking procedure code: try { Connection conn = DBC.getConnection(); if (conn == null) { System.out.println("Failed"); } else { conn.setAutoCommit(false); CallableStatement proc = conn.prepareCall("{call test_cc (?)}"); proc.registerOutParameter(1,Types.VARCHAR); proc.execute(); ResultSet rs = proc.getResultSet(); String aa = proc.getString(2); if (rs == null) { System.out.println(" RS is null"); #-----1 } else { ResultSetMetaData metadata = rs.getMetaData();#---2 while (rs.next()) { .............. } } } catch (Exception e) { } but the result that run is :i can get the variable aa value,it is '666666666',but the performing can`t get into #1 point,at #2 point,it throw the exception :object has bean closed. please tell me ,how to get the variable aa value,and get resultset
View Replies !
CREATE TYPE Failed Because It Could Not Find Type
Hello -- 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??
View Replies !
SqlDataSource.Select Error: Unable To Cast Object Of Type 'System.Data.DataView' To Type 'System.String'.
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!
View Replies !
Unable To Cast COM Object Of Type 'System.__ComObject' To Class Type 'System.Data.SqlClient.SqlConn
Dear all, 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 Me.Output0Buffer.PORCRETEN = CDbl(Row.Column11 / 100) Me.Output0Buffer.IMPRETEN = CDbl(Row.Column12 / 100) Me.Output0Buffer.EJERCICIO = CInt(Row.Column2) Me.Output0Buffer.CODPROV = CInt(Row.Column7) Me.Output0Buffer.MODALIDAD = CInt(Row.Column8) Me.Output0Buffer.NIFPERC = CStr(Row.Column3) Me.Output0Buffer.NIFREP = CStr(Row.Column4) Me.Output0Buffer.NOMBRE = CStr(Row.Column6) Me.Output0Buffer.EJERDEV = CDbl(Row.Column13) With sqlComm .Parameters("@Ejercicio").Value = CInt(Row.Column2) .Parameters("@NIFPerc").Value = CStr(Row.Column3) .Parameters("@NIFReP").Value = CStr(Row.Column4) .Parameters("@Nombre").Value = CStr(Row.Column6) .Parameters("@CodProv").Value = CInt(Row.Column7) .Parameters("@Modalidad").Value = CInt(Row.Column8) .Parameters("@ImpBase").Value = valorColumna10 .Parameters("@PorcReten").Value = CDbl(Row.Column11 / 100) .Parameters("@ImpReten").Value = CDbl(Row.Column12 / 100) .Parameters("@EjerDev").Value = CDbl(Row.Column13) .ExecuteNonQuery() End With End Sub Public Overrides Sub AcquireConnections(ByVal Transaction As Object) Dim nDTS As IDTSConnectionManager90 = Me.Connections.TablaMODELO80 sqlConnecta = CType(nDTS.AcquireConnection(Nothing), SqlConnection) End Sub Public Overrides Sub PreExecute() sqlComm = New SqlCommand("INSERT INTO hac_modelo180(Ejercicio,NIFPerc,NIFReP,Nombre,CodProv,Modalidad,ImpBase,PorcReten,ImpReten,EjerDev) " & _ "VALUES(@Ejercicio,@NIFPerc,@NIFReP,@Nombre,@CodProv,@Modalidad,@ImpBase,@PorcReten,@ImpReten,@EjerDev)", sqlConnecta) sqlParam = New SqlParameter("@Ejercicio", Data.SqlDbType.SmallInt) sqlComm.parameters.add(sqlParam) sqlParam = New SqlParameter("@NIFPerc", Data.SqlDbType.Char) sqlComm.parameters.add(sqlParam) sqlParam = New SqlParameter("@NIFReP", Data.SqlDbType.Char) sqlComm.parameters.add(sqlParam) sqlParam = New SqlParameter("@Nombre", Data.SqlDbType.VarChar) sqlComm.parameters.add(sqlParam) sqlParam = New SqlParameter("@CodProv", Data.SqlDbType.TinyInt) sqlComm.parameters.add(sqlParam) sqlParam = New SqlParameter("@Modalidad", Data.SqlDbType.SmallInt) sqlComm.parameters.add(sqlParam) sqlParam = New SqlParameter("@ImpBase", Data.SqlDbType.Decimal) sqlComm.parameters.add(sqlParam) sqlParam = New SqlParameter("@PorcReten", Data.SqlDbType.Decimal) sqlComm.parameters.add(sqlParam) sqlParam = New SqlParameter("@ImpReten", Data.SqlDbType.Decimal) sqlComm.parameters.add(sqlParam) sqlParam = New SqlParameter("@EjerDev", Data.SqlDbType.Decimal) sqlComm.Parameters.Add(sqlParam) End Sub Public Sub New() End Sub Public Overrides Sub ReleaseConnections() nDts.ReleaseConnection(sqlConnecta) End Sub Protected Overrides Sub Finalize() MyBase.Finalize() End Sub End Class Thanks a lot for your help
View Replies !
@PARAM1 : Unable To Cast Object Of Type 'System.Data.SqlTypes.SqlInt32 To Type System.IConvertable
I get the following message in the vs2005 querybuilder when i do a preview: *********************************** SQL Execution Error. 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? Anne-Jan Tuinstra
View Replies !
A Text-type Value To A Date-type Value
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.
View Replies !
Convert Ntext Type To XML Type
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
View Replies !
Unable To Cast Object Of Type 'System.String' To Type 'System.Web.UI.WebControls.Parameter'.
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
View Replies !
Unable To Cast COM Object Of Type 'ADODB.CommandClass' To Interface Type 'ADODB._Command'
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): Windows Registry Editor Version 5.00 [HKEY_CLASSES_ROOTInterface{B08400BD-F9D1-4D02-B856-71D5DBA123E9}] @="_Command" [HKEY_CLASSES_ROOTInterface{B08400BD-F9D1-4D02-B856-71D5DBA123E9}ProxyStubClsid] @="{00020424-0000-0000-C000-000000000046}" [HKEY_CLASSES_ROOTInterface{B08400BD-F9D1-4D02-B856-71D5DBA123E9}ProxyStubClsid32] @="{00020424-0000-0000-C000-000000000046}" [HKEY_CLASSES_ROOTInterface{B08400BD-F9D1-4D02-B856-71D5DBA123E9}TypeLib] @="{EF53050B-882E-4776-B643-EDA472E8E3F2}" "Version"="2.7" [HKEY_LOCAL_MACHINESOFTWAREClassesInterface{B08400BD-F9D1-4D02-B856-71D5DBA123E9}] @="_Command" [HKEY_LOCAL_MACHINESOFTWAREClassesInterface{B08400BD-F9D1-4D02-B856-71D5DBA123E9}ProxyStubClsid] @="{00020424-0000-0000-C000-000000000046}" [HKEY_LOCAL_MACHINESOFTWAREClassesInterface{B08400BD-F9D1-4D02-B856-71D5DBA123E9}ProxyStubClsid32] @="{00020424-0000-0000-C000-000000000046}" [HKEY_LOCAL_MACHINESOFTWAREClassesInterface{B08400BD-F9D1-4D02-B856-71D5DBA123E9}TypeLib] @="{EF53050B-882E-4776-B643-EDA472E8E3F2}" "Version"="2.7" but, no change alas. All three machines are running Windows 2000. Any advice would be appreciated. Thanks in advance, Ross
View Replies !
Unable To Cast Object Of Type 'System.DBNull' To Type 'System.Byte[]'.
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.
View Replies !
COnverting Numeric Data Type (Oracle) To Date Data Type Using SSIS
We have some columns in a table where the date is stored as 19980101 (YYYYMMDD). The data type for this column is NUMBER(8) in Oracle. I need to copy rows from Oracle to SQL Server using SSIS. I used the Data Conversion transformation editor to change it to DT_DATE, but the rows are not being inserted to the destination. On Error, If I fail the component, then the error is : There was an error with input column "ORDER_DATE_CONV" (1191) on input "OLE DB Destination Input" (29). The column status returned was: "Conversion failed because the data value overflowed the specified type.". Regards RH
View Replies !
Converting A Data Type Double To A Type Float Using A Data Adapter && SSCE 3.5
Hi, I can populate a dataTable with type double (C#) of say '1055.01' however when I save these to the CE3.5 database using a float(CE3.5) I lose the decimal portion. The 'offending' code is: this.court0TableAdapter1.Update(this.mycourtsDataSet1.Court0); this.mycourtsDataSet1.AcceptChanges(); this.court0TableAdapter1.Fill(this.mycourtsDataSet1.Court0); This did not happen with VS2005/CE3.01. I have tried changing all references to decimal (or money in CE3.5) without luck. I'm beginning to think that string may be the way to go!!!!!!! Can someone shed some light on my problem. Thanks, Later: It's necessary to update the datatable adapter as the 3.01 and 3.5 CE are not compatible.
View Replies !
Index Creation Causes Error &"The Conversion Of A Char Data Type To A Datetime Data Type Resulted...&"
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!
View Replies !
Coredll.lib(COREDLL.dll) : Fatal Error LNK1112: Module Machine Type 'THUMB' Conflicts With Target Machine Type 'ARM'
Dear All: When i try to debug the northwindoledb example with VS2005 i get the foolwing error message. Is there a way to configure the solution to run witn WM5 emulator. The application run with wm5 emulator when i set the plataform to Windows Mobile 2003, but i want to use the wm5 platform to debug my application with wm5 emulator. ------ Rebuild All started: Project: northwindoledb, Configuration: Debug Windows Mobile 5.0 Pocket PC SDK (ARMV4I) ------ Deleting intermediate and output files for project 'northwindoledb', configuration 'Debug|Windows Mobile 5.0 Pocket PC SDK (ARMV4I)' Compiling... Employees.cpp northwindoledb.cpp stdafx.cpp Generating Code... Compiling resources... Linking... coredll.lib(COREDLL.dll) : fatal error LNK1112: module machine type 'THUMB' conflicts with target machine type 'ARM' Build log was saved at "file://d:Proyectos_VS2005Northwindoledb_WM2003_SQLEW_Windows Mobile 5.0 Pocket PC SDK (ARMV4I)DebugBuildLog.htm" northwindoledb - 1 error(s), 0 warning(s) ========== Rebuild All: 0 succeeded, 1 failed, 0 skipped ==========
View Replies !
The Conversion Of A Char Data Type To A Datetime Data Type!!
hello all .. I have a form that includes two textboxes (Date and Version) .. When I try to insert the record I get the following error message .. seems that something wrong with my coversion (Data type)"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.The statement has been terminated." in my SQL database I have the date feild as datetime and the version as nvarchar(max) this is the code in the vb page .. Can you please tell me how to solve this problem?Imports System.Data.SqlClient Imports system.web.configuration Partial Class Admin_emag_insert Inherits System.Web.UI.Page Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load Record_DateTextBox.Text = DateTime.Now End Sub Protected Sub clearButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles clearButton.Click Me.VersionTextBox.Text = "" End Sub Protected Sub addButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles addButton.Click Dim objConnection As SqlConnection Dim objDataCommand As SqlCommand Dim ConnectionString As String Dim record_date As Date Dim version As String Dim emagSQL As String 'save form values in variables record_date = Record_DateTextBox.Text version = VersionTextBox.Text ConnectionString = WebConfigurationManager.ConnectionStrings("HRDBConnectionString").ConnectionString 'Create and open the connection objConnection = New SqlConnection(ConnectionString) objConnection.Open() emagSQL = "Insert into E_Magazine (Record_Date, Version ) " & _ "values('" & record_date & "','" & version & "')" 'Create and execute the command objDataCommand = New SqlCommand(emagSQL, objConnection) objDataCommand.ExecuteNonQuery() objConnection.Close() AddMessage.Text = "A new emagazine was added successfully" Me.VersionTextBox.Text = "" End Sub End Class
View Replies !
Converting Numeric Data Type To Text Data Type
Hi,I would like to convert a dollar amount ($1,500) to represent Fifteenhundred dollars and 00/100 cents only for SQL reporting purposes. Isthis possible and can I incorporate the statement into an existingleft outer join query.Thanks in advance,Gavin
View Replies !
Convert Text Data Type To Smalldatetime Data Type
I have a field that is currently stored as the data type nvarchar(10), and all of the data in this field is in the format mm/dd/yyyy or NULL. I want to convert this field to the smalldatetime data type. Is this possible? I've tried to use cast in the following way, (rsbirthday is the field name, panelists is the table), but to no avail. SELECT rsbirthday CAST(rsbirthday AS smalldatetime) FROM panelists the error returned is "incorrect syntax near 'rsbirthday'. I'm rather new to all things SQL, so I only have the vaguest idea of what I'm actually doing. Thanks for the help!
View Replies !
Constraint
I have a varchar field in a table.I want to restrict the entries in that field as "yes" or "no" nothing else.No record will be allowed for this field without yes or no.My question is is it possible without using any trigger for the table?I want to do it with the help of a constraint.
View Replies !
Constraint Ddl
When I see desing table option in enterprise manager of a table I don't see any constraints, but when I extract ddl I can see all 6 of them. They are all unique constraints not the check constraints. Is this normal. I am new to SQL Server and would appreciate some explanation. Thanks
View Replies !
Constraint Help!!
using alter table syntax how can i insert the field TramingChoiceCd Extend the constraint on NetwkChannel table UQ__TetwkChannel__50FB042B to include TramingChoiceCd
View Replies !
Constraint Help!!
using alter table syntax how can i change the field TramingChoiceCd Extend the constraint on NetwkChannel table UQ__TetwkChannel__50FB042B to include TramingChoiceCd Alter Table TetwkChannel Alter Column TramingChoiceCdvarchar(8) Null
View Replies !
Help-Constraint
Hi, I am trying to figure out how to do this. For each row, only one out of two columns(id1,id2) should be populated. So if the id1 column is already populated and the application tries to fill in something for id2 then we just simply don't want to allow that and vice versa. I am thinking triggers is the way to go. What do you think? thanks Rozina
View Replies !
Constraint
Which is the preferred method Rule, Check Constraint or Trigger? I want to set a column to todays date when the column is = "T" else when "F" set it to a future date. Each time there is a insertion into the table.
View Replies !
Constraint Help
Hi, i want to put a contraint on a table which much check agains two values in the same column for the same member. For example, i don't want a male to get information based on breast cancer, and i don't want a female to get information based on prostate cancer. I have included some sample data. Just copy and paste. Code Block DECLARE @MemberLookupValues TABLE (OptionID INT, ValueID INT, Description VARCHAR(20)) INSERT @MemberLookupValues VALUES (3, 10, 'Male') INSERT @MemberLookupValues VALUES (3, 11, 'Female') INSERT @MemberLookupValues VALUES (7, 69, 'Prostate Cancer') INSERT @MemberLookupValues VALUES (7, 70, 'Breast Cancer') DECLARE @MemberValues TABLE (MemberID INT, OptionID INT, ValueID INT) INSERT @MemberValues VALUES (1, 3, 10) INSERT @MemberValues VALUES (1, 7, 69) INSERT @MemberValues VALUES (1, 7, 70) INSERT @MemberValues VALUES (2, 3, 11) INSERT @MemberValues VALUES (2, 7, 69) SELECT * FROM @MemberLookupValues SELECT * FROM @MemberValues I've highlighted the values that must be stopped. So the basic check would be, IF OptionID = 3 AND ValueID = 10 then it must not allow you to insert the values OptionID = 7 AND ValueID = 70 I hope that makes sense. Any help will be greatly appreciated, if you need any more informaiton then just ask, Kind Regards Carel Greaves
View Replies !
|
TRACKER
