Inserting To Table With Dashes In Fieldnames
Jul 23, 2005
Hello,
I have had a request, one of our tables is used as a report and I have been
asked that all fieldnames for months have dashes in them,
like Jan-05 instead of Jan05 and so on...
Now what we have is a CURSOR which loops through all values in another table
generating these fieldnames, like 'Jan-05', Feb-05' etc..
Then the table definition is modified so these months display as fieldnames.
That is fine,
Except later it calls another stored procedure which inserts values from
another table into this one.
INSERT INTO TableName
SELECT *
FROM Tablename
Obviously this is failing because the newly amended table now has dashes in
the fieldnames now.
Now as the fieldnames are created dynamically I cannot simply do INSERT
Tablename ([Jan-05], [feb-5]) etc, as one would normally do,
How could I do this bearing in mind its not always going to be 12 months we
use,
Basically I am asking how can I modify this INSERT statement so it takes
into account the fieldnames with dashes in them
Bearing in mind I don't know what the fieldnames are called as they are
created dynamically by a CURSOR in a different routine.
And its not always 12 months.
have you any ideas?
and the dashes are required
Jayne
View 4 Replies
ADVERTISEMENT
Mar 6, 2008
Help! I need to write a SQL statement to INSERT a record into an Access table, which has an Autonumber field as its primary key. Sadly, many of the fieldnames contain one or more spaces in the name (eg. "field name").
When I try to write the insert statement with the fieldnames specified like this:
INSERT INTO tblOne ( fieldname1, fieldname2, field name 3 )
VALUES ( value1, value2, value3 )
the spaces in the last fieldname cause a problem.
When I try encasing the problem fieldname in brackets, like this:
INSERT INTO tblOne ( fieldname1, fieldname2, [field name 3] )
VALUES ( value1, value2, value3 )
SQL thinks I want to be prompted for a value.
When I try to use the fully-qualified name in the insert statement like this:
INSERT INTO tblOne ( fieldname1, fieldname2, tblOne.[fieldname 3] )
VALUES ( value1, value2, value3 )
I get a SQL syntax error.
Then I tried doing the insert without spelling out the fieldnames at all, like this:
INSERT INTO tblOne
VALUES ( value1, value2, value3 )
but I am required to provide a value for every field, including the autonumber field, which defeats the purpose of the autonumber.
Any ideas?
View 10 Replies
View Related
Oct 10, 2007
I am trying to insert data into two different tables. I will insert into Table 2 based on an id I get from the Select Statement from Table1.
Insert Table1(Title,Description,Link,Whatever)Values(@title,@description,@link,@Whatever)Select WhateverID from Table1 Where Description = @DescriptionInsert into Table2(CategoryID,WhateverID)Values(@CategoryID,@WhateverID)
This statement is not working. What should I do? Should I use a stored procedure?? I am writing in C#. Can someone please help!!
View 3 Replies
View Related
Apr 8, 2008
Hi!
I run a query that returns strings that look like this: 08-07, 06-07,04-05 etc. But when I copy the query outuput from Management Studion in SS2005 into Excel 2003, strings get converted into custom dates such as 8-Jun, 9-Jul, 4-May etc in Excel. Is there anthing I can do in T-SQL to prevent this conversion? The strings are set as char(5) datatype in a T-SQL view.
Thanks.
View 8 Replies
View Related
May 30, 2008
Can anyone help with this. IF i have a ssn like 123459636 and i want to insert dashes into it how would i do that. Results would be 123-45-9636
The second problem is that some of the ssn dont have zero in them so some look like 1234567 i would have to pad with zero then trim from the right to 9 digits then insert the dashes not sure how to do that
results would be 001-23-4567
View 7 Replies
View Related
Jan 31, 2008
I have created a table Table with name as Varchar and id as int. Now i have started inserting the rows like, insert into Table values ('arun',20).Yes i have inserted a row in the table. Now i have got the values " arun's ", 50. insert into Table values('arun's',20) My sqlserver is giving me an error instead of inserting the row. How will you solve this problem?
View 3 Replies
View Related
May 29, 2008
Hi,
In a extract that i have the SS# is as 123-45-6789 how do i remove the dashes in the string and have the final expression of 123456789.
thanks
Karen
View 3 Replies
View Related
Oct 12, 2007
Hi,I have a large table with a 'datetime' column that has date and timevalues in it. The data is in this format:2007-10-02 09:54:00.000The table is called 'profile' and the column 'msgdate'I want to return only rows that match a specific date. So far I havethe following query working:select * from profile where msgdate like '%2007%'This returns all rows that start with '2007'. However I cannot seem toge the syntax that will allow me to return a specific date, e.g.2007-10-02I have researched this, trying all sorts of queries with escapecharacters/sequences because of the dash character, but I cannot getit to return anything. Most of my queries have ran without error, itsjust that no data is returned.James
View 2 Replies
View Related
Aug 8, 2007
when i do a bulk insert with dashes (i.e. one - two), i get the character u with two dots above it ( Ü ) in place of all the dashes? can't seem to figure out why. the field terminator is a comma ( , ).
i'm inserting just text with dashes into char(75) field.
can someone explain and provide a solution? i would greatly appreciate it!
my script is quite simple, maybe i'm missing something.
Code Snippet
bulk insert testtable
from '\localc$adjust.txt'
with (fieldterminator = ',')
thanks!
View 1 Replies
View Related
Nov 8, 2006
Ok. Here is a weird problem.
I have a package which contains a variable with the value of a UNC sharename. The hostname in the share contains dashes.
I am trying to create a SQL Agent entry to execute said package. Because the package is now in production I want to pass in a new value for the sharename variable. I use the SQL @@SERVERNAME variable to build the package command. (Notice the BlotterUploadFolder variable at the end.)
SELECT @command = N'/FILE "\' + @@SERVERNAME + 'PackagesMiddleOfficeReconcilliationImportManualBlotter_AccountTransfers.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW /CONNECTION MiddleOfficeDb;"Data Source=' + @@SERVERNAME + ';Initial Catalog=MiddleOffice;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;" /SET package.variables[BlotterUploadFolder].Value;\' + @@SERVERNAME + 'ManualBlottersUploads'
If I print the @command variable right after setting it, I get what I expect.
/FILE "\PRD-NY-DB-01PackagesMiddleOfficeReconcilliationImportManualBlotter_AccountTransfers.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW /CONNECTION MiddleOfficeDb;"Data Source=PRD-NY-DB-01;Initial Catalog=MiddleOffice;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;" /SET package.variables[BlotterUploadFolder].Value;\PRD-NY-DB-01ManualBlottersUploads
If I cut-paste this into cmd window and execute it with DTExec.exe the package works fine. But when I run the schedule task it causes an error. When I go into the Job Properties dialog to look at the step, I see the value (in the "Set values" table) set to \PRD instead of \PRD-NY-DB-01.
If I modify the SELECT command above to put double-quotes around the value, the value is correct in the Job Properties dialog but causes an error when I exec it with DTExec.
/FILE "\PRD-NY-DB-01PackagesMiddleOfficeReconcilliationImportManualBlotter_AccountTransfers.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW /CONNECTION MiddleOfficeDb;"Data Source=PRD-NY-DB-01;Initial Catalog=MiddleOffice;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;" /SET package.variables[BlotterUploadFolder].Value;"\PRD-NY-DB-01ManualBlottersUploads"
causes the error
Option "-NY" is not valid.
Hopefully somebody has an idea of what is going on.
Thanks.
- Jason
View 3 Replies
View Related
Jul 23, 2005
Hi,I'm having trouble running the following query:select * from message where text_body like ' ----------%'ie, five spaces followed by at least ten hyphens. The query doesn'tcomplete, so eventually I cancel it. If I remove the hyphens from thequery ("... like ' %'") then it runs fine (though it doesn't findthe correct data).Am I confusing SQL Server by using a wildcard or regular expression?(I'm using SQL Server 2000 - 8.99.760).Thanks in advance for any helpRichard
View 5 Replies
View Related
Jul 20, 2005
Hi all,I have a table in the DB which holds ISBN book nubers .. which, as you knowhave dashes (examlple: 0-98765-8-9).When I run a query from an ASP page to find the number, it doesn't returnanything:sISBN = "0-98765-8-9"OpenSQL(select URL from ISBN_Redirect where ISBN=" & sISBN )Can you give me a hind of what I am doing wrong?Thanks so much in advance.S
View 1 Replies
View Related
Jun 29, 2007
Using the "set values" tab, I am trying to set the values of package variables to strings that contain spaces and/or dashes. If I run the package using the integrations services server, it runs perfectly. If I schedule the package on SQL Server agent, I get the following error.
"Executed as user: GBPSQLServer. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Option "-restofstring" is not valid. The command line parameters are invalid. The step failed."
Looking at the command line generated by the SQL Agent interface, the value is quoted using double quotes like this:
/SQL "PackageName" /SERVER SQL1 /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /SET "Package.Variables[LogFolder].Value";"\remoteservernameinetpub he-restofstring
estofstring" /REPORTING E
It seems that the command line parser is not recognising the quotes, or maybe the slashes are throwing it off, or sql server agent is doing something funny to the command line before it executes it.
Anybody got any ideas?
View 1 Replies
View Related
Sep 14, 2015
I have created a job which executes a procedure which executes and creates logs of StartTime and EndTime and records affected. The last step of the job sends mails to recipients from the logs table. Though I get the info correctly I get all annoying dashes for 4-5 lines between each line of message. How to remove the dashes.
Below is the code i use to send mails.
EXEC msdb.dbo.sp_send_dbmail
@profile_name='SQL Mail Service',
@recipients='biscribbler@gmail.com',
@copy_recipients='biscribbler@gmail.com',
@subject='SOFT SCRUB Completed for LiveDB',
[Code] ....
View 2 Replies
View Related
Apr 10, 2006
I have imported a package using SSIS into the Maintenance Plan folder from another server.
It imports fine, but when i Modify the package then click save I get the following error : 'Guid should contain 32 digits with 4 dashes'.
This has been reported on other forums without resolution.
Pete
View 17 Replies
View Related
Feb 23, 2015
I am trying to insert bulk data into main table from staging table in sql server 2012. If any error comes, this total activity is rollbacked. I don't want that to happen. I want to know the records where ever the problem persists, and the rest has to be inserted.
View 2 Replies
View Related
Apr 24, 2008
My Pocket PC application exports signature as an image. Everything is fine when choose Use SQL statements in TableAdapter Configuration Wizard.
main.ds.MailsSignature.Clear();
main.ds.MailsSignature.AcceptChanges();
string[] signFiles = Directory.GetFiles(Settings.signDirectory);
foreach (string signFile in signFiles)
{
mailsSignatureRow = main.ds.MailsSignature.NewMailsSignatureRow();
mailsSignatureRow.Singnature = GetImageBytes(signFile); //return byte[] array of the image.
main.ds.MailsSignature.Rows.Add(mailsSignatureRow);
}
mailsSignatureTableAdapter.Update(main.ds.MailsSignature);
But now I am getting error "General Network Error. Check your network documentation" after specifying Use existing stored procedure in TableAdpater Configuration Wizard.
ALTER PROCEDURE dbo.Insert_MailSignature( @Singnature image )
AS
SET NOCOUNT OFF;
INSERT INTO MailsSignature (Singnature) VALUES (@Singnature);
SELECT Id, Singnature FROM MailsSignature WHERE (Id = SCOPE_IDENTITY())
For testing I created a desktop application and found that the same Code, same(Use existing stored procedure in TableAdpater Configuration Wizard) and same stored procedure is working fine in inserting image into the table.
Is there any limitation in CF?
Regards,
Professor Corrie.
View 3 Replies
View Related
May 24, 2007
Hi, I have a table in which I will insert several redundant data. Don't ask why, is Integration services, it only reads data and inserts it in a SQL table. THis way, I have a SQL table with several lines repeating them selves. What I want to do is create a procedure that reads the distinct data and inserts it in another table, but my problem is that I am not able to select data line by line on the original table to save it in local variables and insert it on the another table, I just can select the last line. I've tried a while cycle but no succeed. Here is my code: create proc insertLocalizationASdeclare @idAp int, @macAp varchar(20), @floorAp varchar(2), @building varchar(30), @department varchar(30)select @idAp = idAp from OLTPLocalization where idAp not in (select idAp from dimLocalization)select @macAp=macAp,@floorAp=floorAp,@building=building,@department=department from OLTPLocalizationif (@idAp <> null)beginInsert into dimLocalization VALUES(@idAp,@macAp,@floorAp,@building,@department)endGO This only inserts the last line in the "oltpLocalization" table. O the other hand, like this:create proc aaaaasdeclare @idAp as int, @macAp as varchar(50), @floorAp as int, @building as varchar(50), @department as varchar(50)while exists (select distinct(idAp) from OLTPLocalization)begin select @idAp =idAp from OLTPLocalization where idAp not in (select idAp from dimLocalization) select @macAp = macAp from OLTPLocalization where idAp = @idAp select @building = building from OLTPLocalization where idAp = @idAp select @department = department from OLTPLocalization where idAP = @idApif (@idAp <> null)begin insert into dimLocalization values(@idAp,@macAp,@floorAp,@building,@department)endendgo this retrieves every distinct idAp in each increment on the while statement. The interess of the while is really selecting each different line in the OLTPLocalization table. I did not find any foreach or for each statement, is there any way to select distinct line by line in a sql table and save each column result in variables, to then insert them in another table? I've also thought about web service, that reads the distinct data from the oltpLocalization into a dataset, and then inserts this data into the dimLocalization table. Is there anything I can do?Any guess?Really needing a hand here!Thanks a lot!
View 1 Replies
View Related
Oct 22, 2012
I write a insert trigger on my table LeaveRegister(1000 rows) and inserting rows in audit table, but when i inserting a row in LeaveRegister table. In audit table 1000 + 1 rows are inserting every time.
View 6 Replies
View Related
Aug 14, 2015
Below is my table structure. And I am inserting data from other temp table.
CREATE TABLE #revf (
[Cusip] [VARCHAR](50) NULL, [sponfID] [VARCHAR](max) NULL, GroupSeries [VARCHAR](max) NULL, [tran] [VARCHAR](max) NULL, [AddDate] [VARCHAR](max) NULL, [SetDate] [VARCHAR](max) NULL, [PoolNumber] [VARCHAR](max) NULL, [Aggregate] [VARCHAR](max) NULL, [Price] [VARCHAR](max) NULL, [NetAmount] [VARCHAR](max) NULL,
[Code] ....
Now in a next step I am deleting the records from #revf table. Please see the delete code below
DELETE
FROM #revf
WHERE fi_gnmaid IN (
SELECT DISTINCT r2.fi_gnmaid
FROM #revf r1, #revf r2
[Code] ...
I don't want to create this #rev table so that i can avoid the delete statement. But data should not affect. Can i rewrite the above as below:
SELECT [Cusip], [sponfID], GroupSeries, [tran], [AddDate], [SetDate], [PoolNumber], [Aggregate], [Price], [NetAmount], [Interest],
[Coupon], [TradeDate], [ReversalDate], [Description], [ImportDate], MAX([fi_gnmaid]) AS Fi_GNMAID, accounttype, [IgnoreFlag], [IgnoreReason], IncludeReversals, DatasetID, [DeloitteTaxComments], [ReconciliationID],
[Code] ....
If my above statement is wrong . Where i can improve here? And actually i am getting 4 rows difference.
View 5 Replies
View Related
Nov 2, 2015
INSERT
INTO [Table2Distinct]
([CLAIM_NUMBER]
,[ACCIDENT_DATE]
[code]....
I used the above query and it still inserts all the duplicate records. What is wrong with my statement?
View 5 Replies
View Related
Sep 18, 2014
I have a 2010 SSIS package where I am reading csv files with different fields and formatted data, I have created separate packages for each file and I am formatting the data to fit the final destination tables data elements, I've been instructed to create 7 separate packages to read each file and the format the data from the 7 csv files, and insert into their table tbl1, tbl2, tbl3...etc then, I'm taking a execute sql task and wanting to insert the tbl1, tbl2, tbl3...etc into destination table that will be the final table for all reports and other uses.
1- should I create a ID?
2- these files will be read once a month
3- I want to append the data, not drop and recreate each run,
4- It's 2012 SQL and 2010 SSIS
Each csv file is in a different format, some have 15 columns, other have 8 I have to parse the data, in SP to align with the fields in the destination table.
5- Can I force RowID to be the next auto gen number from tbl1,for the start of insert for tbl2, then last row of tbl2 for insert of tbl3???
View 0 Replies
View Related
Jan 19, 2008
Code Block
Hi,
I'm working on a database for a financial client and part of what i need to do is calculate a value from two separate rows in the same table and insert the result in the same table as a new row. I have a way of doing so but i consider it to be extremely inelegant and i'm hoping there's a better way of doing it. A description of the existing database schema (which i have control over) will help in explaining the problem:
Table Name: metrics_ladder
id security_id metric_id value
1 3 80 125.45
2 3 81 548.45
3 3 82 145.14
4 3 83 123.32
6 4 80 453.75
7 4 81 234.23
8 4 82 675.42
.
.
.
Table Name: metric_details
id metric_id metric_type_id metric_name
1 80 2 Fiscal Enterprise Value Historic Year 1
2 81 2 Fiscal Enterprise Value Current Fiscal Year
3 82 2 Fiscal Enterprise value Forward Fiscal year 1
4 83 2 Fiscal Enterprise Value Forward Fiscal Year 2
5 101 3 Calendar Enterprise value Historic Year 1
6 102 3 Calendar Enterprise Value Current Fiscal Year
5 103 3 Calendar Enterprise value Forward Year 1
6 104 3 Calendar Enterprise Value Forward Year 2
Table Name: metric_type_details
id metric_type_id metric_type_name
1 1 Raw
2 2 Fiscal
3 3 Calendar
4 4 Calculated
The problem scenario is the following: Because a certain number of the securities have a fiscal year end that is different to the calendar end in addition to having fiscal data (such as fiscal enterprise value and fiscal earnings etc...) for each security i also need to store calendarised data. What this means is that if security with security_id = 3 has a fiscal year end of October then using rows with ids = 1, 2, 3 and 4 from the metrics_ladder table i need to calculate metrics with metric_id = 83, 84, 85 and 86 (as described in the metric_details table) and insert the following 4 new records into metrics_ladder:
id security_id metric_id value
1 3 101 <calculated value>
2 3 102 <calculated value>
3 3 103 <calculated value>
4 3 104 <calculated value>
Metric with metric_id = 101 (Calendar Enterprise value Historic Year 1) will be calculated by taking 10/12 of the value for metric_id 80 plus 2/12 of the value for metric_id 81.
Similarly, metric_id 102 will be equal to 10/12 of the value for metric_id 81 plus 2/12 of the value for metric_id 82,
metric_id 103 will be equal to 10/12 of the value for metric_id 82 plus 2/12 of the value for metric_id 83 and finally
metric_id 104 will be NULL (determined by business requirements as there is no data for forward year 3 to use).
As i could think of no better way of doing this (and hence the reason for this thread) I am currently achieving this by pivoting the relevant data from the metrics_ladder so that the required data for each security is in one row, storing the result in a new column then unpivoting again to store the result in the metrics_ladder table. So the above data in nmetrics_ladder becomes:
security_id 80 81 82 83 101 102
----------- -- -- -- -- -- --
3 125.45 548.45 145.14 123.32 <calculated value> <calculated value>
4 ...
.
.
.
which is then unpivoted.
The SQL that achieves this is more or less as follows:
*********
START SQL
*********
declare @calendar_averages table (security_id int, [101] decimal(38,19), [102] decimal(38,19), [103] decimal(38,19), [104] decimal(38,19),etc...)
-- Dummy year variable to make it easier to use MONTH() function
-- to convert 3 letter month to number. i.e. JAN -> 1, DEC -> 12 etc...
DECLARE @DUMMY_YEAR VARCHAR(4)
SET @DUMMY_YEAR = 1900;
with temp(security_id, metric_id, value)
as
(
select ml.security_id, ml.metric_id, ml.value
from metrics_ladder ml
where ml.metric_id in (80,81,82,83,84,85,86,87,88,etc...)
-- only consider securities with fiscal year end not equal to december
and ml.security_id in (select security_id from company_details where fiscal_year_end <> 'dec')
)
insert into @calendar_averages
select temppivot.security_id
-- Net Income
,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[80])
+((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[81]) as [101]
,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[81])
+((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[82]) as [102]
,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[82])
+((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[83]) as [103]
,NULL as [104]
-- Share Holders Equity
,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[84])
+((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[85]) as [105]
,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[85])
+((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[86]) as [106]
,(CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR))/12*[86])
+((12 - CONVERT(DECIMAL, MONTH(cd.fiscal_year_end + @DUMMY_YEAR)))/12*[87]) as [107]
,NULL as [108]
-- Capex
-- Sales
-- Accounts payable
etc...
..
..
from temp
pivot
(
sum(value)
for metric_id in ([80],[81],[82],[83],[84],[85],[86],[87],[88],etc...)
) as temppivot
inner join company_details cd on temppivot.security_id = cd.security_id
*********
END SQL
*********
The result then needs to be unpivoted and stored in metrics_ladder.
And FINALLY, the question! Is there a more elegant way of achieving this??? I have complete control over the database schema so if creating mapping tables or anything along those lines would help it is possible. Also, is SQL not really suited for such operations and would it therefore be better done in C#/VB.NET.
Many thanks (if you've read this far!)
M.
View 6 Replies
View Related
Jan 16, 2005
Hi,
Anyone can help me?
How to create Table A by inserting all the data from Table B?
Cheers,
Daniel.
View 1 Replies
View Related
May 12, 2008
How do i insert data into multiple tables. Lets say i have 2 tables: Schedules and Event
Schedules data is entered into the Schedules Table first
then now i need to insert Event table's data by refrencing the (PK ID) from the schedules table.
How do i insert data into Event table referencing the (PK ID) from Schedules Table ?
Fields inside each of the tables can be found below:
Event Table
(PK,FK) ScheduleID
EventTitle
AccountManager
Presenter
EventStatus
Comment
Schedule Table
(PK) ID
AletrnateID
name
UserID
UserName
StartTime
EndTime
ReserveSource
Status
StatusRetry
NextStatusDateTime
StatusRemarks
View 2 Replies
View Related
Jun 4, 2015
I am looking for an alternate logic for below-mentioned code where I am inserting into a table and having left join with the same table
insert TABLE1([ID],[Key],[Return])
select distinct a.[ID],cat1,cat2 from
(select ID,[Key] Cat1 ,[Return] cat2 from @temp as temp) a left join TABLE1 oon a.ID= o.ID
and a.Cat1 = o.[Key]
and a.cat2 = o.[return]
where [key] is null order by ID
View 2 Replies
View Related
Jan 9, 2008
Hey all,
Quick question for ya. If I wanted to insert 1 value into 1 table and another value into another table, how would I do that with closing connections and reopening and whatnot? I am using Visual Web Dev and SQL server 2005 express. I have been trying to mess around with it but I can't figure it out. Here is the situation.
I want to insert a GroupName and GroupDescription that a user fills in in 2 text boxes named GroupNametxt and GroupDescriptiontxt. This data will go into the "Group" table.
I then also want to Insert the data UserID (taken from the aspnet ID table), and GroupName from the Grouptxt.text into a GroupMembership table.
How would I do all this in 1 button click? I know some basic TSQL but I don't know how to handle the opening and closing of connections and whatnot.
Thanks,
Chris
View 2 Replies
View Related
Feb 15, 2008
VWD 2005 Express. Visual Basic. Sql Server 2005.
How may I insert a record into a table from code behind (I am doing this without any entries to a form). The connection name is GoodNews_Extranet. The table is Login. The fields I want to write are UserId and dtTime. Thanks for any help. The insert command is:
INSERT INTO [Login] ([SystemUserId], [dtTime]) VALUES (@SystemUserId, @dtTime)
I will provide the @ parameters programmatically. I just need to know the commands I should use in Visual Basic to actually execute the INSERT command. Thanks for the help.
View 2 Replies
View Related
Jan 18, 2004
I've got a string value in C# that contains:
"<?xml version="1.0"?><?qbxml version="2.0"?><QBXML><QBXMLMsgsRq onError="stopOnError"><JournalEntryAddRq requestID="1"><JournalEntryAdd><TxnDate>2003-11-18</TxnDate><JournalDebitLine><AccountRef>
<FullName>1200</FullName>
</AccountRef>
<Amount>160.06</Amount>
<Memo>613663</Memo>
<ClassRef>
<FullName>Buffalo</FullName>
</ClassRef></JournalDebitLine><JournalCreditLine><AccountRef>
<FullName>5687</FullName>
</AccountRef>
<Amount>160.06</Amount>
<Memo>613663</Memo>
<ClassRef>
<FullName>Buffalo</FullName>
</ClassRef></JournalCreditLine><JournalDebitLine><AccountRef>
<FullName>1200</FullName>
</AccountRef>
<Amount>133.85</Amount>
<Memo>300931</Memo>
<ClassRef>
<FullName>Buffalo</FullName>
</ClassRef></JournalDebitLine><JournalCreditLine><AccountRef>
<FullName>5687</FullName>
</AccountRef>
<Amount>133.85</Amount>
<Memo>300931</Memo>
<ClassRef>
<FullName>Buffalo</FullName>
</ClassRef></JournalCreditLine></Journ
alEntryAdd></JournalEntryAddRq></QBXMLMsgsRq></QBXML>"
I'm passing that string to an insert stored procedure that hits a varchar field (varchar 5000).
problem is, nothing is showing up in the database.
there are no errors, but the field I have shows nothign in it after the insert.
if i run a test and set my string to "this is a test" it goes into the table from the SP_insert no problem.
So i'm figuring, somehow the formatting of the XML string in C# isn't kosher with MS SQL.
the above text is the exact string value from VS.NET command output (? mystring).
I'm really in need of help here guys. I'm trying to log this XML result into a log table for reference if there are ever any problems I can look in the log table @ the xml.
but i can't get it to insert.
Help!
View 3 Replies
View Related
Aug 24, 2004
I have the 3 tables below. I want to be able to add more than one item to a quote. I thought I had designed the tables so that I could have more than one item in a quote....? I've tried to do this:
insert into has_quote(date_of_quote,service_desk_contact,category,cat_ref)
values('09/12/2002', 'me',2,'C92') where quote_id = '1'
but it doesn't work. Am I totally missing something here?
Code:
CREATE TABLE Item (
cat_ref VARCHAR(5)PRIMARY KEY,
descrip VARCHAR(50),
date_added SMALLDATETIME,
date_last_pricecheck SMALLDATETIME,
cat_type VARCHAR(20),
contract VARCHAR(10),
cost_price SMALLMONEY,
installation_charge SMALLMONEY,
commercial_markup SMALLMONEY,
supplier_name VARCHAR(20),
supplier_phone VARCHAR(20),
notes VARCHAR(500))
CREATE TABLE has_quote (
quote_id INT IDENTITY (1,1) PRIMARY KEY,
date_of_quote SMALLDATETIME,
service_desk_contact VARCHAR(20),
category INTEGER,
cat_ref VARCHAR(5)FOREIGN KEY
REFERENCES Item(cat_ref),
first_name VARCHAR(10),
surname VARCHAR(10),
customer_phone VARCHAR(20),
FOREIGN KEY (first_name, surname, customer_phone)
REFERENCES Customer(first_name, surname, customer_phone)
ON DELETE CASCADE
ON UPDATE CASCADE)
CREATE TABLE Customer (
first_name VARCHAR(10),
surname VARCHAR(10),
customer_phone VARCHAR(20),
contract VARCHAR(10),
location VARCHAR(20),
email VARCHAR(50),
cust_id INT IDENTITY (1,1),
PRIMARY KEY (first_name, surname, customer_phone))
View 14 Replies
View Related
May 23, 2008
Hello,
I have created a CTE to combine some data. I have also created a temporary table that I need this data that the CTE returns to be inserted to and then everything that is in this temp table needs to be inserted to an existing table in my database. I don't know how to select the values from the CTE into the temp table?
Create Table #TempLabTestConfigImport
(labtestkey varchar(10) null,valuecode varchar(32)null,
value_description varchar(255) null,value_type varchar(32) null,
units varchar(32) null, table_name varchar(30) null,
field_name varchar(30) null)
Insert Into #TempLabTestConfigImport
(labtestkey,valuecode,value_description,value_type,units,table_name,field_name)
Does my select go here??? I tried select * and it doesn't like that syntax...
With labtestcheck (labtestcode)
as
(
select a.labtestcode
from EMR_temp_labtest_configupdate a
Where Not Exists (Select * From lab_test_add_conf b where b.labtest_key = a.labtestcode)
)
select row_number() over (partition by t.labtestcode order by b.valuecode) as count,
t.labtestcode,b.zsegcode,b.valuecode,b.valuedesc,b.valuetype,b.units,b.tablename,b.fieldname
from EMR_temp_labtest_configupdate a
inner join emr_zseg_code_descriptions b on a.zsegcode = b.zsegcode
inner join labtestcheck t on t.labtestcode = a.labtestcode
View 8 Replies
View Related
Nov 30, 2005
Hi, i am new to SQL. I have this problem here, i need a little help.
How do i insert data into a table only when there is no such record in the existing table? I have this:
INSERT INTO ptable ( symptoms, weight, solutions ) VALUES ( '"+str+"', "+percent+", '"+str3+"' );
View 6 Replies
View Related
Nov 17, 2006
Hi,
I am using MSSQL and I want to do something like this:
insert into
people
values
(newid(), *value from names table* )
but I can't work out how to select the value from one table and insert it into another table in a single query. Using a subquery or combining with a select has not worked.
Is this a job for a datacursor or is there a simpler way?
Thanks.
View 6 Replies
View Related