No new tables in database after executed query - Database Discussion Boards

Hi all. I am busy writing a program, in Microsoft Visual Web Developer, and have a database was several tables. The one I am focusing on now is the ACCOUNT table. This table contains username, password and email column.

I want to compare a username entered into a textbox with the existing usernames in the ACCOUNT table, so that I can create a message saying that the entered user already exists.

I know how to do this in C# with if statements, but am really unsure with SQL, since not sure how to use it with SELECT.Thanks

Member 9912091 wrote:
I want to compare a username entered into a textbox with the existing usernames in the ACCOUNT table

SQL

SELECT 1
  FROM Account
 WHERE Username = @Username

Returns 1 of the username exists Smile | :)

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Thanks!

Yeah, what Eddy said. But, provided you have a unique index on the column (and you should), it may be more efficient simply to try the INSERT and catch the exception -- you have to do that anyway.

modified 11-May-13 13:36pm.

Instead of using 1 in Eddys query use count(*), this will tell you how many records match the name.

You should study database design as you may run into some of the basic errors made by newbies. Primary keys and unique constraints.

Never underestimate the power of human stupidity
RAH

Hi All,
If any one tell me by which i can transfer table from one sql server database to another sql server database.

See if it helps: Copy Table With Data From One Database to Another in SQL Server 2012[^]

Sandeep Mewara
Microsoft ASP.NET MVP 2012 & 2013

[My Blog]: Sandeep Mewara's Tech Journal!
[My Latest Article]: HTML5 Quick Start Web Application

Hi all. I am using Microsoft SQL Server 2008 Management Studio. I have been trying to create a database (containing tables etc) from a query file (Delivery.sql).

In the Object explorer I right-clicked Database folder and created the "Delivery database". I then clicked New Query and pasted the contents of Delivery.sql (from notepad) into the new query.

Now when I execute this code, it says it has completed successfully, but in the Object Explorer the "Delivery database" does not contain any tables Sigh | :sigh:

. I am confused and am in need of help . Thanks.

Here is the image of the object explorer: http://imageshack.us/a/img694/7558/sqlobjectexplorerp.png[^]

Here is the code of the query file (Delivery.sql):

SQL

USE [Delivery database]
GO

/****** Object:  Database [Delivery]    Script Date: 05/09/2013 19:23:43 ******/
CREATE DATABASE [Delivery] ON  PRIMARY 
( NAME = N'Delivery', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\Delivery.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'Delivery_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\Delivery_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

ALTER DATABASE [Delivery] SET COMPATIBILITY_LEVEL = 100
GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [Delivery database].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO

ALTER DATABASE [Delivery] SET ANSI_NULL_DEFAULT OFF 
GO

ALTER DATABASE [Delivery] SET ANSI_NULLS OFF 
GO

ALTER DATABASE [Delivery] SET ANSI_PADDING OFF 
GO

ALTER DATABASE [Delivery] SET ANSI_WARNINGS OFF 
GO

ALTER DATABASE [Delivery] SET ARITHABORT OFF 
GO

ALTER DATABASE [Delivery] SET AUTO_CLOSE OFF 
GO

ALTER DATABASE [Delivery] SET AUTO_CREATE_STATISTICS ON 
GO

ALTER DATABASE [Delivery] SET AUTO_SHRINK OFF 
GO

ALTER DATABASE [Delivery] SET AUTO_UPDATE_STATISTICS ON 
GO

ALTER DATABASE [Delivery] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO

ALTER DATABASE [Delivery] SET CURSOR_DEFAULT  GLOBAL 
GO

ALTER DATABASE [Delivery] SET CONCAT_NULL_YIELDS_NULL OFF 
GO

ALTER DATABASE [Delivery] SET NUMERIC_ROUNDABORT OFF 
GO

ALTER DATABASE [Delivery] SET QUOTED_IDENTIFIER OFF 
GO

ALTER DATABASE [Delivery] SET RECURSIVE_TRIGGERS OFF 
GO

ALTER DATABASE [Delivery] SET  DISABLE_BROKER 
GO

ALTER DATABASE [Delivery] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO

ALTER DATABASE [Delivery] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO

ALTER DATABASE [Delivery] SET TRUSTWORTHY OFF 
GO

ALTER DATABASE [Delivery] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO

ALTER DATABASE [Delivery] SET PARAMETERIZATION SIMPLE 
GO

ALTER DATABASE [Delivery] SET READ_COMMITTED_SNAPSHOT OFF 
GO

ALTER DATABASE [Delivery] SET HONOR_BROKER_PRIORITY OFF 
GO

ALTER DATABASE [Delivery] SET  READ_WRITE 
GO

ALTER DATABASE [Delivery] SET RECOVERY SIMPLE 
GO

ALTER DATABASE [Delivery] SET  MULTI_USER 
GO

ALTER DATABASE [Delivery] SET PAGE_VERIFY CHECKSUM  
GO

ALTER DATABASE [Delivery] SET DB_CHAINING OFF 
GO

Did you refresh the Object Explorer after creation?

Personal Blog: A Software Programmer
Twitter: JammoD

Yeah it didnt work. However I realised that this script didnt have any code to create tables so I used a different script with "CREATE TABLE" code in the query and it worked! thanks

Hi there, I just want to get some opinions on which tool to use to diagram an ER Picture of a DB model having approx 900 tables, but ....

I have tried a few (Erwin, Aqua Data Studio, Powerdesigner and a few more - even tried Visio). All have problems because I just have the tables - no modelled relations (a pretty bad model but that's the fact). A few PK's almost no FK's. All this is in the code layer or even in the head of the developer only. I want to put all this together to graficaly illustrate our data structure.

What I need to get this job done is
- reverse engineer to have a starting Point (Oracle 11 and MSSQL2008) to have all tables in this tool (on a hidden layer if possible)
- Select some tables and copy them (link) to a new layer to switch visibility depending on the development task that i want to give to a developer
- Display compact (square with table Name, and PKs only). Hide all other columns
- a good way to arrange everything on Screen to avoid crossing Relations and to nicely arrange every table and relation
- toggle visibility of a layer
- a certain table appears on more than one layer depending on the development tasks
- something that makes it all "nice" to present to the Boss.
- crow feet Notation

I more search a documentation type of drawing tool rather than a physical modelling. Some of the tested tools started with creating "alter table add column" scripts that immediately change my DB. This is something I dont need.

Any idea out there?
Any tools avail that accomplish this task?

Thanks a lot in advance
Ole

I'm using Oracle SQL Developer Data Modeler[^]

ole.Grossklaus@gmx.de wrote:
- reverse engineer to have a starting Point (Oracle 11 and MSSQL2008) to have all tables in this tool (on a hidden layer if possible

Supports Oracle (duh), MSSQL and DB2.

ole.Grossklaus@gmx.de wrote:
- Select some tables and copy them (link) to a new layer to switch visibility depending on the development task that i want to give to a developer

It's tab based, you can create a new view with a subset of existing tables.

ole.Grossklaus@gmx.de wrote:
- Display compact (square with table Name, and PKs only). Hide all other columns

Yes

ole.Grossklaus@gmx.de wrote:
- a good way to arrange everything on Screen to avoid crossing Relations and to nicely arrange every table and relation

Auto arrange sucks big donkey balls. But it does in all tools in my opinion. So it's up to you to do the work I'm afraid.

ole.Grossklaus@gmx.de wrote:
- toggle visibility of a layer

It's tab based, and you can have one or more windows and move the tabs between them.

ole.Grossklaus@gmx.de wrote:
- a certain table appears on more than one layer depending on the development tasks

A table can appear in many windows/views, not sure if this is what you mean.

ole.Grossklaus@gmx.de wrote:
- something that makes it all "nice" to present to the Boss.

Use Powerpoint. Smile | :)

ole.Grossklaus@gmx.de wrote:
- crow feet Notation

Barker notation - check

Be excellent to each other. And... PARTY ON, DUDES!
Abraham Lincoln

Microsoft SQL Server 2012 introduces 14 new built-in functions. These new functions are:
Conversion functions

> PARSE
> TRY_CONVERT
> TRY_PARSE

Date and time functions

> DATEFROMPARTS
> DATETIME2FROMPARTS
> DATETIMEFROMPARTS
>DATETIMEOFFSETFROMPARTS
> EOMONTH
> SMALLDATETIMEFROMPARTS
> TIMEFROMPARTS

Logical functions

> CHOOSE
> IIF

String functions

> CONCAT
> FORMAT

Please read[^].

Use the best guess

Karthik_J, Coimbatore wrote:
Microsoft SQL Server 2012 introduces 14 new built-in functions.

And you posted this to demonstrate that you can read documentation maybe?

I am using an Access 2000 database file. When parsing user input that has SQL reserved characters, I put [] around any reserved characters I find. This works fine, except when dealing with %s.

I have a table named "Foo", with two rows:
Type as text, SubType as text, with two rows of values:

"Sales Tax (%)", "NY"
"Sales Tax (%)", PA"

When I execute the following:

SELECT * From Foo WHERE Type LIKE '%Sales Tax ([%])%'

I get the rows returned I expect, where the row has a value in the Type column of "Sales Tax (%)"

But when I execute:

SELECT * From Foo WHERE Type = 'Sales Tax ([%])'

No rows are returned

Even if I omit the parenthesis around the % sign, it does not find the rows.

Is there something odd I am doing wrong, perhaps specific to Access SQL?

<edit>move on, nothing to see here except the proofs of to little sleep.</edit>

Access isn't SQLServer. You're not supposed to use brackets either.
I would strongly recommend renaming the offending fields.

More info here[^].

I especially like this part: "However, if you do use the special characters, you may experience unexpected errors."

Be excellent to each other. And... PARTY ON, DUDES!
Abraham Lincoln

modified 10-May-13 10:49am.

I am not sure I understand. The % character does not appear in any field names, or table names. The data type of both fields, named "Type" and "SubType" are text, and that text data can contain a % character as part of whatever text the user chooses to enter into the field, along with other special characters.

(I have an application where I prompt the user to enter a text value for the Type and SubType fields, and I save that text into those fields as a new row. The problem I have is that the user can enter a % as part of their user-entered text, and I need to be able to search on text field data for a % character they may have entered; I can put brackets around all other special characters when building my query and they are found fine, but the % just doesn't work for some reason when used in an = query.

My bad, I think my brain went to sleep before the rest of me did.

Be excellent to each other. And... PARTY ON, DUDES!
Abraham Lincoln

JohnBlocker wrote:
Even if I omit the parenthesis around the % sign, it does not find the rows.

So this:

SQL

SELECT * From Foo WHERE Type = 'Sales Tax (%)'

returns nothing?

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

JohnBlocker wrote:
When parsing user input that has SQL reserved characters

Parsing how? What application/language are you doing this in?

This could be an odd question, but, you tried

Quote:
SELECT * From Foo WHERE Type = 'Sales Tax ([%])'

Cause, when your using '=' command, means that is HAS to be egual.

Hi everyone
I have a question,is it correct
connected data objects consist of connection,command,reader and dataadapter(why we need to DataAdapter in a connected model?),transaction,....
disconnected data objects consist of dataset,dataTable,datarow,dataadapter

so we have two main models connected and disconnected.
what is the rule of DataAdapter in connected model? why do we need to use of a dataadpter to fill a dataset like this in a connected model?

.....
sqlDataAdapter = new SqlDataAdapter(cmdString, sqlConnection);
sqlDataAdapter.SelectCommand = sqlCommand;
SqlDataAdapter sqlDataAdapter;
sqlDataSet = new DataSet();
sqlDataSet.Clear();
int intValue = sqlDataAdapter.Fill(sqlDataSet);

is it right we are shifting of connected model to disconnected model,if yes why?

I would not include DataAdapter in the "connected" category.

The "connected" paradigm is still appropriate for many applications, but less so with distributed applications.

DataAdapters cause me nothing but trouble; I don't use them. Dead | X|

Is it correct we can fill a dataset with connected objects(connection,command,dataadapter) and without them(with data source window)? if yes so we are using of the connected objects to fill a disconnected object(dataset),right?