Can I Keep A Object In Memory Betweek Calls To SqlProcedure?
Feb 25, 2008
I am wondering if it is at all possible to keep an object in memory between calls to a SqlFunction or SqlProcedure.
Every time a row is inserted in to a table, i need to do some processing on the data. The processing becomes many times faster if I can do some preprocessing (the preprocessing is independent of the data inserted into the table). So basically, I want to save the result of the preprocessing, only do it one time, and save the result in a object in memory. Is this at all possible?
Regards, Egil.
View 5 Replies
ADVERTISEMENT
Jan 17, 2008
I am getting a Must declare the table variable "@TableName" exception on this method and Im not sure why.
This is my commandText before I insert my parameters.
Code Block
Dim historyRecordCommand As String = "SELECT * FROM @TableName WHERE dbo.GetPrimaryKeyColumnName(@TableName)" & _
" = CONVERT(VARCHAR, @Key) AND HistoryRecordCreateDate BETWEEN" & _
" CONVERT(VARCHAR, @StartTime, 21) AND CONVERT(VARCHAR, @EndTime, 21)"
Code Block
Dim historyRecordSqlCommand As SqlCommand = connection.CreateCommand
historyRecordSqlCommand.CommandText = historyRecordCommand
historyRecordSqlCommand.Parameters.AddWithValue("@TableName", rowAccessionSystemLogDetail.TableName)
historyRecordSqlCommand.Parameters.AddWithValue("@Key", rowAccessionSystemLogDetail.TablePrimaryKeyValue)
historyRecordSqlCommand.Parameters.AddWithValue("@StartTime", rowAccessionSystemLogs.StartTime)
historyRecordSqlCommand.Parameters.AddWithValue("@EndTime", rowAccessionSystemLogs.EndTime)
Dim historyRowSqlReader As SqlDataReader = historyRecordSqlCommand.ExecuteReader(CommandBehavior.SingleRow)
I only expect to get 1 row back and I have checked all my values and they are fine also.
Anyone have any ideas?
Thanks
View 5 Replies
View Related
Apr 25, 2005
Hi.
I’m having a conceptual problem with tracking sales vs. call center calls.
Each record in the fact table represents a call to the call center.
In this record are various facts and foreign keys that map to marketing campaigns, etc.
The product sold ID is NULL on no sale, and filled in for a sale.
When creating MDX to retrieve data by campaign, for example, to track the number of calls for a campaign vs. it’s sales, that works fine (because there’s a column labeled sale that’s either 0 or 1 for the sale and I just sum it). This way I get the conversion percentage of calls to sales.
But when creating MDX to track the product sold, as soon as I do a crossjoin on campaign and products sold, for example, I lose the total calls- the number of calls is the same as the sales (similar results as a SQL join). Am I doing something wrong or is it conceptually impossible to track this type of metric down to the product when there are NULLs in the fact table? I’ve tried converting the nulls to a NONE category, but that doesn’t stop the crossjoin from not giving me the desired results, plus I then have to filter out NONE as a product.
Is that why a lot of sample warehouses have a separate sales cube?
Thank you,
Richard
View 2 Replies
View Related
May 16, 2006
Hi group,
I've got a performance issue.
Here's in global what the sp (Let's call it SP_A) does.
Step 1 Call a different SP (Lets call it SP_B) and store the output in a variable
Step 2 SP_B runs a select statement that returns 1 value
Step 3 SP_A uses this value as a parameter in a select statement.
Step 4 The result of the SP_A is the result of the select statement (744 rows (always))
All tables used in SP_A and SP_B are temp tables.
Total performance of SP_A is between 0.090 and 0.140 seconds.
The problem is that this SP is called 180 times from outside SQL server. That means that the total processing time is somewhere between 21 and 25 seconds.
When I move the entire processing to within SQL server I gain only 2 seconds. So I lose 2 seconds in connecting to the database 180 times.
Can someone give me some pointers on where to look for performance wins?
If you like I can add the SP's
Regards,
Sander
View 7 Replies
View Related
Jan 15, 2006
I have an old app that I'm trying to recode and improve performance.
From the start it makes three seperate calls to a db, checks to see if the record exists, if it doesn't it adds it, then takes the data from all three and inserts it into a final call.
Here is a quick example of the script
Select * from table1 where id = " & tempVariable
If Not RS.EOF Then
strTable1 = RS("SomeRec")
Else
RS.ADDNEW
RS("SomeRec") = tempRec1
RS.UPDATE
RS.Requery
strTable1 = RS("SomeRec")
End If
RS.CLOSE
Select * from table2 where id =2
If Not RS.EOF Then
strTable2 = RS("SomeRec")
Else
RS.ADDNEW
RS("SomeRec") = tempRec2
RS.UPDATE
RS.Requery
strTable2 = RS("SomeRec")
End If
RS.CLOSE
Select * from table3 where id =3
If Not RS.EOF Then
strTable3 = RS("SomeRec")
Else
RS.ADDNEW
RS("SomeRec") = tempRec3
RS.UPDATE
RS.Requery
strTable3 = RS("SomeRec")
End If
RS.CLOSE
INSERT INTO Table4 (Table1, Table2, Table3) VALUES ('" & strTable1 & "', '" & strTable2 & "', '" & strTable3 & "'
These is probably an easy solution however I don't know where to start. Any help or ideas will be greatly appreciated.
Thanks
-Scott
View 1 Replies
View Related
Feb 28, 2008
I am attempting to see if an application is calling a particular stored proc. i have the Profiler setup to only display the login and it is. I have all items set to display under Stored Procedures, but I can't see names of procedures in the Text Data.
View 4 Replies
View Related
Feb 4, 2008
hi all.
when i worked with oracle before, it was very common to call a function from inside a s.procedure.
now, someone talled me that in sqlserver this is not a best performance approach, and i should try to avoid this.
is this true?
View 4 Replies
View Related
May 2, 2007
My Problem is that I don€™t know really what does a call means.
We got a SQL Standard Edition.
Can I install as many instances I want in a Server?
How many users can access the Server at the same time?
If I use SQL authentication, and two users use this logging information, how many calls do they made? 2 one per each user (PC) or just 1 one per logging.
If I have a client / server application, and the server access the SQL Server using SQL authentication. Can I have unlimited number of clients?
View 3 Replies
View Related
Dec 14, 2006
Hi,
I'd like to keep state between calls to a UDF (mainly for caching purposes). I can shove an object into the appdomain using SetData and read it using GetData, but that requires the assembly to be set to UNSAFE. I'm confident I can secure the DB and the assembly fairly well, but I like defense in depth, and if there's another way to save state between calls to a UDF, I would prefer those.
Is there another way to store state between calls to a UDF, without putting data into DB tables or using things that will require the assembly to have such a wide permission set?
Thanks,
Alex
View 4 Replies
View Related
Aug 18, 2007
Suppose my database has 3 table A though C and I need to get the number of records in each. I could do it as individual selects like
SELECT 'A' AS 'Table', COUNT(*) AS 'Count' FROM A
SELECT 'B' AS 'Table', COUNT(*) AS 'Count' FROM B
SELECT 'C' AS 'Table', COUNT(*) AS 'Count' FROM C
or
SELECT 'A' AS 'Table', COUNT(*) AS 'Count' FROM A
UNION
SELECT 'B' AS 'Table', COUNT(*) AS 'Count' FROM B
UNION
SELECT 'C' AS 'Table', COUNT(*) AS 'Count' FROM C
Is there any problem in unioning statements like these? In reality there were over a hundred tables and the TSQL was generated by looping through SYSOBJECTS. I eventually used the first construct as the XML was simpler to generate.
View 6 Replies
View Related
Aug 24, 2007
I need to develope a stored procedure (eventually called by a trigger) that creates a record in an event table for all the descendants of a drawing. There are 3 tables involved as example tables shown below:
DwgTable
DwgID (integer)--drawing record identifier
PrntDwgID (integer)--parent drawing record identifier (a previously defined DwgID from this table)
DwgEventTable
DwgEventID (integer)--record identifier
DwgID (integer)--value from DwgTable
EventID (integer)--value from EventTable
EventTable
EventID
There are other fields in two of the tables and only the fields shown in the DwgEventTable, but only the fields shown are required for adding a record in the DwgEventTable for a new event in the EventTable. The problem is identifying all the DwgID's of the descendant of the DwgID where the EventID occurred. There may be 0 to N descendants in 0 to N generations. I need to add a record for the original DwgID and all the descendant DwgID's in the DwgEventTable for the event identified by EventID.
I could do this from the client side, but a better place would be from the server side. I need some clue(s) on how to start coding a recursive stored procedure in SQL Server 2005. From what I have read, you cannot create a managed code procedure that appends or updates records--if managed code can add/modify records then I can do the above with managed code procedure.
Any Suggestions?
View 4 Replies
View Related
Apr 16, 2008
I am running the code below in a sql job. Exec (@SQL) errors every time but after 4 failures the job fails instead of trying @MaxRetries times. Any idea on why this is happening?
Set @RetryCounter = 0
WHILE (@RetryCounter < @MaxRetries)
Begin
SELECT @error_value = 0
Exec (@SQL)
SELECT @error_value = @@ERROR
If @error_value = 0
Set @RetryCounter = @MaxRetries
Else
Begin
WAITFOR DELAY '00:05:00'
Set @RetryCounter = @RetryCounter + 1
End
End
View 17 Replies
View Related
Feb 26, 2007
I have several SSIS Packages which are called by few different applications. I need to make packages Mutually Exclusive, so that If one is running; calls to any other Package should be put on wait till the running one has finished its execution.
What are the best ways to do this ?
I would sincerely appreciate inputs in this.
View 6 Replies
View Related
Dec 24, 2003
I've got a huge inefficiency in my code that I'm trying to fix. I’m coding in VB.NET using ASP.NET and an MSSQL 2000 server.
I’m working in a temporary table that has an identical layout as another, non-temporary table in my database. Once I get the temp table how I want it, I need to insert everything from that table into my main table. Before I can do that, however, I need to delete all the records in the main table with certain fields that match a record’s fields in the temporary table.
Right now, I have a method that builds one delete statement per record in the temporary table and then runs those statements on the main table. Since I’m dealing with the order of 50,000 records (at least) here, building and sending those statements to the server takes forever.
Is there a way I can accomplish the same thing without building and sending such a huge SQL call to the server? If so, how would I go about doing that?
Thanks in advance for whatever help you can give,
-Starwiz
View 6 Replies
View Related
Apr 25, 2006
I would like to make inner join with results from different exec %procedureName% calls.
thanks.
View 3 Replies
View Related
Feb 17, 2008
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.
View 3 Replies
View Related
Mar 7, 2006
Hi all
I am using internal activation on a queue to process the messages, should an error be encountered I call stored procedure A in the same database to log the error. Part of the processing in stored procedure A is a call to stored procedure B in another database (on the same server), however I have not been able to get this call to B to work. Currently I get the error "The server principal XXXXXX is not able to access the database YYYYYYY under the current security context".
I have tried various combinations (too many to remember) of database owners, roles and permissions as well as EXECUTE AS on both A and B and the Queue but none seem to work. Can anyone give me simple example of a setup which would allow this cross database call to work?
Thanks
Ian
View 6 Replies
View Related
Sep 27, 2007
I'm having a terrible time trying to figure out how to do this, does anyone have experience?
My code works fine in the report viewer, but all I get from the deployment is #Error symbols. I've added my custom dll file to the config file and gave it full trust, as well as the URL of the web service I'm calling. I've even modified the rest of the codegroups within the file to FullTrust just in case.
I've also added the [assembly: AllowPartiallyTrustedCallers] to the AssemblyInfo class, and [WebPermission(SecurityAction.Assert)] to the constructor of my custom assembly no avail.
I'm at a complete loss now as to why my deployment cannot run this code. As far as debugging goes, Visual Studio is not located on the reporting server, nor is it an option to have it put there. I've been able to run the code with the web service lines commented out, but as soon as I put even the webservice.Credentials = CredentialCache.DefaultCredentials in I'm greeted with the nice and calming #Error.
Any suggestions?
View 1 Replies
View Related
Feb 5, 2008
Hi,
I want to retrieve some parameter values from Reporting Services using GetReportParameters from
Microsoft.SqlServer.ReportingServices.ReportService2005.
It works fine unless the parameter types are date/time. In such a case it seems as if the format of the parameter depends on the ReportServices installation:
Example:
On a German installation the date/time format is "dd.mm.yyyy" but on an English installation the format is different (mm/dd/yyyy) (and of course the time part is also different).
Is there a way to determine the format to be expected? (... even in the case I've no knowledge about the server ReportingServices is running.)
View 5 Replies
View Related
May 21, 2007
I am trying to query a list in sharepoint using its lists.asmx webservice and in turn its GetListItems call. I currently have the call working using the following code in the Querystring for the report
<Query>
<Method Namespace="http://schemas.microsoft.com/sharepoint/soap/" Name="GetListItems"/>
<SoapAction>http://schemas.microsoft.com/sharepoint/soap/GetListItems</SoapAction>
</Query>
This seems to work fine except that the list has some empty fields in the first record and for some unbelivable reason they (the empty fields) do not get returned as part of the dataset unless i put a value into them. I then came across a parameter for GetListItems called viewFields which is supposed to . Tehn sample Soap request is shown as follows
<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlnsoap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<GetListItems xmlns="http://schemas.microsoft.com/sharepoint/soap/">
<listName>string</listName>
<viewName>string</viewName>
<query>
<xsdchema>schema</xsdchema>xml</query>
<viewFields>
<xsdchema>schema</xsdchema>xml</viewFields>
<rowLimit>string</rowLimit>
<queryOptions>
<xsdchema>schema</xsdchema>xml</queryOptions>
</GetListItems>
</soap:Body>
</soap:Envelope>
As i have a parameter for viewfields setup.... What value do i pass to it ??? My XML is in its infancy but i though something along the lines of the following would do as an input.
<FieldRef Name='Title'/><FieldRef Name='Address1'/>
Also, maybe i am going the wrong way about this.Is there an easier way of defining the Parameters as part of the Querystring or do Parameters always have to be setup in the parameters tab (My Parameter values are static and will never change).
View 1 Replies
View Related
Aug 21, 2012
I I work for a small business who has a somewhat successful ecommerce site. Our website is based on some VERY large DB's with 10's of millions of records, and 40+GB of images, so we choose to self host so we could easily manage the data. As things have grown and we've cobbled a fair amount of integration between the website databases and our internal systems. Fast forward 4-5 years, the website has grown to the point that self hosting is costing us a fortune in bandwidth costs, so I'm thinking of moving to a co-location facility. If I do that, I need to send the website tables along with it. Is there a replication stradagy that would allow us to have two independant DB's, both able to take updates that would sync with one another? My other thought is to spit the tables on two different DB's and let some of the data live in the co-location facility.. but I am worried about the performance impact for local users.
View 3 Replies
View Related
Jun 25, 2014
I have a query using the sys.dm_exec_query_stats that returns the last exec of all proc calls for the last day and execution times.
Is there a way to get all the executions and not just the last w/o using a db trace?
View 1 Replies
View Related
Mar 8, 2008
All -
I have a SQL Server 2005 HTTP endpoint. I create a transaction scope in a client application and call two methods on that endpoint. Will the operations performed in the two methods, really, be in a transaction?
In other words, will SQL Server enlist in the ambient transaction even when I am accessing it through a HTTP endpoint?
Thanks.
View 4 Replies
View Related
Oct 9, 2006
Hi.
How can I get the messages sent by the server while I'm executing a stored procedure via the JDBC driver?
I need to get my own debug messages (done through the print() function)
and also standard server messages (such as "x row(s) affected" or
results from SET STATISTICS TIME ON). Is this possible?
Many thanks.
Carlos
View 4 Replies
View Related
May 23, 2007
I developed one DLL in vb .Net which call two web services (sql endpoints) I wish to use this solution but for hundreds of servers (endpoints) I want to ask if I could do that with dynamic parameters passed to de DLL CALL in sql server clr.
Actually when I compile de DLL I need to have referenced (hardcoded) the webservices (endpoints) and when I need a brand new endpoint I need to reference it, compile the dll and release.
I cannot find how to invoke the service within the DLL with the namespace or webservice name as parameter.
Mi code to invoke now my referenced end point is: (note that my endpoint name is rperez)
Dim CallWebService As New rperez.IntelisisServiceEndPoint
CallWebService.Credentials = System.Net.CredentialCache.DefaultCredentials
Call CallWebService.IntelisisService(ArchivoOriginal)
My class rperez.IntelisisServiceEndPoint is referenced (hardcoded) in the dll project I need it dynamic
Do I need to change my code to PROXY object ?
View 1 Replies
View Related
Nov 24, 2015
I created a Switch statement below that surprisingly doesn't throw any errors and some of it actually works. The problem is that the function calls in the 3rd and 4th sections of it below (in bold) are not working.
=switch(HASONEVALUE('s1JudgeIds'[JudgeName]),
values('s1JudgeIds'[JudgeName])<>"1 - All Judges" && values('s1Perm1'[Exit])<>"Still in Out-of-Home Care",CALCULATE(count(s1Perm1[entity_id]),FILTER(ALL(Time[ExitMonthCategory]),Time[ExitMonthCategory] <= MAX(Time[ExitMonthCategory]))),
values('s1JudgeIds'[JudgeName])="1 - All Judges" && values('s1Perm1'[Exit])<>"Still in Out-of-Home Care",calculate([Numerator],all('s1JudgeIds'[JudgeName])),
values('s1JudgeIds'[JudgeName])="1 - All Judges" && values('s1Perm1'[Exit])="Still in Out-of-Home Care",calculate([Numerator-stillincare],all('s1JudgeIds'[JudgeName])),
values('s1JudgeIds'[JudgeName])<>"1 - All Judges" && values('s1Perm1'[Exit])="Still in Out-of-Home Care",calculate([Numerator-stillincare])
View 24 Replies
View Related
Oct 25, 2007
Hi,
I am experiencing some problems calling reports in ReportServer using URL.
I have several multivalue parameters that are not being set through the URL. The values for this parameters should be the default ones that I have set in VS.
When I preview the report in VS, the default values are correctly set but, when using the URL call, I keep being prompt for this parameters and the report is not rendered until I set them manually.
Does anyone ever had this problem?
Thank you in advance
Catarina Ribeiro
View 1 Replies
View Related
Jul 13, 2015
I am looking to test this feature - and the "Transaction Performance Collector" has recommended me a table to port to In-Memory OLTP.Â
I have now tried the "Table Memory Optimization Advisor" tool.
After a couple of tweaks to the table design - the tool is now passing validation but the tool is not allowing to progress to the next step:
Could it be down to not having enough memory? But would this not show in the advisor?
View 4 Replies
View Related
Sep 28, 2007
Hello. I have received the follwoing error upon an attempt to Browse the Cube. All other tabs are functional, including the Calculations tab. We are running Windows Server 2003 SP2 and SQL Server 2005 SP2. Any suggestions would be greatly appreciated!
**EDIT** - Have confirmed SP1 for VS2005 is installed both locally and on server, also.
Attempted to read or write protected memory. This is often an indication that other memory is corrupt. (Microsoft Visual Studio)
------------------------------
Program Location:
at Microsoft.Office.Interop.Owc11.PivotView.get_FieldSets()
at Microsoft.AnalysisServices.Controls.PivotTableFontAdjustor.TransformFonts(Font font)
at Microsoft.AnalysisServices.Browse.CubeBrowser.UpdatePivotTable(Boolean translate)
at Microsoft.AnalysisServices.Browse.CubeBrowser.UpdateAll(Boolean translate)
at Microsoft.AnalysisServices.Browse.CubeBrowser.InitialUpdate()
at Microsoft.AnalysisServices.Browse.CubeBrowser.SupportFunctionWhichCanFail(FunctionWhichCanFail function)
View 4 Replies
View Related
Oct 11, 2007
I've been researching AWE to determine if we should enable this for our environment.
Currently we have a quad core box with 4 gb of RAM (VMware). OS: Windows 2003 std, SQL Server 2005 std. 3GB is not set but will be as soon as we can perform maintenance on the server.
I have read mixed feedback on AWE, either it works great or grinds you to a hault. I would assume that the grinding to a hault is due to not setting the min/max values correctly or not enabling the lock page in memory setting.
We only have one instance of SQL on the server and this box won't be used for anything else aside from hosting SQL services. We do plan on running SSRS off of this server as well.
1. Will running SSRS and enabling AWE cause me problems? Will I have to reduce the max setting by the SSRS memory usage or will it share and play nice?
2. How do I go about setting the Max value? Should it be less than the physical RAM in the box? Right now its set to the default of 214748364, even if I don't enable AWE should this default value be changed?
3. It seems that even at idle the SQL server holds a lot of memory and the page file grows. If I restart the process in the morning, memory usage in taskmon is at 600mb or so. By the end of the day, its up around 2gb. How can I track down whats causing this, should this even concern me?
4. The lock Page in memory setting worries me. Everything I've read on this seems to give a warning about serious OS and other program support degradation. In some cases to the point where they have to restore the settings on the server before they can bring it back up. What are your thoughts on this.
View 3 Replies
View Related
Jun 20, 2007
I'm new to programming with the ReportViewer object and this issue has me stumped: it appears if you have some optional parameters in your report, and a way to refresh that report with different parameter values, the report "remembers" parameter values from previous calls to SetParameters() on subsequent renderings of the report. If a parameter is included in a call to ServerReport.SetParameters() on the first rendering, but not included in a subsequent call and the report is re-rendered, the previous value of the parameter (rather than the default value) appears to be used.
Here's a snippet of some test code I wrote within an ASP.NET 2.0 test application:
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
this.rptViewer.ServerReport.ReportServerUrl = new Uri(this.txtReportServerUrl.Text);
this.rptViewer.ServerReport.ReportPath = this.txtReportPath.Text;
}
}
protected void btnViewRpt_Click(object sender, EventArgs e)
{
ReportParameter[] rptParams = GetReportParameters();
this.rptViewer.ServerReport.SetParameters(rptParams);
this.rptViewer.ServerReport.Refresh();
}
private ReportParameter[] GetReportParameters()
{
int paramCount = 0;
ReportParameter[] retVal;
string emptyVal = null;
if (txtName.Text != "") paramCount++;
if (txtAddress.Text != "") paramCount++;
if (txtZip.Text != "") paramCount++;
retVal = new ReportParameter[paramCount];
paramCount = 0;
if (txtName.Text != "")
retVal[paramCount++] = new ReportParameter("Name", txtName.Text);
if (txtAddress.Text != "")
retVal[paramCount++] = new ReportParameter("Address", txtAddress.Text);
if (txtZip.Text != "")
retVal[paramCount++] = new ReportParameter("Zip", txtZip.Text);
return retVal;
}
The test report was written to simply echo back the values of the parameters that are specified. The report definition allows NULL to be specified for the parameters.
The test app was written so if I enter a blank value for Name, Address or Zip, the corresponding parameter does not get created in C# and does not get sent to the report server. If I view the report with all three values (parameters) filled in, I see the parameters echoed back to me in my simple report as expected. If I clear the parameter values the first time the report is rendered, none are sent to the report server and I get no values echoed back in my report, also as expected. I can change the values and click on the View Report button and see the new values for the parameters as expected. However, if I clear any previously-specified parameters and click on View Report, the previously-specified values for the ones that are now cleared are still displayed by the report.
So my question is: once a parameter has been sent to the report, how does one "unsend" it on subsequent refreshes? I know I can create the parameter and set its value to null...but I have a situation here where that can cause errors. It'd be better if I could simply leave out the unspecified parameters and have the report refresh and render as if I were rendering it for the first time.
Any suggestions?
View 7 Replies
View Related
Oct 11, 2006
Hi There,This is related to a ms access database but since I use the SqlDataSource control I thought I should post here.I have a project that I was working on with this ms access db and using sql controls, everything was working just finesince one day I started getting "Object reference not set to an instance of an object" messages when I try to designa query or retrieve a schema, nothing works at design time anymore but at runtime everything is perfect, its a lotof work for me now to create columns,schemas and everything manually, I've tried reinstalling visualstudio, ado componentsbut nothing seems to fix it, did this ever happen to any of you guys?any tip is really appreciated thanks a lot
View 2 Replies
View Related
Dec 17, 2007
Does any one has any clue for this error ? I did went through a lot of articles on this error but none helped . I am working in Visual studie 2005 and trying to upload image in sql database through a simple form. Here is the code
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Web.Configuration;
using System.IO;
public partial class Binary_frmUpload : System.Web.UI.Page
{protected void Page_Load(object sender, EventArgs e)
{
}protected void btnUpload_Click(object sender, EventArgs e)
{if (FileUpload.HasFile == false)
{
// No file uploaded!lblUploadDetails.Text = "Please first select a file to upload...";
}
else
{string str1 = FileUpload.PostedFile.FileName;
string str2 = FileUpload.PostedFile.ContentType; string connectionString = WebConfigurationManager.ConnectionStrings["GSGA"].ConnectionString;
//Initialize SQL Server Connection SqlConnection con = new SqlConnection(connectionString);
//Set insert query string qry = "insert into Officers (Picture,PictureType ,PicttureTitle) values(@ImageData, @PictureType, @PictureTitle)";
//Initialize SqlCommand object for insert. SqlCommand cmd = new SqlCommand(qry, con);
//We are passing Original Image Path and Image byte data as sql parameters. cmd.Parameters.Add(new SqlParameter("@PictureTitle", str1));
cmd.Parameters.Add(new SqlParameter("@PictureType", str2));Stream imgStream = FileUpload.PostedFile.InputStream;
int imgLen = FileUpload.PostedFile.ContentLength;byte[] ImageBytes = new byte[imgLen]; cmd.Parameters.Add(new SqlParameter("@ImageData", ImageBytes));
//Open connection and execute insert query.
con.Open();
cmd.ExecuteNonQuery();
con.Close(); //Close form and return to list or images.
}
}
}
Object reference not set to an instance of an object.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.Source Error:
Line 32:
Line 33: string str2 = FileUpload.PostedFile.ContentType;
Line 34: string connectionString = WebConfigurationManager.ConnectionStrings["GSGA"].ConnectionString;
Line 35:
Line 36: //Initialize SQL Server Connection Source File: c:UsersManojDocumentsVisual Studio 2005WebSitesGSGABinaryfrmUpload.aspx.cs Line: 34
Stack Trace:
[NullReferenceException: Object reference not set to an instance of an object.]
Binary_frmUpload.btnUpload_Click(Object sender, EventArgs e) in c:UsersManojDocumentsVisual Studio 2005WebSitesGSGABinaryfrmUpload.aspx.cs:34
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) +5102
View 2 Replies
View Related
