Showing posts with label form. Show all posts
Showing posts with label form. Show all posts

Friday, March 23, 2012

How to use string values? I really need help for this. Thanks.

Hello,

I am working with ASP.NET/VB and Microsoft SQL 2000 database.

I have a search form where keywords are submitted.
Consider I write the the keywords 'asp' and 'book'. The results page is called as follows: results.aspx?search=asp%20book

Then I use this script in results.aspx to put the keywords in a string:

Sub Page_Load(sender As Object, e As System.EventArgs)
Dim keywords() As String = Request.QueryString("search").Split(CChar(""))
End Sub

My table is set for FULL TEXT SEARCH.
Consider the SQL when I look for records containing 'asp' and 'book':

SELECT *
FROM dbo.documents
WHERE CONTAINS (*, 'ASP or BOOK')

This SQL looks only for these words. What I need is to look for records that contain the Keywords included in the string keywords().

Can you tell me how to access the string values in the SQL and use it?

Thanks,
Miguel::Can you tell me how to access the string values in the SQL and use it?

Yes. You can not. A sql statement can not access your string variable. No way.

YOu have to handle this from the other side: you have to generate SQL that is correct in the first place. Means: the SQL has to contain all the data you want, so that it does not need to access the string value.

So start from the other end. How does your SQL have to look like (check it in query analyzer), then build code that generates exactly this sql statement. If you have trouble doing so (which I can not believe - I think you basically misunderstand that SQL is just a string that you pass forward and no, it can not connect to your variables anymore), a beginner programmer class will help, as well as any book teaching beginner programming - string manipulation is extremely beginner level.|||Assuming your string array keywords contain two strings "asp" and "book"

you could do this:

Dim s as string
Dim sep as string = ""
dim result as string

For each s in keywords
result = result & sep & s
sep = " OR "
Next

you now have a string var called result that looks like this: "asp OR book"

you then use the result string to help you complete the full sql stringsql

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.

Monday, March 19, 2012

How to use ReportViewer in a Windows Form?

Hi all,

How can I use ReportViewer in a Windows Form? I'm using RS 2000. Thanks.

No. The ReportViewer controls that ship with VS 2005 (both winforms and webforms) require RS 2005.

Friday, March 9, 2012

How to use import/export utility in Sql Server 2000

Hi,

I've a data back up of sql server 2000 database in .bak form. I want to import data from this .bak file using the import/export utility. But there is no .bak option available in datasource select button of import/export utility. Then, what is the way to import data from .bak file?

you can not use import/export wizard in this scenario. You should restore database. Use Restore Command. Read about Restore with Move option in BOL(books online)

copied from BOL :

RESTORE FILELISTONLY
FROM DISK = 'c:\Northwind.bak'
RESTORE DATABASE TestDB
FROM DISK = 'c:\Northwind.bak'
WITH MOVE 'Northwind' TO 'c:\test\testdb.mdf',
MOVE 'Northwind_log' TO 'c:\test\testdb.ldf'

Madhu

|||I have marked Madhu's response as it stands, further do not attempt to open this .bak file as it is a database backup file and only by using RESTORE statement you would be able to restore on the other SQL server to extract the data using import/export wizard.|||

Hi

Madhu's suggestion worked well. I am able to restore.

How to use IF..THEN to select SQL Server/Database?

I have a single form that the users can do lookups for items. We have two locations each with its own SQL Server and database. Trying to use an IF...THEN statement as they select the DB they want to query it then creates the connection string for that particular database. I haven't a clue on how to do this. If someone could point me to the documentation to do this I would really appreciate it.

<%@. Page Language="VB" %><%@. ImportNamespace="System.Data" %><%@. ImportNamespace="System.Data.OleDB" %><%@. ImportNamespace="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">Sub get_Dies(ByVal senderAs System.Object,ByVal eAs System.EventArgs)Dim dbConnectionAs New SqlConnectionIf ddlDatabase.SelectedValue ="db2"Then ? dbConnection ="server=server2;Database=db2;UID=user;PWD=pass" dbConnection.Open()Else ? dbConnection ="server=server1;Database=db1;UID=user;PWD=pass" dbConnection.Open()End If Dim sqlStringAs String =" sql statement"Dim dbCommandAs New SqlCommand(sqlString, dbConnection)Dim dbDataReaderAs SqlDataReader dbDataReader = dbCommand.ExecuteReader(CommandBehavior.CloseConnection) gvDies.DataSource = dbDataReader gvDies.DataBind() dbConnection.Close()End Sub </script><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"> <title>Die Usage Lookup</title><link rel="stylesheet" href="/intranet.css" type="text/css" /></head><body> <form id="form1" runat="server"> <div> Find dies matching: <asp:TextBox ID="tbDies" runat="server" /><br />Select database: <asp:DropDownList id="ddlDatabase" runat="server" AutoPostBack="true" OnSelectedIndexChanged="get_Dies"> <asp:ListItem Text="" Selected="true" Value="" /> <asp:ListItem Text="DB1" Value="db1" /> <asp:ListItem Text="DB2" Value="db2" /> </asp:DropDownList> <asp:gridview ID="gvDies" runat="server" GridLines="none" > </asp:gridview> </div> </form></body></html>

in your if statement you can assign the connectionstring to the connection object such as:

Dim connectionStringAsString

If ddlDatabase.SelectedValue ="db2"Then
connectionString ="server=server2;Database=db2;UID=user;PWD=pass"
Else
connectionString ="server=server1;Database=db1;UID=user;PWD=pass"
End If

Dim dbConnectionAs New SqlConnection(connectionString)

dbConnection.Open()


|||

That is brilliant!! Did exactly what I was looking for. Thank you very much.

How to use form authentication with Reporting Service

Can we use form authentication with Reporting Service?
Is it more convenient to do with form authentication if I use Report Viewer
in webapplication?Yes, you can use Forms auth with Reporting Services.
Here is an MSDN link:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/htm
l/ufairs.asp
--
| From: "ad" <flying@.wfes.tcc.edu.tw>
| Subject: How to use form authentication with Reporting Service
| Date: Thu, 29 Sep 2005 22:48:35 +0800
| Lines: 6
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| X-RFC2646: Format=Flowed; Original
| Message-ID: <uwln0TQxFHA.1284@.tk2msftngp13.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: 218-162-75-240.dynamic.hinet.net 218.162.75.240
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:53360
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Can we use form authentication with Reporting Service?
|
| Is it more convenient to do with form authentication if I use Report
Viewer
| in webapplication?
|
|
||||Yep, but prepare for the pain if you do!

Friday, February 24, 2012

How to use a variable in update statement?

Does anyone have insight on how I can accomplish this?

I have a table with a column called username. In that column I have a value in the form "domain_A\user". There are about 2900 records in the table. I want to change the domain portion to "domain_B"of a select few of the users based on a list. I would hope that I can use an update statement that has a variable. Then get that variable from the list I have.

Any help is greatly appreciated.update tbl set username = a.username
from tbl inner join a_list a
on tbl.username = replace(a.username, 'domain_B', 'domain_A')|||declare @.domain varchar(20)
set @.domain = 'domain_B\'
update test set jsp_name=@.domain+substring(jsp_name,10,15) where --ur where clause