I designed my BLL to work as follows: 'Update existing company
<System.ComponentModel.DataObjectMethodAttribute _
(System.ComponentModel.DataObjectMethodType.Update, True)> _
Public Function UpdateCompany(ByVal Original_CompanyID As Int64, ...) As Boolean
'Get current record
Dim Companies As biodata.CompaniesDataTable = CompaniesAdapter.GetCompanyByID(Original_CompanyID)
'If no record exists, Update fails
If Companies.Count = 0 Then
Throw New ApplicationException("Company does not exist.")
End If
Dim Company As biodata.CompaniesRow = Companies(0)
'Set all the values to load into the database
Company.CompanyName = CompanyName
...
Dim RowsAffected As Integer = CompaniesAdapter.Update(Company)
Return (RowsAffected = 1)
End Function
Recently I deleted a column in my SQL database. In order to account for this change, I deleted the same column in my DAL. Then, because of the obvious compile errors that would occur if I didn't, I deleted all references to that column in my BLL. My DAL automatically creates the Update and Insert statements. Now, the error I'm getting is as follows:
Exception Details: System.Data.SqlClient.SqlException: Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.
The statement has been terminated.
Source Error:
Line 18409: System.ComponentModel.Design.HelpKeywordAttribute("vs.data.TableAdapter")> _
Line 18410: Public Overloads Overridable Function Update(ByVal dataRow As System.Data.DataRow) As Integer
Line 18411: Return Me.Adapter.Update(New System.Data.DataRow() {dataRow})
Line 18412: End Function
Line 18413:
Stack Trace:
[SqlException (0x80131904): Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.
The statement has been terminated.]
System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount) +200
System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount) +104
System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping) +3042
System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows) +345
biodataTableAdapters.CompaniesTableAdapter.Update(DataRow dataRow) in C:WINDOWSMicrosoft.NETFrameworkv2.0.50727Temporary ASP.NET Files
oot67b7db20aea00f88App_Code.nf1apfb7.1.vb:18411
CompaniesBLL.UpdateCompany(Int64 Original_CompanyID, String CompanyName, String Division, String Website, Nullable`1 CompanyInactive, String CompanyInactiveReason, Nullable`1 EmployeeCount, Nullable`1 YearFounded, Nullable`1 YearStartedBiobased, Nullable`1 NAICS, String Address1, String Address2, String City, String StateCode, String Territory, String CountryCode, String Zip, String CompanyNotes, String Prefix, String FName, String LName, String Suffix, String Title, String Email, String Phone1, String PhoneType1, String Phone2, String PhoneType2, String Phone3, String PhoneType3, String UpdateType, String UpdatedBy, Int64 SecurityLevel, Int64[] Flags) in F:App_CodeBLLCompaniesBLL.vb:376
Now, this exception makes no sense to me because the DAL should automatically fix the update and insert statements itself, and I never directly access those statements. The error is occuring at CompaniesAdapter.Update(Company) in my code above. Any ideas?
I'll try re-posting this, as the other post didn't seem to take. I'm a long time "classic" ASP developer used to SQL Server 2000. I'm messing around now with Visual Web Developer 2005 and SQL Server Express 2005 (both latest versions). I've inclided an MDF file with my ASP.NET project, and have created some tables and entered some data. From time to time, when starting the site, I receive the following error:
A transport-level error has occurred when sending the request to the server. (provider: Named Pipes Provider, error: 0 - No process is on the other end of the pipe.) It always appears on this line:
Dim retReader As SqlDataReader = MyCommand.ExecuteReader I've posted this question elsewhere, and haven't received any insight as to what the problem may be. Can anyone help? Thanks; Brad Brening
Hello, I am having trouble catching an sqlexception. When i try to put data into my database that is duplicate i want it to display an error message and so catch the exception however i am getting errors. The catch statement i am using is:Label lab1 = (Label)FormView1.FindControl("Label10"); try { EditSqlDataSource.Insert();Response.Redirect("Appointments.aspx"); }catch (Exception ex) {lab1.Text = "Appointment Slot Already Taken: Booking Not Made."; } and the error that i am getting is: Error 1 The type or namespace name 'SqlException' could not be found (are you missing a using directive or an assembly reference?) Thanks in advance Mike
Hi, Here is my stored procedure and when I'm trying to use it through my code, it throws this SqlException. As it shows @JulianDate is infact a parameter of my SP. What am I doin wrong here??
CREATE PROC CabsSchedule_Save ( @JulianDatesmallint, @SiteCodesmallint, @YearMonthsmallint, @CalendarDaysmallint, @BillPeriodsmallint, @WorkDaysmallint, @CalDayBillRcvdvarchar(30), @Remarksvarchar(50) ) AS DELETE FROM CabsSchedule WHERE YearMonth = @YearMonth AND SiteCode = @SiteCode;
SET @InsQuery = 'INSERT INTO CabsSchedule(JulianDate, SiteCode, YearMonth, CalendarDay, BillPeriod, WorkDay ' SET @ValInsQuery = 'VALUES (@JulianDate, @SiteCode, @YearMonth, @CalendarDay, @BillPeriod, @WorkDay'
IF NOT @CalDayBillRcvd IS NULL BEGIN SET @InsQuery = @InsQuery + ', CalDayBillRcvd, ' SET @ValInsQuery = @ValInsQuery + ', @CalDayBillRcvd, ' END
IF NOT @CalDayBillRcvd IS NULL BEGIN SET @InsQuery = @InsQuery + ', Remarks' SET @ValInsQuery = @ValInsQuery + ', @Remarks' END
BEGIN SET @ValInsQuery = @ValInsQuery + ')' SET @FullQuery = @InsQuery + @ValInsQuery END GO
Error Details @JulianDate smallint, is not a parameter for procedure CabsSchedule_Save. 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.Data.SqlClient.SqlException: @JulianDate smallint, is not a parameter for procedure CabsSchedule_Save
I am new to Sql Server 2005, I am using vb.net 2005 to manually connect to the database but I am getting the following error Login failed for user 'BM-PC/B-Mustafa'
I have extended a table adapter(the one generated with the VS wizard) for one of my tables so i could add an overload to the Fill method to return rows within a certain datetime range from a smalldatetime collumns in the same table
Code Snippet
[System.Diagnostics.DebuggerNonUserCodeAttribute()] [System.ComponentModel.Design.HelpKeywordAttribute("vs.data.TableAdapter")] [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Fill, true)] public int Fill(WrkDS.TransferenciasDataTable dataTable, DateTime Data1, DateTime Data2) { //Instanciar comando com filtro
string ComandoQuery = "SELECT * FROM Transferencias WHERE Data >= '" + Data1.ToString("yyyyMMdd") + "' and Data <= '" + Data2.ToString("yyyyMMdd") + "'";
SqlCommand ComandoComFiltro = new SqlCommand(ComandoQuery, this._connection);
this.Adapter.SelectCommand = ComandoComFiltro; if ((this.ClearBeforeFill == true)) { dataTable.Clear(); } System.Windows.Forms.MessageBox.Show(ComandoComFiltro.CommandText); int returnValue = this.Adapter.Fill(dataTable); return returnValue; }
as you can see the string "ComandoQuery" is in a valid sql format, in fact I executed the exact same query in SQL server management studio and it returned the rows i wanted but when i try it in my application it trows an SqlException "The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value."
this is really making my head hurt, i cant seem to figure whats wrong, if someone has any clue please post here.
hi there i've copied my code below. I've checked my database and everything is fine. I'm not very sure about the error stated below.Can anyone help? Thank u very much!
Hello... string query = "insert into Offer_Assignment (Offer_Id,Cust_Id,Assigned_Date) values ("+ds.Tables[0].Rows[i][0]+","+ds.Tables[0].Rows[i][1] + ",convert(DateTime,'"+ds.Tables[0].Rows[i][2]+"'))"; When i excute this query in my system am getting this following error. "Conversion failed when converting datetime from character string."I am retrieving datetime value form another table And inserting into Offer_Assignment table.Am using Convert method to convert the value am getting from the dataset into datetime.But still am getting this error. Can you please help me to resolve ths issue. Thanks.
Hi, I get an error as I try to insert to SQL server using a stored procedure: "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."For some reason the stored procedure inserts every row twice. Does anyone know what could be wrong? Thanks in advance!string sCN = ConfigurationManager.ConnectionStrings["AdminConnectionString"].ConnectionString; SqlConnection sqlConnection1 = new SqlConnection(sCN);System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(); cmd.CommandType = System.Data.CommandType.Text;string strTemp = (Session["activityID"]).ToString();if (strTemp != "") { string sql2 = Session["txtFirstName"].ToString();string sql3 = Session["txtLastName"].ToString(); string sql4 = Session["txtStreetAddress"].ToString();string sql6 = Session["txtPostalAddress"].ToString(); string sql7 = Session["txtTelephoneHome"].ToString(); string sql10 = Session["txtEmail"].ToString(); string sql13 = Session["txtAppartmentNo"].ToString(); cmd.Parameters.AddWithValue("@mbrFirstName", sql2); cmd.Parameters.AddWithValue("@mbrLastName", sql3);cmd.Parameters.AddWithValue("@mbrStreetAddress", sql4); cmd.Parameters.AddWithValue("@mbrPostalAddress", sql6); cmd.Parameters.AddWithValue("@mbrTelephoneHome", sql7); cmd.Parameters.AddWithValue("@mbrEmail", sql10);
When an SQLException is thrown there is often more than one error in the SqlException.Errors property. Am I correct in the assumption that the farther down the list of the Errors the more specific th error gets? ie SqlException.Errors[1] is more specific than SqlException.Errors[0]?
I'm trying to return all the rooms that are in a specific area being selected in a ComboBox and I don't want any rooms that have no MacNo. I would have thought this would do it but they Visual Studio throws me a SqlException "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."
String dbsql = "SELECT a.RoomID " + " , a.Room " + " FROM tblRdrRm a " + " WHERE a.RoomID = (SELECT RoomID " + " FROM tblRdrInfo r " + " WHERE r.AreaID = '" + reaid + "' " + " AND r.MacNo IS NOT NULL ) " + " AND a.AreaID = '" + reaid + "' " +
" UNION " +
"SELECT b.RoomID " + " , b.Room " + " FROM tblRdrRm b " + " WHERE b.AreaID = '" + reaid + "' " + "";
Hi fellows! I have a problem that just driving me crazy, I have a program that using a web service as a mid tier connects to a SQL Server 2005, I using stored procedures in all my queries through ADO.net 2.0 data adapters, the problem that I can’t catch any exceptions in stored procedure except couple of them, like exce permeation denied, but even stuff like key violation or RAISERROR can’t be catched, to rollback my transactions, and this despite severity level of the error, I searched internet, there is couple guys posted similar problems but no body found the solutions, hoping to find one here, the problem that you can’t just ignore this problem because it is cracks the whole logic of the DB! Please help…
I am storing employee numbers in the aspnet_Profile table in the PropertyValuesString column. I want to query the aspnet_Profile/PropertyValuesString field to see if a user's employee ID has already been entered. I get a SqlException error in the App_Code.-u895job.0.cs file (the DAL) when I try to query the aspnet_Profile table using SubSonic (see details below). Is there a better way to query this table? What am I doing wrong?Thanks, -Allen-------------------------------------------------------- My code behind CS file:AspnetProfile userProfile = new AspnetProfile("PropertyValuesString", txtEmployeeID.Text);if (userProfile != null){ lblAnnounce.Text = "Got it";} The line in the App_Code.-u895job.0.cs file that throws the error:public AspnetProfile(string columnName, object columnValue){ SetSQLProps(); InitSetDefaults(); LoadByParam(columnName,columnValue); <-- THE LINE THAT THROWS THE ERROR} The error from Visual Studio 2008:SqlException was unhandled by user codeThe text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator. aspnet_Profile table structure:CREATE TABLE [dbo].[aspnet_Profile]( [UserId] [uniqueidentifier] NOT NULL, [PropertyNames] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [PropertyValuesString] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [PropertyValuesBinary] [image] NOT NULL, [LastUpdatedDate] [datetime] NOT NULL,PRIMARY KEY CLUSTERED ( [UserId] ASC) ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GOUSE [WebApp]GOALTER TABLE [dbo].[aspnet_Profile] WITH NOCHECK ADD CONSTRAINT [FK__aspnet_Pr__UserI__32767D0B] FOREIGN KEY([UserId])REFERENCES [dbo].[aspnet_Users] ([UserId])GOALTER TABLE [dbo].[aspnet_Profile] CHECK CONSTRAINT [FK__aspnet_Pr__UserI__32767D0B]
Hello, I have a problem with my Update sql server command. The error message is strange : System.Data.SqlClient.SqlException: Invalid column name 'CMD_DATE_ORDER'. Invalid column name 'CMD_REF_CLIENT'. at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at ASP.addorder_aspx.MasterGrid_Update(Object Sender, DataGridCommandEventArgs E) in C:Inetpubwwwrootproject1addorder.aspx:line 157 On line 157 I have : UpdateCommand.ExecuteNonQuery()
The Update code : Sub MasterGrid_Update(Sender As Object, E As DataGridCommandEventArgs) ' update the database with the new values ' get the edit text boxes Dim dateCom As String = CType(e.Item.FindControl("dateCom"), TextBox).Text Dim NumBon As String = CType(e.Item.FindControl("NumBon"), TextBox).Text Dim NomDest As String = CType(e.Item.FindControl("NomDest"), TextBox).Text Dim NovoieDest As String = CType(e.Item.FindControl("NovoieDest"), TextBox).Text Dim nomvoieDest As String = CType(e.Item.FindControl("nomvoieDest"), TextBox).Text Dim cpDest As String = CType(e.Item.FindControl("cpDest"), TextBox).Text Dim villeDest As String = CType(e.Item.FindControl("villeDest"), TextBox).Text Dim paysDest As String = CType(e.Item.FindControl("paysDest"), TextBox).Text Dim telDest As String = CType(e.Item.FindControl("telDest"), TextBox).Text Dim dateExp As String = CType(e.Item.FindControl("dateExp"), TextBox).Text
Dim myConnection As New SqlConnection(strConnect) Dim UpdateCommand As SqlCommand = new SqlCommand() UpdateCommand.Connection = myConnection ' Add to CLIID_LGN the selected value in the DropDownList Dim cliid As String cliid = Trim(DDL.SelectedItem.Value)
' execute the command Try myConnection.Open() UpdateCommand.ExecuteNonQuery()
Catch ex as Exception Message.Text = ex.ToString()
Finally myConnection.Close()
End Try
' Resort the grid for new records If AddingNew = True Then MasterGrid.CurrentPageIndex = 0 AddingNew = false End If
' rebind the grid MasterGrid.EditItemIndex = -1 BindMasterGrid() End Sub I have send a response.write instruction : the values are all good. In the sql server database, the syntax of this 2 fields CMD_DATE_ORDER and CMD_REF_CLIENT are good.
Using ADO.Net and SQL 2005, we have an exception handler in one of our .VB classes. We extract all the information from the exception and dump the stack into an email, but we would also like to see the query that caused the exception.
Is there an easy way to get the offending query without putting a try-catch around the hundreds of queries?
Cannot insert the value NULL into column 'PortfolioID', table 'SazamaBuilders.dbo.Portfolio'; column does not allow nulls. INSERT fails.The statement has been terminated. Looking for help with the correcting this. This is the complete error message I am receiving. When debugging and I try to save something the error comes up right next to objportfolio.Save(). The following is my code for the save button:Protected Sub btnSave_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSave.Click
If Page.IsValid ThenDim objportfolio As New BusinessObjects.Portfolio objportfolio.LoadByPrimaryKey(ViewState("ItemID")) If objportfolio.es.HasData = False Then objportfolio.AddNew() End IfWith objportfolio .portfolioName = Me.txtportfolioName.Text ' See if the directory is there, if not, create one If IO.Directory.Exists(Server.MapPath("../Gallery/")) = False ThenIO.Directory.CreateDirectory(Server.MapPath("../Gallery/")) End If If Me.FileUpload1.HasFile ThenMe.FileUpload1.SaveAs(Server.MapPath("../Gallery/" & Me.FileUpload1.FileName)) .Filename = Me.FileUpload1.FileName End If End With 'Now save the portfolio objportfolio.Save() 'Cleanup objportfolio = NothingResponse.Redirect("./manage_portfolio.aspx") End If
ASP.NET application, MS SQL DB, and a table with a timestamp(8) column.
Error: Cannot insert a non-null value into a timestamp column. Use INSERT with a column list or with a default of NULL for the timestamp column.
I'm using an SQLDataAdapter and typed DataSet, inserting a row into the dataset, and calling Update() to send changes to the DB.
The thing that baffles me about receiving this error, is that the DB column can be null and I get this error wheter I attempt to insert NULL or a valid byte array into the column.
(No - I am not trying to insert a datetime into the column.)
The error just doesn't seem too descriptive of the problem I'm having, and I'm quite confused.
I am new to ASP.NET and trying to learn. I am getting the problem when I try to insert data into SQL. The exception that I am getting is
Exception Details: System.Data.SqlClient.SqlException: An explicit value for the identity column in table 'Customers' can only be specified when a column list is used and IDENTITY_INSERT is ON.
My table name is 'Customers' and its as below. ----------------------------------------------------------------------- CustomerID--numeric---Identity Seed is 1000---Identity Increment is 1---No Nulls. Name--------varchar---No Nulls. address------Varchar---No Nulls. State--------Varchar---No Nulls. Zip-----------VarcHar---No Nulls. PhoneNumber-Varchar---No Nulls. Email---------Varchar---No Nulls.
and my code is (No fun of my code please--I am a learner) -----------------------------------------------------------------------
Imports System.Data
Imports System.Data.SqlClient
Public Class register
Inherits System.Web.UI.Page
Protected WithEvents cnNWind As SqlConnection
'Protected drCustomers As SqlDataReader
Protected WithEvents lbladd As System.Web.UI.WebControls.Label
Protected WithEvents lblState As System.Web.UI.WebControls.Label
Protected WithEvents lblcity As System.Web.UI.WebControls.Label
Protected WithEvents lblzip As System.Web.UI.WebControls.Label
Protected WithEvents lblPhone As System.Web.UI.WebControls.Label
Protected WithEvents lblEmail As System.Web.UI.WebControls.Label
Protected WithEvents TxtName As System.Web.UI.WebControls.TextBox
Protected WithEvents txtAdd As System.Web.UI.WebControls.TextBox
Protected WithEvents txtState As System.Web.UI.WebControls.TextBox
Protected WithEvents txtCity As System.Web.UI.WebControls.TextBox
Protected WithEvents txtZip As System.Web.UI.WebControls.TextBox
Protected WithEvents txtPhone As System.Web.UI.WebControls.TextBox
Protected WithEvents TxtEmail As System.Web.UI.WebControls.TextBox
Protected WithEvents BtnSubmit As System.Web.UI.WebControls.Button
Protected WithEvents Label1 As System.Web.UI.WebControls.Label
#Region " Web Form Designer Generated Code "
'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
End Sub
Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub
#End Region
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
End Sub
Private Sub BtnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles BtnSubmit.Click
Dim cmCustomers As New SqlCommand("INSERT INTO Customers VALUES ('',' " & TxtName.Text & "','" & txtAdd.Text & "','" & txtState.Text & "','" & txtZip.Text & "','" & txtPhone.Text & "','" & TxtEmail.Text & "')", cnNWind)
cmCustomers.ExecuteNonQuery()
cmCustomers.ExecuteNonQuery()
cnNWind.Close()
End If
End Sub
End Class
The Error I am getting: ----------------------------------------------------------------------- Server Error in '/SCart' Application. --------------------------------------------------------------------------------
An explicit value for the identity column in table 'Customers' can only be specified when a column list is used and IDENTITY_INSERT is ON. 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.Data.SqlClient.SqlException: An explicit value for the identity column in table 'Customers' can only be specified when a column list is used and IDENTITY_INSERT is ON.
Source Error:
Line 46: If cnNWind.State = ConnectionState.Open Then Line 47: Dim cmCustomers As New SqlCommand("INSERT INTO Customers VALUES ('','" & TxtName.Text & "','" & txtAdd.Text & "','" & txtState.Text & "','" & txtZip.Text & "','" & txtPhone.Text & "','" & TxtEmail.Text & "')", cnNWind) Line 48: cmCustomers.ExecuteNonQuery() Line 49: cnNWind.Close() Line 50: End If
[SqlException: An explicit value for the identity column in table 'Customers' can only be specified when a column list is used and IDENTITY_INSERT is ON.] System.Data.SqlClient.SqlCommand.ExecuteNonQuery() SCart.register.BtnSubmit_Click(Object sender, EventArgs e) in d:inetpubwwwrootSCart egister.aspx.vb:48 System.Web.UI.WebControls.Button.OnClick(EventArgs e) System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) System.Web.UI.Page.ProcessRequestMain()
-------------------------------------------------------------------------------- Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET Version:1.1.4322.573
The code works fine If I Dont use Identity Column, But I need to use Identity Column in my Project :( ...Please Help.
I am running Windows 2003 Server, ASP.NET 2.0, IIS, SQL 2005, and VS 2005. This is a Dell dual 3.0GHz with 3GB RAM. Off and on we get an error like below on simple queries that should take less than a second to run. Then if we wait a while and try again, it will run fast. The error states a timeout problem. As we look at RAM and CPU usuage, we are fine. However, SQL Profiler does show that the duration for some of these very simple queries take forever. One example query selects a row from a table with only 19 rows.
Server Error in '/' Application.
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.The statement has been terminated. 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.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.The statement has been terminated.Source Error:
Line 39: Line 40: MyConnUpdate.Open() Line 41: cmdUpdate.ExecuteNonQuery() Line 42: MyConnUpdate.Close() Line 43: End SubSource File: d:wwwrootwebewsblogviewDetails.aspx.vb Line: 41 Stack Trace:
[SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +857466 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +735078 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +149 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +886 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +132 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +415 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135 viewDetails.IncrementViewCount(Int32 newsID) in d:wwwrootwebewsblogviewDetails.aspx.vb:41 viewDetails.Page_Load(Object sender, EventArgs e) in d:wwwrootwebewsblogviewDetails.aspx.vb:8 System.Web.UI.Control.OnLoad(EventArgs e) +99 System.Web.UI.Control.LoadRecursive() +47 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1061
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42
Any help would be greatly appreciated! Thanks, Nate Dogg
can someone help me in this???? this function is throwing exception "sqlException unhandled in user code"."incorrect syntax near ?"
Public Overrides Sub UpdateUser(ByVal us As System.Web.Security.MembershipUser) Dim conn As New SqlConnection(connStr) Dim cmd As New SqlCommand("UPDATE users SET firstname = ?, lastname = ?, company= ?, address= ?, state= ? WHERE Username = ?", conn) Dim u As User = CType(us, User) cmd.Parameters.Add("@firstname", SqlDbType.VarChar, 50).Value = u.FirstName cmd.Parameters.Add("@lastname", SqlDbType.VarChar, 50).Value = u.LastName cmd.Parameters.Add("@company", SqlDbType.VarChar, 50).Value = u.Company cmd.Parameters.Add("@address", SqlDbType.NVarChar, 200).Value = u.Address cmd.Parameters.Add("@state", SqlDbType.VarChar, 50).Value = u.State cmd.Parameters.Add("@Username", SqlDbType.NVarChar, 64).Value = u.UserName Try conn.Open() cmd.ExecuteNonQuery() Catch e As SqlException Throw e 'Throw New Exception("Some Error occured during updation...") Finally conn.Close() End Try End Sub
I'm trying to retrieve an image from my ms sql server 2005, and i'm using VS2005....however, i have the following error during the compilation process Code in webform2.aspx.vb:Partial Class webform2 Inherits System.Web.UI.Page Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load Dim connstr As String = "Data Source=DCPRJ007SQLEXPRESS;Initial Catalog=mydatabase;Integrated Security=True" Dim cnn As New Data.SqlClient.SqlConnection(connstr) Dim cmd As New Data.SqlClient.SqlCommand("select * from dbo.images where id=" & Request.QueryString("id"), cnn) cnn.Open() Dim dr As Data.SqlClient.SqlDataReader = cmd.ExecuteReader() Dim bindata() As Byte = dr.GetValue(1) Response.BinaryWrite(bindata) End SubEnd Class System.Data.SqlClient.SqlException was unhandled by user code Class=15 ErrorCode=-2146232060 LineNumber=1 Message="Incorrect syntax near '='." Number=102 Procedure="" Server="DCPRJ007SQLEXPRESS" Source=".Net SqlClient Data Provider" State=1 StackTrace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader() at webform2.Page_Load(Object sender, EventArgs e) in C:Documents and SettingsAdministratorMy DocumentsVisual Studio 2005WebSitesWebSite7webform2.aspx.vb:line 10 at System.Web.UI.Control.OnLoad(EventArgs e) at System.Web.UI.Control.LoadRecursive() at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
I have 2 identical sql servers one of which is linked to the other via a linked server config which for one particular query gives back the error I give in the subject of this thread: java.sql.SQLException: Could not get the data of the row from the OLE DB provider 'SQLOLEDB'
if I run the exact same sql string in query analyser it runs with no problem. if have similar queries also running from java which use the linked server and these do not give an error. I am at a loss as to explain why this is happening.
the query in question joins together a number of views which reference the linked server.
I have tried everything I can think of including recreating the views and linked server definition. If all is OK in query analyser, how can it fail when called over jbdc (especially when all other db requests succeed) ?
A customer has reported getting the following excpetion in our logs, and I have never seen it before and wanted to see if there was any insight you could provide to understand when you throw this exception.
The process is a "purging" process- it just executes a sequence of DELETE statements that should be fairly simple (delete a number of records from a table and CHECK some constraints, no cascading), after the sequence it commits. All of this occurs on the same connection. We use c3p0 for connection pooling.
Here is the exception:
2008-03-22 08:30:08,699 WARN impl.NewPooledConnection : [c3p0] A PooledConnection that has already signalled a Connection error is still in use! 2008-03-22 08:30:08,699 WARN impl.NewPooledConnection : [c3p0] Another error has occurred [ com.microsoft.sqlserver.jdbc.SQLServerException: The server failed to resume the transaction. Desc:9f00000002. ] which will not be reported to listeners! com.microsoft.sqlserver.jdbc.SQLServerException: The server failed to resume the transaction. Desc:9f00000002. at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source) at com.microsoft.sqlserver.jdbc.IOBuffer.processPackets(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerConnection.doConnectionCommand(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerConnection$ConnectionCommandRequest.execute(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeRequest(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectionCommand(Unknown Source) at com.microsoft.sqlserver.jdbc.SQLServerConnection.rollback(Unknown Source) at com.mchange.v2.c3p0.impl.NewProxyConnection.rollback(NewProxyConnection.java:855) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at org.hibernate.jdbc.BorrowedConnectionProxy.invoke(BorrowedConnectionProxy.java:50) at $Proxy15.rollback(Unknown Source) at
I get the following error when my insert has a DBNull value for a column of type varbinary(MAX). "Implicit conversion from data type nvarchar to varbinary(max) is not allowed. Use the CONVERT function to run this query." In my opinion, the .NET SqlDataAdapter is attempting to convert the null value to a nvarchar. Is it possible to insert a null value in varbinary(max)? I didn't have this problem with the image datatype. Is this a bug or is there a work around to this problem?
Hi All, I have a form (asp website with the default.aspx and default.aspx.cs) which I use to connect to the SQL Server 2005 database and insert, update,get and delete data to and from it. In the table I have a column called Picture to insert a photo (usually bmp files) which I declared as a varbinary(max). ID is the primary key and is an int (i tried it to be numeric & varchar). I am trying to insert data into the table using a form with this syntax: SqlConnection enter_conn = new SqlConnection("user id=;" + "password=;server=servername;" + "Trusted_Connection=yes;" + "database=Member Profiles; " + "connection timeout=30"); enter_conn.Open(); string insertSql = "Insert dbo.Details(ID,Last_Name,First_Name,Middle_Initial,Project, Organization,Manager,Current_Skills,Biography,Hobbies, Picture) Select 1001, 'ABC','XYZ','R',' Website Development','ACT',LKM','ASP.Net, C#.Net, SQL Server 2005, SharePoint, Java, ',' developing the new website for ACT using SharePoint and SQL Server 2005', ' Singing, Listening to Music, Dancing, Cooking, Swimming', BulkColumn from Openrowset( Bulk 'C:\photos\rose.bmp', Single_Blob) as Picture";
SqlCommand myCommand = new SqlCommand(insertSql, enter_conn); int i = myCommand.ExecuteNonQuery();
I have no data in the table and am doing the first insert. Though the data gets inserted I am getting this exception: System.Data.SqlClient.SqlException: Violation of PRIMARY KEY constraint 'PK_Details'. Cannot insert duplicate key in object 'dbo.Details'. The statement has been terminated. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at _Default.Enter_Click(Object sender, EventArgs e) in c:Documents and SettingskrkondaXMy DocumentsVisual Studio 2005WebSitesMember ProfilesDefault.aspx.cs:line 82
Can someone please suggest anything???? I appreciate a quick response please!!!!
I am getting this error message System.Data.SqlClient.SqlException: Invalid object name 'RacingHeritage' I have noticed from other posts where the database owner is the login name I rebuilt the table and the owner is now dbo Here is the Code Function GetCustomers() As System.Data.DataSet Dim connectionString As String = Application("HiddenConnection") Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString) Dim queryString As String = "SELECT [RacingHeritage].[RHID], [RacingHeritage].[UserID], [RacingHeritage].[Clie"& _ "ntFirstName] FROM [RacingHeritage]" Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand dbCommand.CommandText = queryString dbCommand.Connection = dbConnection Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.SqlClient.SqlDataAdapter dataAdapter.SelectCommand = dbCommand Dim dataSet As System.Data.DataSet = New System.Data.DataSet dataAdapter.Fill(dataSet) Return dataSet End Function Any Ideas
Folks, This is driving me nuts. I posted the other day on a related issue, which I belive turned out to be a file permission. I am trying to test my asp.net site under IIS before deploying it. I am now getting the above message which breaks on the opening page on opening a connection to the sql server database. I have permissions set on the database for MachineNameASPNET and NT AUTHORITYNETWORK SERVICE as Ken Tucker suggested. Still getting the message. This certainly looks like a sqlserver permission issue but I am at a loss as to how to grant IIS or the specific web app permission to access the database. Any assistance is truely appreciated. I have spent way to many hours on this. If you don't have the answer but can suggest a resource where I can research it that would help also. Thanks
Hello, we have asp.net apps on a windows 2003 server that connect to an instance of sql server 2005 enterprise edition on another windows 2003 server. We get this error at random times. Anyone know what could be causing it?
System.Web.HttpUnhandledException: Exception of type System.Web.HttpUnhandledException was thrown. ---> System.Data.SqlClient.SqlException: SSL Security error. at System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) at System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) at System.Data.SqlClient.SqlConnection.Open()Thanks
Why am I getting this error, what does it mean and how can I fix it. Here is my stored proceduce and method to call it. ALTER PROCEDURE dbo.hnDeleteHost(@pHostID varchar(50), @pRet int out)AS SELECT Host FROM HostName WHERE HostNameID = @pHostID if (@@ROWCOUNT > 0) begin DELETE FROM HostName WHERE HostNameID = @pHostID SET @pRet = @@ROWCOUNTendelse begin SET @pRet = -1end And the method using (SqlConnection cn = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"])) { cn.Open(); SqlCommand cmd = new SqlCommand("hnDeleteHost", cn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@pHostID", SqlDbType.VarChar, 50, "HostNameID")); cmd.UpdatedRowSource = UpdateRowSource.None; cmd.ExecuteNonQuery(); }
I keep getting this error whenever I try to run my query: System.Data.SqlClient.SqlException: Incorrect syntax near '>'. I'm just trying to fill a dataset with three tables that contain the past few days headlines...what am I doing wrong?? Private Sub fishHeadlines() Dim dateNow As DateTime = DateTime.Now()Dim dateThen As DateTime = DateTime.Today.AddDays(-2) 'create the table array so we can create the sql statement in a moment Dim table() As Stringtable = New String() {"Snapper", "Scissor", "MahiMahi"} Dim strSelect As String 'Create a dataset to hold the tables containing the headlinesDim headlinesDS As New DataSet() 'create the connection string - SnapshotConnectionString is in web.config file Dim strConnect As StringstrConnect = ConfigurationManager.ConnectionStrings("fishConnectionString").ConnectionString 'create a connection object to the databaseDim objConnect As New SqlConnection(strConnect) objConnect.Open() Dim i As Integer 'fill the datatablesFor i = 0 To table.Length strSelect = "SELECT Event FROM " & table(i) & "WHERE (DateOfEntry > '" & dateThen & "')" 'create a data adapter object using connection and sql statementsDim objDA1 As New SqlDataAdapter(strSelect, objConnect) 'fill the dataset objDA1.Fill(headlinesDS, table(i)) Next Dim strTable As StringDim dr As DataRow strTable = "<table>"For i = 0 To table.Length For Each dr In headlinesDS.Tables(table(i)).Rows strTable += "<tr><td>" & dr(0).ToString() & "</td></tr>" Next Next strTable += "</table>" 'display the data lblHeadlines.Text = strTable End Sub
Hi there, i copied some insert code from one page to another and it keeps throwing me this error, i was wondering if anyone can help me out. Here is the error System.Data.SqlClient.SqlException: The name "Credit" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Admin_expenses.Button1_Click(Object sender, EventArgs e) in d:hostingmemberashdfhtdocswwwAdminexpenses.aspx.cs:line 40 This is my code, i cant see anything obvious and as i said i just copied it across, the only things changed were the names and the sqlstringprotected void Button1_Click(object sender, EventArgs e) {decimal intamount = Convert.ToDecimal(txtAmount.Text); string sConnectionStringExpense = "Data Source=xxxx;Initial Catalog=xxxx;User ID=xxxx;Password=xxxx";SqlConnection objConnExpense = new SqlConnection(sConnectionStringExpense); using (objConnExpense) { objConnExpense.Open();string sqlExpense = "INSERT INTO tbl_expenses (expense_amount, expense_payment_type, expense_type, expense_description) " + "VALUES (" + intamount + "," + ddlPaymentType.SelectedItem + "," + ddlExpenseType.SelectedValue + "," + txtDescription.Text + ")";SqlCommand objCmdExpense = new SqlCommand(sqlExpense, objConnExpense); try { objCmdExpense.ExecuteNonQuery(); }catch (Exception ex) {lblcomplete.Text = "!!Adjust stock for product at location " + Convert.ToString(ex); } finally { objConnExpense.Close(); } }
We are developing Asp.Net Application in following environment.
Windows 2003 Dot Net 2003 SQL Server 2000
Problem :
We have team of four people. We made one machine as database server and rest of the people trying to connect to that database server. we are getting error "SQL server not found.".
We are using following string as a connection string.