Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
function
Starting Member
12 Posts |
 Posted - 2010-09-01 : 02:51:44
|
| Hi,Maybe the question belongs in .NET Forum, maybe in SQL Forum. I dont know...The only thing i know is that its busting my nerves the past few days.All i try to do, through ASP.NET, is fill a datagrid with a dataset from a SQL database which contains Russian texts (along with english) and then export the data to an Excel file. Although all the webpages show the texts correctly (which also means that i've hit the target with the Tables Collations), only one page keeps busting my b...s! The strange about this web page is that it contains Country Criteria for my search mechanism, and sometimes the result of the Excel file is showing correctly, and some other times it shows rubbish texts (like chinese or something)!I've tried all the encodings around, but none seems to solve the problem permanently. All i wonder is how the hell the same page sometimes works fine, and other times doesn't??????=========PAGE CODE=========<%@ PAGE LANGUAGE="VB" Debug=True %><%@ Import Namespace="System.Data.Odbc" %><%@ Import Namespace="System.Data" %><%@ Import Namespace="System" %><% Response.Expires = -1 'Response.Write(Session("USER_FULL_PERMISSION")) 'Response.End() if (session("USER_FULL_PERMISSION") <> "1") then Response.Redirect("login.aspx") end if%><script language="vbscript" runat="server"> Private Sub Page_Load(ByVal obj As Object, ByVal e As EventArgs) If Not Page.IsPostBack() Then 'Call FillTables(tables) drpCountry_Fill() drpHotel_Fill() End If End Sub '================================================================ Private Sub btnExcel_Click(ByVal obj As Object, ByVal e As EventArgs) BindData() Response.Clear() Response.Charset = "utf-8" Response.AddHeader("content-disposition", "attachment;filename=report2.xls") Response.Buffer = True Response.ContentType = "application/vnd.ms-excel" Response.Charset = "" Me.EnableViewState = False Dim oStringWriter As New System.IO.StringWriter() Dim oHtmlTextWriter As New System.Web.UI.HtmlTextWriter(oStringWriter) dg.RenderControl(oHtmlTextWriter) Response.Write("<table><tr><td>Country:" & Me.drpCountry.SelectedItem.Text & " Hotel:" & Me.drpHotel.SelectedItem.Text & " From:" & Me.txtDateArrival1.Text & " To:" & Me.txtDateArrival2.Text & "</td></tr><tr><td height='10'></td></tr></table>" & oStringWriter.ToString()) Response.End() End Sub '================================================================ Private Sub drpCountry_Fill() Dim it As ListItem Dim conobj As odbc.odbcConnection Dim comobj As odbc.odbcCommand Dim sqlStr As String = "SELECT * FROM COUNTRY ORDER BY country_name" Dim dr As odbc.odbcDataReader it = New ListItem("---ALL---", "0") Me.drpCountry.Items.Add(it) conobj = New odbc.odbcConnection(Application("strConnect")) conobj.Open() comobj = New odbc.odbcCommand(sqlStr, conobj) dr = comobj.ExecuteReader If dr.HasRows() Then While dr.Read() it = New ListItem it.Text = dr.Item("country_name") it.Value = dr.Item("country_id") Me.drpCountry.Items.Add(it) End While End If conobj.Close() End Sub '================================================================ Private Sub drpHotel_Fill() Dim it As ListItem Dim conobj As Odbc.OdbcConnection Dim comobj As Odbc.OdbcCommand Dim sqlStr As String = "SELECT * FROM HOTELGROUPS ORDER BY hgrp_id" Dim dr As Odbc.OdbcDataReader it = New ListItem("---ALL---", "0") Me.drpHotel.Items.Add(it) conobj = New Odbc.OdbcConnection(Application("strConnect")) conobj.Open() comobj = New Odbc.OdbcCommand(sqlStr, conobj) dr = comobj.ExecuteReader If dr.HasRows() Then While dr.Read() it = New ListItem it.Text = dr.Item("hgrp_name") it.Value = dr.Item("hgrp_ids") Me.drpHotel.Items.Add(it) End While End If conobj.Close() End Sub '====================================================== Private Sub BindData() Dim where_part As String = " WHERE 1=1 " If Me.drpCountry.SelectedIndex <> 0 Then where_part = where_part & " AND res_country=" & Me.drpCountry.SelectedValue End If If Me.drpHotel.SelectedIndex <> 0 Then where_part = where_part & " AND res_hotel in (" & Me.drpHotel.SelectedValue & ")" End If If Me.txtDateArrival1.Text <> "" And Me.txtDateArrival2.Text <> "" Then where_part = where_part & " AND res_arrivalDate>=convert(datetime,'" & Me.txtDateArrival1.Text & "',103) and res_arrivalDate<=convert(datetime,'" & Me.txtDateArrival2.Text & "',103)" End If If Me.txtDateArrival1.Text <> "" And Me.txtDateArrival2.Text = "" Then where_part = where_part & " AND res_arrivalDate>=convert(datetime,'" & Me.txtDateArrival1.Text & "',103)" End If If Me.txtDateArrival1.Text = "" And Me.txtDateArrival2.Text <> "" Then where_part = where_part & " AND res_arrivalDate<=convert(datetime,'" & Me.txtDateArrival2.Text & "',103)" End If Dim conobj As New OdbcConnection(Application("strConnect")) conobj.Open() Dim sqlstr As String = "SELECT st_title as 'TITLE', country_name as 'COUNTRY', st_Surname as 'SURNAME', st_Name AS 'NAME', " & _ "st_Email AS 'EMAIL', hotel_name as 'HOTEL', res_nights as 'TOTAL NIGHTS', " & _ "convert(nvarchar,res_arrivalDate,103) as 'ARRIVAL DATE', " & _ "res_rooms as 'NO OF ROOMS', res_suites as 'NO OF SUITES', " & _ "res_thalassospaDays as 'SPA NO OF DAYS', res_points as 'TOTAL POINTS'" & _ "FROM RESERVATIONS " & _ "LEFT JOIN COUNTRY ON COUNTRY.country_id=RESERVATIONS.res_country " & _ "LEFT JOIN HOTELS ON HOTELS.hotel_id=RESERVATIONS.res_hotel " & _ "LEFT JOIN SUNNYTIMERS ON SUNNYTIMERS.st_IDNumber = RESERVATIONS.res_IATANumber " & _ "LEFT JOIN STATUS ON STATUS.status_id=RESERVATIONS.res_status " & _ where_part & " ORDER BY res_country" 'Response.Write(sqlstr) 'Response.End() Dim ds As New DataSet Dim dt As New DataTable dt.TableName = "results" dt.Columns.Add("TITLE") dt.Columns.Add("COUNTRY") dt.Columns.Add("SURNAME") dt.Columns.Add("NAME") dt.Columns.Add("EMAIL") dt.Columns.Add("HOTEL") dt.Columns.Add("TOTAL NIGHTS") dt.Columns.Add("NO OF ROOMS") dt.Columns.Add("NO OF SUITES") dt.Columns.Add("SPA NO OF DAYS") dt.Columns.Add("TOTAL POINTS") ds.Tables.Add(dt) Dim da As New OdbcDataAdapter(sqlstr, conobj) da.Fill(ds.Tables("results")) dg.DataSource = ds.Tables("results") dg.DataBind() conobj.Close() End Sub '====================================================== 'Private Sub btnSearch_Click(ByVal obj As Object, ByVal e As EventArgs) ' BindData() 'End Sub ''=================================================== 'Private Sub dg_Paged(ByVal sender As Object, ByVal e As DataGridPageChangedEventArgs) ' dg.CurrentPageIndex = e.NewPageIndex ' BindData() 'End Sub</script><HTML><HEAD> <TITLE></TITLE> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"> <!-- Stylesheet Declarations Starts --> <LINK type="text/css" href="styles.css" rel="stylesheet"> <!-- Stylesheet Declarations Ends --> <META content="MSHTML 6.00.2800.1400" name=GENERATOR></HEAD><%--<FRAMESET rows="115,*" BORDER="0" scrolling="no" NORESIZE> <FRAME name="top" src="top.aspx" BORDER="0" topmargin="0" NORESIZE> <FRAME name="Main" src="menu.aspx" BORDER="0" NORESIZE></FRAMESET>--%><body> <form id="frm" runat="server"> <table border="0" cellpadding="0" cellspacing="0" width="100%" height="100%"> <tr> <td width="100%" height="100%" align="center"> <table border="0" cellpadding="0" cellspacing="0" width="100%" height="100%"> <!-- #include file="header.aspx" --> <tr> <td width="100%" valign="top" height="100%"> <table border="0" cellpadding="0" cellspacing="0" width="100%" height="100%"> <tr> <td class="welcomeText" valign="top" width="250" height="100%"> <table border="0" cellpadding="0" cellspacing="0" width="90%" class="wholetable"> <!-- #include file="reportsMenu.aspx" --> </table> </td> <td height="100%" width="1" bgcolor="#000000"> </td> <td style="padding:10 10 10 10;" valign="top"> <table border="0" cellpadding="0" cellspacing="0"> <tr> <td class="text" align="center" width="100%" colspan="2"> <b>REPORT 2 - COUNTRIES, ARRIVAL DATE, HOTELS</b> </td> </tr> <tr> <td colspan="2" height="10"></td> </tr> <tr> <td class="text" align="right" width="120"> Country: </td> <td align="left" width="84%"> <asp:DropDownList id="drpCountry" cssclass="drpList" runat="server" width="250" /> </td> </tr> <tr> <td colspan="2" height="10"></td> </tr> <tr> <td class="text" align="right" width="120"> Hotel: </td> <td align="left" width="84%"> <asp:DropDownList id="drpHotel" cssclass="drpList" runat="server" width="250" /> </td> </tr> <tr> <td colspan="2" height="10"></td> </tr> <tr> <td class="text" align="right" width="120"> Arrival date (from): </td> <td align="left" width="84%"> <asp:textbox cssclass=fields id="txtDateArrival1" width=100 runat="server"/> <a href="javascript:;" onclick="window.open('popup.aspx?textbox=txtDateArrival1','cal','width=250,height=225,left=270,top=180')"> <img src="images/SmallCalendar.gif" border="0" align=absmiddle></a> </td> </tr> <tr> <td colspan="2" height="10"></td> </tr> <tr> <td class="text" align="right" width="120"> Arrival date (to): </td> <td align="left" width="84%"> <asp:textbox cssclass=fields id="txtDateArrival2" width=100 runat="server"/> <a href="javascript:;" onclick="window.open('popup.aspx?textbox=txtDateArrival2','cal','width=250,height=225,left=270,top=180')"> <img src="images/SmallCalendar.gif" border="0" align=absmiddle></a> </td> </tr> <%--<tr> <td colspan="2" height="10"></td> </tr> <tr> <td colspan="2" align="center"> <asp:button ID="btnSearch" CssClass="buttons" runat="server" Text="Search" OnClick="btnSearch_Click" /> </td> </tr>--%> <tr> <td colspan="2" height="10"></td> </tr> <tr> <td colspan="2" align="center"> <asp:button ID="btnExcel" CssClass="buttons" runat="server" Text="Export Excel" OnClick="btnExcel_Click" /> </td> </tr> <tr> <td colspan="2" height="10"></td> </tr> <tr> <td colspan="2" align="center"> <asp:DataGrid Visible="true" GridLines="Both" Font-Names="Verdana" Font-Size="10px" Enabled="true" CellPadding="5" CellSpacing="5" ID="dg" runat="server" BorderWidth="1" HeaderStyle-HorizontalAlign="Center" HeaderStyle-Font-Bold="True" HeaderStyle-BackColor="#29386D" HeaderStyle-ForeColor="White" AutoGenerateColumns="true"> <HeaderStyle Font-Size="12px" BackColor="#29386D" Font-Bold="True" ForeColor="White" HorizontalAlign="Center"/> </asp:DataGrid> </td> </tr> </table> </td> </tr> <tr> <td height="100%"></td> </tr> </table> </td> </tr> </table> </td> </tr> </table> </form></body></HTML> |
|
|
|
|
|
|
|
Russian text in sql server and .net