Pass Variables Across Forms, Then Insert Into Database
Nov 30, 2004
Hey Guys:
--- Not sure if this should be moved to webforms forum, or if it belongs here ---
Alright, I have been dealing with this issue for a few days now, and have found a few solutions but they all seem to throw different errors so I figured I'd ask here.
What i am trying to do is have a webform where user enter data, and have the data passed across forms, then displayed and inserted into a database on another form. THe first for has an asp:rangevalidator control dymamicly built so I cannot simply take of the tags and use the old style.
Eventually the user will be directed to a paypal form, and upon successful completion be redirected to the page with the insert command within it, but for now, passing it to a second page for review, then inserting it will work.
I am not sure how to accomplish this, a tutorial or a code example would be great!! I have though about panels, creating public objects, etc, but all the solutions I have found have one issue or another when I attempt to create them.
Autogrow of file 'FORMS' in database 'FORMS' cancelled or timed out after 30547 ms. Use ALTER DATABASE to set a smaller FILEGROWTH or to set a new size.
FORMS.LDF file is 7613952 KBand the growth is 512MB .
By how much should I set the filegrowth? The users are complaining that the application is freezing on them.
passing variables (values) from a form (MS Access Project) to a stored procedures in order to select records shouldn't be all that difficult. However after searching for hours and hours in various forums and discussions i'm still nowhere. I really hope someone can give me the missing hint.
OK All I whant to do is select data from the sql server via an access interface. the value entered into the access form by the user (for example the client name) should then be transferred to the stored procedure which will then return all the records of the chosen client.
The simple task of transferring this form value to the stored procedure is driving me crazy. Does anyone have an idea.
Hello, I'm new to the forum and new to SQL, ASP.NET, etc. I am creating an intranet site for my company in VS 2005 and have run into a very annoying problem that I can't seem to solve. I have tried Googling it and came up empty. I have a database in SQL Express 2005 and my website will be accessing several tables within the database. I can retrieve info just fine and I can update, delete, etc just fine using gridview or other prebuilt tools, but when I add a few text boxes and wire a button to the SqlDataSource.Insert() command, I get a new record that is full of null values except for the identity key I have set. The kicker is that I am also using a master page and when I duplicate the web page without the master page link, everything works just fine. The following snippets show what I'm doing:<InsertParameters><asp:FormParameter Name="Name" Type="String" FormField="txtName" /><asp:FormParameter Name="Location" Type="String" FormField="ddlLocation" /><asp:FormParameter Name="Issue" Type="String" FormField="txtProblem" /></InsertParameters>Of course I match the formfields to the text boxes, create an onclick event for my button, the sqldatasource is configured correctly, it just doesn't work with the master page no matter what I do. Any help would be appreciated. Thanks
I placed a post regarding this issue previously but no success. So I thought I explain everything properly this time in a new post. Thanks
I have created a stored procedure which passes variables to the ssis package and then executes the package. The two variables inside the ssis package are @FileName and @ConnectionPath As you can see from the below stored procedure, xp_cmdshell is used to execute the package. If only the first variable is used in the package and the @connectionPath variable is hardcoded inside the package then package runs fine. Problem is in this particular call as you see below because @ConnectionPath is included.
Error: 2007-08-08 08:46:01.29 Code: 0xC0202009 Source: BaseProfiles2 Connection manager "CounterpartyExposure" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for ODBC Drivers" Hresult: 0x80004005 Description: "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified". End Error
if only the output is run in the query analyser then the error is:
The identifier that starts with 'Data Source=gblond088sjyMSQL_curves_DEV1, 2025;Initial Catalog=CounterpartyExposure;Provider=SQLNCLI.1;Integrated Security=SSPI' is too long. Maximum length is 128.
iam trying to pass variable to a statement to grab data to from one DB table and pitch it in the same table in another DB base on evaluation like a where clause. but its not working what am i doing rong
here is the code
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ClientComment]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
Declare @BDFR varchar(20), @BDTO varchar(20), @EQID varchar(20), @TABLEDESC varchar(20), @DBO varchar(20) set @TABLEDESC = 'ClientComment' set @DBO = '.dbo.' set @BDFR = 'Commander' + @DBO + @TABLEDESC set @BDTO = 'Test_Commander'+ @DBO + @TABLEDESC set @EQID = '80_300_113' insert into @BDTO select from @BDFR where Eqid = @EQID
Fairly new to triggers and stored procedures and so far I haven't had to pass any variables or even call an SP from a trigger. The problem I have is to pass a 'callid' variable from an insert trigger to a stored procedure.
The SP is to delete any rows that already exist that contain the passed 'callid' and then insert the new inserted data (using variables again hopefully).
Is this at all possible and if so what is the syntax for passing the variable from the trigger and then reading it into the stored procedure ?
I have a project that uses GUID's througout and I'm completely stumped.
1) I create a "batch" GUID to batch the records I'm about to process.
2) I call a web service on a remote machine, and reserve the batch records by inserting the batch GUID into a string ---works fine
3)I call another web service that returns the rows that I just reserved as XML objects and insert into a string variable
4)I need to use the "batch"GUID variable which is typed as a string (DT_WSTR) as an added column so in a Data Flow Task I do the following:
a) use the XML string variable as the source of a XML Source Task -- works (now that I'm passing custom objects and not a dataset -- curious as to why I can't consume a dataset but thats a different question)
This is where things get tricky:
I've tried to add the BatchGUID as a derived column, as a datatype of DT_WSTR (unicode string) and convert it to uniqueidentifier in the Data Conversion task --- error, cannot convert unicode to uniqueidentifier (I know I can in C# and SQL Server so why not here).
I've tried to CAST the BatchGUID as a uniqueidentifier and pass that to the datasource -- again conversion error.
I've tried using the type Object and Casting to anything and that doesn't work either.
I've tried to pass the unicode all the way to the SQL Server Destination -- and insert into UniqueIdentifier field... again no go.
All help would be appreciated, at this point I can't see any way of using a UniqueIdentifier as a key field, and maintaining it through the package... Is this a bug?
Oh and if you want to have some real fun, try returning the type of UniqueIdentifier as an output parameter using a ADO.NET connection.
UpdateCommand="UPDATE PsnQualifications SET quantity=@quantity WHERE rowstatus=1 and qualId=@original_qualId"
DeleteCommand="UPDATE PsnQualifications SET rowStatus=0, lastUpdateOn=getdate(), lastUpdateBy=@createdBy WHERE rowstatus=1 and qualId=@original_qualId"
Greetings, I am in need of assistance, I have a number of SQL databases that will be used by multiple users, this database will handle all their profile data (first name, last name, ect)Could someone post an example how to apply data from a web form textbox and insert the data into SQL? Any links or tutorials would help a ton! Please feel free to contact me via e-mail at:swaneyshawn@hotmail.com
I want to pass a command line values into an osql run stored procedure. The commandline values should be are the values to put into the insert stored procedure that writes them to a table.
Hi, I want to pass below given query into a variable
"if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ <POS_MONTH>]]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) DROP TABLE [dbo].[ <POS_DATE>] GO
SELECT * INTO [dbo].[<POS_DATE>] ] FROM SG_POS_Template WHERE 1 = 0; GO" Where [<POS_DATE>] is a parameter by which value will be assigned dymanically.....anybody please help me out....!!
Hi all I have a form where user input the data and onclick it has to be saved in to the database and dnt want to write on button click inser into table ................ i Want to write a function where i can take table name and columns data how can this be done please its urgent .........Same i want for update,delete etc? please
Hello, I am trying to use the forms view control to do a simple web app. My issue is, I cant get the connection to automatically generate insert, update and delete statements. I tried to do this manually but I always get an error. I have read that I need all the keys selected--this still didnt work. I have about 6 tables picked in my view, and if I pick one of them then this advanced feature works. I have the primary keys selected. here is my saved view. this is in SQL 2000. SELECT TOP 100 PERCENT dbo.ADDRESS.EMAIL, dbo.ADDRESS.FIRST_NAME AS [first name], dbo.ADDRESS.LAST_NAME AS [last name], dbo.ADDRESS.STATE, dbo.ADDRESS.TEL1 AS phone, dbo.INVOICES.QUOTE_NO AS [SALES QUOTE], dbo.CUST.NAME AS Company, dbo.ITEMS.ITEMNO AS [Course Number], dbo.ITEMS.DESCRIPT AS S_Descript, dbo.ADDRESS.JOB_TITLE AS [Job Title], dbo.TRAINING_SCHEDULE.[MONTH], dbo.TRAINING_SCHEDULE.S_DATE, dbo.TRAINING_SCHEDULE.END_DATE, dbo.TRAINING_SCHEDULE.IS_CONFIRMED, dbo.TRAINING_SCHEDULE.IS_PAID, dbo.TRAINING_SCHEDULE.CUST_CODE AS Account, dbo.TRAINING_SCHEDULE.SCHEDULE_ID, dbo.CUST.CUST_CODE, dbo.INVOICES.INVOICES_ID, dbo.X_INVOIC.X_INVOICE_ID, dbo.ADDRESS.ADDR_CODEFROM dbo.TRAINING_SCHEDULE INNER JOIN dbo.CUST ON dbo.TRAINING_SCHEDULE.CUST_CODE = dbo.CUST.CUST_CODE RIGHT OUTER JOIN dbo.X_INVOIC RIGHT OUTER JOIN dbo.INVOICES ON dbo.X_INVOIC.ORDER_NO = dbo.INVOICES.DOC_NO LEFT OUTER JOIN dbo.ADDRESS ON dbo.INVOICES.CUST_CODE = dbo.ADDRESS.CUST_CODE LEFT OUTER JOIN dbo.ITEMS ON dbo.ITEMS.ITEMNO = dbo.X_INVOIC.ITEM_CODE ON dbo.CUST.CUST_CODE = dbo.ADDRESS.CUST_CODEWHERE (dbo.X_INVOIC.ITEM_CODE LIKE 'FOT-%') AND (dbo.X_INVOIC.STATUS = 7) AND (dbo.ADDRESS.TYPE IN (4, 5, 6)) AND (dbo.ADDRESS.EMAIL <> '') AND (dbo.ADDRESS.COUNTRY = 'UNITED STATES') AND (dbo.ITEMS.CATEGORY = 'TRAININGCLASSES') AND (dbo.TRAINING_SCHEDULE.CUST_CODE = 'joe')ORDER BY dbo.ADDRESS.STATE I am new to this so I am not sure what to include. thanks, yellier
I am trying to get the contents of the Excel Files dynamically and dumping into the SQL Database using SSIS. Through WMI Event Watcher, I could find when one or more Excel files dumped in a particular folder and using ForEach Loop Container I was able to take all the filenames and pass it through Variables. But at the same time in the Data Flow, I have to pass each Sheet of an Excel File to the Excel Source control and export the data to my SQL Database using OLEDB Destination.
For that I need to get the names of each sheets in an Excel File and pass it to the Excel Source Control through variables. But when I give Data Access Mode as "Table name or view name variable" and provide the variable name in that, then it is giving an error message as "A destination table name has not been provided".
And at the same time, Since I was not able to provide an static Filename (as I am passing through Variables), when I tried to map the columns in the OleDB Destination, it is not allowing me to map the columns.
So all these things I should do at Run-time using Variables in SSIS. I don't want to hard-code any filenames or Sheet names. If any one of you have a solution, please share with me.
I'm new to SSIS, but have been programming in SQL and ASP.Net for several years. In Visual Studio 2005 Team Edition I've created an SSIS that imports data from a flat file into the database. The original process worked, but did not check the creation date of the import file. I've been asked to add logic that will check that date and verify that it's more recent than a value stored in the database before the import process executes.
Here are the task steps.
[Execute SQL Task] - Run a stored procedure that checks to see if the import is running. If so, stop execution. Otherwise, proceed to the next step.
[Execute SQL Task] - Log an entry to a table indicating that the import has started.
[Script Task] - Get the create date for the current flat file via the reference provided in the file connection manager. Assign that date to a global value (FileCreateDate) and pass it to the next step. This works.
[Execute SQL Task] - Compare this file date with the last file create date in the database. This is where the process breaks. This step depends on 2 variables defined at a global level. The first is FileCreateDate, which gets set in step 3. The second is a global variable named IsNewFile. That variable needs to be set in this step based on what the stored procedure this step calls finds out on the database. Precedence constraints direct behavior to the next proper node according to the TRUE/FALSE setting of IsNewFile.
If IsNewFile is FALSE, direct the process to a step that enters a log entry to a table and conclude execution of the SSIS.
If IsNewFile is TRUE, proceed with the import. There are 5 other subsequent steps that follow this decision, but since those work they are not relevant to this post. Here is the stored procedure that Step 4 is calling. You can see that I experimented with using and not using the OUTPUT option. I really don't care if it returns the value as an OUTPUT or as a field in a recordset. All I care about is getting that value back from the stored procedure so this node in the decision tree can point the flow in the correct direction.
The SSIS package passes the FileCreateDate parameter to this procedure, which then compares that parameter with the date saved in tbl_ImportFileCreateDate.
If the date is newer (or if there is no date), it updates the field in that table and returns a TRUE IsNewFile bit value in a recordset.
Otherwise it returns a FALSE value in the IsNewFile column.
SELECT @CreateDateInTable = FileCreateDate FROM tbl_ImportFileCreateDate WHERE ProcessName = @ProcessName
IF EXISTS (SELECT ProcessName FROM tbl_ImportFileCreateDate WHERE ProcessName = @ProcessName)
BEGIN
-- The process exists in tbl_ImportFileCreateDate. Compare the create dates.
IF (@FileCreateDate > @CreateDateInTable)
BEGIN
-- This is a newer file date. Update the table and set @IsNewFile to TRUE.
UPDATE tbl_ImportFileCreateDate
SET FileCreateDate = @FileCreateDate
WHERE ProcessName = @ProcessName
SET @IsNewFile = 1
END
ELSE
BEGIN
-- The file date is the same or older.
SET @IsNewFile = 0
END
END
ELSE
BEGIN
-- This is a new process for tbl_ImportFileCreateDate. Add a record to that table and set @IsNewFile to TRUE.
INSERT INTO tbl_ImportFileCreateDate (ProcessName, FileCreateDate)
VALUES (@ProcessName, @FileCreateDate)
SET @IsNewFile = 1
END
SELECT @IsNewFile
The relevant Global Variables in the package are defined as follows: Name : Scope : Date Type : Value FileCreateDate : (Package Name) : DateType : 1/1/2000 IsNewFile : (Package Name) : Boolean : False
Setting the properties in the "Execute SQL Task Editor" has been the difficult part of this. Here are the settings.
General Name = Compare Last File Create Date Description = Compares the create date of the current file with a value in tbl_ImportFileCreateDate. TimeOut = 0 CodePage = 1252 ResultSet = None ConnectionType = OLE DB Connection = MyServerDataBase SQLSourceType = Direct input IsQueryStoredProcedure = False BypassPrepare = True
I tried several SQL statements, suspecting it's a syntax issue. All of these failed, but with different error messages. These are the 2 most recent attempts based on posts I was able to locate. SQLStatement = exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output SQLStatement = exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
Parameter Mapping Variable Name = User::FileCreateDate, Direction = Input, DataType = DATE, Parameter Name = 0, Parameter Size = -1 Variable Name = User::IsNewFile, Direction = Output, DataType = BYTE, Parameter Name = 1, Parameter Size = -1
Result Set is empty. Expressions is empty.
When I run this in debug mode with this SQL statement ... exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output ... the following error message appears.
SSIS package "MyPackage.dtsx" starting. Information: 0x4004300A at Import data from flat file to tbl_GLImport, DTS.Pipeline: Validation phase is beginning.
Error: 0xC002F210 at Compare Last File Create Date, Execute SQL Task: Executing the query "exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output" failed with the following error: "No value given for one or more required parameters.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Compare Last File Create Date
Warning: 0x80019002 at GLImport: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "MyPackage.dtsx" finished: Failure.
When the above is run tbl_ImportFileCreateDate does not get updated, so it's failing at some point when calling the procedure.
When I run this in debug mode with this SQL statement ... exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output ... the tbl_ImportFileCreateDate table gets updated. So I know that data piece is working, but then it fails with the following message.
SSIS package "MyPackage.dtsx" starting. Information: 0x4004300A at Import data from flat file to tbl_GLImport, DTS.Pipeline: Validation phase is beginning.
Error: 0xC001F009 at GLImport: The type of the value being assigned to variable "User::IsNewFile" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
Error: 0xC002F210 at Compare Last File Create Date, Execute SQL Task: Executing the query "exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output" failed with the following error: "The type of the value being assigned to variable "User::IsNewFile" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object. ". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Task failed: Compare Last File Create Date
Warning: 0x80019002 at GLImport: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "MyPackage.dtsx" finished: Failure.
The IsNewFile global variable is scoped at the package level and has a Boolean data type, and the Output parameter in the stored procedure is defined as a Bit. So what gives?
The "Possible Failure Reasons" message is so generic that it's been useless to me. And I've been unable to find any examples online that explain how to do what I'm attempting. This would seem to be a very common task. My suspicion is that one or more of the settings in that Execute SQL Task node is bad. Or that there is some cryptic, undocumented reason that this is failing.
This should be a fairly simple question. It's based on this error message:"Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0." I get this when executing a stored procedure upon processing a form. This error happens when I intentionally provide input to the stored procedure that I know should cause it to error out. I catch the exception, and it contains the error message, but it also contains the above message added on to it, which I don't want. I won't post the entire stored procedure. But I'll list a digest of it (Just those lines that are significant). Assume that what's included is what happens when I provide bad input:BEGINBEGIN TRYBEGIN TRANSACTION RAISERROR('The item selected does not exist in the database.', 16, 1); COMMIT -- This won't execute when the RAISERROR breaks out to the CATCH block END TRY BEGIN CATCHROLLBACKDECLARE @ErrorSeverity INT, @ErrorMessage NVARCHAR(4000)SET @ErrorSeverity = ERROR_SEVERITY()SET @ErrorMessage = ERROR_MESSAGE() RAISERROR(@ErrorMessage, @ErrorSeverity, 1) END CATCH END Okay, so that works fine. The problem is when I execute this with an SqlCommand object, on which I've opened a transaction. I won't include the entire setup of the data (with the parameters, since those seem fine), but I'll give my code that opens the connection and executes the query: con.Open(); SqlTransaction transaction = con.BeginTransaction(); command.Transaction = transaction; try { command.ExecuteNonQuery(); transaction.Commit(); } catch (Exception ex) { transaction.Rollback(); } finally { con.Close(); } I'm calling the stored procedure listed above (which has its own transaction), using a SqlCommand object on which I've opened a transaction. When there is no error it works fine. But when I give the stored procedure bad data, it gives me that message about the transaction count. Is there something I need to do in either my SQL or my C# to handle this? The entire message found in the Exception's Message is a concatenation of the message in my RAISERROR, along with the transaction count message I quoted at the beginning. Thanks, -Dan
I have SQLExpress and SQL2005 both installed on my W2K3 server. I can access my databases with SQL2005 without problems.
As far as my memory serves me, I have not made any manual changes to the SQLExpress setup - nor will I as my users will have no knowledge as to how to - it must be an out-of-the-box solution.
I am using VS2005 to create a Windows Form project. I used VS2005 IDE to create a database as MyBookings.mdf . I have, in the IDE successfully created a table. This is all using SQLExpress.
I now wish to access the database and have the following as my connection string:
The following error message is generated when I try to access the db from within my program:
System.Data.SqlClient.SqlException:A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)
The SQL log enties for my past several tries are:
007-03-20 09:57:50.92 Logon Cannot attach the file 'MyBookings.mdf' as database 'MyBookings'. [CLIENT: <local machine>] 2007-03-20 09:58:13.92 spid51 Error: 5105, Severity: 16, State: 2. 2007-03-20 09:58:13.92 spid51 A file activation error occurred. The physical file name '.MyBookings.mdf' may be incorrect. Diagnose and correct additional errors, and retry the operation. 2007-03-20 09:58:13.92 Logon Error: 1832, Severity: 20, State: 1. 2007-03-20 09:58:13.92 Logon Cannot attach the file '.MyBookings.mdf' as database 'MyBookings'. [CLIENT: <local machine>]
I have spent 2 days researching this and have come up with no solution.
I have an Execute SQL Task that returns a Full Rowset from a SQL Server table and assigns it to a variable objRecs. I connect that to a foreach container with an ADO enumerator using objRecs variable and Rows in first table mode. I defined variables and mapped them to the columns.
I tested this by placing a Script task inside the foreach container and displaying the variables in a messagebox.
Now, for each row, I want to write a record to an MS Access table and then update a column back in the original SQL Server table where I retreived data in the Execute SQL task (i have the primary key). If I drop a Data Flow Task inside my foreach container, how do I pass the variables as input to an OLE DB Destination on the Data Flow?
Also, how would I update the original source table where source.id = objRects.id?
Thank you for your assistance. I have spent the day trying to figure this out (and thought it would be simple), but I am just not getting SSIS. Sorry if this has been covered.
I have a asp.net 2.0 web application. In one of the modules I have following scenario. In the UI there are 3 pages to take the input from the user. On the 1st page, we are inserting data in the master table and with the same we are beginning new sql transaction with the isolation level READ UNCOMMITTED. so that uncommited master table data can be read. Master table has also has 4 detail tables. Then the user is navigated to 2nd and then to 3rd page where the user can enter multiple records in the detail tables. And only after finishing on the 3rd page, the transaction is commited. I want to ask that is it the right way of doing this kind of functionality? And is there any other way of doing this?
I have a windows forms application that I would like to put in production on several machines. Each machine will contain it's own data and does not need to be networked.
I can use my local connection right now to access the data, but I would like to change my connection so that it will work when installed on a client's machine.
My Current connection string looks like this:
Data Source=.SQLEXPRESS;AttachDbFilename="C:Documents and SettingsMYNAMEMy DocumentsVisual Studio 2005ProjectsInterimApplicationInterimApplicationInterim.mdf";Integrated Security=True;User Instance=True
I believe I could use something like this:
Dim connectionString as string = "Data Source=" & Environment.GetFolderPath(Environment.SpecialFolder.ProgramFiles) & "/InterimApplication/data/Interim.mdf;Integrated Security=True;User Instance=True"
However, I am not sure. Also, if the client does not install to the default directory, then this may cause the application to not work properly.
Lastly, I am not sure if the client machine needs any additional software on their computer in order for the application to be capable of connecting to the data source.
Any recommendations, ideas, comments, or general help is greatly appreciated.
I am new to SSIS so I'm sure this is easy to do but I can't seem to figure it out. I created a SQL task that creates a result set. I would like to loop through each result of the result set and insert it into another table.
I'm not sure how to map the result set as input to the second SQL task that will do the insert. I can put the variable in the parameter mapping but I don't know what the SQL should look like to insert this into my table. Each row of the result set has five columns. Normally I have something that looks like INSERT...Values(?,?,?,?,?) but because my input parameter is just one result set object this doesn't work.
Can somebody please help me with compiling my insert trigger below. I am fairly new to SQL server 2000 and I am having troubles with using variables in insert triggers. The trigger that I am creating will basically update another table based on a certain criteria that is not specified below. I am hoping to first get my trigger to work then apply the criteria on when to fire afterwards. I just need help with being able to store values in my declared variables for insert into another table. Thanks in advance for everyones help.
Use database_testing
IF EXISTS (SELECT name FROM sysobjects WHERE type = 'TR' AND name = 'Trigger_Name') DROP TRIGGER Trigger_Name GO
CREATE TRIGGER Trigger_Name ON [trigger_table] FOR INSERT AS Declare @resource_id int = inserted.resource @type = varchar(100) = inserted.type @date_logged (datetime) = inserted.creation_date @created varchar(100) = inserted.username
I am new to scripting in general and I've run into an issue when attempting to write a VB variable to a database table in SQL Express. I am trying to record the value of the variable to the db, but it does not appear that the value is being passed to SQL. If I hard code the values in the SQL statement it works fine. Can someone explain what I'm doing wrong accomplish this? My code is below. Thanks in advance. file.aspx <asp:SqlDataSource ID="SqlDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:SqlConnectionString %>" SelectCommand="SELECT * FROM [Table]" InsertCommand="INSERT INTO [Table] (field1, field2) VALUES (& variable1 &, & variable2 &);" > </asp:SqlDataSource> file.aspx.vb Protected Sub Button_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button.Click Dim variable1 As String = FileUpload.FileName Dim variable2 As String = Date.Now Dim path As String = Server.MapPath("~/directory/) If FileUpload.HasFile = True Then Try SqlDataSource.Insert() FileUpload.PostedFile.SaveAs(path & _ FileUpload.FileName) End Try
I was wondered what the most efficient way to do the following in MS SQL (2000, 2005, and above): Psudo Code: DECLARE @var1, @var2, @var3 VARCHAR(100) SET @var1, @var2, @var3 = (SELECT var1, var2, var3 FROM Table WHERE [ID] = @someID) <-- Returns only one row Right now I'm making a call for every variable. I should play around with temp tables but seems like allot of overhead code just to get 3 values. I'm thinking there's a simple way to do this. Thanks for any ideas
I have a flat file of records in csv format. I want ti create a dts that can be executed from asp.net using vb.net to load data from .csv file into sql server 2000 table.
Now what further i want that this DTS must have a parameter mapped to file name of .csv. so when i execute this dts from asp.net web application i will set the file path to this variable and then execute it on server.
So that meanz dts stays on server all it require before execution a new file path to load data from file that is already uploaded on server in a directory.
if any one have any idea to how to create such a dts and secondly how to execute in asp.net.