how to make the SQL Express accessible via local IP?

i am using the SQL Server 2005 Express and I want to access it using the local PC IP instead of PC name.

You should be able to just swap the machine name for the ip address and it will work.sql

how to make select query to access view in other server

Dear All,

i am making small web application using asp.net, C# ,sql2000.

i want a about regarding how to access view or table from other server to local server. i have base database where there is a view which need to access in my database of local server.
that is how to make select query to access view in other server

Please help

thanks

You have to add you second server to linked servers (server objects-> linked servers-> right click new linked server) and next in your query use server name in table name like:

select * from [server].[database].dbo.[tablename]

or use open query

Select * from OPENQUERY([servername],'SELECT * from [database].dbo.[tablename]')

second is faster but use it only if your query will hit only data on second server.

Thanks

sql

how to make select query to access view in other server

Dear All,

i am making small web application using asp.net, C# ,sql2000.

i want a about regarding how to access view or table from other server to local server. i have base database where there is a view which need to access in my database of local server.
that is how to make select query to access view in other server
Please help

thanks

there are many ways to access other server.database.table

(a) Configure LinkedServer

b)OPENDATASOURCE

(c) OpenQuery

read about this in BOL

Madhu

How to make aspnet DB use local time zone?

I created aspnet DB on SQL Server 2K using aspnet_regsql utility. Everything works fine except the DateTime fields in all tables are using a wrong time zone. How do I set it to use my local time zone?

Any help will be greatly appreciated.

All stored procedures in aspnetdb use UTC-based datetime fields. That's pretty normal practice for any database that might need to be accessed from multiple time zones.

How to limit amount of results in a search?

I'm busy writing a local site search engine that searches through a sqlserver database and I want to know how or what is the correct sqlsyntax to use in order to limit the amount of results a page loads at atime? The idea is obviously similar to something like google where youonly see a certain amount of results first and then click at the botomfor the next eg. 10 results.
The second question is how do I, after the first page with the firstset of results that were shown, "clear" the second page ofthe previous html in order to show the next set of results? To give you an idea what my code looks like at the moment. Please don'tkill me if the code is done a bit a lame, because I'm still learning.
<%@. Page Language="C#" Debug="true" EnableSessionState="true" %>
<%@. import Namespace="System.Data" %>
<%@. import Namespace="System.Data.SqlClient" %>
<%@. import Namespace="System.IO" %>
<script runat="server">
void Page_Load(Object sender , EventArgs e)
{
//strings used to get values from basicSearch.aspx
string strProvince;
string strGender;
string strHeight;
string strBodyType;
string strLooks;
string strHairColor;
string strEyeColor;
string strEthnicity;
string strHomeLanguage;
string strRelStatus;
string strRelInterest;
string strHaveChildren;
string strWantChildren;
//strings used for storing results from search
string resUserName;
string resFirstName;
string resLastName;
string resUserPhoto;
string resProvince;
string resGender;
string resAge;
string resHeight;
string resBodyType;
string resLooks;
string resHairColor;
string resEyeColor;
string resEthnicity;
string resHomeLanguage;
string resRelStatus;
string resRelInterest;
string resHaveChildren;
string resWantChildren;
string resProfileHeading;
string resTextDescription;
string resTextDescription2 = "";// used for the actual display of the value
string strQuery; // to store concattenated search strings
string strInput; // to store textfile input while being read
StreamReader objStreamReader;

strProvince = Session["sessionProvince"].ToString();
strGender = Session["sessionGender"].ToString();
strHeight = Session["sessionHeight"].ToString();
strBodyType = Session["sessionBodyType"].ToString();
strLooks = Session["sessionLooks"].ToString();
strHairColor = Session["sessionHairColor"].ToString();
strEyeColor = Session["sessionEyeColor"].ToString();
strEthnicity = Session["sessionEthnicity"].ToString();
strHomeLanguage = Session["sessionHomeLanguage"].ToString();
strRelStatus = Session["sessionRelStatus"].ToString();
strRelInterest = Session["sessionRelInterest"].ToString();
strHaveChildren = Session["sessionHaveChildren"].ToString();
strWantChildren = Session["sessionWantChildren"].ToString();

strQuery = strProvince + " " +strGender + " " + strHeight + " " + strBodyType + " " + strLooks + " " +
strHairColor + " " + strEyeColor + " " + strEthnicity + " " +strHomeLanguage + " " +
strRelStatus + " " + strRelInterest + " " + strHaveChildren + " "+ strWantChildren;

SqlConnection conPubs;
string strSearch;
SqlCommand cmdSearch;
SqlDataReader dtrSearch;
conPubs = new SqlConnection(@."Server=THALION\THALION;Integrated Security=SSPI;Database=DateGame" );
//retrieve the results from the db
strSearch = "SELECT * FROMclient," + "FREETEXTTABLE( client, * , @.searchphrase ) searchTable "+ "WHERE [KEY] = client.userName " + "ORDER BY RANK DESC ";
cmdSearch = new SqlCommand( strSearch, conPubs );
cmdSearch.Parameters.Add( "@.searchphrase", strQuery );
conPubs.Open();
dtrSearch = cmdSearch.ExecuteReader();

//start display of results
lblResults.Text = "<tablewidth='100%' style='border-style:solid; border-width:thin;border-color:#E1E2DC;' cellpadding='0' cellspacing='0'>";
while ( dtrSearch.Read())
{
//values read from the returned result set
resUserName = dtrSearch[ "userName" ].ToString();
resFirstName = dtrSearch[ "firstName" ].ToString();
resLastName = dtrSearch[ "lastName" ].ToString();
resUserPhoto = dtrSearch[ "userPhoto" ].ToString();
resProvince = dtrSearch[ "province" ].ToString();
resGender = dtrSearch[ "gender" ].ToString();
resAge = dtrSearch[ "age"].ToString();
resHeight = dtrSearch[ "height" ].ToString();
resBodyType = dtrSearch[ "bodyType" ].ToString();
resLooks = dtrSearch[ "looks" ].ToString();
resHairColor = dtrSearch[ "hairColour" ].ToString();
resEyeColor = dtrSearch[ "eyeColour" ].ToString();
resEthnicity = dtrSearch[ "ethnicity" ].ToString();
resHomeLanguage = dtrSearch[ "homeLang" ].ToString();
resRelStatus = dtrSearch[ "relationshipStatus"].ToString();
resRelInterest = dtrSearch[ "relationPreference" ].ToString();
resHaveChildren = dtrSearch[ "haveChildren" ].ToString();
resWantChildren = dtrSearch[ "wantChildren" ].ToString();
resProfileHeading = dtrSearch[ "profileHeading" ].ToString();
resTextDescription = dtrSearch[ "textDescription" ].ToString();

// read the text file's info into a variable for display
if (File.Exists( MapPath( "text files" +"\\" +resTextDescription ) ) )
{

objStreamReader = File.OpenText( MapPath( "text files" +"\\"+resTextDescription ) );
strInput = objStreamReader.ReadLine();
while ( strInput != null)
{

resTextDescription2 += strInput;
strInput = objStreamReader.ReadLine();
}
objStreamReader.Close();
}
else
{
resTextDescription2 = "myFile.txt does not exist!";
}
//determine whether male or female in order to display correct sign
if ( resGender == "Male")
resGender = "Male_sign_1.jpg";
else
resGender = "Female_sign_1.jpg";
//determine whether 'want' and 'have' children and convert to correctwords for display
if ( resHaveChildren == "have kids" )
resHaveChildren = "Yes";
else
resHaveChildren = "No";
if ( resWantChildren == "want kids" )
resWantChildren = "Yes";
else
resWantChildren = "No";
// The writing of html to display the values calculated
lblResults.Text += "<tr><td width='16%' bgcolor='#C7C9BE' class='text_bold'><div align='center'>" +resUserName +
"</div><hr noshade class='hr_line'></td>";
lblResults.Text += "<td colspan='2' bgcolor='#E1E2DC'class='text_bold'><div align='center'>" + resProfileHeading +
"</div><hr noshade class='hr_line'></td><tdcolspan='2'><div align='center'class='page_headings'>%</div></td></tr>";
lblResults.Text += "<tr><td rowspan='15' valign='top'bgcolor='#C7C9BE'><p align='center'><img src='images/" +resGender + "' width='20' height='22'></p>" +
"<img src='photos/" + resUserPhoto + "' width='80'height='88'><div align='center'></div></td>";
lblResults.Text += "<td colspan='2' bgcolor='#E1E2DC'><pclass='text'><br></p><p class='text'>" +resTextDescription2 + "</p><pclass='text_bold'> </p></td>";
lblResults.Text += "<td width='7%' rowspan='15'valign='top'><img src='images/hotlist_1.jpg' alt='Add ToFavorites' width='34' height='32'></td>" +
"<td width='14%' rowspan='15' valign='top'><imgsrc='images/email_1.jpg' alt='Email this profile' width='42'height='36'></td></tr>";
lblResults.Text += "<tr><td width='26%' bgcolor='#E1E2DC'class='text_bold'>Location :</td><td width='37%'bgcolor='#E1E2DC' class='text'>" + resProvince +"</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'class='text_bold'>Age :</td><td bgcolor='#E1E2DC'class='text'>" + resAge + "</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'class='text_bold'>Height : </td><td bgcolor='#E1E2DC'class='text'>" + resHeight + "</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'class='text_bold'>Body Type : </td><td bgcolor='#E1E2DC'class='text'>" + resBodyType + "</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'class='text_bold'>Looks : </td><td bgcolor='#E1E2DC'class='text'>"+ resLooks+"</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'class='text_bold'>Hair Colour : </td><td bgcolor='#E1E2DC'class='text'>"+resHairColor+"</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'class='text_bold'>Eye Colour : </td><td bgcolor='#E1E2DC'class='text'>"+resEyeColor+"</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'class='text_bold'>Ethnicity : </td><td bgcolor='#E1E2DC'class='text'>"+resEthnicity+"</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'class='text_bold'>Home Language : </td><tdbgcolor='#E1E2DC'class='text'>"+resHomeLanguage+"</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'class='text_bold'>Relationship Status : </td><tdbgcolor='#E1E2DC'class='text'>"+resRelStatus+"</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'class='text_bold'>Realtionship Interest : </td><tdbgcolor='#E1E2DC'class='text'>"+resRelInterest+"</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'class='text_bold'>Have Children : </td><tdbgcolor='#E1E2DC'class='text'>"+resHaveChildren+"</td></tr>";
lblResults.Text += "<tr><td bgcolor='#E1E2DC'class='text_bold'>Want Children : </td><tdbgcolor='#E1E2DC'class='text'>"+resWantChildren+"</td></tr>";
lblResults.Text += "<tr><td colspan='2'bgcolor='#E1E2DC'> </td></tr>";
lblResults.Text += "<tr><tdbgcolor='#497792'> </td><td colspan='2'bgcolor='#678BA1'> </td><td colspan='2'bgcolor='#678BA1'> </td></tr>";
lblResults.Text += "<tr><td> </td><tdcolspan='2'> </td><tdcolspan='2'> </td></tr>";
resTextDescription2 = "";
}
lblResults.Text += "</table>";
conPubs.Close();
}
void Button_Login(Object sender , ImageClickEventArgs e)
{
SqlConnection conClient;
string strSelect;
string strclientName;
SqlCommand cmdSelect;

//create a connection
conClient = newSqlConnection( @."Server=THALION\THALION;IntegratedSecurity=SSPI;database=DateGame" );
//select statement
strSelect = "Select userNameFrom [client] Where userName=@.username and userPassword=@.userpassword";
cmdSelect = new SqlCommand( strSelect, conClient );
cmdSelect.Parameters.Add( "@.username", txtNickName.Text );
cmdSelect.Parameters.Add( "@.userpassword", txtPword.Text );
//open a connection to db
conClient.Open();
//check to see if it already exists
strclientName = System.Convert.ToString(cmdSelect.ExecuteScalar());
if (strclientName.ToLower() == txtNickName.Text.ToString().ToLower())
{
//Store user name as session variable
Session["sessionUserName"] = txtNickName.Text.ToString();
txtNickName.Text = "";
txtPword.Text = "";
}
else
lbl_invalid_login.Text = "Invalid login!";
conClient.Close();
}
</script>

Actually, you code is quite nice! quick suggestion, use the ? operator for stuff like:

code wrote:

if ( resGender == "Male")
resGender = "Male_sign_1.jpg";
else
resGender = "Female_sign_1.jpg"

w/ the question mark operator it becomes

code wrote:

resGender = (resGender=="Male"?"Male_sign_1.jpg":"Female_sign_1.jpg");

as for how to impelment pagination, here's a linkHow to Implement a Repeater Control with Pagination