Search Results

Search found 10101 results on 405 pages for 'temporary tables'.

Page 347/405 | < Previous Page | 343 344 345 346 347 348 349 350 351 352 353 354  | Next Page >

• SQL, MVC, Entity Framework

  - by Anthony

  Hi Im using the above technologies and have ran into what I presume is a design issue I have made. I have an Artwork table in my DB and have been able to add art (I now think of these as Digital Products) to a shopping cart + CartLine table fine. The system I have that adds art to galleries and user accounts etc works fine. Now the client wants to sell T-shirts, Mugs and Pens etc, 'HardwareProducts' so I have created a 'HardwareProducts' table. Now I have two different product types in two tables. I use GUID's as the PK's in both the HardwareProducts table and Artwork table. When a customer adds an item to their cart I store the GUID in the ProductID column in the CartItems table. The issue is the database will not know which table to reference when I bring the LineItem object up through my ORM to the front end. In OOP I can see how you would have a base class of Product, and then a DigitalProduct class and HardwareProduct class drived from it, but how do you model this in SQL Server and the Entity Framework please, or is there another way?

  Read the article

• Best way to handle Many-to-Many relationships in PHP MySQL

  - by Jayrox

  I am looking for the best way to handle a database of many-to-many relationships in PHP and MySQL. Right now I have 2 tables: Users (id, user_name, first_name, last_name) Connections (id_1, id_2) In the User table id is auto incremented on add and user_name is unique, but can be changed. Unfortunately, I don't have control over the user_name and its ability to be changed, but I must account for it. The Connections table is obviously, user1 and user2's id. The connection table needs to account for these possible relations: user1 --> user2 (user 1 friends with user 2 but not user2 friends with user1) user2 --> user1 (user 2 friends with user 1 but not user1 friends with user2) user1 <--> user2 (user 1 and user 2 mutually friends) user1 <-!-> user2 (user 1 and user 2 not friends) That part is not the problem, The problem I am having with is keeping these relations unique when and if they change in batches. Possible solution 1: delete all of user 1's relations and readd them with the updated list. I think this might be too slow for my needs. Solution 2? Anyone else encounter this problem? How should I best handle this? update: distinguishing relationships: i handle relationships like this: user1, user2 user1, user3 user2, user1 in that example the following is true: user1 follows user2 and user3 user2 only follows user1 but doesn't follow user3 user3 doesn't follow either user1 or user2

  Read the article

• Asp.net renders string with wrong encoding, but PHP doesn't (MySQL)

  - by citronas

  I took over some old php application with MySQL as database. Inside the database, there are tables including content with localized strings (therefore containing special chars) Currently there is a PHP application accessing that database. My job is to create an ASP.net (C# codebehind) application that accesses that strings as well. That works, as far as encoding goes. If I try to access these strings, I do get a kind of encoding problem, like 'Ändern' and 'Prüfzeichen', but only in the ASP.net application. The PHP app sets utf-8 as charset and the strings are perfectly rendered. In the ASP.net application it's gibberish, regardless of the page encoding. In the MySQL database, the charset for the specified table 'translations' is set to 'latin --cp1252 West European' and collation to 'latin_swedish_ci'. I can't seem to figure out what PHP apparently does, and ASP.net does not. I traced the php code and could not find any sign of special encoding while getting a string from the database. The question is, how can I ensure correct encoding inside the ASP.net application without modifying the database, because big changes at the php code are not possible? Does anybody have a clue?

  Read the article

• How to publish internal data to the internet - as simple as possible

  - by mlarsen

  We have a .net 2-tier application where a desktop program is talking to a database. We support MS SQL Server 2000, 2005, 2008 and Oracle 9, 10 and 11. The application is sold, not as shrink-wrap, but pretty close. It is quite important for us that the installation and configuration is as easy as possible as installation instructions are usually supplied in written form to the customers internal IT-department. Our application is usually not seen as mission critical for the IT-department, so we need to keep their work down to a minimum. Now we are starting to get wishes for a web application build on top of the same data. The web application will be hosted by us and delivered as a SaaS application. Now the challenge is how to move data back and forth between the web application and the customers internal database. as I see it we have some requirements: We must be ready to handle the situation where the customers database is not accessible from the DMZ. I guess the easiest solution is that all communication is initiated from inside the customers lan. As little firewall configuration as possible. The best is if we can run without any special configuration as long as outgoing traffic from the customers lan are not blocked. If we need something changed in the firewall, we must be able to document that the change is secure. It doesn't have to be real time. Moving data in batches every ten minutes or so is OK. Data moves both ways, but not the same tables, so we don't have to support merges. It would be nice if we don't have to roll our own framework completely. Looking forward to hear your suggestions.

  Read the article

• Python/MySQL fails under Windows

  - by AP257

  I'm trying to get Python 2.6 to communicate with MySQL Server 5.1, under Windows XP, but I keep getting a strange error, "SystemError: NULL object passed to Py_BuildValue": >>> import MySQLdb as mysql >>> db = mysql.connect(user = "root", passwd="whatever", db="mysql", host="localh ost") >>> cu = db.cursor() >>> cu.execute("show tables") Traceback (most recent call last): File "<stdin>", line 1, in <module> File "C:\dirr\lib\site-packages\MySQLdb\cursors.py", line 173, in execu te self.errorhandler(self, exc, value) File "C:\ dirr\lib\site-packages\MySQLdb\connections.py", line 36, in de faulterrorhandler raise errorclass, errorvalue SystemError: NULL object passed to Py_BuildValue I thought it might be a character set problem, but I've tried setting and setting MySQL as UTF-8, and it hasn't made a difference. I guess there must be a problem with python-mysql. Can anyone help? UPDATE OK, python-mysql under windows is a bit of a nightmare, particularly with Python 2.6 it seems. Rather than installing python-mysql with pip, use this installer instead. That fixed it.

  Read the article

• Help with SQL Query

  - by djfrear

  With regards to the following statement: Select * From explorer.booking_record booking_record_ Inner Join explorer.client client_ On booking_record_.labelno = client_.labelno Inner Join explorer.tour_hotel tour_hotel_ On tour_hotel_.tourcode = booking_record_.tourrefcode Inner Join explorer.hotelrecord hotelrecord_ On tour_hotel_.hotelcode = hotelrecord_.hotelref Where booking_record_.bookingdate Not Like '0000-00-00' And booking_record_.tourdeparturedate Not Like '0000-00-00' And hotelrecord_.hotelgroup = "LPL" And Year(booking_record_.tourdeparturedate) Between Year(AddDate(Now(), Interval -5 Year)) And Year(Now()) My MySQL skills are certainly not up to scratch, the actual result set I wish to find is "a customer who has been to 5 or more LPL hotels in the past 5 years". So far I havent got as far as dealing with the count as I'm getting a huge number of results with some 250+ per customer. I assume this is to do with the way I'm joining tables. Schema wise the booking_record table contains a tour reference code, which links to tour_hotel which then contains a hotelcode which links to hotelrecord. This hotelrecord table contains the hotelgroup. The client table is joined to the booking_record via a booking reference and a client may have many bookings. If anyone could suggest a way for me to do this I'd be very grateful and hopefully learn enough to do it myself next time! I've been scratching my head over this one for a few hours now! Customers may have many bookings within booking_record Daniel.

  Read the article

• Variable amount of columns returned in mysqli prepared statement

  - by manyxcxi

  I have a situation where a dynamic query is being generated that could select anywhere from 1 to over 300 different columns across multiple tables. It currently works fine just doing a query, however the issue I'm running into in using a prepared statement is that I do not know how to handle the fact that I don't know how many columns I will be asking for each time and therefor don't know how to process the results. The reason I believe a bind statement will help is because once this query is run once, it will most likely (though not always) be run again with the exact same parameters. Currently I have something like this: $rows = array(); $this->statement = $this->db->prepare($query); $this->statement->bind_param('i',$id); $this->statement->execute(); $this->statement->bind_result($result); while($this->statement->fetch()) { $rows[] = $result; } I know this doesn't work as I want it to, my question is how do I get the data back out of the query. Is it possible to bring the columns back in an associative array by column name, like a standard mysqli query?

  Read the article

• Spring MVC + Hibernate encoding problem

  - by Bar

  I work on Spring MVC + Hibernate application, use MySQL (ver. 5.0.51a) with the InnoDB engine. The problem appears when I am sending a form with cyrillic characters. As the result, database contains senseless chars in unknown encoding. All the JSP pages, database (+ tables and fields) created using UTF-8. Hibernate config also contains property which sets encoding to UTF-8. I had solved this by creating filter which encodes request content with UTF-8. Exemplary code: … encoding = "UTF-8"; request.setCharacterEncoding(encoding); chain.doFilter(request, response); … But it visibly slows down the app. The interesting thing is that executing insert query directly from the app (i.e. running from Eclipse as Java Application) works perfect. Any suggestions are welcome. TIA, Michael.

  Read the article

• Problem with WHERE columnName = Data in MySQL query in C#

  - by Ryan Sullivan

  I have a C# webservice on a Windows Server that I am interfacing with on a linux server with PHP. The PHP grabs information from the database and then the page offers a "more information" button which then calls the webservice and passes in the name field of the record as a parameter. So i am using a WHERE statement in my query so I only pull the extra fields for that record. I am getting the error: System.Data.SqlClient.SqlException:Invalid column name '42' Where 42 is the value from the name field from the database. my query is string selectStr = "SELECT name, castNotes, triviaNotes FROM tableName WHERE name =\"" + show + "\""; I do not know if it is a problem with my query or something is wrong with the database, but here is the rest of my code for reference. NOTE: this all works perfectly when I grab all of the records, but I only want to grab the record that I ask my webservice for. public class ktvService : System.Web.Services.WebService { [WebMethod] public string moreInfo(string show) { string connectionStr = "MyConnectionString"; string selectStr = "SELECT name, castNotes, triviaNotes FROM tableName WHERE name =\"" + show + "\""; SqlConnection conn = new SqlConnection(connectionStr); SqlDataAdapter da = new SqlDataAdapter(selectStr, conn); DataSet ds = new DataSet(); da.Fill(ds, "tableName"); DataTable dt = ds.Tables["tableName"]; DataRow theShow = dt.Rows[0]; string response = "Name: " + theShow["name"].ToString() + "Cast: " + theShow["castNotes"].ToString() + " Trivia: " + theShow["triviaNotes"].ToString(); return response; } }

  Read the article

• Selecting a whole database over an individual table to output to file

  - by Daniel Wrigley

  :::::::: EDIT :::::::: New code for people to have a look at, one question I have with this is where do I set were the *.gz file is saved? $backupFile = $dbname . date("Y-m-d-H-i-s") . '.gz'; $command = "mysqldump --opt -h $dbhost -u $dbuser -p $dbpass $dbname | gzip > $backupFile"; system($command); Also why the hell can you not reply yo your own post with answering it? :( :::::::: EDIT :::::::: Ok Im having trouble finding out how to select a full database for backup as an *.sql file rather than only an individual table. On the localhost I have several databases with one named "foo" and it is that which I want to backup and not any of the individual tables inside the database "foo". The code to connect to the database; //Database Information $dbhost = "localhost"; $dbname = "foo"; $dbuser = "bar"; $dbpass = "rulz"; //Connect to database mysql_connect ($dbhost, $dbuser, $dbpass) or die("Could not connect: ".mysql_error()); mysql_select_db($dbname) or die(mysql_error()); The code to backup the database; // Grab the time to know when this post was submitted $time = date('Y-m-d-H-i-s'); $tableName = 'foo'; $backupFile = '/sql/backup/'. $time .'.sql'; $query = "SELECT * INTO OUTFILE '". $backupFile ."' FROM ". $tableName .""; $result = mysql_query($query)or die("Database query died: " . mysql_error()); My brain is hurting near to the end of the day so no doubts i've missed something out very obvious. Thanks in advance to anyone helping me out.

  Read the article

• Is it possible to use SqlGeography with Linq to Sql?

  - by cofiem

  I've been having quite a few problems trying to use Microsoft.SqlServer.Types.SqlGeography. I know full well that support for this in Ling to Sql is not great. I've tried numerous ways, beginning with what would the expected way (Database type of geography, CLR type of SqlGeography). This produces the NotSupportedException, which is widely discussed via blogs. I've then gone down the path of treating the geography column as a varbinary(max), as geography is a UDT stored as binary. This seems to work fine (with some binary reading and writing extension methods). However, I'm now running into a rather obscure issue, which does not seem to have happened to many other people. System.InvalidCastException: Unable to cast object of type 'Microsoft.SqlServer.Types.SqlGeography' to type 'System.Byte[]'. This error is thrown from an ObjectMaterializer when iterating through a query. It seems to only occur when the tables containing geography columns are included in a query implicitly (ie. using the EntityRef<> properties to do joins). System.Data.Linq.SqlClient.ObjectReaderCompiler.ObjectReader`2.MoveNext() My question: If I'm retrieving the geography column as varbinary(max), I might expect the reverse error: can't cast byte[] to SqlGeography. That I would understand. This I don't. I do have some properies on the partial LINQ to SQL classes that hide the binary conversion... could those be the issue? Any help appreciated, and I know there's probably not enough information.

  Read the article

• Changing character encoding in MySQL, PHP scripts, HTML

  - by Sandman

  So, I have built on this system for quite some time, and it is currently outputting Latin1 (ISO-8859-1) to the web browser, and this is the components: MySQL - all data is stored with the Latin1 character set PHP - All PHP text files are stored on disk with Latin1 encoding HTML - The output has the http-equiv="content-type" content="text/html; charset=iso-8859-1" meta tag So, I'm trying to understand how the encoding of the different parts come into play in my workflow. If I open a PHP script and change its encoding within the text editor to UTF-8 and save it back to disk and reload the web browser, the text is all messed up - unless the text comes from the DB. If I change the encoding of the DB to UTF-8 and keep the PHP files in latin1 I have to use utf8_decode() for the data to display correctly. And if I change the HTML code the browser will read it incorrectly. So yeah, I realise that if I want to "upgrade" to UTF8, I have to update all three parts of this setup for it to work correctly, but since it's a huge system with some 180k lines of PHP code and millions of posts in a lot of databases/tables, I don't want to start something like this without understanding everything correctly. What haven't I thought about? What could mess this up beyond fixing? What are the procedures for changing the encoding of an entire MySQL installation and what's the easiest way to change the encoding of hundreds or thousands of PHP files on disk? The META tag is luckily added dynamically, so I'll change that in one place only :) Let me hear about your experiences with this.

  Read the article

• PHP / Zend Framework: Force prepend table name to column name in result array?

  - by Brian Lacy

  I am using Zend_Db_Select currently to retrieve hierarchical data from several joined tables. I need to be able to convert this easily into an array. Short of using a switch statement and listing out all the columns individually in order to sort the data, my thought was that if I could get the table names auto-prepended to the keys in the result array, that would solve my problem. So considering the following (assembled) SQL: SELECT user.*, contact.* FROM user INNER JOIN contact ON contact.user_id = user.user_id I would normally get a result array like this: [username] => 'bob', [contact_id] => 5, [user_id] => 2, [firstname] => 'bob', [lastname] => 'larsen' But instead I want this: [user.user_id] => 2, [user.username] => 'bob', [contact.contact_id] => 5, [contact.firstname] => 'bob', [contact.lastname] => 'larsen' Does anyone have an idea how to achieve this? Thanks!

  Read the article

• Mysql return value as 0 in the fetch result.

  - by Karthik

  I have this two tables, -- -- Table structure for table `t1` -- CREATE TABLE `t1` ( `pid` varchar(20) collate latin1_general_ci NOT NULL, `pname` varchar(20) collate latin1_general_ci NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; -- -- Dumping data for table `t1` -- INSERT INTO `t1` VALUES ('p1', 'pro1'); INSERT INTO `t1` VALUES ('p2', 'pro2'); -- -------------------------------------------------------- -- -- Table structure for table `t2` -- CREATE TABLE `t2` ( `pid` varchar(20) collate latin1_general_ci NOT NULL, `year` int(6) NOT NULL, `price` int(3) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; -- -- Dumping data for table `t2` -- INSERT INTO `t2` VALUES ('p1', 2009, 50); INSERT INTO `t2` VALUES ('p1', 2010, 60); INSERT INTO `t2` VALUES ('p3', 2007, 200); INSERT INTO `t2` VALUES ('p4', 2008, 501); my query is, SELECT * FROM `t1` LEFT JOIN `t2` ON t1.pid = t2.pid Getting the result, pid pname pid year price p1 pro1 p1 2009 50 p1 pro1 p1 2010 60 p2 pro2 NULL NULL NULL My question is, i want to get the price value is 0 instead of NULL. How can i write the query to getting the price value is 0. Thanks in advance for help.

  Read the article

• symfony doctrine build-sql error

  - by user313571

  I have some big problems with symfony and doctrine at the beginning of a new project. I have created database diagram with mysql workbench, inserted the sql into phpmyadmin and then I've tried symfony doctrine:build-schema to generate the YAML schema. It generates a wrong schema (relations don't have on delete/on update) and after this I've tried symfony doctrine:build --sql and symfony doctrine:insert-sql The insert-sql statement generates error (can't create table ... failing query alter table add constraint ....), so I've decided to take a look over the generated sql and I've found out some differences between the sql generated from mysql workbench (which works perfect, including relations) and the sql generated by doctrine. I'll be short from now: I have to tables, EVENT and FORM and a 1 to n relation (each event may have multiple forms) so the correct constraint (generated with workbench) is ALTER TABLE `form` ADD CONSTRAINT `fk_form_event1` FOREIGN KEY (`event_id`) REFERENCES `event` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; doctrine generated statement is: ALTER TABLE event ADD CONSTRAINT event_id_form_event_id FOREIGN KEY (id) REFERENCES form(event_id); It's totally reversed and I am sure here is the error. What should I do? It's also correct like this?

  Read the article

• CSS Multiple multi-column divs

  - by accelerate

  I have a bunch of items (text, image, mixed content, etc) that I want to display. The user can define what row and what column that item appears in. For example, in row 1, there could be two items/columns, both images. In row two, there could be three items / columns, one with an image, two others as pure text. Oh, and the user may specify the width of any particular column/image/item. I have a solution that uses multiple tables that works. In essence, each row is a new table. This works for the most part. I'm wondering if I can use just divs? Now my CSS foo is lacking, and I tried to copy examples from the web, and I haven't been able to get it working. Right now I have something like this: `[for each row] [for each column] [content] ` But everything is overlapping each other. I've also tried using "position: relative", but things look even more borked. So can divs actually be used for multiple rows and different number of columns?

  Read the article

• Understanding MongoDB(and NoSQL in general) and How to make the best use of it

  - by Earlz

  Hello, I am beginning to think that my next project I am wanting to do would work better with a NoSQL solution. The project would either involve a ton of 2-column tables or a ton of dynamic queries with dynamically generated columns in a traditional SQL database. So I feel a NoSQL database would be much cleaner. I'm looking at MongoDB and it looks pretty promising. Anyway, I'm attempting to make sense of it all. Also, I will be using MongoMapper in Ruby. Anyway though, I'm confused as to how to layout things in such a freeform database. I've read http://stackoverflow.com/questions/2170152/nosql-best-practices and the answer there says that normalization is usually bad in a NoSQL DB. So how would be the best way of laying out say a simple blog with users, posts, and comments? My natural thought was to have 3 collections for each and then link them by a unique ID. But this apparently is wrong? So, what are some of the ways to lay out such a thing? My concern with the answer given in the other question is what if the author's name changed. You'd have to go through updating a ton of posts and comments. But is this an ok thing to do with NoSQL?

  Read the article

• I'm looking for a way to evaluate reading rate in several languages

  - by i30817

  I have a software that is page oriented instead of scrollbar oriented so i can easily count the words, but i'd like a way to filter outliers and some default value for the text language (that is known). The goal is from the remaining text to calculate the remaining time. I'm not sure what is the best unit to use. WPM (words per minute) from here seems very fuzzy and human oriented. Besides i don't know how many "words" remain in the text. http://www.sfsu.edu/~testing/CalReadRate.htm So i came up with this: The user is reading the text. The total text size in characters is known. His position in the text is known. So the remaining characters to read is also known. If a language has a median word length of say 5 chars, then if i had a WPM speed for the user, i could calculate the remaining time. 3 things are needed for this: 1) A table of the median word length of the language. 2) A table of the median WPM of a median user per language. 3) Update the WPM to fit the user as data becomes available, filtering outliers. However i can't find these tables. And i'm not sure how precise it is assuming median word length.

  Read the article

• MySQL LEFT JOIN, INNER JOIN etc, complicated query, PHP + MySQL for a forum

  - by Sven Eriksson

  So I've got a little forum I'm trying to get data for, there are 4 tables, forum, forum_posts, forum_threads and users. What i'm trying to do is to get the latest post for each forum and giving the user a sneak peek of that post, i want to get the number of posts and number of threads in each forum aswell. Also, i want to do this in one query. So here's what i came up with: SELECT lfx_forum_posts.*, lfx_forum.*, COUNT(lfx_forum_posts.pid) as posts_count, lfx_users.username, lfx_users.uid, lfx_forum_threads.tid, lfx_forum_threads.parent_forum as t_parent, lfx_forum_threads.text as t_text, COUNT(lfx_forum_threads.tid) as thread_count FROM lfx_forum LEFT JOIN (lfx_forum_threads INNER JOIN (lfx_forum_posts INNER JOIN lfx_users ON lfx_users.uid = lfx_forum_posts.author) ON lfx_forum_threads.tid = lfx_forum_posts.parent_thread AND lfx_forum_posts.pid = (SELECT MAX(lfx_forum_posts.pid) FROM lfx_forum_posts WHERE lfx_forum_posts.parent_forum = lfx_forum.fid GROUP BY lfx_forum_posts.parent_forum) ) ON lfx_forum.fid = lfx_forum_posts.parent_forum GROUP BY lfx_forum.fid ORDER BY lfx_forum.fid ASC This get the latest post in each forum and gives me a sneakpeek of it, the problem is that lfx_forum_posts.pid = (SELECT MAX(lfx_forum_posts.pid) FROM lfx_forum_posts WHERE lfx_forum_posts.parent_forum = lfx_forum.fid GROUP BY lfx_forum_posts.parent_forum) Makes my COUNT(lfx_forum_posts.pid) go to one (aswell as the COUNT(lfx_forum_threads.tid) which isn't how i would like it to work. My question is: is there some somewhat easy way to make it show the correct number and at the same time fetch the correct post info (the latest one that is)? If something is unclear please tell and i'll try to explain my issue further, it's my first time posting something here.

  Read the article

• Access User Meta Data on User Registration in Wordpress

  - by Shadi Almosri

  Hiya, I am attempting to carry out a few functions when a user registers on a wordpress site. I have created a module for this which carries out the following function: add_action( 'user_register', 'tml_new_user_registered' ); function tml_new_user_registered( $user_id ) { //wp_set_auth_cookie( $user_id, false, is_ssl() ); //wp_redirect( admin_url( 'profile.php' ) ); $user_info = get_userdata($user_id); $subscription_value = get_user_meta( $user_id, "subscribe_to_newsletter", TRUE); if($subscription_value == "Yes") { //include("Subscriber.Add.php"); } echo "<pre>: "; print_r($user_info); print_r($subscription_value); echo "</pre>"; exit; } But it seems that i am not able to access any user meta data as at the end of this stage none of it is stored. Any ideas how i execute a function once Wordpress has completed the whole registration process of adding meta data into the relevant tables too? I attempted to use this: add_filter('user_register ','tml_new_user_registered',99); But with no luck unfortunately. Thanks in advance!

  Read the article

• Recommend me an architecture for this Facebook application

  - by andybaird

  Firstly, this question is subjective. There is not a right answer for this question and it really depends on what works for you. I'm hoping to use this thread as a breeding ground for ideas. I hope this is acceptable in this medium. I'm working on building a Facebook app that will be replacing an already popular app that gets ~50k hits a day. The original app is using a very typical LAMP setup with help from some Zend libraries for database layer extraction. For the most part the app worked well, except to solve a lot of issues I ended up fragmenting tables to speed things up. As a result, I couldn't do a lot of things with the app that I wanted to (namely any processing using aggregate data that needed to be returned quickly) So I'm starting to design plans for the next version of this application, and I have a whole bunch of new and cool features that I know would choke my current setup. I'm looking for technological recommendations of data storage methods that scale well. The database does not necessarily need to be relational, simple key/value storage would suffice (although at present time I know little to nothing about KV stores) What's your recommendation? How would you tackle this? I'd like to take a completely free approach to this -- although I am most familiar and comfortable using PHP, I want to leave all technical options open.

  Read the article

• Getting table schema from a query

  - by Appu

  As per MSDN, SqlDataReader.GetSchemaTable returns column metadata for the query executed. I am wondering is there a similar method that will give table metadata for the given query? I mean what tables are involved and what aliases it has got. In my application, I get the query and I need to append the where clause programically. Using GetSchemaTable(), I can get the column metadata and the table it belongs to. But even though table has aliases, it still return the real table name. Is there a way to get the aliase name for that table? Following code shows getting the column metadata. const string connectionString = "your_connection_string"; string sql = "select c.id as s,c.firstname from contact as c"; using(SqlConnection connection = new SqlConnection(connectionString)) using(SqlCommand command = new SqlCommand(sql, connection)) { connection.Open(); SqlDataReader reader = command.ExecuteReader(CommandBehavior.KeyInfo); DataTable schema = reader.GetSchemaTable(); foreach (DataRow row in schema.Rows) { foreach (DataColumn column in schema.Columns) { Console.WriteLine(column.ColumnName + " = " + row[column]); } Console.WriteLine("----------------------------------------"); } Console.Read(); } This will give me details of columns correctly. But when I see BaseTableName for column Id, it is giving contact rather than the alias name c. Is there any way to get the table schema and aliases from a query like the above? Any help would be great!

  Read the article

• SQL select row into a string variable without knowing columns

  - by Brandi

  Hello, I am new to writing SQL and would greatly appreciate help on this problem. :) I am trying to select an entire row into a string, preferably separated by a space or a comma. I would like to accomplish this in a generic way, without having to know specifics about the columns in the tables. What I would love to do is this: DECLARE @MyStringVar NVARCHAR(MAX) = '' @MyStringVar = SELECT * FROM MyTable WHERE ID = @ID AS STRING But what I ended up doing was this: DECLARE @MyStringVar = '' DECLARE @SecificField1 INT DECLARE @SpecificField2 NVARCHAR(255) DECLARE @SpecificField3 NVARCHAR(1000) ... SELECT @SpecificField1 = Field1, @SpecificField2 = Field2, @SpecificField3 = Field3 FROM MyTable WHERE ID = @ID SELECT @StringBuilder = @StringBuilder + CONVERT(nvarchar(10), @Field1) + ' ' + @Field2 + ' ' + @Field3 Yuck. :( I have seen some people post stuff about the COALESCE function, but again, I haven't seen anyone use it without specific column names. Also, I was thinking, perhaps there is a way to use the column names dynamically getting them by: SELECT [COLUMN_NAME] FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'MyTable' It really doesn't seem like this should be so complicated. :( What I did works for now, but thanks ahead of time to anyone who can point me to a better solution. :) EDIT: Got it fixed, thanks to everyone who answered. :)

  Read the article

• A many-to-many relation joined disallows intellisense/lookup in joined table

  - by BerggreenDK

  I want to be able to select a product and retrieve all sub-parts(products) within it. My approach is to find the Product id and then retrieve the list of ProductParts with that as a parent and while fetching those, follow the key back to the Product child to get the name and details of each Part. I was hoping to use something similar to: part.linked_product_id.name I have two tables. One for [Product] and and a relation [ProductPart] that has two FK ID's to [Product] Table Product() { int ID; // (PRIMARY, NOT NULL) String Name; ... details removed for overview purpose... } Table ProductPart() { int Product_ID; // FK (linked with relation to Product/parent) int Part_Product_ID; // FK (linked with relation to Product/childen) ... details removed for overview purpose... } I have checked the SQL-diagram and it shows the two relations (both are one to many) and in my DBML they also looks right. Here is my LINQ to SQL snippet that doesnt work for me... wondering why my joins dont work as supposed. FaultySnippet() { ProductDataContext db = new ProductDataContext(); var list = ( from part in db.ProductParts join prod in db.Products on part.Part_Product_ID equals prod.ID where (part.Product_ID == product_ID) select new { Name = part.Part_Product_ID. ?? // <-- NO details from Joined table? ... rest of properties from ProductPart join... I hoped... } ); }

  Read the article

• Type or namespace could not be found

  - by Jason Shultz

  I'm using LINQ to SQL to connect my database to my home page. I created my datacontext (named businessModel.dbml) In it I have two tables named Category and Business. In home controller I reference the model and attempt to return to the view the table: var dataContext = new businessModelDataContext(); var business = from b in dataContext.Businesses select b; ViewData["WelcomeMessage"] = "Welcome to Jerome, Arizona!"; ViewData["MottoMessage"] = "Largest Ghost Town in America!"; return View(business); and in the view I have this: <%@ Import Namespace="WelcomeToJerome.Models" %> and <% foreach (business b in (IEnumerable)ViewData.Model) { %> <li><%= b.Title %></li> <% } %> Yet, in the view business is cursed with the red underline and say's that The type or namespace name 'business' could not be found (are you missing a using directive or an assembly reference?) What am I doing wrong? This has had me stumped all afternoon. link to all the code in pastebin: http://pastebin.com/es4RnS2q

  Read the article

< Previous Page | 343 344 345 346 347 348 349 350 351 352 353 354  | Next Page >