Re: Database redesign advice, please. - Database Discussion Boards

pmarfleet13-Dec-07 21:42

13-Dec-07 21:42

This article[^] shows how to query an Excel speadsheet using the Jet OLEDB Provider. Once you have your data in a DataTable, you can iterate through it and run INSERT queries against your SQL Server database. The code is in C# but it should be trivial to convert it to VB.NET.

If your spreadsheet is particularly large, you may want to consider using DTS/SSIS to perform the import and trigger the operation from your .NET code.

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

Importing NULLs Instead of Actual Values

Bryan Bradford13-Dec-07 9:58

Bryan Bradford

13-Dec-07 9:58

Need some help with an import problem.

Got a database (SQL 2005) with a column that has a type of VARCHAR(100) and I am trying to import data from an Excel file using ADO.NET and stored procedures.

Here are some examples of the values I am tyring to import:
0
81752646
TS:2923137:ABC
WELCOME_HEAD:H:1:2:B
etc...

I am getting the actual values to import for the ones like 0 and 81752646 but for the others it is importing a NULL value every time.

I have done the registry fix for mixed data type and it makes no difference.

Anyone have any idea how to fix this?

Thanks,
Bryan

Re: Importing NULLs Instead of Actual Values

pmarfleet13-Dec-07 11:29

pmarfleet

13-Dec-07 11:29

I guess that you have altered the TypeGuessRows registry setting. Have you included the element IMEX=1 in your connection string? This informs the driver that columns where the data is of an indeterminate data type should be treated as text.

The following text is from KB article 194124:

NOTE: Setting IMEX=1 tells the driver to use Import mode. In this state, the registry setting ImportMixedTypes=Text will be noticed. This forces mixed data to be converted to text. For this to work reliably, you may also have to modify the registry setting, TypeGuessRows=8. The ISAM driver by default looks at the first eight rows and from that sampling determines the datatype. If this eight row sampling is all numeric, then setting IMEX=1 will not convert the default datatype to Text; it will remain numeric.

Adding this setting as well as modifying the registry should produce the desired results.

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

Re: Importing NULLs Instead of Actual Values

Bryan Bradford14-Dec-07 10:28

Bryan Bradford

14-Dec-07 10:28

I have the TypeGuessRows set to 0. I'll have to go back to the code to see if I'm using the IMEX = 1.

Thanks,
Bryan

Database redesign advice, please.

jimbaloo13-Dec-07 5:59

jimbaloo

13-Dec-07 5:59

I have been asked to redesign the database my company uses for it's web applications. The old database is SQL Server 2000 and I am updating it to 2005.
The old db has many problems, not the least of which is that it simply "evolved". As each new developer worked on it they added tables to do whatever they needed to do instead of finding tables that already contained the data they were operating on. I currently have in excess of 250 tables, a quick search of the pages in the website reveals that at least 60 of these tables are not used. What I need to do is find what data is used and what data is repeated in different tables under different names.

I would appreciate any advice, where to find articles, etc., on how to go about this redesign while minimizing the time I have to spend. Please, point me to where I can find strategies, techniques, etc., to optimize my efforts.
Thanks
Jim

Re: Database redesign advice, please.

Mark Churchill15-Dec-07 18:27

Mark Churchill

15-Dec-07 18:27

Firstly you need to work out how important your existing data it. How much needs to be retained, etc.

Then you can decide whether you are going to be massaging the existing schema (yuck) and keeping all the data, or you have free reign to redesign the entire thing from scratch.

Try making an entity relationship diagram off the existing schema (or business process - as the existing schema is probably crap) and see what is left out or missing.

Then get signoff on the ERD, and enjoy a nice new schema that makes sense. Try and follow some of our tips in our quick reference sheet under Active Record Friendly Databases - it will give you a much wider choice in selecting your data layer tool.

Mark Churchill
Director
Dunn & Churchill
Diamond Binding: Zero to Data Layer in 3 mins

Re: Database redesign advice, please.

jimbaloo17-Dec-07 2:20

jimbaloo

17-Dec-07 2:20

Thanks for the advice Mark. After several days of working on the old database I went to my manager and told him it was going to be too much work to simply rework the old data into a new database so now I get to build from scratch.
What url for Active Record Friendly Databases? I can't find the page.

Re: Database redesign advice, please.

Mark Churchill17-Dec-07 3:30

Mark Churchill

17-Dec-07 3:30

Its in the Diamond Binding Quick Reference Sheet, in the downloads section.[^]

While following those tips isn't mandatory, it will certainly make your object model a lot more intuitive - especially if you use a tool like Diamond Binding to handle your data layer (give it a try!).

Mark Churchill
Director
Dunn & Churchill
Diamond Binding: Zero to Data Layer in 3 mins

Linking databases across servers

Leo Smith13-Dec-07 4:39

Leo Smith

13-Dec-07 4:39

I would like to know how to link a database table into a local view when the primary source is on another server. I am currently using MS-SQL 2005. Can this be done?

Thanks,

Leo T. Smith

Reporting Services reports: Display problem in Firefox

Ziggy98112-Dec-07 23:16

Ziggy981

12-Dec-07 23:16

Hi,

im new to using SSRS reports. I created a report and tried viewing it in Firefox, however it is not displaying correctly. The width of the whole report is shrunk on the left of the page. Arents SSRS reports compatible with Firefox browsers?.. or is there something im missing here?
I'd appreciate some help in this...

Thanks

ZiggY.

Re: Reporting Services reports: Display problem in Firefox

pmarfleet13-Dec-07 2:37

pmarfleet

13-Dec-07 2:37

The SSRS documentation states that reports can only be expected to render correctly in Internet Explorer. A known workaround is to place a hidden textbox on your report that spans the entire page width.

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

SELECT MAX() with a CRecordeset on an Oracle database

ilgale12-Dec-07 21:19

ilgale

12-Dec-07 21:19

Hi guys,
I've a problem opening a CRecordset object on an Oracle database
using the Microsoft ODBC for Oracle.
The query I'm using is something like the following:

SELECT MAX(OBJECTID) FROM MY_TABLE

the CRecordset object is a dynaset.

When I execute the code I get an error from the ODBC driver
tha says that is not possible to use keyset cursor in join
operations with distinct, union, intersect or minus conditions
or on read only dataset.

Can someone tell me how to solve the problem ?

Thanks a lot !

importing excel sheet into a database table [modified]

sivaram praveen12-Dec-07 19:51

sivaram praveen

12-Dec-07 19:51

hi,

In my application i need to import the excel sheet data into a database table "Emp_Profile_Tbl" which is created in the database.

Dim connstr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("Team wise EMP Profile (2).xls") + ";Extended Properties=Excel 8.0;"
Dim objconn As New OleDbConnection(connstr)
Dim ds As New DataSet
Dim objadapter As New OleDbDataAdapter("SELECT * FROM [Sheet1$]", objconn)
objadapter.TableMappings.Add("Table", "Emp_Profile_Tbl")
Try

'in excel: insert->name->seleziono il range:nomi
objconn.Open()
objadapter.Fill(ds)
dg_empprofile.DataSource = ds.Tables(0).DefaultView
dg_empprofile.DataBind()

Finally
objconn.Close()
End Try

i'm getting the values into datgrid but the values are not saving in "Emp_Profile_Tbl" database table.

pls help me to sort this out.

Thank u,

modified on Thursday, December 13, 2007 2:37:51 AM

Re: importing excel sheet into a database table

pmarfleet12-Dec-07 21:45

pmarfleet

12-Dec-07 21:45

sivaram praveen wrote:
i'm getting the values into datgrid but the values are not saving in "Emp_Profile_Tbl" database table.

You haven't written any code to save the data back to your database, so it's hardly surprising that nothing is happening. You need to specify insert/update/delete commands for your data adapter so that changes to the dataset can be propagated back to the database. Read this article[^] for more information.

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

[ANN] The Compete Guide to ODBC Escape Sequences in Firebird and Interbase.

AndrewMerkulov12-Dec-07 18:30

AndrewMerkulov

12-Dec-07 18:30

The Compete Guide to ODBC Escape Sequences in Firebird and Interbase.
ODBC Escape Sequences are SQL extension for writing SQL queries which will be compatible with many DBMS. Guide includes descriptions and samples for 69 scalar functions and 3 escape sequences.
Get it here

Table of Contents

1. What are ODBC Escape Sequences?
2. Why use ODBC Escape sequences?
3. Where can I use ODBC Escape Sequences?
4. Date, Time and Timestamp Escape Sequences.
5. Procedure Call Escape Sequence.
6. Scalar Function Escape Sequence.
7. Which functions supports in my database server?
  • String functions.
  • Numeric functions.
  • Date and time functions.
  • System functions.
  • Explicit Data Type Conversion Function.
8. Functions Description and the samples of functions calls.
  • String functions.
  • Numeric Functions.
  • Date and time functions.
  • System functions.
  • Explicit Data Type Conversion Function.

Read complete article about ODBC Escape Sequences

ErrorMsg:Syntax error converting the varchar value ' ' to a column of data type int.

Giri K12-Dec-07 14:53

Giri K

12-Dec-07 14:53

Guys pls Help me,

below is my procedure, i need to flag it as *, here is what iam doing

ALTER     PROC [dbo].[uprUpfronts_GetByIntroducerDate]<br />
@IntroducerID	int,<br />
@BalanceID	int,<br />
@UserID		varchar(10)<br />
AS<br />
BEGIN<br />
<br />
	--SET LoanID = U.LoanID as varchar (10)<br />
	SELECT	CASE<br />
			WHEN EXISTS (SELECT LoanID FROM FastLoan_Applications.dbo.tblLoanPurpose WHERE LoanID = U.LoanID) <br />
			THEN ' * ' + CAST(U.LoanID AS varchar(10)) <br />
			ELSE U.LoanID <br />
			END [Loan Number],<br />
			A.ShortName, <br />
			CASE<br />
				WHEN U.LoanIncreaseID IS NULL THEN LS.StageDate<br />
				ELSE LIS.StageDate<br />
			END [Settlement], U.LoanAmount, U.UpfrontRate,<br />
			U.UpfrontAmount, U.ApplicationFee, U.UpfrontGST, U.UpfrontTotal,<br />
			ISNULL(US.Name, L.IntroducerRef) [SalesPerson], <br />
			U.CommissionRunID<br />
	FROM		FastLoan_Commissions.dbo.tblUpfront U<br />
	LEFT JOIN	FastLoan_Applications.dbo.tblLoan L ON L.LoanID = U.LoanID<br />
	LEFT JOIN	FastLoan_Applications.dbo.tblApplication A ON A.ApplicationID = L.ApplicationID<br />
	LEFT JOIN	FastLoan_Applications.dbo.tblLoanStage LS ON LS.LoanID = L.LoanID<br />
		AND	LS.LoanStageTypeID = 6<br />
	LEFT JOIN	FastLoan_Applications.dbo.tblLoanIncreaseStage LIS ON LIS.LoanIncreaseID = U.LoanIncreaseID<br />
		AND	LIS.LoanStageTypeID = 6<br />
	LEFT JOIN	FastLoan_Applications.dbo.zstblUser US ON US.UserID = A.IntroducerSalesPersonID<br />
	WHERE		U.BalanceID = @BalanceID<br />
		AND	U.IntroducerID = @IntroducerID<br />
<br />
	ORDER BY	Settlement

but when i select te=he report i got the following error Can pls any one help me

Thanks in advance..

Re: ErrorMsg:Syntax error converting the varchar value ' ' to a column of data type int.

Rocky#12-Dec-07 18:33

Rocky#

12-Dec-07 18:33

well I'm sorry I didnt read ur complete code properly but I think u need to use ISNULL (If its SQL SERVER) to convert the nulls to zero or aply a check to see if the value is '' then change it to '0'

Rocky

Success is a ladder which you can't climb with your hands in your pockets.

Re: ErrorMsg:Syntax error converting the varchar value ' ' to a column of data type int.

Giri K13-Dec-07 12:09

Giri K

13-Dec-07 12:09

Thx for the reply, my requirement is i need to add '*' to the column, pls go through the query once, if you find time.

Re: ErrorMsg:Syntax error converting the varchar value ' ' to a column of data type int.

Giri K13-Dec-07 12:34

Giri K

13-Dec-07 12:34

Sorry i need to convert Int to varchar to add * or flag it, to identify that is exist in another table.

thanks heaps..

ADO INSERT

CSharpDavid12-Dec-07 9:44

CSharpDavid

12-Dec-07 9:44

I use "INSERT INTO [Flash Turn Parameters] " +
"VALUES ('" + toolPosition1.ToS .....
to create a new record in a database table

and use cmd.ExecuteNonQuery(); to execute the SQL from C# ADO
the 1st field is an auto increment IDENTITY field. How can I find out the value after the record has been created ?

Thanks

Re: ADO INSERT

Christian Graus12-Dec-07 10:46

Christian Graus

12-Dec-07 10:46

use a stored proc like this:

create proc Insert @value int

declare @id int

insert into [bad table name] values (@value)

set @id = scope_identity()

return @id

You could also make it an out parameter

create proc Insert @value int, @id int out

insert into [bad table name] values (@value)

set @id = scope_identity()

Christian Graus - Microsoft MVP - C++

"also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )

Re: ADO INSERT

pmarfleet12-Dec-07 10:47

pmarfleet

12-Dec-07 10:47

You need to write a stored procedure that performs the INSERT operation and then returns the latest IDENTITY value as an OUTPUT parameter. The latest IDENTITY value can be determined by using the SCOPE_IDENTITY[^] command.

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

Updatable view

Goodway12-Dec-07 9:22

Goodway

12-Dec-07 9:22

hello everyone,
how can i create an updatable view in sql server 2005.

Thanks.

Re: Updatable view

pmarfleet12-Dec-07 10:44

pmarfleet

12-Dec-07 10:44

I suggest you read this article[^].

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

compare 2 datasets

mfmaneef12-Dec-07 8:44

mfmaneef

12-Dec-07 8:44

Hello,

i have to develop an application to compare a dataset (with around 1000s of records)with a database table which has almost more than 4 million records.

and if the dataset has matches in the table i will mark them as processed. is there any way to achive this .

note: i already have tried comparing them with 2 loops iterating manually in each record...i want some other way

aneef

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.