Binding dataset to a crystal report

Hi all,

The code I used is as follows.

dim dbconnection
as new oledb.oledbconnection(....conn string...)

Dim SQL As String

SQL = "SELECT * FROM <TABLENAME> "

dbConnection.Open()
Dim objAdapter As New OleDb.OleDbDataAdapter(SQL, dbConnection)
Dim objDataSet As New DataSet
objAdapter.Fill(objDataSet)
Dim oReport As New CrystalDecisions.CrystalReports.Engine.ReportDocument
Dim sRptPath As String = Server.MapPath("CRlMT.rpt")
oReport.Load(sRptPath)
oReport.SetDataSource(objDataSet)
CrystalReportViewer1.ReportSource = oReport

when i ran it I got the following error.

Logon failed. Details: ADO Error Code: 0x Source: Provider Description: Authentication failed. Native Error: Error in File <path>.rpt: Unable to connect: incorrect log on parameters.

what I am doing wrong here. Help is greatly appreciated.

note: asp.net web app.Make sure the file exists at the application path. Also make sure if you have permission to access the databasesql

Binding a SqlDataSource, to a GridView at Runtime

Hello

I'm experiencing some problems, binding a SqlDataSource to a GridView.

The following code creates the SqlDataSource:
string strConn = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
string strProvider = ConfigurationManager.ConnectionStrings["ConnectionString"].ProviderName;

SqlDataSource ds = new SqlDataSource(strProvider, strConn);
ds.SelectCommand = "SELECT * FROM rammekategori";

Then i bind the SqlDataSource to a GridView:

GridView1.DataSource = ds;
GridView1.DataBind();

ErrorMessage:

Format of the initialization string does not conform to specification starting at index 0.

Exception Details: System.ArgumentException: Format of the initialization string does not conform to specification starting at index 0.

Line 24: GridView1.DataBind();

Am i totally off target here? Can it be something about, that you have to set the datasource of the gridview, before the Page_Load event?

Thanks - MartinHN

You are probably not going to be happy with the results if you do manage to fix your immediate error. May I suggest you actually put the SqlDataSource in the .aspx page? You can then bind it in code, but don't change the select statement in code, as that too will cause some issues.|||

oh. The code was copied from VS2005, i forgot one important detail.

I add the SqlDataSource to the page, by using Page.Controls.Add(ds);

Still, i get the error, even if i set the DataSource property of the gridview like this:

GridView1.DataSource = (SqlDataSource)Page.FindControl("ds");

--

MartinHN

|||

What if you try this:

SqlDataSource m_SqlDataSource = Page.FindControl("ds") as SqlDataSource;

if (m_SqlDataSource != null)
{
GridView1.DataSourceID = m_SqlDataSource.ID;
GridView1.DataBind();
}

HTH,
Ryan

|||

>>>>What if you try this:

SqlDataSource m_SqlDataSource = Page.FindControl("ds") as SqlDataSource;

if (m_SqlDataSource != null)
{
GridView1.DataSourceID = m_SqlDataSource.ID;
GridView1.DataBind();
}

Still no success... It is still the samme error message:Format of the initialization string does not conform to specification starting at index 0.

I was wondering, if i need to set a couple of more properties on my SqlDataSource.

One thing i just tried, was createing a new SqlDataSource directly on the aspx page, from the Designer in VS2005. The i set the datasource, to the FindControl method, with the name as the parameter. That worked. But i'm interessted in getting a SqlDataSource object, from lower tier, than the page it self. So that i have a complete SqlDataSource with select, update, delete and insert commands, to use along with the gridview...

|||

Ah. I totally missed the actual error. Please post your connection string, leaving out any login information, but keep the formatting as-is.

|||

ok...

The connectionstring i am using are as follows:

<connectionStrings>
<add name="ConnectionString" connectionString="DRIVER={MySQL ODBC 3.51 Driver};SERVER=myserverip;DATABASE=mydb;UID=myuid;PASSWORD=mypwd;" providerName="System.Data.Odbc" />
</connectionStrings>

Should there be any troubles using a mySQL DB?

|||

I think I see your problem. The overloads for the SqlDataSource on MSDN are as follows:

SqlDataSource()
SqlDataSource(string connectionString, string selectCommand)
SqlDataSource(string providerName, string connectionString, string selectCommand)

So, you should be doing something like this:

string strConn = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
string strProvider = ConfigurationManager.ConnectionStrings["ConnectionString"].ProviderName;
string selectCommand = "SELECT * FROM rammekategori";

SqlDataSource ds = new SqlDataSource(strProvider, strConn, selectCommand);

HTH,
Ryan

|||

Ryan - I really cannot find a way to show my appreciation... That was very nice to have sorted out, thanks for your help.

What about insert, delete and update commands, can i set them after I've instantiated the SqlDataSource object?

|||

martinhn wrote:

What about insert, delete and update commands, can i set them after I've instantiated the SqlDataSource object?

Yes.

SqlDataSource.DeleteCommand
SqlDataSource.InsertCommand
SqlDataSource.UpdateCommand

HTH,
Ryan

sql

Bind a stored procedure within dataview

As I would like to execute a stored procedure within the Report Creation
Wizard. But only SQL String are allowed.
How can I "put" my stored procedure in this data view ? (I try EXEC
my_stored_proc in the SQL String area but not working...)
I would like to do it because I have almost 60 fields to add one by one into
a table manualy
If one of you have tips or can help me, I will appreciate :)
Have a good day !Are you getting any particular Errors? I've found that some sproc can be
called using exec sprocname @.Param1,@.Parm2 etc., while some do not. I've
resorted to the follwowing:
Putting a select * from table1
Once you get to the data tab, click on the ... and change the Command Type
to stored procedure.
You can then enter you sproc name in the query string box (don't enter the
exec).
If the sproc has parameters you'l need to set those up as well, to feed the
sproc.
"JahPil" wrote:
> As I would like to execute a stored procedure within the Report Creation
> Wizard. But only SQL String are allowed.
> How can I "put" my stored procedure in this data view ? (I try EXEC
> my_stored_proc in the SQL String area but not working...)
> I would like to do it because I have almost 60 fields to add one by one into
> a table manualy
> If one of you have tips or can help me, I will appreciate :)
> Have a good day !

BINARY_CHECKSUM algorithm

Hello,

Do you know if the algorithm for the BINARY_CHECKSUM function in documented
somewhere?
I would like to use it to avoid returning some string fields from the
server.
By returning only the checksum I could lookup the string in a hashtable and
I think this could make the code more efficient on slow connections.

Thanks in advanced and kind regards,

Orly JuniorI don't know where the algorithm is documented but I don't think it will
help you. There are many more possible strings than checksums so there isn't
a one-to-one correspondence between them. Unless your set of possible
strings is constrained to a small set you couldn't guarantee to translate a
checksum back into a string and you'd still have to maintain a lookup table
of strings on the client side. So if your set of strings is small and
constrained then you may as well invent your own codes. Alternatively, take
a look at Huffman Coding or one of the zip compression algorithms.

--
David Portas
SQL Server MVP
--|||Orly Junior (nomail@.nomail.com) writes:
> Do you know if the algorithm for the BINARY_CHECKSUM function in
> documented somewhere? I would like to use it to avoid returning some
> string fields from the server. By returning only the checksum I could
> lookup the string in a hashtable and I think this could make the code
> more efficient on slow connections.

Risky business. The checksum algorithm is fairly simple-minded. I beleive
it uses some xor mechanism. I don't have the references around right now,
but I recall that SQL Server MVP Steve Kass demonstrated how some quite
small changes could result in the same checksum.

Better in such case, to augment the table with a timestamp column. Such
a column is automatically updated every time SQL Server updates the
row. So you could store the timestamp client side, and pass that value,
if the table has the same value, there is no need for a refresh.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Orly,

Here is what I believe it does for a single varchar
column. It's not a particularly good hash function at
all.

create function binary_checksum_varchar (
@.t varchar(1000)
) returns int as begin
declare @.b bigint set @.b = 0
declare @.c tinyint
declare @.s bit set @.s = 0
declare @.i int set @.i = 1

while @.i <= len(@.t) begin
set @.c = ascii(substring(@.t,@.i,1))
set @.b = @.b / 16 * 16 + @.b % 16 ^ @.c / 16
set @.b = @.b * 16 + @.c % 16
if @.c >= 128 begin
set @.b = @.b ^ 0xFF
set @.s = 1 - @.s
end
set @.b = @.b % 0x0100000000 ^ @.b / 0x0100000000
set @.i = @.i + 1
end

if @.s = 1 set @.b = @.b ^ 0xFFFFFFFF
if @.b >= 0x80000000 set @.b = @.b | 0xFFFFFFFF00000000
return @.b
end
go

You'll find more information if some of the threads
here:

http://groups.google.co.uk/groups?q...+kass+sqlserver

Steve Kass
Drew University

Orly Junior wrote:

> Hello,
> Do you know if the algorithm for the BINARY_CHECKSUM function in documented
> somewhere?
> I would like to use it to avoid returning some string fields from the
> server.
> By returning only the checksum I could lookup the string in a hashtable and
> I think this could make the code more efficient on slow connections.
> Thanks in advanced and kind regards,
> Orly Junior
>

Binary parameters to stored procedures?

I have a stored procedure that takes a byte string as an argument:
CREATE PROCEDURE Reporting_TicketSelectGroups
@.publicPart NVARCHAR(400),
@.checkField BINARY(46),
@.langCode VARCHAR(9)
AS
...
I've created a DataSet with the name of the stored proc as as its query
string and with this expression as its parameter value for @.checkField:
=Code.CheckField(Parameters!ticket.Value)
This in turn refers to a function in the Code tab of the Report
Properties property sheet:
Public Function CheckField(ByVal aTicket As String) As Byte()
...
Return Convert.FromBase64String(...)
End Function
The idea is that there is a parameter called ticket and it is split in
to two parts, one part being in binary, and these two parts are then
used as parameters to the various queries used in the report. When I
attempt to preview this report, I get this error message:
An error has occurred during report processing.
Query execution failed for data set 'Groups'.
Implict conversion from data type nvarchar to binary is not allowed.
Use the CONVERT function to run this query.
I'm assuming the last two sentences come from SQL Server and indicate
that my byte[] value is being converted to string on the way -- either
that or I have stuffed it up in some way. Can anyone tell me whether
this approach should work, or is simply not possible to pass binary
parameters from RS?
--
Damian CugleyI wrote:
> I have a stored procedure that takes a byte string as an argument:
> [...] Can anyone tell me whether
> this approach should work, or is simply not possible to pass binary
> parameters from RS?
I gather from the deafening silence that it is possible to pass neither
binary parameters nor other formats like UUIDs.
My workaround was straightforward enough, once I had decided to do it: I
wrote a base64 (RFC 1521) codec in T-SQL so I can pass the data safely
as a character string.

Binary Data Type

I want to set a variable (binary) from another variable
(varchar) with the actual string. i.e I have generated the
actual binary value and want to pass it to another
variable...
i have tried to use 'set @.x (binary) = @.y (varchar)' but
an Implicit conversion is not allowed. I cannot do an
explicit convertion by using 'cast(@.y as binary)' as this
converts the string into its own binary value
help
MatConvert the binary value into a hex string & assign it to the variable.
Search SQL Server Books Online for sp_hexadecimal, which is a stored
procedure which can do this conversion. Also search the google archives of
this newsgroup for examples of various approaches to binary datatype
conversions.
--
Anith|||Here's the easiest way I know of (although it's somewhat of a kludge):
declare @.binary varbinary(5)
declare @.string varchar(12)
declare @.sql nvarchar(200)
set @.string='0x1234567890'
set @.sql = 'set @.binary = ' + @.string
exec sp_executesql @.sql, N'@.binary varbinary(5) output', @.binary output
print @.binary
"mat" <anonymous@.discussions.microsoft.com> wrote in message
news:458501c42b98$09762ea0$a101280a@.phx.gbl...
> I want to set a variable (binary) from another variable
> (varchar) with the actual string. i.e I have generated the
> actual binary value and want to pass it to another
> variable...
> i have tried to use 'set @.x (binary) = @.y (varchar)' but
> an Implicit conversion is not allowed. I cannot do an
> explicit convertion by using 'cast(@.y as binary)' as this
> converts the string into its own binary value
> help
> Mat|||Jeff,
Problem is, 1101 in base 2 (binary) = 13 in base 10 (decimal); if you print
convert(int, @.bin2), you'll get a result of 77, not 13 (at least, on my
system).
"Jeff Duncan" <anonymous@.discussions.microsoft.com> wrote in message
news:C07F51FE-1BD8-462F-B0D1-24D0A93CD5B2@.microsoft.com...
> Mat
> try playing with this. It seems to work fine for me
>
> declare @.bin varbinary,
> @.var Varchar(30),
> @.bin2 varbinary
> select @.bin = 1101
> print @.bin
> select @.var = convert(varchar(30), @.bin)
> print @.var
> select @.bin2 = convert(varbinary, @.var)
> print @.bin2
> Jeff Duncan
> MCDBA, MCSE+Isql

Binary Data Type

I want to set a variable (binary) from another variable
(varchar) with the actual string. i.e I have generated the
actual binary value and want to pass it to another
variable...
i have tried to use 'set @.x (binary) = @.y (varchar)' but
an Implicit conversion is not allowed. I cannot do an
explicit convertion by using 'cast(@.y as binary)' as this
converts the string into its own binary value
help
Mat
Convert the binary value into a hex string & assign it to the variable.
Search SQL Server Books Online for sp_hexadecimal, which is a stored
procedure which can do this conversion. Also search the google archives of
this newsgroup for examples of various approaches to binary datatype
conversions.
Anith
|||Here's the easiest way I know of (although it's somewhat of a kludge):
declare @.binary varbinary(5)
declare @.string varchar(12)
declare @.sql nvarchar(200)
set @.string='0x1234567890'
set @.sql = 'set @.binary = ' + @.string
exec sp_executesql @.sql, N'@.binary varbinary(5) output', @.binary output
print @.binary
"mat" <anonymous@.discussions.microsoft.com> wrote in message
news:458501c42b98$09762ea0$a101280a@.phx.gbl...
> I want to set a variable (binary) from another variable
> (varchar) with the actual string. i.e I have generated the
> actual binary value and want to pass it to another
> variable...
> i have tried to use 'set @.x (binary) = @.y (varchar)' but
> an Implicit conversion is not allowed. I cannot do an
> explicit convertion by using 'cast(@.y as binary)' as this
> converts the string into its own binary value
> help
> Mat
|||Mat
try playing with this. It seems to work fine for me
declare @.bin varbinary,
@.var Varchar(30),
@.bin2 varbinary
select @.bin = 1101
print @.bin
select @.var = convert(varchar(30), @.bin)
print @.var
select @.bin2 = convert(varbinary, @.var)
print @.bin2
Jeff Duncan
MCDBA, MCSE+I
|||Jeff,
Problem is, 1101 in base 2 (binary) = 13 in base 10 (decimal); if you print
convert(int, @.bin2), you'll get a result of 77, not 13 (at least, on my
system).
"Jeff Duncan" <anonymous@.discussions.microsoft.com> wrote in message
news:C07F51FE-1BD8-462F-B0D1-24D0A93CD5B2@.microsoft.com...
> Mat
> try playing with this. It seems to work fine for me
>
> declare @.bin varbinary,
> @.var Varchar(30),
> @.bin2 varbinary
> select @.bin = 1101
> print @.bin
> select @.var = convert(varchar(30), @.bin)
> print @.var
> select @.bin2 = convert(varbinary, @.var)
> print @.bin2
> Jeff Duncan
> MCDBA, MCSE+I

Binary Data Type

I want to set a variable (binary) from another variable
(varchar) with the actual string. i.e I have generated the
actual binary value and want to pass it to another
variable...
i have tried to use 'set @.x (binary) = @.y (varchar)' but
an Implicit conversion is not allowed. I cannot do an
explicit convertion by using 'cast(@.y as binary)' as this
converts the string into its own binary value
help
MatConvert the binary value into a hex string & assign it to the variable.
Search SQL Server Books Online for sp_hexadecimal, which is a stored
procedure which can do this conversion. Also search the google archives of
this newsgroup for examples of various approaches to binary datatype
conversions.
Anith|||Here's the easiest way I know of (although it's somewhat of a kludge):
declare @.binary varbinary(5)
declare @.string varchar(12)
declare @.sql nvarchar(200)
set @.string='0x1234567890'
set @.sql = 'set @.binary = ' + @.string
exec sp_executesql @.sql, N'@.binary varbinary(5) output', @.binary output
print @.binary
"mat" <anonymous@.discussions.microsoft.com> wrote in message
news:458501c42b98$09762ea0$a101280a@.phx.gbl...
> I want to set a variable (binary) from another variable
> (varchar) with the actual string. i.e I have generated the
> actual binary value and want to pass it to another
> variable...
> i have tried to use 'set @.x (binary) = @.y (varchar)' but
> an Implicit conversion is not allowed. I cannot do an
> explicit convertion by using 'cast(@.y as binary)' as this
> converts the string into its own binary value
> help
> Mat|||Mat
try playing with this. It seems to work fine for me
declare @.bin varbinary,
@.var Varchar(30),
@.bin2 varbinary
select @.bin = 1101
print @.bin
select @.var = convert(varchar(30), @.bin)
print @.var
select @.bin2 = convert(varbinary, @.var)
print @.bin2
Jeff Duncan
MCDBA, MCSE+I|||Jeff,
Problem is, 1101 in base 2 (binary) = 13 in base 10 (decimal); if you print
convert(int, @.bin2), you'll get a result of 77, not 13 (at least, on my
system).
"Jeff Duncan" <anonymous@.discussions.microsoft.com> wrote in message
news:C07F51FE-1BD8-462F-B0D1-24D0A93CD5B2@.microsoft.com...
> Mat
> try playing with this. It seems to work fine for me
>
> declare @.bin varbinary,
> @.var Varchar(30),
> @.bin2 varbinary
> select @.bin = 1101
> print @.bin
> select @.var = convert(varchar(30), @.bin)
> print @.var
> select @.bin2 = convert(varbinary, @.var)
> print @.bin2
> Jeff Duncan
> MCDBA, MCSE+I

Best way to select a Constant String from a Table ?

Hi,
What is the best way to generate a constant String from a Table (Only 1 row)
.
I have some sql statements returning a few rows. I want to also return a row
with a Constant String along with this sql statement..
e.g.
Select * from orders
Union
Select 'End of Order Select' from '
I can try selecting it from orders table, but this should return only one ro
w.
Any ideas ?
- AnandSorry for the trouble, found out the answer that I can just do
Select 'End of Order Select'
"S Anand" wrote:

> Hi,
> What is the best way to generate a constant String from a Table (Only 1 ro
w).
> I have some sql statements returning a few rows. I want to also return a r
ow
> with a Constant String along with this sql statement..
> e.g.
> Select * from orders
> Union
> Select 'End of Order Select' from '
> I can try selecting it from orders table, but this should return only one
row.
> Any ideas ?
> --
> - Anand|||hi Anand
This will work fine if you are note selecting any value from a table. If u
require a value
from a table along with a constant value, then u require to do like this:
SELECT TOP 1 'Const Value', <COLUMNS> FROM <TABLE>
you query can be modified as
Select * from orders
Union ALL
Select 'End of Order Select'
try using UNION ALL if u definately want the text to be displayed.
in case of UNION the second table will not display a value if the same value
exists
in the main table.
Hope this gives u a better picture
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"S Anand" wrote:
> Sorry for the trouble, found out the answer that I can just do
> Select 'End of Order Select'
> "S Anand" wrote:
>|||A few comments:

> Select 'End of Order Select'
Use square brackets of double-quotes around the produced column name instead
. The column you produce
in the result is an identifier and non-standard identifiers are delimited wi
th double-quotes in SNAI
SQL (and SQL Server) and SQL Server also allow double-quotes. Why SQL Server
allow single quotes for
identifiers in this particular case is beyond my understanding, very strange
..
Don't do SELECT *. It might just have been an example, but imagine of the ta
ble structure changes
and you add or remove columns. The UNION won't work.
Also, don't expect the SELECT with a constant to come last unless you do an
ORDER BY for the UNION.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"S Anand" <x@.hotmail.com> wrote in message
news:81178FBE-0B61-411E-8836-D01240F81227@.microsoft.com...
> Sorry for the trouble, found out the answer that I can just do
> Select 'End of Order Select'
> "S Anand" wrote:
>

Best way to filter out noise words from user entered search string

Hi there,
I'm implemeting a full text search on my company's site and am trying to get
around the 'Query contained only noise words' error. What's the best way of
stripping the text from the search string and then notifying the user that
the words were removed? (kind of like a google search). Is there a way to
compare the search string with the noise word file and remove all instances
of matching words?
Thanks!
-- are we all computer monkeys?
The best way to do this is to stop mssearch, replace the contents of your
noise word list with a single space and then restart MSSearch and rebuild
your catalog.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Mr. Roundhill" <MrRoundhill@.discussions.microsoft.com> wrote in message
news:21EE62C6-74C2-4E01-B3A7-79D3612E5584@.microsoft.com...
> Hi there,
> I'm implemeting a full text search on my company's site and am trying to
get
> around the 'Query contained only noise words' error. What's the best way
of
> stripping the text from the search string and then notifying the user that
> the words were removed? (kind of like a google search). Is there a way
to
> compare the search string with the noise word file and remove all
instances
> of matching words?
> Thanks!
> --
> -- are we all computer monkeys?

Best way to connect to a MSSQL database

Hello,

Is it better to use a DSN connection or using a connection string Set Connection = Server.CreateObject("ADODB.Connection")

Which is easier to use and more reliable?

Also could I have all the connection info on one page and then just include it with a include statment everytime I need to query the database??

Thank you,

Rich

Are you using classic ASP or ASP.NET? The first has pages ending in .asp, and the second ends with .aspx.

In either case, DSNs and ODBC are discouraged.

For Sql Server 2000, look here:http://www.connectionstrings.com/?carrier=sqlserver, and for 2005, here:http://www.connectionstrings.com/?carrier=sqlserver2005

|||

I am learning classic ASP and then will learn ASP.NET

Why are DSNs and ODBC discouraged...

I was looking at the websites you gave me.. then are all saying .NET... will classic ASP work too??

Rich

|||

for Classic ASP i would recommend using DSN and ODBC as you can use any database with this kind of connections with smaller changes.

you can place your connection in a page called connection.asp which then you will need to inlcude at the top of each page. for sample check out this:

http://www.codeproject.com/database/connectionstrings.asp

but for ASP.NET its provides its own Provider for different database which makes its easy to control the database and are faster and more releiable then ODBC.

thanks

|||

Frankly, I would go straight to ASP.NET. It has replaced classic ASP. There's a newsgroup for classic ASP over at microsoft.public.inetserver.asp.general (which you can get to through Google Groups). There used to be thousands of posts a month there. No there are just one or two per day. Classic ASP will not be developed any further. The only reason that people generally use it these days is because they have existing apps that were written in classic ASP that they want to maintain.

ODBC is deprecated technology. DSNs make use of ODBC. They cause unnecessary overhead and maintenance. For classic ASP, you should use OLEDB.

http://databases.aspfaq.com/database/what-should-my-connection-string-look-like.html