Cleanup / Foreign key

I have an address table I set up so that I can start our address campaign.
There is a field (open entry) called Business Type that I allowed the works
to enter to describe the business type. Now that the entry is complete ,
I'm in cleanup phase. Now, my question is: I know I need to make Business
Type a foreign key and select distinct to insert them into their own table,
but should I do that before I clean up the inconsistencies or after. There
are about 2000 different business types, some are minor like - Car Sales -
Used and Car Sales _ Used. I'm creating a system that allows users to
search based on business type.
Before or after, why?Before!
Why? Do not allow bad data into the database in the first place, it is a
bitch getting it out.
Jay
<programmingcodeATjards.com> wrote in message
news:uVedQl9%23HHA.320@.TK2MSFTNGP04.phx.gbl...
>I have an address table I set up so that I can start our address campaign.
>There is a field (open entry) called Business Type that I allowed the works
>to enter to describe the business type. Now that the entry is complete ,
>I'm in cleanup phase. Now, my question is: I know I need to make Business
>Type a foreign key and select distinct to insert them into their own table,
>but should I do that before I clean up the inconsistencies or after. There
>are about 2000 different business types, some are minor like - Car Sales -
>Used and Car Sales _ Used. I'm creating a system that allows users to
>search based on business type.
> Before or after, why?
>sqlsql

Chinese chars in SQL?

We have a need to enter and store Chinese characters in a SQL Server table.
Has anyone done this before?
We're using ASP .NET to program entry forms, so they'd have to accept
Chinese chars, the data would be stored in SQL Server, and then search
results would have to display Chinese as well.
What additional technology might we need to implement this?
Thanks!!As far as I know you may use datatype as NVarchar / Nchar.
Even if the datatype the column is Varchar / Char it should still be able to save it, provided you take care of it in UI i.e. ASP page using code-page setting.|||You would need to store data as Unicode (i.e. nvarchar) and select the
approriate Chinese collation for your data. You need to also make sure that
your application is ready to process Chinese (or other) data:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vsent7/html/vxorilocalizationplanning.asp
--
Michiel Wories, SQL Server PM
This posting is provided "AS IS" with no warranties, and confers no rights.
--
"Dean J. Garrett" <deanj_garrett@.yahoo.com> wrote in message
news:Ojae0LPmDHA.372@.TK2MSFTNGP11.phx.gbl...
> We have a need to enter and store Chinese characters in a SQL Server
table.
> Has anyone done this before?
> We're using ASP .NET to program entry forms, so they'd have to accept
> Chinese chars, the data would be stored in SQL Server, and then search
> results would have to display Chinese as well.
> What additional technology might we need to implement this?
> Thanks!!
>
>|||Thank you! I'll follow your advice.
"Michiel Wories [MS]" <mwories@.online.microsoft.com> wrote in message
news:3f97f365$1@.news.microsoft.com...
> You would need to store data as Unicode (i.e. nvarchar) and select the
> approriate Chinese collation for your data. You need to also make sure
that
> your application is ready to process Chinese (or other) data:
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vsent7/html
/vxorilocalizationplanning.asp
> --
> Michiel Wories, SQL Server PM
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> --
> "Dean J. Garrett" <deanj_garrett@.yahoo.com> wrote in message
> news:Ojae0LPmDHA.372@.TK2MSFTNGP11.phx.gbl...
> > We have a need to enter and store Chinese characters in a SQL Server
> table.
> > Has anyone done this before?
> >
> > We're using ASP .NET to program entry forms, so they'd have to accept
> > Chinese chars, the data would be stored in SQL Server, and then search
> > results would have to display Chinese as well.
> >
> > What additional technology might we need to implement this?
> >
> > Thanks!!
> >
> >
> >
>

Checking if an entry exists in a database

I would like to be able to check if a certain entry exists in a SQL table. Ideally, the output would be a boolean so I can use it in an IF statement which would tell it what to do depending on whether or not the entry exists. Thanks for anyone that helps. :)

I had written a simple example here.

Below code has simple example on it:

<%

@.PageLanguage="VB" %>

<%

@.ImportNamespace="System.Data" %>

<%

@.ImportNamespace="System.Data.SqlClient" %>
<scriptrunat="server">
ProtectedSub btnCheckData_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)TryDim HasdataAsStringDim myConnectionAsNew SqlConnection("server=server\sqlserver;database=db;User Id=uid;Password=swd;")Dim myCommandAsNew SqlCommand( _"Select 'X' from preview Where facility='" & TextBox1.Text &"'", myConnection)

myCommand.Connection.Open()

Hasdata = myCommand.ExecuteScalar()

myCommand.Connection.Close()

myCommand.Connection.Dispose()

If Hasdata ="X"Then
' Do if data exist process here
Response.Write("Data exit")
Else
' Do if data not exist process here
Response.Write("Data not exist")
EndIfCatch exAs Exception

Response.Write(

"Error:" & ex.Message)ExitSubEndTryEndSub

</

script>

<

htmlxmlns="http://www.w3.org/1999/xhtml">

<

headrunat="server"><title>Untitled Page</title>

</

head>

<

body><formid="form1"runat="server"><div> <asp:TextBoxID="TextBox1"runat="server"></asp:TextBox><br/><br/><asp:ButtonID="btnCheckData"runat="server"OnClick="btnCheckData_Click"Text="CheckData"/></div></form>

</

body>

</

html>

I have used executescalar and checked data is there or not in above example. If you see select, in that I am taking input from textbox for where clause, I am checking if that value is in DB or not, if not we won't get any data, but if the value is there, then select returns 'X'. So by basing on that I am checking the If condition as below.

If Hasdata ="X"Then
' Do if data exist process here
Response.Write("Data exit")
Else
' Do if data not exist process here
Response.Write("Data not exist")
EndIf

So as you asked if data is there you can keep it in True condition or if not there then you can keep in false condition.

Hope it helps!

|||Thanks for the help. When I run the code, I get an error telling me I need to cast to string (I'm using c#). And when I fix that line to this: rescheck = (string)cmd.ExecuteScalar(); It says: Invalid column name 'Owner'

Any ideas on how to fix this?

Thanks|||

Here is C# code:

<%@. Page Language="C#" %>
<%@. Import Namespace="System.Data" %>
<%@. Import Namespace="System.Data.SqlClient" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">

protected void btnCheckData_Click(object sender, EventArgs e)
{
try
{
String Hasdata;
SqlConnection myConnection = new SqlConnection(@."server=Sreebrain\SreeSQL;database=SriTest;User Id=sa;Password=srikog1!;");
SqlCommand myCommand = new SqlCommand( "Select 'X' from preview Where facility='" + TextBox1.Text + "'", myConnection);

myCommand.Connection.Open();
Hasdata = (string)myCommand.ExecuteScalar();
myCommand.Connection.Close();
myCommand.Connection.Dispose();

if (Hasdata == "X")
{
// Do if data exist process here
Response.Write("Data exit");
}
else
{
// Do if data not exist process here
Response.Write("Data not exist");
}
}
catch (Exception ex)
{
Response.Write("Error:" + ex.Message);

}
}
</script>

<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
<br />
<br />
<asp:Button ID="btnCheckData" runat="server" OnClick="btnCheckData_Click" Text="CheckData" /></div>
</form>
</body>
</html>

|||

Ok, You got invalid column means, In your select statement you might given a column name as owner, that field could not be in your Table.

Ex: Select 'X' from preview

Instead of column name give 'X' there so that you can check for 'X' when you got the result back.
Also make sure in your where clause you given the disered field as you wanted.

If you still get error after you do these changes, please post the code, so that I can invistigate the problem.

Hope this time it will make you clear!

|||Thanks for all the help. You were right on the money. Everything's fixed :)|||

I have 2 labels with visibility set to FALSE, I want to display 1 label when the entry exists and the other 1 when the entry does not exist in my DB

your code looks like it should work, yet I am getting some exceptional error

here the error

Object reference not set to an instance of an object.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.NullReferenceException: Object reference not set to an instance of an object.

Source Error:

Line 61: If Hasdata = "X" Then
Line 62: Label12.Visible = True
Line 63: Else
Line 64: Label9.Visible = True
Line 65: End If

Source File:D:\Visual Studio 2005\Projects\WebApplication11\WebApplication11\admin\Createu.aspx.vb Line:63

Stack Trace:

[NullReferenceException: Object reference not set to an instance of an object.]
 WebApplication11.Createu.TextBox1_TextChanged(Object sender, EventArgs e) in D:\Visual Studio 2005\Projects\WebApplication11\WebApplication11\admin\Createu.aspx.vb:63
 System.Web.UI.WebControls.TextBox.OnTextChanged(EventArgs e) +105
 System.Web.UI.WebControls.TextBox.RaisePostDataChangedEvent() +134
 System.Web.UI.WebControls.TextBox.System.Web.UI.IPostBackDataHandler.RaisePostDataChangedEvent() +7
 System.Web.UI.Page.RaiseChangedEvents() +137
 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +4778

---

Version Information: Microsoft .NET Framework Version:2.0.50727.832; ASP.NET Version:2.0.50727.832

and heres the code

Protected Sub TextBox1_TextChanged(ByVal senderAs System.Object,ByVal eAs System.EventArgs)Handles TextBox1.TextChangedTry Dim HasdataAs String Dim myConnectionAs New SqlConnection("Server=localhost;UID=SA;PWD=****;database=knowledge")Dim myCommandAs New SqlCommand("Select'X' from usr Where Uid='" & TextBox1.Text & "'", myConnection) myCommand.Connection.Open() Hasdata = myCommand.ExecuteScalar() myCommand.Connection.Close() myCommand.Connection.Dispose() If Hasdata = "X" Then Label12.Text = True Else Label9.Visible = True End If Catch ex As Exception Response.Write("Error:" & ex.Message)Exit Sub End Try End Sub