I keep getting an exception when trying to cast an output param as a float type. the SPROC is marked as float OUTPUT and the Cost column in the database is a float type as well. And there are no nulls in the cloumn either. here is how my code looks:
SqlParameter prmCost= new SqlParameter("@Cost", SqlDbType.Float,8);
prmCost.Direction=ParameterDirection.Output;
cmd.Parameters.Add(prmCost);
//...blah blah blah
//invalid cast gets throw on here (it happens with all my float types)
{ //setup the connection and connection string SqlConnection addTutor = new SqlConnection(); addTutor.ConnectionString = "data source=QUAKEMASTER;" + "initial catalog=DThomas;uid=sa;password=**********;"; string InsertTutor = "INSERT INTO Tutors (firstname, lastname, tutorID, office)Values(@firstName, @lastName,@tutorID,@location)"; SqlCommand insertTutor = new SqlCommand(InsertTutor); insertTutor.Connection = addTutor; //populating an array of sqlParameters performs the same function as an SqlParameters.Add SqlParameter[] param = new SqlParameter[4]; param[0] = new SqlParameter("@firstName",txtFirstName.Text); param[1] = new SqlParameter("@lastName",txtLastName.Text); param[2] = new SqlParameter("@tutorID",int.Parse(txtTutorID.Text)); param[3] = new SqlParameter("@location",listOffice.SelectedItem.ToString()); try { //open the connection addTutor.Open(); //execute the NonSqlQuery insertTutor.ExecuteNonQuery(); //close the connection insertTutor.Connection.Close(); } catch (Exception ThatFeckedUp) { throw ThatFeckedUp; } finally { addTutor.Close(); }
}
Sure i've missed something just not sure what.....
Not sure if there's a GP 8.0 forum, so giving this one a go.
I've added an AFTER UPDATE trigger to the RM00101 table (customer master) in a Great Plains 8.0 SQL Server 2000 SP4 DB. The trigger assigns field values to variables, constructs an update query, and executes the query on a table in a linked SQL Server 2005 DB.
The trigger works fine when fired as a result of a direct field update made through Enterprise Manager. However, when the same update is made through the Great Plains GIU (customer card window), an exception error is thrown:
"Unhandled database exception: A Save operation on table €˜RM_Customer_MSTR€™ failed accessing SQL data
EXCEPTION_CLASS_DB DB_ERR_SQL_DATA_ACCESS_ERR€?
The odd thing is that if I drop the trigger from the RM00101 table, the exception error still occurs. Not just on the record originally updated, but on all records and all fields within the record.
Code for the trigger follows:
SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO
CREATE TRIGGER CTrig_Logic_Update_Customer ON RM00101
AFTER UPDATE AS
IF UPDATE(CUSTNMBR) or UPDATE(CUSTCLAS) or UPDATE(CNTCPRSN) or UPDATE(STMTNAME) or UPDATE(SHRTNAME) or UPDATE(TAXSCHID) or UPDATE(ADDRESS1) or UPDATE(ADDRESS2) or UPDATE(ADDRESS3) or UPDATE(CITY) or UPDATE(STATE) or UPDATE(ZIP) or UPDATE(PHONE1) or UPDATE(FAX) or UPDATE(SLPRSNID) or UPDATE(PYMTRMID) or UPDATE(PRCLEVEL) or UPDATE(SALSTERR) or UPDATE(INACTIVE) or UPDATE(HOLD) or UPDATE(CRLMTAMT)
BEGIN
DECLARE @Server_Name Varchar(25), @Logic_DB_Name Varchar(25), @SQLStr nvarchar(1000), @CustomerN int, @SICCode int, @ARContact Varchar(35), @LongName Varchar(50), @CustomerName Varchar(24), @SalesTaxCode Int, @AddrLine1 Varchar(40), @AddrLine2 Varchar(40), @AddrLine3 Varchar(40), @City Varchar(30), @StateProv Varchar(4), @PostalCode Varchar(15), @TelephoneN Varchar(25), @FaxTelephoneN Varchar(25), @SalespersonN Int, @TermsCode Varchar(60), -- Put the customer terms into the CommentN1 field @CustRateSched Int, @SalesAreaCode Int, @InactivePurge Tinyint, @CreditStatus Tinyint, @CreditLimit Int
------- Get the new Customer data from Inserted table
SELECT @CustomerN = CAST(RTRIM(i.CUSTNMBR) as Integer), @SICCode = ISNULL((SELECT Dex_Row_ID FROM RM00201 WHERE RM00201.CLASSID = i.CUSTCLAS),0), @ARContact = RTRIM(i.CNTCPRSN), @LongName = RTRIM(i.STMTNAME), @CustomerName = RTRIM(i.SHRTNAME), @SalesTaxCode = ISNULL((SELECT Dex_Row_ID FROM TX00101 WHERE TX00101.TAXSCHID = i.TAXSCHID),0), @AddrLine1 = RTRIM(i.ADDRESS1), @AddrLine2 = RTRIM(i.ADDRESS2), @AddrLine3 = RTRIM(i.ADDRESS3), @City = RTRIM(i.CITY), @StateProv = RTRIM(LEFT(i.STATE,2)), @PostalCode = RTRIM(i.ZIP), @TelephoneN = RTRIM(LEFT(i.PHONE1,10)), @FaxTelephoneN = RTRIM(LEFT(i.FAX,10)), @SalespersonN = RTRIM(i.SLPRSNID), @TermsCode = RTRIM(i.PYMTRMID), @CustRateSched = RTRIM(i.DEX_ROW_ID), @SalesAreaCode = ISNULL((SELECT Dex_Row_ID FROM RM00303 WHERE RM00303.SALSTERR = i.SALSTERR),0), @InactivePurge = i.INACTIVE, @CreditStatus = i.HOLD, @CreditLimit = i.CRLMTAMT FROM inserted i
------- Get Logic server name and database name
SELECT @Server_Name = RTRIM(l.Server_Name), @Logic_DB_Name = RTRIM(l.Logic_DB_Name) FROM tbl_Logic_DB l
------- Insert new Customer record into Logic database
I have a dataset with 500,000 records and I'm getting the following error with ReportViewer control for local report. "An error has occurred during local report processing. An error has occurred during report processing. Exception of type System.OutOfMemoryException was thrown. any help with this would be highly appreciated.
WHEN 'DAYS' THEN DATEDIFF(dd, CHECKOUT_TS, GETDATE())
ELSE DELIVER_ID END
FROM REQUEST
If @sortcolumn has 'DATE' or 'DAYS' it runs fine....any other value, I get an exception "Conversion failed when converting datetime from character string". All columns except CHECKOUT_TS are of type varchar.
The following stored procedure sets a value for the @@RowCount global variable. How do I make use of it in the Data Access Layer? When I set the SPROC as the source for the object, the value numberRows does not appear to be an option. In the end I just want to set the value of @@RowCount to a Label.Text What should I do?ALTER PROCEDURE dbo.ap_Select_ModelRequests_RequestDateTime @selectDate datetime ,@selectCountry Int AS
SELECT DISTINCT configname FROM ModelRequests JOIN CC_host.dbo.usr_cmc As t2 ON t2.user_id = ModelRequests.username JOIN Countries ON Countries.Country_Short = t2.country WHERE RequestDateTime >= @selectDate and RequestDateTime < dateadd(dd,1, @selectDate) AND configname <> '' AND interfacename LIKE '%DOWNLOAD%' AND result = 0 AND Country_ID = @selectCountry ORDER BY configname SELECT @@RowCount As numberRows
I am working through a tutorial and have stumbled into something that does not quite make sense to me. I was wondering if someone could help me understand this.
I have created this SP, this all makes sense to me due to the assignment of the artistname column value to the @artistname variable. In other words what is on the right of the equal sign is assigned to what is on the left.
create procedure ShowPopStyle @style varchar(30), @artistname varchar(30) output as select @artistname = artistname from artists where style = @style go
Now when you execute this SP, what does not makes sense to me is if I need to declare a variable to hold the output, which I presume is null, shouldn't the @returnname be on the left side of the equal sign instead of the right?
declare @returnname varchar(30) -- variable for the output from the procedure exec showpopstyle 'Pop', @artistname = @returnname output print @returnname
I need to return a value to VB. I've tried returning a numeric value NUMERIC(25,20) via an output parameter but this didn't work. I'm know at a point in wich I created a bigint and multiplied the value so that the decimals are gone. However it only returns NULL?!?!?!?!!?!? Here's part of my stored proc
CREATE PROCEDURE dbo.uspCalcWeightedAverage @StartDate2 varchar(10), @EndDate2 varchar(10), @InMarket nvarchar(50), @InProductType int, @InWeekDay int, @WeightedAverage bigint OUTPUTAS......SELECT @WeightedAverage = cast(10000000000 * (SUM(HHF.FACTOR) / COUNT(PDF.FLAG)) as bigint)FROM TBL_PRODUCTDEFS PDF INNER JOIN #DATESBETWEENINTERVAL DBI ON DATEPART(HH, [DBI].[DATE]) = [PDF].[HOUR] INNER JOIN tbl_historichourlyfactors HHF ON DATEPART(D,DBI.DATE) = HHF.DayID AND [PDF].[HOUR] = [HHF].[HOUR] AND DATEPART(M,DBI.DATE) = [HHF].[Month]WHERE PDF.MARKETID = @InMarketID AND PDF.PRODUCTTYPEID = @InProductTypeID AND [PDF].[WD-WE] = @InWeekDay AND HHF.MARKETID = @InMarketID AND PDF.FLAG = 1GROUP BY FLAG
When I retrieve the output param it returns a NULL value. the properties in VB say that the parameter has the following props: attribute 64 (Long) NumericScale 0 (Byte) Precision 19 (Byte) Size 0 (ADO_LNGPTR) Type adBigInt Value Null
I try to return it with the following code (got the code from a friend)
Public Function RunProcedure(ByVal v_strStoredProcName As String, ByRef r_varParamValues() As Variant) As ADODB.RecordsetDim objAdoRecordset As ADODB.RecordsetDim objAdoCommand As ADODB.CommandDim lngCtr As Long On Error GoTo RunCommand_Error ' Create cmd object Set objAdoCommand = New ADODB.Command Set objAdoCommand.ActiveConnection = m_oAdoConnection objAdoCommand.ActiveConnection = m_oAdoConnection objAdoCommand.CommandText = v_strStoredProcName objAdoCommand.CommandType = adCmdStoredProc Call objAdoCommand.Parameters.Refresh 'Stop For lngCtr = 0 To UBound(r_varParamValues) If objAdoCommand.Parameters(lngCtr + 1).Direction = adParamInput Then objAdoCommand.Parameters(lngCtr + 1).Value = r_varParamValues(lngCtr) End If Next Set objAdoRecordset = New ADODB.Recordset objAdoRecordset.CursorLocation = adUseClient Set objAdoRecordset = objAdoCommand.Execute 'Stop For lngCtr = 0 To objAdoCommand.Parameters.Count - 1 If objAdoCommand.Parameters(lngCtr).Direction = adParamOutput Or objAdoCommand.Parameters(lngCtr).Direction = adParamInputOutput Then r_varParamValues(lngCtr - 1) = objAdoCommand.Parameters(lngCtr).Value End If Next Set RunProcedure = objAdoRecordsetRunCommand_Exit: ' Collect your garbage here Exit FunctionRunCommand_Error: ' Collect your garbage here Call g_oGenErr.Throw("WeatherFcst.CDbsConn", "RunCommand")End Function
This is my SProc: CREATE PROCEDURE dbo.ap_Select_ModelRequests_RequestDateTime /* Input or Output Parameters *//* Note that if you declare a parameter for OUTPUT, it can still be used to accept values. *//* as is this procedure will very well expect a value for @numberRows */@selectDate datetime ,@selectCountry int ,@numberRows int OUTPUT AS SELECT DISTINCT configname FROM ModelRequests JOIN CC_host.dbo.usr_smc As t2 ON t2.user_id = ModelRequests.username JOIN Countries ON Countries.Country_Short = t2.country WHERE RequestDateTime >= @selectDate and RequestDateTime < dateadd(dd,1, @selectDate) AND configname <> '' AND interfacename LIKE '%DOWNLOAD%' AND result = 0 AND Country_ID = @selectCountry ORDER BY configname /* @@ROWCOUNT returns the number of rows that are affected by the last statement. *//* Return a scalar value of the number of rows using an output parameter. */SELECT @numberRows = @@RowCount GO And This is my code. I know there will be 100's of records that are selected in the SProc, but when trying to use the Output Parameter on my label it still says -1Protected Sub BtnGetModels_Click(ByVal sender As Object, ByVal e As System.EventArgs) Dim dateEntered As String = TxtDate.TextDim selectCountry As String = CountryList.SelectedValue Dim con As New SqlClient.SqlConnection con.ConnectionString = "Data Source=10.10;Initial Catalog=xx;Persist Security Info=True;User ID=xx;Password=xx"Dim myCommand As New SqlClient.SqlCommand myCommand.CommandText = "ap_Select_ModelRequests_RequestDateTime" myCommand.CommandType = CommandType.StoredProceduremyCommand.Parameters.AddWithValue("@selectDate", dateEntered) myCommand.Parameters.AddWithValue("@selectCountry", CInt(selectCountry))Dim myParam As New SqlParameter("@numberRows", SqlDbType.Int) myParam.Direction = ParameterDirection.Output myCommand.Parameters.Add(myParam) myCommand.Connection = con con.Open()Dim reader As SqlDataReader = myCommand.ExecuteReader()Dim rowCount As Integer = reader.RecordsAffected numberParts.Text = rowCount.ToString con.Close() End Sub
I have a stored proc that I'd like to return an output param from. I'm using a SQLDataSource and invoking the Update method which calls the sproc.The proc looks like this currently: ALTER proc [dbo].[k_sp_Load_IMIS_to_POP_x]@vcOutputMsg varchar(255) OUTPUT AS SET NOCOUNT ON ; select @vcOutputMsg = 'asdf'
The code behind looks like this:protected void SqlDataSource1_Updated(object sender, SqlDataSourceStatusEventArgs e) { //handle error on return string returnmessage = (string)e.Command.Parameters["@vcOutputMsg"].Value; }
On the page source side, the params are defined declaratively: <UpdateParameters> <asp:Parameter Direction="ReturnValue" Name="RETURN_VALUE" Type="Int32" /> <asp:Parameter Direction="InputOutput" Name="vcOutputMsg" Type="String" /> </UpdateParameters>
When I run it, the code behind throws the following exception - "Unable to cast object of type 'System.DBNull' to type 'System.String'" PLEASE HELP! What am I doing wrong? Is there a better way to get output from a stored proc?
I have a stored proc that will insert a new row into a table with the values of the parameters you pass in. I need it to return the value of the ID that's generated by an Identity column once the row has been written and that value has been generated. If I just do a SELECT Max(), I could accidentally grab a row written by someone else, right?
My current sp looks like this:
CREATE PROCEDURE sp_SaveNewLabel
-- @LabelID int output @LabelType int , @Logo int , @Field01 char(30)
AS
INSERT INTO tbLabel (LabelType , Logo , Field01)
VALUES (@LabelType , @Logo , @PrintCC , @Field01)
How do I grab the new LabelID (the column is int, Identity) and return it from the stored proc. Any help would be greatly appreciated...
is it true that I will not be able to use the returned value from an sp that is called on every row from an oledb command transformation? I see all kinds of complaints on the web but cant determine if this would be a waste of time. I'd like to append the returned value (which is calculated and cannot be joined in the buffer) to the data on its way out of the transformation.
here's my code:my $sth = $dbhSQL->prepare('{call proc(?,?,?)}');$sth->bind_param(1,"asd");$sth->bind_param(2,"klm");$sth->bind_param_inout(3,$no_go, 1000);$sth->execute;print "no go = $no_go";while(my @row=$sth->fetchrow_array){print "@row";}$sth->finish;Here's my stored procedure:CREATE PROCEDURE proc@id varchar(50),@iyt varchar(20),@no_go int OUTPUTASSET NOCOUNT ONDECLARE @id_err int,@ans_glue_err intBEGIN TRANSACTIONSELECT user_id FROM myTableWHERE user_id=@id AND iyt=@iytSET @id_err = @@ERRORIF @@ROWCOUNT <> 0BEGINSELECT date,date_mod FROM ans_glueWHERE user_id=@idSET @no_go = 0SET @ans_glue_err=@@ERRORENDELSEBEGINSET @no_go = 1ENDIF @id_err = 0 AND @ans_glue_err = 0BEGINCOMMIT TRANSACTIONENDELSEBEGINROLLBACK TRANSACTIONENDthe procedure runs perfectly in Cold Fusion, returning both recordsetsand output param, but in perl, it won't print the output param and Idon't know how to access the second recordsetHELP!
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON SET NOCOUNT ON
GO -- ============================================= ALTER PROCEDURE [dbo].[spPK] -- Add the parameters for the stored procedure here @varNSC varchar(4), @varNC varchar(2), @varIIN varchar(7), @varIMCDMC varchar(8), @varOut as int output AS Declare @varPK int set @varPK = 0 BEGIN
--This checks Method 1 --NSC = @varNSC --NC = @varNC --IIN = @varIIN begin if exists (select Item_id From Item Where NSC = @varNSC and NC = @varNC and IIN = @varIIN) set @varPK = (select Item_id From Item Where NSC = @varNSC and NC = @varNC and IIN = @varIIN) set @varOut = @varPK if @varPK <> 0 Return end
[There are some more methods here]
Return
END
How do I get at the output value?
I have tried using derived column and ole db command but can't seem to grasp how to pass the value to the derived column. I can get oledb command to run using 'exec dbo.spPK ?, ?, ?, ?, ? output' but don't know what to do from here.
In a Database "AP" of my SQL Server Management Studio Express (SSMSE), I have a stored procedure "spInvTotal3":
CREATE PROC [dbo].[spInvTotal3]
@InvTotal money OUTPUT,
@DateVar smalldatetime = NULL,
@VendorVar varchar(40) = '%'
This stored procedure "spInvTotal3" worked nicely and I got the Results: My Invoice Total = $2,211.01 in my SSMSE by using either of 2 sets of the following EXEC code: (1) USE AP GO --Code that passes the parameters by position DECLARE @MyInvTotal money EXEC spInvTotal3 @MyInvTotal OUTPUT, '2006-06-01', 'P%' PRINT 'My Invoice Total = $' + CONVERT(varchar,@MyInvTotal,1) GO (2) USE AP GO DECLARE @InvTotal as money EXEC spInvTotal3 @InvTotal = @InvTotal OUTPUT, @DateVar = '2006-06-01', @VendorVar = '%' SELECT @InvTotal GO //////////////////////////////////////////////////////////////////////////////////////////// Now, I want to print out the result of @InvTotal OUTPUT in the Windows Application of my ADO.NET 2.0-VB 2005 Express programming. I have created a project "spInvTotal.vb" in my VB 2005 Express with the following code:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Public Class Form1
Public Sub printMyInvTotal()
Dim connectionString As String = "Data Source=.SQLEXPRESS; Initial Catalog=AP; Integrated Security=SSPI;"
Dim conn As SqlConnection = New SqlConnection(connectionString)
Try
conn.Open()
Dim cmd As New SqlCommand
cmd.Connection = conn
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "[dbo].[spInvTotal3]"
Dim param As New SqlParameter("@InvTotal", SqlDbType.Money)
param.Direction = ParameterDirection.Output
cmd.Parameters.Add(param)
cmd.ExecuteNonQuery()
'Print out the InvTotal in TextBox1
TextBox1.Text = param.Value
Catch ex As Exception
MessageBox.Show(ex.Message)
Throw
Finally
conn.Close()
End Try
End Sub
End Class ///////////////////////////////////////////////////////////////////// I executed the above code and I got no errors, no warnings and no output in the TextBox1 for the result of "InvTotal"!!?? I have 4 questions to ask for solving the problems in this project: #1 Question: I do not know how to do the "DataBinding" for "Name" in the "Text.Box1". How can I do it? #2 Question: Did I set the CommandType property of the command object to CommandType.StoredProcedure correctly? #3 Question: How can I define the 1 output parameter (@InvTotal) and 2 input parameters (@DateVar and @VendorVar), add them to the Parameters Collection of the command object, and set their values before I execute the command? #4 Question: If I miss anything in print out the result for this project, what do I miss?
when I debug my appliction with .netcf , the output panel display a lot of exception
as shown the following, But it still does work , just very slow. What's matter?
'MobileCard.exe' (Managed): Loaded 'C:Program FilesMicrosoft Visual Studio 8SmartDevicesSDKCompactFramework2.0v2.0DebuggerBCLmscorlib.dll', No symbols loaded. 'MobileCard.exe' (Managed): Loaded 'c:documents and settingspan桌�mobilecardmobilecardin eleaseMobileCard.exe', Symbols loaded. 'MobileCard.exe' (Managed): Loaded 'C:Program FilesMicrosoft Visual Studio 8SmartDevicesSDKCompactFramework2.0v2.0DebuggerBCLSystem.Windows.Forms.dll', No symbols loaded. 'MobileCard.exe' (Managed): Loaded 'C:Program FilesMicrosoft Visual Studio 8SmartDevicesSDKCompactFramework2.0v2.0DebuggerBCLSystem.dll', No symbols loaded. 'MobileCard.exe' (Managed): Loaded 'C:Program FilesMicrosoft Visual Studio 8SmartDevicesSDKCompactFramework2.0v2.0DebuggerBCLSystem.Drawing.dll', No symbols loaded. 'MobileCard.exe' (Managed): Loaded 'C:Program FilesMicrosoft Visual Studio 8SmartDevicesSDKCompactFramework2.0v2.0DebuggerBCLSystem.Data.dll', No symbols loaded. 'MobileCard.exe' (Managed): Loaded 'C:Program FilesMicrosoft Visual Studio 8SmartDevicesSDKCompactFramework2.0v2.0DebuggerBCLSystem.Xml.dll', No symbols loaded. 'MobileCard.exe' (Managed): Loaded 'c:documents and settingspan桌�mobilecardmobilecardin eleaseOpenNETCF.Windows.Forms.dll', No symbols loaded. 'MobileCard.exe' (Managed): Loaded 'c:documents and settingspan桌�mobilecardmobilecardin eleaseOpenNETCF.Drawing.dll', No symbols loaded. 'MobileCard.exe' (Managed): Loaded 'C:Program FilesMicrosoft Visual Studio 8SmartDevicesSDKCompactFramework2.0v2.0DebuggerBCLMicrosoft.WindowsCE.Forms.dll', No symbols loaded. 'MobileCard.exe' (Managed): Loaded 'c:program filesmicrosoft visual studio 8smartdevicessdksql servermobilev3.0System.Data.SqlServerCe.dll', No symbols loaded. A first chance exception of type 'System.IO.FileNotFoundException' occurred in mscorlib.dll A first chance exception of type 'System.IO.FileNotFoundException' occurred in mscorlib.dll A first chance exception of type 'System.UnauthorizedAccessException' occurred in mscorlib.dll A first chance exception of type 'System.UnauthorizedAccessException' occurred in mscorlib.dll 'MobileCard.exe' (Managed): Loaded 'System.SR.dll', No symbols loaded. A first chance exception of type 'System.IO.FileNotFoundException' occurred in mscorlib.dll 'MobileCard.exe' (Managed): Loaded 'System.SR.resources.dll', No symbols loaded. A first chance exception of type 'System.IO.FileNotFoundException' occurred in mscorlib.dll A first chance exception of type 'System.IO.FileNotFoundException' occurred in mscorlib.dll A first chance exception of type 'System.UnauthorizedAccessException' occurred in mscorlib.dll A first chance exception of type 'System.UnauthorizedAccessException' occurred in mscorlib.dll A first chance exception of type 'System.UnauthorizedAccessException' occurred in mscorlib.dll A first chance exception of type 'System.UnauthorizedAccessException' occurred in mscorlib.dll A first chance exception of type 'System.UnauthorizedAccessException' occurred in mscorlib.dll A first chance exception of type 'System.UnauthorizedAccessException' occurred in mscorlib.dll A first chance exception of type 'System.UnauthorizedAccessException' occurred
Using C#, SQL Server 2005, ASP.NET 2, in a web app, I've tried removing the size from parameters of type NCHAR, NVARCHAR, and VARCHAR. I'd rather just send a string and let the size of the parameter in the SP truncate any extra chars if need be. I began getting the error below, and eventually realized it happened only with output parameters, as in the code snippet below.String[3]: the Size property has an invalid size of 0. par = new SqlParameter("@BusinessEntity", SqlDbType.NVarChar); par.Direction = ParameterDirection.Output; cmd.Parameters.Add(par); cmd.ExecuteNonQuery();What's the logic behind this? Is there any way around it other than either finding out what the size should be, or assigning a size larger than would ever be needed? ThanksMike Thomas
Does anybody know how could I calculate the new date(adding @c to @bor subtracting @b to @c), having for example a declaration like this:DECLARE @a CHAR(12)DECLARE @b DATETIMEDECLARE @c INTSET @b='3.04.04';SET @c=6and to calculate the number of days between two dates with this kindof declaration(@a-@b or @b - @a):DECLARE @a CHAR(12)DECLARE @b DATETIMEDECLARE @c INTSET @a='12.2.04';SET @b='3.04.04';Thanks in advance.
I can't take full credit for this. I want to share this with Jeff Moden who did the important research for this calculation here.
All I did was just adapting some old code according to the mantissa finding Jeff made and optimized it a little
Some test codeDECLARE@SomeNumber FLOAT, @BinFloat BINARY(8)
SELECT@SomeNumber = -185.6125, @BinFloat = CAST(@SomeNumber AS BINARY(8))
SELECT@SomeNumber AS [Original], CAST(@SomeNumber AS BINARY(8)) AS [Binary], dbo.fnBinaryFloat2Float(CAST(@SomeNumber AS BINARY(8))) AS [Converted], @SomeNumber - dbo.fnBinaryFloat2Float(CAST(@SomeNumber AS BINARY(8))) AS [Error]
And here is the code for the function.CREATE FUNCTION dbo.fnBinaryFloat2Float ( @BinaryFloat BINARY(8) ) RETURNS FLOAT AS BEGIN DECLARE@Part TINYINT, @PartValue TINYINT, @Mask TINYINT, @Mantissa FLOAT, @Exponent SMALLINT, @Bit TINYINT, @Ln2 FLOAT, @BigValue BIGINT
WHILE @Part <= 8 BEGIN SELECT@Part = @Part + 1, @PartValue = CAST(SUBSTRING(@BinaryFloat, @Part, 1) AS TINYINT), @Mask =CASE WHEN @Part = 2 THEN 8 ELSE 128 END
WHILE @Mask > 0 BEGIN IF @PartValue & @Mask > 0 SET @Mantissa = @Mantissa + EXP(-@Bit * @Ln2)
SELECT@Bit = @Bit + 1, @Mask = @Mask / 2 END END
RETURNSIGN(@BigValue) * @Mantissa * POWER(CAST(2 AS FLOAT), @Exponent - 1023) END Thanks again Jeff!
Greetings everyone, I am attempting to build my first application using Microsofts Sql databases. It is a Windows Mobile application so I am using Sql Server Compact 3.5 with Visual Studio 2008 Beta 2. When I try and insert a new row into one of my tables, the app throws the error message shown in the title of this topic. '((System.Exception)($exception)).Message' threw an exception of type 'System.NotSupportedException'
My table has 4 columns (i have since changed my FavoriteAccount datatype from bit to Integer) http://i85.photobucket.com/albums/k71/Scionwest/table.jpg
Account type will either be "Checking" or "Savings" when a new row is added, the user will select what they want from a combo box.
Next is a snap shot of my startup form. http://i85.photobucket.com/albums/k71/Scionwest/form.jpg
Where it says "Favorite Account: None" in the top panel, I am using a link label. When a user clicks "None" it will go to a account creation wizard, and set the first account as it's primary/favorite. As more accounts are added the user can select which will be his/her primary/favorite. For now I am just creating a sample account when the label is clicked in an attempt to get something working. Below is the code used.
account.FavoriteAccount = 1;//datatype is an integer, I have changed it since I took the screenshot.
financesDataSet.BankAccount.Rows.Add(account); //The next three lines where added while I was trying to get this to work. //I don't know if I really need them or not, I receive the error regardless if these are here or not.
catch (global:ystem.InvalidCastException e) { //Stops at the following line, this error was caused by 'if (this.financesDataSet.BankAccount[num].FavoriteAccount == 1)'
throw new global:ystem.Data.StrongTypingException("The value for column 'FavoriteAccount' in table 'BankAccount' is DBNull.", e);
I have no idea what I am doing wrong, all of the code I used I retreived from Microsofts help documentation included with VS2008. I have tried used my TableAdapter.Insert() method and it still failed when it got to
if (this.financesDataSet.BankAccount[num].FavoriteAccount == 1)
in my refreshDatabase() method it still failed.
When I look, the data has been added into the database, it's just when I try to retreive it now, it bails on me. Am I retreiving the information wrong?
When running the package in VisualStudio it runs properly, but if I let this package run as part of an SQL-Server Agent job, I got the message "The script threw an exception: Exception of type 'System.OutOfMemoryException' was thrown." on my log and the package ends up with an error.
Both times it is exactly the same package on the same server, so I don't know how the debug or even if there is anything I need to debug?
Can someone please help me with this? I'm losing all my hair trying to figure it out. I've tried all that I can think of. I am getting the Error converting data type varchar to numeric.
Thanks Sam "O"
cmdInsert = New SqlCommand( "INSERT INTO tmp_blank (sessionid, ssn,job,sun,mon,tue,wed,thu,fri,sat,totcol) SELECT '" & Session.SessionId & "', ssn,job,sun,mon,tue,wed,thu,fri,sat, (cast(sun as numeric(4,2)) + cast(mon as numeric(4,2)) + cast(tue as numeric(4,2)) + cast(wed as numeric(4,2)) + cast(thu as numeric(4,2)) + cast(fri as numeric(4,2)) + cast(sat as numeric(4,2))) as totcol from " & strTableName, conTimeCard)
I'm new to SQL Server and I have to finish a VB.Net program from an employee that has left the company.
I have a table with column 'vanafdatum' witch has datatype char but they stored only dates in this column ( like this: 13/09/2006).
When I try to run the next querie I receive an error message "The conversion of a char data type to a datetime data type resulted in an out of range datetime value".
This is the querie I try to run
SELECT MAX(CAST(vanafdatum AS datetime)) AS vanaf_datum FROM tarieven WHERE (vanafdatum < (SELECT getdate()))
Is there any way I can cast the char datatype to a datetime datatype without getting this error. I allready tried to change the data type from char to datetime, but doesn't work either. A possible solution is to erase the column an manually fill in all the fields again in the data type datetime, but that seems a little stupid to do. So I wonder if there is a solution that can change the data type for all rows in the database without losing any data.
I've got an ftp task that will be downloading a couple of files each night. today they're called
blah20060830blah
the date value in the middle changes each day. I'm trying to adjust this value with an expression. The expression doesn't want to cast my getdate function into a string that this property will accept. I know i'm missing something stupid.
I would like to know if casting is supported in SQL Server Compact Edition. I get an error when i attempt to cast a datetime or bigint value to nvarchar or ntext. CAST(datetime AS nvarchar(15))
Does anybody knows if its supported or how could i cast values?
Hi,I've come across a problem that requires i cast a string to a date, had a go but can't get it to work properly.SELECT EventID, EventName, EventShortDesc, EventLink, EventStartDateFROM dbo.tbl_EventWHERE (CAST EventStartDate AS DATE) > GetDate()ORDER BY EventStartDate DESCBasically i only want events which haven't expired (were before the current date) to be returned. EventStartDate is held as an varchar(10) therefore needs to be cast before i can compare. Is this the best way or is there a better one? If it is how do i get it to work?Any help would be great thanks,drazic19
Hi,I'm trying to execute server-side trace with SqlServer 2000 oncluster. After scripting trace by Profiler I executed this script inQueryanalyzer and got error code 12. According to BOL this code saysthat file is not created but I don't know why, is there are anyobstacles which preventing to add this trace??Does anybody had that problem?If it helps I added script which I tried to execute:-- Create a Queuedeclare @rc intdeclare @TraceID intdeclare @maxfilesize bigintset @maxfilesize = 5exec @rc = sp_trace_create @TraceID output, 0, N'C:Profiler_result esult.trc', @maxfilesize, NULLif (@rc != 0) goto error-- Client side File and Table cannot be scripted-- Set the eventsdeclare @on bitset @on = 1exec sp_trace_setevent @TraceID, 10, 1, @on/*here are n-setevents*/exec sp_trace_setevent @TraceID, 43, 35, @on-- Set the Filtersdeclare @intfilter intdeclare @bigintfilter bigintexec sp_trace_setfilter @TraceID, 1, 1, 6, N'history'exec sp_trace_setfilter @TraceID, 1, 1, 6, N'move_history'exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'exec sp_trace_setfilter @TraceID, 35, 1, 6, N'kis'-- Set the trace status to startexec sp_trace_setstatus @TraceID, 1-- display trace id for future referencesselect TraceID=@TraceIDgoto finisherror:select ErrorCode=@rcfinish:goThanks in advance for any adviceRegards,Bartolo
