Search Results

Search found 497 results on 20 pages for 'sqlconnection'.

Page 14/20 | < Previous Page | 10 11 12 13 14 15 16 17 18 19 20  | Next Page >

  • Insert Error:CREATE DATABASE permission denied in database 'master'. cannot attach the file

    - by user1300580
    i have a register page on my website I am creating and it saves the data entered by the user into a database however when I click the register button i am coming across the following error: Insert Error:CREATE DATABASE permission denied in database 'master'. Cannot attach the file 'C:\Users\MyName\Documents\MyName\Docs\Project\SJ\App_Data\SJ-Database.mdf' as database 'SJ-Database'. These are my connection strings: <connectionStrings> <add name="ApplicationServices" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient"/> <add name="MyConsString" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|SJ-Database.mdf; Initial Catalog=SJ-Database; Integrated Security=SSPI;" providerName="System.Data.SqlClient" /> </connectionStrings> Register page code: using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Data; using System.Data.SqlClient; public partial class About : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { } public string GetConnectionString() { //sets the connection string from your web config file "ConnString" is the name of your Connection String return System.Configuration.ConfigurationManager.ConnectionStrings["MyConsString"].ConnectionString; } private void ExecuteInsert(string name, string gender, string age, string address, string email) { SqlConnection conn = new SqlConnection(GetConnectionString()); string sql = "INSERT INTO Register (Name, Gender, Age, Address, Email) VALUES " + " (@Name,@Gender,@Age,@Address,@Email)"; try { conn.Open(); SqlCommand cmd = new SqlCommand(sql, conn); SqlParameter[] param = new SqlParameter[6]; //param[0] = new SqlParameter("@id", SqlDbType.Int, 20); param[0] = new SqlParameter("@Name", SqlDbType.VarChar, 50); param[1] = new SqlParameter("@Gender", SqlDbType.Char, 10); param[2] = new SqlParameter("@Age", SqlDbType.Int, 100); param[3] = new SqlParameter("@Address", SqlDbType.VarChar, 50); param[4] = new SqlParameter("@Email", SqlDbType.VarChar, 50); param[0].Value = name; param[1].Value = gender; param[2].Value = age; param[3].Value = address; param[4].Value = email; for (int i = 0; i < param.Length; i++) { cmd.Parameters.Add(param[i]); } cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); } catch (System.Data.SqlClient.SqlException ex) { string msg = "Insert Error:"; msg += ex.Message; throw new Exception(msg); } finally { conn.Close(); } } protected void cmdRegister_Click(object sender, EventArgs e) { if (txtRegEmail.Text == txtRegEmailCon.Text) { //call the method to execute insert to the database ExecuteInsert(txtRegName.Text, txtRegAge.Text, ddlRegGender.SelectedItem.Text, txtRegAddress.Text, txtRegEmail.Text); Response.Write("Record was successfully added!"); ClearControls(Page); } else { Response.Write("Email did not match"); txtRegEmail.Focus(); } } public static void ClearControls(Control Parent) { if (Parent is TextBox) { (Parent as TextBox).Text = string.Empty; } else { foreach (Control c in Parent.Controls) ClearControls(c); } } }

    Read the article

  • [Flex 4 and .Net] Retrieving tables from SQL database

    - by mG
    Hi everyone, As the title says, I want to retrieve tables of data from a SQL database, using Flex 4 and .Net WebService. I'm new to both Flex and DotNet. Please tell me a proper way to do it. This is what I've done so far: Retrieving an array of string: (this works) .Net: [WebMethod] public String[] getTestArray() { String[] arStr = { "AAA", "BBB", "CCC", "DDD" }; return arStr; } Flex 4: <?xml version="1.0" encoding="utf-8"?> <s:Application xmlns:fx="http://ns.adobe.com/mxml/2009" xmlns:s="library://ns.adobe.com/flex/spark" xmlns:mx="library://ns.adobe.com/flex/mx" minWidth="955" minHeight="600"> <fx:Script> <![CDATA[ import mx.collections.ArrayCollection; import mx.controls.Alert; import mx.rpc.events.ResultEvent; [Bindable] private var ac:ArrayCollection = new ArrayCollection(); protected function btn_clickHandler(event:MouseEvent):void { ws.getTestArray(); } protected function ws_resultHandler(event:ResultEvent):void { ac = event.result as ArrayCollection; Alert.show(ac.toString()); } ]]> </fx:Script> <fx:Declarations> <s:WebService id="ws" wsdl="http://localhost:50582/Service1.asmx?WSDL" result="ws_resultHandler(event)"/> </fx:Declarations> <s:Button x="10" y="30" label="Button" id="btn" click="btn_clickHandler(event)"/> </s:Application> Retrieving a DataTable: (this does not work) DotNet: [WebMethod] public DataTable getUsers() { DataTable dt = new DataTable("Users"); SqlConnection conn = new SqlConnection("server = 192.168.1.50; database = MyDatabase; user id = sa; password = 1234; integrated security = false"); SqlDataAdapter da = new SqlDataAdapter("select vFName, vLName, vEmail from Users", conn); da.Fill(dt); return dt; } Flex 4: <?xml version="1.0" encoding="utf-8"?> <s:Application xmlns:fx="http://ns.adobe.com/mxml/2009" xmlns:s="library://ns.adobe.com/flex/spark" xmlns:mx="library://ns.adobe.com/flex/mx" minWidth="955" minHeight="600"> <fx:Script> <![CDATA[ import mx.collections.ArrayCollection; import mx.controls.Alert; import mx.rpc.events.ResultEvent; [Bindable] private var ac:ArrayCollection = new ArrayCollection(); protected function btn_clickHandler(event:MouseEvent):void { ws.getUsers(); } protected function ws_resultHandler(event:ResultEvent):void { ac = event.result as ArrayCollection; Alert.show(ac.toString()); } ]]> </fx:Script> <fx:Declarations> <s:WebService id="ws" wsdl="http://localhost:50582/Service1.asmx?WSDL" result="ws_resultHandler(event)"/> </fx:Declarations> <s:Button x="10" y="30" label="Button" id="btn" click="btn_clickHandler(event)"/> </s:Application>

    Read the article

  • Right code to retrieve data from sql server database

    - by HasanGursoy
    Hi, I have some problems in database connection and wonder if I have something wrong in my code. Please review. This question is related: Switch between databases, use two databases simultaneously question. cs="Data Source=mywebsite.com;Initial Catalog=database;User Id=root;Password=toor;Connect Timeout=10;Pooling='true';" using (SqlConnection cnn = new SqlConnection(WebConfigurationManager.ConnectionStrings["cs"].ConnectionString)) { using (SqlCommand cmmnd = new SqlCommand("", cnn)) { try { cnn.Open(); #region Header & Description cmmnd.Parameters.Add("@CatID", SqlDbType.Int).Value = catId; cmmnd.CommandText = "SELECT UpperID, Title, Description FROM Categories WHERE CatID=@CatID;"; string mainCat = String.Empty, rootCat = String.Empty; using (SqlDataReader rdr = cmmnd.ExecuteReader()) { if (rdr.Read()) { mainCat = rdr["Title"].ToString(); upperId = Convert.ToInt32(rdr["UpperID"]); description = rdr["Title"]; } else { Response.Redirect("/", false); } } if (upperId > 0) //If upper category exists add its name { cmmnd.Parameters["@CatID"].Value = upperId; cmmnd.CommandText = "SELECT Title FROM Categories WHERE CatID=@CatID;"; using (SqlDataReader rdr = cmmnd.ExecuteReader()) { if (rdr.Read()) { rootCat = "<a href='x.aspx'>" + rdr["Title"] + "</a> &raquo; "; } } } #endregion #region Sub-Categories if (upperId == 0) //show only at root categories { cmmnd.Parameters["@CatID"].Value = catId; cmmnd.CommandText = "SELECT Count(CatID) FROM Categories WHERE UpperID=@CatID;"; if (Convert.ToInt32(cmmnd.ExecuteScalar()) > 0) { cmmnd.CommandText = "SELECT CatID, Title FROM Categories WHERE UpperID=@CatID ORDER BY Title;"; using (SqlDataReader rdr = cmmnd.ExecuteReader()) { while (rdr.Read()) { subcat.InnerHtml += "<a href='x.aspx'>" + rdr["Title"].ToString().ToLower() + "</a>\n"; description += rdr["Title"] + ", "; } } } } #endregion } catch (Exception ex) { HasanG.LogException(ex, Request.RawUrl, HttpContext.Current); Response.Redirect("/", false); } finally { cnn.Close(); } } } The random errors I'm receiving are: A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.) A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached. Cannot open database "db" requested by the login. The login failed. Login failed for user 'root'.

    Read the article

  • How can I get SQL Server transactions to use record-level locks?

    - by Joe White
    We have an application that was originally written as a desktop app, lo these many years ago. It starts a transaction whenever you open an edit screen, and commits if you click OK, or rolls back if you click Cancel. This worked okay for a desktop app, but now we're trying to move to ADO.NET and SQL Server, and the long-running transactions are problematic. I found that we'll have a problem when multiple users are all trying to edit (different subsets of) the same table at the same time. In our old database, each user's transaction would acquire record-level locks to every record they modified during their transaction; since different users were editing different records, everyone gets their own locks and everything works. But in SQL Server, as soon as one user edits a record inside a transaction, SQL Server appears to get a lock on the entire table. When a second user tries to edit a different record in the same table, the second user's app simply locks up, because the SqlConnection blocks until the first user either commits or rolls back. I'm aware that long-running transactions are bad, and I know that the best solution would be to change these screens so that they no longer keep transactions open for a long time. But since that would mean some invasive and risky changes, I also want to research whether there's a way to get this code up and running as-is, just so I know what my options are. How can I get two different users' transactions in SQL Server to lock individual records instead of the entire table? Here's a quick-and-dirty console app that illustrates the issue. I've created a database called "test1", with one table called "Values" that just has ID (int) and Value (nvarchar) columns. If you run the app, it asks for an ID to modify, starts a transaction, modifies that record, and then leaves the transaction open until you press ENTER. I want to be able to start the program and tell it to update ID 1; let it get its transaction and modify the record; start a second copy of the program and tell it to update ID 2; have it able to update (and commit) while the first app's transaction is still open. Currently it freezes at step 4, until I go back to the first copy of the app and close it or press ENTER so it commits. The call to command.ExecuteNonQuery blocks until the first connection is closed. public static void Main() { Console.Write("ID to update: "); var id = int.Parse(Console.ReadLine()); Console.WriteLine("Starting transaction"); using (var scope = new TransactionScope()) using (var connection = new SqlConnection(@"Data Source=localhost\sqlexpress;Initial Catalog=test1;Integrated Security=True")) { connection.Open(); var command = connection.CreateCommand(); command.CommandText = "UPDATE [Values] SET Value = 'Value' WHERE ID = " + id; Console.WriteLine("Updating record"); command.ExecuteNonQuery(); Console.Write("Press ENTER to end transaction: "); Console.ReadLine(); scope.Complete(); } } Here are some things I've already tried, with no change in behavior: Changing the transaction isolation level to "read uncommitted" Specifying a "WITH (ROWLOCK)" on the UPDATE statement

    Read the article

  • Problem while inserting data from GUI layer to database

    - by Rahul
    Hi all, I am facing problem while i am inserting new record from GUI part to database table. I have created database table Patient with id, name, age etc....id is identity primary key. My problem is while i am inserting duplicate name in table the control should go to else part, and display the message like...This name is already exits, pls try with another name... but in my coding not getting..... Here is all the code...pls somebody point me out whats wrong or how do this??? GUILayer: protected void BtnSubmit_Click(object sender, EventArgs e) { if (!Page.IsValid) return; int intResult = 0; string name = TxtName.Text.Trim(); int age = Convert.ToInt32(TxtAge.Text); string gender; if (RadioButtonMale.Checked) { gender = RadioButtonMale.Text; } else { gender = RadioButtonFemale.Text; } string city = DropDownListCity.SelectedItem.Value; string typeofdisease = ""; foreach (ListItem li in CheckBoxListDisease.Items) { if (li.Selected) { typeofdisease += li.Value; } } typeofdisease = typeofdisease.TrimEnd(); PatientBAL PB = new PatientBAL(); PatientProperty obj = new PatientProperty(); obj.Name = name; obj.Age = age; obj.Gender = gender; obj.City = city; obj.TypeOFDisease = typeofdisease; try { intResult = PB.ADDPatient(obj); if (intResult > 0) { lblMessage.Text = "New record inserted successfully."; TxtName.Text = string.Empty; TxtAge.Text = string.Empty; RadioButtonMale.Enabled = false; RadioButtonFemale.Enabled = false; DropDownListCity.SelectedIndex = 0; CheckBoxListDisease.SelectedIndex = 0; } else { lblMessage.Text = "Name [<b>" + TxtName.Text + "</b>] alredy exists, try another name"; } } catch (Exception ex) { lblMessage.Text = ex.Message.ToString(); } finally { obj = null; PB = null; } } BAL layer: public class PatientBAL { public int ADDPatient(PatientProperty obj) { PatientDAL pdl = new PatientDAL(); try { return pdl.InsertData(obj); } catch { throw; } finally { pdl=null; } } } DAL layer: public class PatientDAL { public string ConString = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString; public int InsertData(PatientProperty obj) { SqlConnection con = new SqlConnection(ConString); con.Open(); SqlCommand com = new SqlCommand("LoadData",con); com.CommandType = CommandType.StoredProcedure; try { com.Parameters.AddWithValue("@Name", obj.Name); com.Parameters.AddWithValue("@Age",obj.Age); com.Parameters.AddWithValue("@Gender",obj.Gender); com.Parameters.AddWithValue("@City", obj.City); com.Parameters.AddWithValue("@TypeOfDisease", obj.TypeOFDisease); return com.ExecuteNonQuery(); } catch { throw; } finally { com.Dispose(); con.Close(); } } } Property Class: public class PatientProperty { private string name; private int age; private string gender; private string city; private string typedisease; public string Name { get { return name; } set { name = value; } } public int Age { get { return age; } set { age = value; } } public string Gender { get { return gender; } set { gender = value; } } public string City { get { return city; } set { city = value; } } public string TypeOFDisease { get { return typedisease; } set { typedisease = value; } } } This is my stored Procedure: CREATE PROCEDURE LoadData ( @Name varchar(50), @Age int, @Gender char(10), @City char(10), @TypeofDisease varchar(50) ) as insert into Patient(Name, Age, Gender, City, TypeOfDisease)values(@Name,@Age, @Gender, @City, @TypeofDisease) GO

    Read the article

  • Is asp.net caching my sql results?

    - by Christian W
    I have the following method in an App_Code/Globals.cs file: public static XmlDataSource getXmlSourceFromOrgid(int orgid) { XmlDataSource xds = new XmlDataSource(); var ctx = new SensusDataContext(); SqlConnection c = new SqlConnection(ctx.Connection.ConnectionString); c.Open(); SqlCommand cmd = new SqlCommand(String.Format("select orgid, tekst, dbo.GetOrgTreeXML({0}) as Subtree from tblOrg where OrgID = {0}", orgid), c); var rdr = cmd.ExecuteReader(); rdr.Read(); StringBuilder sb = new StringBuilder(); sb.AppendFormat("&lt;node orgid=\"{0}\" tekst=\"{1}\"&gt;",rdr.GetInt32(0),rdr.GetString(1)); sb.Append(rdr.GetString(2)); sb.Append("&lt;/node&gt;"); xds.Data = sb.ToString(); xds.ID = "treedata"; rdr.Close(); c.Close(); return xds; } This gives me an XML-structure to use with the asp.net treeview control (I also use the CssFriendly extender to get nicer code) My problem is that if I logon on my pc with a code that gives me access on a lower level in the tree hierarchy (it's an orgianization hierarchy), it somehow "remembers" what level i logon at. So when my coworker tests from her computer with another code, giving access to another place in the tree, she get's the same tree as me. (The tree is supposed to show your own level and down.) I have added a html-comment to show what orgid it passes to the function, and the orgid passed is correct. So either the treeview caches something serverside, or the sqlquery caches it's result somehow... Any ideas? Sql function: ALTER function [dbo].[GetOrgTreeXML](@orgid int) returns XML begin RETURN (select org.orgid as '@orgid', org.tekst as '@tekst', [dbo].GetOrgTreeXML(org.orgid) from tblOrg org where (@orgid is null and Eier is null) or Eier=@orgid for XML PATH('NODE'), TYPE) end Extra code as requested: int orgid = int.Parse(Session["org"].ToString()); string orgname = context.Orgs.Where(q => q.OrgID == orgid).First().Tekst; debuglit.Text = String.Format("<!-- Id: {0} \n name: {1} -->", orgid, orgname); var orgxml = Globals.getXmlSourceFromOrgid(orgid); tvNavtree.DataSource = orgxml; tvNavtree.DataBind(); Where "debuglit" is a asp:Literal in the aspx file. EDIT: I have narrowed it down. All functions returns correct values. It just doesn't bind to it. I suspect the CssFriendly adapter to have something to do with it. I disabled the CssFriendly adapter and the problem persists... Stepping through it in debug it's correct all the way, with the stepper standing on "tvNavtree.DataBind();" I can hover the pointer over the tvNavtree.Datasource and see that it actually has the correct data. So something must be faulting in the binding process...

    Read the article

  • Web Services c#, Sending notifications Clients

    - by Diode
    I want to send a notification (say a string) to subscribers(subscribers ip addresses are in a database on the server side) by calling another method. when ever I call that method the output becomes error-some. [WebMethod] public string GetGroupPath(string emailAddress, string password, string ipAddress) { //SqlDataAdapter dbadapter = null; DataSet returnDS = new DataSet(); string groupName = null; string groupPath = null; SqlConnection dbconn = new SqlConnection("Server = localhost;Database=server;User ID = admin;Password = password;Trusted_Connection=false;"); dbconn.Open(); SqlCommand cmd = new SqlCommand(); string getGroupName = "select users.groupname from users where emailaddress = "+"'"+ emailAddress+"'"+ " and "+ "users.password = " + "'" +password+"'"; cmd.CommandText = getGroupName; cmd.Connection = dbconn; SqlDataReader reader = null; try { reader = cmd.ExecuteReader(); while (reader.Read()) { groupName = reader["groupname"].ToString(); } } catch (Exception) { groupPath = "Invalied"; } dbconn.Close(); dbconn.Open(); if (groupName != null) { string getPath = "select groups.grouppath from groups where groupname = " + "'" + groupName + "'"; cmd.CommandText = getPath; cmd.Connection = dbconn; try { reader = cmd.ExecuteReader(); while (reader.Read()) { groupPath = reader["grouppath"].ToString(); } } catch { groupPath = "Invalied"; } } else groupPath = "Invalied"; dbconn.Close(); if (groupPath != "Invalied") { dbconn.Open(); string getPath = "update users set users.ipaddress = "+"'"+ipAddress+"'"+" where users.emailaddress = " + "'" + emailAddress + "'"; cmd.CommandText = getPath; cmd.Connection = dbconn; cmd.ExecuteNonQuery(); dbconn.Close(); } NotifyUsers(); //NotifyUsers nu = new NotifyUsers(); //List<string> ipList = new List<string>(); //ipList.Add("192.168.56.1"); //nu.Notify(); return groupPath; } private void NotifyUsers() { Socket sock = new Socket(AddressFamily.InterNetwork, SocketType.Dgram, ProtocolType.Udp); byte[] ipb = Encoding.ASCII.GetBytes("255.255.255.255"); IPAddress ipAddress = new IPAddress(ipb); IPEndPoint endPoint = new IPEndPoint(ipAddress, 15000); string notification = "new_update"; byte[] sendBuffer = Encoding.ASCII.GetBytes(notification); sock.SendTo(sendBuffer, endPoint); sock.Close(); } This is what has to be basically done. in the server side I have a listening thread and it gets notification when the server sends data( assume for now the database contains client ip address). then ever I call the web method it gives a error "invalid IPAddress" atline byte[] ipb = Encoding.ASCII.GetBytes("255.255.255.255"); thank you :) since this is my first ever post please be kind enough to give me a better feedback too :) thaks

    Read the article

  • Update transaction in SQL Server 2008 R2 from ASP.Net not working

    - by Amarus
    Hello! Even though I've been a stalker here for ages, this is the first post I'm making. Hopefully, it won't end here and more optimistically future posts might actually be me trying to give a hand to someone else, I do owe this community that much and more. Now, what I'm trying to do is simple and most probably the reason behind it not working is my own stupidity. However, I'm stumped here. I'm working on an ASP.Net website that interacts with an SQL Server 2008 R2 database. So far everything has been going okay but updating a row (or more) just won't work. I even tried copying and pasting code from this site and others but it's always the same thing. In short: No exception or errors are shown when the update command executes (it even gives the correct count of affected rows) but no changes are actually made on the database. Here's a simplified version of my code (the original had more commands and tons of parameters each, but even when it's like this it doesn't work): protected void btSubmit_Click(object sender, EventArgs e) { using (SqlConnection connection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString)) { string commandString = "UPDATE [impoundLotAlpha].[dbo].[Vehicle]" + "SET [VehicleMake] = @VehicleMake" + " WHERE [ComplaintID] = @ComplaintID"; using (SqlCommand command = new SqlCommand(commandString, connection)) { SqlTransaction transaction = null; try { command.Connection.Open(); transaction = connection.BeginTransaction(IsolationLevel.Serializable); command.Transaction = transaction; SqlParameter complaintID = new SqlParameter("@complaintID", SqlDbType.Int); complaintID.Value = HttpContext.Current.Request.QueryString["complaintID"]; command.Parameters.Add(complaintID); SqlParameter VehicleMake = new SqlParameter("@VehicleMake", SqlDbType.VarChar, 20); VehicleMake.Value = tbVehicleMake.Text; command.Parameters.Add(VehicleMake); command.ExecuteNonQuery(); transaction.Commit(); } catch { transaction.Rollback(); throw; } finally { connection.Close(); } } } } I've tried this with the "SqlTransaction" stuff and without it and nothing changes. Also, since I'm doing multiple updates at once, I want to have them act as a single transaction. I've found that it can be either done like this or by use of the classes included in the System.Transactions namespace (CommittableTransaction, TransactionScope...). I tried all I could find but didn't get any different results. The connection string in web.config is as follows: <connectionStrings> <add name="ApplicationServices" connectionString="Data Source=localhost;Initial Catalog=ImpoundLotAlpha;Integrated Security=True" providerName="System.Data.SqlClient"/> </connectionStrings> So, tldr; version: What is the mistake that I did with that record update attempt? (Figured it out, check below if you're having a similar issue.) What is the best method to gather multiple update commands as a single transaction? Thanks in advance for any kind of help and/or suggestions! Edit: It seems that I was lacking some sleep yesterday cause this time it only took me 5 minutes to figure out my mistake. Apparently the update was working properly but I failed to notice that the textbox values were being overwritten in Page_Load. For some reason I had this part commented: if (IsPostBack) return; The second part of the question still stands. But should I post this as an answer to my own question or keep it like this?

    Read the article

  • SQL Server 2005: Improving performance for thousands or Insert requests. logout-login time= 120ms.

    - by Rad
    Can somebody shed some lights on how SQL Server 2005 deals with may request issued by a client using ADO.NET 2.0. Below is the shortend output of SQL Trace. I can see that connection pooling is working (I believe there is only one connection being pooled). What is not clear to me is why we have so many sp_reset_connection calls i.e a series of: Audit Login, SQL:BatchStarting, RPC:Starting and Audit Logout for each loop in for loop below. I can see that there is constant switching between tempdb and master database which leads me to conclude that we lost the context when next connection is created by fetching it from the pool based on ConectionString argument. I can see that every 15ms I can get 100-200 login/logout per second (reported at the same time by Profiler). The after 15ms I have again a series fo 100-200 login/logout per second. I need clarification on how this might affect much complex insert queries in production environment. I use Enterprise Library 2006, the code is compiled with VS 2005 and it is a console application that parses a flat file with 10 of thousand of rows grouping parent-child rows, runs on an application server and runs 2 stored procedure on a remote SQL Server 2005 inserting a parent record, retrieves Identity value and using it calls the second stored procedure 1, 2 or multiple times (sometimes several thousands) inserting child records. The child table has close to 10 million records with 5-10 indexes some of them being covering non-clustered. There is a pretty complex Insert trigger that copies inserted detail record to an archive table. All in all I only have 7 inserts per second which means it can take 2-4 hours for 50 thousand records. When I run Profiler on the test server (that is almost equivalent with production server) I can see that there is about 120ms between Audit Logout and Audit Login trace entries which almost give me chance to insert about 8 records. So my question is if there is some way to improve inserting of records since the company loads 100 thousands of records and does daily planning and has SLA to fulfill client request coming as flat file orders and some big files 10 thousands have to be processed(imported quickly). 4 hours to import 60 thousands should be reduced to 30 minutes. I was thinking to use BatchSize of DataAdapter to send multiple stored procedure calls, SQL Bulk inserts to batch multiple inserts from DataReader or DataTable, SSIS fast load. But I don't know how to properly analyze re-indexing and stats population and maybe this has to take some time to finish. What is worse is that the company uses the biggest table for reporting and other online processing and indexes cannot be dropped. I manage transaction manually by setting a field to a value and do an transactional update changing that value to a new value that other applications are using to get committed rows. Please advise how to approach this problem. For now I am trying to have a staging tables with minimal logging in a separate database and no indexes and I will try to do batched (massive) parent child inserts. I believe Production DB has simple recovery model, but it could be full recovery. If DB user that is being used by my .NET console application has bulkadmin role does it mean its bulk inserts are minimally logged. I understand that when a table has clustered and many non-clustered indexes that inserts are still logged for each row. Connection pooling is working, but with many login/logouts. Why? for (int i = 1; i <= 10000; i++){ using (SqlConnection conn = new SqlConnection("server=(local);database=master;integrated security=sspi;")) {conn.Open(); using (SqlCommand cmd = conn.CreateCommand()){ cmd.CommandText = "use tempdb"; cmd.ExecuteNonQuery();}}} SQL Server Profiler trace: Audit Login master 2010-01-13 23:18:45.337 1 - Nonpooled SQL:BatchStarting use tempdb master 2010-01-13 23:18:45.337 RPC:Starting exec sp_reset_conn tempdb 2010-01-13 23:18:45.337 Audit Logout tempdb 2010-01-13 23:18:45.337 2 - Pooled Audit Login -- network protocol master 2010-01-13 23:18:45.383 2 - Pooled SQL:BatchStarting use tempdb master 2010-01-13 23:18:45.383 RPC:Starting exec sp_reset_conn tempdb 2010-01-13 23:18:45.383 Audit Logout tempdb 2010-01-13 23:18:45.383 2 - Pooled Audit Login -- network protocol master 2010-01-13 23:18:45.383 2 - Pooled SQL:BatchStarting use tempdb master 2010-01-13 23:18:45.383 RPC:Starting exec sp_reset_conn tempdb 2010-01-13 23:18:45.383 Audit Logout tempdb 2010-01-13 23:18:45.383 2 - Pooled

    Read the article

  • ASP.NET Membership - Retrieve Password and PasswordSalt from Membership Table - Hash UserID

    - by Steve
    Hello, I am so close to get this project done. I need to retrieve the password and passwordSalt from my Membership table to compare it to my 'OldPasswords' table. The problem is the Membership provider does not let me use the GetPassword method because the password is hashed. And I can not retrieve it in a normal sqlConnection because the UserID is hashed also. Does anyone know how to hash the UserID so I can put it in my where clause? Or maybe there is a different way to get to that data? Any help is appreciated. Thank you, Steve

    Read the article

  • SqlCommand.Dispose() not disposing the SqlParameters in it - Memory Leak - C#.NET

    - by NLV
    Hello I've a windows forms application with MS SQL Server 2005 as the back end. I have written code in the form to call few stored procedures using SqlConnection, SqlCommand objects and i properly dispose everything. I've disposed sqlcommand object by calling oSqlCommand.Dispose() But i witnessed my application consuming huge amount of memory. I basically pass large XML files as SqlParameters. I finally decided to memory profile it using RedGate Memory profiler and i noticed that the System.Data.SqlClient.SqlParameters are not disposed. Any insights on this? Thanks NLV

    Read the article

  • Ado.net performance:What does SNIReadSync do?

    - by Beatles1692
    We have a query that takes 2 seconds to run in Sql Server Management Studio but it takes 13 seconds to be shown on a client screen. I used dotTrace to profile my source code and noticed there is this SNIReadSync method (part of ADO.net assemblies)that takes a lot of time to do its job(9 seconds).I ran my source over server so I could omit the network effects and the result was the same. It doesn't matter if I'm using OleDBConnection or SqlConnection. It doesn't matter if I'm using a DataReader or a DataSet. Connection pooling does not solve this issue(as my result shows). I googled this issue and I couldn't find an answer to the question that what this method is actually doing and how we can improve it. here's what I found on StakOverFlow that's not helpful either: http://stackoverflow.com/questions/1610874/snireadsync-executing-between-120-500-ms-for-a-simple-query-what-do-i-look-for

    Read the article

  • Populating DataGrid with SQLResult Air Flex

    - by Deyon
    I been beating my self up all day on this...I'm about to call it quits and get some Chinese food -=\ I'm selecting data from a local SQL DB. [Bindable] public var ac:ArrayCollection; public function select():void { statement.sqlConnection=conn; statement.text="SELECT * FROM PROJECT"; statement.execute(); var res : SQLResult = statement.getResult(); ac = new ArrayCollection(res.data); //So this traces out [Object][object] So it works trace(ac); } If I do var myObj:Object=res.data[0]; I can trace myObj and view the data. But I don't know how to insert the data into a datagrid. mygrid.dataProvider=ac; dose not work. I'm using Flash Builder 4 Help please....

    Read the article

  • Using SqlBulkCopy in a multithread scenario with ThreadPool issue

    - by Ruben F.
    Hi. I'm facing a dilemma (!). In a first scenario, i implemented a solution that replicates data from one data base to another using SQLBulkCopy synchronously and i had no problem at all. Now, using ThreadPool, i implemented the same in a assynchronously scenario, a thread per table, and all works fine, but past some time (usualy 1hour because the operations of copy takes about the same time), the operations sended to the ThreadPool stop being executed. There are one diferent SQLBulkCopy using one diferent SQLConnection per thread. I already see the number of free threads, and they are all free at the begining of the invocation...I have one AutoResetEvent to wait that the threads finish their job before launching again, and a Semaphore FIFO that hold the counter of active threads. There are some issue that I have forgotten or that I should avaliate when using SqlBulkCopy? I apreciate some help, because my ideas are over;) Thanks

    Read the article

  • insert into sql table column as GUID

    - by loviji
    I have tried with ado.net create table columnName with unique name. as uniquename I use new Guid() Guid sysColumnName = new Guid(); sysColumnName = Guid.NewGuid(); string stAddColumn = "ALTER TABLE " + tableName + " ADD " + sysColumnName.ToString() + " " + convertedColumnType + " NULL"; SqlCommand cmdAddColumn = new SqlCommand(stAddColumn, con); cmdAddColumn.ExecuteNonQuery(); con.Close(); and it fails: System.Data.SqlClient.SqlException: Incorrect syntax near '-'. ? System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) ? System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) now question, how can i fix it, or how can use different way to create unique column?

    Read the article

  • StructureMap: "No default instance of plugin defined" - even though it is

    - by Dave Hanna
    I've been using StructureMap for about 6 months now; you would think it would start getting easier. It doesn't seem to. Here's the first line of my registry: For<IDbConnection>() .Singleton() .Use<SqlConnection>() .Ctor<string>(WebConfigurationManager.ConnectionStrings["UnifiedConnectionString"].ConnectionString); It compiles and runs. But when I try to use that Interface, like this: return MsSqlConfiguration.MsSql2008.ConnectionString(((DbConnection)ObjectFactory.GetInstance<IDbConnection>()).ConnectionString); I get StructureMap Exception Code: 202 No Default Instance defined for PluginFamily System.Data.IDbConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089 Now I may be missing something (okay, I obviously am), but I don't know how much more plain I can be about defining the default instance for IDbConnection. What am I doing wrong?

    Read the article

  • Grid View Pagination.

    - by Wondering
    Dear All, I have a grid view and I want to implement Pagination functionality.This is working fine. protected DataSet FillDataSet() { string source = "Database=GridTest;Server=Localhost;Trusted_Connection=yes"; con = new SqlConnection(source); cmd = new SqlCommand("proc_mygrid", con); ds = new DataSet(); da = new SqlDataAdapter(cmd); da.Fill(ds); GridView1.DataSource = ds; GridView1.DataBind(); return ds; } protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e) { int newPagenumber = e.NewPageIndex; GridView1.PageIndex = newPagenumber; GridView1.DataSource = FillDataSet(); GridView1.DataBind(); } But the problem is for each pagination I have to call FillDataSet(); Is there any way to stop that.Any other coding approach? Thanks.

    Read the article

  • How do I close a database connection in a WCF service?

    - by Dan
    I have been unable to find any documentation on properly closing database connections in WCF service operations. I have a service that returns a streamed response through the following method. public virtual Message GetData() { string sqlString = BuildSqlString(); SqlConnection conn = Utils.GetConnection(); SqlCommand cmd = new SqlCommand(sqlString, conn); XmlReader xr = cmd.ExecuteXmlReader(); Message msg = Message.CreateMessage( OperationContext.Current.IncomingMessageVersion, GetResponseAction(), xr); return msg; } I cannot close the connection within the method or the streaming of the response message will be terminated. Since control returns to the WCF system after the completion of that method, I don't know how I can close that connection afterwards. Any suggestions or pointers to additional documentation would be appreciated. Dan

    Read the article

  • trying to connect mysql with vb.net

    - by user225269
    I've found this at connection strings.com http://connectionstrings.com/mysql Do I need to download connector-net from this site: http://dev.mysql.com/downloads/connector/net/ I recycled the code that I used in connecting vb.net with ms sql: Imports system.data.sqlclient idnum = TextBox1.Text lname = TextBox2.Text fname = TextBox3.Text skul = TextBox4.Text Using sqlcon As New SqlConnection("Server=localhost;Port=3306;Database=testing;Uid=root;Pwd=nitoryolai123$%^;") sqlcon.Open() Dim sqlcom As New SqlCommand() sqlcom.Connection = sqlcon sqlcom.CommandText = "INSERT INTO [student](ID, LASTNAME, FIRSTNAME, SCHOOL) VALUES (@ParameterID, @ParameterLastName, @ParameterFirstName, @ParameterSchool)" sqlcom.Parameters.AddWithValue("@ParameterID", TextBox1.Text) sqlcom.Parameters.AddWithValue("@ParameterLastName", TextBox2.Text) sqlcom.Parameters.AddWithValue("@ParameterFirstName", TextBox3.Text) sqlcom.Parameters.AddWithValue("@ParameterSchool", TextBox4.Text) sqlcom.ExecuteNonQuery() End Using But I get this error: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) Please help, what solutions would you recommend to this problem?

    Read the article

  • inserting null values in datetime column and integer column

    - by reggie
    I am using C# and developing a winform application. I have a project class which has the project attributes. the constructor of the project class is as follows: newProject = new Project(GCD_ID.IsNull() ? (int?)null : Convert.ToInt32(GCD_ID), txt_Proj_Desc.Text, txt_Prop_Name.Text, ST.ID.ToString().IsNull() ? null: ST.ID.ToString(), cmbCentre.Text, SEC.ID.ToString().IsNull() ? null : SEC.ID.ToString(), cmbZone.Text, FD.ID.ToString().IsNull() ? null : FD.ID.ToString(), DT.ID.ToString().IsNull() ? null : DT.ID.ToString(), OP.ID.ToString().IsNull() ? null : OP.ID.ToString(), T.ID.ToString().IsNull() ? null : T.ID.ToString(), CKV.ID.ToString().IsNull() ? null : CKV.ID.ToString(), STAT.ID.ToString().IsNull() ? null : STAT.ID.ToString(), MW.IsNull() ? (Double?)null : Convert.ToDouble(MW), txt_Subject.Text, Ip_Num.IsNull() ? (int?)null : Convert.ToInt32(Ip_Num), H1N_ID.IsNull() ? (int?)null : Convert.ToInt32(H1N_ID), NOMS_Slip_Num.IsNull() ? (int?)null : Convert.ToInt32(NOMS_Slip_Num), NMS_Updated.IsNull() ? (DateTime?)null : Convert.ToDateTime(NMS_Updated), Received_Date.IsNull() ? (DateTime?)null : Convert.ToDateTime(Received_Date), Actual_IS_Date.IsNull() ? (DateTime?)null : Convert.ToDateTime(Actual_IS_Date), Scheduled_IS_Date.IsNull() ? (DateTime?)null : Convert.ToDateTime(Scheduled_IS_Date), UpSt.ID.ToString().IsNull() ? null : UpSt.ID.ToString(), UpFd.ID.ToString().IsNull() ? null : UpFd.ID.ToString(), txtHVCircuit.Text, cmbbxSIA.Text); My problem is that i cannot insert values into the database when the datetime variables and the integer variables are null. all this data are assigned to the variable from textboxes on the form.. bELOW is the database function which takes in all the variables and insert them into the database. public static void createNewProject(int? GCD_ID, string Project_Desc, string Proponent_Name, int Station_ID, string OpCentre, int Sector_ID, string PLZone, int Feeder, int DxTx_ID, int OpControl_ID, int Type_ID, int ConnKV_ID, int Status_ID, double? MW, string Subject, int? Ip_Num, int? H1N_ID, int? NOMS_Slip_Num, DateTime? NMS_Updated, DateTime? Received_Date, DateTime? Actual_IS_Date, DateTime? Scheduled_IS_Date, int UP_Station_ID, int UP_Feeder_ID, string @HV_Circuit, string SIA_Required) { SqlConnection conn = null; try { //Takes in all the employee details to be added to the database. conn = new SqlConnection(databaseConnectionString); conn.Open(); SqlCommand cmd = new SqlCommand("createNewProject", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("GCD_ID", GCD_ID)); cmd.Parameters.Add(new SqlParameter("Project_Desc", MiscFunctions.Capitalize(Project_Desc))); cmd.Parameters.Add(new SqlParameter("Proponent_Name", MiscFunctions.Capitalize(Proponent_Name))); cmd.Parameters.Add(new SqlParameter("Station_ID", Station_ID)); cmd.Parameters.Add(new SqlParameter("OpCentre", OpCentre)); cmd.Parameters.Add(new SqlParameter("Sector_ID", Sector_ID)); cmd.Parameters.Add(new SqlParameter("PLZone", PLZone)); cmd.Parameters.Add(new SqlParameter("Feeder", Feeder)); cmd.Parameters.Add(new SqlParameter("DxTx_ID", DxTx_ID)); cmd.Parameters.Add(new SqlParameter("OpControl_ID", OpControl_ID)); cmd.Parameters.Add(new SqlParameter("Type_ID", Type_ID)); cmd.Parameters.Add(new SqlParameter("ConnKV_ID", ConnKV_ID)); cmd.Parameters.Add(new SqlParameter("Status_ID", Status_ID)); cmd.Parameters.Add(new SqlParameter("MW", MW)); cmd.Parameters.Add(new SqlParameter("Subject", Subject)); cmd.Parameters.Add(new SqlParameter("Ip_Num", Ip_Num)); cmd.Parameters.Add(new SqlParameter("H1N_ID", H1N_ID)); cmd.Parameters.Add(new SqlParameter("NOMS_Slip_Num", NOMS_Slip_Num)); cmd.Parameters.Add(new SqlParameter("NMS_Updated", NMS_Updated)); cmd.Parameters.Add(new SqlParameter("Received_Date", Received_Date)); cmd.Parameters.Add(new SqlParameter("Actual_IS_Date", Actual_IS_Date)); cmd.Parameters.Add(new SqlParameter("Scheduled_IS_Date", Scheduled_IS_Date)); cmd.Parameters.Add(new SqlParameter("UP_Station_ID", UP_Station_ID)); cmd.Parameters.Add(new SqlParameter("UP_Feeder_ID", UP_Feeder_ID)); cmd.Parameters.Add(new SqlParameter("HV_Circuit", HV_Circuit)); cmd.Parameters.Add(new SqlParameter("SIA_Required", SIA_Required)); cmd.ExecuteNonQuery(); } catch (Exception e) //returns if error incurred. { MessageBox.Show("Error occured in createNewProject" + Environment.NewLine + e.ToString()); } finally { if (conn != null) { conn.Close(); } } } My question is, how do i insert values into the database. please please help

    Read the article

  • How to check a SQL database table to see if a record exists

    - by Anthony
    I have a SQL database that creates a record for every document uploaded by the user to the server. I want to check this table before a user uploads a document to ensure they don't upload a file with name that already exists. I know how to make the connection and make the SqlCommand to query the table for an existing record. But I don't know how to check the record count from the sqlCommand I made. Does that make sense? Using myConnectionCheck As New SqlConnection(myConnectionStringCheck) Dim myCommandCheck As New SqlCommand() myCommandCheck.Connection = myConnectionCheck myCommandCheck.CommandText = "SELECT * FROM Req_Docs WHERE Doc_Name =" & DocName myConnectionCheck.Open() myCommandCheck.ExecuteNonQuery() End Using Thanks in advance, Anthony

    Read the article

  • How do I close a database connection being used to produce a streaming result in a WCF service?

    - by Dan
    I have been unable to find any documentation on properly closing database connections in WCF service operations. I have a service that returns a streamed response through the following method. public virtual Message GetData() { string sqlString = BuildSqlString(); SqlConnection conn = Utils.GetConnection(); SqlCommand cmd = new SqlCommand(sqlString, conn); XmlReader xr = cmd.ExecuteXmlReader(); Message msg = Message.CreateMessage( OperationContext.Current.IncomingMessageVersion, GetResponseAction(), xr); return msg; } I cannot close the connection within the method or the streaming of the response message will be terminated. Since control returns to the WCF system after the completion of that method, I don't know how I can close that connection afterwards. Any suggestions or pointers to additional documentation would be appreciated.

    Read the article

  • SqlCommand() ExecuteNonQuery() truncates command text.

    - by H. Abraham Chavez
    I'm building a custom db deployment utility, I need to read text files containing sql scripts and execute them against the database. Pretty easy stuff, so far so good. However I've encountered a snag, the contents of the file are read successfully and entirely, but once passed into the SqlCommand and then executed with SqlCommand.ExecuteNonQuery only part of the script is executed. I fired up Profiler and confirmed that my code is not passing all of the script. private void ExecuteScript(string cmd, SqlConnection sqlConn, SqlTransaction trans) { SqlCommand sqlCmd = new SqlCommand(cmd, sqlConn, trans); sqlCmd.CommandType = CommandType.Text; sqlCmd.CommandTimeout = 9000000; // for testing sqlCmd.ExecuteNonQuery(); } // I call it like this, readDMLScript contains 543 lines of T-SQL string readDMLScript = ReadFile(dmlFile); ExecuteScript(readDMLScript, sqlConn, trans);

    Read the article

  • .NET and Closing SQL Server connections

    - by user307076
    I am having a hard time figuring out why the following constructor will not close connnections. When I view the active connections. Here is the code that I have. Public Sub New(ByVal UserID As Integer) Dim oConn As New SqlConnection(ConfigurationManager.ConnectionStrings("connStr").ToString()) Dim cmd As New SqlCommand("stored proc", oConn) Dim sdr As SqlDataReader cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.AddWithValue("@userID", UserID) oConn.Open() sdr = cmd.ExecuteReader() Try If Not sdr.HasRows Then sdr.Close() If Not oConn Is Nothing Then If oConn.State <> ConnectionState.Closed Then oConn.Close() End If End If cmd.Dispose() Exit Sub End If 'User has account in WATS, proceed to load account information While sdr.Read _firstname = Convert.ToString(sdr("First Name")) _lastname = Convert.ToString(sdr("Last Name")) End While Catch ex As Exception 'Throw New Exception("User Error: " + ex.Message) Finally sdr.Close() If Not oConn Is Nothing Then If oConn.State <> ConnectionState.Closed Then oConn.Close() End If End If cmd.Dispose() End Try End Sub

    Read the article

  • ado.net managing connections

    - by madlan
    Hi, I'm populating a listview with a list of databases on a selected SQL instance, then retrieving a value from each database (It's internal product version, column doesn't always exist) I'm calling the below function to populate the second column of the listview: item.SubItems.Add(DBVersionCheck(serverName, database.Name)) Function DBVersionCheck(ByVal SelectedInstance As String, ByVal SelectedDatabase As String) Dim m_Connection As New SqlConnection("Server=" + SelectedInstance + ";User Id=sa;Password=password;Database=" + SelectedDatabase) Dim db_command As New SqlCommand("select Setting from SystemSettings where [Setting] = 'version'", m_Connection) Try m_Connection.Open() Return db_command.ExecuteScalar().trim m_Connection.Dispose() Catch ex As Exception 'MessageBox.Show(ex.Message) Return "NA" Finally m_Connection.Dispose() End Try End Function This works fine except it's creating a connection to each database and leaving it open. My understanding is the close()\dispose() releases only the connection from the pool in ado rather than the actual connection to sql. How would I close the actual connections after I've retrieved the value? Leaving these open will create hundreds of connections to databases that will probably not be used for that session.

    Read the article

< Previous Page | 10 11 12 13 14 15 16 17 18 19 20  | Next Page >