Search Results

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

Page 6/20 | < Previous Page | 2 3 4 5 6 7 8 9 10 11 12 13  | Next Page >

  • SQL ConnectionString in web.config and global.asax implications

    - by rlb.usa
    This is going to sound very odd, but I have a web.config like this: <connectionStrings> <remove name="LocalSqlServer"/> <add name="LocalSqlServer" connectionString="Data Source=BACKUPDB;..." providerName="System.Data.SqlClient"/> </connectionStrings> And a global.asax like this: void Session_Start(object sender, EventArgs e) { // Code that runs when a new session is started if (Application["con"] == null || Application["con"] == "") { Application["con"] = "Data Source=PRODUCTIONDB;..."; } } And EVERYWHERE in my code, I reference my ConnectionStrings like this: SqlConnection con = new SqlConnection(Convert.ToString(HttpContext.Current.Application["con"])); However, I see that everything I do inside this application goes to BACKUP db instead of PRODUCTIONDB. What is going on, how could this happen, and why? It doesn't make any sense to me, and it got me into a lot of trouble. We use LocalSqlServer string for FormsAuthentication.

    Read the article

  • SQLite.Net Issue With BeginTransaction

    - by cam
    I'm trying to use System.Data.Sqlite library, and I'm following the documentation about optimizing inserts so I copied this code directly out of the documentation: using (SQLiteTransaction mytransaction = myconnection.BeginTransaction()) { using (SQLiteCommand mycommand = new SQLiteCommand(myconnection)) { SQLiteParameter myparam = new SQLiteParameter(); int n; mycommand.CommandText = "INSERT INTO [MyTable] ([MyId]) VALUES(?)"; mycommand.Parameters.Add(myparam); for (n = 0; n < 100000; n ++) { myparam.Value = n + 1; mycommand.ExecuteNonQuery(); } } mytransaction.Commit(); } Now, I initialize I connection right before that by using SqlConnection myconnection = new SqlConnection("Data Source=blah"); I have a Database named blah, with the correct tables and values. The problem is when I run this code, it says "Operation is not valid due to the current state of the object" I've tried changing the code around several times, and it still points to BeginTransaction. What gives?

    Read the article

  • Call server side method from JSON

    - by Zerotoinfinite
    How to call a method on a server side from JSON. Below is the code I am using SERVER SIDE: [WebMethod] private void GetCustomer( string NoOfRecords) { string connString = "Data Source=Something;Initial Catalog=AdventureWorks;Trusted_Connection=True;"; SqlConnection sCon = new SqlConnection(connString); SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Sales.Customer WHERE CustomerID < '" + NoOfRecords+ "' ", sCon); DataSet ds = new DataSet(); da.Fill(ds); GvDetails.DataSource = ds.Tables[0]; GvDetails.DataBind(); } On Client Side: var txtID = document.getElementById('txtValue'); $.ajax({ type: "POST", url: "Default.aspx/GetCustomer", data: "{Seconds:'" + txtID +"'}", contentType: "application/json; charset=utf-8", dataType: "json", success: function(response) { alert(response); } }); Now I want that on button click, I would call the function at the client side [JSON], which pass the textbox value to the function. Please help

    Read the article

  • How to add an XML parameter to a stored procedure in C#?

    - by salvationishere
    I am developing a C# web application in VS 2008 which interacts with my Adventureworks database in my SQL Server 2008. Now I am trying to add new records to one of the tables which has an XML column in it. How do I do this? This is the error I'm getting: System.Data.SqlClient.SqlException was caught Message="XML Validation: Text node is not allowed at this location, the type was defined with element only content or with simple content. Location: /" Source=".Net SqlClient Data Provider" ErrorCode=-2146232060 Class=16 LineNumber=22 Number=6909 Procedure="AppendDataC" Server="." 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.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.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at ADONET_namespace.ADONET_methods.AppendDataC(DataRow d, Hashtable ht) in C:\Documents and Settings\Admin\My Documents\Visual Studio 2008\Projects\AddFileToSQL\AddFileToSQL\ADONET methods.cs:line 212 InnerException: And this is a portion of my code in C#: try { SqlConnection conn2 = new SqlConnection(connString); SqlCommand cmd = conn2.CreateCommand(); cmd.CommandText = "dbo.AppendDataC"; cmd.CommandType = CommandType.StoredProcedure; cmd.Connection = conn2; ... sqlParam10.SqlDbType = SqlDbType.VarChar; SqlParameter sqlParam11 = cmd.Parameters.AddWithValue("@" + ht["@col11"], d[10]); sqlParam11.SqlDbType = SqlDbType.VarChar; SqlParameter sqlParam12 = cmd.Parameters.AddWithValue("@" + ht["@col12"], d[11]); sqlParam12.SqlDbType = SqlDbType.Xml; ... conn2.Open(); cmd.ExecuteNonQuery(); //This is the line it fails on and then jumps //to the Catch statement conn2.Close(); errorMsg = "The Person.Contact table was successfully updated!"; } catch (Exception ex) { Right now in my text input MDF file I have the XML parameter as: '<Products><id>3</id><id>6</id><id>15</id></Products>' Is this valid format for XML?

    Read the article

  • Passing value in silverlight

    - by Dilse Naaz
    How can pass a value from one page to another page in silverlight. I have one silver light application which contains two pages, one xaml.cs file and one asmx.cs file. I have one text box in xaml page names Text1. My requirement is that at the time of running, i could pass the textbox value to asmx.cs file. How it will be done? my code in asmx.cs file is public string DataInsert(string emp) { SqlConnection conn = new SqlConnection("Data Source=Nisam\\OFFICESERVERS;Initial Catalog=Employee;Integrated Security=SSPI"); SqlCommand cmd = new SqlCommand(); conn.Open(); cmd.Connection = conn; cmd.CommandText = "Insert into demo Values (@Name)"; cmd.Parameters.AddWithValue("@Name", xxx); cmd.ExecuteNonQuery(); return "Saved"; } the value xxx in code is replaced by the passed value from xaml.cs page. pls help me

    Read the article

  • Converting SQL to LINQ to XML

    - by Morano88
    I'm writing the following code to convert SQL to LINQ and then to XML: SqlConnection thisConnection = new SqlConnection(@"Data Source=3BDALLAH-PC;Initial Catalog=XMLC;Integrated Security=True;Pooling=False;"); thisConnection.Open(); XElement eventsGive = new XElement("data", from c in ?????? select new XElement("event", new XAttribute("start", c.start), new XAttribute("end",c.eend), new XAttribute("title",c.title), new XAttribute("Color",c.Color), new XAttribute("link",c.link))); Console.WriteLine(eventsGive); The name of the table is "XMLC" and I want to refer to it. How can I do that? When I put its name directly VS gives an error. Also when I say thisConnection.XMLC it doesn't work.

    Read the article

  • How to move from untyped DataSets to POCO\LINQ2SQL in legacy application

    - by artvolk
    Good day! I've a legacy application where data access layer consists of classes where queries are done using SqlConnection/SqlCommand and results are passed to upper layers wrapped in untyped DataSets/DataTable. Now I'm working on integrating this application into newer one where written in ASP.NET MVC 2 where LINQ2SQL is used for data access. I don't want to rewrite fancy logic of generating complex queries that are passed to SqlConnection/SqlCommand in LINQ2SQL (and don't have permission to do this), but I'd like to have result of these queries as strong-typed objects collection instead of untyped DataSets/DataTable. The basic idea is to wrap old data access code in a nice-looking from ASP.NET MVC "Model". What is the fast\easy way of doing this?

    Read the article

  • cannot read multiple rows from sqldatareader

    - by amby
    Hi, when i query for only one record/row, sqldatareader is giving correct result but when i query for multiple rows, its giving error on the client side. below is my code. please tell me what is the problem here. string query = "select * from Customer_Order where orderNumber = " + order;//+" OR orderNumber = 17"; DataTable dt = new DataTable(); Hashtable sendData = new Hashtable(); try { using (SqlConnection conn = new SqlConnection(ConnectionString)) { using (SqlCommand cmd = new SqlCommand(query, conn)) { conn.Open(); SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); dt.Load(dr); } }

    Read the article

  • ADO.NET: Faster way to check if the database server is accessible?

    - by lotana
    At the moment I am using this code to check if the database is accessible: public bool IsDatabaseOnline(string con) { bool isConnected = false; SQLConnection connect = null; try { connect = new SQLConnection(con); connect.Open(); isConnected = true; } catch (Exception e) { isConnected = false; } finally { if (connect != null) connect.Close(); } return isConnected; } While this code works fine, there is a disadvantage. If the server is not online it spends about 4 full seconds trying to open the connection before deciding that it is not available. Is there a way to test the connection without trying to actually opening it and waiting for the timeout? Something like a database-equivalent of ping?

    Read the article

  • How Do I insert Data in a table From the Model MVC?

    - by user54197
    I have data coming into my Model, how do I setup to insert the data in a table? public string Name { get; set; } public string Address { get; set; } public string City { get; set; } public string State { get; set; } public string Zip { get; set; } public Info() { using (SqlConnection connect = new SqlConnection(connections)) { string query = "Insert Into Personnel_Data (Name, StreetAddress, City, State, Zip, HomePhone, WorkPhone)" + "Values('" + Name + "','" + Address + "','" + City + "','" + State + "','" + Zip + "','" + ContactHPhone + "','" + ContactWPhone + "')"; SqlCommand command = new SqlCommand(query, connect); connect.Open(); command.ExecuteNonQuery(); } } The Name, Address, City, and so on is null when the query is being run. How do I set this up?

    Read the article

  • Can't get a SQL command to recognise the params added

    - by littlechris
    Hi, I've not used basic SQL commands for a while and I'm trying to pass a param to a sproc and the run it. However when I run the code I get a "Not Supplied" error. Code: SqlConnection conn1 = new SqlConnection(DAL.getConnectionStr()); SqlCommand cmd1 = new SqlCommand("SProc_Item_GetByID", conn1); cmd1.Parameters.Add(new SqlParameter("@ID", itemId)); conn1.Open(); cmd1.ExecuteNonQuery(); I'm not really sure why this would fail. Apologies for the basic question, but I'm lost! Thanks in advance.

    Read the article

  • Is it OK to pass SQLCommand as a parameter?

    - by TooFat
    I have a Business Layer that passes a Conn string and a SQLCommand to a Data Layer like so public void PopulateLocalData() { System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.CommandText = "usp_PopulateServiceSurveyLocal"; DataLayer.DataProvider.ExecSQL(ConnString, cmd); } The DataLayer then just executes the sql like so public static int ExecSQL(string sqlConnString, System.Data.SqlClient.SqlCommand cmd) { int rowsAffected; using (SqlConnection conn = new SqlConnection(sqlConnString)) { conn.Open(); cmd.Connection = conn; rowsAffected = cmd.ExecuteNonQuery(); cmd.Dispose(); } return rowsAffected; } Is it OK for me to pass the SQLCommand as a parameter like this or is there a better more accepted way of doing it. One of my concerns is if an error occurs when executing the query the cmd.dispose line will never execute. Does that mean it will continue to use up memory that will never be released?

    Read the article

  • Why does this code block say "not all code paths return a value"?

    - by Kishan
    I wrote following code...but i am getting Error like: Error 1 'LoginDLL.Class1.Login(string, string, string)': not all code paths return a value Please help me... Thanks in advance... My code is as given below... public int Login(string connectionString,string username,string password) { SqlConnection con=new SqlConnection(connectionString); con.Open(); SqlCommand validUser = new SqlCommand("SELECT count(*) from USER where username=@username", con); validUser.Parameters.AddWithValue("@username", username); int value=Convert.ToInt32(validUser.ExecuteScalar().ToString()); if (value == 1) { //check for password SqlCommand validPassword = new SqlCommand("SELECT password from USER where username=@username", con); validPassword.Parameters.AddWithValue("@username", username); string pass = validPassword.ExecuteScalar().ToString(); if (pass == password) { //valid login return 1; } else { return 0; } } else if (value == 0) { return 2; } }

    Read the article

  • How can i return dataset perfectly from sql?

    - by Phsika
    i try to write a winform application: i dislike below codes: DataTable dt = new DataTable(); dt.Load(dr); ds = new DataSet(); ds.Tables.Add(dt); Above part of codes looks unsufficient.How can i best loading dataset? public class LoadDataset { public DataSet GetAllData(string sp) { return LoadSQL(sp); } private DataSet LoadSQL(string sp) { SqlConnection con = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"].ToString()); SqlCommand cmd = new SqlCommand(sp, con); DataSet ds; try { con.Open(); cmd.CommandType = CommandType.StoredProcedure; SqlDataReader dr = cmd.ExecuteReader(); DataTable dt = new DataTable(); dt.Load(dr); ds = new DataSet(); ds.Tables.Add(dt); return ds; } finally { con.Dispose(); cmd.Dispose(); } } }

    Read the article

  • How to catch exception on RollBack

    - by Jagd
    What is the best way to implement error handling for a SqlTransaction RollBack that already exists within a catch clause? My code is roughly like this: using (SqlConnection objSqlConn = new SqlConnection(connStr)) { objSqlConn.Open(); using (SqlTransaction objSqlTrans = objSqlConn.BeginTransaction()) { try { // code // more code // and more code } catch (Exception ex) { // What happens if RollBack() has an exception? objSqlTrans.Rollback(); throw ex; } } } I believe that my application had an exception in the try block, which in turn was caught in the catch block and then the RollBack was attempted. However, the error that I'm seeing says something about a SqlTransaction.ZombieCheck(), which is making me wonder if the RollBack() itself threw an exception as well. So, do I need to implement some type of error handling at the RollBack()? How do I do that and manage to hold on to the exception that put the execution into the catch block in the first place?

    Read the article

  • c# - Object reference not set to an instance of an object.

    - by tom
    Line 30: sUsername.Trim(); Line 31: sPassword.Trim(); Line 32: string ConnectionString = WebConfigurationManager.ConnectionStrings["dbnameConnectionString"].ConnectionString; Line 33: SqlConnection myConnection = new SqlConnection(ConnectionString); Line 34: try 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. Any ideas? I don't understand the error.

    Read the article

  • C# SQL Parameter Errors in Loops

    - by jakesankey
    Please help me out with this. I have this small application to load txt files into a sql db and it works fine with sqlite. When I ported to SQL I started getting 'parameter already declared' errors.. If anyone can help me reorganize this code, it would be great! I need to get the parameter definitions outside of the loops or something.. using System; using System.Data; using System.Data.SQLite; using System.IO; using System.Text.RegularExpressions; using System.Threading; using System.Collections.Generic; using System.Linq; using System.Data.SqlClient; namespace JohnDeereCMMDataParser { internal class Program { public static List<string> GetImportedFileList() { List<string> ImportedFiles = new List<string>(); using (SqlConnection connect = new SqlConnection(@"Server=FRXSQLDEV;Database=RX_CMMData;Integrated Security=YES")) { connect.Open(); using (SqlCommand fmd = connect.CreateCommand()) { fmd.CommandText = @"SELECT FileName FROM Import;"; fmd.CommandType = CommandType.Text; SqlDataReader r = fmd.ExecuteReader(); while (r.Read()) { ImportedFiles.Add(Convert.ToString(r["FileName"])); } } } return ImportedFiles; } private static void Main(string[] args) { using (SqlConnection con = new SqlConnection(@"Server=FRXSQLDEV;Database=RX_CMMData;Integrated Security=YES")) { con.Open(); using (SqlCommand insertCommand = con.CreateCommand()) { Console.WriteLine("Connecting to SQL server..."); SqlCommand cmdd = con.CreateCommand(); string[] files = Directory.GetFiles(@"C:\Documents and Settings\js91162\Desktop\", "R.txt*", SearchOption.AllDirectories); insertCommand.Parameters.Add(new SqlParameter("@FeatType", DbType.String)); insertCommand.Parameters.Add(new SqlParameter("@FeatName", DbType.String)); insertCommand.Parameters.Add(new SqlParameter("@Value", DbType.String)); insertCommand.Parameters.Add(new SqlParameter("@Actual", DbType.Decimal)); insertCommand.Parameters.Add(new SqlParameter("@Nominal", DbType.Decimal)); insertCommand.Parameters.Add(new SqlParameter("@Dev", DbType.Decimal)); insertCommand.Parameters.Add(new SqlParameter("@TolMin", DbType.Decimal)); insertCommand.Parameters.Add(new SqlParameter("@TolPlus", DbType.Decimal)); insertCommand.Parameters.Add(new SqlParameter("@OutOfTol", DbType.Decimal)); List<string> ImportedFiles = GetImportedFileList(); foreach (string file in files.Except(ImportedFiles)) { var FileNameExt1 = Path.GetFileName(file); cmdd.Parameters.Add(new SqlParameter("@FileExt", FileNameExt1)); cmdd.CommandText = @" IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'RX_CMMData' AND TABLE_NAME = 'Import')) BEGIN SELECT COUNT(*) FROM Import WHERE FileName = @FileExt; END"; int count = Convert.ToInt32(cmdd.ExecuteScalar()); con.Close(); con.Open(); if (count == 0) { Console.WriteLine("Parsing CMM data for SQL database... Please wait."); insertCommand.CommandText = @" INSERT INTO Import (FeatType, FeatName, Value, Actual, Nominal, Dev, TolMin, TolPlus, OutOfTol, PartNumber, CMMNumber, Date, FileName) VALUES (@FeatType, @FeatName, @Value, @Actual, @Nominal, @Dev, @TolMin, @TolPlus, @OutOfTol, @PartNumber, @CMMNumber, @Date, @FileName);"; string FileNameExt = Path.GetFullPath(file); string RNumber = Path.GetFileNameWithoutExtension(file); string RNumberE = RNumber.Split('_')[0]; string RNumberD = RNumber.Split('_')[1]; string RNumberDate = RNumber.Split('_')[2]; DateTime dateTime = DateTime.ParseExact(RNumberDate, "yyyyMMdd", Thread.CurrentThread.CurrentCulture); string cmmDate = dateTime.ToString("dd-MMM-yyyy"); string[] lines = File.ReadAllLines(file); bool parse = false; foreach (string tmpLine in lines) { string line = tmpLine.Trim(); if (!parse && line.StartsWith("Feat. Type,")) { parse = true; continue; } if (!parse || string.IsNullOrEmpty(line)) { continue; } Console.WriteLine(tmpLine); foreach (SqlParameter parameter in insertCommand.Parameters) { parameter.Value = null; } string[] values = line.Split(new[] { ',' }); for (int i = 0; i < values.Length - 1; i++) { SqlParameter param = insertCommand.Parameters[i]; if (param.DbType == DbType.Decimal) { decimal value; param.Value = decimal.TryParse(values[i], out value) ? value : 0; } else { param.Value = values[i]; } } } insertCommand.Parameters.Add(new SqlParameter("@PartNumber", RNumberE)); insertCommand.Parameters.Add(new SqlParameter("@CMMNumber", RNumberD)); insertCommand.Parameters.Add(new SqlParameter("@Date", cmmDate)); insertCommand.Parameters.Add(new SqlParameter("@FileName", FileNameExt)); // insertCommand.ExecuteNonQuery(); } } Console.WriteLine("CMM data successfully imported to SQL database..."); } con.Close(); } } } } FYI - the PartNumber, CMMNumber, Date, etc at the bottom are pulled from the file name and I need it in the table next to each respective record.

    Read the article

  • Raw SQL sent to SQL Server from .NET on stored procedure call

    - by Jeff Meatball Yang
    Is there a way to get the raw text that is sent to SQL Server, as seen in SQL Profiler, from the ADO.NET call? using(SqlConnection conn = new SqlConnection(connString)) { SqlCommand cmd = conn.CreateCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "GetSomeData"; cmd.Parameters.Add("@id").Value = someId; cmd.Parameters.Add("@someOtherParam").Value = "hello"; conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); // this sends up the call: exec GetSomeData @id=24, @someOtherParam='hello' // how can I capture that and write it to debug? Debug.Write("exec GetSomeData @id=24, @someOtherParam='hello'"); }

    Read the article

  • Is it OK to re-create many SQL connections (SQL 2008)

    - by Mr. Flibble
    When performing many inserts into a database I would usually have code like this: using (var connection = new SqlConnection(connStr)) { connection.Open(); foreach (var item in items) { var cmd = new SqlCommand("INSERT ...") cmd.ExecuteNonQuery(); } } I now want to shard the database and therefore need to choose the connection string based on the item being inserted. This would make my code run more like this foreach (var item in items) { connStr = GetConnectionString(item); using (var connection = new SqlConnection(connStr)) { connection.Open(); var cmd = new SqlCommand("INSERT ...") cmd.ExecuteNonQuery(); } } Which basically means it's creating a new connection to the database for each item. Will this work or will recreating connections for each insert cause terrible overhead?

    Read the article

  • Value cannot be null.Parameter name: key when databind in ASP.NET

    - by Yongwei Xing
    Hi all I am triing to bind the data to a listbox from sql server then got the error "Value cannot be null.Parameter name: key" sqlCommand = "SELECT [Country] FROM [tbl_LookupCountry] where [Country] IS NOT NULL"; SqlConnection sqlConCountry = new SqlConnection(connectString); SqlCommand sqlCommCountry = new SqlCommand(); sqlCommCountry.Connection = sqlConCountry; sqlCommCountry.CommandType = System.Data.CommandType.Text; sqlCommCountry.CommandText = sqlCommand; sqlCommCountry.CommandTimeout = 300; sqlConCountry.Open(); reader = sqlCommCountry.ExecuteReader(); ddlCountry.DataSource = reader; ddlCountry.DataBind(); sqlConCountry.Close(); Does anyone meet this problem before?

    Read the article

  • C# - Rollback SqlTransaction in catch block - Problem with object accessability

    - by Marks
    Hi there. I've got a problem, and all articles or examples i found seem to not care about it. I want to do some database actions in a transaction. What i want to do is very similar to most examples: using (SqlConnection Conn = new SqlConnection(_ConnectionString)) { try { Conn.Open(); SqlTransaction Trans = Conn.BeginTransaction(); using (SqlCommand Com = new SqlCommand(ComText, Conn)) { /* DB work */ } } catch (Exception Ex) { Trans.Rollback(); return -1; } } But the problem is, that the SqlTransaction Trans is declared inside the try block. So it is not accessable in the catch() block. Most examples just do Conn.Open() and Conn.BeginTransaction() before the try block. But i think thats a bit risky, since both can throw multiple exceptions. Am I wrong, or do most people just ignore this risk? Whats the best solution to be able to rollback, if an exception happens. Thanks in advance, Marks

    Read the article

  • Need to get Multiple tables from SqlServer at a time

    - by narmadha
    Hi ,I am working with C#.net,I am using cursor concept in my storedprocedure,While executing it in Sqlserver it is showing Multiple tables,but it is not returning multiple Tables in the code,it is returning only the first table,The following is the code which i have used: DataSet ds = new DataSet("table1"); using (SqlConnection connection = new SqlConnection(connectionstring)) { using (SqlDataAdapter da = new SqlDataAdapter("getworkpersondetails_Orderidwise", connection)) { da.SelectCommand.CommandType = CommandType.StoredProcedure; SqlParameter param; param = new SqlParameter("@OrderId", SqlDbType.Int); param.Value = OrderId; da.SelectCommand.Parameters.Add(param); param = new SqlParameter("@CompanyId", SqlDbType.Int); param.Value = CompanyId; da.SelectCommand.Parameters.Add(param); connection.Open(); da.Fill(ds); connection.Close(); return ds; } }

    Read the article

  • Check if server exists

    - by buhtla
    Here is the problem. User can enter server name and connection string for database. If server is not accessible (wrong address, firewalls or any other issue), I want to be aware of this as quickly as possible. If I'm using sqlConnection and try to connect to a non-accessible server this takes very long (I think more than 1 minute!). This has nothing to do with connection timeout btw so setting this property won't help. My idea is to first try to ping server and if I get response (which means server is accessible from the point of application), than proceed with sqlConnection. If there is no response from ping, operation is aborted and user is properly notified. Is there any better way of doing this? Any idea would be welcome.

    Read the article

  • SQL Invalid Object Name 'AddressType'

    - by salvationishere
    I am getting the above error in my VS 2008 C# method when I try to invoke the SQL getColumnNames stored procedure from VS. This SP accepts one input parameter, the table name, and works successfully from SSMS. Currently I am selecting the AdventureWorks AddressType table for it to pull the column names from this table. I can see teh AdventureWorks table available in VS from my Server Explorer / Data Connection. And I see both the AddressType table and getColumnNames SP showing in Server Explorer. But I am still getting this error listed above. Here is the C# code snippet I use to execute this: public static DataTable DisplayTableColumns(string tt) { SqlDataReader dr = null; string TableName = tt; string connString = "Data Source=.;AttachDbFilename=\"C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\AdventureWorks_Data.mdf\";Initial Catalog=AdventureWorks;Integrated Security=True;Connect Timeout=30;User Instance=False"; string errorMsg; SqlConnection conn2 = new SqlConnection(connString); SqlCommand cmd = conn2.CreateCommand(); try { cmd.CommandText = "dbo.getColumnNames"; cmd.CommandType = CommandType.StoredProcedure; cmd.Connection = conn2; SqlParameter parm = new SqlParameter("@TableName", SqlDbType.VarChar); parm.Value = TableName; parm.Direction = ParameterDirection.Input; cmd.Parameters.Add(parm); conn2.Open(); dr = cmd.ExecuteReader(); } catch (Exception ex) { errorMsg = ex.Message; } And when I examine the errorMsg it says the following: " at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)\r\n at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)\r\n at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)\r\n at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)\r\n at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()\r\n at System.Data.SqlClient.SqlDataReader.get_MetaData()\r\n at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)\r\n at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)\r\n at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)\r\n at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)\r\n at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)\r\n at System.Data.SqlClient.SqlCommand.ExecuteReader()\r\n at ADONET_namespace.ADONET_methods.DisplayTableColumns(String tt) in C:\Documents and Settings\Admin\My Documents\Visual Studio 2008\Projects\AddFileToSQL\AddFileToSQL\ADONET methods.cs:line 35" Where line 35 is dr = cmd.ExecuteReader();

    Read the article

  • SQL error C# - Parameter already defined

    - by jakesankey
    Hey there. I have a c# application that parses txt files and imports the data from them into a sql db. I was using sqlite and am now working on porting it to sql server. It was working fine with sqlite but now with sql i am getting an error when it is processing the files. It added the first row of data to the db and then says "parameter @PartNumber has already been declared. Variable names must be unique within a batch or stored procedure". Here is my whole code and SQL table layout ... the error comes at the last insertCommand.ExecuteNonQuery() instance at the end of the code... SQL TABLE: CREATE TABLE Import ( RowId int PRIMARY KEY IDENTITY, PartNumber text, CMMNumber text, Date text, FeatType text, FeatName text, Value text, Actual text, Nominal text, Dev text, TolMin text, TolPlus text, OutOfTol text, FileName text ); CODE: using System; using System.Data; using System.Data.SQLite; using System.IO; using System.Text.RegularExpressions; using System.Threading; using System.Collections.Generic; using System.Linq; using System.Data.SqlClient; namespace JohnDeereCMMDataParser { internal class Program { public static List<string> GetImportedFileList() { List<string> ImportedFiles = new List<string>(); using (SqlConnection connect = new SqlConnection(@"Server=FRXSQLDEV;Database=RX_CMMData;Integrated Security=YES")) { connect.Open(); using (SqlCommand fmd = connect.CreateCommand()) { fmd.CommandText = @"IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'RX_CMMData' AND TABLE_NAME = 'Import')) BEGIN SELECT DISTINCT FileName FROM Import; END"; fmd.CommandType = CommandType.Text; SqlDataReader r = fmd.ExecuteReader(); while (r.Read()) { ImportedFiles.Add(Convert.ToString(r["FileName"])); } } } return ImportedFiles; } private static void Main(string[] args) { Console.Title = "John Deere CMM Data Parser"; Console.WriteLine("Preparing CMM Data Parser... done"); Console.WriteLine("Scanning for new CMM data... done"); Console.ForegroundColor = ConsoleColor.Gray; using (SqlConnection con = new SqlConnection(@"Server=FRXSQLDEV;Database=RX_CMMData;Integrated Security=YES")) { con.Open(); using (SqlCommand insertCommand = con.CreateCommand()) { SqlCommand cmdd = con.CreateCommand(); string[] files = Directory.GetFiles(@"C:\Documents and Settings\js91162\Desktop\", "R303717*.txt*", SearchOption.AllDirectories); List<string> ImportedFiles = GetImportedFileList(); foreach (string file in files.Except(ImportedFiles)) { string FileNameExt1 = Path.GetFileName(file); cmdd.CommandText = @" IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'RX_CMMData' AND TABLE_NAME = 'Import')) BEGIN SELECT COUNT(*) FROM Import WHERE FileName = @FileExt; END"; cmdd.Parameters.Add(new SqlParameter("@FileExt", FileNameExt1)); int count = Convert.ToInt32(cmdd.ExecuteScalar()); con.Close(); con.Open(); if (count == 0) { Console.WriteLine("Parsing CMM data for SQL database... Please wait."); insertCommand.CommandText = @" INSERT INTO Import (FeatType, FeatName, Value, Actual, Nominal, Dev, TolMin, TolPlus, OutOfTol, PartNumber, CMMNumber, Date, FileName) VALUES (@FeatType, @FeatName, @Value, @Actual, @Nominal, @Dev, @TolMin, @TolPlus, @OutOfTol, @PartNumber, @CMMNumber, @Date, @FileName);"; insertCommand.Parameters.Add(new SqlParameter("@FeatType", DbType.Decimal)); insertCommand.Parameters.Add(new SqlParameter("@FeatName", DbType.Decimal)); insertCommand.Parameters.Add(new SqlParameter("@Value", DbType.Decimal)); insertCommand.Parameters.Add(new SqlParameter("@Actual", DbType.Decimal)); insertCommand.Parameters.Add(new SqlParameter("@Nominal", DbType.Decimal)); insertCommand.Parameters.Add(new SqlParameter("@Dev", DbType.Decimal)); insertCommand.Parameters.Add(new SqlParameter("@TolMin", DbType.Decimal)); insertCommand.Parameters.Add(new SqlParameter("@TolPlus", DbType.Decimal)); insertCommand.Parameters.Add(new SqlParameter("@OutOfTol", DbType.Decimal)); string FileNameExt = Path.GetFullPath(file); string RNumber = Path.GetFileNameWithoutExtension(file); string RNumberE = RNumber.Split('_')[0]; string RNumberD = RNumber.Split('_')[1]; string RNumberDate = RNumber.Split('_')[2]; DateTime dateTime = DateTime.ParseExact(RNumberDate, "yyyyMMdd", Thread.CurrentThread.CurrentCulture); string cmmDate = dateTime.ToString("dd-MMM-yyyy"); string[] lines = File.ReadAllLines(file); bool parse = false; foreach (string tmpLine in lines) { string line = tmpLine.Trim(); if (!parse && line.StartsWith("Feat. Type,")) { parse = true; continue; } if (!parse || string.IsNullOrEmpty(line)) { continue; } Console.WriteLine(tmpLine); foreach (SqlParameter parameter in insertCommand.Parameters) { parameter.Value = null; } string[] values = line.Split(new[] { ',' }); for (int i = 0; i < values.Length - 1; i++) { SqlParameter param = insertCommand.Parameters[i]; if (param.DbType == DbType.Decimal) { decimal value; param.Value = decimal.TryParse(values[i], out value) ? value : 0; } else { param.Value = values[i]; } } insertCommand.Parameters.Add(new SqlParameter("@PartNumber", RNumberE)); insertCommand.Parameters.Add(new SqlParameter("@CMMNumber", RNumberD)); insertCommand.Parameters.Add(new SqlParameter("@Date", cmmDate)); insertCommand.Parameters.Add(new SqlParameter("@FileName", FileNameExt)); // insertCommand.ExecuteNonQuery(); } } } Console.WriteLine("CMM data successfully imported to SQL database..."); } con.Close(); } } } }

    Read the article

< Previous Page | 2 3 4 5 6 7 8 9 10 11 12 13  | Next Page >