Posts by M_K_Higa

M_K_Higa 2 Junior Poster

14 Years Ago

Folks - I found the answer. I needed to set the extended property IMEX=1 in the connection string. Here's a link that explained what I needed to do: http://msdn.microsoft.com/en-us/library/ms141683(SQL.100).aspx.

Databases mssql

M_K_Higa 2 Junior Poster

14 Years Ago

SSIS: Load Excel data to table

Greetings - I need your help.

I have an SSIS package with a Data Flow Task which has the following Data Flow tasks: Excel Source -> Data Conversion -> SQL Server Destination .

The data in my Excel worksheet contains strings as well as numbers. (Also has some blank cells.)

I'm trying to cast all data into strings and insert it into a SQL table, but the numerical data doesn't get inserted -- I get null instead.

I've casted the worksheet data to Unicode string [DT_WSTR] and mapped the casted fields to my SQL table but still get null for the cells containing numbers. All fields in the SQL table are varchar.

Databases mssql

M_K_Higa 2 Junior Poster

16 Years Ago

Re: Popup window on button click

I'm not sure I understand. Is there a question here?

Web Development asp.net

M_K_Higa 2 Junior Poster

16 Years Ago

Re: Copy data from one table to another table order by

Let me know how it worked. If you get any errors, let me know of those too.

(I assumed you are using MS SQL 7 or above.)

Databases mssql

M_K_Higa 2 Junior Poster

16 Years Ago

Re: Copy data from one table to another table order by

copy/paste the following code into query analyzer.

SELECT C1, C2, C3, 0 [grp]
INTO #t1 
FROM staging

declare @cnt as int 
set @cnt = 0
update #t1
set @cnt = grp = @cnt + 1 
where C1 = 41

set @cnt = 0
update #t1
set @cnt = grp = @cnt + 1 
where C1 = 42

set @cnt = 0
update #t1
set @cnt = grp = @cnt + 1 
where C1 = 51

SELECT C1, C2, C3
FROM #t1 ORDER BY grp, C1

Databases mssql

M_K_Higa 2 Junior Poster

16 Years Ago

Re: Copy data from one table to another table order by

I'm not so sure how your data columns relates to each other, but here's one way you can put data into the grp column....

This is a three step process since it looks like you have three unique values in C1. Also, I'm making the assumption that you don't care which 41, 42 and 51 gets grouped together.

Here's step 1. This increments the grp column where the C1 column has a value of 41. This process is repeated for C1=42 and C1=51.

declare @cnt as int 
set @cnt = 0
update #t1
set @cnt = grp = @cnt + 1 
where C1 = 41

C1=42

declare @cnt as int 
set @cnt = 0
update #t1
set @cnt = grp = @cnt + 1 
where C1 = 42

C1=51

declare @cnt as int 
set @cnt = 0
update #t1
set @cnt = grp = @cnt + 1 
where C1 = 51

Finally, you can run the following query...

select C1, C2, C3 from #t1
order by grp, C1

Hope this helps.

Databases mssql

M_K_Higa 2 Junior Poster

16 Years Ago

Re: Copy data from one table to another table order by

Here's how you get the grp column into your temp table.

select C1, C2, C3, 0 [grp]
into #t1 
from staging

This creates a temp table called #t1 with a column named grp which is all initialized with the value 0.

You will then have to update the grp column so that you can achieve your results.

Databases mssql

M_K_Higa 2 Junior Poster

16 Years Ago

Re: Copy data from one table to another table order by

It looks like you want to do some logical grouping.

Here's what I would do....

C1 C2 C3 Grp
41 te cc 1
42 te cc 1
51 te cc 1
41 te cc 2
42 te cc 2
51 te cc 2
41 te cc 3
42 te cc 3
51 te cc 3

With this structure, you can sort by grp, c1 to get your desired results. If you don't want the Grp column, in your temp table, then exclude it from your select statement.

Databases mssql

M_K_Higa 2 Junior Poster

16 Years Ago

Re: filtering same values in one field in 2 tables

Sorry - I haven't touched Access in ages so I don't think I can add any more value to this thread.

Perhaps the only advice I can give is to open up Access and test the query within the Access environment. Otherwise, use some of the Access wizards to see what kind of code it generates. Maybe this will give you a starting point.

Sorry I can't help any further.

Databases mssql

M_K_Higa 2 Junior Poster

16 Years Ago

Re: filtering same values in one field in 2 tables

I think you have to qualify the field, "GRAPHICSVERSION" in the case statement.

You have:

cmd = New OleDbCommand("SELECT table2.GRAPHICSVERSION, case when [B]GRAPHICSVERSION[/B] IS NULL then 0 else 1 end as ExistsInTable1 from table2 left outer join table1 on table2.GRAPHICSVERSION=table1.GRAPHICSVERSION",con)

I think you need something like this:

cmd = New OleDbCommand("SELECT table2.GRAPHICSVERSION, case when [B]table1.GRAPHICSVERSION[/B] IS NULL then 0 else 1 end as ExistsInTable1 from table2 left outer join table1 on table2.GRAPHICSVERSION=table1.GRAPHICSVERSION",con)

Databases mssql

M_K_Higa 2 Junior Poster

16 Years Ago

Re: Input box not accepting focus...

Here's your code...

<body bgcolor="#000000" onload="load()" onunload="GUnload()"> 

<div id="" align="center">
    <a href="http://www.laudontech.com"><img alt="logo" src="http://www.laudontech.com/Images/LaudontechLogoSmall.png" border="0"></a>
</div>

<div id="A" style="width: 640; position: absolute; left: 50%; margin-left: -300px;"  >
<h3><font color="#FF8080" face="trebuchet ms" ><center>Garmin Office Plans via Google Maps</center></h3>
</font>
</br>
</div>
<form name="form1" method="POST" action="test5.php5">
</br> 
<P ALIGN=CENTER>
<INPUT TYPE = "TEXT" VALUE ="" NAME="SearchItem" ID="SearchItem">
<INPUT TYPE = "Submit" Name = "Submit1" VALUE = "Find">
<br>  
</P>
</form>

Your <div id="A" ...> tag is interfering with your text box. i.e., it overlaps the text box.

To see what I mean, try coloring the background of the div tag with a color other than black.

Here's what I did to expose the problem... <div id="A" style="background-color: silver; width: 640; position: absolute; left: 50%; margin-left: -300px;" > You can probably fix this by changing your code to something like this... <div id="A" align="center" style="background-color: silver;> or even getting rid of the div, font and center tag and doing something like this... <h3 style="color: #FF8080; font-face: trebuchet ms; text-align: center;">Garmin Office Plans via Google Maps</h3> Hope this helps. Here's the final code.... (up to the closing </form> tag)

<html xml:lang="en" lang="en">
<head>

  <title>Test</title>
  <meta http-equiv="content-type" content="text/html; charset=utf-8"/>

</head>


<body bgcolor="#000000" onload="load()" onunload="GUnload()"> 

<div id="" align="center">
    <a href="http://www.laudontech.com"><img alt="logo" src="http://www.laudontech.com/Images/LaudontechLogoSmall.png" border="0"></a>
</div>

<h3 style="color: #FF8080; font-face: trebuchet ms; text-align: center;">Garmin Office Plans via Google Maps</h3>


<form name="form1" method="POST" action="test5.php5">
</br> 
<P ALIGN=CENTER>
<INPUT TYPE = "TEXT" VALUE ="" NAME="SearchItem" ID="SearchItem">
<INPUT TYPE …

UI / UX Design web-design

M_K_Higa 2 Junior Poster

16 Years Ago

Re: Input box not accepting focus...

Are you trying to clear the text box when it gets focus? if so, the see the code below. (I'm not too sure what you wanted to get out of the onclick event.)

<html>
<title>test</title>
<script type="text/javascript"> <!--
function clearDefault(arg) {
	//alert("Test");
	arg.value="";
}
// -->
</script>
</head>
<body>
<form name="form1" method="POST" action="#">
<input type="text" value="Enter a search term" name="SearchItem" id="SearchItem" onfocus="clearDefault(this)" />
<input type="Submit" Name = "Submit1" value="Find" />
</form>
</body>
</html>

UI / UX Design web-design

M_K_Higa 2 Junior Poster

16 Years Ago

Re: Optional Closing Tags in HTML

Don't feel that you missed the boat; that was probably a boat you were better off missing anyway. It's good to get into the habit of writing correct code; breaking the habit of writing bad code is difficult.

Depending on your DOCTYPE declaration, this may raise concerns. As PKern states, closing tags are required in xhtml.

Although trimming access code saves you in bandwidth usage, this could make the difference between forcing the browser to go into quirks mode or standards mode. Browsers perform better in standards mode because the parsing requires less overhead. You can find out more about this by google-ing "Quirks Mode vs. Standards Mode".

I.e. Trimming code benefits you; good code benefits the user. If done correctly, trimming code benefits you and the end user.

If you want to save bandwidth by cutting out access code, you can start by cutting out unnecessary white space. This helps dramatically, while still preserving code correctness.

UI / UX Design html-css web-design

M_K_Higa 2 Junior Poster

16 Years Ago

Re: Prevent queries from SQL Injection attack in SQL Server 2005

Use stored procedures and pass the data you need to update as parameters.

Databases mssql

M_K_Higa 2 Junior Poster

17 Years Ago

Re: Get data out of excel file stored as an image

I don't know how to specify the sheet by position.

I also struggled with the sheet names containing spaces. This seems to be a limitation as far as I was able to research. In the end, I replaced all spaces in the sheet name with the underscore character. (This was the least headache.)

Databases mssql

M_K_Higa 2 Junior Poster

17 Years Ago

Re: Get data out of excel file stored as an image

Be careful in situations where you have different data types in the same column.

For example, in my spreadsheet, the first four rows contain information that really has nothing to do with the data. The real data starts from the fifth row and down. So in my example, I have integer data in the first column (starting from the fifth row). However, cell A1 contains a title that is a string. If I use cast(f1 as int) in my code, it will fail on cell A1.

In your case, you're casting to a varchar so no big deal, but if you are casting to a numeric data type you'll need to be aware of the pit falls.

Bottom line - lots of error checking and data massaging may be required.

Databases mssql

M_K_Higa 2 Junior Poster

17 Years Ago

Re: Get data out of excel file stored as an image

Ok. One more thing (and I use this technique a lot). You can conditionally inject a zero to your string data based on conditions.

case when ((cast(f1 as int) > -10) or (cast(f1 as int) < 10)) then '0' + f1 
else f1 
end

This code will inject a '0' in front of any single digit number.

Databases mssql

M_K_Higa 2 Junior Poster

17 Years Ago

Re: Get data out of excel file stored as an image

Yes -- this is a hurdle that we have to face. You have to wirte a second routine that will cast your data into correct data types.

I tweak my data by getting the table containing the spreadsheet data and inserting it into a second table. The second table is defined with correct data types, so I have to cast anything that is not a character data type.

Databases mssql

M_K_Higa 2 Junior Poster

17 Years Ago

Re: Get data out of excel file stored as an image

Yes -- you can build the statement and then use the execute command to execute the statement.

I do something similar to the following in my actual code...

declare @text_01 as varchar(1000) 
declare @text_02 as varchar(500)
declare @spreadsheet as varchar(250)
declare @worksheet as varchar(250)
declare @sql_statement as varchar(2000)

set @spreadsheet = 'c:\path\test.xls'
set @worksheet = 'Sheet1'

set @text_01 = 'INSERT INTO SpreadsheetDataDump (f1,f2,f3,f4,f5,f6) SELECT f1,f2,f3,f4,f5,f6 '

set @text_02 = ' FROM OPENDATASOURCE(''Microsoft.Jet.OLEDB.4.0'',''Data Source="'
+  @spreadsheet + '";Extended Properties="Excel 8.0;IMEX=1;HDR=NO"'')...'
+  @worksheet + '$' 


set @sql_statement =  @text_01 + @text_02

exec (@sql_statement )

In my actual code, the @spreadsheet and @worksheet variables are passed in as parameters. I've declared them in this sample code for clarity.

Hope this helps.

Databases mssql

M_K_Higa 2 Junior Poster

17 Years Ago

Re: Create view with if statements?

Have you considered puting this code in a stored procedure instead of a view? This type of code is easily implemented in a stored procedure.

If you require a view, then you may want to create a stored procedure to populate a separate table. This table can be populated according to the logic you describe. You would then view the populated table with a view.

If what I describe is acceptable for your needs and you need more information I can provide.

Databases mssql

M_K_Higa 2 Junior Poster

17 Years Ago

Re: Transfer data between pages

If you are using the .net framework 2.0 or greater, you may want to look at the PreviousPage property of the Page object.

For example, let's say you have a hidden field in your original page named hfTheHiddenField and want its value when you get the target page. You can do something like this...

Control ctrl;
string theString;
ctrl = PreviousPage.FindControl("hfTheHiddenField");
if(ctrl != null) {
  //cast it and get it's string value.
  theString.Value = ((HiddenField)ctrl).Value.ToString(); 
}

You could also use Server.Transfer or the Request.Response objects to get values from previous pages as well.

Web Development asp.net

M_K_Higa 2 Junior Poster

18 Years Ago

Re: Sharepoint Problem

It's not functionality that comes out of the box, but I believe you can do this. Let me divide your question into two parts.

PART 1:

First of all, each page in a Sharepoint site is ultimately an asp.net page. This fact opens up a lot of possibilities.

A) You can edit these pages by connecting to the website using MS Frontpage. (I think MS is deprecating Frontpage, but there's supposed to be a replacement.) Anyway, this allows you to write server side code.

B) You can develop your own web part and insert it into your page. This page is a good starter on how to create a web part.

So, this part is up to you. You need to know/find out how to write code which will run in sharepoint, and there are may forums out there to help you on this.

PART 2:

Yes, you can hide/display controls based on who logged in. Simply put, all you are going to do is write an "if" statement which says if a user is valid, then show the control, otherwise hide the control. In asp.net, this would be something along the lines of

if (theUserAuthorizaion == valid) {
  theControl.Visible = true;
} else {
  theControl.Visible = false;
}

Now, in order to help you more, I need some specific information.

Tell me a little bit about your Sharepoint infrastructure. Is this an internet or intranet …

Web Development asp.net

M_K_Higa 2 Junior Poster

18 Years Ago

Re: ASP.NET Popup Control

Tell us your requirements. Did you find any third party controls that do what you are looking for? If so, which? This gives us a better picture of what you require.

Web Development asp.net

M_K_Higa 2 Junior Poster

18 Years Ago

Re: New cell phone....

hey M_K_Higa, can you hear me now?

Huh? What did you say?:)

Maybe if I'd gone with the "can you hear me now?" guy, I'd still have a cell. But for now, I live in a cave. :'(

Geeks' Lounge

M_K_Higa 2 Junior Poster

18 Years Ago

Re: New cell phone....

I've thrown away my cell phone about three years ago. (I was happy with the phone, but angry with the carrier - the one with the trench coat guy.) I had a Kyocera smartphone - it had a built-in palm.

I've been happy ever since. ;)

Geeks' Lounge

M_K_Higa 2 Junior Poster

18 Years Ago

Re: How to print tables in SQL Server 2005

You can create a view to get the data you want and the import it into excel. The "Import External Data" is in the Data menu, and is wizard based, so it's pretty easy.

If your data changes, all you have to do is refresh your excel sheet and you get updated data as well.

Databases mssql

M_K_Higa 2 Junior Poster

18 Years Ago

Re: Collins: Why this scientist believes in God

Hmm... I guess.. =/
Sorry, but my religion doesn't require women do stuff like that. And btw: it's called opinions...
And if I'm judging anyone, then it would be Tom Cruise.

I wasn't directing my post directly to you. But kudos to you for responding. Looking at this post, so far, you haven't dodged a single tough question.

Geeks' Lounge

M_K_Higa 2 Junior Poster

18 Years Ago

Re: Collins: Why this scientist believes in God

Go back a few posts and start replacing "scientology" with your religion. Hmmmm, makes you wonder how long your measuring stick is, eh?

Be careful how you judge others...

Geeks' Lounge

christina>you commented: . -1

M_K_Higa 2 Junior Poster

18 Years Ago

Re: Word Association Game

U.S. Army -> under budget

Geeks' Lounge posting-games

M_K_Higa 2 Junior Poster

18 Years Ago

Re: Word Association Game

game -> tetris

Geeks' Lounge posting-games

M_K_Higa 2 Junior Poster

18 Years Ago

Re: Word Association Game

cook -> Hop Sing

Geeks' Lounge posting-games

M_K_Higa 2 Junior Poster

18 Years Ago

Re: Collins: Why this scientist believes in God

Come on, dude. Some jokes are better left untold. Bad taste.

Geeks' Lounge

christina>you commented: Thank you :) - Christina +3

M_K_Higa 2 Junior Poster

18 Years Ago

Re: Word Association Game

armstrong -> apollo

Geeks' Lounge posting-games

M_K_Higa 2 Junior Poster

18 Years Ago

Re: Word Association Game

mistake -> source code

Geeks' Lounge posting-games

M_K_Higa 2 Junior Poster

18 Years Ago

Re: Collins: Why this scientist believes in God

Yes. It's debated because it's hard to understand. Just like the folks who had a hard time understanding that the world was not flat. It must have taken a whole lot of faith to believe that the ships going west were not going to fall off the edge. Of course, now we know better.

As time goes on, more gets revealed. These days, almost everyone knows that the world is not flat. Perhaps some day, we will get a definitive answer. In the meantime, I prefer to believe all was created in six literal days - until proven otherwise.

Geeks' Lounge

M_K_Higa 2 Junior Poster

18 Years Ago

Re: Collins: Why this scientist believes in God

God created the world in 6 days; not 7. God rested on the 7th day. This is Fact based on faith!

There was a point in time when folks believed that the world was flat. The world was as round as it was back then as it is now.

Why do people have to "understand" stuff. A little faith goes a long way. Just gotta believe, right? ... but no matter how much I believe, my code still contains bugs. ;)

Geeks' Lounge

M_K_Higa 2 Junior Poster

18 Years Ago

Re: Word Association Game

dying -> decaying

Geeks' Lounge posting-games

M_K_Higa 2 Junior Poster

18 Years Ago

Re: Word Association Game

returns -> Christmas gifts

Geeks' Lounge posting-games

M_K_Higa 2 Junior Poster

18 Years Ago

Re: Word Association Game

chips -> AMD

Geeks' Lounge posting-games

M_K_Higa 2 Junior Poster

18 Years Ago

Re: Get data out of excel file stored as an image

First of all, let me recap the situation. I need an interface that allows users to upload excel data into a sql database.

1) I need a way to get excel data into my sql database.

2) I use the following sql statement to get the excel data into my sql database…

insert into SpreadsheetDataDump (f1,f2,f3,f4,f5,f6) 
Select f1,f2,f3,f4,f5,f6 
FROM OPENDATASOURCE
('Microsoft.Jet.OLEDB.4.0','Data Source="D:\spreadsheets\test.xls";Extended Properties="Excel 8.0;IMEX=1;HDR=NO"')
...WorksheetName$

Keep in mind, in this scenario, the excel file must exist on the sql server.

3) Users need a way to upload the excel file onto the sql server.

4) I cannot grant users direct access to the sql server’s file system.

Task: How does the user get the excel file onto the sql server file system without granting access to the sql server’s file system?

In essence, there are three steps in this process. 1. Get excel file into sql table. 2. Put excel file (stored as Image) onto disc. 3. Insert excel data into sql database.

Step-1) I use a web (asp.net) interface to upload the excel file into a sql table as an Image data type. I’m going to defer you to http://aspnet.4guysfromrolla.com/articles/120606-1.aspx, which explains the process of getting a binary file uploaded into a sql table. You’ll get a lot more stuff out of this article, but we’re concerned with the part about getting the file uploaded into the database. I thought it was a neat article, so I'm …