Create Table And Fields
Jan 13, 2007
I have maked this little code but dont know the way to insertt new table and fileds in database DATABASE01...
Private Sub Command1_Click()
Dim objConn As ADODB.Connection
Set objConn = New ADODB.Connection
objConn.ConnectionString = "driver={SQL Server};server=DVD377-14D9E48CSQLEXPRESS;uid=;pwd=;database=master"
objConn.Open
objConn.Execute "CREATE DATABASE DATABASE01"
''''''''''''''''''
'"CREATE table Mytable"
''''''''''''''''''
''''''''''''''''''
'"CREATE filed1" in text format
'"CREATE filed2" in text format
'"CREATE filed3" in text format
'"CREATE filed4" in date format(DD/MM/YYYY)
''''''''''''''''''
objConn.Close
End Sub
View 1 Replies
ADVERTISEMENT
Oct 24, 2006
I want to create a table automatically,and fields come from a csv file
any idea? TIA
View 1 Replies
View Related
Jun 28, 2006
i am Create a StoreProcedure That Search in All Tables And Filds by Keyword
and return one result????????
View 1 Replies
View Related
Feb 26, 2008
I need to create a table that only allows fields to be updated if the field value is NULL. Is there anyway to configure a table in this manner?
In the example below the record will be created with a serial number and verification ready flag set to true. Other processes down stream will update the remaining fields as needed but should not update fields that have already been initialized.
Table Definition
USE [DB_AUTOMATED_PACKAGING_SYSTEM]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TBL_LABEL_VERIFICATION](
[SerialNumber] [varchar](50) NOT NULL,
[VerificationReady] [int] NOT NULL,
[VoidLabelImage] [image] NULL,
[VerifiedBy] [varchar](50) NULL,
[VerifiedDate] [datetime] NULL,
[Verified] [int] NULL,
CONSTRAINT [PK_TBL_LABEL_VERIFICATION] PRIMARY KEY CLUSTERED
(
[SerialNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
View 1 Replies
View Related
Mar 26, 2008
I am developing a asp.net app and I have a long, very long list of data fields.
The values of the data fields have to be saved to a SQL database. The list is so long that it a pain in the ass to have to write all the code even to declare an object and all its properties by hand. So...
I made a list of all the field names and types and load it all up in an array. Then I wrote a bunch of macros to write all the properties one by one with the corredt type for the object I needed to create instead of doing it all one by one by hand.
I want to do the same thing with the Database. I dont want to have to hand define all the fiedls 1 by 1 by hand in the IDE. I want to write a simple macro, a loop theat loops thru my array and creates a field with the field name and type that it says in the array.
Simple enough.. Now I have the table defined already since that was simple enough. It is the 200 ++ fields that I dont wanna do.
So, please what is the code I need (in VB) to create 1 simple field in an existing table. Say the table is called "Table1" in the database "Database1" and the field I want is to be called "Field1" and to be 250 chr string..... ???
Thank you Marc
View 3 Replies
View Related
Oct 17, 2007
Hello,
I would like create in a table (A) a column with a formula's data.
In this formula I would like implement 2 fields from a Table (B)
So the formula can be :
[TABLE_B].[FIELD1] + [TABLE_B].[FIELD2]
Is it possible?
We can call 2 fields from a other table?
Thank you
View 1 Replies
View Related
Jan 26, 2007
Hi, I'm trying to build a several tables using sql ce 3.1
I refer to the manual on Create Table:
ms-help://MS.SSCE.v31.EN/ssmprog3/html/143cad25-5c1d-4c96-bd8b-6a95dbfb1b00.htm
The sample:
CREATE TABLE MyCustomers (CustID int IDENTITY (100,1) PRIMARY KEY,
CompanyName nvarchar (50))
just simply doesn't work displaying this error:
SQL Execution Error
Executed statement: CREATE TABLE MyCustomers (CustID int IDENTITY (100,1) PRIMARY KEY,
CompanyName nvarchar (50))
Error Source: SQL Server Compact Edition ADO.Net Data Provider
Error Message: There was an error parsing the query. [Token line number =1,
Token line offset 40, Token in error=IDENTITY]
I tested against a SQL Desktop, everything is OK.
What's wrong with the sentence?
Please some advise or workaround will be appreciated
Thanks
MSCD Fernando Zorrilla de San Martín
View 4 Replies
View Related
Apr 21, 2008
hi
i have table i use it for update insert
and the users use this table from a grid on the web
and i need to prevent from white space in the fields in table
so how to
create TRIGGER remove white space from a fields in table scan and fix it ?
Code Snippet
SELECT TRIM(fieldname)
, LTRIM(fieldname)
, RTRIM(fieldname)
, LTRIM(RTRIM(fieldname))
FROM tablename
Code Snippet
WHERE (LTRIM(RTRIM(fieldname)) = 'Approve')
Code Snippet
replace(@text,' ','')
create TRIGGER on update insert and not to damage the text in the all fields
TNX
View 21 Replies
View Related
Sep 11, 2015
I have a record in an Excel format (Excel 2010) and I would like to bulk import that into SQL Server 2008 and also while importing, SQL Server will automatically create a new table based on the header fields or row of the source file.
I am not sure if SQL Server 2008 has this capabilities.
View 0 Replies
View Related
Feb 20, 2008
How can I create a Table whose one field will be 'tableid INT IDENTITY(1,1)' and other fields will be the fields from the table "ashu".
can this be possible in SQL Server without explicitly writing the"ashu" table's fields name.
View 8 Replies
View Related
Nov 20, 2007
Hi!
I need to sync an application on windows mobile 5 that uses sql server mobile with sql server 2005. I've tried Merge and it populated the tables on the server with new fields and triggers. Does RDA do the same thing?
Jesus saves. But Gretzky slaps in the rebound.
View 2 Replies
View Related
Jul 31, 2013
I have a list of items in one table and a field (pageName) in another table that may contain one of the aforementioned items somewhere within that field. There is no fixed position within the field where the itemNo may be so I can't just use SUBSTRING(pageName,2,5) in(select itemNo from tblItem).
Logically, it's like I need to combine IN and LIKE: select pageName where pageName LIKE IN %select itemNo from tblitemNo%..LIKE can only handle one comparison string.
View 5 Replies
View Related
Nov 20, 2007
Hi!
I need to sync an application on windows mobile 5 that uses sql server mobile with sql server 2005. I've tried Merge and it populated the tables on the server with new fields and triggers. Does RDA do the same thing?
Jesus saves. But Gretzky slaps in the rebound.
View 4 Replies
View Related
Aug 9, 2004
Howdy all !
I'm just a bit on the frustrated side cause I want to create a foreign key but SQL Server won't let me. :(
I have table A with a primary key of main_id
I have table B with a primary key of another_id
Table A has a corresponding field called another_id.
I'm trying to create a foreign key between Table A & Table B on another_id but since it's not the Primary Key in Table A I get the following error:
There are no primary or candidate keys in the referenced table 'A' that match the referencing column list in the foreign key 'fk_classB_classA'.
Am I missing something totally obvious here? Why should I have to create a foreign key on a primary key?
What I find interesting is that I can create the relationship from enterprise manager but when I script it out is when I run into problems.
Here's the script I'm using:
Begin Code
alter table B add
constraint fk_classB_classA foreign key
(classB) references A (classB)
on delete no action
on update no action
End Code
Any help is greatly appreciated.
tam
View 6 Replies
View Related
Jan 24, 2007
Amir writes "Dear Team Members;
Im writing a program in ASP language which it is using SQL Server for database.
I want to know how to create a link between fields of two tables. For example:
I have a table named: Projects containing:
p_name
status
date
member1
member2
and another table named results containing:
p_name
s_status
status1
status2
I want to create a link between field s_status of STATUS table and field status of table Projects
Users are sending data to table projects, when a user send some data to status of projects, I want this link to send data from status in project to s_status of results.
Could you please help me how to do it?
Regards.
Amir Zandi"
View 1 Replies
View Related
Aug 2, 2007
Can anyone please help me with the following:
I have a table which has fields called defid, datavalue, pid etc.
I have to create a new table which should have fields:
CustID, datavalue[SocSec]{note:this is the value in d.datavalue when defid is 1004), datavalue[FirstName]{note: this is the value in d.datavalue when defid is 1006}, datavalue[LastName]{note: this is the value in d.datavalue when defid is 1007}, [FullName] {note: this is the value I should get by joining the datavalues (lastname and firstname) when defifis 1006 and 1007) ie. ex: FirstNameLastName.
COULD ANYONE PLEASE HELP ME ON THIS. DATA QUERY is as follows:
Thank you in advance.
SELECT DISTINCT c.CustID, d.datavalue, f.name
FROM dbo.Customers c
INNER JOIN dbo.Date7 d ON d.pid = c.root
INNER JOIN dbo.fielddisc f ON f.defid = d.defid
WHERE c.separatedate BETWEEN @StartDate AND @EndDate
AND c.DateTab = 7 AND d.defid IN (1004, 1006, 1007, 1009) AND c.CustID = 10 AND c.root = 8472
ORDER BY c.root
View 3 Replies
View Related
Oct 4, 2006
What would be the recommendation/approach in creating a seperate field in which joins several differate fields together.
I have a table with field name a, b, and c. I want the information in those fields to be populated in a seperate field, d.
So instead of:
a
122
b
joe
c
st
I would have:
d
122 joe st
Thanks!
View 12 Replies
View Related
Mar 31, 2008
Hello.
I need some help constructing a query i need to run on my database. I need to add 2 fields to every table in my databse. However, some of the tables already have1 or both these fields so i need to somehow do a check if the dield already exists. If it does not create the fields. Im using a MS SQL express 2005 server.
Could anyone help me construct this. Im pretty novice at SQL.
Thanks.
View 7 Replies
View Related
Dec 14, 2014
How can we create a DB for a single attribute such as ORDER DETAILS, CASH RECEIPT, TAX INVOICE having more than 2 text fields.
Also, in every form attribute such as order id is not present - in order identify the same as a primay key. So, which other attributes or fields can be considered as a primary key.
View 5 Replies
View Related
Aug 29, 2013
For large databases is it a good idea to create indexes for fields that are used in Where statements? Does that improve performance and reduce overhead?
View 4 Replies
View Related
Oct 19, 2007
I want to combine two of my fields somewhat like when you combine to char fields and concatenate them. But these two fields are int. How do i do that? Here's my query right now:
Code Block
SELECT AutoID,
CONVERT(Varchar(Mars_Calender_Year )+ CONVERT(Varchar(Mars_Calender_Period_Code) as MarsId
FROM NavisionReplication.dbo.Tbl_Mars_Calender
ORDER BY Mars_Calender_Year DESC
what am i doing wrong?
View 5 Replies
View Related
Sep 1, 2015
I have the following table (Table does not have unique key id )
Last Name First Name DATE Total-Chrg
Jaime KRiSH 5/1/2015 -4150.66
Jaime KRiSH 5/1/2015 1043.66
Jaime KRiSH 5/1/2015 1043.66
Jaime KRiSH 5/1/2015 4150.66
Jaime KRiSH 5/3/2015 4150.66
Peter Jason 5/1/2015 321.02
Peter Jason 5/1/2015 321.02
Peter Jason 5/23/2015 123.02
I want the results to be in following way
Uniq ID Last Name First Name DATE Total-Chrg
1 Jaime KRiSH 5/1/2015 -4150.66
2 Jaime KRiSH 5/1/2015 1043.66
2 Jaime KRiSH 5/1/2015 1043.66
3 Jaime KRiSH 5/1/2015 4150.66
4 Jaime KRiSH 5/3/2015 4150.66
5 Peter Jason 5/1/2015 321.02
6 Peter Jason 5/1/2015 321.02
7 Peter Jason 5/23/2015 123.02
May be we may do by dense_rank or Row_Number, but I couldn't get the exact query to produce based on the above table values. There are some duplicates in the table(which are not duplicates as per the Business). For those duplicated Unique ID should be same(Marked in Orange Color which are duplicates).
View 4 Replies
View Related
May 22, 2007
Hi All
I hope someone smart can help me, it would be highly appriciated.
I am developing an SQL Serverdatabase and in on of the tables I need the primary key to consist of two pieces of data.
TblOrders: OrderNum, Orderdate, ....
TblDispatchers: DispatcerID, Dispatcher, DispatcherAddress
The OrderNum field in TblOrders need to be a composite of an AutoNum-field (incrementet by 1) and the DispatcerID from the tblDispatchers.
Can this be done, and how.
Many thanks
Kind regards
Tina
View 3 Replies
View Related
Mar 17, 2014
I'm trying to create an output file in a specific layout. For some reason my output file is adding an extra 10 spaces between the Account Number and the Check Number in the statement below. The rest of the output file looks fine. Where the extra 10 spaces are coming from? I need 1 Filler Space between these fields.
SELECT DISTINCT
CASE p.PaymentMethodID WHEN 10 THEN 'I' WHEN 60 THEN 'V' WHEN 50 THEN 'S' ELSE 'I' END
+ CONVERT(CHAR(1), '')
+ (REPLICATE('0', 20 - LEN(ba.AccountNumber))+ CONVERT(CHAR(20), ba.AccountNumber))
+ CONVERT(CHAR(1), '')
+ (REPLICATE('0', 18 - LEN(p.CheckNumber)) + CONVERT(VARCHAR(18), p.CheckNumber))
[Code] .....
View 2 Replies
View Related
May 3, 2004
Hello all,
Please help....
I have a text file which needs to be created into a table (let's call it DataFile table). For now I'm just doing the manual DTS to import the txt into SQL server to create the table, which works. But here's my problem....
I need to extract data from DataFile table, here's my query:
select * from dbo.DataFile
where DF_SC_Case_Nbr not like '0000%';
Then I need to create a new table for the extracted data, let's call it ExtractedDataFile. But I don't know how to create a new table and insert the data I selected above into the new one.
Also, can the extraction and the creation of new table be done in just one stored procedure? or is there any other way of doing all this (including the importation of the text file)?
Any help would be highly appreciated.
Thanks in advance.
View 3 Replies
View Related
Aug 4, 2004
Hi All,
I'm trying to create a proc for granting permission for developer, but I tried many times, still couldn't get successful, someone can help me? The original statement is:
Create PROC dbo.GrantPermission
@user1 varchar(50)
as
Grant create table to @user1
go
Grant create view to @user1
go
Grant create Procedure to @user1
Go
Thanks Guys.
View 14 Replies
View Related
May 20, 2008
the subject pretty much says it all, I want to be able to do the following in in VB.net code):
{[If [table with this name] already exists [in this sql database] then [ don't create another one] else [create it and populate it with these values]}
How would I do this?
View 3 Replies
View Related
Jul 20, 2005
Can I dynamically (from a stored procedure) generatea create table script of all tables in a given database (with defaults etc)a create view script of all viewsa create function script of all functionsa create index script of all indexes.(The result will be 4 scripts)Arno de Jong,The Netherlands.
View 1 Replies
View Related
Apr 23, 2008
I have 2 tables with 2 fields in common between them. How do I get a count of rows in table 1 but not in table 2 matching those 2 fields? Thanks.
View 7 Replies
View Related
Jul 20, 2005
I have some code that dynamically creates a database (name is @FullName) andthen creates a table within that database. Is it possible to wrap thesethings into a transaction such that if any one of the following fails, thedatabase "creation" is rolledback. Otherwise, I would try deleting on errordetection, but it could get messy.IF @Error = 0BEGINSET @ExecString = 'CREATE DATABASE ' + @FullNameEXEC sp_executesql @ExecStringSET @Error = @@ErrorENDIF @Error = 0BEGINSET @ExecString = 'CREATE TABLE ' + @FullName + '.[dbo].[Image] ( [ID][int] IDENTITY (1, 1) NOT NULL, [Blob] [image] NULL , [DateAdded] [datetime]NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]'EXEC sp_executesql @ExecStringSET @Error = @@ErrorENDIF @Error = 0BEGINSET @ExecString = 'ALTER TABLE ' + @FullName + '.[dbo].[Image] WITHNOCHECK ADD CONSTRAINT [PK_Image] PRIMARY KEY CLUSTERED ( [ID] ) ON[PRIMARY]'EXEC sp_executesql @ExecStringSET @Error = @@ErrorEND
View 2 Replies
View Related
Aug 31, 2006
Hello all:
Is it possible to creates fields of the table dynamically?. I have this situation in my project. This is just a small sample. I have row of length 140. I don't wan't to declare all this fields manually using the create table command.
The description of table is as, in this table all the field are of type varchar only, there are like 140 columns.
create dummy emp (
field1 VARCHAR(100), field2 varchar(200), field3 VARCHAR(100).... )
Table: Dummy
================================================== ==
field1 field2 field3..........
Empid Empname empaage1 sam 23...........
2 rai 22............
.
.
.
n raj 45.............
================================================== ==
Now I want to create another table as "EMP" , with proper data type
fields too..
create table emp (
empid int, empname varchar(100), empage int....)
The table should look like as:
Table: EMP
================================================== ==
Empid Empname empaage............
1 sam 23...............
2 rai 22................
.
.
.
n raj 45.................
================================================== ==
I want to do this dynamically.....
Some how I need to extract those field from table[dummy]; the first row acts as a column header for the table[Emp] and the subsequent row acts as a record for the table[Emp]
A small rough snippet of the code will be appreciated....
Waiting for replies........
saby
View 1 Replies
View Related
May 29, 2008
I want to update my Table but it has 30 fields so how can i modify my table i have to add some fields in it
I need that query for alter table
in it
reply me soon
View 14 Replies
View Related
Jun 13, 2008
All- Supposing I have table_a and table_b. Table_a has fields FIRST_A and LAST_A for people's first and last names. Similarly, table_b has FIRST_B and LAST_B for people's first and last names.
How would I create a record in table_b for each record in table_a, such that for each added record, the values of FIRST_A is copied to FIRST_B and LAST_A is copied to LAST_B. (Table_b will have other fields that are left unfilled.)
Thanks!
-Kurt
View 1 Replies
View Related