Storing Data As Varchar, But Keeping Breaks?
Jun 11, 2007Is it possible to store data as varchar and keep line breaks, spaces, indents, and all that stuff intact?
Thanks again for all the help.
ADVERTISEMENT
Storing And Retrieving Line Breaks/newlines From Multi-line Textbox (C#)
Aug 31, 2007I hope I'm posting this in the correct forum (forgive me if I'm not) since I'm not sure if this is an issue with inserting an item into a db or the processing of what I get out of it. I wrote a basic commenting system in which someone my post a comment about something written on the site. I wanted to keep it very simple, but I at least want the ability for a user to have newlines in their comment without having to hardcode a <br /> or something like that. Is there a way for me to detect a newline if someone, for example, is going to their next paragraph?
Let me know if you need a better explanation.
Thanks in advance!
Which Is Better? Storing Data In The Database OR Storing It In The File System
Dec 29, 2006Hello there,I just want to ask if storing data in dbase is much better than storing it in the file system? Because for one, i am currenlty developing my thesis which uploads a blob.doc file to a web server (currently i'm using the localhost of ASP.NET) then retrieves it from the local hostAlso i want to know if im right at this, the localhost of ASP.NET is the same as the one of a natural web server on the net? Because i'm just thinking of uploading and downloading the files from a web server. Although our thesis defense didn't require us to really upload it on the net, we were advised to use a localhost on our PC's. I'll be just using my local server Is it ok to just use a web server for storing files than a database?
View 6 Replies View RelatedKeeping Dirty Data
Jan 24, 2008This may be more of a data design question and not an ssis question, but figured folks here could have a good idea.....the organization I'm in has the business need of collecting data from outside organizations and tracking what data is bad and what data is good. When I say bad data I mean everything from things outside of range to absolute *** - characters in integer columns, integers in character columns, special characters, etc. The data comes in in the form of flat file so it's a free for all until it hits ssis & the db engine.
Eventually of course they work to get the data corrected at the source & resubmitted but in the meantime, they have the legitimate need of not only pushing the data into the database (dirty or not), but keeping all the bad stuff. I can't in good conscience make everything a varchar to catch everything - that would go against the database gods. IMO - I still must make an integer be an integer , characters are characters, etc. But what do I do with the junk? Any thoughts?
Problems Moving Data Over 8000k In DB2 Varchar Column Into SQL Server Varchar(max) Using SSIS
Nov 20, 2007
I have looked far and wide and have not found anything that works to allow me to resolve this issue.
I am moving data from DB2 using the MS OLEDB Provider for DB2. The OLEDB source sees the column of data as DT_TEXT. I setup a destination to SQL Server 2005 and everything looks good until I try and run the package.
I get the error:
[OLE DB Source [277]] Error: An OLE DB error has occurred. Error code: 0x80040E21. An OLE DB record is available. Source: "Microsoft DB2 OLE DB Provider" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
[OLE DB Source [277]] Error: Failed to retrieve long data for column "LIST_DATA_RCVD".
[OLE DB Source [277]] Error: There was an error with output column "LIST_DATA_RCVD" (324) on output "OLE DB Source Output" (287). The column status returned was: "DBSTATUS_UNAVAILABLE".
[OLE DB Source [277]] Error: The "output column "LIST_DATA_RCVD" (324)" failed because error code 0xC0209071 occurred, and the error row disposition on "output column "LIST_DATA_RCVD" (324)" specifies failure on error. An error occurred on the specified object of the specified component.
[DTS.Pipeline] Error: The PrimeOutput method on component "OLE DB Source" (277) returned error code 0xC0209029. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
Any suggestions on how I can get the large string data in the varchar column in DB2 into the varchar(max) column in SQL Server 2005?
The Data Types Varchar And Varchar Are Incompatible In The Modulo Operator
Jan 4, 2008I am trying to create a store procedure inside of SQL Management Studio console and I kept getting errors. Here's my store procedure.
Code Block
CREATE PROCEDURE [dbo].[sqlOutlookSearch]
-- Add the parameters for the stored procedure here
@OLIssueID int = NULL,
@searchString varchar(1000) = NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
IF @OLIssueID <> 11111
SELECT * FROM [OLissue], [Outlook]
WHERE [OLissue].[issueID] = @OLIssueID AND [OLissue].[issueID] = [Outlook].[issueID] AND [Outlook].[contents] LIKE + ''%'' + @searchString + ''%''
ELSE
SELECT * FROM [Outlook]
WHERE [Outlook].[contents] LIKE + ''%'' + @searchString + ''%''
END
And the error I kept getting is:
Msg 402, Level 16, State 1, Procedure sqlOutlookSearch, Line 18
The data types varchar and varchar are incompatible in the modulo operator.
Msg 402, Level 16, State 1, Procedure sqlOutlookSearch, Line 21
The data types varchar and varchar are incompatible in the modulo operator.
Any help is appreciated.
Purging A Database Data While Keeping The Structure Intact
Oct 15, 2006Is it possible to purge all records in the database while retaining the the table structures. Even better yet, could I do it on a table by table basis? If I simply delete all the records the identities for the tables do not revert back to 1.
View 2 Replies View RelatedKeeping Text Format After Stored In The Data Base.
Jan 14, 2004I want the users of my site to be able to write a couple of paragraphs, save it to SQL Server and then have it read back and look the same. I don't want the users to be able to add any html to the text they submit. I just want them to be able to seperate or indent their paragraphs.
At one point, I had a textbox that saved text to the server and read it back the same way it was originally inputed. However, I can't figure our what I had done to make it work.
Any ideas?
Thanks
Transact SQL :: Splitting Column Value While Keeping Existing Data
Jun 22, 2015Currently I have a column with multiple postcodes in one value which are split with the “/” character along with the corresponding location data. What I need to do is split these postcode values into separate rows while keeping their corresponding location data.
For example
PostCode Latitude Longitude
66000/66100 42.696595 2.899370
20251/20270 42.196471 9.404951
Would become
PostCode Latitude Longitude
66000 42.696595 2.899370
66100 42.696595 2.899370
20251 42.196471 9.404951
20270 42.196471 9.404951
SQL Server 2012 :: Splitting Column Value While Keeping Existing Data?
Jun 22, 2015Currently I have a column with multiple postcodes in one value which are split with the “/” character along with the corresponding location data. What I need to do is split these postcode values into separate rows while keeping their corresponding location data.
For example:
PostCodeLatitudeLongitude
66000/6610042.6965952.899370
20251/2027042.1964719.404951
Would become
PostCodeLatitudeLongitude
6600042.6965952.899370
6610042.6965952.899370
2025142.1964719.404951
2027042.1964719.404951
how this can be done?
Reporting Services :: Row Delimiter In Data Breaks CSV Export In SSRS?
Jul 10, 2015Currently I'm working on a few SSRS reports which will be exported to CSV.
In some of the fields e.g. "Description" the data can contain carriage return and line feed characters (CR LF). SO when that happens the csv export breaks the line and rest of the data from that fields goes into the net line. This creates a few undesirable line breaks.
I can always replace these characters from that fields.
Is there a way I keep those characters in the field and still my csv export come up fine.
Page Breaks In PDF Don't Match Page Breaks In HTML
Mar 7, 2007I have several nested sub-total groups that are all coded to "page break at end". I don't know how to tell RS to suppress the page break if one sub-total immediately follows another. The initial HTML output seems to be smart enough to do this suppression on its own, but when I export to PDF, all the page breaks appear. This causes several pages at the end of the report with one line per page. Perhaps, if I could explicitly suppress these page breaks, the PDF would render properly. How can I do this?
View 1 Replies View RelatedStoring Data
Jun 20, 2006Hi again.
How do I store in SQL SERVER with 2 decimal places?
I set Data Type to Decimal with Scale set to 4 and Precision 19. But this doesn't work.
Any ideas? Thanks.
Help Me Out For Storing More Data
Jul 16, 2006CREATE TABLE [dbo].[blogs] (
[blog_title] [nvarchar] (500) ,
[blog_desc_full] [varchar] (8000) ,
[blogger_name] [nvarchar] (100) ,
[mailid] [nvarchar] (100) ,
[blogid] [numeric](10, 0) IDENTITY (1, 1) NOT NULL ,
[blog_desc] [nvarchar] (125) ,
[cat_name] [varchar] (100) ,
[b_url] [varchar] (250) ,
[b_date] [datetime] NULL ,
[author] [nvarchar] (250) ,
[approval] [char] (1)
) ON [PRIMARY]
GO
using this script i have created my blog table.
and a procedure given below. i am using to insert data in it.
CREATE PROCEDURE SP_BlogAdd
@blog_title nvarchar(500),
@blog_desc_full varchar(8000),
@blogger_name nvarchar(100),
@mailid nvarchar(100),
@blog_desc nvarchar(125),
@cat_name varchar(100),
@b_url varchar(250),
@author nvarchar(250)
AS
INSERT INTO blogs(blog_title, blog_desc_full, blogger_name, mailid, blog_desc, cat_name, b_url,author)
VALUES(@blog_title ,@blog_desc_full ,@blogger_name,@mailid ,@blog_desc ,@cat_name ,@b_url,@author)
GO
now, the problem i m facing is.
i am using varchar datatype for [blog_desc_full] [varchar] (8000).
i want more than this size to store data in it.
please give me some detailed code.
i tried text datatype but i didnt succeed. how to use text datatype.
i replaced with text datatype.but in length i couldnt type. it shows only 16.
Please help me out.
regards,
ASIF
Help Me Out For Storing More Data
Jul 16, 2006CREATE TABLE [dbo].[blogs] (
[blog_title] [nvarchar] (500) ,
[blog_desc_full] [varchar] (8000) ,
[blogger_name] [nvarchar] (100) ,
[mailid] [nvarchar] (100) ,
[blogid] [numeric](10, 0) IDENTITY (1, 1) NOT NULL ,
[blog_desc] [nvarchar] (125) ,
[cat_name] [varchar] (100) ,
[b_url] [varchar] (250) ,
[b_date] [datetime] NULL ,
[author] [nvarchar] (250) ,
[approval] [char] (1)
) ON [PRIMARY]
GO
using this script i have created my blog table.
and a procedure given below. i am using to insert data in it.
CREATE PROCEDURE SP_BlogAdd
@blog_title nvarchar(500),
@blog_desc_full varchar(8000),
@blogger_name nvarchar(100),
@mailid nvarchar(100),
@blog_desc nvarchar(125),
@cat_name varchar(100),
@b_url varchar(250),
@author nvarchar(250)
AS
INSERT INTO blogs(blog_title, blog_desc_full, blogger_name, mailid, blog_desc, cat_name, b_url,author)
VALUES(@blog_title ,@blog_desc_full ,@blogger_name,@mailid ,@blog_desc ,@cat_name ,@b_url,@author)
GO
now, the problem i m facing is.
i am using varchar datatype for [blog_desc_full] [varchar] (8000).
i want more than this size to store data in it.
please give me some detailed code.
i tried text datatype but i didnt succeed. how to use text datatype.
i replaced with text datatype.but in length i couldnt type. it shows only 16.
Please help me out.
regards,
ASIF
Storing Formatted Data
Jan 22, 2007I have a table for articles that I want as
the basis for a blog. I have a field of description where the actual
article will go, I have only ever really
used tables to put in 'blocks' of text, how would I go about storing/displaying
data that is in my database table in a more formatted way, for example line
breaks, indents etc?
Data Storing Problem......
May 11, 2007Hi everyone,
I have some data which is in korean Language and i want to store that data in sql server 2000 table's row.But i am not able to store it.
When i try to store it then it display me square box in table.
Does anybody have any idea about this matter?
Plz reply me back as soon as possible.
Thanks
-------Nimesh Patel
Storing Vs Calculating Data
Dec 19, 2007Hi, I'm in the process of creating a database table and was wondering if it's better to store calculated values or recalculate them each time. So for example, I am creating a table that stores articles and then a table to store the pages to the article. If a new page is added should I update the pages field in the articles table or should I calculate the number of pages for an article when it's queried? Thanks,John
View 3 Replies View RelatedStoring Historical Data?
Apr 7, 2008From what I've read this is called 'slowly changing dimensions'. Bassically the system I'm working on needs to store the history of certain data so that at any time a user can look up an old project and view it exactly as is, even though the associated parts might have had certain changes over time. From what I can tell Type 2 ( current and historical records are stored in the same table) seems to be the most popular. Type 4 (current records in one table and historical records in a seperate history table) seems like it would also work but I've been unable to find any articles comparing the two. Does anybody have any info on the dis/advantages of one v.s. the other?
View 3 Replies View RelatedWhat Is The Use Of Storing Data In 2 Database ? And How To Do That ?
Mar 5, 2008
Can I know answer of this question ? in detail explanation.
Storing Staging Data As XML In SQL 2000.
Aug 25, 2005Scenario:
We want to stage all data coming into our database from various
applications. We have a generic control where all data goes
through to get to the database.
We want have the data staged in a 'staging database'. My question
is, what would be the best way to store the staging database in the
database.
I'm thinking storing it as XML in a SQL column. The reason for
this is because the data could change from application to
application. It would be impossible to create a relational table
because maintenance would be a nightmare.
Has anyone ever done something like this. Would you save the node names in another table in SQL?
Any help would be appreciated as I'm new to XML.
Thanks,
ScAndal
Question About Storing Data In Tables
Dec 15, 2005I am trying to make my tables so that when data is entered using a formview even if a textbox is left blank there is still a value like for example NA entered into that field. Is that possible?
also in the column properties under general what is the Default value or binding?
Storing Time Duration Data
Nov 15, 2006i've got an excel spreadsheet generated everyday which stores the duration of talk time as 0:02:09
When i use the Integration Services Package to move the data into a table. I've used datetime column. It comes up as 1899-12-30 00:00:02:09.000
What's best practice to store duration as in a SQL Server table? or should i convert it to seconds.
Storing Out Put Of Data From Stored Procedure In A
Jul 5, 2007Hi
i m new in sqlserver databases
i need to know how to "store output of data from stored procedure in a text file "
suppose i have a stored procedure which has to cuculate some out put from some tables and in the end i want that all out put in comma delimited text file.
my databse name is check1
i need help please
thanks in advance
take care
bye
Storing Large Amounts Of Data
Mar 31, 2008Hi,
I was wondering if any one could help me, I need to store large amounts of data in my database, at present I have it set to nvchar (8000), I've looked around and noticed you can use text which stores up to 2 million, but is slow in displaying the information.
Any ideas or points in the right directions would be great.
Thanks
Storing Data In Image Field
Jul 20, 2005I have a very large array of floating point numbers which I am tryingtostore in an image type field. I am using the dataset class todirectly enter the data but am having trouble trying to get this typeinto the image field. I guess the question is how do I get a largefloating point array into an image (byte)field of a database using thedataset class for new row entry.Thanks
View 1 Replies View RelatedStoring BitmapSource Image Data Into SQL CE
Jan 30, 2008
Hi,
I'm storing thumbnails in SQL CE and accessing them from my WPF application. The save seems to take place ok and there's binary data in the image field however when it comes back out it appears to be corrupted somehow. Can someone have a look at the code below and verify I'm doing the conversion correctly before storing in my image field? Thanks!
- Roland
Code Snippet
private byte[] GetArrayOfPixels(BitmapSource bitmapsource)
{
Int32 stride = bitmapsource.PixelWidth * bitmapsource.Format.BitsPerPixel / 8;
Int32 ByteSize = stride * bitmapsource.PixelHeight * bitmapsource.Format.BitsPerPixel / 8;
byte[] arrayofpixel = new byte[ByteSize];
bitmapsource.CopyPixels(arrayofpixel, stride, 0);
return arrayofpixel;
}
//I assign the byte array returned to my datatable like so:
MediaAsset asset = new MediaAsset(){
Thumbnail =
new Binary(GetArrayOfPixels(Thumbnail as BitmapSource));
}
//then I insert it into my tables collection
myDB.MediaAssets.InsertOnSubmit(asset);
//and submit the change
myDB.SubmitChanges();
Am I not getting that pixel array correctly for storage?
Thanks for the help!
Roland
Transact SQL :: Nvarchar (max) Not Storing Whole Data
Nov 11, 2015select name from sampletext where id=1
Value under the column name where id=1 is:- uhfhdajkfahgfiasfhgafahsfiahfiahfsai......(upto 600000 characters)
The value under the name column is more than 600000 characters. When i am using the following logic it is storing upto 72000 characters, not reading the whole value under the 'name' column.
Code:-
declare @s1 nvarchar(max)
declare @substr nvarchar(max)
set @s1 = (select name from sampletext where id=1)declare @i int
declare @i int
DECLARE @j int
[Code] .....
Is there a way to get whole characters under a column instead of storing limited characters?
Storing Serialized Data In Sql Server CE????????
Feb 19, 2008Hi
is it possible to store a SQL CE command object in SQL Server CE database.
If so then what would be the datatype of such column????
thanx in advance
Storing Arabic Data In Db(sql Server 2005)
Oct 1, 2007Hi,
How to insert or store arabic data into a table in sql server 2005, also i want to retrieve it as arabic format.But now it insert (?????) like this.Please help me to find a solution.
Regards
Sabna S
Problem Storing Form Data Into Database
Oct 15, 2007Hey all, I have a form in one page and when the user clicks the submit button it has to save the data into my SQL database which i have created.It doesnt show any error and it successfully redirects to another page but not saving the data .Could someone help please.Here is my code under submit button SqlDataSource txtDataSource = new SqlDataSource(); txtDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["LocalSqlServer"].ToString(); txtDataSource.InsertCommandType = SqlDataSourceCommandType.Text; txtDataSource.InsertCommand = "INSERT INTO Hamburgdata(user_name, report_type, company_name , street_address, city, state, zip_code, tax_ID) VALUES (@user_name,@report_type,@company_name ,@street_address,@city,@state,@zip_code,@Tax_ID)"; txtDataSource.InsertParameters.Add("user_name", User.Identity.Name); txtDataSource.InsertParameters.Add("report_type", ReportType.Text); txtDataSource.InsertParameters.Add("company_name", CompanyName.Text); txtDataSource.InsertParameters.Add("street_address", StreetAddress.Text); txtDataSource.InsertParameters.Add("city", City.Text); txtDataSource.InsertParameters.Add("state", State.Text); txtDataSource.InsertParameters.Add("zip_code", ZipCode.Text); txtDataSource.InsertParameters.Add("tax_ID", TaxID.Text); int rowAffected = 0; try { rowAffected = txtDataSource.Insert(); } catch (Exception exp) { } finally { txtDataSource = null; } } ThanksVik
View 7 Replies View RelatedStoring French Characters To SQL Data Base
May 30, 2008Hi
I find that users of my web site are using Canadian French character encoding such as ALT0233 (= é) or ALT0244 (= ô) when completing text boxes for data input on .aspx pages.
When saved to the SQL db, these characters are converted to é or ô and when retrieving the data, appear as é or ô in the text box.
The datatype in the table is nvarchar(60).
Data is saved using command.Parameters.AddWithValue("@PostingTitle", Server.HtmlEncode(Trim(Me.txtPostingTitle.Text)))
How can I save the data with the correct character inserted into the db and subsequently retreive the character.
Thanks in advance.
Storing DATA To Sql Server From Access Efficiency
Jun 13, 2008I have a function which is writing thousands of records coming from Access database and I was wondering if someone can suggest how can reconstruct my code for faster processing. Here my code: Public Sub MigrateNFSData(ByVal calcTbl As DataTable, ByVal strDBConnection As String) Dim sqlServerConn As New SqlConnection(strDBConnection) 'Define stored procedures Dim command As New SqlCommand Dim getAccID As New SqlCommand("GetAccountID", sqlServerConn) Dim getActionID As New SqlCommand("GetActionID", sqlServerConn) Dim getExchangeID As New SqlCommand("GetExchangeID", sqlServerConn) 'Dim getParrentAccID As New SqlCommand("GetParentAccID", sqlServerConn) Dim getStatusID As New SqlCommand("GetStatusID", sqlServerConn) Dim getTraderID As New SqlCommand("GetTraderID", sqlServerConn) Dim getGroupID As New SqlCommand("GetGroupID", sqlServerConn) Dim getGroupIDByIP As New SqlCommand("GetGroupIDByIP", sqlServerConn) Dim getTraderIDByIP As New SqlCommand("GetTraderIDByIP", sqlServerConn) 'Define insert records stored procedures Dim insertAcc As New SqlCommand("InsertAccount", sqlServerConn) insertAcc.CommandType = CommandType.StoredProcedure Dim insertAction As New SqlCommand("InsertAction", sqlServerConn) insertAction.CommandType = CommandType.StoredProcedure Dim insertExchange As New SqlCommand("InsertExchange", sqlServerConn) insertExchange.CommandType = CommandType.StoredProcedure Dim insertGroup As New SqlCommand("InsertGroup", sqlServerConn) insertGroup.CommandType = CommandType.StoredProcedure Dim insertStatus As New SqlCommand("InsertStatus", sqlServerConn) insertStatus.CommandType = CommandType.StoredProcedure Dim insertTrader As New SqlCommand("InsertTrader", sqlServerConn) insertTrader.CommandType = CommandType.StoredProcedure Try sqlServerConn.Open() Catch ex As Exception MessageBox.Show("Connection failed to open!") End Try 'Set parameters to helper Get Stored Procedures to retreive Id's getAccID.Parameters.Add("@AccName", SqlDbType.NVarChar) getAccID.CommandType = CommandType.StoredProcedure getActionID.Parameters.Add("@ActionName", SqlDbType.NVarChar) getActionID.CommandType = CommandType.StoredProcedure getExchangeID.Parameters.Add("@ExchName", SqlDbType.NVarChar) getExchangeID.CommandType = CommandType.StoredProcedure 'getParrentAccID.Parameters.Add("@ParentName", SqlDbType.NVarChar) 'getParrentAccID.CommandType = CommandType.StoredProcedure getStatusID.Parameters.Add("@StatusName", SqlDbType.NVarChar) getStatusID.CommandType = CommandType.StoredProcedure getTraderID.Parameters.Add("@TraderName", SqlDbType.NVarChar) getTraderID.CommandType = CommandType.StoredProcedure getGroupID.Parameters.Add("@GroupName", SqlDbType.NVarChar) getGroupID.CommandType = CommandType.StoredProcedure getGroupIDByIP.Parameters.Add("@IP", SqlDbType.NVarChar) getGroupIDByIP.CommandType = CommandType.StoredProcedure getTraderIDByIP.Parameters.Add("@IP", SqlDbType.NVarChar) getTraderIDByIP.CommandType = CommandType.StoredProcedure command = New SqlCommand("InsertTradeTransaction", sqlServerConn) command.CommandType = CommandType.StoredProcedure 'Set Parameters for Insert stored procedures insertAcc.Parameters.Add("@Account", SqlDbType.Text) insertAction.Parameters.Add("@ActionName", SqlDbType.Text) insertExchange.Parameters.Add("@Exchange", SqlDbType.Text) insertGroup.Parameters.Add("@Group", SqlDbType.Text) insertGroup.Parameters.Add("@ACCID", SqlDbType.Int) insertGroup.Parameters.Add("@GroupID", SqlDbType.UniqueIdentifier) insertStatus.Parameters.Add("@StatusName", SqlDbType.Text) insertTrader.Parameters.Add("@Group", SqlDbType.UniqueIdentifier) insertTrader.Parameters.Add("@IP", SqlDbType.Text) insertTrader.Parameters.Add("@TraderName", SqlDbType.Text) insertTrader.Parameters.Add("@TraderID", SqlDbType.UniqueIdentifier) 'Adding stored Get Stored Procedure's parameters----------------------- command.Parameters.Add("@OrderNum", SqlDbType.Text) command.Parameters.Add("@ACC_ID", SqlDbType.Int) command.Parameters.Add("@Group_ID", SqlDbType.UniqueIdentifier) command.Parameters.Add("@Trader_ID", SqlDbType.UniqueIdentifier) command.Parameters.Add("@Exch_ID", SqlDbType.Int) command.Parameters.Add("@Date", SqlDbType.DateTime) command.Parameters.Add("@Time", SqlDbType.DateTime) command.Parameters.Add("@ActionID", SqlDbType.Int) command.Parameters.Add("@StatusID", SqlDbType.Int) command.Parameters.Add("@TimeSent", SqlDbType.DateTime) command.Parameters.Add("@Qty", SqlDbType.Int) command.Parameters.Add("@Product", SqlDbType.Text) command.Parameters.Add("@MMYYY", SqlDbType.Text) command.Parameters.Add("@ExchOrderID", SqlDbType.Text) command.Parameters.Add("@TimeTicks", SqlDbType.Int) command.Parameters.Add("@W2G", SqlDbType.Int) command.Parameters.Add("@W2Exch", SqlDbType.Int) command.Parameters.Add("@G2ExchDerived", SqlDbType.Int) command.Parameters.Add("@Msg", SqlDbType.NVarChar) 'command.Parameters.Add("@ExchDate", SqlDbType.DateTime) 'command.Parameters.Add("@ParentID", SqlDbType.Int) 'Paremeters Defenition-------------------------------------- 'Parsing DateTime Objects Dim formaterA As IFormatProvider formaterA = New System.Globalization.CultureInfo("en-GB", True) Dim dateObj As Date 'DEBUG 'Dim rows = calcTbl.Rows.Count Dim colValues = GetColumnsValues(calcTbl) 'Write table with computed NFS data to sql server DB For Each dr As DataRow In calcTbl.Rows Dim orderNo = dr.Item("Order No").ToString() command.Parameters("@OrderNum").Value = dr.Item("Order No").ToString() getAccID.Parameters("@AccName").Value = dr.Item("Acct").ToString() If getAccID.ExecuteScalar() = Nothing Then insertAcc.Parameters("@Account").Value = dr.Item("Acct").ToString() insertAcc.ExecuteNonQuery() getAccID.Parameters("@AccName").Value = dr.Item("Acct").ToString() command.Parameters("@ACC_ID").Value = getAccID.ExecuteScalar() Else command.Parameters("@ACC_ID").Value = Int32.Parse(getAccID.ExecuteScalar()).ToString() End If getGroupID.Parameters("@GroupName").Value = dr.Item("Group ID").ToString() If getGroupID.ExecuteScalar() = Nothing Then 'Find Group by IP address if input Data Table doesn't have group getGroupIDByIP.Parameters("@IP").Value = dr.Item("IP").ToString() If getGroupIDByIP.ExecuteScalar() = Nothing Then insertGroup.Parameters("@GroupID").Value = Guid.NewGuid insertGroup.Parameters("@Group").Value = dr.Item("Group ID") insertGroup.Parameters("@ACCID").Value = getAccID.ExecuteScalar() insertGroup.ExecuteNonQuery() command.Parameters("@Group_ID").Value = getGroupID.ExecuteScalar() Else command.Parameters("@Group_ID").Value = getGroupIDByIP.ExecuteScalar() End If Else command.Parameters("@Group_ID").Value = getGroupID.ExecuteScalar() End If getTraderID.Parameters("@TraderName").Value = dr.Item("Trd ID").ToString() If getTraderID.ExecuteScalar() = Nothing Then getTraderIDByIP.Parameters("@IP").Value = dr.Item("IP").ToString() If getTraderIDByIP.ExecuteScalar() = Nothing Then insertTrader.Parameters("@Group").Value = getGroupID.ExecuteScalar() insertTrader.Parameters("@IP").Value = dr.Item("IP").ToString() insertTrader.Parameters("@TraderName").Value = dr.Item("Trd ID").ToString() insertTrader.Parameters("@TraderID").Value = Guid.NewGuid insertTrader.ExecuteNonQuery() command.Parameters("@Trader_ID").Value = getTraderID.ExecuteScalar() Else command.Parameters("@Trader_ID").Value = getTraderIDByIP.ExecuteScalar() End If Else command.Parameters("@Trader_ID").Value = getTraderID.ExecuteScalar() End If getExchangeID.Parameters("@ExchName").Value = dr.Item("Exch").ToString() If getExchangeID.ExecuteScalar() = Nothing Then insertExchange.Parameters("@Exchange").Value = dr.Item("Exch").ToString() insertExchange.ExecuteNonQuery() command.Parameters("@Exch_ID").Value = getExchangeID.ExecuteScalar() Else command.Parameters("@Exch_ID").Value = getExchangeID.ExecuteScalar() End If getActionID.Parameters("@ActionName").Value = dr.Item("Action").ToString() If getActionID.ExecuteScalar() = Nothing Then insertAction.Parameters("@ActionName").Value = dr.Item("Action").ToString() command.Parameters("@ActionID").Value = getActionID.ExecuteScalar() Else command.Parameters("@ActionID").Value = getActionID.ExecuteScalar() End If getStatusID.Parameters("@StatusName").Value = dr.Item("Status").ToString() If getStatusID.ExecuteScalar() = Nothing Then insertStatus.Parameters("@StatusName").Value = dr.Item("Status").ToString() insertStatus.ExecuteNonQuery() command.Parameters("@StatusID").Value = getStatusID.ExecuteScalar() Else command.Parameters("@StatusID").Value = getStatusID.ExecuteScalar() End If 'getParrentAccID.Parameters("@ParentName").Value = "" 'If getParrentAccID.ExecuteScalar() = 0 Then 'insert parent acc 'Else 'command.Parameters("@ParentID").Value = getParrentAccID.ExecuteScalar() dateObj = Date.Parse(dr.Item("Exch Date").ToString(), formaterA) command.Parameters("@Date").Value = dateObj command.Parameters("@Time").Value = DateTime.Parse(dr.Item("Time").ToString()) command.Parameters("@TimeSent").Value = DateTime.Parse(dr.Item("Time Sent").ToString()) If (dr.Item("Qty").Equals(System.DBNull.Value)) Then command.Parameters("@Qty").Value = System.DBNull.Value Else command.Parameters("@Qty").Value = Int32.Parse(dr.Item("Qty").ToString()) End If command.Parameters("@Product").Value = dr.Item("Product").ToString() command.Parameters("@MMYYY").Value = dr.Item("MMMYY").ToString() command.Parameters("@ExchOrderID").Value = dr.Item("Exchange Order ID").ToString() If (dr.Item("Time Ticks").Equals(System.DBNull.Value)) Then command.Parameters("@TimeTicks").Value = System.DBNull.Value Else command.Parameters("@TimeTicks").Value = Int32.Parse(dr.Item("Time Ticks").ToString()) End If 'command.Parameters("@ExchDate").Value = Date.Parse(dr.Item("Exch Date").ToString()) 'command.Parameters("@ExchDate").Value = Convert.ToDateTime(dr.Item("Exch Date").ToString()) 'DEBUG 'Dim strW2G = dr.Item("W2G").ToString() 'Dim strW2E = dr.Item("W2E").ToString() If (dr.Item("W2G").Equals(System.DBNull.Value)) Then command.Parameters("@W2G").Value = System.DBNull.Value Else command.Parameters("@W2G").Value = Int32.Parse(dr.Item("W2G").ToString()) End If If dr.Item("W2E").Equals(System.DBNull.Value) Then command.Parameters("@W2Exch").Value = System.DBNull.Value Else command.Parameters("@W2Exch").Value = Int32.Parse(dr.Item("W2E").ToString()) End If 'command.Parameters("@G2ExchDerived").Value = Int32.Parse(dr.Item("Time Delta G2E").ToString()) If (dr.Item("Time Delta G2E").Equals(System.DBNull.Value)) Then command.Parameters("@G2ExchDerived").Value = System.DBNull.Value Else command.Parameters("@G2ExchDerived").Value = Int32.Parse(dr.Item("Time Delta G2E").ToString()) End If command.Parameters("@Msg").Value = dr.Item("Msg").ToString() command.ExecuteNonQuery() Next sqlServerConn.Close() End Sub
View 3 Replies View Related