Showing posts with label website. Show all posts
Showing posts with label website. Show all posts

Monday, March 26, 2012

how to use UPC database in sql server

I am working in ASP.NET 2.0 and Sql Server 2005, I need to use UPCdatabase for functioning of my website as i need to fetch results fromthis databse by comparing it with the Code entered by the user.I am notsure how to use this UPC database.Can i directly fetch this databaseinformation into my local databse or i need to make calls to the centilizedlocation where the databse is stored and how to do in either way. Do ineed to pay for this or its available freee.

Please provide me the information and solution for this.

Thanks in advance

If the information in the UPC database is not to be updated then just copy the tables into your application database. This will mean one less connection string in use and thus improve performance. If the UPC database will be updated often then either you must set up a DTS job (SQL2000) or SSIS (SQL2005) to import the data, or just connect to it separatly.

As to licencing costs, please ask the vendor.

|||

How can i copy the tables.And if i want UPC databse to update then can you please send me the procedure or code to set up SSIS to import the data.I never worked on this.

Can we import the data into sql server from a text file ? If yes , then how we can do that.

Appreciate your reply.


|||

>And if i want UPC databse to update then can you please send me the procedure or code to set up SSIS to import the data.I never worked on this.
Without a copy of the UPC database I cannot do this.

I did ask you which version of SQL Server you are using?

>Can we import the data into sql server from a text file ? If yes , then how we can do that.
There are facilities within SQL Server to import such a file. How big is it? Can you send a sample via private message to me?

|||

Appreciate your reply.

I am working in SQL server 2005. The file size is approx. 10MB i suppose, didnt have file rite now with me.

|||

If the the records are all very much the same, just post a few (say 10!) and I will have a look at it tomorrow morning.

|||

This is the sample data i have for now.It can be available in Microsoft Word also.

Check it up and let me know the solution for this & let me know if any other information you need from me.I appreciate you response.

0000000000017,,"wrsi river cd"

0000000000093,,"treo 700w"

0000000000123,,"Wrsi Riversound cafe cd"

0000000000161,0,"Dillons/Kroger Employee Coupon($1.25 credit)"

0000000001090,VAR,"Rountech Asset 1"

0000000001205,"1 gal","GIANT NATURALMOUNTAIN SPRING WATER"

0000000001243,,"CVS Photo 1-Hour 4x6 Finishing"

0000000001601,,"Sainsbury's Red Pepper"

0000000002288,"18 oz","Winn Dixie HandLotion"

0000000002745,,sunglasses

0000000004145,"7.6 cm x 1.8m","ConformingBandages"

0000000005210,,maggi

0000000006217,"1 gal","Trader Joe's 1%Lowfat Milk"

0000000007511,"44oz","Maverik Plastic Soda Cup"

0000000008570,,jewerly

0000000009997,"32 FL OZ","Down to Earth -Goji Berry Juice"

0000000010344,CD,"WNCI Morning Zoo - Oops... We DidIt Again!"

0000000014946,"6.5mm thick - 3mroll","Florist Ribbon - Lemon Yellow"

0000000043816,"1.76 oz","Altoids/Peppermint"

0000000046268,,JICAMA

0000000049887,"1 x 8 oz","Coca Cola:DietCoke"

|||

Looking at

0000000001205,"1 gal","GIANT NATURALMOUNTAIN SPRING WATER"

I derived the following table structure
/****** Object: Table [dbo].[UPC] Script Date: 06/02/2007 06:05:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[UPC](
[Id] [int] IDENTITY(1,1) NOT NULL,
[A] [char] (13) COLLATE Latin1_General_CI_AS NOT NULL,
[B] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[C] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
CONSTRAINT [PK_UPC] PRIMARY KEY CLUSTERED ([Id] ASC)
WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @.name=N'MS_Description',
@.value=N'Identity primary key' ,@.level0type=N'SCHEMA',
@.level0name=N'dbo', @.level1type=N'TABLE', @.level1name=N'UPC',
@.level2type=N'COLUMN', @.level2name=N'Id'
GO
EXEC sys.sp_addextendedproperty @.name=N'MS_Description',
@.value=N'Possible key' ,@.level0type=N'SCHEMA', @.level0name=N'dbo',
@.level1type=N'TABLE', @.level1name=N'UPC', @.level2type=N'COLUMN', @.level2name=N'A'
GO
EXEC sys.sp_addextendedproperty @.name=N'MS_Description',
@.value=N'Optional measure size' ,@.level0type=N'SCHEMA',
@.level0name=N'dbo', @.level1type=N'TABLE', @.level1name=N'UPC', @.level2type=N'COLUMN', @.level2name=N'B'
GO
EXEC sys.sp_addextendedproperty @.name=N'MS_Description',
@.value=N'Item description?' ,@.level0type=N'SCHEMA',
@.level0name=N'dbo', @.level1type=N'TABLE', @.level1name=N'UPC', @.level2type=N'COLUMN', @.level2name=N'C'

I will save the supplied data as UPC.TXT

|||

Do i need to run this structure as it in Query analyzer.Is the location of the UPC.txt matters.

Please advise for the procedure to use the above script.

Thanks

|||

>Do i need to run this structure as it in Query analyzer.
Yes you you will need to run the script to create the table.

>Is the location of the UPC.txt matters.
No, the the location does not matter, just so long you can remember where it is.

The ASP.NET site was down when I put in my first reply this morning, so the reasoning concerning the column naming was lost.The column names are arbitrary, other than the Id column. Column A is possibly a unique value.

|||

Do the following:

Right click on the database, select tasks..., import Data|||

I created the table using your script.But i am not able to find the Import/Export functionlality when i right click on my database.I am using Microsoft Sql Server Management Studio Express.

|||

I have just had a hunt on the MSDN site, the screen of of the express range, do not have the Import..., Export... sub options. You will therefore need to write procedural code to:

Read the file record by record,|||

I am able to import tha data.What i done is first imprted the file in Sql server 2000 and then from 2000 to sql sever 2005. And import successed

I appreciate your reply.

sql

Friday, March 23, 2012

How to use simple login form with SQL [newbie] ?

Heya Everyone !
I am new in Asp.net - I've read tutorial at this page, but i have some problems.
I want to have a login form at my website. I also want that login form is checking if user is in a user table (database). I mean simply login form - you write login and a password, than click button - site execute query (select * from user_table where user_id=login and user_password=password or something like this). But I have a problem with building this all code with sql connections. Can someone tell me how can I send query to my sql database ? And how can I display it? And once more - how set the cookies, but thats the other problem.

Hi there,

First of all, are you using VS 2003 or VS 2005? Because if you're using VS 2005, there are built in IDE to create a login form.

|||ronnyr --> i'm using VS 2005, but I want to have login through the sql database (simple CMS) not like this is in asp.net tutorial, where i'm using login form and changing parametrs in administration zone (I hope that you know what I mean).

I just want:
Two textboxes and simple button (like you have in html form). When I click my button, site should send a query which looks for user with user login the same as that in a textbox1 and with pass the same as that in a textbox2. If all is ok, user is logged in.|||And once more --> I have problem with:

Dim ConnectionString As String = System.Configuration.AppSettingsSection("ConnectionStrings")

How I should declare this string?
Im getting an error:
'AppSettingsSection' is a type in 'Configuration' and cannot be used as an expression.|||

So basically you just need to figure out the problem with your database connection.

Check this site:http://aspnet.4guysfromrolla.com/articles/110905-1.aspx

Hope this helps...

|||Okey, I've got connection. But I have another problems.

Dim ConnectionString As String = ConfigurationManager.ConnectionStrings("forumConnectionString").ConnectionString
Dim test As String
test = Label1.Text

Using myConnection As New Data.SqlClient.SqlConnection(ConnectionString)
'Specify the SQL query
Const sql As String = "SELECT * FROM kontakty"

'Create a SqlCommand instance
Dim myCommand As New Data.SqlClient.SqlCommand(sql, myConnection)

'Get back a DataSet
Dim myDataSet As New Data.DataSet

'Create a SqlDataAdapter instance
Dim myAdapter As New Data.SqlClient.SqlDataAdapter(myCommand)
myAdapter.Fill(myDataSet)

'Bind the DataSet to the GridView
g.DataSource = myDataSet
g.DataBind()

'Close the connection
myConnection.Close()
End Using

And I have:
TextBox1, TextBox2, Button

How I should make that when I click button, query will be
"SELECT [user_id] FROM users WHERE [user_name]=TextBox1.Text, [user_pass]=TextBox2.Text"
This query is incorrect, but I hope that someone show me how is ok.|||And once more:
i can't see an Query Wizard (or Code builder wizard). I searched lotsof, but i didn't find anything. Anyone can help me how can I enableQuery Wizard? (VS 2005)|||

Dim ConnectionString As String = ConfigurationManager.ConnectionStrings("forumConnectionString").ConnectionString
Dim test As String
test = Label1.Text

Using myConnection As New Data.SqlClient.SqlConnection(ConnectionString)
'Specify the SQL query
Const sql As String = "SELECT COUNT(*) FROM kontakty WHEREUsername=@.Username ANDPassword=@.Password"

'Create a SqlCommand instance
Dim myCommand As New Data.SqlClient.SqlCommand(sql, myConnection)
myCommand.AddParameter("@.Username",txtUsername.text)
myCommand.AddParameter("@.Password",txtPassword.text)

if myCommand.ExecuteScaler==0 then
txtError.text="Invalid Username or Password"
else
Session("Username")=txtUsername.text
response.redirect("somepage.aspx")
end if
End Using

Or something very similiar to that... (Sorry, coded by hand, no syntax check)

|||

I'm not sure what "Query wizard" you are referring to, but...

You can also do something similiar by dropping a sqldatasource object on the page, set it's connection properties, then on the selectcommand property, there will be a "..." button you can hit to help you make your query. Tell it you want to select something like the username from your users table, add a where clause to it whereusername=@.username andpassword=@.password, click the refresh parameters button, then tell it the @.username parameter comes from a control, tell it the controlid is txtUsername, tell it the @.password parameter comes from a control, tell it the controlid is txtPassword, then when you click the button, in it's click event do something similiar like this: (Sorry, I've never had to do this myself, so it's not going to be exact)

dim ds as dataset

ds=sqldatasource1.select

if ds.row(0).column(0).value=txtUsername.text then

' Do login stuff

else

'Do bad login stuff

end if

|||

Now that I've said all that, I'd recommend you don't do this, and use the built in log in form, and related controls. You can tie login form and controls to a SQL database extremely easily, and it'll take care of a HUGE chunk of the issues you'll face, such as:

How to hash a password into the database so it's secure.

How to allow a user to reset his password (in a fairly secure manner...)

Handle the cookies for "Remember me" type log ins.

Persist User preferences and profiles.

And it'll make it all much easier for you to do whatever it is your web app is supposed to do rather than concentrating on the unimportant stuff.

|||Motley --> big thanks, but I have few problems ...

1.
myCommand.AddParameter("@.Username", user_login.Text)
I have an error:

AddParameter' is not a member of 'System.Data.SqlClient.SqlCommand'.

I don't know how to change this, cause in VS 2005 there are other way to this all functions (I hope you understand :) )

2.
You mean that this simple login form which I have in toolbox I can changed for my SQL database? Not as it normal that I put login form and it logged me to the other database.

3.
As an Query Wizard I mean this from point 6 till 28
http://www.asp.net/webmatrix/guidedtour/section91/createloginpage.aspx
There Wizard make self a login procedure.

Thanks for this help which I already get :)
|||

1. It should have been:

myCommand.Parameters.Add(New system.data.sqlclient.sqlparameter("@.Username",user_login.text))

2. Yes, you can have the login control access a different database, by specifying the provider in your web.config. If your database is on SQL Server 2005, the web.config admin pages can create the tables and stored procedures for you automatically, or you can use a small batch file that is included with VS 2005 to create them for you on a SQL Server 2000 database.

3. That is a feature of Web Matrix, which is a different IDE than VS 2005.