In Visual Web Developer I
have created a data input form based on the documentation I found in the .NET
Framework Class Library (SqlDataSource.InsertCommand Property).
Originally my form contained 4 textboxes
(FirstName, LastName, Phone, Email) and worked fine.
All this data is nvarchar string data.
When I added a checkbox I
get an error that “String
was not recognized as a valid Boolean�
when the checkbox is checked.
When the checkbox is unchecked the data is input without a problem.
This is a bit data field in the table.
Here is my VB code, Insert
Parameters for my SqlDataSource1, and button code:
<script
runat="server">
Private Sub InsertData(ByVal
Source As Object,
ByVal e As
EventArgs)
I suspect that the problem
is with the VB code which will only accept string input data, but don’t know
how to fix it.
Any thoughts on how to
fix this problem?
Thanks in advance for
any help provided.
Where interid in ('comp1', 'comp2', 'comp4', 'comp5')
what would be the best way to using these scripts pull the data to my testDW and not have duplicate data issues?
I was thinking of using a staging DB on the GP cluster and then building an import data package to run nightly. the issue i had was how do i avoid duplicate data ?
Hello All, I am migrating data from one database to another. I am using Multicast to seperate (legal street,legal mail and legal city) and (mail_street,mail_state,mail_zip,mail_city) also later after UNION of the above I am doing two lookups as I had to get contact ID and Customer ID from other two tables. In UNION i am matching (Mail street legal street) and so on.
I am getting double the data in the output. my input data is 1000000 and im gettin 2000000.
Hello everyone, I am new to mssql so please excuse all my silly questions.
I can create databases and tables. This is fairly easy, now I am having trouble with inputting data into the database using the data entry browser (or whatever it is called to enter data directly into a table).
I usually get an error when trying to submit the entry, firstly I have a "id" field which I think I have set to auto increase (Identity is ticked, seed and increment is set to 1).
when I go to add data all fields have "Null" in them and from what I understand it allows the fields to be empty, except the primary key which is the id field in this case and I expect that to be filled in automatically like with mysql but isn’t.
Can some kind person instruct or direct me somewhere on how to enter data into a table without it failing.
Hi there. This is only my second post ever- so please bear with me.
First, I am using SQL Server 2000.
I have built a DTS package using an Oracle ODBC and the following query;
select pv.segment1 vendor_nbr, pv.vendor_name,pv.attribute4 , pv.creation_date from po.po_vendors pv , po.po_vendor_sites_all pvs where pv.vendor_id = pvs.vendor_id(+) operating unit AND trunc(pv.creation_date) = trunc(sysdate)
This DTS package is not always run on the same day the records are created. So......
What I want to do is have the dts package ask the user for the date they are looking for-
I know I need to probably use a parameter, but I have been unable to figure out how to do this in a DTS pkg. I have several reSQL reference books and I don't even know what to search for in Books online. Could anyone help??
hi there.. I have write a code in the submit button using vb code so that when people key in their email.. it will be saved in my created database called test.mdf. but when i debug it, i tried to write an my email address in the textbox in my website but when i click at the submit button, the web page display the email was not enter in my database. here is the code, can anybody help me. i'm really lost here.. Protected Sub SubmitButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Dim TestDataSource As New SqlDataSource()TestDataSource.ConnectionString = ConfigurationManager.ConnectionStrings("TestConnectionString1").ToString()
TestDataSource.InsertCommandType = SqlDataSourceCommandType.Text TestDataSource.InsertCommand = "INSERT INTO Email(EmailAddress, IPAddress, DateTimeStamp) VALUES (@EmailAddress,@IPAddress,@DateTimeStamp)" TestDataSource.InsertParameters.Add("EmailAddress", emailAddressTextBox.Text) TestDataSource.InsertParameters.Add("IPAddress", Request.UserHostAddress.ToString())TestDataSource.InsertParameters.Add("DateTimeStamp", DateTime.Now()) Dim rowsAffected As Integer = 0
Try rowsAffected = TestDataSource.Insert() Catch ex As Exception Server.Transfer("test_problem.aspx")
Finally
TestDataSource = Nothing
End Try
If rowsAffected <> 1 ThenServer.Transfer("test_problem.aspx") ElseServer.Transfer("Test_Confirm.aspx")
hi all , does SQL server able to input the date that is originally from 2/6/2004 11:07:46 AM to DD-MM-YY 06-02-04 and store in the DB. For example , this is the SQL insert query
INSERT INTO ADMIN ( ID , NAME , DATE ) VALUES ( 'A001' , 'karen' ,'2/6/2004 11:07:46 AM')
the date will automatically converted to 06-02-04 and store in DB .
Does it need to use SQL Rule or user-defined data type.
hi , i am new to this. how should i update the user input values into sql server database? i am using asp.net and c# FIELDS are- userId, name, description, startTime, endTime, audiencePassword, presenterPassword
i know it must be simple...but i haven't worked on this before.
This is very similar to the post: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1901076&SiteID=1
I am trying to do this. Basically, if the field I am checking contains a space - not an empty string - I want to assign it an empty string so it will get converted to NULL when it hits the SQL destination. The space character from the source is causing the rows to fail I want to force a NULLin the database as a "workaround" from something I am working on.
I thought I had this set up correctly, but it is not working. I have the field in question as an input field set to ReadWrite
If Row.[COLNAME].ToString = " " Then Row.[COLNAME] = "" End If
I am getting the following error:
[Assign District Code [12469]] Error: System.NullReferenceException: Object reference not set to an instance of an object. at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer) at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper90 wrapper, Int32 inputID, IDTSBuffer90 pDTSBuffer, IntPtr bufferWirePacket)
Firstly the disclaimer: im a total SSIS bunny. Ok, with that out of the way... What i want to do is have data read from an MSMQ message (which will be a simple XML message) and have it transformed and inserted into a simple table. Sound simple, but i cant work out how to do it.
I started a new Integration Services Project and dropped a Data Flow task on the Control flow tab, then double-click it to go to the data flow tab. I have added a MSMQ connection manager which points at my private queue and i can hit the test button and it sees it tests ok. How do i wire that into my data flow?
On my dataflow tab i have a SQL Server Destination (set to the local SQl server instance) and ive been trying a DataReader source and try setting the Advanced Editor "Connection Manager" column on IDBConnection row to point to my MSMQ connection manager. I get some error "cannot aquire a managed connection from the run-time connection manager". How do i get a Data Flow source which reads the XML message off my MSMQ connection manager??
On the Control Flow tab you can add a Message Queue Task and i can bind it to my MSMQ connection manager and set it up as a recieve task etc, but how do i use that?
I am using the flat file connection manager. I read in a set of flat files and copy them into a staging database table. I want to ignore anything in the text file past column 266. I can't get this to work correctly. I can't define the last column to end at 266 and ignore the rest. I tried adding an extra column but that did not work either.
Maybe this is the wrong formum but I've got a question for which you probably have the answer, i hope.
Situation ------------ John is member of Group_A and Group_B Bill is member of Group_B and Group_C Allison is member of Group_A and Group_E
How can I create a query to input Allisons username into table 1 and groupmembership into table 2. Also updating the relationship within table3 must be done automaticaly. I want to avoid duplicate records. The final situation I want is given in red text.
The relationships between the tables are as follows ------------------------------------------------------------- Table1 (PK)ID-Userinfo [ONE] <------------> [MANY] Table3 ID-Userinfo Table3 (PK)ID-GroupInfo [MANY] <------------> [ONE] Table2 (PK)ID-GroupInfo
Table1: UserInfo ------------------------------ (PK)ID-Userinfo UserName 1 John 2 Bill 3 Allison
Each day I receive a file with a different name. For example, the name is filename_mmddyyyy.txt where filename_ stays constant and mmddyyyy is the date of the file. The file is always in the same format.
I want to build an SSIS where I pass it this file name. I can write a script to generate the correct file name. How do I build the SSIS so it can accept the input parameter and find the correct file to process?
I've got a time series of the number of new customer subscriptions, which is the target attribute to predict. The number of subscriptions depends on various marketing activities, such as mailings, which are known within the time series for the past.
If I train an ART (MS Autoregression Tree), it learns the trend pattern as well as the correlations between the marketing activities and the target (cross-correlations), right? What I would like to do is, providing the model with some marketing activity planning for the future and let the model predict the number of new subscriptions based on a) the past trend pattern and b) the future activities.
Unfortunately a time series algorithm does not provide some kind of scoring for input data.
What would be the best approach to solve this problem? How about linear regression? But how to train a regression model with trend patterns?
One people created a word input file (15 pages, including check boxes, text boxes, drop down lists...). Is it possible to save data in word input file to SQL table?
While recently working with several mining models, I came across something that struck me as pretty odd - and I'm hoping to find an explanation for the behavior.
Consider the following setup:
A single table in the relational database represents the only case table A single, continuous column is the predictable A mining structure has been created
The mining structure contains a single model, based on the MS Decision Trees algorithm Input columns were selected for the model via the BI Studio wizard (i.e., those provided via the "Suggest" button) The structure has been fully processed Now, the interesting parts:
I view the scatterplot for the mining model, under the Mining Accuracy Chart tab Back on the Mining Structure tab, I delete one of the input columns I add the same column back into the structure The structure is fully processed again When I view the scatterplot for the mining model, under the Mining Accuracy Chart tab, a different set of data points are presented for the model predictions A different set of decision trees under the Mining Model Viewer tab confirms thisHow could different patterns have been found this second time around, even though all of the input columns were the same (as well as the training cases)?
(Note: I encountered this situation while creating a new mining model that was identical to an existing one. Even though the models received the exact same inputs and training cases, they yielded different results. I was able to reproduce the behavior by using steps 1-6 above, though.)
Can someone provide some insight on this behavior, or some kind of explanation of what may be happening?
Im trying to determine the best way to store data gathered from a form that a user will fill out online. The form is dynamic and is customized at run time based on group-specific criteria. The end result is a form that might have 3 extra text boxes, 2 extra sets of radio buttons and a freeform textbox, whereas for another group, there might be a slightly different set of input fields. Now comes the issue of storing this data. Since the fields can be somewhat dynamic, it could get tricky to define table columns for each possible input field. So Im considering storing the data as xml. Has anyone else had to build custom forms and ended up storing the data as xml ?
I need to loop the recordset returned from a ExecuteSQL task and transform each row using a Data Conversion task (or a Script Task).
I know how to loop the recordset returned by an ExecuteSQL task:
http://www.sqlis.com/59.aspx
I loop the returned recordset (which is mapped to a User variable of type System.Object) and assign the Variable Mappings in the ForEach Loop to different user variables which map to the Exec proc resultset (with names and data types).
I assume to now use these as the Available Input columns for the Data Conversion task, I drag a Data Flow task inside the For Each Loop container and double-click it, then add a Data Conversion task.
But the Input columns (which I entered in the Variable Mappings in the ForEach Loop containers) dont show up in the Available Input columns of the Data Conversion task.
How do I link the Variable Mappings in the ForEach Loop containers from the recordset returned by the Execute SQL Task to the Available Input columns of the Data Conversion task?
.......................
If this is not possible, and the advice is to use the OLEDB data flow as the input for the Data Conversion task (which is something I tried too), then the results from an OLEDB Command (using EXEC sp_myproc) are not mapped to the Available Input columns of the Data Conversion task either (as its not an explicit SQL Statement and the runtime results from a stored proc exection)
I would like to use the ExecuteSQL task to do this as the Package is clean and comprehensible. Which is the easiest best way to map the returned results from a Stored proc execution to the Available Input columns of any Data Flow transformation task for the transform operations I need to execute on each row of data?
[ Could not find any useful advice on this anywhere ]
I have a delimited text file with 650+ columns. The sum of the column lengths of a single row, if fully populated, exceeds 30K bytes. The "killer" fields lengthwise are the "Description" fields. If they were removed from the input file, the remainig columns would occupy about 5000 bytes, which is within SQL max row length.Â
Can SSIS be used to created these two tables? (one without  description fields, the other with those field but arranged vertically in the table rows).
The fundamental issue is I can not import a single file row into a sql table because that row length could exceed the max byte count for a row.
Hi, I was wondering how I can complete a column (which doesnt have an input one) with data. For example:
I have a sql query which bring data of 3 columns
ID | FISRT NAME | LAST NAME 1 MIKE MORGAN 2 SARA JOHANES
So, I will insert that data in a FLAT FILE CONNECTION MANAGER, which I configured with 3 columns and I did the corresponding mapping in the FLAT FILE DESTINTATION.
Now, If I add one more column in the FLAT FILE CONNECTION MANAGER, I will not have it mapped to a input one, obviously. So, what I need is to add one more column to the flat file destination and complete it with zeros values in it.
Probably I can solve this part by introducing a DERIVED COLUMN and there I can configure the zeros that I want to add to the column. But I'm not sure if I can do that without having a input column. So, the question will be, how can I add one column to a flat file which doesnt have a input and introduce any value that I want to it? Hope I was clear Thanks for your help.
I was trying to load data using SSIS, Data Flow Task, OLE DB Source, source was a view to a OLE DB Destination (SQL Server). This view returns 420,591 rows from Query Analyzer in 21 seconds. Row length is 925. When I try to executed the Data Flow Task from SSIS, I had to stop the process after 30 minutes, because only 2,000 rows had been retrieved. I modified the view to retun top 440, 000 and reran. This time all 420, 591 rows were retrieved and written in 22 seconds. Next, I tried to use a TOP 100 Percent. Again, only 2,000 rows were return after 30 minutes. TempDB is on a separate SAN Raid group with 200 gig free, Databases on a separate drive with 200 gig free. Server has 13 gig of memory and no other processes were executing.
The only way I could populate the table was by using an Execute SQL Task and hard code an Insert into table selecting data from the view (35 seconds) from SSIS.
Have anyone else experience this or a similar issue? Anyone have a solutionexplanation?
Table is created. I can SELECT * FROM accepted and see my column names.
Then I try to BCP into the table using:
C:>BCP sales..east in C:Resultsaccepted.txt -t -f C:cpformataccepted.fmt -Usa -Ppwd
I get this error:
Starting copy... SQLState = 22001, NativeError = 0 Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation SQLState = 22001, NativeError = 0 Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
and so on......
In the .fmt file I've tried "", " ", " " and everthing I could think of as a delimiter. Still no luck. I've tried almost every switch available to both OSQL and BCP. The data I am trying to BCP is a SQL result so I don't think any special delimiters are placed. I've tried not using the .fmt file and using the prompts but still no luck. Data is CAST in the query and doesn't excede 45 characters. Hope I've explained my problem well enough.
Hi,I need to input Chinese character into the table of the database. I did try to install/run both Chinese/English version of Visual Studio into Chinese/English version of Server 2003 but it still didn't work.Please help !stephen
I have two textboxes on the page...shipdate and duedate. When the page loads, shipdate has today's date loaded and duedate has a date that's 28 days later than today. When I change the dates and submit it's not updating in the database instead I'm getting the two dates in pageload. What am I doing wrong?Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
ShipDateTxt.Text = Today() DueDateTxt.Text = DateAdd(DateInterval.Day, 28, Today()) End SubProtected Sub LoanRequest_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles LoanRequest.Click Dim conn As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("TrainUserConnectionString").ConnectionString)Dim cmd As New Data.SqlClient.SqlCommandWith cmd .Connection = conn .CommandType = Data.CommandType.StoredProcedure .CommandText = "UpdateloanerInfo".Parameters.AddWithValue("@requestorid", Integer.Parse(Request.QueryString("requestorid"))) .Parameters.AddWithValue("@shipdate", ShipDateTxt.Text).Parameters.AddWithValue("@duedate", DueDateTxt.Text) End With Here's updateloanerinfo stored procedure: @requestorid int,@shipdate datetime,@duedate datetime AS update LibraryRequest set [shipdate] = @shipdate,[duedate] = @duedate Where requestorid=@requestorid
Here is what I am trying to do: Every month we need to import a fixed format text file into one of our tables. The format and location of the file is same every month except for the name. I want to create a DTS package to import it and call this DTS package first thing in a stored procedure(after which I do some processing with this imported data). I want to create the filename in my stored procedure and then call this DTS package to import it.
I am usig DTS as the interface is so much easier and want to avoid bcp :-)
I am just learning SQL Server and I am stuck with a few things. I was wondering if you can define input masks for columns in the database in SQL Server. I have used this feature many times in Access and on the front end in Oracle Forms. Is there such a thing in SQL Server?? Is it just called something different.....? I will be trying to format telephone numbers and dates. Thank you for you time.